Advanced cursors

Introduction to Oracle SQL & PL/SQL
Updated 03/20/2005

Cursor FOR Loops

The cursor FOR loop provides an elegant, simple syntax to to iterate over a result set.  To underscore the advantages of cursor FOR loops, consider the following PL/SQL block which uses a basic loop.


SET SERVEROUTPUT ON
DECLARE
-- EMP_CURSOR will retrieve all columns and all rows from the EMP table
CURSOR emp_cursor IS
SELECT *
FROM emp;
emp_record emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor;
LOOP
--Advance the pointer in the result set, assign row values to EMP_RECORD
FETCH emp_cursor INTO emp_record;
--Test to see if no more results
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(emp_record.ename||' [' ||emp_record.empno||']');
END LOOP;
CLOSE emp_cursor;
END;
/

DECLARE
-- EMP_CURSOR will retrieve all columns and all rows from the EMP table
CURSOR emp_cursor IS
SELECT *
FROM emp;
BEGIN
FOR emp_record IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(emp_record.ename||' ['||emp_record.empno||']');
END LOOP;
END;
/

BEGIN
FOR emp_record IN (SELECT * FROM emp) LOOP
DBMS_OUTPUT.PUT_LINE(emp_record.ename||' ['||emp_record.empno||']');
END LOOP;
END;
/


Parameterized Cursors (passong parameters to cursors)

DECLARE
v_deptno NUMBER;
v_job VARCHAR2(15);
v_sum_sal NUMBER;

/* Since v_deptno and v_job are declared above, they are in scope,
* and can be referenced in the cursor body. They will be used as
* placeholders until the cursor is opened, at which
* point the values of the variables will be substituted (bound)
* into the query as literals.
*/
CURSOR emp_stats_cursor IS
SELECT SUM(sal) sum_sal
FROM emp
WHERE deptno=v_deptno
AND job=v_job;
BEGIN
v_deptno:=10;
v_job:='MANAGER';

OPEN emp_stats_cursor;
/* When the cursor is opened, the values of the PL/SQL
* variables are bound into the query.
* In this example, the cursor would return the
* result set using the following query:
SELECT SUM(sal) sum_sal
FROM emp
WHERE deptno=10 --current value of v_deptno is 10
AND job='MANAGER'; --current value of v_job is 'MANAGER'
*/

FETCH emp_stats_cursor INTO v_sum_sal;
CLOSE emp_stats_cursor;
DBMS_OUTPUT.PUT_LINE(v_deptno||' : '||v_job||' : '||v_sum_sal);

v_deptno:=30;
v_job:='SALESMAN';

OPEN emp_stats_cursor;
/* In this example, the cursor would
* return the result set using the following query:

SELECT SUM(sal) sum_sal
FROM emp
WHERE deptno=30 --current value of v_deptno is 30
AND job='SALESMAN'; --current value of v_job is 'SALESMAN'
*/

FETCH emp_stats_cursor INTO v_sum_sal;
CLOSE emp_stats_cursor;
DBMS_OUTPUT.PUT_LINE(v_deptno||' : '||v_job||' : '||v_sum_sal);
END;
/

DECLARE

v_sum_sal NUMBER;

/* The parameters are declared in the cursor declaration.
* Parameters have a datatype, but NO SIZE; that is, you
* can declare a parameter of datatype VARCHAR2, but never
* VARCHAR2(20).
* As above, the parameters will be placeholders (that is,
* formal parameters) until the cursor is opened, at which
* point the actual parameter values will be substituted
* (bound) into the query as literals.
* The parameters are in scope (that is, they can be referenced)
* only inside the cursor body.
*/

CURSOR emp_stats_cursor(cp_deptno NUMBER, cp_job VARCHAR2) IS
SELECT SUM(sal) sum_sal
FROM emp
WHERE deptno=cp_deptno
AND job=cp_job;

BEGIN

OPEN emp_stats_cursor(10,'MANAGER');
/* When the cursor is opened, the values of the parameters
* are bound into the query. In other words, the actual parameters
* (values) will replace the formal parameters (placeholders).
* In this example, the cursor would return the result set using
* the following query:

SELECT SUM(sal) sum_sal
FROM emp
WHERE deptno=10
AND job='MANAGER';
*/

