|GNADE User's Guide: GNADE, The GNat Ada Database Environment; Version 1.5.3; Document Revision $Revision: 1.45 $|
|Prev||Chapter 14. Embedded SQL Syntax Specification||Next|
A cursor is a declares a SQL query with its input and result parameters. The result set is created when the cursor is opened. The syntax for declaring, opening and closing a cursor is shown below.
'DECLARE' <name> [ 'REOPENABLE' | 'LOCAL' ] 'CURSOR' 'FOR' <sql query> 'OPEN' <name> 'CLOSE' <name> ['FINAL']
LOCAL cursors are only defined within the scope of the block where the nearest DECLARE section is. If the scope is left, the cursor and the associated result set are deleted.
Example 14-4. Local Cursors
As shown in the example below, the cursor emp_cursor will only be valid in the scope of the procedure Print_Departement:
procedure Print_Departement( .......... ... departement : in Integer ) is EXEC SQL BEGIN DECLARE SECTION ; .... Depno : INT := INT( Departement ); .... EXEC SQL END DECLARE SECTION ; begin EXEC SQL AT DB01 DECLARE emp_cursor LOCAL CURSOR FOR SELECT EMPNO, FIRSTNAME, NAME, JOB, MANAGER, SALARY FROM employees WHERE deptno = :Depno ORDER BY EMPNO, NAME; .......
Normally it is not possible to open the same cursor twice. The type REOPENABLE has been introduced, in order to allow the recursive opening of cursors. This feature may also be emulated by means of recursive procedures with local cursors.
If the cursor type is omitted the cursor and its associated result set to exist only once.