7.6.2. DECLARE CURSOR

Used for

Declaring a named cursor

Available in

PSQL

Syntax

DECLARE [VARIABLE] cursorname CURSOR FOR (<select>) [FOR UPDATE]

The DECLARE CURSOR …​ FOR statement binds a named cursor to the result set obtained in the SELECT statement specified in the FOR clause. In the body code, the cursor can be opened, used to walk row-by-row through the result set and closed. While the cursor is open, the code can perform positioned updates and deletes using the WHERE CURRENT OF in the UPDATE or DELETE statement.

Cursor Idiosyncrasies

• The optional FOR UPDATE clause can be included in the SELECT statement but its absence does not prevent successful execution of a positioned update or delete

• Care should be taken to ensure that the names of declared cursors do not conflict with any names used subsequently in statements for AS CURSOR clauses

• If the cursor is needed only to walk the result set, it is nearly always easier and less error-prone to use a FOR SELECT statement with the AS CURSOR clause. Declared cursors must be explicitly opened, used to fetch data and closed. The context variable ROW_COUNT has to be checked after each fetch and, if its value is zero, the loop has to be terminated. A FOR SELECT statement checks it automatically.

  Nevertheless, declared cursors provide a high level of control over sequential events and allow several cursors to be managed in parallel.

• The SELECT statement may contain parameters. For instance:

  SELECT NAME || :SFX FROM NAMES WHERE NUMBER = :NUM

  Each parameter has to have been declared beforehand as a PSQL variable, even if they originate as input and output parameters. When the cursor is opened, the parameter is assigned the current value of the variable.

Examples Using Named Cursors

1. Declaring a named cursor in the trigger.

   CREATE OR ALTER TRIGGER TBU_STOCK
     BEFORE UPDATE ON STOCK
   AS
     DECLARE C_COUNTRY CURSOR FOR (
       SELECT
         COUNTRY,
         CAPITAL
       FROM COUNTRY
     );
   BEGIN
     /* PSQL statements */
   END

2. A collection of scripts for creating views with a PSQL block using 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;
     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 where
   -- a local variable is used
     DECLARE CUR_F CURSOR FOR (
       SELECT
         RDB$FIELD_NAME
       FROM
         RDB$RELATION_FIELDS
       WHERE
         -- It is important that the variable must 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 the value
       -- of the RELATION variable initiated 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

OPEN, FETCH, CLOSE