ORACLE FORALL介绍

ORACLE 10G OFFICIAL DOCUMNET 

-------------------------------------------------------------------------

一 介绍：

1、语法

for all statement ::=

bounds_clause ::=

2、关键字与参数介绍：

==index_name：一个无需声明的标识符，作为集合下标使用；

==sql_statement：静态语句，例如：UPDATE或者DELETE；或者动态（EXECUTE IMMEDIATE）DML语句。

==SAVE EXCEPTIONS：可选关键字，表示即使一些DML语句失败，直到FORALL loop执行完毕才抛出异常。可以使用SQL%BULK_EXCEPTIONS 查看异常信息。

==lower_bound .. upper_bound：数字表达式，来指定一组连续有效的索引数字。该表达式只需解析一次。

==INDICES OF collection_name：用于指向稀疏数组的实际下标

==VALUES OF index_collection_name：用于指向集合的一个子集的下标数组

二 使用FORALL：

1、循环中声明删除语句（Issuing DELETE Statements in a Loop

CREATE TABLE employees_temp AS SELECT * FROM employees;

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 employees_temp WHERE department_id = depts(i);
   COMMIT;
END;
/

2、循环中声明插入语句（Issuing INSERT Statements in a Loop）

CREATE TABLE parts1 (pnum INTEGER, pname VARCHAR2(15));
CREATE TABLE parts2 (pnum INTEGER, pname VARCHAR2(15));

DECLARE
  TYPE NumTab IS TABLE OF parts1.pnum%TYPE INDEX BY PLS_INTEGER;
  TYPE NameTab IS TABLE OF parts1.pname%TYPE INDEX BY PLS_INTEGER;
  pnums  NumTab;
  pnames NameTab;
  iterations CONSTANT PLS_INTEGER := 50000;
  t1 INTEGER;
  t2 INTEGER;
  t3 INTEGER;
BEGIN
  FOR j IN 1..iterations LOOP  -- load index-by tables
     pnums(j) := j;
     pnames(j) := 'Part No. ' || TO_CHAR(j);
  END LOOP;
  t1 := DBMS_UTILITY.get_time;
  FOR i IN 1..iterations LOOP  -- use FOR loop
     INSERT INTO parts1 VALUES (pnums(i), pnames(i));
  END LOOP;
  t2 := DBMS_UTILITY.get_time;
  FORALL i IN 1..iterations  -- use FORALL statement
     INSERT INTO parts2 VALUES (pnums(i), pnames(i));
  t3 := DBMS_UTILITY.get_time;
  DBMS_OUTPUT.PUT_LINE('Execution Time (secs)');
  DBMS_OUTPUT.PUT_LINE('---------------------');
  DBMS_OUTPUT.PUT_LINE('FOR loop: ' || TO_CHAR((t2 - t1)/100));
  DBMS_OUTPUT.PUT_LINE('FORALL:   ' || TO_CHAR((t3 - t2)/100));
  COMMIT;
END;
/

FORALL要明显快于FOR..LOOP结构：

Execution Time (secs)
---------------------
FOR loop: 5.14
FORALL:   .56

PL/SQL 过程已成功完成。

3、集合部分元素使用FORALL（Using FORALL with Part of a Collection）

DROP TABLE employees_temp;

CREATE TABLE employees_temp AS SELECT * FROM employees;

DECLARE
   TYPE NumList IS VARRAY(10) OF NUMBER;
   depts NumList := NumList(5,10,20,30,50,55,57,60,70,75);
BEGIN
   FORALL j IN 4..7  -- use only part of varray
      DELETE FROM employees_temp WHERE department_id = depts(j);
   COMMIT;
END;
/

4、对非连续索引值使用FORALL（Using FORALL with Non-Consecutive Index Values）

-- Create empty tables to hold order details
CREATE TABLE  valid_orders (cust_name VARCHAR2(32), amount NUMBER(10,2));
CREATE TABLE  big_orders AS SELECT * FROM valid_orders WHERE 1 = 0;
CREATE TABLE  rejected_orders AS SELECT * FROM valid_orders WHERE 1 = 0;

DECLARE
-- Make collections to hold a set of customer names and order amounts.
   SUBTYPE cust_name IS valid_orders.cust_name%TYPE;
   TYPE cust_typ IS TABLe OF cust_name;
   cust_tab cust_typ;
   SUBTYPE order_amount IS valid_orders.amount%TYPE;
   TYPE amount_typ IS TABLE OF NUMBER;
   amount_tab amount_typ;
-- Make other collections to point into the CUST_TAB collection.
   TYPE index_pointer_t IS TABLE OF PLS_INTEGER;
   big_order_tab index_pointer_t := index_pointer_t();
   rejected_order_tab index_pointer_t := index_pointer_t();
   PROCEDURE setup_data IS BEGIN
 -- Set up sample order data, including some invalid orders and some 'big' orders.
     cust_tab := cust_typ('Company1','Company2','Company3','Company4','Company5');
     amount_tab := amount_typ(5000.01, 0, 150.25, 4000.00, NULL);
   END;
   
