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

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 alsoSection 7.6.13, “OPEN”, Section 7.6.14, “FETCH”, Section 7.6.15, “CLOSE”

A local variable can be of any SQL type other than an array.

• A domain name can be specified as the type and the variable will inherit all of its attributes.

• If the TYPE OF domain clause is used instead, the variable will inherit only the domain’s data type, and, if applicable, its character set and collation attributes. Any default value or constraints such as NOT NULL or CHECK constraints are not inherited.

• If the TYPE OF COLUMN relation.column> option is used to borrow from a column in a table or view, the variable will inherit only the column’s data type, and, if applicable, its character set and collation attributes. Any other attributes are ignored.

The variable can be constrained NOT NULL if required. If a domain has been specified as the data type and already carries the NOT NULL constraint, it will not be necessary. With the other forms, including use of a domain that is nullable, the NOT NULL attribute should be included if needed.

Unless specified, the character set and collation sequence of a string variable will be the database defaults. A CHARACTER SET clause can be included, if required, to handle string data that is going to be in a different character set. A valid collation sequence (COLLATE clause) can also be included, with or without the character set clause.

Local variables are NULL when execution of the module begins. They can be initialized so that a starting or default value is available when they are first referenced. The DEFAULT <initvalue> form can be used, or just the assignment operator, =: = <initvalue>. The value can be any type-compatible literal or context variable.

   |CREATE OR ALTER PROCEDURE SOME_PROC
   |AS
   |  -- Declaring a variable of the INT type
   |  DECLARE I INT;
   |  -- Declaring a variable of the INT type that does not allow NULL
   |  DECLARE VARIABLE J INT NOT NULL;
   |  -- Declaring a variable of the INT type with the default value of 0
   |  DECLARE VARIABLE K INT DEFAULT 0;
   |  -- Declaring a variable of the INT type with the default value of 1
   |  DECLARE VARIABLE L INT = 1;
   |  -- Declaring a variable based on the COUNTRYNAME domain
   |  DECLARE FARM_COUNTRY COUNTRYNAME;
   |  -- Declaring a variable of the type equal to the COUNTRYNAME domain
   |  DECLARE FROM_COUNTRY TYPE OF COUNTRYNAME;
   |  -- Declaring a variable with the type of the CAPITAL column in the COUNTRY table
   |  DECLARE CAPITAL TYPE OF COLUMN COUNTRY.CAPITAL;
   |BEGIN
   |  /* PSQL statements */
   |END

See alsoData Types and Subtypes, Custom Data Types — Domains, CREATE DOMAIN

You can use parameters — either named or positional — in the DSQL statement string. Each parameter must be assigned a value.

   |...
   |DECLARE license_num VARCHAR(15);
   |DECLARE connect_string VARCHAR (100);
   |DECLARE stmt VARCHAR (100) =
   |  'SELECT license
   |   FROM cars
   |   WHERE driver = :driver AND location = :loc';
   |BEGIN
   |  ...
   |  SELECT connstr
   |  FROM databases
   |  WHERE cust_id = :id
   |  INTO connect_string;
   |  ...
   |  FOR
   |    SELECT id
   |    FROM drivers
   |    INTO current_driver
   |  DO
   |  BEGIN
   |    FOR
   |      SELECT location
   |      FROM driver_locations
   |      WHERE driver_id = :current_driver
   |      INTO current_location
   |    DO
   |    BEGIN
   |      ...
   |      EXECUTE STATEMENT (stmt)
   |        (driver := current_driver,
   |         loc := current_location)
   |      ON EXTERNAL connect_string
   |      INTO license_num;
   |      ...

   |DECLARE license_num VARCHAR (15);
   |DECLARE connect_string VARCHAR (100);
   |DECLARE stmt VARCHAR (100) =
   |  'SELECT license
   |   FROM cars
   |   WHERE driver = ? AND location = ?';
   |BEGIN
   |  ...
   |  SELECT connstr
   |  FROM databases
   |  WHERE cust_id = :id
   |  into connect_string;
   |  ...
   |  FOR
   |    SELECT id
   |    FROM drivers
   |    INTO current_driver
   |  DO
   |  BEGIN
   |    FOR
   |      SELECT location
   |      FROM driver_locations
   |      WHERE driver_id = :current_driver
   |      INTO current_location
   |    DO
   |    BEGIN
   |      ...
   |      EXECUTE STATEMENT (stmt)
   |        (current_driver, current_location)
   |      ON EXTERNAL connect_string
   |      INTO license_num;
   |      ...

Traditionally, the executed SQL statement always ran within the current transaction, and this is still the default. WITH AUTONOMOUS TRANSACTION causes a separate transaction to be started, with the same parameters as the current transaction. It will be committed if the statement runs to completion without errors and rolled back otherwise. WITH COMMON TRANSACTION uses the current transaction if possible.

If the statement must run in a separate connection, an already started transaction within that connection is used, if available. Otherwise, a new transaction is started with the same parameters as the current transaction. Any new transactions started under the COMMON regime are committed or rolled back with the current transaction.

By default, the SQL statement is executed with the privileges of the current user. Specifying WITH CALLER PRIVILEGES adds to this the privileges of the calling procedure or trigger, just as if the statement were executed directly by the routine. WITH CALLER PRIVILEGES has no effect if the ON EXTERNAL clause is also present.

