Bulk Collect

    This is used for array fetches
    With this you can retrieve multiple rows of data with a single round trip.
    This reduces the number of context switches between the pl/sql and sql engines. 
    You can use bulk collect in both dynamic and static sql.
    You can use bulk collect in select, fetch into and returning into clauses.
    SQL engine automatically initializes and extends the collections you reference in the bulk collect clause.
    You can fetch into multiple collections with one column each.
    You can use the limit clause of bulk collect to restrict the no of rows retrieved.
   


SELECT * FROM product_details;

-->-- Bulk collect in FETCH INTO --<--


DECLARE
  TYPE alldata_typ IS TABLE OF product_details%ROWTYPE;
  dta alldata_typ;
  CURSOR proddt_cur IS
    SELECT * FROM product_details;
BEGIN
  OPEN proddt_cur;
  FETCH proddt_cur BULK COLLECT
    INTO dta;
  CLOSE proddt_cur;

  FOR i IN dta.FIRST .. dta.COUNT LOOP
    dbms_output.put_line(dta(i).p_name || ' department id is ' || dta(i).p_id);
  END LOOP;
END;
/


-->-- Bulk collect in SELECT clause --<--

DECLARE
  TYPE alldata_typ IS TABLE OF product_details%ROWTYPE;
  dta alldata_typ;
BEGIN
  SELECT * BULK COLLECT INTO dta FROM product_details;
  FOR i IN dta.FIRST .. dta.LAST LOOP
    dbms_output.put_line(dta(i).p_name || ' department id is ' || dta(i).p_id);
  END LOOP;
END;



-->-- LIMIT in Bulk collect --<--

DECLARE
  TYPE alldata_typ IS TABLE OF product_details%ROWTYPE;
  dta alldata_typ;
  CURSOR proddt_cur IS
    SELECT * FROM product_details;
BEGIN
  OPEN proddt_cur;
  FETCH proddt_cur BULK COLLECT
    INTO dta LIMIT 30;
  CLOSE proddt_cur;

  FOR i IN dta.FIRST .. dta.COUNT LOOP
    dbms_output.put_line(dta(i).p_name || ' department id is ' || dta(i).p_id);
  END LOOP;
END;
/



-->-- Multiple fetches in INTO clause --<--

SELECT * FROM product_details;

DECLARE
  TYPE alldata_typ1 IS TABLE OF product_details.p_id%TYPE;
  TYPE alldata_typ2 IS TABLE OF product_details.p_name%TYPE;
  dta1 alldata_typ1;
  dta2 alldata_typ2;
  CURSOR proddt_cur IS
    SELECT p_id, p_name FROM product_details;
BEGIN
  OPEN proddt_cur;
  FETCH proddt_cur BULK COLLECT
    INTO dta1, dta2;
  CLOSE proddt_cur;

  FOR i IN dta1.FIRST .. dta1.COUNT LOOP
    dbms_output.put_line('Department Id : ' || dta1(i));
  END LOOP;

  FOR i IN dta2.FIRST .. dta2.COUNT LOOP
    dbms_output.put_line('Department Name : ' || dta2(i));
  END LOOP;

END;
/


DROP TABLE dep_details;

DROP TABLE product_details;