BEGIN
   setup_data(); --initialization
   DBMS_OUTPUT.PUT_LINE('--- Original order data ---');
   FOR i IN 1..cust_tab.LAST LOOP
     DBMS_OUTPUT.PUT_LINE('Customer #' || i || ', ' || cust_tab(i) || ': $' ||
                           amount_tab(i));
   END LOOP;
-- Delete invalid orders (where amount is null or 0).
   FOR i IN 1..cust_tab.LAST LOOP
     IF amount_tab(i) is null or amount_tab(i) = 0 THEN
        cust_tab.delete(i);
        amount_tab.delete(i);
     END IF;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('--- Data with invalid orders deleted ---');
   FOR i IN 1..cust_tab.LAST LOOP
     IF cust_tab.EXISTS(i) THEN
       DBMS_OUTPUT.PUT_LINE('Customer #' || i || ', ' || cust_tab(i) || ': $' ||
                             amount_tab(i));
      END IF;
   END LOOP;
-- Because the subscripts of the collections are not consecutive, use
-- FORALL...INDICES OF to iterate through the actual subscripts, 
-- rather than 1..COUNT
   FORALL i IN INDICES OF cust_tab
     INSERT INTO valid_orders(cust_name, amount) 
        VALUES(cust_tab(i), amount_tab(i));
   
-- Now process the order data differently
-- Extract 2 subsets and store each subset in a different table
   setup_data(); -- Initialize the CUST_TAB and AMOUNT_TAB collections again.
   FOR i IN cust_tab.FIRST .. cust_tab.LAST LOOP
     IF amount_tab(i) IS NULL OR amount_tab(i) = 0 THEN
       rejected_order_tab.EXTEND; -- Add a new element to this collection
-- Record the subscript from the original collection
       rejected_order_tab(rejected_order_tab.LAST) := i; 
     END IF;
     IF amount_tab(i) > 2000 THEN
        big_order_tab.EXTEND; -- Add a new element to this collection
-- Record the subscript from the original collection
        big_order_tab(big_order_tab.LAST) := i;
     END IF;
   END LOOP;
-- Now it's easy to run one DML statement on one subset of elements, 
-- and another DML statement on a different subset.
   FORALL i IN VALUES OF rejected_order_tab
     INSERT INTO rejected_orders VALUES (cust_tab(i), amount_tab(i));
   FORALL i IN VALUES OF big_order_tab
     INSERT INTO big_orders VALUES (cust_tab(i), amount_tab(i));
   COMMIT;
END;
/

--- Original order data ---
Customer #1, Company1: $5000.01
Customer #2, Company2: $0
Customer #3, Company3: $150.25
Customer #4, Company4: $4000
Customer #5, Company5: $
--- Data with invalid orders deleted ---
Customer #1, Company1: $5000.01
Customer #3, Company3: $150.25
Customer #4, Company4: $4000
 
PL/SQL procedure successfully completed

-- Verify that the correct order details were stored
SELECT cust_name "Customer", amount "Valid order amount" FROM valid_orders;
SELECT cust_name "Customer", amount "Big order amount" FROM big_orders;
SELECT cust_name "Customer", amount "Rejected order amount" FROM rejected_orders;

5、使用%BULK_ROWCOUNT返回受影响的记录行数

CREATE TABLE emp_temp AS SELECT * FROM employees;
DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   depts NumList := NumList(30, 50, 60);
BEGIN
   FORALL j IN depts.FIRST..depts.LAST
      DELETE FROM emp_temp WHERE department_id = depts(j);
-- How many rows were affected by each DELETE statement?
   FOR i IN depts.FIRST..depts.LAST
   LOOP
      DBMS_OUTPUT.PUT_LINE('Iteration #' || i || ' deleted ' ||
         SQL%BULK_ROWCOUNT(i) || ' rows.');
   END LOOP;
END;
/

6、FORALL与BULK COLLECT 一起使用（Using FORALL With BULK COLLECT）

CREATE TABLE emp_temp AS SELECT * FROM employees;
DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   depts NumList := NumList(10,20,30);
   TYPE enum_t IS TABLE OF employees.employee_id%TYPE;
   TYPE dept_t IS TABLE OF employees.department_id%TYPE;
   e_ids enum_t;
   d_ids dept_t;
BEGIN
  FORALL j IN depts.FIRST..depts.LAST
    DELETE FROM emp_temp WHERE department_id = depts(j)
       RETURNING employee_id, department_id BULK COLLECT INTO e_ids, d_ids;
  DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows:');
  FOR i IN e_ids.FIRST .. e_ids.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('Employee #' || e_ids(i) || ' from dept #' || d_ids(i));
  END LOOP;
END;
/

-----------------------------

Present   by   dylan.