PL/SQL Cursor Variables with REF CURSOR

Summary: in this tutorial, you will learn about PL/SQL cursor variables and how to manage cursors variables using REF CURSOR.

Introduction to PL/SQL cursor variables

A cursor variable is a variable that references to a cursor. Different from implicit and explicit cursors, a cursor variable is not tied to any specific query. Meaning that a cursor variable can be opened for any query.

The most important benefit of a cursor variable is that it enables passing the result of a query between PL/SQL programs. Without a cursor variable, you have to fetch all data from a cursor, store it in a variable e.g., a collection, and pass this variable as an argument. With a cursor variable, you simply pass the reference to that cursor.

To declare a cursor variable, you use the REF CURSOR is the data type. PL/SQL has two forms of REF CURSOR typeS: strong typed and weak typed REF CURSOR.

The following shows an example of a strong REF CURSOR.

DECLARE TYPE customer_t IS REF CURSOR RETURN customers%ROWTYPE; c_customer customer_t;
Code language: SQL (Structured Query Language) (sql)

This form of cursor variable called strong typed REF CURSOR because the cursor variable is always associated with a specific record structure, or type.

And here is an example of a weak typed REF CURSOR declaration that is not associated with any specific structure:

DECLARE TYPE customer_t IS REF CURSOR; c_customer customer_t;
Code language: SQL (Structured Query Language) (sql)

Starting from Oracle 9i, you can use SYS_REFCURSOR, which is a predefined weak typed REF CURSOR, to declare a weak REF CURSOR as follows:

Code language: SQL (Structured Query Language) (sql)

PL/SQL cursor variable examples

The following function gets all direct reports of a manager based on the manager id from the employees table in the sample database. The function returns a weak typed REF CURSOR variable:

CREATE OR REPLACE FUNCTION get_direct_reports( in_manager_id IN employees.manager_id%TYPE) RETURN SYS_REFCURSOR AS c_direct_reports SYS_REFCURSOR; BEGIN OPEN c_direct_reports FOR SELECT employee_id, first_name, last_name, email FROM employees WHERE manager_id = in_manager_id ORDER BY first_name, last_name; RETURN c_direct_reports; END;
Code language: SQL (Structured Query Language) (sql)

The following anonymous block calls the get_direct_reports() function and processes the cursor variable to display the direct reports of the manager with id of 46.

DECLARE c_direct_reports SYS_REFCURSOR; l_employee_id employees.employee_id%TYPE; l_first_name employees.first_name%TYPE; l_last_name employees.last_name%TYPE; l_email; BEGIN -- get the ref cursor from function c_direct_reports := get_direct_reports(46); -- process each employee LOOP FETCH c_direct_reports INTO l_employee_id, l_first_name, l_last_name, l_email; EXIT WHEN c_direct_reports%notfound; dbms_output.put_line(l_first_name || ' ' || l_last_name || ' - ' || l_email ); END LOOP; -- close the cursor CLOSE c_direct_reports; END; /
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use PL/SQL cursor variables with REF CURSOR type.

Was this tutorial helpful?