批量綁定可以通過減少在PL/SQL和SQL引擎之間的上下文切換(context switches )以此提高性能。
批量綁定（Bulk binds）主要包括：
(1) Input collections, use the FORALL statement，用來改善DML（INSERT、UPDATE和DELETE) 操作的性能。
(2) Output collections, use BULK COLLECT clause，用來提高查詢（SELECT）的性能。
Oracle 10g開始forall語句可以使用三種方式：
◆ in low..up
◆ in indices of collection 取得集合元素下標的值。
◆ in values of collection 取得集合元素的值。
forall語句還可以使用部分集合元素。
sql%bulk_rowcount(i)表示forall語句第i元素所作用的行數。
--drop table blktest;--CREATE TABLE blktest (num NUMBER(20), name varchar2(50));--CREATE OR REPLACE PROCEDURE p_bulktest ISDECLARETYPE type_num IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;TYPE type_name IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;tab_num type_num;tab_name type_name;t1 NUMBER;t2 NUMBER;t3 NUMBER;BEGINFOR i IN 1 .. 500000 LOOPtab_num(i) := i;tab_name(i) := 'name: ' || to_char(i);END LOOP;SELECT dbms_utility.get_time INTO t1 FROM dual;FOR i IN 1 .. 500000 LOOPINSERT INTO blktest VALUES(tab_num(i), tab_name(i));END LOOP;SELECT dbms_utility.get_time INTO t2 FROM dual;FORALL i IN 1 .. 500000INSERT INTO blktest VALUES(tab_num(i), tab_name(i));SELECT dbms_utility.get_time INTO t3 FROM dual;dbms_output.put_line('Execution Time(S)');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));END;/*Execution Time(S)-------------------FOR loop: 32.78FORALL: 2.64*//*bulk collect 語句：用於取得批量數據，只適用於select into ,fetch into 及DML語句的返回子句DECLARETYPE type_emp IS TABLE OF scott.emp%ROWTYPE INDEX BY BINARY_INTEGER;tab_emp type_emp;TYPE type_ename IS TABLE OF scott.emp.ename%TYPE INDEX BY BINARY_INTEGER;tab_ename type_ename;CURSOR c ISSELECT *FROM scott.emp;BEGINSELECT * BULK COLLECTINTO tab_empFROM scott.emp;FOR i IN 1 .. tab_emp.COUNT LOOPdbms_output.put_line(tab_emp(i).ename);END LOOP;dbms_output.new_line;DELETE scott.emp RETURNING ename BULK COLLECT INTO tab_ename;FOR i IN 1 .. tab_emp.COUNT LOOPdbms_output.put_line(tab_emp(i).ename);END LOOP;ROLLBACK;OPEN c;FETCH c BULK COLLECTINTO tab_emp;dbms_output.new_line;FOR i IN 1 .. tab_emp.COUNT LOOPdbms_output.put_line(tab_emp(i).sal);END LOOP;END;*/==============================批量輸入FORALL+批量輸出BULKDECLARE--批量輸入FORALL+批量輸出BULKTYPE type_num IS TABLE OF NUMBER;tab_1 type_num;tab_2 type_num;BEGINtab_1 := type_num(1, 2, 3); FORALL i IN 1 .. tab_1.COUNT --EXECUTE IMMEDIATE 'update t2 set id2=id*2 where id=:1 returning id2 into :2' --USING tab_1(i) RETURNING BULK COLLECT INTO tab_2;update t2 set id2=id*2 where id=tab_1(i) returning id2 bulk collect into tab_2;FOR i IN 1 .. tab_2.COUNT LOOPdbms_output.put_line(tab_2(i));END LOOP;END; ==============================
