ForAll and bulk collect are implementations of bulk SQL

November 20, 2014, Thursday Update 1. Example 1 Declare
/*typeCard_rec_type isRecord
(Dn_no channel.dn_no%type,
channel_id channel.channel_id%type);
typeNested_card_type is Table ofCard_rec_type;
Card_tab Nested_card_type; */
cursorCard_rec is
Selectdn_no,channel_id fromChannel
typeNested_card_type is Table ofcard_rec%rowtype;--> cursor-based nested table types
Card_tab Nested_card_type;
begin
Selectdn_no,channel_id
Bulk Collect intoCard_tab
fromChannel
wherelan_id=7;
ForAll IinchCard_tab. First.. Card_tab. Last
Insert into(Selectdn_no,channel_id fromTb_card)
ValuesCard_tab (i);
Commit;
Dbms_output.put_line (' The Total ' | | Card_tab.Count| | ' have INSERT INTO Tb_card ');
End;
/--If we capture when ForAll throws an exception and commit in the exception processing statement, we can save the record that we have successfully updated.
Example 2declare  cursor cur is select object_id,object_name from Dba_objects a where a.object_type= ' TABLE ';  l_count num ber:=0;  l_commit number:=100;    type T_target is table of cur%rowtype;  L_target T_TARGET;&NB Sp L_limit number default 10;  begin  open cur;  loop    FETCH cur bulk collect into l_target l Imit l_limit;    Exit when l_target.count=0;         forall i in L_target.first: l_target.last         insert into TMP_LBX (id,name) VALUES (L_target (i). Object_id,l_target (i). object_name);                  l_count: =l_count + l_target.count;&nbsp ;        if l_count >= l_commit then           commit;    &NB Sp      l_count:=0;         end if;       end loop;     commit;         close Cur;end;
2. Exception Handling Example ForAll bulk_exceptions capture Declare
typeId_list is Table oft.object_id%type Index byBinary_integer;
Id_info id_list;
I Number;
Errors_num Number;
begin
Selectobject_id
Bulk Collect intoId_info fromT

ForAll IinchId_info. First.. Id_info. LastSave exceptions
UpdateTSetsubobject_name=object_type| |timestamp
whereObject_id=id_info (i);
Commit;
Exception
When others Then
Errors_num: =SQL%bulk_exceptions.Count;
Dbms_output.put_line (' Total Exception is: ' | | To_char (Errors_num));

If Errors_num > 10 Then
Errors_num: = 10;
EndIf

forIinch1.. Errors_numLoop
Dbms_output.put_line (' Error # ' | | i| | ' is ' | |SQL%bulk_exceptions (i). error_index| | ' ' | | SQLERRM (-SQL%bulk_exceptions (i). Error_code));
End Loop;

rollback;
End;
/
--Exception record storage--Create test table Tb_bulk_test (p_id number primary key,p_name varchar2); insert into Tb_bulk_test (p_i D,p_name) Select A.object_id,a.object_name from Dba_objects a where a.object_id>500 and rownum<=1000;
--Generate random tables CREATE table Tb_test asselect trunc (Dbms_random.value (10,10000)) ID from DUAL Connect by level <1001;--Creating exception record table CREATE TABLE Log_error (Err_ind varchar2 (Ten), Err_code varchar2 (), insert_date date default sysdate);
DECLARE CURSOR c1 is select ID from Tb_test;type c1_type is table of C1%rowtype;c1_tab c1_type;bulk_errors EXCEPTION;PRAGM A exception_init (bulk_errors,-24381);
Type Type_errorindex is table of number index by Binary_integer;err_ind type_errorindex;type Type_errorcode is table of VA RCHAR2 (+) index by Binary_integer;err_code Type_errorcode;
Begin open C1;    Fetch C1 bulk collect into C1_tab; ForAll i in C1_tab.first.    C1_tab.last Save exceptions INSERT INTO (select p_id from Tb_bulk_test) values C1_tab (i);    Commit Exception when Bulk_errors then for J in 1..            Sql%bulk_exceptions.count () Loop Err_ind (j): =sql%bulk_exceptions (j). Error_index;            Err_code (j): =-sql%bulk_exceptions (j). Error_code;            Execute immediate ' INSERT into Log_error (Err_ind,err_code) VALUES (: 1,:2) ' using Err_ind (j), Err_code (j);          Commit   End Loop;   End 3. ForAll and ROWCOUNT Declare
typeId_list is Table oft.object_id%type Index byBinary_integer;
Id_info id_list;
I Number;
J Number;
Errors_num Number;
begin
Selectobject_id
Bulk Collect intoId_info fromT

ForAll IinchId_info. First.. Id_info. LastSave exceptions
UpdateTSetsubobject_name=object_type| |timestamp
whereObject_id=id_info (i);


forJinchId_info. First.. Id_info. Last Loop
Dbms_output.put_line (' iteration# ' | | j| | ': ' | |SQL%bulk_rowcount (j) | | ' rows. ');
End Loop;
Commit;
End;
/




Reference: http://otn.itpub.net/17203031/viewspace-777035/


From for notes (Wiz)

ForAll and bulk collect are implementations of bulk SQL