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);