Programming Guide

DECLARE CURSOR

Declares a named cursor for an SQL statement

Synopsis

DBMS [WITH CONNECTION connection] DECLARE cursor CURSOR
	FOR SQLstatement

Arguments

WITH CONNECTION connection
Name of connection to associate with the cursor. If the clause is not used, Panther opens the cursor on the default connection.

cursor
Name of cursor to be created.

SQLstatement
SQL statement to be performed when the cursor is executed.

Description

Use DBMS DECLARE CURSOR to create or redeclare a named cursor.

If the application has not already declared cursor, Panther allocates a new cursor structure and adds its name to the list of declared cursors.

If a cursor with the name cursor already exists and if the connection is the same, Panther reinitializes this cursor. Reinitialization clears any information on SELECT columns and binding parameters. It does not clear any attributes assigned to the cursor with the statements:

Panther uses these settings if the cursor is redeclared with a SELECT statement. It ignores the attributes if the cursor is redeclared with an INSERT, UPDATE, or DELETE statement. To redeclare the cursor with a new (empty) structure, close the cursor with CLOSE CURSOR before executing the new declaration.

If a cursor is redeclared on a different connection, Panther automatically closes the cursor and declares a new structure.

A cursor remains open until it is explicitly closed with the CLOSE CURSOR command. Closing a connection also closes all cursors on the connection.

There are few restrictions on valid cursor names. However, avoid using any DBMS, JDB, or Panther keywords as a cursor name. Panther is case sensitive regarding cursor names; for example, it considers cursor c1 as different from cursor C1.

For information on the format of parameters in the SQL statement, refer to Chapter 30, "Writing Information to the Database," in Application Development Guide and refer to "Using Database Cursors" in Application Development Guide for information about declaring cursors.

Example

// When the following statement is executed, it fetches
 
// a list of actors in the specified video.
 
proc s_entry
 
DBMS WITH CONNECTION c1 DECLARE act_cursor CURSOR FOR \
	SELECT actors.first_name, actors.last_name, roles.role \
	FROM actors, roles \
	WHERE actors.actor_id = roles.actor_id \
	AND roles.title_id = ::film_code
 
proc exec1
 
DBMS WITH CURSOR t_cursor EXECUTE USING film_code
 
return

See Also

CLOSE CURSOR, EXECUTE, WITH CURSOR, dm_is_cursor