LOOP 中出现异常后怎么继续执行循环，执行下一条记录 ，
create or replace procedure amerce(quarterstart in varchar2,
quarterend in varchar2,
quarter in varchar2) is
V_m1 INTEGER;
v_vendor srm062.vendor%TYPE;
v_plant srm062.plant%TYPE;
v_acount_demif srm062.demif%TYPE; -- demif¼¾¶ÈºÏ¼Æ
v_o_demif srm061.demif%TYPE; --Ä¿±ê
-- v_o_ppm srm061.ppm%TYPE; --PPMÄ¿±ê
v_demifmoney srm065.demifmoney%TYPE;
v_Cdemifmoney srm065.demifmoney%TYPE;
v_startscope srm066.startscope%TYPE;
v_endscope srm066.endscope%TYPE;
v_menoy srm066.menoy%TYPE;
v_ppm srm065.Ppm%TYPE; --PPMºÏ¼Æ
cursor c_s061 is
select t.plant, t.vendor, sum(t.demif)
from srm062 t
where t.closedate > = TO_DATE(quarterstart, 'YYYY-MM-DD ')
and t.closedate <= TO_DATE(quarterend, 'YYYY-MM-DD ')
group by t.plant, t.vendor;
cursor c_s066 is
select s66.startscope, s66.endscope, s66.menoy from srm066 s66;
begin
OPEN C_S061;
LOOP
FETCH c_s061
into v_plant, v_vendor, v_acount_demif;
EXIT WHEN c_s061%NOTFOUND;
--Ä¿±êÖµ
select s.demif --, s.ppm
into v_o_demif --, v_o_ppm
from srm061 s
where s.plant = v_plant
and s.vendor = v_vendor
and s.objectyear = substr(quarterstart, 0, 4);
select sum(t63.returnamount) * 1000000 / sum(t63.amount)
INTO v_ppm
from srm063 t63
where TO_DATE(t63.rbdate, 'YYYY-MM-DD ') > = TO_DATE(quarterstart, 'YYYY-MM-DD ')
and TO_DATE(t63.rbdate, 'YYYY-MM-DD ') <= TO_DATE(quarterend, 'YYYY-MM-DD ')
and t63.plant = v_plant
and t63.vendor = v_vendor
group by t63.plant, t63.vendor;
OPEN C_S066;
LOOP
fetch c_s066
into v_startscope, v_endscope, v_menoy;
EXIT WHEN c_s066%NOTFOUND;
IF v_startscope <= v_acount_demif AND v_endscope > = v_acount_demif THEN
v_Cdemifmoney := v_acount_demif * v_menoy;
END IF;
END LOOP;
INSERT into srm065
(YEAR,
MONTH,
VENDOR,
PLANT,
DEMIFYEAR,
DEMIF,
DEMIFMONEY,
PPM,
DATADATE
)
values
(substr(quarterstart, 0, 4),
quarter,
v_vendor,
v_plant,
v_o_demif,
v_acount_demif,
v_Cdemifmoney,
v_ppm,
sysdate);
CLOSE c_s066;
DBMS_OUTPUT.put_line(v_Cdemifmoney);
COMMIT;
END LOOP;
CLOSE c_s061;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
DBMS_OUTPUT.put_line( 'procedur amerce error ');
NULL;
-- Consider logging the error and then re-raise
RAISE;
end amerce;
/
[解决办法]
原本我试过goto 不能回到正常逻辑，不过楼上大哥说得我心虚了，现试验如下：
1、在正常逻辑中goto可以使用。注意：下面的语句是死循环，一定要在调试器里面单步执行。
CREATE OR REPLACE PROCEDURE TEST
IS
tmpvar NUMBER;
BEGIN
tmpvar := 0;
< <a> >
SELECT NULL
INTO tmpvar
FROM dual;
DBMS_OUTPUT.put_line ( 'i can goto ');
GOTO a;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
RAISE;
-- Consider logging the error and then re-raise
END TEST;
/
2、在异常中无法回到正常逻辑，试图回来的话会返回PLS-00375: 非法 GOTO 语句; 此处的 GOTO 无法转移至标记 'A '。
CREATE OR REPLACE PROCEDURE TEST
IS
tmpvar NUMBER;
BEGIN
tmpvar := 0;
< <a> >
SELECT NULL
INTO tmpvar
FROM emp;
DBMS_OUTPUT.put_line ( 'i can goto ');
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
GOTO a;
-- Consider logging the error and then re-raise
END TEST;
/
[解决办法]
汗 ..果然存在
CREATE OR REPLACE PROCEDURE TEST
IS
tmpvar NUMBER;
BEGIN
tmpvar := 0;
< <a> >
BEGIN
SELECT 1
INTO tmpvar
FROM dual
WHERE 1=2;
DBMS_OUTPUT.put_line ( 'i can goto ');
EXCEPTION
WHEN OTHERS
THEN GOTO a;
END ;
-- Consider logging the error and then re-raise
END TEST;
这个呢?
[解决办法]
不早了啊..
有兴趣加我QQ 122252261.
我试验了一下 大约可以 ..
不过 小心是死循环啊
[解决办法]
这个很简单啊，只要在循环里捕获一下异常就可以了啊
create or replace procedure temp as
...
begin
for v in ... loop
begin
insert into table1...;
insert into table2...;
update table3...;
commit;
exception when others then
rollback;
dbms_output.put_line(errmsg);
end;
end loop;
exception when others then
...
end temp;
[解决办法]
上面不是很清楚了嘛：
OPEN C_S061;
LOOP
FETCH c_s061
into v_plant, v_vendor, v_acount_demif;
EXIT WHEN c_s061%NOTFOUND;
--Ä¿±êÖµ
begin
select s.demif --, s.ppm
into v_o_demif --, v_o_ppm
from srm061 s
where s.plant = v_plant
exception when others then
--输出异常信息
dbms_output.put_line(sqlerrm);
end;
....
就是说把异常捕获一下，就不会退出了，你要在循环里捕获异常只需要在循环里加个begin ...
exception when others then ...end;就可以了。
