Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / PL/SQL Tutorial / PL/SQL Cursor Variables with REF CURSOR

PL/SQL Cursor Variables with REF CURSOR

Summary: in this tutorial, you will learn about PL/SQL cursor variables and how to manage cursors variables using REF CURSOR.

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 REF CURSOR.

The following shows an example of a strong REF CURSOR.

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

The following function gets all direct reports of a manager based on the manager id from the employees table in the sample database. The function returns a weak typed REF CURSOR variable:

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.

  • Was this tutorial helpful?
  • YesNo
Previous PL/SQL Cursor with Parameters
Next Oracle CURSOR FOR UPDATE

PL/SQL Getting Started

  • What is PL/SQL
  • PL/SQL Anonymous Block
  • PL/SQL Data Types
  • PL/SQL Variables
  • PL/SQL Comments
  • PL/SQL Constants

PL/SQL Conditional Control

  • PL/SQL IF THEN
  • PL/SQL CASE
  • PL/SQL GOTO
  • PL/SQL NULL Statement

PL/SQL Loops

  • PL/SQL LOOP
  • PL/SQL FOR LOOP
  • PL/SQL WHILE Loop
  • PL/SQL CONTINUE

PL/SQL Select Into

  • PL/SQL SELECT INTO

PL/SQL Exception Handlers

  • PL/SQL Exception
  • PL/SQL Exception Propagation
  • PL/SQL RAISE Exceptions
  • RAISE_APPLICATION_ERROR

PL/SQL Records

  • PL/SQL Record

PL/SQL Cursors

  • PL/SQL Cursor
  • PL/SQL Cursor FOR LOOP
  • PL/SQL Cursor with Parameters
  • PL/SQL Updatable Cursor

PL/SQL Procedures & Functions

  • PL/SQL Procedure
  • PL/SQL Function
  • PL/SQL Cursor Variables

PL/SQL Packages

  • PL/SQL Package
  • PL/SQL Package Specification
  • PL/SQL Package Body

PL/SQL Triggers

  • PL/SQL Triggers
  • PL/SQL Statement-level Triggers
  • PL/SQL Row-level Triggers
  • PL/SQL INSTEAD OF Triggers
  • PL/SQL Disable Triggers
  • PL/SQL Enable Triggers
  • PL/SQL Drop Triggers
  • Oracle Mutating Table Error

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.