This instruction allows computing all the rows of a collection in a single pass. FORALL cannot be use on the client-side and can precede one and only one statement at a time.
If an error is raised by the FORALL statement, all the rows processed are rolled back. You can save the rows that raised an error (and do not abort the process) with the SAVE EXCEPTION keyword. Every exception raised during execution is stored in the BULK_EXCEPTIONS collection. This is a collection of records composed by two attributes:
FORALL index IN min_index .. max_index [SAVE EXCEPTION] sql_order
%BULK_EXCEPTIONS(n).ERROR_INDEX which contains the index number
%BULK_EXCEPTIONS(n).ERROR_CODE which contains the error code .
i.e.
SQL> Declare
2 TYPE TYP_TAB_TEST IS TABLE OF TEST%ROWTYPE;
3 tabrec TYP_TAB_TEST;
4
CURSOR C_test is select A, B From TEST;
5 Begin
6 -- Load the collection from the table --
7 Select A, B BULK COLLECT into tabrec From TEST;
8
9 -- Insert into the table from the collection --
10 Forall i in tabrec.first..tabrec.last
11 Insert into TEST values tabrec(i);
12
13 -- Update the table from the collection --
14 For i in tabrec.first..tabrec.last Loop
15 tabrec(i).B := tabrec(i).B * 2;
16 End loop ;
17
18 -- Use of cursor --
19 Open C_test ;
20 Fetch C_test BULK COLLECT Into tabrec;
21 Close C_test ;
22
23 End ;
24 /
Implementation restriction
It is not allowed to use the FORALL statement and an UPDATE order that use the SET ROW functionality
SQL> Declare
2 TYPE TAB_EMP is table of EMP%ROWTYPE;
3 emp_tab TAB_EMP;
4 Cursor CEMP is Select * From EMP;
5 Begin
6 Open CEMP;
7 Fetch CEMP BULK COLLECT Into emp_tab;
8 Close CEMP;
9
10 Forall i in emp_tab.first..emp_tab.last
11 Update EMP set row = emp_tab(i) where EMPNO = emp_tab(i).EMPNO ; -- ILLEGAL
12
13 End ;
14 /
Update EMP set row = emp_tab(i) where EMPNO = emp_tab(i).EMPNO ; -- ILLEGAL
*
ERROR at line 11:
ORA-06550: line 11, column 52:
PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND
table of records
You have to use a standard FOR LOOP statement:
For i in emp_tab.first..emp_tab.last loop
Update EMP set row = emp_tab(i) where EMPNO = emp_tab(i).EMPNO ;
End loop;
Or use simple collections:
Declare
TYPE TAB_EMPNO is table of EMP.EMPNO%TYPE;
TYPE TAB_EMPNAME is table of EMP.ENAME%TYPE;
no_tab TAB_EMPNO ;
na_tab TAB_EMPNAME;
Cursor CEMP is Select EMPNO, ENAME From EMP;
Begin
Open CEMP;
Fetch CEMP BULK COLLECT Into no_tab, na_tab ;
Close CEMP;
Forall i in no_tab.first..no_tab.last
Update EMP set ENAME = na_tab(i) where EMPNO = no_tab(i) ;
End;
Exceptions:
If an error is raised by the FORALL statement, all the rows processed are rolled back. You can save the rows that raised an error (and do not abort the process) with the SAVE EXCEPTION keyword.
Every exception raised during execution is stored in the %BULK_EXCEPTIONS collection. This is a collection of records composed by two attributes:
%BULK_EXCEPTIONS(n).ERROR_INDEX which contains the index number
%BULK_EXCEPTIONS(n).ERROR_CODE which contains the error code
The total amount of errors raised by the FORALL instruction is stored in the SQL%BULK_EXCEPTIONS.COUNT attribute.
SQL> Declare
2 TYPE TYP_TAB IS TABLE OF Number;
3 tab TYP_TAB := TYP_TAB( 2, 0, 1, 3, 0, 4, 5 ) ;
4 nb_err Pls_integer ;
5 Begin
6 Forall i in tab.first..tab.last SAVE EXCEPTIONS
7 Delete from EMP where SAL = 5 / tab(i) ;
8 Exception
9 When others then
10 nb_err := SQL%BULK_EXCEPTIONS.COUNT ;
11 dbms_output.put_line( to_char( nb_err ) || ' Errors ' ) ;
12 For i in 1..nb_err Loop
13 dbms_output.put_line( 'Index ' || to_char( SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ) || ' Er
ror : '
14 || to_char( SQL%BULK_EXCEPTIONS(i).ERROR_CODE ) ) ;
15 End loop ;
16 End ;
17 /
2 Errors
Index 2 Error : 1476
Index 5 Error : 1476
PL/SQL procedure successfully completed.
The %BULK_ROWCOUNT attribute.
This is an INDEX-BY table that contains for each SQL order the number of rows processed.
If no row is impacted, SQL%BULK_ROWCOUNT(n) equals 0.
SQL> Declare
2 TYPE TYP_TAB_TEST IS TABLE OF TEST%ROWTYPE ;
3 TYPE TYP_TAB_A IS TABLE OF TEST.A%TYPE ;
4 TYPE TYP_TAB_B IS TABLE OF TEST.B%TYPE ;
5 tabrec TYP_TAB_TEST ;
6 taba TYP_TAB_A ;
7 tabb TYP_TAB_B ;
8 total Pls_integer := 0 ;
9 CURSOR C_test is select A, B From TEST;
10 begin
11 -- Load the collection from the table --
12 Select A, B BULK COLLECT into tabrec From TEST ;
13
14 -- Insert rows --
15 Forall i in tabrec.first..tabrec.last
16 insert into TEST values tabrec(i) ;
17
18 For i in tabrec.first..tabrec.last Loop
19 total := total + SQL%BULK_ROWCOUNT(i) ;
20 End loop ;
21
22 dbms_output.put_line('Total insert : ' || to_char( total) ) ;
23
24 total := 0 ;
25 -- Upadate rows --
26 For i in tabrec.first..tabrec.last loop
27 update TEST set row = tabrec(i) where A = tabrec(i).A ;
28 End loop ;
29
30 For i in tabrec.first..tabrec.last Loop
31 total := total + SQL%BULK_ROWCOUNT(i) ;
32 End loop ;
33
34 dbms_output.put_line('Total upfdate : ' || to_char( total) ) ;
35
36 End ;
37 /
Total insert: 20
Total upfdate: 20
PL/SQL procedure successfully completed.
No comments:
Post a Comment