Cursor Expressions (Oracle9i)

Oracle has introduced a powerful new feature in the SQL language: the cursor expression. A cursor expression, denoted by the CURSOR operator, returns a nested cursor from within a query. Each row in the result set of this nested cursor can contain the usual range of values allowed in a SQL query; it can also contain other cursors as produced by subqueries.

The CURSOR syntax, though first introduced in Oracle8i SQL, was not available from within PL/SQL programs. This deficiency is corrected in Oracle9i: SQL statements within a PL/SQL procedure or function can take advantage of the CURSOR expression.

You can therefore use cursor expressions to return a large and complex set of related values retrieved from one or more tables. You can then process the cursor expression result set using nested loops that fetch from the rows of the result set, and then additional rows from any nested cursors within those rows.

Cursor expressions can get complicated, given how complex the queries and result sets can be. Nevertheless, it's good to know all the possible ways to retrieve data from the Oracle RDBMS.

You can use cursor expressions in any of the following:

· Explicit cursor declarations

· Dynamic SQL queries

· REF CURSOR declarations and variables

You cannot use a cursor expression in an implicit query.

The syntax for a cursor expression is very simple:

CURSOR (subquery)

Oracle opens the nested cursor defined by a cursor expression implicitly as soon as it fetches the row containing the cursor expression from the parent or outer cursor. This nested cursor is closed when:

· You explicitly close the cursor.

· The outer, parent cursor is executed again, closed, or canceled.

· An exception is raised while fetching from a parent cursor. The nested cursor is closed along with the parent cursor.