PL/SQL 批量操作

-- Start

在 PL/SQL 中执行 SQL 语句的时候，PL/SQL 引擎将参数传递给 SQL 引擎，然后接收返回值。通常是传递一个参数，接收一个返回值，如果有很多这样的操作，我们不得不把它放到一个循环语句中，如下是一个简单的例子。

DECLARE  TYPE NumList IS VARRAY(20) OF NUMBER;  emps NumList := NumList(10, 30, 70);BEGIN  FOR i IN emps.FIRST..emps.LAST LOOP    DELETE FROM employees WHERE employee_id = emps(i);  END LOOP;END;

那有没有一种办法批量传递参数和批量接收返回值呢？答案是肯定的，下面是一个简单的例子。

DECLARE  TYPE NumList IS VARRAY(20) OF NUMBER;  emps NumList := NumList(10, 30, 70);BEGIN  -- FORALL 语句批量执行下面的语句  FORALL i IN emps.FIRST..emps.LAST    DELETE FROM employees WHERE employee_id = emps(i);END;

理想很丰满，现实很骨感，如果在批量执行的过程中出异常了，Oracle 该如何处理？Oracle 会中断执行，回滚所有操作。如果我们想让它中断执行，提交已经做的更改，该怎么办呢？看看下面的例子吧。

DECLARE  TYPE NumList IS VARRAY(20) OF NUMBER;  emps NumList := NumList(10, 30, 70);BEGIN  FORALL i IN emps.FIRST..emps.LAST    update employees set last_name = last_name || 'value too large exception' WHERE employee_id = emps(i);  EXCEPTION    WHEN OTHERS THEN    COMMIT;END;

如果我们想让它忽略异常继续执行，该怎么做呢？看看下面的例子吧。

DECLARE  TYPE NumList IS VARRAY(20) OF NUMBER;  emps NumList := NumList(10, 30, 70);    -- 批量操作失败后会抛出 24381 错误  bulk_errors EXCEPTION;  PRAGMA EXCEPTION_INIT(bulk_errors, -24381);BEGIN  -- SAVE EXCEPTIONS 表示保存异常继续执行  FORALL i IN emps.FIRST..emps.LAST SAVE EXCEPTIONS    update employees set last_name = last_name || 'Values too long' WHERE employee_id = emps(i);  EXCEPTION    WHEN bulk_errors THEN      -- SQL%BULK_EXCEPTIONS.COUNT 表示发生错误的条数      FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP        DBMS_OUTPUT.PUT_LINE ('执行第' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX         || '条语句发生了错误，错误代码：' || SQL%BULK_EXCEPTIONS(i).ERROR_CODE        || ', 错误信息：' || SQLERRM(-(SQL%BULK_EXCEPTIONS(i).ERROR_CODE)));      END LOOP;      COMMIT;    WHEN OTHERS THEN      DBMS_OUTPUT.PUT_LINE('未知异常.');      ROLLBACK;      RAISE;        --　显示删除结果  FOR i IN emps.FIRST..emps.LAST LOOP    DBMS_OUTPUT.PUT_LINE ('第' || i || '条语句更新了' || SQL%BULK_ROWCOUNT(i) || '行');  END LOOP;  DBMS_OUTPUT.PUT_LINE ('总共更新了' || SQL%ROWCOUNT || '行');END;

下面我们看一个如何批量接收返回值的例子。

DECLARE  TYPE ID_TABLE IS TABLE OF EMPLOYEES.EMPLOYEE_ID%TYPE;  TYPE NAME_TABLE IS TABLE OF EMPLOYEES.LAST_NAME%TYPE;  ids ID_TABLE;  names NAME_TABLE;  BEGIN  SELECT EMPLOYEE_ID, LAST_NAME  BULK COLLECT INTO ids, names -- 批量返回数据  FROM EMPLOYEES  ORDER BY EMPLOYEE_ID;  FOR i IN ids.FIRST()..names.LAST() LOOP    DBMS_OUTPUT.PUT_LINE('EMPLOYEE #' || ids(i) || ': ' || names(i));  END LOOP;END;

下面的例子将返回值放到一个记录中。

DECLARE  CURSOR c1 IS SELECT EMPLOYEE_ID, LAST_NAME FROM EMPLOYEES;  TYPE ID_NAME_TABLE IS TABLE OF c1%ROWTYPE;  emps ID_NAME_TABLE;  BEGIN  SELECT EMPLOYEE_ID, LAST_NAME  BULK COLLECT INTO emps -- 批量返回数据  FROM EMPLOYEES  ORDER BY EMPLOYEE_ID;  FOR i IN emps.FIRST()..emps.LAST() LOOP    DBMS_OUTPUT.PUT_LINE('EMPLOYEE #' || emps(i).EMPLOYEE_ID || ':' || emps(i).LAST_NAME);  END LOOP;END;

下面的例子游标中批量接收返回值

DECLARE  CURSOR c1 IS SELECT EMPLOYEE_ID, LAST_NAME FROM EMPLOYEES;  TYPE ID_NAME_TABLE IS TABLE OF c1%ROWTYPE;  emps ID_NAME_TABLE;  BEGIN  OPEN c1;    LOOP    -- 每次取10条    FETCH c1 BULK COLLECT INTO emps LIMIT 10;        FOR i IN emps.FIRST()..emps.LAST() LOOP        DBMS_OUTPUT.PUT_LINE('EMPLOYEE #' || emps(i).EMPLOYEE_ID || ':' || emps(i).LAST_NAME);      END LOOP;        EXIT WHEN c1%NOTFOUND;  END LOOP;    CLOSE c1;END;

下面的例子从删除语句中获取返回值。

DECLARE  CURSOR c1 IS SELECT EMPLOYEE_ID, LAST_NAME FROM EMPLOYEES;  TYPE ID_NAME_TABLE IS TABLE OF c1%ROWTYPE;  emps ID_NAME_TABLE;  BEGIN  DELETE FROM EMPLOYEES  RETURNING EMPLOYEE_ID, LAST_NAME  BULK COLLECT INTO emps; -- 批量返回数据  FOR i IN emps.FIRST()..emps.LAST() LOOP    DBMS_OUTPUT.PUT_LINE('EMPLOYEE #' || emps(i).EMPLOYEE_ID || ':' || emps(i).LAST_NAME);  END LOOP;END;

--更多参见：Oracle PL/SQL 精萃

-- 声明：转载请注明出处

-- Last Edited on 2015-06-30

-- Created by ShangBo on 2015-04-19

-- End