oracle动态的创建临时表

knyanzmufa

　　CREATE OR REPLACE PACKAGE BODY "DAILYREPORTNEW1" is
       procedure welldailyreportnew1(startdate in varchar2,enddate in varchar2,
       orgid in varchar2,my_ref_cursor out ref_cursor) is
       begin
                 DECLARE
                 flg NUMBER;
                 sqlstr1 varchar2(1000);--创建临时表
                 sqlstr2 varchar2(1000);--插入数据
                 sqlstr3 varchar2(1000);--查询临时表
                 sqlstr4 varchar2(1000);--查询临时表
                 syzl varchar2(10);--收油总量
                 yyzl VARCHAR2(10);--用油总量
                 zkcl  varchar2(10);--库存量
                 seqno VARCHAR2(10);--序号
                 orgname VARCHAR2(200);--机构名称
                 uporgname VARCHAR2(200);--上级机构名称
                 countss VARCHAR2(50);
                 coutsy  VARCHAR2(50);
                 cyz VARCHAR2(10);--差异值
                 cursor alldate is
                 Select Rownum,org_id times,org_name oname
                 from tab_org_info where tab_org_info.up_org_id=orgid;
                 --查询父组织为orgid的所有子组织(编号，名称)
                 curdate alldate%rowtype;
                 BEGIN
                      SELECT COUNT(*) INTO flg FROM User_Tables WHERE TABLE_NAME = 'TEMP_TAB3';
                       sqlstr1 := 'CREATE   GLOBAL   TEMPORARY   TABLE   TEMP_TAB3(
                        seqno varchar2(10),
                        orgname varchar2(20),
                        uporgname varchar2(20),
                        syzl varchar2(10),
                        yyzl varchar2(10),
                        zkcl varchar2(10),
                        cyz varchar2(10)
                      )
                      ON   COMMIT   delete   ROWS';
                 IF flg = 0 THEN
                         execute immediate 'grant create table to omstest';
                         execute immediate sqlstr1;
                 END IF;
                 open alldate;
                      loop
                          begin
                            fetch alldate into curdate;
                            exit when alldate%notfound;
                            --朱湘鄂2010-01-13
                            --每井队收油总量syzl
                            select nvl(sum(z.zsyl),0) INTO syzl from
                            (select nvl(toi.syl,0) as zsyl from tab_oil_info toi
                            where toi.org_id = curdate.times and toi.acq_date>=startdate and toi.acq_date<=enddate) z;
                            --每井队用油总量 yyzl
                            select nvl(sum(x.zyyl),0) INTO yyzl from
                            (select nvl(toi.yyl,0) as zyyl from tab_oil_info toi
                            where toi.org_id = curdate.times and toi.acq_date>=startdate and toi.acq_date<=enddate) x;
                 
                            --上级机构以及井队名称
                            SELECT toi1.Org_Name,curdate.oname INTO uporgname,orgname FROM
                            tab_org_info toi1 WHERE toi1.org_id = orgid;
                           
                            --每井队库存量kcl
                            dbms_output.put_line(curdate.times);                           
                            select min(nvl(TO_CHAR(toi.kcl),0)) into zkcl from tab_oil_info toi where toi.org_id=curdate.times and toi.acq_date>=startdate and toi.acq_date<=enddate;                        
                           
                           -- 序号seqno
                           SELECT nvl(sum(seq_no),0) INTO seqno FROM tab_oil_info WHERE org_id = curdate.times ;
                           dbms_output.put_line(curdate.times);
　　/***得到差异值***/--cyz
                             cyz:='0';
                             cyz:=nvl(to_number(syzl),0)-nvl(to_number(yyzl),0)-nvl(to_number(zkcl),0);
                             dbms_output.put_line('差异值为:'+cyz);                           
                             if cyz is null
                             then
                              cyz:='0';
                             end if;
                             dbms_output.put_line(cyz);
                             execute immediate 'insert into temp_tab3(seqno,orgname,uporgname,syzl,yyzl,zkcl,cyz) values('''||seqno||''','''||orgname||''','''||uporgname||''','''||syzl||''','''||yyzl||''','''||zkcl||''','''||cyz||''')';
                          end;
                      end loop;
                 --收油总量
                 select nvl(tt.syl,0) INTO syzl from (select nvl(sum(syl),0) syl from tab_oil_info where acq_date>=startdate and acq_date<=enddate and org_id in(select org_id from tab_org_info where up_org_id=orgid)) tt;
                 dbms_output.put_line(syzl);    
                 --查询用油总量
                 select nvl(sum(x.zyyl),0) INTO yyzl from
                 (select nvl(toi.yyl,0) as zyyl from tab_oil_info toi
                   where toi.org_id = curdate.times and toi.acq_date>=startdate and toi.acq_date<=enddate) x;
                 dbms_output.put_line(yyzl);    
                 zkcl:='0';
                 cyz:='0';
                 select '总计' INTO seqno from dual;
                 select '','' INTO orgname,uporgname from dual;
                 execute immediate 'select * from temp_tab3';
                 execute immediate 'insert into temp_tab3(seqno,orgname,uporgname,syzl,yyzl,zkcl,cyz) values('''||seqno||''','''||orgname||''','''||uporgname||''','''||syzl||''','''||yyzl||''','''||zkcl||''','''||cyz||''')';
　　sqlstr3 := 'select * from temp_tab3';
                 open my_ref_cursor for sqlstr3;
                 end; 
       end welldailyreportnew1;
end dailyreportnew1;