How to use PL/SQL Cursor Variable with REF CURSOR In SQL
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.
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