3,491
社区成员
发帖
与我相关
我的任务
分享
CREATE OR REPLACE procedure test(time1 in varchar2,time2 in varchar2) is
cr RefCursor.t_Refcursor;
v_bh number(20); --这个与表中“问题类型编号”字段类型一致
v_mc varchar2(100); --这个与表中“问题类型名称”字段类型一致
v_sql varchar2(4000);
v_sql1 varchar2(1000):='';
begin
v_sql :='create or replace view aa as '
v_sql1:= 'select to_char(AUDITTIME,''yyyy'') as 年份 ';
open cr for 'select distinct PROBLEMTYPEID from ES_AUDITRECORDS where to_char(AUDITTIME,''yyyy'')>='||''''||nvl(time1,'1111')||''''||' and to_char(AUDITTIME,''yyyy'')<='||''''||nvl(time2,'9999')||'''';
fetch cr into v_bh;
select PROBLEMTYPENAME into v_mc from ES_PROBLEMSTYPE where PROBLEMTYPEID=v_bh;
while cr %found loop
v_sql1:=v_sql||' , sum(case PROBLEMTYPEID when ''' || v_bh || ''' then 1 else 0 end) as "' || v_mc||'"';
fetch cr into v_bh;
select PROBLEMTYPENAME into v_mc from ES_PROBLEMSTYPE where PROBLEMTYPEID=v_bh;
end loop;
v_sql1:=v_sql1 || ' from ES_AUDITRECORDS where to_char(AUDITTIME,''yyyy'')>='||''''||nvl(time1,'1111')||''''||' and to_char(AUDITTIME,''yyyy'')<='||''''||nvl(time2,'9999')||''''||' group by to_char(AUDITTIME,''yyyy'')';
v_sql:=v_sql ||'('||v_sql1||')';
execute immediate v_sql;
end;
/
CREATE OR REPLACE procedure test(time1 in varchar2,time2 in varchar2) is
v_cur RefCursor.t_Refcursor;
cr RefCursor.t_Refcursor;
v_bh number(20); --这个与表中“问题类型编号”字段类型一致
v_mc varchar2(100); --这个与表中“问题类型名称”字段类型一致
v_sql varchar2(4000);
v_sql1 varchar2(1000):='';
begin
v_sql :='create or replace view aa as '
v_sql1:= 'select to_char(AUDITTIME,''yyyy'') as 年份 ';
open cr for 'select distinct PROBLEMTYPEID from ES_AUDITRECORDS where to_char(AUDITTIME,''yyyy'')>='||''''||nvl(time1,'1111')||''''||' and to_char(AUDITTIME,''yyyy'')<='||''''||nvl(time2,'9999')||'''';
fetch cr into v_bh;
select PROBLEMTYPENAME into v_mc from ES_PROBLEMSTYPE where PROBLEMTYPEID=v_bh;
while cr %found loop
v_sql1:=v_sql||' , sum(case PROBLEMTYPEID when ''' || v_bh || ''' then 1 else 0 end) as "' || v_mc||'"';
fetch cr into v_bh;
select PROBLEMTYPENAME into v_mc from ES_PROBLEMSTYPE where PROBLEMTYPEID=v_bh;
end loop;
v_sql1:=v_sql1 || ' from ES_AUDITRECORDS where to_char(AUDITTIME,''yyyy'')>='||''''||nvl(time1,'11111111111111')||''''||' and to_char(AUDITTIME,''yyyy'')<='||''''||nvl(time2,'9999')||''''||' group by 年份';
v_sql:=v_sql ||'('||v_sql1||')';
execute immediate v_sql;
end;
/