PL/SQL Cursor Variable with REF CURSOR

Cursor variables, also known as REF CURSORs, in PL/SQL, provide a dynamic and flexible means to handle query results. A cursor variable is a reference to a cursor, which can be opened, fetched, and closed dynamically at runtime.

In this article, we’ll delve into the usage of cursor variables with REF CURSOR in PL/SQL and showcase their versatility in managing dynamic queries and result sets.

Why We Use Ref Cursor in PL/SQL?

Cursor variables with REF CURSOR are important in PL/SQL programming for below reasons:

Dynamic SQL: They allow the creation and execution of dynamic SQL queries. This is useful when the structure of the query or the tables involved is not known at compile time.
Reusability: Cursor variables can be reused across different parts of the program reducing code duplication and improving maintainability.
Parameter Passing: They can be used to pass query results between different program units such as stored procedures or functions and enable more modular and flexible code.
Data Manipulation: They enable complex data manipulation operations that may involve multiple queries or data sources.

What is the Cursor Variable with REF CURSOR?

Cursor variables in PL/SQL provide a means to work with dynamic SQL queries and results. Unlike explicit cursors, cursor variables allow the definition of a cursor without specifying the SQL query at compile-time. This flexibility is particularly useful when dealing with varying queries or when the query needs to be determined dynamically during runtime. REF CURSORs, associated with cursor variables enable the retrieval of query results. The below method helps to understand the Cursor Variable with REF CURSOR very effectively.

Using Cursor Variable to Fetch Data Dynamically
Using Passing Cursor Variable as Parameter to a Procedure

Let’s understand both methods with the help of examples

1. Using PL/SQL Cursor Variable with REF CURSOR

Example: Let’s create a PL/SQL block that utilizes a cursor variable with REF CURSOR to dynamically fetch and display data from the “employees” table. The PL/SQL block should open a cursor for a dynamic query that selects all columns from the “employees” table, fetch the data into variables for “employee_id” and “employee_name“, and then display each employee’s ID and name using the DBMS_OUTPUT.PUT_LINE function. Finally the cursor should be closed to release resources.

Query:

-- Sample Data
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50)
);

INSERT INTO employees VALUES (1, 'John Doe');
INSERT INTO employees VALUES (2, 'Jane Smith');

-- PL/SQL Block with Cursor Variable
DECLARE
    TYPE ref_cursor_type IS REF CURSOR;
    cursor_variable ref_cursor_type;
    emp_id employees.employee_id%TYPE;
    emp_name employees.employee_name%TYPE;
BEGIN
    -- Dynamic Query using Cursor Variable
    OPEN cursor_variable FOR 'SELECT * FROM employees';
    
    -- Fetch and Display Data
    LOOP
        FETCH cursor_variable INTO emp_id, emp_name;
        EXIT WHEN cursor_variable%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_id || ', Employee Name: ' || emp_name);
    END LOOP;

    -- Close Cursor
    CLOSE cursor_variable;
END;
/

Output:

Employee ID	Employee Name
1	John Doe
2	Jane Smith

Explanation:

The cursor variable cursor_variable is dynamically opened for the query ‘SELECT * FROM employees’.
The loop fetches and displays the data from the result set.

2. Using Passing Cursor Variable as Parameter to a Procedure

Let’s create a PL/SQL procedure named “display_employee_data” that accepts a cursor variable as an IN OUT parameter. The procedure should fetch and display employee data (employee_id and employee_name) from the cursor variable. Additionally, we need to create a PL/SQL block that opens a cursor for a dynamic query selecting all columns from the “employees” table. The block should call the “display_employee_data” procedure with the cursor variable as a parameter and then close the cursor to release resources.

Query:

-- Procedure Accepting Cursor Variable as Parameter
CREATE OR REPLACE PROCEDURE display_employee_data (
    p_cursor_variable IN OUT SYS_REFCURSOR
)
IS
    emp_id employees.employee_id%TYPE;
    emp_name employees.employee_name%TYPE;
BEGIN
    -- Fetch and Display Data
    LOOP
        FETCH p_cursor_variable INTO emp_id, emp_name;
        EXIT WHEN p_cursor_variable%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_id || ', Employee Name: ' || emp_name);
    END LOOP;
END;
/

-- Sample Data
INSERT INTO employees VALUES (3, 'Bob Johnson');
INSERT INTO employees VALUES (4, 'Alice Williams');

-- PL/SQL Block Calling Procedure with Cursor Variable
DECLARE
    TYPE ref_cursor_type IS REF CURSOR;
    cursor_variable ref_cursor_type;
BEGIN
    -- Dynamic Query using Cursor Variable
    OPEN cursor_variable FOR 'SELECT * FROM employees';
    
    -- Call Procedure with Cursor Variable as Parameter
    display_employee_data(p_cursor_variable => cursor_variable);

    -- Close Cursor
    CLOSE cursor_variable;
END;
/

Output:

Employee ID	Employee Name
1	John Doe
2	Jane Smith
3	Bob Johnson
4	Alice Williams

Explanation:

The procedure display_employee_data accepts a cursor variable as a parameter and fetches data from the provided result set.
The PL/SQL block calls this procedure with the cursor variable, displaying the combined data from both sets.

Conclusion

PL/SQL cursor variables with REF CURSORs provide a flexible and dynamic approach to working with query results. Their ability to handle varying queries and serve as parameters to procedures enhances the adaptability and reusability of PL/SQL code. By leveraging cursor variables, developers can create more dynamic, adaptable, and efficient PL/SQL programs, especially in scenarios where the structure of queries or the result sets may vary at runtime.

Article Tags :

Databases

PL/SQL

PL/SQL Query