CREATE OR REPLACE PROCEDURE PROC_AS_MEDU_GEN_REPENTRY( ln_rep_no in number, ln_ret out number ) IS lv_scope varchar2(20); lc_cell_code char(4); cursor cur_as_code_medu is select scope,cell_code from as_code_medu; v_errornumber number; v_errortext varchar2(200); BEGIN open cur_as_code_medu; loop fetch cur_as_code_medu into lv_scope,lc_cell_code; exit when cur_as_code_medu%NOTFOUND;
insert into as_medu_repentry(rep_no,scope,cell_code) values(ln_rep_no,lv_scope,lc_cell_code); end loop; insert into as_tinc_repentry(rep_no) values(ln_rep_no); close cur_as_code_medu; commit; exception //异常处理 when others then ln_ret := -1; v_errornumber := sqlcode; v_errortext := substr(sqlerrm,1,200); END;
用goto也可以,直接跳到最后面DECLARE v_Counter BINARY_INTEGER := 1; BEGIN LOOP INSERT INTO temp_table VALUES (v_Counter, 'Loop count'); v_Counter := v_Counter + 1; IF v_Counter > 50 THEN GOTO l_EndOfLoop; END IF; END LOOP; <<l_EndOfLoop>> END; /
我觉得是可以直接用return 的
return最好解决方法:SQL> set serveroutput on SQL> SQL> begin 2 for i in 1..10 loop 3 if i=4 then 4 return; 5 end if; 6 dbms_output.put_line(i); 7 end loop; 8 end; 9 / 1 2 3PL/SQL procedure successfully completed
ln_rep_no in number,
ln_ret out number
)
IS
lv_scope varchar2(20);
lc_cell_code char(4);
cursor cur_as_code_medu is
select scope,cell_code
from as_code_medu;
v_errornumber number;
v_errortext varchar2(200);
BEGIN
open cur_as_code_medu;
loop
fetch cur_as_code_medu into lv_scope,lc_cell_code;
exit when cur_as_code_medu%NOTFOUND;
insert into as_medu_repentry(rep_no,scope,cell_code)
values(ln_rep_no,lv_scope,lc_cell_code);
end loop;
insert into as_tinc_repentry(rep_no)
values(ln_rep_no);
close cur_as_code_medu;
commit;
exception //异常处理
when others then
ln_ret := -1;
v_errornumber := sqlcode;
v_errortext := substr(sqlerrm,1,200);
END;
v_Counter BINARY_INTEGER := 1;
BEGIN
LOOP
INSERT INTO temp_table
VALUES (v_Counter, 'Loop count');
v_Counter := v_Counter + 1;
IF v_Counter > 50 THEN
GOTO l_EndOfLoop;
END IF;
END LOOP; <<l_EndOfLoop>>
END;
/
SQL>
SQL> begin
2 for i in 1..10 loop
3 if i=4 then
4 return;
5 end if;
6 dbms_output.put_line(i);
7 end loop;
8 end;
9 /
1
2
3PL/SQL procedure successfully completed
如果实存储过程为return.