BULK Collect

--BULK Collect Example----

SET SERVEROUTPUT ON
declare
cursor c1 is
select ename, sal, job from emp;

TYPE name is table of emp.ename%type;
TYPE sal  is table of emp.sal%type;
TYPE job  is table of emp.job%type;

names name ;
sals sal;
jobs job;

begin

open c1;
fetch c1 bulk collect into names, sals, jobs;
close c1;

FOR i in 1.. names.count LOOP
dbms_output.put_line (names (i));
dbms_output.put_line (sals (i));
dbms_output.put_line (jobs (i));
dbms_output.put_line ('------------------------');
END LOOP;
dbms_output.put_line (names (11));
dbms_output.put_line (sals (12));
dbms_output.put_line (jobs (13));

END;

OUTPUT :

SMITH
800
CLERK
------------------------
ALLEN
1600
SALESMAN
------------------------
WARD
1250
SALESMAN
------------------------
JONES
2975
MANAGER
------------------------
MARTIN
1250
SALESMAN
------------------------
BLAKE
2850
MANAGER
------------------------
CLARK
2450
MANAGER
------------------------
SCOTT
3000
ANALYST
------------------------
KING
5000
PRESIDENT
------------------------
TURNER
1500
SALESMAN
------------------------
ADAMS
1100
CLERK
------------------------
JAMES
950
CLERK
------------------------
FORD
3000
ANALYST
------------------------
MILLER
1300
CLERK
------------------------
ADAMS
950
ANALYST
PL/SQL procedure successfully completed.

/*Advantage of above example is user can point to a particular record
directly provided by cursor After closing the cursor and loop we can fetch a particular record.*/

How to Return Multiple Values and Rows with PL/SQL Function

CREATE OR REPLACE TYPE emp_details -- Collection
AS
   OBJECT (first_name VARCHAR2 (30),
           last_name VARCHAR2 (30),
           date_of_birth DATE);


create type emp is table of emp_details;


CREATE OR REPLACE FUNCTION xx_return_multiple_rows_f
   RETURN emp
AS
       l_emp_details    emp := emp();
BEGIN
    l_emp_details.extend;
    l_emp_details(1) := emp_details ('BIJOY', 'JOSEPH', to_date('12-MAR-1986','DD-MON-YYYY'));
    l_emp_details.extend;
    l_emp_details(2) := emp_details ('SUBHU', 'NAYAK', to_date('12-MAR-1986','DD-MON-YYYY'));
 
   RETURN l_emp_details;
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN NULL;
END;

****************************xxxxxxxxx*******************
To Retrieve the rows:

SET SERVEROUTPUT ON
Declare
   l_emp_details1    emp := emp();
   v_idx  pls_integer;
Begin
   l_emp_details1 := xx_return_multiple_rows_f;
   if l_emp_details1.count > 0 then
      v_idx := l_emp_details1.first;
      while v_idx is not null
      Loop
        dbms_output.put_line(l_emp_details1(v_idx).first_name);
        v_idx := l_emp_details1.next(v_idx);
      end loop;
   end if;
end;
/

****************************////***********************


create or replace function get_data ( type varchar2 ) return sys_refcursor as
  v1 sys_refcursor;
begin

  if type = 'EMP' then
    open v1 for select * from emp;
  elsif type = 'DEPT' then
    open v1 for select * from dept;
  end if;

return v1;
end;

Execution:

SQL> select get_data('EMP') FROM DUAL;

GET_DATA('EMP')
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

********

SQL> select get_data('DEPT') FROM DUAL;

GET_DATA('DEPT')
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON