Use for all to insert value to table collection of rowtype : 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     FORALL indx IN indices of guide
 29        INSERT INTO favorites VALUES family (indx);
 30
 31     SELECT COUNT(*) into l_count FROM favorites;
 32     DBMS_OUTPUT.PUT_LINE (l_count);
 33
 34  END;
 35  /

PL/SQL procedure successfully completed.

SQL>
SQL> DROP TABLE favorites;

Table dropped.

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