Use VALUES OF with undefined row : forall « PL SQL Statements « Oracle PL/SQL Tutorial






SQL> CREATE TABLE favorites (
  2     flavor VARCHAR2(100),
  3     NAME VARCHAR2(100));

Table created.

SQL>
SQL> DECLARE
  2     TYPE favorites_tt IS TABLE OF favorites%ROWTYPE INDEX BY PLS_INTEGER;
  3     TYPE guide_tt IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
  4
  5     family   favorites_tt;
  6     guide    guide_tt;
  7     l_count INTEGER;
  8
  9     PROCEDURE cleanup IS
 10     BEGIN
 11        DELETE FROM favorites;
 12        guide.DELETE;
 13     END;
 14  BEGIN
 15     family (1).flavor := 'CHOCOLATE';
 16     family (1).NAME := 'VEVA';
 17     family (25).flavor := 'STRAWBERRY';
 18     family (25).NAME := 'STEVEN';
 19     family (500).flavor := 'VANILLA';
 20     family (500).NAME := 'CHRIS';
 21     family (5000).flavor := 'ROCKY ROAD';
 22     family (5000).NAME := 'ELI';
 23     family (5001).flavor := 'PINEAPPLE';
 24     family (5001).NAME := 'MOSHE';
 25     family (5002).flavor := 'EVERYTHING';
 26     family (5002).NAME := 'MICA';
 27
 28
 29     guide (-1000) := 1;
 30     guide (1000) := 7589;
 31     guide (10000) := 5001;
 32     BEGIN
 33         FORALL indx IN VALUES OF guide INSERT INTO favorites VALUES family (indx);
 34     EXCEPTION
 35        WHEN OTHERS
 36        THEN
 37           DBMS_OUTPUT.PUT_LINE (SQLERRM);
 38     END;
 39     SELECT COUNT(*) into l_count FROM favorites;
 40     DBMS_OUTPUT.PUT_LINE (l_count);
 41
 42  END;
 43  /

PL/SQL procedure successfully completed.

SQL>
SQL> DROP TABLE favorites;

Table dropped.

SQL>
SQL>








22.18.forall
22.18.1.The FORALL command builds a set of SQL statements and executes all of them at once.
22.18.2.Use for all to insert value to table collection of rowtype
22.18.3.Use VALUES OF
22.18.4.Use VALUES OF with undefined row
22.18.5.Forall in indices of
22.18.6.Use forall to loop through first element to last element in a table collection of number