RETURNING BULK COLLECT INTO : RETURNING Into « PL SQL « Oracle PL / SQL

RETURNING BULK COLLECT INTO
 
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>
Related examples in the same category

RETURNING INTO clause.