oracle 存储过程返回游标

--过程部分
      procedure   get_Fpqk(diqu varchar2, jcdxmc varchar2,bcldwmc varchar2,kssj varchar2 ,jssj varchar2 ,beginRow number, endRow  number,  sumCount out number,cur   out   rc__type_fpjl)   is
      begin
                    --使用前先清空
         execute immediate 'truncate table  BIZ_Test';
          
           --默认SQL
           sqlValue:='select *from ( select diqu ,jcdxmc , bcldwmc  , BCLDWBH,
          nvl(sum(decode(fpbz,1,1,0)),0) as blue  ,nvl(sum(decode(fpbz,2,1,0)),0)  as yellow  ,nvl(sum(decode(fpbz,3,1,0)),0) as red
          from biz_fpjl [0]  group by diqu, bcldwmc ,bcldwbh ,jcdxmc    order by  jcdxmc )' ;


          whereValue:=' where  1=1 ';
        
        
          
             --判断是否存在时间,总数，未处理，有效牌数均可通过时间查询
              if   kssj is not null    then
                  whereValue:=whereValue||' and fpsj  >=to_date('''||kssj||''',''yyyy-mm-dd hh24:mi:ss'')'; 
              end if ;
           
              --结束时间 总数，未处理，有效牌数均可通过时间查询
             if   jssj is not  null    then
                  whereValue:=whereValue||' and fpsj  <=to_date('''||jssj||''',''yyyy-mm-dd hh24:mi:ss'')+1';    
             end if ;
  
             if   diqu is not  null    then
                 begin 
                  whereValue:=whereValue||' and diqu   = '''||diqu||'''';   
                end ;
             end if ;
            
                if   jcdxmc  is not  null    then
               begin 
                  whereValue:=whereValue||' and jcdxmc   = '''||jcdxmc||'''';   
                end ;
             end if ;
            
               if   bcldwmc  is not  null    then
               begin 
                  whereValue:=whereValue||' and bcldwmc   like  ''%'||bcldwmc||'%''';   
               
                end ;
             end if ;
            
                dbms_output.put_line('wherevalue'||whereValue||' and (fpbz=1 or fpbz =2 ) and clfs =''自动''');
        
          sqlValue1:=replace(sqlValue,'[0]',whereValue);--默认初始化
          --取红黄牌
          sqlValue2:=replace(sqlValue,'[0]',whereValue||' and (fpbz=1 or fpbz =2 ) and clfs =''自动''');
         
          --取有效拍
          sqlValue3:=replace(sqlValue,'[0]',whereValue ||' and zhuantai =0');
            
                      
       
                dbms_output.put_line('sql1'||sqlValue1);
                dbms_output.put_line('sql2'||sqlValue2);
                dbms_output.put_line('sql3'||sqlValue3);
          --初始化地区名称等信息 
         
          --执行监察信息数据初始化，初始产生 监察地区，类别，名称， 监察总数
           execute immediate ' insert into  BIZ_Test(diqu ,jcdxmc, bcldwmc,BCLDWBH,jc_yjps ,jc_jcps,jc_cfps)'||sqlValue1;
          
            dbms_output.put_line(
            ' 1 update BIZ_Test a set  ( a.diqu,a.jcdxmc ,a.bcldwmc,a.bcldwbh,a.wcl_YJPS , a.wcl_jcps,a.wcl_cfps) = ('||sqlValue2||'  b where    a.bcldwbh =b.bcldwbh  and rownum=1)
              where exists (select 1 from ('||sqlValue2||') b where    a.bcldwbh =b.bcldwbh  and rownum=1 )' ) ;
              dbms_output.put_line('插入基础信息成功 '); 
              dbms_output.put_line('未处理牌数更新成功 ');
            --更新 未处理牌数
             execute immediate 
             'update BIZ_Test a set  ( a.diqu,a.jcdxmc ,a.bcldwmc,a.bcldwbh,a.wcl_YJPS , a.wcl_jcps,a.wcl_cfps) = ('||sqlValue2||'  b where    a.bcldwbh =b.bcldwbh and rownum=1 )
              where exists (select 1 from ('||sqlValue2||') b where    a.bcldwbh =b.bcldwbh and rownum=1)'  ;
              dbms_output.put_line('未处理牌数更新成功 ');     
            
                  --更新 未处理牌数
            dbms_output.put_line(
             ' 2 update BIZ_Test a set  ( a.diqu,a.jcdxmc ,a.bcldwmc,a.bcldwbh,a.yx_YJPS , a.yx_jcps,a.yx_cfps) = ('||sqlValue3||'b  where    a.bcldwbh =b.bcldwbh and rownum=1  )
                  where exists (select 1 from ('||sqlValue3||') b where    a.bcldwmc =b.bcldwmc  and rownum=1)' );
            
            --更新有效牌数
              execute immediate 
              ' update BIZ_Test a set  ( a.diqu,a.jcdxmc ,a.bcldwmc,a.bcldwbh,a.yx_YJPS , a.yx_jcps,a.yx_cfps) = ('||sqlValue3||'b  where    a.bcldwbh =b.bcldwbh  and rownum=1)
                  where exists (select 1 from ('||sqlValue3||') b where    a.bcldwbh =b.bcldwbh and rownum=1 )'  ; 
               dbms_output.put_line('有效牌数 ');                      
            commit;
          
          execute immediate  'select count(1) from biz_test' into  sumCount;
             dbms_output.put_line( sumCount);
           
          --打开游标
           open   cur   for    'select * from (select a.* ,rownum row_num  from  biz_test a where rownum <= '||endRow||' ) temp where row_num >'||beginRow;
           --结束方法
       END get_Fpqk;