oracle动态的创建临时表
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;
