ForAll .. Save Exceptions

CREATE TABLE emp3 (deptno NUMBER(2), job VARCHAR2(15),sal number);
INSERT INTO emp3 VALUES(10, 'zhangsan',100);
INSERT INTO emp3 VALUES(10, 'lisi',500);
INSERT INTO emp3 VALUES(20, 'wangwu',2000);
INSERT INTO emp3 VALUES(30, 'liliu',3000);
INSERT INTO emp3 VALUES(30, 'mazi',5000);

 

DECLARE
  TYPE numlist IS TABLE OF NUMBER;
  num_tab numlist := numlist(10, 0, 11, 12, 30, 0, 20, 199, 2, 0, 9, 1);
  errors_num  NUMBER;
  e_dml_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_dml_errors, -24381);
 
BEGIN
  FORALL i IN num_tab.first .. num_tab.last SAVE EXCEPTIONS
    DELETE FROM emp3 WHERE sal > 50000 / num_tab(i);
   
EXCEPTION
  WHEN OTHERS THEN
    errors_num := SQL%bulk_exceptions.count;
    dbms_output.put_line('Number of errors is:' || errors_num);
   
    FOR i IN 1 .. errors_num LOOP
      dbms_output.put_line('-------------------------');
      dbms_output.put_line('Number of errors is:' || i || ' occurred during ' || 'iteration ' || SQL%BULK_EXCEPTIONS(i).error_index);
      dbms_output.put_line('Oracle error is:' || SQLERRM(-sql%BULK_EXCEPTIONS(i).error_code));
    END LOOP;
END;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25684327/viewspace-694163/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25684327/viewspace-694163/