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 #

In PL/SQL, a REF CURSOR is short for reference cursor, which is a pointer to a query’s result.

A REF CURSOR allows to dynamimcally opens and fetches data from a query at runtime. Typically, you use the REF CURSOR to return query results from functions or stored procedures.

To declare a referencce cursor variable, you use the REF CURSOR as the data type.

PL/SQL supports two kinds of REF CURSOR types:

• Strong typed reference cursor ties to a specific query structure.
• Weak typed referencce cursor can point to any query.

For example, the following shows how to define 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, which is customers in this ccase.

Note that we use the customers table from the sample database.

Here’s 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 #

We’ll use the customers table from the sample database:

First, define a function that gets all direct reports of a manager based on the manager id from the employees table in the sample database.

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 function returns a weak typed REF CURSOR variable.

Second, call the get_direct_reports() function and processes the cursor variable to display the manager’s direct reports with the id of 46 using an anonymous block:

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)

Summary #

• Use reference cursors (REF CURSOR) to return result sets from fuctions or stored procedures.