PL/SQL BULK COLLECT INTO

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的执行效率。