ORACLE游标与异常处理

须6963

　　过程示例

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;