|PL/SQL User's Guide and Reference
Release 2 (9.2)
Part Number A96624-01
PL/SQL Language Elements, 14 of 52
To execute a multi-row query, Oracle opens an unnamed work area that stores processing information. An explicit cursor lets you name the work area, access the information, and process the rows individually. For more information, see "Managing Cursors".
This identifies an explicit cursor previously declared within the current scope.
This is a type specifier. For the syntax of
datatype, see "Constants and Variables".
This identifies a database table (or view) that must be accessible when the declaration is elaborated.
This is an arbitrarily complex combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable. When the declaration is elaborated, the value of
expression is assigned to the parameter. The value and the parameter must have compatible datatypes.
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.
This identifies a user-defined record previously declared within the current scope.
This identifies a user-defined record type that was defined using the datatype specifier
This keyword introduces the
RETURN clause, which specifies the datatype of a cursor return 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 spec. Also, the number, order, and datatypes of select items in the
SELECT clause must match the
This attribute provides a record type that represents a row in a database table or a row fetched from a previously declared cursor or cursor variable. Fields in the record and corresponding columns in the row have the same names and datatypes.
This is a query that returns a result set of rows. Its syntax is like that of
select_into_statement without the
INTO clause. See "SELECT INTO Statement". If the cursor declaration declares parameters, each parameter must be used in the query.
This attribute provides the datatype of a previously declared user-defined record.
You must declare a cursor before referencing it in an
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 of PL/SQL Identifiers".
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 not allowed:
Some examples of cursor declarations follow:
CURSOR c1 IS SELECT empno, ename, job, sal FROM emp WHERE sal > 2000; CURSOR c2 RETURN dept%ROWTYPE IS SELECT * FROM dept WHERE deptno = 10; CURSOR c3 (start_date DATE) IS SELECT empno, sal FROM emp WHERE hiredate > start_date;