PL/SQL BULK COLLECT INTO

转载: http://blog.chinaunix.net/u/19782/showart_237649.html

The BULK COLLECT INTO clause can improve the performance of queries that reference collections.

 

For example, the following PL/SQL block queries multiple values into PL/SQL tables, both with and without bulk binds:

-- Find all employees whose manager's ID number is 7698.

DECLARE TYPE Var_tab IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;

Empno VAR_TAB;

Ename VAR_TAB;

 Counter NUMBER;

CURSOR C IS SELECT Empno, Ename FROM Emp_tab WHERE Mgr = 7698; BEGIN

-- Efficient method, using a bulk bind SELECT Empno, Ename BULK COLLECT INTO Empno, Ename FROM Emp_Tab WHERE Mgr = 7698;

-- Slower method, assigning each collection element within a loop. counter := 1; FOR rec IN C LOOP Empno(Counter) := rec.Empno; Ename(Counter) := rec.Ename; Counter := Counter + 1; END LOOP; END;

You can use BULK COLLECT INTO with tables of scalar values, or tables of %TYPE values. Without the bulk bind, PL/SQL sends a SQL statement to the SQL engine for each employee that is selected, leading to context switches that hurt performance.

Ref: Oracle Document (PL/SQL Procedures and Packages)

 

 当Oracle运行PL/SQL时会使用两套引擎，所有procedural code由PL/SQL engine 完成，所有SQL由SQL engine处理。所以如果Oracle从一个collection中循环执行相同的DML操作，那么为了避免两套engine切换所消耗的系统资源，可以使用bulk binds来把所有的DML操作binding到一次操作中完成。这将极大提高PL/SQL的执行效率。