==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;
- /