PLSQL BULK COLLECT INTO

    技术2022-05-11  22

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


    最新回复(0)