PL/SQL User's Guide and Reference

Cursors

Description

To execute a multi-row query, Oracle opens an unnamed work area that stores processing information. A cursor lets you name the work area, access the information, and process the rows individually. For more information, see "Managing Cursors"

Syntax

cursor_declaration ::=

CURSOR cursor_name [(cursor_parameter_declaration[, 
  cursor_parameter_declaration]...)] IS select_statement;

cursor_specification ::=

CURSOR cursor_name [(cursor_parameter_declaration[, 
  cursor_parameter_declaration]...)]
    RETURN {  cursor_name%ROWTYPE
            | record_name%TYPE
            | record_type_name
            | table_name%ROWTYPE};

cursor_body ::=

CURSOR cursor_name [(cursor_parameter_declaration[, 
  cursor_parameter_declaration]...)]
    RETURN {  cursor_name%ROWTYPE
            | record_name%TYPE
            | record_type_name
            | table_name%ROWTYPE} IS select_statement;

cursor_parameter_declaration ::=

cursor_parameter_name [IN]
   {  cursor_name%ROWTYPE
    | cursor_variable_name%TYPE
    | plsql_table_name%TYPE
    | record_name%TYPE
    | scalar_type_name
    | table_name%ROWTYPE
    | table_name.column_name%TYPE
    | variable_name%TYPE} [{:= | DEFAULT} expression]

Keyword and Parameter Description

cursor_parameter_name

This identifies a cursor parameter; that is, a variable declared as the formal parameter of a cursor. A cursor parameter can appear in a query wherever a constant can appear. The formal parameters of a cursor must be IN parameters. The query can also reference other PL/SQL variables within its scope.

select_statement

This is a query that returns a result set of rows. If the cursor declaration declares parameters, each parameter must be used in the query. The syntax of select_statement is like that of select_into_statement, which is defined in "SELECT INTO Statement"

, except that select_statement cannot have an INTO clause.

RETURN

This keyword introduces the RETURN clause, which specifies the datatype of a cursor result value. You can use the %ROWTYPE attribute in the RETURN clause to provide a record type that represents a row in a database table or a row returned by a previously declared cursor. Also, you can use the %TYPE attribute to provide the datatype of a previously declared record.

A cursor body must have a SELECT statement and the same RETURN clause as its corresponding cursor specification. Also, the number, order, and datatypes of select items in the SELECT clause must match the RETURN clause.

cursor_name

This identifies an explicit cursor previously declared within the current scope.

record_name

This identifies a user-defined record previously declared within the current scope.

record_type_name

This identifies a RECORD type previously defined within the current scope. For more information, see "User-Defined Records"

table_name

This identifies a database table (or view) that must be accessible when the declaration is elaborated.

%ROWTYPE

This attribute provides a record type that represents a row in a database table or a row fetched from a previously declared cursor. Fields in the record and corresponding columns in the row have the same names and datatypes.

%TYPE

This attribute provides the datatype of a previously declared field, record, PL/SQL table, database column, or variable.

cursor_variable_name

This identifies a PL/SQL cursor variable previously declared within the current scope.

scalar_type_name

This identifies a predefined scalar datatype such as BOOLEAN, NUMBER, or VARCHAR2, which must be specified without constraints. For more information, see "Datatypes"

plsql_table_name

This identifies a PL/SQL table previously declared within the current scope.

variable_name

This identifies a PL/SQL variable previously declared within the current scope.

expression

This is an arbitrarily complex combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable. For the syntax of expression, see "Expressions"

. When the declaration is elaborated, the value of expression is assigned to the parameter. The value and the parameter must have compatible datatypes.

Usage Notes

You must declare a cursor before referencing it in an OPEN, FETCH, or CLOSE statement. And, you must declare a variable before referencing it in a cursor declaration. The word SQL is reserved by PL/SQL for use as the default name for implicit cursors and cannot be used in a cursor declaration.

You cannot assign values to a cursor name or use it in an expression. However, cursors and variables follow the same scoping rules. For more information, see "Scope and Visibility" .

You retrieve data from a cursor by opening it, then fetching from it. Because the FETCH statement specifies the target variables, using an INTO clause in the SELECT statement of a cursor_declaration is redundant and invalid.

The scope of cursor parameters is local to the cursor, meaning that they can be referenced only within the query used in the cursor declaration. The values of cursor parameters are used by the associated query when the cursor is opened. The query can also reference other PL/SQL variables within its scope.

The datatype of a cursor parameter must be specified without constraints. For example, the following parameter declarations are illegal:

CURSOR c1 (emp_id NUMBER NOT NULL, dept_no NUMBER(2))  -- illegal

Examples

Two examples of cursor declarations follow:

CURSOR c1 IS
   SELECT ename, job, sal FROM emp WHERE deptno = 20;
CURSOR c2 (start_date DATE) IS
   SELECT empno, sal FROM emp WHERE hiredate > start_date;