How to use Passing Cursor Variable as Parameter to a Procedure In SQL
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.
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.
Contact Us