1 2 3 下一页
(1)FORALL
下面的两个例子对比了FORALL与FOR循环之间的区别:
SQL> create table t_bulk as select * from employees; SQL> desc t_bulk; Name Type Nullable Default Comments -------------- ------------ -------- ------- -------- EMPLOYEE_ID NUMBER(6) Y FIRST_NAME VARCHAR2(20) Y LAST_NAME VARCHAR2(25) EMAIL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) Y HIRE_DATE DATE JOB_ID VARCHAR2(10) SALARY NUMBER(8,2) Y COMMISSION_PCT NUMBER(2,2) Y MANAGER_ID NUMBER(6) Y DEPARTMENT_ID NUMBER(4) Y DECLARE TYPE NUMList IS VARRAY(20) OF NUMBER; depts NumList := NumList(10, 30, 70); BEGIN FOR i IN depts.FIRST..depts.LAST LOOP DELETE FROM t_bulk WHERE department_id = depts(i); END LOOP; END; DECLARE TYPE NumList IS VARRAY(20) OF NUMBER; depts NumList := NumList(10, 30, 70); -- department numbers BEGIN FORALL i IN depts.FIRST..depts.LAST DELETE FROM t_bulk WHERE department_id = depts(i); END;
虽然从内部执行机制上来说,两个循环有很大的区别,但从语法上来说,还是非常类似的。不过有一个小细节需要注意,就是FORALL语句并没有对应的END语句。
我们再来看看使用FORALL的情况下对异常的处理:
CREATE TABLE t_bulk2(f1 NUMBER(3)); DECLARE TYPE type1 IS TABLE OF NUMBER; v type1:=type1(1, 2, 3000, 4, 5, 6, 77777, 8, 9, 10001); BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE t_bulk2'; -- FORALL idx IN v.FIRST..v.LAST INSERT INTO t_bulk2 VALUES(v(idx)); -- EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); END; ORA-01438: 值大于为此列指定的允许精度 PL/SQL procedure successfully completed SQL> SELECT * FROM t_bulk2; F1 ----
Oracle 9i中引入了SAVE EXCEPTIONS语法及与之对应的“ORA-24381: error(s) in array DML”异常,使用它们,我们可以跳过FORALL中出现异常的语句,并将异常保存在SQL%BULK_EXCEPTIONSP这个集合中:
DECLARE TYPE type1 IS TABLE OF NUMBER; v type1:=type1(1, 2, 3000, 4, 5, 6, 77777, 8, 9, 10001); -- BULK_ERROR EXCEPTION; PRAGMA EXCEPTION_INIT(BULK_ERROR, -24381); BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE t_bulk2'; -- FORALL idx IN v.FIRST..v.LAST SAVE EXCEPTIONS INSERT INTO t_bulk2 VALUES(v(idx)); -- EXCEPTION WHEN BULK_ERROR THEN FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP DBMS_OUTPUT.PUT_LINE(SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE)||', Statement: #'||SQL%BULK_EXCEPTIONS(i).ERROR_INDEX); END LOOP; WHEN OTHERS THEN NULL; END; ORA-01438: 值大于为此列指定的允许精度, Statement: #3 ORA-01438: 值大于为此列指定的允许精度, Statement: #7 ORA-01438: 值大于为此列指定的允许精度, Statement: #10 PL/SQL procedure successfully completed SQL> SELECT * FROM t_bulk2; F1 ----
(注意使用ERROR_CODE时要加上负号。)
下面介绍如何获取第一条语句所影响的行数,这需要使用SQL%BULK_ROWCOUNT:
上一篇: Oracle 11g系列—8—SQL语句编写规范
下一篇: 找到Oracle数据库中性能最差的查询语句
1 2 3 下一页