Oracle PL/SQL - PL SQL Cursor Cursor Variables

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Cursor
  4. Cursor Variables

Introduction

A cursor variable is like an explicit cursor. It is the address of an item, not the item itself.

Creating Cursor Variables

To create a cursor variable, either declare a variable of the predefined type SYS_REFCURSOR or define a REF CURSOR type.

Then you can declare a variable of that type.

A cursor variable is sometimes called a REF CURSOR.

The basic syntax of a REF CURSOR type definition is: 

TYPE type_name IS REF CURSOR [ RETURN return_type ]

If you specify return_type, then the REF CURSOR type and cursor variables of that type are strong, otherwise they are weak.

SYS_REFCURSOR and cursor variables of that type are weak.

With a strong cursor variable, you can associate only queries that return the specified type.

With a weak cursor variable, you can associate any query.

Weak REF CURSOR types are interchangeable with each other and with the predefined type SYS_REFCURSOR.

You can assign the value of a weak cursor variable to any other weak cursor variable.

You can assign the value of a strong cursor variable to another strong cursor variable for the same type.

You can partition weak cursor variable arguments to table functions only with the PARTITION BY ANY clause, not with PARTITION BY RANGE or PARTITION BY HASH.

The following code defines strong and weak REF CURSOR types, variables of those types, and a variable of the predefined type SYS_REFCURSOR.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- ww w. j a v  a 2  s  .com

SQL> CREATE TABLE emp(
  2  empid NUMBER(6),
  3  first_name VARCHAR2(20),
  4  last_name VARCHAR2(25),
  5  department_id NUMBER(4)) ;
SQL>
SQL> drop table departments;

Table dropped.

SQL> CREATE TABLE departments(
  2  department_id NUMBER(4),
  3  department_name VARCHAR2(30)CONSTRAINT dept_name_nn NOT NULL,
  4  manager_id NUMBER(6),
  5  location_id NUMBER(4)) ;
SQL>
SQL> DECLARE
  2    TYPE empcurtyp IS REF CURSOR RETURN emp%ROWTYPE;  -- strong type
  3    TYPE genericcurtyp IS REF CURSOR;     -- weak type
  4
  5    cursor1  empcurtyp;       -- strong cursor variable
  6    cursor2  genericcurtyp;   -- weak cursor variable
  7    my_cursor SYS_REFCURSOR;  -- weak cursor variable
  8
  9    TYPE deptcurtyp IS REF CURSOR RETURN departments%ROWTYPE;  -- strong type
 10    dept_cv deptcurtyp;  -- strong cursor variable
 11  BEGIN
 12    NULL;
 13  END;
 14  /

PL/SQL procedure successfully completed.

SQL>

Related Topics