Steven, check this out:
The addition of EXECUTE IMMEDIATE as an option for FORALL actually gives us loads more options: you can do a nested FORALL as show below, for example. Note that this will only work if the inner collection is a SQL type, and it makes sense if the outer array is small and the inner arrays are large because we will parse (or soft parse) one PL/SQL block for every element of the outer array.
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> CREATE TYPE number_table_type AS TABLE OF NUMBER (10);
2 /
Type created.
SQL> CREATE TYPE array_type AS TABLE OF number_table_type;
2 /
Type created.
SQL> CREATE TABLE table_name (column_name NUMBER (10));
Table created.
SQL> DECLARE
2 v_array array_type :=
3 array_type (
4 number_table_type (1, 2, 3),
5 number_table_type (1, 2, 3, 4, 5),
6 number_table_type (1, 2),
7 number_table_type (1, 2, 3, 4));
8 BEGIN
9 FORALL i IN v_array.FIRST .. v_array.LAST
10 EXECUTE IMMEDIATE
11 'DECLARE ' ||
12 ' v_table number_table_type := :1;' ||
13 'BEGIN ' ||
14 ' FORALL j IN v_table.FIRST .. v_table.LAST ' ||
15 ' INSERT INTO table_name (column_name) ' ||
16 ' VALUES (v_table (j)); ' ||
17 'END; '
18 USING v_array (i);
19 END;
20 /
PL/SQL procedure successfully completed.
SQL> SELECT *
2 FROM table_name;
COLUMN_NAME
-----------
1
2
3
1
2
3
4
5
1
2
1
2
3
4
14 rows selected.
SQL>
Answer:
Wow! Thanks for bringing this to my attention, James.
I hadn't really given any thought to using FORALL in this way. Upon reflection, it makes sense (well, it had better, hadn't it?), as an anonymous block is considered a special case, within Oracle, of a SQL statement. It certainly expands the horizons of what is possible with a FORALL statement!
No comments:
Post a Comment