|CREATE PROCEDURE MYPROC (
   |  a INTEGER,
   |  b INTEGER,
   |  name VARCHAR (30)
   |)
   |RETURNS (
   |  c INTEGER,
   |  str VARCHAR(100))
   |AS
   |BEGIN
   |  -- assigning a constant
   |  c = 0;
   |  str = '';
   |  SUSPEND;
   |  -- assigning expression values
   |  c = a + b;
   |  str = name || CAST(b AS VARCHAR(10));
   |  SUSPEND;
   |  -- assigning expression value built by a query
   |  c = (SELECT 1 FROM rdb$database);
   |  -- assigning a value from a context variable
   |  str = CURRENT_USER;
   |  SUSPEND;
   |END

See alsoSection 7.7.3, “DECLARE VARIABLE”

  |create or alter trigger on_connect on connect
  |as
  |begin
  |    set bind of decfloat to double precision;
  |    set time zone 'America/Sao_Paulo';
  |end

See alsoManagement Statements

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

• A domain name can be specified as the type; 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.

For local variables, you can specify the NOT NULL constraint, disallowing NULL values for the variable. If a domain has been specified as the data type and the domain already has the NOT NULL constraint, the declaration is unnecessary. For other forms, including use of a domain that is nullable, the NOT NULL constraint can be included if needed.

