Bulk绑定是如何提高性能的。有空再翻译吧。(from oracle)

How Do Bulk Binds Improve Performance?

The assigning of values to PL/SQL variables in SQL statements is called binding. PL/SQL binding operations fall into three categories:

• in-bind When a PL/SQL variable or host variable is stored in the database by an INSERT or UPDATE statement.
• out-bind When a database value is assigned to a PL/SQL variable or a host variable by the RETURNING clause of an INSERT, UPDATE, or DELETE statement.
• define When a database value is assigned to a PL/SQL variable or a host variable by a SELECT or FETCH statement.

A DML statement can transfer all the elements of a collection in a single operation, a process known as bulk binding. If the collection has 20 elements, bulk binding lets you perform the equivalent of 20 SELECT, INSERT, UPDATE, or DELETE statements using a single operation. This technique improves performance by minimizing the number of context switches between the PL/SQL and SQL engines. With bulk binds, entire collections, not just individual elements, are passed back and forth.

To do bulk binds with INSERT, UPDATE, and DELETE statements, you enclose the SQL statement within a PL/SQL FORALL statement.

To do bulk binds with SELECT statements, you include the BULK COLLECT clause in the SELECT statement instead of using INTO.

For full details of the syntax and restrictions for these statements, see "FORALL Statement" and "SELECT INTO Statement".

Example: Performing a Bulk Bind with DELETE

The following DELETE statement is sent to the SQL engine just once, even though it performs three DELETE operations:

DECLARE   TYPE NumList IS VARRAY(20) OF NUMBER;   depts NumList := NumList(10, 30, 70);  -- department numbersBEGIN   FORALL i IN depts.FIRST..depts.LAST      DELETE FROM emp WHERE deptno = depts(i);END;

Example: Performing a Bulk Bind with INSERT

In the example below, 5000 part numbers and names are loaded into index-by tables. All table elements are inserted into a database table twice: first using a FOR loop, then using a FORALL statement. The FORALL version is much faster.

SQL> SET SERVEROUTPUT ONSQL> CREATE TABLE parts (pnum NUMBER(4), pname CHAR(15));Table created.SQL> GET test.sql 1  DECLARE 2     TYPE NumTab IS TABLE OF NUMBER(4) INDEX BY BINARY_INTEGER; 3     TYPE NameTab IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER; 4     pnums  NumTab; 5     pnames NameTab; 6     t1 NUMBER(5); 7     t2 NUMBER(5); 8     t3 NUMBER(5); 9     10     11  BEGIN12     FOR j IN 1..5000 LOOP  -- load index-by tables13        pnums(j) := j;14        pnames(j) := 'Part No. ' || TO_CHAR(j); 15     END LOOP;16     t1 := dbms_utility.get_time;17     FOR i IN 1..5000 LOOP  -- use FOR loop18        INSERT INTO parts VALUES (pnums(i), pnames(i));19     END LOOP;20     t2 := dbms_utility.get_time;21     FORALL i IN 1..5000  -- use FORALL statement22        INSERT INTO parts VALUES (pnums(i), pnames(i));23     get_time(t3);24     dbms_output.put_line('Execution Time (secs)');25     dbms_output.put_line('---------------------');26     dbms_output.put_line('FOR loop: ' || TO_CHAR(t2 - t1));27     dbms_output.put_line('FORALL:   ' || TO_CHAR(t3 - t2));28* END;SQL> /Execution Time (secs)---------------------FOR loop: 32FORALL:   3PL/SQL procedure successfully completed.