PL/SQL - Cursor Variables (also known as REF CURSORs)

About

With 7.2 on up of the database you have cursor variables. Cursor variables are cursors opened by a pl/sql routine and fetched from by another application or pl/sql routine.

The cursor variables are opened with the privileges of the owner of the procedure and behave just like they were completely contained within the pl/sql routine.

A cursor variable holds the memory location of a query work area, rather than the contents of the area.

Declaring a cursor variable creates a pointer. In SQL, a pointer has the data type REF x, where REF is short for REFERENCE and x represents the entity being referenced. A REF CURSOR, then, identifies a reference to a cursor variable. Because many cursor variables might exist to point to many work areas, REF CURSOR can be thought of as a category or data type specifier that identifies many different types of cursor variables. A REF CURSOR essentially encapsulates the results of a query.

Like a cursor, a cursor variable points to the current row in the result set of a multi-row query.

Articles Related

Definition

A cursor variable is :

• is like a pointers to result sets
  • You use them when you want to perform a query in one subprogram, and process the results in a different subprogram (possibly one written in a different language). A cursor variable has datatype REF CURSOR, and you might see them referred to informally as REF CURSORs.
• a parameter that you can pass to local and stored subprograms.
  • Opening the cursor variable in one subprogram, and processing it in a different subprogram, helps to centralize data retrieval.
• is useful for multi-language applications, where a PL/SQL subprogram might return a result set to a subprogram written in a different language, such as Java or Visual Basic.
• is useful for communication client server.
  • Cursor variables are available to every PL/SQL client. For example, you can declare a cursor variable in a PL/SQL host environment such as an OCI or Pro*C program, then pass it as an input host variable (bind variable) to PL/SQL. Application development tools such as Oracle Forms, which have a PL/SQL engine, can use cursor variables entirely on the client side. Or, you can pass cursor variables back and forth between a client and the database server through remote procedure calls.
  • If you have a PL/SQL engine on the client side, calls from client to server impose no restrictions. For example, you can declare a cursor variable on the client side, open and fetch from it on the server side, then continue to fetch from it back on the client side. You can also reduce network traffic by having a PL/SQL block open or close several host cursor variables in a single round trip.
• more flexible because it is not tied to a specific query.

Management

Declaration

To create a cursor variable, either:

• declare a variable of the predefined type SYS_REFCURSOR
• or define a REF CURSOR type and then declare a variable of that type.

REF CURSOR type

Define a REF CURSOR type, then declare cursor variables of that type.

DECLARE
  TYPE DeptCurTyp IS REF CURSOR RETURN departments%ROWTYPE;

REF CURSOR types can be :

• strong (with a return type)

Strong REF CURSOR types are less error prone because the PL/SQL compiler lets you associate a strongly typed cursor variable only with queries that return the right set of columns.

• or weak (with no return type).

Weak REF CURSOR types are more flexible because the compiler lets you associate a weakly typed cursor variable with any query. Because there is no type checking with a weak REF CURSOR, all such types are interchangeable. Instead of creating a new type, you can use the predefined type SYS_REFCURSOR.

Once you define a REF CURSOR type, you can declare cursor variables of that type in any PL/SQL block or subprogram.

DECLARE
   TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;  -- strong
   TYPE genericcurtyp IS REF CURSOR;  -- weak
   cursor1 empcurtyp;
   cursor2 genericcurtyp;
   my_cursor SYS_REFCURSOR; -- didn't need to declare a new type
   TYPE deptcurtyp IS REF CURSOR RETURN departments%ROWTYPE;
   dept_cv deptcurtyp;  -- declare cursor variable

To avoid declaring the same REF CURSOR type in each subprogram that uses it, you can put the REF CURSOR declaration in a package spec. You can declare cursor variables of that type in the corresponding package body, or within your own procedure or function.

In the RETURN clause of a REF CURSOR type definition, you can use %ROWTYPE to refer to a strongly typed cursor variable, as shown below or to Return a Record Type

DECLARE
   TYPE EmpRecTyp IS RECORD (
      employee_id NUMBER,
      last_name VARCHAR2(25),
      salary   NUMBER(8,2));
   TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;
   emp_cv EmpCurTyp;  -- declare cursor variable

Initialization

The OPEN FOR statement:

• associates a cursor variable with a query,
• allocates database resources to process the query,
• identifies the result set,
• and positions the cursor before the first row of the result set.

Loop

PL/SQL - OPEN-FOR, FETCH, and CLOSE statements

How to

use them in a procedure

REF CURSOR in the example below reference a cursor variable.

DECLARE
   TYPE EmpCurTyp IS REF CURSOR;
   emp_cv   EmpCurTyp;
   emp_rec  emp%ROWTYPE;
   sql_stmt VARCHAR2(200);
   my_job   VARCHAR2(15) := 'CLERK';
BEGIN
   sql_stmt := 'SELECT * FROM emp WHERE job';
   OPEN emp_cv FOR sql_stmt;
   LOOP
      FETCH emp_cv INTO emp_rec;
      EXIT WHEN emp_cv%NOTFOUND;
      -- process record
   END LOOP;
   CLOSE emp_cv;
END;
/

Use them in a function

FUNCTION    "GET_JOBS"
RETURN JobsPkg.ref_cursor
AS jobs_cursor JobsPkg.ref_cursor;
BEGIN
  OPEN jobs_cursor FOR
  SELECT job_id, job_title FROM jobs;
  RETURN jobs_cursor;
END;

Pass Cursor Variables As Parameters

You can declare cursor variables as the formal parameters of functions and procedures.

DECLARE
   TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;
   emp empcurtyp;
-- after result set is built, process all the rows inside a single procedure
--  rather than calling a procedure for each row
   PROCEDURE process_emp_cv (emp_cv IN empcurtyp) IS
      person employees%ROWTYPE;
   BEGIN
      DBMS_OUTPUT.PUT_LINE('-----');
      DBMS_OUTPUT.PUT_LINE('Here are the names from the result set:');
      LOOP
         FETCH emp_cv INTO person;
         EXIT WHEN emp_cv%NOTFOUND;
         DBMS_OUTPUT.PUT_LINE('Name = ' || person.first_name ||
                              ' ' || person.last_name);
      END LOOP;
   END;
BEGIN
-- First find 10 arbitrary employees.
  OPEN emp FOR SELECT * FROM employees WHERE ROWNUM < 11;
  process_emp_cv(emp);
  CLOSE emp;
-- find employees matching a condition.
  OPEN emp FOR SELECT * FROM employees WHERE last_name LIKE 'R%';
  process_emp_cv(emp);
  CLOSE emp;
END;
/