Cursor Handling

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.