快逸报表调用oralce的存储过程(查询临时表，有返回结果集)

1.报表文件

1。1报表中调用的存储过程

{CALL BILLTYPE96371_PROC(?,?,?,?,?,?,?)}

1。2报表传递的参数

游标参数需要指定（变量名任意）。

2.存储过程

 

2.1需要先建立个package

create or replace package report_cur as

  TYPE report_Cursor_Type IS REF CURSOR;

end report_cur;

 

2.2 建立一张创建临时表的存储过程

 

create or replace procedure CREATE_TEMP_TABLE
AS
iCount number;
vsql varchar2(1000);
tablename varchar2(100);
begin
tablename:='TEMPBILLTYPE96371';

 --创建临时表
select   count(*) into iCount   from   ALL_OBJECTS   where   OBJECT_NAME =''||tablename||'';

 if iCount= 0 then

vsql:='create GLOBAL TEMPORARY table '||tablename||'(
 statDate date,
 bigId number(12),
 detailId number(12)
 )ON COMMIT PRESERVE ROWS';
 execute immediate vsql;

 end if;
 --结束
end ;

 

2.3 报表调用的存储过程

create or replace procedure BILLTYPE96371_PROC(
startDate in varchar2,
endDate in varchar2,
deptId in varchar2,
activeSource in varchar2,
businessType in varchar2,
businessDetailType in varchar2,
cv_results out report_cur.report_cursor_type
) is

  dayNum NUMBER(12);
  num    number(12);
  vsql varchar2(1000);
  tempBigId number(12);
  tempDetailId number(12);
  tablename varchar2(100);
  iCount number(12);
  tempStartDate varchar2(30);
  tempEndDate varchar2(30);
  tempDate varchar2(30);
CURSOR cur IS
  select id,parentid from v_billtype where parentid in (SELECT id from v_billType WHERE parentId=186 and isActive = 1) and isActive = 1;

begin

if startDate is null then
tempStartDate:=to_char(sysdate,'yyyy-MM-dd');
else
tempStartDate:=startDate;
end if;
if endDate is null then
tempEndDate:=to_char(sysdate,'yyyy-MM-dd');
else
tempEndDate:=endDate;
end if;

tablename:='TEMPBILLTYPE96371';

  dayNum:=(to_date(tempEndDate,'yyyy-MM-dd')+1)-to_date(tempStartDate,'yyyy-MM-dd');--计算二个日期相隔的天数
 open cur;
 fetch cur into tempDetailId,tempBigId;

 --创建临时表
select   count(*) into iCount   from   ALL_OBJECTS   where   OBJECT_NAME =''||tablename||'';

 if iCount = 0 then

vsql:='create GLOBAL TEMPORARY table '||tablename||'(
 statDate date,
 bigId number(12),
 detailId number(12)
 )';
 execute immediate vsql;
 end if;

 --结束

  while cur%found loop--循环细类
    num:=0;
    while num<dayNum loop--循环日期
      --fetch cur into tempDetailId,tempBigId;
      tempDate:=to_date(tempStartDate,'yyyy-MM-dd')+num;
    vsql:='insert into '||tablename||'(statDate,bigId,detailId) values('''||tempDate||''','||tempBigId||','||tempDetailId||')';

    execute immediate vsql;--插入临时数据
    num:=num+1;
    end loop;

   fetch  cur  into  tempDetailId,tempBigId;
  end loop;

  close cur;

 

 open cv_results for
 SELECT to_char(a.createtime, 'YYYY-MM-DD') as 统计日期,
 (select c.name from bill_type c  where c.id = a.business_type) as 业务类型,
 (select d.name from bill_type d where d.id =a.BUSINESS_DETAIL_TYPE ) as 业务细类 ,
 (select count(d.id) from bill d where d.businessid = a.id) as 工单数量,
 1 as 数量
  from wasu_businessactive a
  WHERE a.from_where='96371'
  and (a.createtime>=to_date(''||tempStartDate||'','YYYY-MM-DD'))
  and (a.createtime<to_date(''||tempEndDate||'','YYYY-MM-DD')+1)
  and (a.createman in (select b.userid from sys_userdept b where b.deptid=''||deptId||'' ) or ''||deptId||'' is null)
  and (a.active_source=''||activeSource||'' or ''||activeSource||'' is null)
  and (a.business_type=''||businessType||''  or  ''||businessType||'' is null)
  and (a.business_detail_type=''||businessDetailType||'' or ''||businessDetailType||'' is null)

 union
 select
  to_char(a.statDate, 'YYYY-MM-DD') as 统计日期,
  (select c.name from bill_type c  where c.id = a.bigId) as 业务类型,
  (select d.name from bill_type d where d.id =a.detailId ) as 业务细类 ,
  0 as 工单数量,
  0 as 数量
  from  TEMPBILLTYPE96371 a
  where
   (a.bigId=''||businessType||''  or  ''||businessType||'' is null)
  and (a.detailId=''||businessDetailType||'' or ''||businessDetailType||'' is null)

 ;

 

end BILLTYPE96371_PROC;