With ON EXTERNAL [DATA SOURCE], the SQL statement is executed in a separate connection to the same or another database, possibly even on another server. If the connect string is NULL or '' (empty string), the entire ON EXTERNAL [DATA SOURCE] clause is considered absent and the statement is executed against the current database.

The optional AS USER, PASSWORD and ROLE clauses allow specificaton of which user will execute the SQL statement and with which role. The method of user log-in and whether a separate connection is open depend on the presence and values of the ON EXTERNAL [DATA SOURCE], AS USER, PASSWORD and ROLE clauses:

• If ON EXTERNAL is present, a new connection is always opened, and:

  • If at least one of AS USER, PASSWORD and ROLE is present, native authentication is attempted with the given parameter values (locally or remotely, depending on the connect string). No defaults are used for missing parameters

  • If all three are absent and the connect string contains no hostname, then the new connection is established on the local host with the same user and role as the current connection. The term 'local' means on the same machine as the server here. This is not necessarily the location of the client

  • If all three are absent and the connect string contains a hostname, then trusted authentication is attempted on the remote host (again, 'remote' from the perspective of the server). If this succeeds, the remote operating system will provide the user name (usually the operating system account under which the Firebird process runs)

• If ON EXTERNAL is absent:

  • If at least one of AS USER, PASSWORD and ROLE is present, a new connection to the current database is opened with the suppled parameter values. No defaults are used for missing parameters

  • If all three are absent, the statement is executed within the current connection

1. There is no way to validate the syntax of the enclosed statement

2. There are no dependency checks to discover whether tables or columns have been dropped

3. Even though the performance in loops has been significantly improved in Firebird 2.5, execution is still considerably slower than when the same statements are launched directly

4. Return values are strictly checked for data type in order to avoid unpredictable type-casting exceptions. For example, the string '1234' would convert to an integer, 1234, but 'abc' would give a conversion error

All in all, this feature is meant to be used very cautiously and you should always take the caveats into account. If you can achieve the same result with PSQL and/or DSQL, it will almost always be preferable.

See alsoSection 7.6.12, “FOR EXECUTE STATEMENT”

The optional AS CURSOR clause surfaces the set in the FOR SELECT structure as an undeclared, named cursor that can be operated on using the WHERE CURRENT OF clause inside the statement or block following the DO command, in order to delete or update the current row before execution moves to the next iteration.

Other points to take into account regarding undeclared cursors:

1. the OPEN, FETCH and CLOSE statements cannot be applied to a cursor surfaced by the AS CURSOR clause

2. the cursor name argument associated with an AS CURSOR clause must not clash with any names created by DECLARE VARIABLE or DECLARE CURSOR statements at the top of the body code, nor with any other cursors surfaced by an AS CURSOR clause

3. The optional FOR UPDATE clause in the SELECT statement is not required for a positioned update

1. A simple loop through query results:

      |CREATE PROCEDURE SHOWNUMS
      |RETURNS (
      |  AA INTEGER,
      |  BB INTEGER,
      |  SM INTEGER,
      |  DF INTEGER)
      |AS
      |BEGIN
      |  FOR SELECT DISTINCT A, B
      |      FROM NUMBERS
      |    ORDER BY A, B
      |    INTO AA, BB
      |  DO
      |  BEGIN
      |    SM = AA + BB;
      |    DF = AA - BB;
      |    SUSPEND;
      |  END
      |END
   

2. Nested FOR SELECT loop:

      |CREATE PROCEDURE RELFIELDS
      |RETURNS (
      |  RELATION CHAR(32),
      |  POS INTEGER,
      |  FIELD CHAR(32))
      |AS
      |BEGIN
      |  FOR SELECT RDB$RELATION_NAME
      |      FROM RDB$RELATIONS
      |      ORDER BY 1
      |      INTO :RELATION
      |  DO
      |  BEGIN
      |    FOR SELECT
      |          RDB$FIELD_POSITION + 1,
      |          RDB$FIELD_NAME
      |        FROM RDB$RELATION_FIELDS
      |        WHERE
      |          RDB$RELATION_NAME = :RELATION
      |        ORDER BY RDB$FIELD_POSITION
      |        INTO :POS, :FIELD
      |    DO
      |    BEGIN
      |      IF (POS = 2) THEN
      |        RELATION = ' "';
      | 
      |      SUSPEND;
      |    END
      |  END
      |END
   

3. Using the AS CURSOR clause to surface a cursor for the positioned delete of a record:

      |CREATE PROCEDURE DELTOWN (
      |  TOWNTODELETE VARCHAR(24))
      |RETURNS (
      |  TOWN VARCHAR(24),
      |  POP INTEGER)
      |AS
      |BEGIN
      |  FOR SELECT TOWN, POP
      |      FROM TOWNS
      |      INTO :TOWN, :POP AS CURSOR TCUR
      |  DO
      |  BEGIN
      |    IF (:TOWN = :TOWNTODELETE) THEN
      |      -- Positional delete
      |      DELETE FROM TOWNS
      |      WHERE CURRENT OF TCUR;
      |    ELSE
      |      SUSPEND;
      |  END
      |END
   

See alsoSection 7.6.2, “DECLARE CURSOR”, Section 7.6.7, “LEAVE”, SELECT, UPDATE, DELETE