Oracle DBA FAQ - Working with Cursors in PL/SQL

Home >> FAQs/Tutorials >> Oracle DBA FAQ

By: FYIcenter.com

Part: 1 2 3 4 5

(Continued from previous part...)

How To Pass a Parameter to a Cursor?

When you define a cursor, you can set a formal parameter in the cursor. The formal parameter will be replaced by an actual parameter in the OPEN cursor statement. Here is a good example of a cursor with two parameters:

CREATE OR REPLACE PROCEDURE FYI_CENTER AS
  CURSOR emp_cur(low NUMBER, high NUMBER)
    IS SELECT * FROM employees WHERE salary >= low
      AND salary <= high;
BEGIN
  FOR row IN emp_cur(12000,15000) LOOP
    DBMS_OUTPUT.PUT_LINE(row.first_name || ' '
      || row.last_name 
      || ': ' || row.salary); 
  END LOOP; 
END;
/
Nancy Greenberg: 12000
John Russell: 14000
Karen Partners: 13500
Alberto Errazuriz: 12000
Michael Hartstein: 13000
Shelley Higgins: 12000

What Is a Cursor Variable?

A cursor variable is a variable of a specific REF CURSOR data type, which is a pointer to a data structure resource connects to query statement result, similar to the CURSOR data type.. The advantage of using cursor variables is that cursor variables can be used as variables to pass between procedures and functions.

How To Define a Cursor Variable?

To define cursor variable, you must decide which REF CURSOR data type to use. There are 3 ways to select a REF CURSOR data type:

Define your own specific REF CURSOR types using the TYPE ... RETURN statement.
Define your own generic REF CURSOR type using the TYPE ... statement.
Use the system defined REF CURSOR type: SYS_REFCURSOR.

The follwoing tutorial exercise defines 3 cursor variables in 3 different ways:

CREATE OR REPLACE PROCEDURE FYI_CENTER AS
  TYPE emp_ref IS REF CURSOR RETURN employees%ROWTYPE;
  TYPE any_ref IS REF CURSOR;
  emp_cur emp_ref;
  any_cur any_ref;
  sys_cur SYS_REFCURSOR;
BEGIN
  NULL;
END;
/

How To Open a Cursor Variable?

A cursor variable must be opened with a specific query statement before you can fetch data fields from its data rows. To open a cursor variable, you can use the OPEN ... FOR statement as shown in the following tutorial exercise:

CREATE OR REPLACE PROCEDURE FYI_CENTER AS
  TYPE emp_ref IS REF CURSOR RETURN employees%ROWTYPE;
  TYPE any_ref IS REF CURSOR;
  emp_cur emp_ref;
  any_cur any_ref;
  sys_cur SYS_REFCURSOR;
BEGIN
  OPEN emp_cur FOR SELECT * FROM employees;
  OPEN any_cur FOR SELECT * FROM employees;
  OPEN sys_cur FOR SELECT * FROM employees;
  CLOSE sys_cur;
  CLOSE any_cur;
  CLOSE emp_cur;
END;
/

How To Loop through a Cursor Variable?

Once a cursor variable is opened with a query statement, it will have the same attributes as a normal cursor and it can be used in the same way a normal cursor too. The following sample script shows you how to loop through a cursor variable:

CREATE OR REPLACE PROCEDURE FYI_CENTER AS
  TYPE emp_ref IS REF CURSOR RETURN employees%ROWTYPE;
  emp_cur emp_ref;
  emp_rec employees%ROWTYPE;
BEGIN
  OPEN emp_cur FOR SELECT * FROM employees
    WHERE manager_id = 101;
  LOOP
    FETCH emp_cur INTO emp_rec;
    EXIT WHEN emp_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Name = ' || 
      emp_rec.first_name || ' ' || emp_rec.last_name); 
  END LOOP; 
  CLOSE emp_cur;
END;
/
Name = Nancy Greenberg
Name = Jennifer Whalen
Name = Susan Mavris
Name = Hermann Baer
Name = Shelley Higgins

(Continued on next part...)

Part: 1 2 3 4 5

More...