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