Bulk Collect

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

(i)  For Input collections-- use the FORALL statement
(ii) For Output collections--use BULK COLLECT clause


#---------------------------------------------------------------------------------
# OUTPUT COLLECTIONS - BULK COLLECT
#---------------------------------------------------------------------------------

--The bulk collect option instructs the SQL engine to bulk bind the output collections
   before returning them to the PL/SQL engine.
--This allows us to load data dynamically into collections at one shot for further processing.
--Bulk collect can be used with SELECT INTO, FETCH INTO and RETURNING INTO statements
Syntax:

   ... BULK COLLECT INTO collection_name[, collection_name] ....


#-------------
# Examples 1
#-------------

DECLARE
TYPE cust_tab IS TABLE OF customer.customer_account_id%TYPE
INDEX BY BINARY_INTEGER;
Custs cust_tab;
BEGIN
SELECT customer_account_id
 BULK COLLECT INTO custs
FROM customer
WHERE effective_date BETWEEN
TO_DATE(‘01-Jan-2004’,’DD-MON-RRRR’) AND TRUNC(SYSDATE);
END;;
/

#-------------
# Examples 2
#-------------


DECLARE
   TYPE NameTab IS TABLE OF emp.ename%TYPE;
   TYPE SalTab IS TABLE OF emp.sal%TYPE;
   names NameTab;
   sals SalTab;
   CURSOR c1 IS SELECT ename, sal FROM emp;
BEGIN
   OPEN c1;


   FETCH c1 BULK COLLECT INTO names, sals;
   FOR i IN names.FIRST..names.LAST LOOP
      DBMS_OUTPUT.PUT_LINE(names(i) || ' ' || sals(i));
   END LOOP;
   CLOSE c1;
END;
/

#-----------------------------------
# Bulk used with Select into clause
#-----------------------------------


declare
 type emp_details is table of emp.ename%type index by binary_integer;
V emp_details;
begin
select ename bulk collect into V
from emp;
for i in V.first .. V.last
 loop
    dbms_output.put_line(V(i));
end loop;
end;

#-------------
# Bulk used in Cursors
#-------------


declare
cursor cf is select * from emp;
type emp_tab is table of emp%rowtype index by binary_integer;
V emp_tab;
v_limit natural := 10;
begin
 open cf;
fetch cf bulk collect into V limit v_limit;
for j in V.first .. V.last
 loop
        dbms_output.put_line(V(j).ename);
end loop;
end;

#-------------
# Bulk Insert
#-------------



Create table BI (a number check(a between 5 and 45));

declare
type no_list is table of number index by binary_integer;
v no_list;
bulk_errors exception;
 pragma exception_init ( bulk_errors, -24381 );
begin
for i in 5..50
loop
   v(i) := i;
end loop;
forall j in V.first .. V.last  save exceptions
   insert into bi values (V(j));
   dbms_output.put_line('Records inserted');
exception
 when bulk_errors then
 for j in 1..sql%bulk_exceptions.count
  loop
    Dbms_Output.Put_Line ( 'Error from element #' ||
      To_Char(sql%bulk_exceptions(j).error_index) || ': ' ||
      Sqlerrm(-sql%bulk_exceptions(j).error_code) );
  end loop;
end;

#-------------
# Bulk Delete
#-------------


declare
 type emp_tab is table of emp%rowtype index by binary_integer;
 V emp_tab;
begin
delete from emp
returning empno,ename,job,mgr,hiredate,sal,comm,deptno bulk collect into V;
for i in V.first .. v.last
loop
   dbms_output.put_line(V(i).ename);
end loop;
end;