Unless specified, the character set and collation of a string variable will be the database defaults. A CHARACTER SET clause can be specified to handle string data that needs a different character set. A valid collation (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 explicitly initialized so that a starting or default value is available when they are first referenced. The initial value can be specified in two ways, DEFAULT <initvalue> and = <initvalue>. The value can be any type-compatible literal or context variable, including NULL.

   |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

The cursor can be forward-only (unidirectional) or scrollable. The optional clause SCROLL makes the cursor scrollable, the NO SCROLL clause, forward-only. By default, cursors are forward-only.

Forward-only cursors can — as the name implies — only move forward in the dataset. Forward-only cursors only support the FETCH [NEXT FROM] statement, other fetch options raise an error. Scrollable cursors allow you to move not only forward in the dataset, but also back, as well as N positions relative to the current position.

• 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 does this 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, or as input or output parameters. When the cursor is opened, the parameter is assigned the current value of the variable.

1. Declaring a named cursor in a 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. Declaring a scrollable cursor

      |EXECUTE BLOCK
      |  RETURNS (
      |    N INT,
      |    RNAME CHAR(63))
      |AS
      |  - Declaring a scrollable cursor
      |  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
      |  / * PSQL statements * /
      |END
   

3. 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
      |      -- 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.7.18, “OPEN”, Section 7.7.19, “FETCH”, Section 7.7.20, “CLOSE”

1. Sub-function within a stored function

      |CREATE OR ALTER FUNCTION FUNC1 (n1 INTEGER, n2 INTEGER)
      |  RETURNS INTEGER
      |AS
      |- Subfunction
      |  DECLARE FUNCTION SUBFUNC (n1 INTEGER, n2 INTEGER)
      |    RETURNS INTEGER
      |  AS
      |  BEGIN
      |    RETURN n1 + n2;
      |  END
      |BEGIN
      |  RETURN SUBFUNC (n1, n2);
      |END
   

2. Recursive function call

      |execute block returns (i integer, o integer)
      |as
      |    -- Recursive function without forward declaration.
      |    declare function fibonacci(n integer) returns integer
      |    as
      |    begin
      |      if (n = 0 or n = 1) then
      |       return n;
      |     else
      |       return fibonacci(n - 1) + fibonacci(n - 2);
      |    end
      |begin
      |  i = 0;
      | 
      |  while (i < 10)
      |  do
      |  begin
      |    o = fibonacci(i);
      |    suspend;
      |    i = i + 1;
      |  end
      |end
   

See alsoSection 7.7.6, “DECLARE PROCEDURE”, CREATE FUNCTION

1. Subroutines in EXECUTE BLOCK

      |EXECUTE BLOCK
      |  RETURNS (name VARCHAR(63))
      |AS
      |  -- Sub-procedure returning a list of tables
      |  DECLARE PROCEDURE get_tables
      |    RETURNS (table_name VARCHAR(63))
      |  AS
      |  BEGIN
      |    FOR SELECT RDB$RELATION_NAME
      |      FROM RDB$RELATIONS
      |      WHERE RDB$VIEW_BLR IS NULL
      |      INTO table_name
      |    DO SUSPEND;
      |  END
      |  -- Sub-procedure returning a list of views
      |  DECLARE PROCEDURE get_views
      |    RETURNS (view_name VARCHAR(63))
      |  AS
      |  BEGIN
      |    FOR SELECT RDB$RELATION_NAME
      |      FROM RDB$RELATIONS
      |      WHERE RDB$VIEW_BLR IS NOT NULL
      |      INTO view_name
      |    DO SUSPEND;
      |  END
      |BEGIN
      |  FOR SELECT table_name
      |    FROM get_tables
      |    UNION ALL
      |    SELECT view_name
      |    FROM get_views
      |    INTO name
      |  DO SUSPEND;
      |END
   

2. With forward declaration and parameter with default value

      |execute block returns (o integer)
      |as
      |    -- Forward declaration of P1.
      |    declare procedure p1(i integer = 1) returns (o integer);
      | 
      |    -- Forward declaration of P2.
      |    declare procedure p2(i integer) returns (o integer);
      | 
      |    -- Implementation of P1 should not re-declare parameter default value.
      |    declare procedure p1(i integer) returns (o integer)
      |    as
      |    begin
      |        execute procedure p2(i) returning_values o;
      |    end
      | 
      |    declare procedure p2(i integer) returns (o integer)
      |    as
      |    begin
      |        o = i;
      |    end
      |begin
      |    execute procedure p1 returning_values o;
      |    suspend;
      |end
   

See alsoSection 7.7.5, “DECLARE FUNCTION”, CREATE PROCEDURE

A sample procedure from the employee.fdb database, showing simple usage of BEGIN …​ END blocks:

   |SET TERM ^;
   |CREATE OR ALTER PROCEDURE DEPT_BUDGET (
   |  DNO CHAR(3))
   |RETURNS (
   |  TOT DECIMAL(12,2))
   |AS
   |  DECLARE VARIABLE SUMB DECIMAL(12,2);
   |  DECLARE VARIABLE RDNO CHAR(3);
   |  DECLARE VARIABLE CNT  INTEGER;
   |BEGIN
   |  TOT = 0;
   | 
   |  SELECT BUDGET
   |  FROM DEPARTMENT
   |  WHERE DEPT_NO = :DNO
   |  INTO :TOT;
   | 
   |  SELECT COUNT(BUDGET)
   |  FROM DEPARTMENT
   |  WHERE HEAD_DEPT = :DNO
   |  INTO :CNT;
   | 
   |  IF (CNT = 0) THEN
   |    SUSPEND;
   | 
   |  FOR SELECT DEPT_NO
   |    FROM DEPARTMENT
   |    WHERE HEAD_DEPT = :DNO
   |    INTO :RDNO
   |  DO
   |  BEGIN
   |    EXECUTE PROCEDURE DEPT_BUDGET(:RDNO)
   |      RETURNING_VALUES :SUMB;
   |    TOT = TOT + SUMB;
   |  END
   | 
   |  SUSPEND;
   |END^
   |SET TERM ;^

See alsoSection 7.7.13, “EXIT”, SET TERM, Section 7.8.4, “WHEN …​ DO”

1. An example using the IF statement. Assume that the variables FIRST, LINE2 and LAST were declared earlier.

     |...
     |IF (FIRST IS NOT NULL) THEN
     |  LINE2 = FIRST || ' ' || LAST;
     |ELSE
     |  LINE2 = LAST;
     |...
   

2. Given IF …​ THEN …​ ELSE is a statement, it is possible to chain them together. Assume that the INT_VALUE and STRING_VALUE variables were declared earlier.

     |IF (INT_VALUE = 1) THEN
     |  STRING_VALUE = 'one';
     |ELSE IF (INT_VALUE = 2) THEN
     |  STRING_VALUE = 'two';
     |ELSE IF (INT_VALUE = 3) THEN
     |  STRING_VALUE = 'three';
     |ELSE
     |  STRING_VALUE = 'too much';
   

   This specific example can be replaced with a simple CASE or the DECODE function.

See alsoSection 7.7.9, “WHILE …​ DO”, CASE

A procedure calculating the sum of numbers from 1 to I shows how the looping construct is used.

   |CREATE PROCEDURE SUM_INT (I INTEGER)
   |RETURNS (S INTEGER)
   |AS
   |BEGIN
   |  s = 0;
   |  WHILE (i > 0) DO
   |  BEGIN
   |    s = s + i;
   |    i = i - 1;
   |  END
   |END

Executing the procedure in isql:

  |EXECUTE PROCEDURE SUM_INT(4);

the result is:

  |S
  |==========
  |10

See alsoSection 7.7.8, “IF …​ THEN …​ ELSE”, Section 7.7.10, “BREAK”, Section 7.7.11, “LEAVE”, Section 7.7.12, “CONTINUE”, Section 7.7.13, “EXIT”, Section 7.7.16, “FOR SELECT”, Section 7.7.17, “FOR EXECUTE STATEMENT”

1. Leaving a loop if an error occurs on an insert into the NUMBERS table. The code continues to be executed from the line C = 0.

      |...
      |WHILE (B < 10) DO
      |BEGIN
      |  INSERT INTO NUMBERS(B)
      |  VALUES (:B);
      |  B = B + 1;
      |  WHEN ANY DO
      |  BEGIN
      |    EXECUTE PROCEDURE LOG_ERROR (
      |      CURRENT_TIMESTAMP,
      |      'ERROR IN B LOOP');
      |    LEAVE;
      |  END
      |END
      |C = 0;
      |...
   

2. An example using labels in the LEAVE statement. LEAVE LOOPA terminates the outer loop and LEAVE LOOPB terminates the inner loop. Note that the plain LEAVE statement would be enough to terminate the inner loop.

      |...
      |STMT1 = 'SELECT NAME FROM FARMS';
      |LOOPA:
      |FOR EXECUTE STATEMENT :STMT1
      |INTO :FARM DO
      |BEGIN
      |  STMT2 = 'SELECT NAME ' || 'FROM ANIMALS WHERE FARM = ''';
      |  LOOPB:
      |  FOR EXECUTE STATEMENT :STMT2 || :FARM || ''''
      |  INTO :ANIMAL DO
      |  BEGIN
      |    IF (ANIMAL = 'FLUFFY') THEN
      |      LEAVE LOOPB;
      |    ELSE IF (ANIMAL = FARM) THEN
      |      LEAVE LOOPA;
      |    SUSPEND;
      |  END
      |END
      |...
   

See alsoSection 7.7.10, “BREAK”, Section 7.7.12, “CONTINUE”, Section 7.7.13, “EXIT”

Using the CONTINUE statement

  |FOR SELECT A, D
  |  FROM ATABLE INTO achar, ddate
  |DO
  |BEGIN
  |  IF (ddate < current_date - 30) THEN
  |    CONTINUE;
  |  /* do stuff */
  |END

See alsoSection 7.7.10, “BREAK”, Section 7.7.11, “LEAVE”, Section 7.7.13, “EXIT”

Using the EXIT statement in a selectable procedure

   |CREATE PROCEDURE GEN_100
   |  RETURNS (I INTEGER)
   |AS
   |BEGIN
   |  I = 1;
   |  WHILE (1=1) DO
   |  BEGIN
   |    SUSPEND;
   |    IF (I=100) THEN
   |      EXIT;
   |    I = I + 1;
   |  END
   |END

See alsoSection 7.7.10, “BREAK”, Section 7.7.11, “LEAVE”, Section 7.7.12, “CONTINUE”, Section 7.7.14, “SUSPEND”

Using the SUSPEND statement in a selectable procedure

   |CREATE PROCEDURE GEN_100
   |  RETURNS (I INTEGER)
   |AS
   |BEGIN
   |  I = 1;
   |  WHILE (1=1) DO
   |  BEGIN
   |    SUSPEND;
   |    IF (I=100) THEN
   |      EXIT;
   |    I = I + 1;
   |  END
   |END

See alsoSection 7.7.13, “EXIT”

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

   |CREATE PROCEDURE P_EXCESS (A_ID INT, A_TRAN INT = NULL, A_CONN INT = NULL)
   |  RETURNS (ID INT, TRAN INT, CONN INT)
   |AS
   |DECLARE S VARCHAR(255) = 'SELECT * FROM TTT WHERE ID = :ID';
   |DECLARE W VARCHAR(255) = '';
   |BEGIN
   |  IF (A_TRAN IS NOT NULL)
   |  THEN W = W || ' AND TRAN = :a';
   | 
   |  IF (A_CONN IS NOT NULL)
   |  THEN W = W || ' AND CONN = :b';
   | 
   |  IF (W <> '')
   |  THEN S = S || W;
   | 
   |  -- could raise error if TRAN or CONN is null
   |  -- FOR EXECUTE STATEMENT (:S) (a := :A_TRAN, b := A_CONN, id := A_ID)
   | 
   |  -- OK in all cases
   |  FOR EXECUTE STATEMENT (:S) (EXCESS a := :A_TRAN, EXCESS b := A_CONN, id := A_ID)
   |    INTO :ID, :TRAN, :CONN
   |      DO SUSPEND;
   |END

By default, the executed SQL statement runs within the current transaction. Using WITH AUTONOMOUS TRANSACTION causes a separate transaction to be started, with the same parameters as the current transaction. This separate transaction will be committed when the statement was executed without errors and rolled back otherwise.

The clause WITH COMMON TRANSACTION uses the current transaction whenever possible; this is the default behaviour. 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 combines the privileges of the calling procedure or trigger with those of the user, 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 connection_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 specification of which user will execute the SQL statement and with which role. The method of user login, and whether a separate connection is opened, depends 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 connection_string). No defaults are used for missing parameters

  • If all three are absent, and connection_string contains no hostname, then the new connection is established on the local server 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 connection_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 username (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 supplied parameter values. No defaults are used for missing parameters

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

If a parameter value is NULL or '' (empty string), the entire parameter is considered absent. Additionally, AS USER is considered absent if its value is equal to CURRENT_USER, and ROLE if it is the same as CURRENT_ROLE.

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. Execution is considerably slower than when the same statements are executed directly as PSQL code

4. Return values are strictly checked for data type 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 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.7.17, “FOR EXECUTE STATEMENT”

The optional AS CURSOR clause surfaces the result set of 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, to delete or update the current row before execution moves to the next row. In addition, it is possible to use the cursor name as a record variable (similar to OLD and NEW in triggers), allowing access to the columns of the result set (i.e. cursor_name.columnname).

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 module body, 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
   

   ☞

   Tip

   Instead of nesting statements, this is generally better solved by using a single statements with a join.

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
   

4. Using an implicitly declared cursor as a cursor variable

      |EXECUTE BLOCK
      | RETURNS (o CHAR(63))
      |AS
      |BEGIN
      |  FOR SELECT rdb$relation_name AS name
      |    FROM rdb$relations AS CURSOR c
      |  DO
      |  BEGIN
      |    o = c.name;
      |    SUSPEND;
      |  END
      |END
   

5. Disambiguating cursor variables within queries

      |EXECUTE BLOCK
      |  RETURNS (o1 CHAR(63), o2 CHAR(63))
      |AS
      |BEGIN
      |  FOR SELECT rdb$relation_name
      |    FROM rdb$relations
      |    WHERE
      |      rdb$relation_name = 'RDB$RELATIONS' AS CURSOR c
      |  DO
      |  BEGIN
      |    FOR SELECT
      |        -- with a prefix resolves to the cursor
      |        :c.rdb$relation_name x1,
      |        -- no prefix as an alias for the rdb$relations table
      |        c.rdb$relation_name x2
      |      FROM rdb$relations c
      |      WHERE
      |        rdb$relation_name = 'RDB$DATABASE' AS CURSOR d
      |    DO
      |    BEGIN
      |      o1 = d.x1;
      |      o2 = d.x2;
      |      SUSPEND;
      |    END
      |  END
      |END
   

See alsoSection 7.7.4, “DECLARE .. CURSOR”, Section 7.7.10, “BREAK”, Section 7.7.11, “LEAVE”, Section 7.7.12, “CONTINUE”, Section 7.7.13, “EXIT”, SELECT, UPDATE, DELETE

Executing a dynamically constructed SELECT query that returns a data set

   |CREATE PROCEDURE DynamicSampleThree (
   |   Q_FIELD_NAME VARCHAR(100),
   |   Q_TABLE_NAME VARCHAR(100)
   |) RETURNS(
   |  LINE VARCHAR(32000)
   |)
   |AS
   |  DECLARE VARIABLE P_ONE_LINE VARCHAR(100);
   |BEGIN
   |  LINE = '';
   |  FOR
   |    EXECUTE STATEMENT
   |      'SELECT T1.' || :Q_FIELD_NAME ||
   |      ' FROM ' || :Q_TABLE_NAME || ' T1 '
   |    INTO :P_ONE_LINE
   |  DO
   |    IF (:P_ONE_LINE IS NOT NULL) THEN
   |      LINE = :LINE || :P_ONE_LINE || ' ';
   |  SUSPEND;
   |END

See alsoSection 7.7.15, “EXECUTE STATEMENT”, Section 7.7.10, “BREAK”, Section 7.7.11, “LEAVE”, Section 7.7.12, “CONTINUE”

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 alsoSection 7.7.4, “DECLARE .. CURSOR”, Section 7.7.19, “FETCH”, Section 7.7.20, “CLOSE”

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

See alsoSection 7.7.4, “DECLARE .. CURSOR”, Section 7.7.18, “OPEN”, Section 7.7.20, “CLOSE”

See Section 7.7.19.1, “FETCH Examples”

See alsoSection 7.7.4, “DECLARE .. CURSOR”, Section 7.7.18, “OPEN”, Section 7.7.19, “FETCH”

Using an autonomous transaction in a trigger for the database ON CONNECT event, to log all connection attempts, including those that failed:

   |CREATE TRIGGER TR_CONNECT ON CONNECT
   |AS
   |BEGIN
   |  -- Logging all attempts to connect to the database
   |  IN AUTONOMOUS TRANSACTION DO
   |    INSERT INTO LOG(MSG)
   |    VALUES ('USER ' || CURRENT_USER || ' CONNECTS.');
   |  IF (EXISTS(SELECT *
   |             FROM BLOCKED_USERS
   |             WHERE USERNAME = CURRENT_USER)) THEN
   |  BEGIN
   |    -- Logging that the attempt to connect
   |    -- to the database failed and sending
   |    -- a message about the event
   |    IN AUTONOMOUS TRANSACTION DO
   |    BEGIN
   |      INSERT INTO LOG(MSG)
   |      VALUES ('USER ' || CURRENT_USER || ' REFUSED.');
   |      POST_EVENT 'CONNECTION ATTEMPT BY BLOCKED USER!';
   |    END
   |    -- now calling an exception
   |    EXCEPTION EX_BADUSER;
   |  END
   |END

See alsoTransaction Control

Notifying the listening applications about inserting a record into the SALES table:

  |CREATE TRIGGER POST_NEW_ORDER FOR SALES
  |ACTIVE AFTER INSERT POSITION 0
  |AS
  |BEGIN
  |  POST_EVENT 'new_order';
  |END

See CREATE FUNCTION Examples