PL/SQL Cursor Variables with REF CURSOR

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

Introduction to PL/SQL cursor variables

A cursor variable is a variable that references a cursor. Unlike implicit and explicit cursors, a cursor variable is not tied to any specific query. This means 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 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: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

This form of cursor variable is 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: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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:

DECLARE
    c_customer SYS_REFCURSOR;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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       employees.email%TYPE;
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: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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