Reducing Loop Overhead for DML Statements and Queries with Bulk SQL(10gr2)
PL/SQL运行SQL的机制是把SQL语句，比如DML,SELECT，发送给SQL 引擎。然后SQL引擎把SQL语句的结果返回给PL/SQL。想象一下，下面这样的PL/SQL块。
FOR j IN 1..99999999 LOOP VAR1[i]=i; INSERT INTO TEST VALUES(VAR[i]); END LOOP;
这个PL/SQL块非常简洁明了，它循环9999999次，每一次循环都把 i 赋值到 VAR1[i]中，然后把这个VAR1[ i ] insert到表TEST中。这么简单的SQL却有着非常严重的性能问题。因为他要循环99999999次，每一次循环PL/SQL引擎都像SQL引擎发送一条INSERT 语句，这期间的通讯开销会导致运行很慢。 Oracle对这种现象的解决方案是使用批量SQL,比如，FORALL，BULK COLLECT。
首先，我们看一下FORALL。 这里我们分为2部分讲，
普通的FORALL
这里我们做一个比较，先是用普通的FOR把一万条数据插入一个表A中，记录一下时间。然后把相同的一万条数据插入同一个表A中，但这次用FORALL。我们看一下哪个快。
1 CREATE OR REPLACE PROCEDURE for_vs_forall 2 AS 3 timer1 INTEGER; 4 timer2 INTEGER; 5 6 TYPE num_tab_type IS TABLE OF NUMBER INDEX BY binary_integer ; 7 num_table num_tab_type; 8 BEGIN 9 /*初始化 num_table*/ 10 FOR i IN 1..9999 LOOP 11 num_table(i) := i; 12 END LOOP; 13 14 /*在FOR循环里向A中insert数据，并在循环开始前和循环结束后记录时间，算出循环一共花的时间*/ 15 16 timer1 := DBMS_UTILITY.get_time; 17 FOR i IN 1..9999 LOOP 18 num_table(i) := i; 19 insert into A values(num_table(i)); 20 END LOOP; 21 timer2 := DBMS_UTILITY.get_time; 22 DBMS_OUTPUT.PUT_LINE('Execution Time (secs)'); 23 DBMS_OUTPUT.PUT_LINE('FOR loop: ' || TO_CHAR((timer2 - timer1)/100)); 24 25 26 /*清空A表*/ 27 28 execute immediate 'delete from A'; 29 30 31 /*在FORALL循环里向A中insert数据，并在循环开始前和循环结束后记录时间，算出循环一共花的时间*/ 32 33 timer1 := DBMS_UTILITY.get_time; 34 FORALL i IN 1..9999 35 INSERT INTO A VALUES(num_table(i)); 36 timer2 := DBMS_UTILITY.get_time; 37 DBMS_OUTPUT.PUT_LINE('Execution Time (secs)'); 38 DBMS_OUTPUT.PUT_LINE('FORALL loop: ' || TO_CHAR((timer2 - timer1)/100)); 39 40 END; 41
简单讲解一下上面的测试，9-10行是初始化num_table这个集合。这里有个小问题，就是如果我在声明这个集合的时候没有指定指定INDEX BY BINARY_INTEGER，那么这里就会报一个ORA-06531: Reference to uninitialized collection的错误。 这个需要研究一下，但我们先跳过这里。 16-23行是通过FOR循环往A表中insert数据，并且在循环开始和循环结束都记录时间以便算出FOR循环的时间。31-38通过FORALL insert数据。在循环开始前和循环结束后都记录并计算时间。 最后看一下两种循环哪个更快。
1 SQL> exec for_vs_forall 2 Execution Time (secs) 3 FOR loop: 2.95 4 Execution Time (secs) 5 FORALL loop: .05 6 7 PL/SQL procedure successfully completed.
运行一下，结果如上所示，FORALL非常快。
INDICES OF 和 VALUES OF
/*首先创建三个表，
valid_orders 记录了customer name 以及对应的 order数量
big_orders 记录了order 数量比较大的customer name 以及order数量
rejected_orders 如果有不符合标准的数据，比如 order 数量为NULL 或者0，则把 customer name 和order数量记录进这个表*/
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;
CREATE OR REPLACE PROCEDURE test_forall
AS
/*创建两个集合用来存储一系列的customer name 和 order amout 这两个集合类似于数组，相同下表的
两个元素就组成了一对儿数据对儿，可以存进 valid_order这个表中。 比如 cust_tab(1), amount_tab（1）*/
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 order_amount;
amount_tab amount_typ;
/*创建两个数据结构，
big_order_tab 这个结构里面每一个元素对应着一对customer name和order amount的组合，
如果里面存着（8,10）那么就意味着第8，10对儿 customer name，order amount应该存进 big_order表
regected_order_tab记录了应该存进rejected_order的数据对儿*/
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();
/*初始化cust_tab 和 amount_tab 生成一些 customer name 和 order amount的数据对儿*/
PROCEDURE setup_data IS BEGIN
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来初始化，生成一些 customer name 和 order amount*/
setup_data();
/*把这些数据打印出来*/
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;
/*如果有一些记录不符合标准，比如order amount=0 或者 null，就把对应的数据对儿删掉。*/
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;
/*输出一下现在的数据， 因为有些数据被删除了，所以输出之前需要用
IF cust_tab.EXISTS（i）确定数据是否存*/
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;
/*注意这里才是重点，因为cust_tab里有一些数据被删除了，这时候如果你还用
FORALL i IN cust_tab.FIRST .. cust_tab.LAST
或者
FORALL i IN 1 .. count
这两种形式，就会出错。 会碰到ORA-22160: element at index [2] does not exist这种错误，
所以需要下面的这种执行方式。
*/
FORALL i IN INDICES OF cust_tab
INSERT INTO valid_orders(cust_name, amount) VALUES(cust_tab(i), amount_tab(i));
/*下面重新初始化数据，学习一下 values of*/
setup_data();
/*这里遍历一下cust_tab 以及 amount_tab这两个表，如果甄别数据，把对应的数据放入big_order
或者rejected_order*/
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;
rejected_order_tab(rejected_order_tab.LAST) := i;
END IF;
IF amount_tab(i) > 2000 THEN
big_order_tab.EXTEND;
big_order_tab(big_order_tab.LAST) := i;
END IF;
END LOOP;
/*这里要注意一个前提就是 big_order_tab中存储的内容是指向cust_tab和order_tab的指针，所以
如果你还像以前这样写
FORALL i IN big_order_tab.FIRST .. big_order_tab.LAST
INSERT INTO big_orders VALUES (cust_tab(i), amount_tab(i));
那么你插入的数据就错了。因为这时候的i是 big_order_tab的下标而不是内容，你需要这样写
*/
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;
运行一下
SQL> delete from valid_orders; 0 rows deleted. SQL> delete from big_orders; 4 rows deleted. SQL> delete from rejected_orders; 0 rows deleted. SQL> commit; Commit complete. SQL> EXEC test_forall --- 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. SQL> select * from valid_orders; CUST_NAME AMOUNT ------------------------------------------------------------------------------------------------ ---------- Company1 5000.01 Company3 150.25 Company4 4000 SQL> select * from big_orders; CUST_NAME AMOUNT ------------------------------------------------------------------------------------------------ ---------- Company1 5000.01 Company4 4000 SQL> select * from rejected_orders; CUST_NAME AMOUNT ------------------------------------------------------------------------------------------------ ---------- Company2 0 Company5
