Oracle7 Server SQL Reference

DECLARE CURSOR (Embedded SQL)

Purpose

To declare a cursor, giving it a name and associating it with a SQL statement or a PL/SQL block.

Prerequisites

If you associate the cursor with an identifier for a SQL statement or PL/SQL block, you must have declared this identifier in a previous DECLARE STATEMENT statement.

Syntax

Keywords and Parameters

identifies the database on which the cursor is declared. The database can be identified by either:

db_name is a database identifier declared in a previous DECLARE DATABASE statement.

:host_variable is a host variable whose value is a previously declared db_name.

If you omit this clause, Oracle7 declares the cursor on your default database.

cursor

is the name of the cursor to be declared.

SELECT command

is a SELECT statement to be associated with the cursor. The following statement cannot contain an INTO clause.

statement_name block_name

identifies a SQL statement or PL/SQL block to be associated with the cursor. The statement_name or block_name must be previously declared in a DECLARE STATEMENT statement.

Usage Notes

You must declare a cursor before referencing it in other embedded SQL statements. The scope of a cursor declaration is global within its precompilation unit and the name of each cursor must be unique in its scope. You cannot declare two cursors with the same name in a single precompilation unit.

You can reference the cursor in the WHERE clause of an UPDATE or DELETE statement using the CURRENT OF syntax, provided that the cursor has been opened with an OPEN statement and positioned on a row with a FETCH statement. For more information on this command, see Programmer's Guide to the Oracle Precompilers.

Example

This example illustrates the use of a DECLARE CURSOR:

EXEC SQL DECLARE emp_cursor CURSOR

	FOR SELECT ename, empno, job, sal

			FROM emp

			WHERE deptno = :deptno

			FOR UPDATE OF sal