Cursor predicates

>>-cursor-variable-name--IS--+-----+--+-FOUND-+----------------><
                             '-NOT-'  '-OPEN--'   

Cursor predicates are SQL keywords that can be used to determine the state of a cursor defined within the current scope. They provide a means for easily referencing whether a cursor is open, closed or if there are rows associated with the cursor.

cursor-variable-name

The name of a SQL variable or SQL parameter of a cursor type.

IS

Specifies that a cursor predicate property is to be tested.

NOT

Specifies that the opposite value of testing the cursor predicate property is to be returned.

FOUND

Specifies to check if the cursor contains rows after the execution of a FETCH statement. If the last FETCH statement executed was successful, and if the IS FOUND predicate syntax is used, the returned value is TRUE. If the last FETCH statement executed resulted in a condition where rows were not found, the result is false. The result is unknown when:

• the value of cursor-variable-name is null
• the underlying cursor of cursor-variable-name is not open
• the predicate is evaluated before the first FETCH action was performed on the underlying cursor
• the last FETCH action returned an error

The IS FOUND predicate can be useful within a portion of SQL PL logic that loops and performs a fetch with each iteration. The predicate can be used to determine if rows remain to be fetched. It provides an efficient alternative to using a condition handler that checks for the error condition that is raised when no more rows remain to be fetched.

When the NOT keyword is specified, so that the syntax is IS NOT FOUND, the result value is the opposite.

OPEN

Specifies to check if the cursor is in an open state. If the cursor is in open and if the IS OPEN predicate syntax is used, the returned value is TRUE. This can be a useful predicate in cases where cursors are passed as parameters to functions and procedures. Before attempting to open the cursor, this predicate can be used to determine if the cursor is already open.

When the NOT keyword is specified, so that the syntax is IS NOT OPEN, the result value is the opposite.

Notes

• A cursor predicate can only be used in statements within a compound SQL (compiled) statement (SQLSTATE 42818).

Example

CREATE TABLE T1 (c1 INT, c2 INT, c3 INT)@

INSERT INTO T1 VALUES (1,1,1),(2,2,2),(3,3,3) @

CREATE TYPE myRowType AS ROW(c1 INT, c2 INT, c3 INT)@

CREATE TYPE myCursorType AS myRowType CURSOR@


CREATE PROCEDURE p(OUT count INT)
LANGUAGE SQL
BEGIN
  DECLARE C1 CURSOR;
  DECLARE lvarInt INT;

  SET count = -1;
  SET c1 = CURSOR FOR SELECT c1 FROM t1;

  IF (c1 IS NOT OPEN) THEN
     OPEN c1;
  ELSE 
     set count = -2;
  END IF;

  SET count = 0;
  IF (c1 IS OPEN) THEN 
  
    FETCH c1 INTO lvarInt;

    WHILE (c1 IS FOUND) DO
      SET count = count + 1;
      FETCH c1 INTO lvarInt;
    END WHILE;
  ELSE
     SET COUNT = 0;
  END IF;

END@

CALL p()@