CURSORS

Declare and OPEN a REF cursor.

There are two steps required to create a cursor variable. First define a ref cursor TYPE; then declare cursor variable(s) of that type.

Define a REF Cursor TYPE:

   TYPE ref_type_name IS REF CURSOR
      [RETURN {cursor_name%ROWTYPE
             |ref_cursor_name%ROWTYPE
             |record_name%TYPE
             |record_type_name
             |table_name%ROWTYPE} ];

The RETURN clause is optional, when included it causes the cursor variable to be strongly typed.

It is a good practice to use strongly typed cursor variable types - this ensures that columns returned by a query will match the return type of the cursor.

Irrespective of the return clause, each TYPE that you define is a new separate datatype.

Cursor_variable_declaration:

  ref_cursor_name ref_type_name;


OPEN a REF cursor...

  OPEN cursor_variable_name FOR select_statement;

or, to be sure it's not open already:

  IF NOT mycursor%ISOPEN THEN 
    OPEN mycursor FOR select_statement;
  END IF;

You can also open a host cursor variable:

  OPEN :host_cursor_variable_name FOR select_statement;

Notes:

The "select_statement" cannot include an INTO clause.

The select statement is part of the OPEN statement (not the declaration) so the same REF cursor can be reopened with different SQL.

Cursor variables can be bind variables. The associated query can reference bind variables as well.

Related Commands:

Open a normal Cursor

Related Views: