Oracle BULK COLLECT with example

Oracle BULK COLLECT technique enables the PL/SQL engine to collect many rows at once and place them in a collection of array.
A collection is an ordered group of elements, all of the same type. It is a general concept that encompasses lists, arrays, and other familiar datatypes.
Each element has a unique subscript that determines its position in the collection.

BULK COLLECT improves performance of queries that retrieve more than one row.
BULK COLLECT also leave it to the optimizer to speed up "read only" cursor FOR loops.
Use the LIMIT clause to avoid excessive PGA memory consumption.

 e.g. 
SELECT * BULK COLLECT INTO collection(s) FROM table;
FETCH cur BULK COLLECT INTO collection(s);
EXECUTE IMMEDIATE query BULK COLLECT INTO collection(s);

Example :
==========================================================
CREATE OR REPLACE PROCEDURE XXBULKCOLLECTTEST
IS
--
--Declare a nested table of records to hold the queried data.
TYPE EMP_COL_REC IS RECORD
    (
    EMPNO_R EMP.EMPNO%TYPE,
    ENAME_R EMP.ENAME%TYPE,
    JOB_R EMP.JOB%TYPE,
    MGR_R EMP.MGR%TYPE,
    HIREDATE_R EMP.HIREDATE%TYPE,
    COMM_R EMP.COMM%TYPE,
    DEPTNO_R EMP.DEPTNO%TYPE,
    SAL_R EMP.SAL%TYPE
    );
--  
TYPE EMP_INFO IS TABLE OF EMP_COL_REC;
P_EMP_INFO EMP_INFO;
--
BEGIN
--  
    SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO, SAL
    BULK COLLECT INTO P_EMP_INFO
    FROM EMP
    WHERE DEPTNO=10;
--
  FOR I IN P_EMP_INFO.FIRST .. P_EMP_INFO.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPNO '||P_EMP_INFO(I).EMPNO_R||'  '
                         ||'SAL '||P_EMP_INFO(I).SAL_R||'  '
                         ||'ENAME '||P_EMP_INFO(I).ENAME_R||'  '
                         ||'JOB '||P_EMP_INFO(I).JOB_R||'  '
                         ||'MGR '||P_EMP_INFO(I).MGR_R||'  '
                         ||'HIREDATE '||P_EMP_INFO(I).HIREDATE_R||'  ');
  END LOOP;
--
END;
=================================================================
BULK COLLECT WITH LIMIT

SAMPLE SCRIPT:

CREATE OR REPLACE PROCEDURE XXBULKCOLLECTLIMIT
IS
--
CURSOR EMP_CUR
IS
SELECT *  FROM EMP;
--  
TYPE EMP_CUR_TBL IS TABLE OF EMP_CUR%ROWTYPE
INDEX BY PLS_INTEGER;
--
EMP_TBL EMP_CUR_TBL;
--
limit_in PLS_INTEGER:=5;
--
BEGIN
--  
OPEN EMP_CUR;
LOOP
  FETCH EMP_CUR
  BULK COLLECT INTO EMP_TBL LIMIT limit_in;
--
  FOR I IN 1 .. EMP_TBL.COUNT
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPNO '||EMP_TBL(I).EMPNO||'  '
                         ||'SAL '||EMP_TBL(I).SAL||'  '
                         ||'ENAME '||EMP_TBL(I).ENAME||'  '
                         ||'JOB '||EMP_TBL(I).JOB||'  '
                         ||'MGR '||EMP_TBL(I).MGR||'  '
                         ||'HIREDATE '||EMP_TBL(I).HIREDATE||'  ');
  END LOOP;
--
  EXIT WHEN EMP_TBL.COUNT <= limit_in;
  END LOOP;
  CLOSE EMP_CUR;
--

END;