FETCH emp_stats_cursor INTO v_sum_sal;
CLOSE emp_stats_cursor;

DBMS_OUTPUT.PUT_LINE('10 : MANAGER : '||v_sum_sal);

OPEN emp_stats_cursor(30,'SALESMAN');
/* In this example, the cursor would return the result set
* using the following query:

SELECT SUM(sal) sum_sal
FROM emp
WHERE deptno=30
AND job='SALESMAN';
*/

FETCH emp_stats_cursor INTO v_sum_sal;
CLOSE emp_stats_cursor;
DBMS_OUTPUT.PUT_LINE('30 : SALESMAN : '||v_sum_sal);
END;
/

DECLARE
v_sum_sal NUMBER;
CURSOR emp_stats_cursor(cp_deptno NUMBER, cp_job VARCHAR2) IS
SELECT SUM(sal) sum_sal
FROM emp
WHERE deptno=cp_deptno
AND job=cp_job;

BEGIN
FOR dept_job_rec IN (SELECT DISTINCT deptno,job FROM emp) LOOP
OPEN emp_stats_cursor(dept_job_rec.deptno, dept_job_rec.job);
FETCH emp_stats_cursor INTO v_sum_sal;
CLOSE emp_stats_cursor;
DBMS_OUTPUT.PUT_LINE(dept_job_rec.deptno ||' : '||dept_job_rec.job||' : '||v_sum_sal);
END LOOP;
END;
/

Ref Cursor

Ref Cursor is THE method to returns result sets to client applications (like C, VB, etc).
You cannot define ref cursors outside of a procedure or function in a package specification or body. Ref cursors can only be processed in the defining procedure or returned to a client application. Also, a ref cursor can be passed from subroutine to subroutine and a cursor cannot. To share a static cursor like that, you would have to define it globally in a package specification or body. Because using global variables is not a very good coding practice in general, ref cursors can be used to share a cursor in PL/SQL without having global variables getting into the mix.

Last, using static cursors—with static SQL (and not using a ref cursor) —is much more efficient than using ref cursors, and the use of ref cursors should be limited to
In short, you want to use static SQL first and use a ref cursor only when you absolutely have to.An Example of Ref cursor is here:

create or replace function sp_ListEmp return types.cursortype
as
    l_cursor types.cursorType;
begin
    open l_cursor for select ename, empno from emp order by ename;
    return l_cursor;
end;
/

Or like this for a procedure:
create or replace procedure getemps( p_cursor in out types.cursorType )
as
begin
      open p_cursor for select ename, empno from emp order by ename;
end;


Cursor variables are cursors opened by a pl/sql routine and fetched from by another application or pl/sql routine (in 7.3 pl/sql routines can fetch from cursor variables as well as open them). The cursor variables are opened with the privelegs of the owner of the procedure and behave just like they were completely contained within the pl/sql routine. It uses the inputs to decide what database it will run a query on. Here is an example:

create or replace package types
as
    type cursorType is ref cursor;
end;
/

create or replace function sp_ListEmp return types.cursortype
as
    l_cursor    types.cursorType;
begin
    open l_cursor for select ename, empno from emp order by ename;
    return l_cursor;
end;
/
create or replace procedure getemps( p_cursor in out types.cursorType )
as
begin
      open p_cursor for select ename, empno from emp order by ename;
end;
/


BULK COLLECT INTO
Introduced in Oracle8i, BULK COLLECT allows you to retrieve multiple rows of data directly into PL/SQL Collections. It will raise NO_DATA_FOUND if it doesn't find any rows, but it certainly doesn't raise TOO_MANY_ROWS if it finds more than one!
More information can be obtained HERE.

DECLARE
   TYPE title_aat IS TABLE OF magazine.title%TYPE
      INDEX BY BINARY_INTEGER;

   l_titles   title_aat;
BEGIN
   SELECT title
   BULK COLLECT INTO l_titles
     FROM magazine;
END;


My advice regarding the kinds of cursors to use when fetching data from Oracle in PL/SQL programs it to whenever possible, use BULK COLLECT—it offers dramatic performance gains. In Oracle9i Release 2, you can even use BULK COLLECT to fetch multiple rows directly into a collection of records. Of course, when you want/need to fetch just a single row, BULK COLLECT doesn't make sense.