带返回参数为游标的存储过程（运用嵌套表）

create or replace type PostType as Object(
   total varchar2(20),
   nowdate varchar2(200)
);


create or replace type PostTypeTable is table of PostType;


create or replace package WellCorss is

   type ref_cursor is ref cursor;  --建立带返回的游标 
 

  procedure GetTongBiByConds(orgid in varchar2,startTime in varchar2,endTime in varchar2,my_ref_cursor out WellCorss.ref_cursor);
 
end WellCorss;


create or replace package body WellCorss is

procedure GetTongBiByConds(orgid in varchar2,startTime in varchar2,endTime in varchar2,my_ref_cursor out WellCorss.ref_cursor)
is
usertotal number; --用户得到和所有
datenow varchar2(20); ---时间
v_PostTypeTable PostTypeTable;----建立表数据
begin
declare
  Cursor alldate is
    Select Rownum,(to_date(startTime,'yyyy-mm-dd')+Rownum-1) times
    from dual connect by rownum< to_date(endTime,'yyyy-mm-dd')-to_date(startTime,'yyyy-mm-dd') +1;--查询开始时间和结束时间之间所有时间
    curdate alldate%rowtype;---定义游标变量
     begin
      open alldate;--打开游标
        loop
           begin
             v_PostTypeTable:=PostTypeTable(); --初始化嵌套表         
           fetch alldate into curdate; --循环所有的时间
             exit when alldate%notfound;           
             dbms_output.put_line(to_char(curdate.times,'yyyy-mm-dd'));--行 
             -----------查询用油信息                                
               select nvl(sum(x.oiltotal+y.otherstotal),0) as total,
             x.nowtime as nowdate into usertotal,datenow
             from
                (select
                  nvl(sum(toi.scyyl1+toi.scyyl2),0) as oiltotal ,
                  to_char(curdate.times,'yyyy-mm-dd') as nowtime
                   from
                   tab_oil_info toi
                   where toi.org_id=orgid and toi.acq_date=to_char(curdate.times,'yyyy-mm-dd'))x,
                (select
                  nvl(sum(tsu.tsyyl),0) as otherstotal
                  from
                  tab_spec_use tsu
                  where tsu.org_id=orgid
                  and tsu.acq_date =to_char(curdate.times,'yyyy-mm-dd'))y group by x.oiltotal,x.nowtime;               
                 --for i in 1..curdate.Rownum loop
                   v_PostTypeTable.Extend;                 
                   v_PostTypeTable(v_PostTypeTable.Count):=PostType(usertotal,datenow);
                   v_PostTypeTable.Extend;
                   dbms_output.put_line(usertotal||datenow); 
                --end loop;      
               end;             
                open my_ref_cursor for select * from table(cast (v_PostTypeTable as PostTypeTable));
         end loop;
    
         -- close alldate;--关闭游标
     end;
end;
end WellCorss;