Switch to: V9V8V7V6V5

This is an old revision of the document!


Cursors

DECLARE Cursor

Syntax

DECLARE cursor_name CURSOR FOR 
         {    variable_name  -- v4.0
         |    direct_select_statement_multiple_rows
         }

Description

This statement declares a cursor. You can declare multiple cursors in a routine, but each cursor in any given block must have a unique name.

Cursors are supported inside stored procedure, functions and triggers. The syntax is as in embedded SQL. Cursors in Valentina have these properties:

  • Read only - not updatable.
  • Both direction - you can go to next, prev, first, last records.

While most other DBMS allow to declare a cursor only with static text of SELECT command, Valentina supports declaring of cursor with string variable. This feature allows you create a SELECT commands at runtime using string concatenation. This is similar to EXECUTE, which works for all other SQL commands except SELECT.

The SELECT statement under DECLARE cannot have an INTO clause, which is used as short-syntax to extract values from a single-row SELECT

Cursor OPEN

OPEN cursor_name

This statement opens a previously declared cursor by name.

Cursor FETCH

FETCH direction [FROM] cursor_name INTO var_name [, var_name] ...
 
direction
    :  { NEXT  | FORWARD  }
    |  { PRIOR | BACKWARD }
    |    FIRST
    |    LAST
    |    ABSOLUTE count
    |    RELATIVE count

This statement fetches a specified row (if a row exists) using the specified open cursor, and advances the cursor pointer.

You can move to the NEXT or PREV record, to the FIRST or LAST record of cursor, move to some absolute Nth position of cursor or position relative to the current record.

If asked record is not found then exception ERR_CURSOR_WRONG_POSITION is raised. You need catch this exception using EXCEPTION section of BEGIN/END block.

Cursor CLOSE Statement

CLOSE cursor_name

This statement closes a previously opened cursor.

If not closed explicitly, a cursor is closed at the end of the compound statement in which it was declared.

Examples

Example:

CREATE PROCEDURE sp1()
BEGIN 
    DECLARE a Long;
    DECLARE cur1 CURSOR FOR SELECT f1 FROM t1;      
 
    OPEN cur1;
    FETCH FIRST cur1 INTO a; 
 
    LOOP
      BEGIN
        PRINT a;
        FETCH NEXT cur1 INTO a;
      EXCEPTION WHEN ERR_CURSOR_WRONG_POSITION THEN 
        LEAVE 
      END
    END LOOP
END

Example:

There is an example of “dynamic” cursor declaration when SELECT string is in the some variable.

CREATE PROCEDURE sp1( fld_name string )
BEGIN 
    DECLARE query String(200);
    SET query = 'SELECT [' || fld_name || '] FROM t1;';
 
    DECLARE cursor_name CURSOR FOR query;
END