Using Explicit Cursors  «Prev 

Incorrect Syntax for usage of Cursor Names

The following statements are incorrect syntax for usage of cursor names (pet_info is the cursor name): 

pet_info := 15000;
  
If pet_info is not NULL then

Keyword and Parameter Descriptions

  1. cursor_name: The name of an open explicit cursor that was declared within the current scope.
  2. cursor_variable_name: The name of an open cursor variable that was declared in the current scope.
  3. host_cursor_variable_name: The name of an open cursor variable that was declared in a PL/SQL host environment and passed to PL/SQL as a bind argument.

Cursor Expressions

Oracle provides a powerful 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, although first introduced in Oracle8i Database SQL, was not available from within PL/SQL programs. This deficiency was corrected in Oracle9i Database Release 1; since then, SQL statements within a PL/SQL procedure or function have been able to take advantage of the CURSOR expression.

Oracle Database PL/SQL Programming
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 database
You can use cursor expressions in any of the following:
  1. Explicit cursor declarations
  2. Dynamic SQL queries
  3. 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)

The database 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:
  1. You explicitly close the cursor.
  2. The outer, parent cursor is executed again, closed, or canceled.
  3. An exception is raised while fetching from a parent cursor. The nested cursor is closed along with the parent cursor.