Bulk Concepts

#---------------------
# HOW TO BULK BINDING
#---------------------

(i)  For Input collections-- use the FORALL statement

(ii) For Output collections--use BULK COLLECT clause


#---------------------------------------------------------------------------------
# INPUT COLLECTIONS - FORALL
#---------------------------------------------------------------------------------

--Input collections are data passed from the PL/SQL engine to the SQL engine to execute INSERT,
  UPDATE, and DELETE statements.
--for all is for bulk input.
--for all run multiple dml statement very efficeintly
--faster than equalent for loop;
--for all only serverside program, not client side.
-- we cannot use the select bulk collect statement in for all statement.

syntax: FORALL index IN lower_bound..upper_bound
         sql_statement;

#---------------------------
# EXAMPLE CODE 1
#---------------------------
DECLARE
TYPE NUMLIST IS VARRAY(10) OF NUMBER;
DEPTS NUMLIST := NUMLIST (10,30,70);
BEGIN
FORALL i IN DEPTS.FIRST..DEPTS.LAST
DELETE FROM EMP WHERE DEPT_ID = DEPTS(i);
COMMIT;
END;

#---------------------------
# EXAMPLE CODE 2
#---------------------------

DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   depts NumList := NumList(10,20,50);
BEGIN
   FORALL i IN depts.FIRST..depts.LAST
      UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(i);
   IF SQL%BULK_ROWCOUNT(3) = 0 THEN
   DBMS_OUTPUT.PUT_LINE('Its 3!!!');
   END IF;
END;
/

#---------------------------
# FORALL ERROR HANDLING
#---------------------------

SQL%BULK_EXCEPTIONS – Collection of records
SQL%BULK_EXCEPTIONS(i).ERROR_INDEX – stores itireration when  exception is raised.
SQL%BULK_EXCEPTIONS(i).ERROR_CODE - stores Oracle error code.

#---------------------------------------------------------------------------------
#   FORALL VS FORLOOP
#---------------------------------------------------------------------------------


In this example, 5000 part numbers and names are loaded into index-by
tables.  Then, all table elements are inserted into a database table
twice.  First, they are inserted using a FOR loop, which completes in
8 seconds.  Then, they are inserted using a FORALL statement, which
completes in only 0 seconds.

SQL> SET SERVEROUTPUT ON
SQL> CREATE TABLE parts (pnum NUMBER(4), pname CHAR(15));

DECLARE
   TYPE NumTab IS TABLE OF NUMBER(4) INDEX BY BINARY_INTEGER;
   TYPE NameTab IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER;
   pnums NumTab;
   pnames NameTab;
   t1 CHAR(5);
   t2 CHAR(5);
   t3 CHAR(5);
   PROCEDURE get_time(t OUT NUMBER) IS
    BEGIN
       SELECT TO_CHAR(SYSDATE,'SSSSS') INTO t FROM dual;
    END;
BEGIN
   FOR j IN 1..5000 LOOP  -- load index-by tables
      pnums(j) := j;
      pnames(j) := 'Part No. ' || TO_CHAR(j);
   END LOOP;
   get_time(t1);

   FOR i IN 1..5000 LOOP -- use FOR loop
      INSERT INTO parts VALUES (pnums(i), pnames(i));
   END LOOP;
   get_time(t2);

   FORALL i IN 1..5000  --use FORALL statement
      INSERT INTO parts VALUES (pnums(i), pnames(i));                    
   get_time(t3);
   DBMS_OUTPUT.PUT_LINE('Execution Time (secs)');
   DBMS_OUTPUT.PUT_LINE('---------------------');
   DBMS_OUTPUT.PUT_LINE('FOR loop: ' || TO_CHAR(t2 - t1));
   DBMS_OUTPUT.PUT_LINE('FORALL:   ' || TO_CHAR(t3 - t2));
END;
/                                                                        

SQL> @bulk.sql
Execution Time (secs)
---------------------
FOR loop: 8
FORALL:   0