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. This separate transaction will be committed when the statement was executed without errors and rolled back otherwise. See Section 7.7.21, “IN AUTONOMOUS TRANSACTION” for more details on autonomous transactions.

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