Cursors

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

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.

OPEN cursor_name

This statement opens a previously declared cursor by name.

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.

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.

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