WHAT IS BULK COLLECT & FORALL

• BULK COLLECT: SELECT statements that retrieve multiple rows with a single fetch, improving the speed of data retrieval
  => Started using BULK COLLECT whenever I need to fetch large volumes of data.
  (Specifically, memory for collections is stored in the program global area (PGA), not the system global area (SGA). SGA memory is shared by all sessions connected to Oracle Database, but PGA memory is allocated for each session. Thus, if a program requires 5MB of memory to populate a collection and there are 100 simultaneous connections, that program causes the consumption of 500MB of PGA memory, in addition to the memory allocated to the SGA.)
  
  Let it be thought I need to retrieve all the rows from the employees table and then perform some compensation analysis on each row. I can use BULK COLLECT as follows:
  
  

  PROCEDURE process_all_rows
  IS
     TYPE employees_aat 
     IS TABLE OF employees%ROWTYPE
        INDEX BY PLS_INTEGER;
     l_employees employees_aat;
  BEGIN
     SELECT *
     BULK COLLECT INTO l_employees
        FROM employees;
       
     FOR indx IN 1 .. l_employees.COUNT 
     LOOP
         analyze_compensation 
        (l_employees(indx));
     END LOOP;
  END process_all_rows; 

  
  
   => Fortunately, PL/SQL makes it easy for developers to control the amount of memory used in a BULK COLLECT operation by using the LIMIT clause.
  
  
   
• FORALL: INSERTs, UPDATEs, and DELETEs that use collections to change multiple rows of data very quickly

Cursor, ref cursor and SYS_Ref cursor.

CREATE OR REPLACE PROCEDURE get_emp_rs (p_deptno    IN  emp.DEPARTMENT_ID%TYPE, p_recordset OUT SYS_REFCURSOR)
AS
BEGIN
     OPEN p_recordset FOR
SELECT
     EMPLOYEE_ID,
     FIRST_NAME,
     LAST_NAME
     EMAIL,
     PHONE_NUMBER,
     HIRE_DATE,
     JOB_ID,
     SALARY,
     COMMISSION_PCT,
     MANAGER_ID,
     DEPARTMENT_ID
FROM   emp
     WHERE  DEPARTMENT_ID = p_deptno
     ORDER BY FIRST_NAME;
END get_emp_rs;
/

===============================================================================
print  out put :
SQL> exec get_emp_rs(p_deptno=>10,p_recordset=>:X);

PL/SQL procedure successfully completed.

SQL> set serveroutput on;SQL> exec get_emp_rs(p_deptno=>10,p_recordset=>:X);

PL/SQL procedure successfully completed.

SQL> print x;

EMPLOYEE_ID FIRST_NAME           EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_ID      SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- -------------------- --------- ---------- ---------- -------------- ---------- -------------
        200 Jennifer             Whalen                    515.123.4444         17-SEP-87 AD_ASST          4400                       101            10
========================================================================



LOOP:

declare
n number (4):=1;
begin
loop
dbms_output.put_line(n);
exit when n>=11;
n:=n+1;
end loop;
end;
/




Cursor;

declare
cursor c1 is select first_name from employees;
X employees.first_name%type;
begin
open c1;
if c1%isopen then
dbms_output.put_line('XXXXcursor opend');
loop
fetch c1  into X ;
if C1%found then
dbms_output.put_line(X||', '||C1%rowcount);
dbms_output.put_line(C1%rowcount);
else
exit;
end if;
end loop;
end if;
close c1;
end;
/


======================

declare
cursor c1 (employee_no number ) is select * from employees where employee_id=employee_no;
X employees%ROWtype;
begin
open c1(&employee_no);
if c1%isopen then
dbms_output.put_line('XXXXcursor opend');
loop
fetch c1  into X ;
if C1%found then
dbms_output.put_line(X.employee_id||X.first_name);
dbms_output.put_line(C1%rowcount);
else
exit;
end if;
end loop;
end if;
close c1;
end;
/
======================================================