请举一个最简单的例子：写一个用游标返回记录集的存贮过程。注：不用包实现，行吗？

Question

请举一个最简单的例子：写一个用游标返回记录集的存贮过程。注：不用包实现，行吗？即：不用package。能不能只用procdure实现该功能？

Answer 1

create or replace procedure P_GET_ATMP_YEWUBB(
STARTDATE in varchar2, 
ENDDATE in varchar2,
SUBORG in varchar,
ORGCODE in varchar,
v_Cur out PANSKY_PACKAGE.outlist) 
is
-------------------------------得到参数
--v_Date      varchar2(8);
v_Random    varchar2(20);
-------------------------------得到生成TMP表游标
--cursor cur01 () is
begin     --为防止并发请求覆盖数据，生成随机数赋值给v_Random
     select dbms_random.string('a',20) into v_Random from dual;
  begin 
     if orgcode <> '8020' then 
        insert into TMP_ATMP_YEWUBB
        select 1,bankno,atmnum,
       sum (qukuanbs) aa1,sum(qukuan) aa2,sum(zhuanzhangbs) aa3 ,sum(zhuanzhang) aa4,
       sum(chunkuanbs) aa5,sum(chunkuan) aa6,sum(to_number(chaxun)) aa7,sum(to_number(gaimi)) aa8,
       sum(to_number(hjjine)) aa9,sum(hjbisshu) aa10
from base_atmp_yewubb
where gendate between STARTDATE and ENDDATE
      and  SUBORG like '%'||F_GET_ORGUNIT(lvbnk2)||'%'
group by bankno,atmnum
union
select 2,'管理网点汇总','',0,0,0,0,0,0,0,0,0,0
from base_atmp_yewubb
union
select 3,bankno,'',aa1,aa2,aa3,aa4,aa5,aa6,aa7,aa8,aa9,aa10
from (select bankno ,
             sum (qukuanbs) as aa1,sum(qukuan) as aa2,sum(zhuanzhangbs) as aa3,sum(zhuanzhang) as aa4,
             sum(chunkuanbs) as aa5,sum(chunkuan) as aa6,sum(to_number(chaxun)) as aa7,sum(to_number(gaimi)) as aa8,
             sum(to_number(hjjine)) as aa9,sum(hjbisshu) as aa10
      from base_atmp_yewubb
      where gendate between STARTDATE and ENDDATE
                  and SUBORG like '%'||F_GET_ORGUNIT(lvbnk2)||'%'
      group by bankno)
union
select 4,'管辖分行汇总','',0,0,0,0,0,0,0,0,0,0
from base_atmp_yewubb
union
select 5,lvbnk2,'',aa1,aa2,aa3,aa4,aa5,aa6,aa7,aa8,aa9,aa10
from (select lvbnk2 ,
             sum (qukuanbs) aa1,sum(qukuan) aa2,sum(zhuanzhangbs) aa3,sum(zhuanzhang) aa4,
             sum(chunkuanbs) aa5,sum(chunkuan) aa6,sum(to_number(chaxun)) aa7,sum(to_number(gaimi)) aa8,
             sum(to_number(hjjine)) aa9,sum(hjbisshu) aa10
      from base_atmp_yewubb
      where gendate between STARTDATE and ENDDATE
            and SUBORG like '%'||F_GET_ORGUNIT(lvbnk2)||'%'
      group by lvbnk2);
------------------------------------------------------------以上为不为区行汇总的      
      elsif orgcode = '8020' then 
------------------------------------------------------------一下为区行汇总的      
      insert into TMP_ATMP_YEWUBB
select 1,bankno,atmnum,
       sum (qukuanbs) aa1,sum(qukuan) aa2,sum(zhuanzhangbs) aa3 ,sum(zhuanzhang) aa4,
       sum(chunkuanbs) aa5,sum(chunkuan) aa6,sum(to_number(chaxun)) aa7,sum(to_number(gaimi)) aa8,
       sum(to_number(hjjine)) aa9,sum(hjbisshu) aa10
from base_atmp_yewubb
where gendate between STARTDATE and ENDDATE
      and SUBORG like '%'||F_GET_ORGUNIT(lvbnk2)||'%'
group by bankno,atmnum
union
select 2,'管理网点汇总','',0,0,0,0,0,0,0,0,0,0
from base_atmp_yewubb
union
select 3,bankno,'',aa1,aa2,aa3,aa4,aa5,aa6,aa7,aa8,aa9,aa10
from (select bankno ,
             sum (qukuanbs) as aa1,sum(qukuan) as aa2,sum(zhuanzhangbs) as aa3,sum(zhuanzhang) as aa4,
             sum(chunkuanbs) as aa5,sum(chunkuan) as aa6,sum(to_number(chaxun)) as aa7,sum(to_number(gaimi)) as aa8,
             sum(to_number(hjjine)) as aa9,sum(hjbisshu) as aa10
      from base_atmp_yewubb
      where gendate between STARTDATE and ENDDATE
                  and SUBORG like '%'||F_GET_ORGUNIT(lvbnk2)||'%'
      group by bankno)
union
select 4,'管辖分行汇总','',0,0,0,0,0,0,0,0,0,0
from base_atmp_yewubb
union
select 5,lvbnk2,'',aa1,aa2,aa3,aa4,aa5,aa6,aa7,aa8,aa9,aa10
from (select lvbnk2 ,
             sum (qukuanbs) aa1,sum(qukuan) aa2,sum(zhuanzhangbs) aa3,sum(zhuanzhang) aa4,
             sum(chunkuanbs) aa5,sum(chunkuan) aa6,sum(to_number(chaxun)) aa7,sum(to_number(gaimi)) aa8,
             sum(to_number(hjjine)) aa9,sum(hjbisshu) aa10
      from base_atmp_yewubb
      where gendate between STARTDATE and ENDDATE
            and SUBORG like '%'||F_GET_ORGUNIT(lvbnk2)||'%'
      group by lvbnk2)
union
select 6,'全辖汇总','',0,0,0,0,0,0,0,0,0,0
from base_atmp_yewubb
union
select 7,'8810','' as ATMNUM,aa1,aa2,aa3,aa4,aa5,aa6,aa7,aa8,aa9,aa10
from (select 
             sum (qukuanbs) aa1,sum(qukuan) aa2,sum(zhuanzhangbs) aa3,sum(zhuanzhang) aa4,
             sum(chunkuanbs) aa5,sum(chunkuan) aa6,sum(to_number(chaxun)) aa7,sum(to_number(gaimi)) aa8,
             sum(to_number(hjjine)) aa9,sum(hjbisshu) aa10
      from base_atmp_yewubb
      where gendate between STARTDATE and ENDDATE);
      end if; 
   
        open v_Cur for select BANKNO,ATMNUM,AA1,AA2,AA3,AA4,AA5,AA6,AA7,AA8,AA9,AA10 from TMP_ATMP_YEWUBB order by NUM,BANKNO;
     
        delete  from TMP_ATMP_YEWUBB;
     commit;EXCEPTION
       WHEN OTHERS THEN
       ROLLBACK  ;    
  end;
end P_GET_ATMP_YEWUBB;

Answer 2

create or replace procedure zc.x_update_y is
type cur is ref cursor;
cc cur;
v_record zc.x%rowtype;
begin
  open cc for 'select * from zc.x'; loop
  fetch cc into v_record;
   exit when cc%notfound;
....
end loop;
end
这是简单得游标用法得部分代码