FirebirdSQL logo

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).

FETCH Examples

  1. Using the FETCH statement:

    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
  2. Using the FETCH statement with nested 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 SRC TYPE OF COLUMN RDB$RELATIONS.RDB$VIEW_SOURCE;
      -- Named cursor declaration
      DECLARE VARIABLE CUR_R CURSOR FOR (
        SELECT
          RDB$RELATION_NAME,
          RDB$VIEW_SOURCE
        FROM RDB$RELATIONS
        WHERE RDB$VIEW_SOURCE IS NOT NULL);
      -- Declaring a named cursor in which
      -- a local variable is used
      DECLARE CUR_F CURSOR FOR (
        SELECT RDB$FIELD_NAME
        FROM RDB$RELATION_FIELDS
        WHERE
        -- the variable must be declared earlier
          RDB$RELATION_NAME =: RELATION);
    BEGIN
      OPEN CUR_R;
      WHILE (1 = 1) DO
      BEGIN
        FETCH CUR_R INTO RELATION, SRC;
        IF (ROW_COUNT = 0) THEN
          LEAVE;
        FIELDS = NULL;
        -- Cursor CUR_F will use the value
        -- the RELATION variable 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 || SRC;
        SUSPEND;
      END
      CLOSE CUR_R;
    EN
  3. An example of using the FETCH statement with a scrollable cursor

EXECUTE BLOCK
  RETURNS (N INT, RNAME CHAR (63))
AS
  DECLARE C SCROLL CURSOR FOR (
    SELECT
      ROW_NUMBER() OVER (ORDER BY RDB$RELATION_NAME) AS N,
      RDB$RELATION_NAME
    FROM RDB$RELATIONS
    ORDER BY RDB$RELATION_NAME);
BEGIN
  OPEN C;
  -- move to the first record (N = 1)
  FETCH FIRST FROM C;
  RNAME = C.RDB$RELATION_NAME;
  N = C.N;
  SUSPEND;
  -- move 1 record forward (N = 2)
  FETCH NEXT FROM C;
  RNAME = C.RDB$RELATION_NAME;
  N = C.N;
  SUSPEND;
  -- move to the fifth record (N = 5)
  FETCH ABSOLUTE 5 FROM C;
  RNAME = C.RDB$RELATION_NAME;
  N = C.N;
  SUSPEND;
  -- move 1 record backward (N = 4)
  FETCH PRIOR FROM C;
  RNAME = C.RDB$RELATION_NAME;
  N = C.N;
  SUSPEND;
  -- move 3 records forward (N = 7)
  FETCH RELATIVE 3 FROM C;
  RNAME = C.RDB$RELATION_NAME;
  N = C.N;
  SUSPEND;
  -- move back 5 records (N = 2)
  FETCH RELATIVE -5 FROM C;
  RNAME = C.RDB$RELATION_NAME;
  N = C.N;
  SUSPEND;
  -- move to the first record (N = 1)
  FETCH FIRST FROM C;
  RNAME = C.RDB$RELATION_NAME;
  N = C.N;
  SUSPEND;
  -- move to the last entry
  FETCH LAST FROM C;
  RNAME = C.RDB$RELATION_NAME;
  N = C.N;
  SUSPEND;
  CLOSE C;
END