|
SQL>
SQL> CREATE TABLE MyTable (
2 num_col NUMBER,
3 char_col VARCHAR2(60)
4 );
Table created.
SQL>
SQL> DECLARE
2 TYPE t_Numbers IS TABLE OF MyTable.num_col%TYPE
3 INDEX BY BINARY_INTEGER;
4 TYPE t_Strings IS TABLE OF MyTable.char_col%TYPE
5 INDEX BY BINARY_INTEGER;
6 v_Numbers t_Numbers;
7 v_Strings t_Strings;
8 BEGIN
9 DELETE FROM MyTable;
10 FOR v_Outer IN 1..10 LOOP
11 FOR v_Inner IN 1..v_Outer LOOP
12 INSERT INTO MyTable (num_col, char_col)
13 VALUES (v_Outer, 'Element #' || v_Inner);
14 END LOOP;
15 v_Numbers(v_Outer) := v_Outer;
16 END LOOP;
17
18 FORALL v_Count IN 1..5
19 DELETE FROM MyTable
20 WHERE num_col = v_Numbers(v_Count)
21 RETURNING char_col BULK COLLECT INTO v_Strings;
22
23 DBMS_OUTPUT.PUT_LINE('After delete:');
24 FOR v_Count IN 1..v_Strings.COUNT LOOP
25 DBMS_OUTPUT.PUT_LINE(' v_Strings(' || v_Count || ') = ' || v_Strings(v_Count));
26 END LOOP;
27 END;
28 /
After delete:
v_Strings(1) = Element #1
v_Strings(2) = Element #1
v_Strings(3) = Element #2
v_Strings(4) = Element #1
v_Strings(5) = Element #2
v_Strings(6) = Element #3
v_Strings(7) = Element #1
v_Strings(8) = Element #2
v_Strings(9) = Element #3
v_Strings(10) = Element #4
v_Strings(11) = Element #1
v_Strings(12) = Element #2
v_Strings(13) = Element #3
v_Strings(14) = Element #4
v_Strings(15) = Element #5
PL/SQL procedure successfully completed.
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>
SQL>
|