Summary: in this tutorial, you will learn about PL/SQL cursor variables and how to manage cursors variables using
Introduction to PL/SQL cursor variables
A cursor variable is a variable that references to a cursor. Different from implicit and explicit cursors, a cursor variable is not tied to any specific query. Meaning 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 simply 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
The following shows an example of a strong
DECLARE TYPE customer_t IS REF CURSOR RETURN customers%ROWTYPE; c_customer customer_t;
This form of cursor variable 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;
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;
PL/SQL cursor variable examples
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;
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; /
In this tutorial, you have learned how to use PL/SQL cursor variables with
REF CURSOR type.