Bulk processing using BULK COLLECT and FORALL

Lets understand BULK COLLECT and FORALL in PL/SQL

• BULK COLLECT
• Collects/retrieves  the data using SELECT statements in a single fetch,This collected data can be used for altering/inserting the data in tables later in procedure code.
• FORALL
• to INSERT, UPDATE, and DELETE the table data using bulk collections with multiple rows of data very quickly.

Example: BULK COLLECT for UPDATE

DECLARE

   CURSOR c1

   IS

      SELECT col_1, col_2 FROM src_tab;

   TYPE datatype_number IS TABLE OF NUMBER

      INDEX BY BINARY_INTEGER;

   TYPE datatype_date IS TABLE OF DATE

      INDEX BY BINARY_INTEGER;

   TYPE datatype_char IS TABLE OF VARCHAR2 (100)

      INDEX BY BINARY_INTEGER;

   v_col_1   datatype_char;

   v_col_2   datatype_char;

 limit_in number  := 100000;

   

BEGIN

   OPEN c1;

   LOOP

      FETCH c1 BULK COLLECT INTO v_col_1, v_col_2 LIMIT limit_in; --This Order must match with Cursor select columns order(basically datatypes should match between cursor columns and bulk collect column). 

      FORALL i IN 1 .. v_col_1.COUNT

         UPDATE trg_tab a

            SET a.col_1 = v_col_1 (i)

          WHERE a.col_2 = v_col_2 (i);

      COMMIT;

      EXIT WHEN c1%NOTFOUND;

   END LOOP;

   CLOSE c1;

END;