Cursor predicates
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
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.
Notes
- A cursor predicate can only be used in statements within a compound SQL (compiled) statement (SQLSTATE 42818).
Example
The following script defines an SQL procedure that contains references to these predicates as well as the prerequisite objects required to successfully compile and call the procedure: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()@