报表统计（使用数组返回游标）

procedure pro_bhkrbb(begintime  in varchar2, --开始日期
                       endtime   in varchar2, --结束日期
                       serid    in varchar2, --网点编号
                       cur_1     out t_cur, --返回
                       as_OutMsg out varchar2, --传出参数
                       an_Res    out int) is
 l_jg     t_array := t_array();
Type  c_Type  is ref Cursor;
c_cur c_Type;
Type query is record (serid varchar2(6),changereason varchar2(3),sl number(12));
var query;
tempserid varchar(6):='0'; --上一个网点编号
column2 number(10,0):=0; -- 丢失 01
column3 number(10,0):=0; -- 损坏 02
column4 number(10,0):=0; -- 卡片质量问题 03
column5 number(10,0):=0; -- 更改银行 04
column6 number(10,0):=0; -- 卡面信息更改 05
column7 number(10,0):=0; -- 有效期满 06
column8 number(10,0):=0; -- 其它原因 99
column9 number(10,0):=0; -- 合计
procedure reasonValue(var1 query) is
begin
case(var1.changereason)
 when '01'  then
   column2:=var1.sl;--丢失
   column9:=column9+var1.sl;
 when '02'  then
   column3:=var1.sl; -- 损坏
   column9:=column9+var1.sl;
 when '03' then
   column4:=var1.sl; -- 卡片质量问题
   column9:=column9+var1.sl;
 when '04' then
   column5:=var1.sl;--更改银行
   column9:=column9+var1.sl;
  when '05' then
   column6:=var1.sl; --卡面信息更改  
   column9:=column9+var1.sl; 
 when '06' then
   column7:=var1.sl; --有效期满
   column9:=column9+var1.sl;
 else
   column8:=var1.sl; --其它原因
   column9:=column9+var1.sl;
 end case; 
end reasonValue;
function getQuerySql (time1 in varchar2,time2 in varchar2,flag boolean) return varchar2 is
sqlstruct varchar2(1000);
begin
if (flag) then
  sqlstruct:= ' select serid,t.changereason,count(*) from zx_card_business t where t.bustype='''||'05'||'''';
else
  sqlstruct:= ' select '''||'00'|| '''as serid, t.changereason,count(*) from zx_card_business t where t.bustype='''||'05'||'''';
end if;
if (time1 is not  null) then
sqlstruct:=sqlstruct||' and APPTIME>to_date('||chr(39)||time1||chr(39)||','||chr(39)||'yyyy-mm-dd'||chr(39)||')';
end if;
if (time2 is not null) then
sqlstruct:=sqlstruct||' and APPTIME<to_date('||chr(39)||time2||chr(39)||','||chr(39)||'yyyy-mm-dd'||chr(39)||')+1';
end if;
if (serid is not null) then
sqlstruct:=sqlstruct|| ' and  SERID='''||serid||'''';
 end if;
if (flag) then
  sqlstruct:=sqlstruct||' group by t.serid,t.changereason ';
else
  sqlstruct:=sqlstruct||' group by t.changereason '; 
end if; 
return sqlstruct;
end getQuerySql;  
procedure tj(time1 in varchar2,time2 in varchar2,flag boolean,tempcolumn1 varchar2) is
column1 varchar2(100);
begin
open c_cur for  getQuerySql(time1,time2,flag);                    
  loop
     fetch c_cur into var;
  exit  when(c_cur%notfound);
    if(tempserid<>'0' and var.serid<>tempserid  ) then
      if (flag) then
        select sername into column1 from zx_card_netrecord where serid=tempserid;
      else
        column1:=tempcolumn1;
      end if; 
       l_jg.extend;   
       l_jg(l_jg.last):=column1||';'||column2||';'||column3||';'||column4||';'||column5||';'||column6||';'||column7||';'||column8||';'||column9;
       tempserid:=var.serid;
       column2:=0;
       column3:=0;
       column3:=0;
       column4:=0;
       column5:=0;
       column6:=0;
       column7:=0;
       column8:=0;
       column9:=0;
    end if;
    tempserid:=var.serid;
    reasonValue(var);   
  end loop;
    if(tempserid<>'0') then
       if(flag) then
       select sername into column1 from zx_card_netrecord where serid=tempserid;
       else
         column1:=tempcolumn1;
       end if;
       l_jg.extend;   
       l_jg(l_jg.last):=column1||';'||column2||';'||column3||';'||column4||';'||column5||';'||column6||';'||column7||';'||column8||';'||column9;
       column2:=0;
       column3:=0;
       column3:=0;
       column4:=0;
       column5:=0;
       column6:=0;
       column7:=0;
       column8:=0;
       column9:=0;
    end if;
close c_cur;
end tj;                   
begin
l_jg.extend;
l_jg(l_jg.last) := 'column1;column2;column3;column4;column5;column6;column7;column8;column9';
tj(begintime,endtime,true,'');
tempserid:='0';
tj(begintime,endtime,false,'单项业务合计');
if(begintime=endtime) then
tempserid:='0';
  if(to_char(to_date(begintime,'yyyy-mm-dd'),'D')='1') then
   tj(to_char((to_date(begintime,'yyyy-mm-dd')-6),'yyyy-mm-dd'),begintime,false,'单项业务周合计');
  else
   tj(to_char((to_date(begintime,'yyyy-mm-dd')-to_char(to_date(begintime,'yyyy-mm-dd'),'D')+2),'yyyy-mm-dd'),to_char((to_date(begintime,'yyyy-mm-dd')-to_char(to_date(begintime,'yyyy-mm-dd'),'D')+8),'yyyy-mm-dd'),false,'单项业务周合计');
  end if;
   tempserid:='0';
   tj(substrb(begintime,0,6)||'-01',to_char(last_day(to_date(begintime,'yyyy-mm-dd')),'yyyy-mm-dd'),false,'单项业务月合计'); 
   tempserid:='0';
   tj(substrb(begintime,0,4)||'-01-01',substrb(begintime,0,4)||'-12-31',false,'单项业务年合计'); 
   tempserid:='0';
   tj('2005-01-01',to_char(sysdate,'yyyy-mm-dd'),false,'单项业务总合计');  
end if;   
open cur_1 for
      select * from table(cast(l_jg as t_array));
    an_Res := 0;                   
exception
    when others then
    as_OutMsg := '统计出错：' || sqlerrm;
    an_Res := -1;                  
end   pro_bhkrbb;