What is bulk collect?

This keyword ask 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 use 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 serveroutput on

SQL> Declare

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

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

  4    Cursor  C_EMP is Select empno From EMP ;

  5    Pass    Pls_integer := 1 ;

  6  Begin

  7    Open C_EMP ;

  8    Loop

  9      -- Fetch the table 3 by 3 --

 10   Fetch C_EMP BULK COLLECT into Temp_no LIMIT 3 ;

 11      Exit When C_EMP%NOTFOUND ;

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

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

 14      End loop ;

 15      Pass := Pass + 1 ;

 16    End Loop ;

 17  End ;

 18  /

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 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

  2     TYPE    TYP_TAB_EMPNO IS TABLE OF EMP.EMPNO%Type;

  3     TYPE    TYP_TAB_NOM   IS TABLE OF EMP.ENAME%Type; 

  4     Temp_no TYP_TAB_EMPNO;

  5     Tnoms   TYP_TAB_NOM ; 

  6  Begin

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

  8     Delete From EMP where sal > 3000

  9     RETURNING empno, ename BULK COLLECT INTO Temp_no, Tnoms ;

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

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

 12     End  loop ;

 13  End ;

 14  /

Fired employee : 7839 KING

PL/SQL procedure successfully completed.