Use VALUES OF : 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     guide (-1000) := 1;
 29     guide (1000) := 5001;
 30     guide (10000) := 5002;
 31     FORALL indx IN VALUES OF guide INSERT INTO favorites VALUES family (indx);
 32
 33     SELECT COUNT(*) into l_count FROM favorites;
 34     DBMS_OUTPUT.PUT_LINE (l_count);
 35
 36  END;
 37  /

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