FirebirdSQL logo
 Data Manipulation (DML) StatementsBuilt-in Scalar Functions 

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
See also

[fblangref50-psql-declare-cursor], [fblangref50-psql-fetch], [fblangref50-psql-close]

FETCH

Fetches a record from a cursor

Syntax
FETCH [<fetch_scroll> FROM] cursor_name
  [INTO [:]varname [, [:]varname ...]];

<fetch_scroll> ::=
    NEXT | PRIOR | FIRST | LAST
  | RELATIVE n | ABSOLUTE n
Table 1. FETCH Statement Parameters
Argument Description

cursor_name

Cursor name.A cursor with this name must be previously declared with a DECLARE …​ CURSOR statement and opened by an OPEN statement.

varname

Variable name

n

Integer expression for the number of rows

The FETCH statement fetches the next row from the result set of the cursor and assigns the column values to PSQL variables.The FETCH statement can be used only with a cursor declared with the [fblangref50-psql-declare-cursor] statement.

Using the optional fetch_scroll part of the FETCH statement, you can specify in which direction and how many rows to advance the cursor position.The NEXT fetch option can be used for scrollable and forward-only cursors.Other fetch options are only supported for scrollable cursors.

The Fetch Options
NEXT

moves the cursor one row forward;this is the default

PRIOR

moves the cursor one record back

FIRST

moves the cursor to the first record.

LAST

moves the cursor to the last record

RELATIVE n

moves the cursor n rows from the current position;positive numbers move forward, negative numbers move backwards;using zero (0) will not move the cursor, and ROW_COUNT will be set to zero as no new row was fetched.

ABSOLUTE n

moves the cursor to the specified row;n is an integer expression, where 1 indicates the first row.For negative values, the absolute position is taken from the end of the result set, so -1 indicates the last row, -2 the second to last row, etc.A value of zero (0) will position before the first row.

The optional INTO clause gets data from the current row of the cursor and loads them into PSQL variables.If a fetch moves beyond the bounds of the result set, the variables will be set to NULL.

It is also possible to use the cursor name as a variable of a record type (similar to OLD and NEW in triggers), allowing access to the columns of the result set (i.e. cursor_name.columnname).

Rules for Cursor Variables

  • When accessing a cursor variable in a DML statement, the colon prefix can be added before the cursor name (i.e. :cursor_name.columnname) for disambiguation, similar to variables.

    The cursor variable can be referenced without colon prefix, but in that case, depending on the scope of the contexts in the statement, the name may resolve in the statement context instead of to the cursor (e.g. you select from a table with the same name as the cursor).

  • Cursor variables are read-only

  • In a FOR SELECT statement without an AS CURSOR clause, you must use the INTO clause.If an AS CURSOR clause is specified, the INTO clause is allowed, but optional;you can access the fields through the cursor instead.

  • Reading from a cursor variable returns the current field values.This means that an UPDATE statement (with a WHERE CURRENT OF clause) will update not only the table, but also the fields in the cursor variable for subsequent reads.Executing a DELETE statement (with a WHERE CURRENT OF clause) will set all fields in the cursor variable to NULL for subsequent reads

  • When the cursor is not positioned on a row — it is positioned before the first row, or after the last row — attempts to read from the cursor variable will result in error “Cursor cursor_name is not positioned in a valid record”

For checking whether all the rows of the result set have been fetched, the context variable ROW_COUNT returns the number of rows fetched by the statement.If a record was fetched, then ROW_COUNT is one (1), otherwise zero (0).