Advanced Cursors

Advanced cursors

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

Cursor FOR Loops
Parameterized Cursors (passing parameters to cusors)
Ref Cursor
BULK COLLECT INTO

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;
/

Now examine the same query using a cursor FOR loop:

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;
/

You can use a cursor for loop without a declared cursor by including a query in the FOR statement. This can enable very compact code.

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

While you can use EXIT statement within a FOR cursor loop, you should not use a cursor FOR loop if you may need to exit the LOOP prematurely. Use a basic or WHILE loop instead.

Parameterized Cursors (passong parameters to cursors)

Cursors can use variables to adjust which rows they select when opened. Instead of hard-coding a value into the WHERE clause of a query, you can use a variable as a placeholder for a literal value. The variable placeholder will substituted with the value of the variable when the cursor is opened. This makes a query more flexible.

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;
/

This method works, but there is a better way. You can declare a cursor using parameters; then whenever you open the cursor, you pass in appropriate parameters. This technique is just as flexible, but is easier to maintain and debug. The above example adapted to use a parameterized cursor:

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;
/

Parameterized cursors are open easier to debug in larger PL/SQL blocks. This is because the the declaration of the cursor body is often far from where the cursor is opened, but processing of the cursor's result set is usually close to where the cursor is opened.
- When opening a cursor which uses variables, you must assign appropriate values to those variables before opening the cursor. So when you are debugging how the cursor is opened, you must confirm the appropriate variable names where the cursor is declared . This is often inconvenient.
- When using PL/SQL variables, it's difficult to confirm the values of the variables when the cursor is opened because the values could be set at an any point from the declaration on.
Parameterized cursors eliminate both these problems because the values used in the cursor can be determined in one place, the OPEN statement. And you don't need to know the names of the cursor parameters. (Though you do need to know the order of the parameters.)
An example which combines a cursor FOR loop with a parameterized query:

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). A ref cursor is a variable, defined as a cursor type, which will point to, or reference a cursor result. The advantage that a ref cursor has over a plain cursor is that is can be passed as a variable to a procedure or a function.
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

Returning result sets to clients
Sharing cursors across many subroutines (very similar to the above point, actually)
Achieving your goal when there is no other efficient/effective means of doing so, such as when dynamic SQL must be used

In short, you want to use static SQL first and use a ref cursor only when you absolutely have to.
Before assigning a cursor variable, a cursor type must be defined.
type author_cursor is ref cursor;

This REF CURSOR is a weak typed cursor variable because it does not define the datatype the cursor will return. Below is the same cursor that is strongly typed.
type author_cursor is ref cursor returning author%rowtype;

Once the cursor type is defined, the actual variable can be defined as the cursor type.
c1 author_cursor;

Now c1 is a variable of a cursor type. It is opened using a SQL statement.
open c1 for select * from authors;
Now c1 has all the attributes of the actual cursor. As with any cursor it is important to close the cursor as soon as you have completed processing.

Very simple example:
declare
type auth_cursor is ref cursor;
c1 auth_cursor;
r_c1 author%rowtype;
begin
open c1 for select * from author;
fetch c1 into r_c1;
    if c1%isopen then
      dbms_output.put_line('The Cursor is open.');
    end if;
    dbms_output.put_line('Row Count is '||c1%rowcount);
close c1;
if not c1%isopen then
   dbms_output.put_line('The Cursor is closed.');
end if;
end;
/
The Cursor is open.
Row Count is 1
The Cursor is closed.

Another 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.