Declares a named cursor for an SQL statement
DBMS [ WITH CONNECTION
- Name of connection to associate with the cursor. If the clause is not used, Panther opens the cursor on the default connection.
- Name of cursor to be created.
SQLstatement to be performed when the cursor is executed.
DBMS DECLARE CURSORto 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
cursoralready exists and if the connection is the same, Panther reinitializes this cursor. Reinitialization clears any information on
SELECTcolumns and binding parameters. It does not clear any attributes assigned to the cursor with the statements:
- DBMS ALIAS
Panther uses these settings if the cursor is redeclared with a
SELECTstatement. It ignores the attributes if the cursor is redeclared with an
DELETEstatement. To redeclare the cursor with a new (empty) structure, close the cursor with DBMS 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 DBMS 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
c1as different from cursor
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.
// When the following statement is executed, it fetches// a list of actors in the specified video.proc s_entryDBMS 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_codeproc exec1DBMS WITH CURSOR t_cursor EXECUTE USING film_codereturn
CLOSE CURSOR, EXECUTE, WITH CURSOR, dm_is_cursor