Oracle PL/SQL BULK COLLECT

• This keyword asks the SQL engine to return all the rows in one or several collections before returning to the PL/SQL engine so, there is one single roundtrip for all the rows between SQL and PL/SQL engine.
• BULK COLLECT cannot be used on the client side.

(Select)(Fetch)(execute immediate) … BULK COLLECT Into collection_name [,collection_name, …] [LIMIT max].

• LIMIT is used to limit the number of rows returned
• BULK COLLECT can also be used to retrieve the result of a DML statement that uses the RETURNING INTO clause.

SQL> set server output on

SQL> Declare

     TYPE    TYP_TAB_EMP IS A TABLE OF EMP.EMPNO%Type ;

     Temp_no TYP_TAB_EMP; -- a collection of EMP.EMPNO%Type

     Cursor  C_EMP is Select empno From EMP ;

      Pass    Pls_integer := 1 ;

    Begin

      Open C_EMP ;

    Loop

        -- Fetch the table 3 by 3 --

       Fetch C_EMP BULK COLLECT into Temp_no LIMIT 3 ;

       Exit When C_EMP%NOTFOUND ;

       For i In Temp_no.first..Temp_no.last Loop

         dbms_output.put_line( 'Pass ' || to_char(Pass) || ' Empno= ' || Temp_no(i) ) ;

       End loop ;

       Pass := Pass + 1 ;

     End Loop ;

   End ;

 

Pass 1 Empno= 9999

Pass 1 Empno= 7369

Pass 1 Empno= 7499

Pass 2 Empno= 7521

Pass 2 Empno= 7566

Pass 2 Empno= 7654

Pass 3 Empno= 7698

Pass 3 Empno= 7782

Pass 3 Empno= 7788

Pass 4 Empno= 7839

Pass 4 Empno= 7844

Pass 4 Empno= 7876

Pass 5 Empno= 7900

Pass 5 Empno= 7902

Pass 5 Empno= 7934

PL/SQL procedure successfully completed.

You can use the LIMIT keyword to preserve your rollback segment:

Declare

  TYPE    TYP_TAB_EMP IS A TABLE OF EMP.EMPNO%Type ;

  Temp_no TYP_TAB_EMP ;

  Cursor  C_EMP is Select empno From EMP ;

  max_lig Pls_Integer := 3 ;

Begin

  Open C_EMP ;

  Loop

    Fetch C_EMP BULK COLLECT into Temp_no LIMIT max_lig ;

    Forall i In Temp_no.first..Temp_no.last

        Update EMP set SAL = Round(SAL * 1.1) Where empno = Temp_no(i) ;

    Commit ; -- Commit every 3 rows

    Temp_no.DELETE ;

    Exit When C_EMP%NOTFOUND ;

  End Loop ;

End ;

BULK COLLECT can also be used to retrieve the result of a DML statement that uses the RETURNING INTO clause:

SQL> Declare

       TYPE    TYP_TAB_EMPNO IS TABLE OF EMP.EMPNO%Type;

       TYPE    TYP_TAB_NOM TABLE OF EMP.ENAME%Type; 

       Temp_no TYP_TAB_EMPNO;

       Tnoms   TYP_TAB_NOM ; 

    Begin

       -- Delete rows and return the result into the collection --

       Delete From EMP where sal > 3000

       RETURNING empno, ename BULK COLLECT INTO Temp_no, Tnoms ;

      For i in Temp_no.first..Temp_no.last Loop

         dbms_output.put_line( 'Fired employee : ' || To_char( Temp_no(i) ) || ' ' || Tnoms(i) ) ;

      End  loop ;

   End ;

 

Fired employee: 7839 KING

PL/SQL procedure successfully completed.