FirebirdSQL logo

OPEN

Opens a declared cursor

Syntax
OPEN cursor_name;
Table 1. OPEN Statement Parameter
Argument Description

cursor_name

Cursor name.A cursor with this name must be previously declared with a DECLARE CURSOR statement

An OPEN statement opens a previously declared cursor, executes its declared SELECT statement, and makes the first record of the result data set ready to fetch.OPEN can be applied only to cursors previously declared in a [fblangref50-psql-declare-cursor] statement.

Note

If the SELECT statement of the cursor has parameters, they must be declared as local variables, or input or output parameters before the cursor is declared.When the cursor is opened, the parameter is assigned the current value of the variable.

OPEN Examples

  1. Using the OPEN statement:

    SET TERM ^;
    
    CREATE OR ALTER PROCEDURE GET_RELATIONS_NAMES
    RETURNS (
      RNAME CHAR(63)
    )
    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 has to 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