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
[sql]
CREATE TABLE employees_temp AS SELECT * FROM employees;
[sql]
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）
[sql]
CREATE TABLE parts1 (pnum INTEGER, pname VARCHAR2(15));
CREATE TABLE parts2 (pnum INTEGER, pname VARCHAR2(15));
[sql]
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结构：
[sql]
Execution Time (secs)
---------------------
FOR loop: 5.14
FORALL: .56
PL/SQL 过程已成功完成。
3、集合部分元素使用FORALL（Using FORALL with Part of a Collection）
[sql]
DROP TABLE employees_temp;
[sql]
CREATE TABLE employees_temp AS SELECT * FROM employees;
[sql]
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）
[sql]
-- 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;
[sql]
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 co