ORACLE游标与异常处理

过程示例

create or replace procedure PROC_TASK_QUERY
is
       g_name          task_company_stock_list.g_name%type;
       code_t          task_company_stock_list.code_t%type;
       firm_code       task_company_stock_list.firm_code%type;
       firm_name       task_company_stock_list.firm_name%type;
       unit_name       task_company_stock_list.unit_name%type;
       in_qty          task_company_stock_list.in_qty%type;
       out_qty         task_company_stock_list.out_qty%type;

       id_d              task_company_stock_detail.id%type;
       eml_id_d          task_company_stock_detail.eml_id%type;
       busi_type_d       task_company_stock_detail.busi_type%type;
       refer_doc_d       task_company_stock_detail.refer_doc%type;
       pass_gate_time_d  task_company_stock_detail.pass_gate_time%type;
       trade_name_d      task_company_stock_detail.trade_name%type;
       name_d            task_company_stock_detail.name%type;
       move_name_d       task_company_stock_detail.move_name%type;
       g_qty_d           task_company_stock_detail.g_qty%type;
       g_name_d          task_company_stock_detail.g_name%type;
       unit_name_d       task_company_stock_detail.unit_name%type;
       code_t_d          task_company_stock_detail.code_t%type;
       g_no_d            task_company_stock_detail.g_no%type;

       sqlstr     long;
       moduleno  varchar2(100);
       userid    varchar2(100);
       queryp    varchar2(10);

       insqlmain  long;
       insqldetail long;
       insqldetail2 long;
       insqldetail3 long;

       startposition number(10);
       len number(10);
       type sql_array is table of task_schedule_query.sqlstr%type index by binary_integer;
       sqlarray sql_array;
       lpindex number(10);
       delimiter varchar2(10);

       type cur is ref cursor ;
       sqlcur cur;
       incur cur;
       indetailcur cur;
begin

   insqlmain := '';
   insqldetail :='';
   insqldetail2 :='';
   insqldetail3 :='';
     
   sqlarray(1):='';
   sqlarray(2):='';
   sqlarray(3):='';
   sqlarray(4):='';
   
   open sqlcur for select user_id,moduleno,sqlstr,query_param from task_schedule_query where status=1;
   loop
     fetch sqlcur into userid,moduleno,sqlstr,queryp;
     exit when sqlcur%notfound;

     --构造查询字符串数组开始
     delimiter:='~';
     startposition:=1;
     lpindex:= 1;
      loop
        select instr(sqlstr,delimiter,startposition ) into len from dual;
        --dbms_output.put_line(startposition);
        --dbms_output.put_line(len);
        if len!=0 then
           select substr(sqlstr,startposition,len-startposition) into sqlarray(lpindex) from dual;
        else
          select substr(sqlstr,startposition) into sqlarray(lpindex) from dual;
          --dbms_output.put_line(sqlarray(lpindex));
          exit;
          end if;
          --dbms_output.put_line(sqlarray(lpindex));
          startposition:=len+1;
         lpindex:=lpindex+1;
      end loop;
     /*
     for i in 1..sqlarray.count
     loop
       dbms_output.put_line(sqlarray(i));
     end loop;
     */

     --构造查询字符串数组结束
     
     insqlmain := sqlarray(1);
     insqldetail:=sqlarray(2);
     insqldetail2 :=sqlarray(3);
     insqldetail3 :=sqlarray(4);

     --分模块执行：企业进出仓库存查询开始
     IF(moduleno='compStockSearch') THEN
        BEGIN       
          update task_schedule_query set status=2,start_time=sysdate where user_id=userid and moduleno=moduleno;                              
          IF(queryp='1' OR queryp='2') THEN                                     
             open incur for insqlmain;
             loop
               fetch incur into g_name,code_t,firm_code,firm_name,unit_name,in_qty,out_qty;
               exit when incur%notfound;
               insert into task_company_stock_list(g_name,code_t,firm_code,firm_name,unit_name,in_qty,out_qty,user_id)
               values(g_name,code_t,firm_code,firm_name,unit_name,in_qty,out_qty,userid);
             end loop;
             close incur;

             DBMS_OUTPUT.put_line('sqlmain is :'||insqlmain);
           END IF;         
        
           IF(queryp='2') THEN
             open indetailcur for insqldetail;
             loop
               fetch indetailcur into id_d,eml_id_d,busi_type_d,refer_doc_d,pass_gate_time_d,trade_name_d,name_d,move_name_d,g_qty_d,g_name_d,unit_name_d,code_t_d,g_no_d;
               exit when indetailcur%notfound;
               insert into task_company_stock_detail(id,eml_id,busi_type,refer_doc,pass_gate_time,trade_name,name,move_name,g_qty,g_name,unit_name,code_t,g_no,user_id)
               values(id_d,eml_id_d,busi_type_d,refer_doc_d,pass_gate_time_d,trade_name_d,name_d,move_name_d,g_qty_d,g_name_d,unit_name_d,code_t_d,g_no_d,userid);
             end loop;
             close indetailcur;

             DBMS_OUTPUT.put_line('sqldetail is :'||insqldetail);
           END IF;
         
           update task_schedule_query set status=3,end_time=sysdate where user_id=userid and moduleno=moduleno;
        --分模块的异常处理，不影响下一个模块的处理   
        EXCEPTION
           WHEN OTHERS THEN 
           update task_schedule_query set status=4 where user_id=userid and moduleno=moduleno;
        END; 
     END IF;
    --分模块执行：企业进出仓库存查询结束
   
 
   
   end loop;
   close sqlcur;
   
     
   commit;
   EXCEPTION
   WHEN OTHERS THEN 
     NULL;


end;

 

游标的多种循环

create or replace procedure Test
is
    
    cursor mycusor is 
      select eml_id,owner_code from store_in_head where rownum<10 order by eml_id ;
     myrecord mycusor%rowtype;
begin  
    --显示游标循环
    open mycusor;
    loop
      fetch mycusor into myrecord.eml_id,myrecord.owner_code;
      exit when mycusor%notfound;
      DBMS_OUTPUT.put_line(myrecord.eml_id||','||myrecord.owner_code);
    end loop;
    close mycusor;
    
    --取游标中多少行
   open mycusor;
   for i in 1..3
   loop
     fetch mycusor into myrecord.eml_id,myrecord.owner_code;
       DBMS_OUTPUT.put_line(myrecord.eml_id||','||myrecord.owner_code);
   end loop;
   close mycusor;
   
   --特殊for循环
     open mycusor;
     for mycurecord in mycusor loop
        DBMS_OUTPUT.put_line(mycurecord.eml_id||','||mycurecord.owner_code);
     end loop;
    close mycusor;
    
     --特殊for循环
   for myrec in (select eml_id,owner_code from store_in_head where rownum<10 order by eml_id ) loop
       DBMS_OUTPUT.put_line(myrec.eml_id||','||myrec.owner_code);
   end loop;
end;

 

带参数的游标：

create or replace procedure Test
is
    
    
     cursor mycusor(p_firm_code varchar2) is 
     select id from store_in_list where firm_code=p_firm_code and  rownum<10 order by eml_id ;
     myrecord mycusor%rowtype;
begin  
    --显示游标循环
    open mycusor('4403447904');
    loop
      fetch mycusor into myrecord.id;
      exit when mycusor%notfound;
      DBMS_OUTPUT.put_line(myrecord.id);
    end loop;
    close mycusor;
   
end;