FORALL statement in Oracle

You can use the FORALL statement to Perform Batch INSERT, UPDATE, and DELETE operations in Oracle.

Syntax:

1. -- Syntax 1:
2. FORALL subscript variable (can only be referenced as subscript)INLower limit... Upper Limit
3. SQL statement;-- Only one SQL statement is allowed.
6. -- Syntax 2:
7. FORALL subscript variableININDICESOF(Skip the element without a value assignment, for exampleDELETE,NULLValue) Set
8. [BETWEENLower limitANDUpper Limit]
9. SQL statement;
12. -- Syntax 3:
13. FORALL subscript variableIN VALUES OFSet (use the values in the set as subscript variables)
14. SQL statement;

 

1. Create TableTb1 (
2. Id number (5 ),
3. NameVarchar2 (50)
4. );

Syntax 1 Demo:

1. -- Batch insert demo
2. Declare
3. Type tb_table_typeIs Table OfTb1 % rowtype
4. Index ByBinary_integer;
5. Tb_table tb_table_type;
6. Begin
7. ForIIn1. 10 loop
8. Tb_table (I). id: = I;
9. Tb_table (I ).Name: ='Name'| I;
10. EndLoop;
11. Forall IIn1. tb_table.Count
12. Insert IntoTb1ValuesTb_table (I );
13. End;
16. -- Batch modification demonstration
17. Declare
18. Type tb_table_typeIs Table OfTb1 % rowtype
19. Index ByBinary_integer;
20. Tb_table tb_table_type;
21. Begin
22. ForIIn1. 10 loop
23. Tb_table (I). id: = I;
24. Tb_table (I ).Name: ='Names'| I;
25. EndLoop;
26. Forall IIn1. tb_table.Count
27. UpdateTb1 tSetRow = tb_table (I)WhereT. id = tb_table (I). id;
28. End;
31. -- Batch Delete demo
32. Declare
33. Type tb_table_typeIs Table OfTb1 % rowtype
34. Index ByBinary_integer;
35. Tb_table tb_table_type;
36. Begin
37. ForIIn1. 10 loop
38. Tb_table (I). id: = I;
39. Tb_table (I ).Name: ='Names'| I;
40. EndLoop;
41. Forall IIn1. tb_table.Count
42. DeleteTb1WhereId = tb_table (I). id;
43. End;

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12