7.6.13. OPEN

Used for

Opening a declared cursor

Available in

PSQL

Syntax

OPEN cursorname

An OPEN statement opens a previously declared cursor, executes the SELECT statement declared for it and makes the first record of the result data set ready to fetch. OPEN can be applied only to cursors previously declared in a DECLARE VARIABLE statement.

Examples

1. Using the OPEN statement:

   SET TERM ^;
   CREATE OR ALTER PROCEDURE GET_RELATIONS_NAMES
   RETURNS (
     RNAME CHAR(31)
   )
   AS
     DECLARE C CURSOR FOR (
       SELECT RDB$RELATION_NAME
       FROM RDB$RELATIONS);
   BEGIN
     OPEN C;
     WHILE (1 = 1) DO
     BEGIN
       FETCH C INTO :RNAME;
       IF (ROW_COUNT = 0) THEN
         LEAVE;
       SUSPEND;
     END
     CLOSE C;
   END^
   SET TERM ;^

2. A collection of scripts for creating views using a PSQL block with named cursors:

   EXECUTE BLOCK
   RETURNS (
     SCRIPT BLOB SUB_TYPE TEXT)
   AS
     DECLARE VARIABLE FIELDS VARCHAR(8191);
     DECLARE VARIABLE FIELD_NAME TYPE OF RDB$FIELD_NAME;
     DECLARE VARIABLE RELATION RDB$RELATION_NAME;
     DECLARE VARIABLE SOURCE TYPE OF COLUMN RDB$RELATIONS.RDB$VIEW_SOURCE;
     -- named cursor
     DECLARE VARIABLE CUR_R CURSOR FOR (
       SELECT
         RDB$RELATION_NAME,
         RDB$VIEW_SOURCE
       FROM
         RDB$RELATIONS
       WHERE
         RDB$VIEW_SOURCE IS NOT NULL);
     -- named cursor with local variable
     DECLARE CUR_F CURSOR FOR (
       SELECT
         RDB$FIELD_NAME
       FROM
         RDB$RELATION_FIELDS
       WHERE
         -- Important! The variable shall be declared earlier
         RDB$RELATION_NAME = :RELATION);
   BEGIN
     OPEN CUR_R;
     WHILE (1 = 1) DO
     BEGIN
       FETCH CUR_R
       INTO :RELATION, :SOURCE;
       IF (ROW_COUNT = 0) THEN
         LEAVE;
       FIELDS = NULL;
       -- The CUR_F cursor will use
       -- variable value of RELATION initialized above
       OPEN CUR_F;
       WHILE (1 = 1) DO
       BEGIN
         FETCH CUR_F
         INTO :FIELD_NAME;
         IF (ROW_COUNT = 0) THEN
           LEAVE;
         IF (FIELDS IS NULL) THEN
           FIELDS = TRIM(FIELD_NAME);
         ELSE
           FIELDS = FIELDS || ', ' || TRIM(FIELD_NAME);
       END
       CLOSE CUR_F;
       SCRIPT = 'CREATE VIEW ' || RELATION;
       IF (FIELDS IS NOT NULL) THEN
         SCRIPT = SCRIPT || ' (' || FIELDS || ')';
       SCRIPT = SCRIPT || ' AS ' || ASCII_CHAR(13);
       SCRIPT = SCRIPT || SOURCE;
       SUSPEND;
     END
     CLOSE CUR_R;
   END

See also

DECLARE CURSOR, FETCH, CLOSE