1、捕捉异常后继续下一次循环需用

EXCEPTION

when others then  null;

这样的结构。

2、在for ...LOOP   ENDLOOP 循环中捕捉异常，必须用begin   end包起来，否则会报错。在PLSQL中报

PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the following

Error: PLS-00103: Encountered the symbol "END" when expecting one of the following

正确

FOR E IN TP LOOP

begin

......................

EXCEPTION

when others then  null;

end;

endloop;

在异常中有时候可能需要回滚数据  和 更新数据状态标识，那么直接使用ROLLBACK 会回滚所有的事务，中间表的数据也不存在，原表数据标识状态位

是原表和中间表联合关联查询，这时就需要设置回滚事务点。

CREATE OR REPLACE PROCEDURE sp_Empi_test20191203

AS

PARM_count int;

begin

begin

--开始事务

--SAVEPOINT A;

update GR_JBXX_BAK a set a.SCZT='A' where yljgdm = '320903PDY604999' and grjbxxbsh='320911195205124062' and xgbz='1';

update GR_JBXX_BAK a set a.SCZT='A' where yljgdm = '320903PDY604999' and grjbxxbsh='32091119520125402X' and xgbz='1';

-SAVEPOINT B;

update GR_JBXX_BAK a set a.SCZT='A' where yljgdm = '320903PDY605027' and grjbxxbsh='320911194601254323' and xgbz='1';

select 1/0 into PARM_count from dual;

EXCEPTION

WHEN OTHERS THEN

begin

ROLLBACK TO A;

DBMS_OUTPUT.put_line(SUBSTR(SQLERRM, 1, 4000));

update GR_JBXX_BAK a set a.SCZT='A' where yljgdm = '320903PDY605027' and grjbxxbsh='32090340002000320175716477961' and xgbz='1';

commit;

null;

end;

END;

select 1 into PARM_count from dual;

DBMS_OUTPUT.put_line(PARM_count);

end sp_Empi_test20191203;

可以设置回滚到事务节点A    和事务节点B，方便我们使用中间表数据。