Forall 下标变量 In 下限..上限 [save exceptions]
一条Sql语句;
Forall 下标变量 In Indices Of 集合 [Between 下限 And 上限] [save exceptions] --跳过没有赋值的元素
一条sql语句;
Forall 下标变量 In Values Of 集合 [save exceptions] --将集合值作为下标 值类型为pls_integer | binary_integer
一条sql语句;
Table created
1、
SQL> Declare
2 Type t Is Table Of tmp%Rowtype Index By Pls_Integer ;
3 vt t;
4 Begin
5 vt(1).id:=1;
6 vt(1).nm:='张三';
7 vt(2).id:=2;
8 vt(2).nm:='李四';
9 vt(3).id:=2;
10 vt(3).nm:='王五';
11 Forall i In vt.first()..vt.last()
12 Insert Into tmp Values(vt(i).id,vt(i).nm);
13 dbms_output.put_line('Forall-Insert之后');
14 For i In vt.first()..vt.last() Loop
15 dbms_output.put_line(Sql%Bulk_Rowcount(i));
16 End Loop;
17 Forall i In vt.first()..vt.last()
18 Update tmp Set nm=nm||'a' Where Id=vt(i).id;
19 dbms_output.put_line('Forall-Update之后');
20 For i In vt.first()..vt.last() Loop
21 dbms_output.put_line(Sql%Bulk_Rowcount(i));
22 End Loop;
23 Forall i In vt.first()..vt.last()
24 Delete tmp Where Id=vt(i).id;
25 dbms_output.put_line('Forall-Delete之后');
26 For i In vt.first()..vt.last() Loop
27 dbms_output.put_line(Sql%Bulk_Rowcount(i));
28 End Loop;
29 Commit;
30 dbms_output.put_line('Forall-Commit之后');
31 For i In vt.first()..vt.last() Loop
32 dbms_output.put_line(Sql%Bulk_Rowcount(i));
33 End Loop;
34 End;
35 /
Forall-Insert之后
1
1
1
Forall-Update之后
1
2
2
Forall-Delete之后
1
2
0
Forall-Commit之后
1
2
0
PL/SQL procedure successfully completed
SQL>
2、
SQL> Declare
2 Type t Is Table Of tmp%Rowtype Index By Pls_Integer ;
3 vt t;
4 Begin
5 vt(1).id:=1;
6 vt(1).nm:='张三';
7 vt(3).id:=3;
8 vt(3).nm:='李四';
9 vt(4).id:=4;
10 vt(4).nm:='王五';
11 Forall i In vt.first()..vt.last()
12 Insert Into tmp Values(vt(i).id,vt(i).nm);
13 End;
14 /
Declare
Type t Is Table Of tmp%Rowtype Index By Pls_Integer ;
vt t;
Begin
vt(1).id:=1;
vt(1).nm:='张三';
vt(3).id:=3;
vt(3).nm:='李四';
vt(4).id:=4;
vt(4).nm:='王五';
Forall i In vt.first()..vt.last()
Insert Into tmp Values(vt(i).id,vt(i).nm);
End;
ORA-22160: 下标 [2] 中的元素不存在
ORA-06512: 在 line 11
SQL> Declare
2 Type t Is Table Of tmp%Rowtype Index By Pls_Integer ;
3 vt t;
4 Begin
5 vt(1).id:=1;
6 vt(1).nm:='张三';
7 vt(3).id:=3;
8 vt(3).nm:='李四';
9 vt(4).id:=4;
10 vt(4).nm:='王五';
11 Forall i In Indices Of vt Between 1 And 3
12 Insert Into tmp Values(vt(i).id,vt(i).nm);
13 End;
14 /
PL/SQL procedure successfully completed
SQL> select * from tmp;
ID NM
---------- --------------------
1 张三
3 李四
3、
SQL> Declare
2 Type t Is Table Of tmp%Rowtype Index By Pls_Integer ;
3 vt t;
4 Type tInd Is Table Of Pls_Integer;
5 vInd tInd:=tInd(1,4);
6 Begin
7 vt(1).id:=1;
8 vt(1).nm:='张三';
9 vt(3).id:=3;
10 vt(3).nm:='李四';
11 vt(4).id:=4;
12 vt(4).nm:='王五';
13 Forall i In Values Of vInd
14 Insert Into tmp Values(vt(i).id,vt(i).nm);
15 End;
16 /
PL/SQL procedure successfully completed
SQL> select * from tmp;
ID NM
---------- --------------------
1 张三
4 王五
SQL>
一条Sql语句;
Forall 下标变量 In Indices Of 集合 [Between 下限 And 上限] [save exceptions] --跳过没有赋值的元素
一条sql语句;
Forall 下标变量 In Values Of 集合 [save exceptions] --将集合值作为下标 值类型为pls_integer | binary_integer
一条sql语句;
SQL%BULK_ROWCOUNT伪集合,用下标标识forall中每一次循环影响的行数(不受commit or rollback 影响)
错误号:-24381
sql%bulk_exceptions.count 返回错误个数
sql%bulk_exceptions(i).error_index 返回错误的循环索引
sql%bulk_exceptions(i).error_code 返回对应的oracle错误编号(需取负)
Table created
1、
SQL> Declare
2 Type t Is Table Of tmp%Rowtype Index By Pls_Integer ;
3 vt t;
4 Begin
5 vt(1).id:=1;
6 vt(1).nm:='张三';
7 vt(2).id:=2;
8 vt(2).nm:='李四';
9 vt(3).id:=2;
10 vt(3).nm:='王五';
11 Forall i In vt.first()..vt.last()
12 Insert Into tmp Values(vt(i).id,vt(i).nm);
13 dbms_output.put_line('Forall-Insert之后');
14 For i In vt.first()..vt.last() Loop
15 dbms_output.put_line(Sql%Bulk_Rowcount(i));
16 End Loop;
17 Forall i In vt.first()..vt.last()
18 Update tmp Set nm=nm||'a' Where Id=vt(i).id;
19 dbms_output.put_line('Forall-Update之后');
20 For i In vt.first()..vt.last() Loop
21 dbms_output.put_line(Sql%Bulk_Rowcount(i));
22 End Loop;
23 Forall i In vt.first()..vt.last()
24 Delete tmp Where Id=vt(i).id;
25 dbms_output.put_line('Forall-Delete之后');
26 For i In vt.first()..vt.last() Loop
27 dbms_output.put_line(Sql%Bulk_Rowcount(i));
28 End Loop;
29 Commit;
30 dbms_output.put_line('Forall-Commit之后');
31 For i In vt.first()..vt.last() Loop
32 dbms_output.put_line(Sql%Bulk_Rowcount(i));
33 End Loop;
34 End;
35 /
Forall-Insert之后
1
1
1
Forall-Update之后
1
2
2
Forall-Delete之后
1
2
0
Forall-Commit之后
1
2
0
PL/SQL procedure successfully completed
SQL>
2、
SQL> Declare
2 Type t Is Table Of tmp%Rowtype Index By Pls_Integer ;
3 vt t;
4 Begin
5 vt(1).id:=1;
6 vt(1).nm:='张三';
7 vt(3).id:=3;
8 vt(3).nm:='李四';
9 vt(4).id:=4;
10 vt(4).nm:='王五';
11 Forall i In vt.first()..vt.last()
12 Insert Into tmp Values(vt(i).id,vt(i).nm);
13 End;
14 /
Declare
Type t Is Table Of tmp%Rowtype Index By Pls_Integer ;
vt t;
Begin
vt(1).id:=1;
vt(1).nm:='张三';
vt(3).id:=3;
vt(3).nm:='李四';
vt(4).id:=4;
vt(4).nm:='王五';
Forall i In vt.first()..vt.last()
Insert Into tmp Values(vt(i).id,vt(i).nm);
End;
ORA-22160: 下标 [2] 中的元素不存在
ORA-06512: 在 line 11
SQL> Declare
2 Type t Is Table Of tmp%Rowtype Index By Pls_Integer ;
3 vt t;
4 Begin
5 vt(1).id:=1;
6 vt(1).nm:='张三';
7 vt(3).id:=3;
8 vt(3).nm:='李四';
9 vt(4).id:=4;
10 vt(4).nm:='王五';
11 Forall i In Indices Of vt Between 1 And 3
12 Insert Into tmp Values(vt(i).id,vt(i).nm);
13 End;
14 /
PL/SQL procedure successfully completed
SQL> select * from tmp;
ID NM
---------- --------------------
1 张三
3 李四
3、
SQL> Declare
2 Type t Is Table Of tmp%Rowtype Index By Pls_Integer ;
3 vt t;
4 Type tInd Is Table Of Pls_Integer;
5 vInd tInd:=tInd(1,4);
6 Begin
7 vt(1).id:=1;
8 vt(1).nm:='张三';
9 vt(3).id:=3;
10 vt(3).nm:='李四';
11 vt(4).id:=4;
12 vt(4).nm:='王五';
13 Forall i In Values Of vInd
14 Insert Into tmp Values(vt(i).id,vt(i).nm);
15 End;
16 /
PL/SQL procedure successfully completed
SQL> select * from tmp;
ID NM
---------- --------------------
1 张三
4 王五
SQL>