7.7 Writing the Body Code

This section takes a closer look at the procedural SQL language constructs and statements that are available for coding the body of a stored procedure, trigger or anonymous PSQL block.

Colon Marker (:)

The colon marker prefix (:) is used in PSQL to mark a reference to a variable in a DML statement. The colon marker is not required before variable names in other PSQL code.

Since Firebird 3.0, the colon prefix can also be used for the NEW and OLD contexts, and for cursor variables.

7.7.1 Assignment Statements

Used forAssigning a value to a variable

Available inPSQL

Syntax

  1. varname = <value_expr>;

Table 7.7.1.1 Assignment Statement Parameters

ArgumentDescription

varname

Name of a parameter or local variable

value_expr

An expression, constant or variable whose value resolves to the same data type as varname

PSQL uses the equal symbol (=) as its assignment operator. The assignment statement assigns an SQL expression value on the right to the variable on the left of the operator. The expression can be any valid SQL expression: it may contain literals, internal variable names, arithmetic, logical and string operations, calls to internal functions, stored functions or external functions (UDFs).

7.7.1.1 Example using assignment statements

  1. CREATE PROCEDURE MYPROC (
  2. a INTEGER,
  3. b INTEGER,
  4. name VARCHAR (30)
  5. )
  6. RETURNS (
  7. c INTEGER,
  8. str VARCHAR(100))
  9. AS
  10. BEGIN
  11. -- assigning a constant
  12. c = 0;
  13. str = '';
  14. SUSPEND;
  15. -- assigning expression values
  16. c = a + b;
  17. str = name || CAST(b AS VARCHAR(10));
  18. SUSPEND;
  19. -- assigning expression value
  20. -- built by a query
  21. c = (SELECT 1 FROM rdb$database);
  22. -- assigning a value from a context variable
  23. str = CURRENT_USER;
  24. SUSPEND;
  25. END

See alsoSection 7.7.3, DECLARE VARIABLE

7.7.2 Management Statements in PSQL

Management statement are allowed in PSQL blocks (triggers, procedures, functions and EXECUTE BLOCK), which is especially helpful for applications that need some management statements to be executed at the start of a session, specifically in ON CONNECT triggers.

The management statements permitted in PSQL are:

7.7.2.1 Example of Management Statements in PSQL

  1. create or alter trigger on_connect on connect
  2. as
  3. begin
  4. set bind of decfloat to double precision;
  5. set time zone 'America/Sao_Paulo';
  6. end

Caution

Although useful as a workaround, using ON CONNECT triggers to configure bind and time zone is usually not the right approach.

See alsoManagement Statements

7.7.3 DECLARE VARIABLE

Used forDeclaring a local variable

Available inPSQL

Syntax

  1. DECLARE [VARIABLE] varname
  2. <domain_or_non_array_type> [NOT NULL] [COLLATE collation]
  3. [{DEFAULT | = } <initvalue>];
  4. <domain_or_non_array_type> ::=
  5. !! See Scalar Data Types Syntax !!
  6. <initvalue> ::= <literal> | <context_var>

Table 7.7.3.1 DECLARE VARIABLE Statement Parameters

ArgumentDescription

varname

Name of the local variable

collation

Collation sequence

initvalue

Initial value for this variable

literal

Literal of a type compatible with the type of the local variable

context_var

Any context variable whose type is compatible with the type of the local variable

The statement DECLARE [VARIABLE] is used for declaring a local variable. The keyword VARIABLE can be omitted. One DECLARE [VARIABLE] statement is required for each local variable. Any number of DECLARE [VARIABLE] statements can be included and in any order. The name of a local variable must be unique among the names of local variables and input and output parameters declared for the module.

Note

A special case of DECLARE [VARIABLE] — declaring cursors — is covered separately in Section 7.7.4, DECLARE .. CURSOR

7.7.3.1 Data Type for Variables

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.

7.7.3.2 NOT NULL Constraint

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.

7.7.3.3 CHARACTER SET and COLLATE clauses

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.

7.7.3.4 Initializing a Variable

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, including NULL.

Tip

Be sure to use this clause for any variables that have a NOT NULL constraint and do not otherwise have a default value available.

7.7.3.5 Examples of various ways to declare local variables

  1. CREATE OR ALTER PROCEDURE SOME_PROC
  2. AS
  3. -- Declaring a variable of the INT type
  4. DECLARE I INT;
  5. -- Declaring a variable of the INT type that does not allow NULL
  6. DECLARE VARIABLE J INT NOT NULL;
  7. -- Declaring a variable of the INT type with the default value of 0
  8. DECLARE VARIABLE K INT DEFAULT 0;
  9. -- Declaring a variable of the INT type with the default value of 1
  10. DECLARE VARIABLE L INT = 1;
  11. -- Declaring a variable based on the COUNTRYNAME domain
  12. DECLARE FARM_COUNTRY COUNTRYNAME;
  13. -- Declaring a variable of the type equal to the COUNTRYNAME domain
  14. DECLARE FROM_COUNTRY TYPE OF COUNTRYNAME;
  15. -- Declaring a variable with the type of the CAPITAL column in the COUNTRY table
  16. DECLARE CAPITAL TYPE OF COLUMN COUNTRY.CAPITAL;
  17. BEGIN
  18. /* PSQL statements */
  19. END

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

7.7.4 DECLARE .. CURSOR

Used forDeclaring a named cursor

Available inPSQL

Syntax

  1. DECLARE [VARIABLE] cursor_name
  2. [[NO] SCROLL] CURSOR
  3. FOR (<select>);

Table 7.7.4.1 DECLARE …​ CURSOR Statement Parameters

ArgumentDescription

cursor_name

Cursor name

select

SELECT statement

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

Note

Syntactically, the DECLARE …​ CURSOR statement is a special case of Section 7.7.3, DECLARE VARIABLE.

7.7.4.1 Forward-Only and Scrollable Cursors

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 commands raise an error. Scrollable cursors allow you to move not only forward in the dataset, but also back, asl well as N positions relative to the current position.

Warning

Scrollable cursors are materialized as a temporary dataset, as such, they consume additional memory or disk space, so use them only when you really need them.

7.7.4.2 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 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:

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

Unstable Variables and Cursors

If the value of the PSQL variable used in the SELECT statement of the cursor changes during the execution of the loop, then its new value may — but not always — be used when selecting the next rows. It is better to avoid such situations. If you really need this behaviour, then you should thoroughly test your code and make sure you understand how changes to the variable affect the query results.

Note particularly that the behaviour may depend on the query plan, specifically on the indexes being used. Currently, there are no strict rules for this behaviour, and this may change in future versions of Firebird.

7.7.4.3 Examples Using Named Cursors

  1. Declaring a named cursor in the trigger.

    1. CREATE OR ALTER TRIGGER TBU_STOCK
    2. BEFORE UPDATE ON STOCK
    3. AS
    4. DECLARE C_COUNTRY CURSOR FOR (
    5. SELECT
    6. COUNTRY,
    7. CAPITAL
    8. FROM COUNTRY
    9. );
    10. BEGIN
    11. /* PSQL statements */
    12. END
  2. Declaring a scrollable cursor

    1. EXECUTE BLOCK
    2. RETURNS (
    3. N INT,
    4. RNAME CHAR(63))
    5. AS
    6. - Declaring a scrollable cursor
    7. DECLARE C SCROLL CURSOR FOR (
    8. SELECT
    9. ROW_NUMBER() OVER (ORDER BY RDB$RELATION_NAME) AS N,
    10. RDB$RELATION_NAME
    11. FROM RDB$RELATIONS
    12. ORDER BY RDB$RELATION_NAME);
    13. BEGIN
    14. / * PSQL statements * /
    15. END
  3. A collection of scripts for creating views with a PSQL block using named cursors.

    1. EXECUTE BLOCK
    2. RETURNS (
    3. SCRIPT BLOB SUB_TYPE TEXT)
    4. AS
    5. DECLARE VARIABLE FIELDS VARCHAR(8191);
    6. DECLARE VARIABLE FIELD_NAME TYPE OF RDB$FIELD_NAME;
    7. DECLARE VARIABLE RELATION RDB$RELATION_NAME;
    8. DECLARE VARIABLE SOURCE TYPE OF COLUMN RDB$RELATIONS.RDB$VIEW_SOURCE;
    9. DECLARE VARIABLE CUR_R CURSOR FOR (
    10. SELECT
    11. RDB$RELATION_NAME,
    12. RDB$VIEW_SOURCE
    13. FROM
    14. RDB$RELATIONS
    15. WHERE
    16. RDB$VIEW_SOURCE IS NOT NULL);
    17. -- Declaring a named cursor where
    18. -- a local variable is used
    19. DECLARE CUR_F CURSOR FOR (
    20. SELECT
    21. RDB$FIELD_NAME
    22. FROM
    23. RDB$RELATION_FIELDS
    24. WHERE
    25. -- It is important that the variable must be declared earlier
    26. RDB$RELATION_NAME = :RELATION);
    27. BEGIN
    28. OPEN CUR_R;
    29. WHILE (1 = 1) DO
    30. BEGIN
    31. FETCH CUR_R
    32. INTO :RELATION, :SOURCE;
    33. IF (ROW_COUNT = 0) THEN
    34. LEAVE;
    35. FIELDS = NULL;
    36. -- The CUR_F cursor will use the value
    37. -- of the RELATION variable initiated above
    38. OPEN CUR_F;
    39. WHILE (1 = 1) DO
    40. BEGIN
    41. FETCH CUR_F
    42. INTO :FIELD_NAME;
    43. IF (ROW_COUNT = 0) THEN
    44. LEAVE;
    45. IF (FIELDS IS NULL) THEN
    46. FIELDS = TRIM(FIELD_NAME);
    47. ELSE
    48. FIELDS = FIELDS || ', ' || TRIM(FIELD_NAME);
    49. END
    50. CLOSE CUR_F;
    51. SCRIPT = 'CREATE VIEW ' || RELATION;
    52. IF (FIELDS IS NOT NULL) THEN
    53. SCRIPT = SCRIPT || ' (' || FIELDS || ')';
    54. SCRIPT = SCRIPT || ' AS ' || ASCII_CHAR(13);
    55. SCRIPT = SCRIPT || SOURCE;
    56. SUSPEND;
    57. END
    58. CLOSE CUR_R;
    59. END

See alsoSection 7.7.18, OPEN, Section 7.7.19, FETCH, Section 7.7.20, CLOSE

7.7.5 DECLARE FUNCTION

Used forDeclaring a sub-function

Available inPSQL

Syntax

  1. <declare-subfunc> ::= <subfunc-forward> | <subfunc-def>
  2. <subfunc-forward> ::= <subfunc-header>;
  3. <subfunc-def> ::= <subfunc-header> <psql-module-body>
  4. <subfunc-header> ::=
  5. DECLARE FUNCTION subfuncname [ ( [ <in_params> ] ) ]
  6. RETURNS <domain_or_non_array_type> [COLLATE collation]
  7. [DETERMINISTIC]
  8. <in_params> ::=
  9. !! See CREATE FUNCTION Syntax !!
  10. <domain_or_non_array_type> ::=
  11. !! See Scalar Data Types Syntax !!
  12. <psql-module-body> ::=
  13. !! See Syntax of Module Body !!

Table 7.7.5.1 DECLARE FUNCTION Statement Parameters

ArgumentDescription

subfuncname

Sub-function name

collation

Collation name

The DECLARE FUNCTION statement declares a sub-function. A sub-function is only visible to the PSQL module that defined the sub-function.

Sub-functions have a number of restrictions:

  • A sub-function cannot be nested in another subroutine. Subroutines are only supported in top-level PSQL modules (stored procedures, stored functions, triggers and anonymous PSQL blocks). This restriction is not enforced by the syntax, but attempts to create nested sub-functions will raise an error feature is not supported with detail message nested sub function.

  • Currently, the sub-function has no direct access to use variables and cursors from its parent module. However, it can access other routines from its parent modules, including recursive calls to itself. In some cases a forward declaration of the routine may be necessary.

A sub-function can be forward declared to resolve mutual dependencies between subroutine, and must be followed by its actual definition. When a sub-function is forward declared and has parameters with default values, the default values should only be specified in the forward declaration, and should not be repeated in subfunc_def.

Note

Declaring a sub-function with the same name as a stored function will hide that stored function from your module. It will not be possible to call that stored function.

Note

Contrary to DECLARE [VARIABLE], a DECLARE FUNCTION is not terminated by a semicolon. The END of its main BEGIN …​ END block is considered its terminator.

7.7.5.1 Examples of Sub-Functions

  1. Subfunction within a stored function

    1. CREATE OR ALTER FUNCTION FUNC1 (n1 INTEGER, n2 INTEGER)
    2. RETURNS INTEGER
    3. AS
    4. - Subfunction
    5. DECLARE FUNCTION SUBFUNC (n1 INTEGER, n2 INTEGER)
    6. RETURNS INTEGER
    7. AS
    8. BEGIN
    9. RETURN n1 + n2;
    10. END
    11. BEGIN
    12. RETURN SUBFUNC (n1, n2);
    13. END
  2. Recursive function call

    1. execute block returns (i integer, o integer)
    2. as
    3. -- Recursive function without forward declaration.
    4. declare function fibonacci(n integer) returns integer
    5. as
    6. begin
    7. if (n = 0 or n = 1) then
    8. return n;
    9. else
    10. return fibonacci(n - 1) + fibonacci(n - 2);
    11. end
    12. begin
    13. i = 0;
    14. while (i < 10)
    15. do
    16. begin
    17. o = fibonacci(i);
    18. suspend;
    19. i = i + 1;
    20. end
    21. end

See alsoSection 7.7.6, DECLARE PROCEDURE, CREATE FUNCTION

7.7.6 DECLARE PROCEDURE

Used forDeclaring a sub-procedure

Available inPSQL

Syntax

  1. <declare-subproc> ::= <subproc-forward> | <subproc-def>
  2. <subproc-forward> ::= <subproc-header>;
  3. <subproc-def> ::= <subproc-header> <psql-module-body>
  4. <subproc-header> ::=
  5. DECLARE subprocname [ ( [ <in_params> ] ) ]
  6. [RETURNS (<out_params>)]
  7. <in_params> ::=
  8. !! See CREATE PROCEDURE Syntax !!
  9. <domain_or_non_array_type> ::=
  10. !! See Scalar Data Types Syntax !!
  11. <psql-module-body> ::=
  12. !! See Syntax of Module Body !!

Table 7.7.6.1 DECLARE PROCEDURE Statement Parameters

ArgumentDescription

subprocname

Sub-procedure name

collation

Collation name

The DECLARE PROCEDURE statement declares a sub-procedure. A sub-procedure is only visible to the PSQL module that defined the sub-procedure.

Sub-procedures have a number of restrictions:

  • A sub-procedure cannot be nested in another subroutine. Subroutines are only supported in top-level PSQL modules (stored procedures, stored functions, triggers and anonymous PSQL blocks). This restriction is not enforced by the syntax, but attempts to create nested sub-procedures will raise an error feature is not supported with detail message nested sub procedure.

  • Currently, the sub-procedure has no direct access to use variables and cursors from its parent module. It can access other routines from its parent modules. In some cases a forward declaration may be necessary.

A sub-procedure can be forward declared to resolve mutual dependencies between subroutines, and must be followed by its actual definition. When a sub-procedure is forward declared and has parameters with default values, the default values should only be specified in the forward declaration, and should not be repeated in subproc_def.

Note

Declaring a sub-procedure with the same name as a stored procedure, table or view will hide that stored procedure, table or view from your module. It will not be possible to call that stored procedure, table or view.

Note

Contrary to DECLARE [VARIABLE], a DECLARE PROCEDURE is not terminated by a semicolon. The END of its main BEGIN …​ END block is considered its terminator.

7.7.6.1 Examples of Sub-Procedures

  1. Subroutines in EXECUTE BLOCK

    1. EXECUTE BLOCK
    2. RETURNS (name VARCHAR(63))
    3. AS
    4. -- Sub-procedure returning a list of tables
    5. DECLARE PROCEDURE get_tables
    6. RETURNS (table_name VARCHAR(63))
    7. AS
    8. BEGIN
    9. FOR SELECT RDB$RELATION_NAME
    10. FROM RDB$RELATIONS
    11. WHERE RDB$VIEW_BLR IS NULL
    12. INTO table_name
    13. DO SUSPEND;
    14. END
    15. -- Sub-procedure returning a list of views
    16. DECLARE PROCEDURE get_views
    17. RETURNS (view_name VARCHAR(63))
    18. AS
    19. BEGIN
    20. FOR SELECT RDB$RELATION_NAME
    21. FROM RDB$RELATIONS
    22. WHERE RDB$VIEW_BLR IS NOT NULL
    23. INTO view_name
    24. DO SUSPEND;
    25. END
    26. BEGIN
    27. FOR SELECT table_name
    28. FROM get_tables
    29. UNION ALL
    30. SELECT view_name
    31. FROM get_views
    32. INTO name
    33. DO SUSPEND;
    34. END
  2. With forward declaration and parameter with default value

    1. execute block returns (o integer)
    2. as
    3. -- Forward declaration of P1.
    4. declare procedure p1(i integer = 1) returns (o integer);
    5. -- Forward declaration of P2.
    6. declare procedure p2(i integer) returns (o integer);
    7. -- Implementation of P1 should not re-declare parameter default value.
    8. declare procedure p1(i integer) returns (o integer)
    9. as
    10. begin
    11. execute procedure p2(i) returning_values o;
    12. end
    13. declare procedure p2(i integer) returns (o integer)
    14. as
    15. begin
    16. o = i;
    17. end
    18. begin
    19. execute procedure p1 returning_values o;
    20. suspend;
    21. end

See alsoSection 7.7.5, DECLARE FUNCTION, CREATE PROCEDURE

7.7.7 BEGIN …​ END

Used forDelimiting a block of statements

Available inPSQL

Syntax

  1. <block> ::=
  2. BEGIN
  3. [<compound_statement> ...]
  4. END
  5. <compound_statement> ::= {<block> | <statement>}

The BEGIN …​ END construct is a two-part statement that wraps a block of statements that are executed as one unit of code. Each block starts with the half-statement BEGIN and ends with the other half-statement END. Blocks can be nested a maximum depth of 512 nested blocks. A block can be empty, allowing them to act as stubs, without the need to write dummy statements.

The BEGIN and END statements have no line terminators (semicolon). However, when defining or altering a PSQL module in the isql utility, that application requires that the last END statement be followed by its own terminator character, that was previously switched — using SET TERM — to some string other than a semicolon. That terminator is not part of the PSQL syntax.

The final, or outermost, END statement in a trigger terminates the trigger. What the final END statement does in a stored procedure depends on the type of procedure:

  • In a selectable procedure, the final END statement returns control to the caller, returning SQLCODE 100, indicating that there are no more rows to retrieve

  • In an executable procedure, the final END statement returns control to the caller, along with the current values of any output parameters defined.

7.7.7.1 BEGIN …​ END Examples

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

  1. SET TERM ^;
  2. CREATE OR ALTER PROCEDURE DEPT_BUDGET (
  3. DNO CHAR(3))
  4. RETURNS (
  5. TOT DECIMAL(12,2))
  6. AS
  7. DECLARE VARIABLE SUMB DECIMAL(12,2);
  8. DECLARE VARIABLE RDNO CHAR(3);
  9. DECLARE VARIABLE CNT INTEGER;
  10. BEGIN
  11. TOT = 0;
  12. SELECT BUDGET
  13. FROM DEPARTMENT
  14. WHERE DEPT_NO = :DNO
  15. INTO :TOT;
  16. SELECT COUNT(BUDGET)
  17. FROM DEPARTMENT
  18. WHERE HEAD_DEPT = :DNO
  19. INTO :CNT;
  20. IF (CNT = 0) THEN
  21. SUSPEND;
  22. FOR SELECT DEPT_NO
  23. FROM DEPARTMENT
  24. WHERE HEAD_DEPT = :DNO
  25. INTO :RDNO
  26. DO
  27. BEGIN
  28. EXECUTE PROCEDURE DEPT_BUDGET(:RDNO)
  29. RETURNING_VALUES :SUMB;
  30. TOT = TOT + SUMB;
  31. END
  32. SUSPEND;
  33. END^
  34. SET TERM ;^

See alsoSection 7.7.13, EXIT, SET TERM

7.7.8 IF …​ THEN …​ ELSE

Used forConditional branching

Available inPSQL

Syntax

  1. IF (<condition>)
  2. THEN <compound_statement>
  3. [ELSE <compound_statement>]

Table 7.7.8.1 IF …​ THEN …​ ELSE Parameters

ArgumentDescription

condition

A logical condition returning TRUE, FALSE or UNKNOWN

compound_statement

A single statement, or two or more statements wrapped in BEGIN …​ END

The conditional branch statement IF …​ THEN is used to branch the execution process in a PSQL module. The condition is always enclosed in parentheses. If the condition returns the value TRUE, execution branches to the statement or the block of statements after the keyword THEN. If an ELSE is present, and the condition returns FALSE or UNKNOWN, execution branches to the statement or the block of statements after it.

Multi-Branch Decisions

PSQL does not provide more advanced multi-branch jumps, such as CASE or SWITCH. However, it is possible to chain IF …​ THEN …​ ELSE statements, see the example section below. Alternatively, the CASE statement from DSQL is available in PSQL and is able to satisfy at least some use cases in the manner of a switch:

  1. CASE <test_expr>
  2. WHEN <expr> THEN <result>
  3. [WHEN <expr> THEN <result> ...]
  4. [ELSE <defaultresult>]
  5. END
  6. CASE
  7. WHEN <bool_expr> THEN <result>
  8. [WHEN <bool_expr> THEN <result> ...]
  9. [ELSE <defaultresult>]
  10. END

Example in PSQL

  1. ...
  2. C = CASE
  3. WHEN A=2 THEN 1
  4. WHEN A=1 THEN 3
  5. ELSE 0
  6. END;
  7. ...

7.7.8.1 IF Examples

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

    1. ...
    2. IF (FIRST IS NOT NULL) THEN
    3. LINE2 = FIRST || ' ' || LAST;
    4. ELSE
    5. LINE2 = LAST;
    6. ...
  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.

    1. IF (INT_VALUE = 1) THEN
    2. STRING_VALUE = 'one';
    3. ELSE IF (INT_VALUE = 2) THEN
    4. STRING_VALUE = 'two';
    5. ELSE IF (INT_VALUE = 3) THEN
    6. STRING_VALUE = 'three';
    7. ELSE
    8. 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

7.7.9 WHILE …​ DO

Used forLooping constructs

Available inPSQL

Syntax

  1. [label:]
  2. WHILE <condition> DO
  3. <compound_statement>

Table 7.7.9.1 WHILE …​ DO Parameters

ArgumentDescription

label

Optional label for LEAVE and CONTINUE. Follows the rules for identifiers.

condition

A logical condition returning TRUE, FALSE or UNKNOWN

compound_statement

A single statement, or two or more statements wrapped in BEGIN …​ END

A WHILE statement implements the looping construct in PSQL. The statement or the block of statements will be executed until the condition returns TRUE. Loops can be nested to any depth.

7.7.9.1 WHILE …​ DO Examples

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

  1. CREATE PROCEDURE SUM_INT (I INTEGER)
  2. RETURNS (S INTEGER)
  3. AS
  4. BEGIN
  5. s = 0;
  6. WHILE (i > 0) DO
  7. BEGIN
  8. s = s + i;
  9. i = i - 1;
  10. END
  11. END

Executing the procedure in isql:

  1. EXECUTE PROCEDURE SUM_INT(4);

the result is:

  1. S
  2. ==========
  3. 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

7.7.10 BREAK

Used forExiting a loop

Available inPSQL

Syntax

  1. [label:]
  2. <loop_stmt>
  3. BEGIN
  4. ...
  5. BREAK;
  6. ...
  7. END
  8. <loop_stmt> ::=
  9. FOR <select_stmt> INTO <var_list> DO
  10. | FOR EXECUTE STATEMENT ... INTO <var_list> DO
  11. | WHILE (<condition>)} DO

Table 7.7.10.1 BREAK Statement Parameters

ArgumentDescription

label

Label

select_stmt

SELECT statement

condition

A logical condition returning TRUE, FALSE or UNKNOWN

The BREAK statement immediately terminates the inner loop of a WHILE or FOR looping statement. Code continues to be executed from the first statement after the terminated loop block.

BREAK is similar to LEAVE, except it doesn’t support a label.

See alsoSection 7.7.11, LEAVE

7.7.11 LEAVE

Used forExiting a loop

Available inPSQL

Syntax

  1. [label:]
  2. <loop_stmt>
  3. BEGIN
  4. ...
  5. LEAVE [label];
  6. ...
  7. END
  8. <loop_stmt> ::=
  9. FOR <select_stmt> INTO <var_list> DO
  10. | FOR EXECUTE STATEMENT ... INTO <var_list> DO
  11. | WHILE (<condition>)} DO

Table 7.7.11.1 LEAVE Statement Parameters

ArgumentDescription

label

Label

select_stmt

SELECT statement

condition

A logical condition returning TRUE, FALSE or UNKNOWN

The LEAVE statement immediately terminates the inner loop of a WHILE or FOR looping statement. Using the optional label parameter, LEAVE can also exit an outer loop, that is, the loop labelled with label. Code continues to be executed from the first statement after the terminated loop block.

7.7.11.1 LEAVE Examples

  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.

    1. ...
    2. WHILE (B < 10) DO
    3. BEGIN
    4. INSERT INTO NUMBERS(B)
    5. VALUES (:B);
    6. B = B + 1;
    7. WHEN ANY DO
    8. BEGIN
    9. EXECUTE PROCEDURE LOG_ERROR (
    10. CURRENT_TIMESTAMP,
    11. 'ERROR IN B LOOP');
    12. LEAVE;
    13. END
    14. END
    15. C = 0;
    16. ...
  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.

    1. ...
    2. STMT1 = 'SELECT NAME FROM FARMS';
    3. LOOPA:
    4. FOR EXECUTE STATEMENT :STMT1
    5. INTO :FARM DO
    6. BEGIN
    7. STMT2 = 'SELECT NAME ' || 'FROM ANIMALS WHERE FARM = ''';
    8. LOOPB:
    9. FOR EXECUTE STATEMENT :STMT2 || :FARM || ''''
    10. INTO :ANIMAL DO
    11. BEGIN
    12. IF (ANIMAL = 'FLUFFY') THEN
    13. LEAVE LOOPB;
    14. ELSE IF (ANIMAL = FARM) THEN
    15. LEAVE LOOPA;
    16. ELSE
    17. SUSPEND;
    18. END
    19. END
    20. ...

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

7.7.12 CONTINUE

Used forContinuing with the next iteration of a loop

Available inPSQL

Syntax

  1. [label:]
  2. <loop_stmt>
  3. BEGIN
  4. ...
  5. CONTINUE [label];
  6. ...
  7. END
  8. <loop_stmt> ::=
  9. FOR <select_stmt> INTO <var_list> DO
  10. | FOR EXECUTE STATEMENT ... INTO <var_list> DO
  11. | WHILE (<condition>)} DO

Table 7.7.12.1 CONTINUE Statement Parameters

ArgumentDescription

label

Label

select_stmt

SELECT statement

condition

A logical condition returning TRUE, FALSE or UNKNOWN

The CONTINUE statement skips the remainer of the current block of a loop and starts the next iteration of the current WHILE or FOR loop. Using the optional label parameter, CONTINUE can also start the next iteration of an outer loop, that is, the loop labelled with label.

7.7.12.1 CONTINUE Examples

Using the CONTINUE statement

  1. FOR SELECT A, D
  2. FROM ATABLE INTO achar, ddate
  3. DO
  4. BEGIN
  5. IF (ddate < current_date - 30) THEN
  6. CONTINUE;
  7. ELSE
  8. BEGIN
  9. /* do stuff */
  10. END
  11. END

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

7.7.13 EXIT

Used forTerminating module execution

Available inPSQL

Syntax

  1. EXIT;

The EXIT statement causes execution of the current PSQL module to jump to the final END statement from any point in the code, thus terminating the program.

Calling EXIT in a function will result in the function returning NULL.

7.7.13.1 EXIT Examples

Using the EXIT statement in a selectable procedure

  1. CREATE PROCEDURE GEN_100
  2. RETURNS (I INTEGER)
  3. AS
  4. BEGIN
  5. I = 1;
  6. WHILE (1=1) DO
  7. BEGIN
  8. SUSPEND;
  9. IF (I=100) THEN
  10. EXIT;
  11. I = I + 1;
  12. END
  13. END

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

7.7.14 SUSPEND

Used forPassing output to the buffer and suspending execution while waiting for caller to fetch it

Available inPSQL

Syntax

  1. SUSPEND;

The SUSPEND statement is used in a selectable stored procedure to pass the values of output parameters to a buffer and suspend execution. Execution remains suspended until the calling application fetches the contents of the buffer. Execution resumes from the statement directly after the SUSPEND statement. In practice, this is likely to be a new iteration of a looping process.

Important Notes

  1. The SUSPEND statement can only occur in stored procedures or sub-procedures

  2. The presence of the SUSPEND keyword defines a stored procedure as a selectable procedure

  3. Applications using interfaces that wrap the API perform the fetches from selectable procedures transparently.

  4. If a selectable procedure is executed using EXECUTE PROCEDURE, it behaves as an executable procedure. When a SUSPEND statement is executed in such a stored procedure, it is the same as executing the EXIT statement, resulting in immediate termination of the procedure.

  5. SUSPENDbreaks the atomicity of the block in which it is located. If an error occurs in a selectable procedure, statements executed after the final SUSPEND statement will be rolled back. Statements that executed before the final SUSPEND statement will not be rolled back unless the transaction is rolled back.

7.7.14.1 SUSPEND Examples

Using the SUSPEND statement in a selectable procedure

  1. CREATE PROCEDURE GEN_100
  2. RETURNS (I INTEGER)
  3. AS
  4. BEGIN
  5. I = 1;
  6. WHILE (1=1) DO
  7. BEGIN
  8. SUSPEND;
  9. IF (I=100) THEN
  10. EXIT;
  11. I = I + 1;
  12. END
  13. END

See alsoSection 7.7.13, EXIT

7.7.15 EXECUTE STATEMENT

Used forExecuting dynamically created SQL statements

Available inPSQL

Syntax

  1. <execute_statement> ::= EXECUTE STATEMENT <argument>
  2. [<option> ...]
  3. [INTO <variables>];
  4. <argument> ::= <paramless_stmt>
  5. | (<paramless_stmt>)
  6. | (<stmt_with_params>) (<param_values>)
  7. <param_values> ::= <named_values> | <positional_values>
  8. <named_values> ::= <named_value> [, <named_value> ...]
  9. <named_value> ::= [EXCESS] paramname := <value_expr>
  10. <positional_values> ::= <value_expr> [, <value_expr> ...]
  11. <option> ::=
  12. WITH {AUTONOMOUS | COMMON} TRANSACTION
  13. | WITH CALLER PRIVILEGES
  14. | AS USER user
  15. | PASSWORD password
  16. | ROLE role
  17. | ON EXTERNAL [DATA SOURCE] <connection_string>
  18. <connection_string> ::=
  19. !! See <filespec> in the CREATE DATABASE syntax !!
  20. <variables> ::= [:]varname [, [:]varname ...]

Table 7.7.15.1 EXECUTE STATEMENT Statement Parameters

ArgumentDescription

paramless_stmt

Literal string or variable containing a non-parameterized SQL query

stmt_with_params

Literal string or variable containing a parameterized SQL query

paramname

SQL query parameter name

value_expr

SQL expression resolving to a value

user

Username. It can be a string, CURRENT_USER or a string variable

password

Password. It can be a string or a string variable

role

Role. It can be a string, CURRENT_ROLE or a string variable

connection_string

Connection string. It can be a string literal or a string variable

varname

Variable

The statement EXECUTE STATEMENT takes a string parameter and executes it as if it were a DSQL statement. If the statement returns data, it can be passed to local variables by way of an INTO clause.

Note

EXECUTE STATEMENT can only produce a single row of data. Statements producing multiple rows of data must be executed with Section 7.7.17, FOR EXECUTE STATEMENT.

7.7.15.1 Parameterized Statements

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

7.7.15.1.1 Special Rules for Parameterized Statements
  1. Named and positional parameters cannot be mixed in one query

  2. Each parameter must be used in the statement text.

    To relax this rule, named parameters can be prefixed with the keyword EXCESS to indicate that the parameter may be absent from the statement text. This option is useful for dynamically generated statements that conditionally include or exclude certain parameters.

  3. If the statement has parameters, they must be enclosed in parentheses when EXECUTE STATEMENT is called, regardless of whether they come directly as strings, as variable names or as expressions

  4. Each named parameter must be prefixed by a colon (:) in the statement string itself, but not when the parameter is assigned a value

  5. Positional parameters must be assigned their values in the same order as they appear in the query text

  6. The assignment operator for parameters is the special operator :=, similar to the assignment operator in Pascal

  7. Each named parameter can be used in the statement more than once, but its value must be assigned only once

  8. With positional parameters, the number of assigned values must match the number of parameter placeholders (question marks) in the statement exactly

  9. A named parameter in the statement text can only be a regular identifier (it cannot be a quoted identifier)

7.7.15.1.2 Examples of EXECUTE STATEMENT with parameters
  1. With named parameters:

    1. ...
    2. DECLARE license_num VARCHAR(15);
    3. DECLARE connect_string VARCHAR (100);
    4. DECLARE stmt VARCHAR (100) =
    5. 'SELECT license
    6. FROM cars
    7. WHERE driver = :driver AND location = :loc';
    8. BEGIN
    9. ...
    10. SELECT connstr
    11. FROM databases
    12. WHERE cust_id = :id
    13. INTO connect_string;
    14. ...
    15. FOR
    16. SELECT id
    17. FROM drivers
    18. INTO current_driver
    19. DO
    20. BEGIN
    21. FOR
    22. SELECT location
    23. FROM driver_locations
    24. WHERE driver_id = :current_driver
    25. INTO current_location
    26. DO
    27. BEGIN
    28. ...
    29. EXECUTE STATEMENT (stmt)
    30. (driver := current_driver,
    31. loc := current_location)
    32. ON EXTERNAL connect_string
    33. INTO license_num;
    34. ...
  2. The same code with positional parameters:

    1. DECLARE license_num VARCHAR (15);
    2. DECLARE connect_string VARCHAR (100);
    3. DECLARE stmt VARCHAR (100) =
    4. 'SELECT license
    5. FROM cars
    6. WHERE driver = ? AND location = ?';
    7. BEGIN
    8. ...
    9. SELECT connstr
    10. FROM databases
    11. WHERE cust_id = :id
    12. into connect_string;
    13. ...
    14. FOR
    15. SELECT id
    16. FROM drivers
    17. INTO current_driver
    18. DO
    19. BEGIN
    20. FOR
    21. SELECT location
    22. FROM driver_locations
    23. WHERE driver_id = :current_driver
    24. INTO current_location
    25. DO
    26. BEGIN
    27. ...
    28. EXECUTE STATEMENT (stmt)
    29. (current_driver, current_location)
    30. ON EXTERNAL connect_string
    31. INTO license_num;
    32. ...
  3. Use of EXCESS to allow named parameters to be unused (note: this is a FOR EXECUTE STATEMENT):

  1. CREATE PROCEDURE P_EXCESS (A_ID INT, A_TRAN INT = NULL, A_CONN INT = NULL)
  2. RETURNS (ID INT, TRAN INT, CONN INT)
  3. AS
  4. DECLARE S VARCHAR(255);
  5. DECLARE W VARCHAR(255) = '';
  6. BEGIN
  7. S = 'SELECT * FROM TTT WHERE ID = :ID';
  8. IF (A_TRAN IS NOT NULL)
  9. THEN W = W || ' AND TRAN = :a';
  10. IF (A_CONN IS NOT NULL)
  11. THEN W = W || ' AND CONN = :b';
  12. IF (W <> '')
  13. THEN S = S || W;
  14. -- could raise error if TRAN or CONN is null
  15. -- FOR EXECUTE STATEMENT (:S) (a := :A_TRAN, b := A_CONN, id := A_ID)
  16. -- OK in all cases
  17. FOR EXECUTE STATEMENT (:S) (EXCESS a := :A_TRAN, EXCESS b := A_CONN, id := A_ID)
  18. INTO :ID, :TRAN, :CONN
  19. DO SUSPEND;
  20. END

7.7.15.2 WITH {AUTONOMOUS | COMMON} TRANSACTION

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.

7.7.15.3 WITH CALLER PRIVILEGES

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

7.7.15.4 ON EXTERNAL [DATA SOURCE]

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.

7.7.15.4.1 Connection Pooling
  • External connections made by statements WITH COMMON TRANSACTION (the default) will remain open until the current transaction ends. They can be reused by subsequent calls to EXECUTE STATEMENT, but only if connection_string is exactly the same, including case

  • External connections made by statements WITH AUTONOMOUS TRANSACTION are closed as soon as the statement has been executed

  • Statements using WITH AUTONOMOUS TRANSACTION can and will re-use connections that were opened earlier by statements WITH COMMON TRANSACTION. If this happens, the reused connection will be left open after the statement has been executed. (It must be, because it has at least one active transaction!)

7.7.15.4.2 Transaction Pooling
  • If WITH COMMON TRANSACTION is in effect, transactions will be reused as much as possible. They will be committed or rolled back together with the current transaction

  • If WITH AUTONOMOUS TRANSACTION is specified, a fresh transaction will always be started for the statement. This transaction will be committed or rolled back immediately after the statement’s execution

7.7.15.4.3 Exception Handling

When ON EXTERNAL is used, the extra connection is always made via a so-called external provider, even if the connection is to the current database. One of the consequences is that exceptions cannot be caught in the usual way. Every exception caused by the statement is wrapped in either an eds_connection or an eds_statement error. In order to catch them in your PSQL code, you have to use WHEN GDSCODE eds_connection, WHEN GDSCODE eds_statement or WHEN ANY.

Note

Without ON EXTERNAL, exceptions are caught in the usual way, even if an extra connection is made to the current database.

7.7.15.4.4 Miscellaneous Notes
  • The character set used for the external connection is the same as that for the current connection

  • Two-phase commits are not supported

7.7.15.5 AS USER, PASSWORD and ROLE

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

Note

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.

7.7.15.6 Caveats with EXECUTE STATEMENT

  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 executed 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.7.17, FOR EXECUTE STATEMENT

7.7.16 FOR SELECT

Used forLooping row-by-row through a selected result set

Available inPSQL

Syntax

  1. [label:]
  2. FOR <select_stmt> [AS CURSOR cursor_name]
  3. DO <compound_statement>

Table 7.7.16.1 FOR SELECT Statement Parameters

ArgumentDescription

label

Optional label for LEAVE and CONTINUE. Follows the rules for identifiers.

select_stmt

SELECT statement

cursor_name

Cursor name. It must be unique among cursor names in the PSQL module (stored procedure, stored function, trigger or PSQL block)

compound_statement

A single statement, or a block of statements wrapped in BEGIN…​END, that performs all the processing for this FOR loop

The FOR SELECT statement

  • retrieves each row sequentially from the result set, and executes the statement or block of statements for each row. In each iteration of the loop, the field values of the current row are copied into pre-declared variables.

    Including the AS CURSOR clause enables positioned deletes and updates to be performed — see notes below

  • can embed other FOR SELECT statements

  • can contain named parameters that must be previously declared in the DECLARE VARIABLE statement or exist as input or output parameters of the procedure

  • requires an INTO clause at the end of the SELECT …​ FROM …​ specification. In each iteration of the loop, the field values of the current row are copied to the list of variables specified in the INTO clause. The loop repeats until all rows are retrieved, after which it terminates

  • can be terminated before all rows are retrieved by using a BREAK, LEAVE or EXIT statement

7.7.16.1 The Undeclared Cursor

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

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

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

7.7.16.2 Examples using FOR SELECT

  1. A simple loop through query results:

    1. CREATE PROCEDURE SHOWNUMS
    2. RETURNS (
    3. AA INTEGER,
    4. BB INTEGER,
    5. SM INTEGER,
    6. DF INTEGER)
    7. AS
    8. BEGIN
    9. FOR SELECT DISTINCT A, B
    10. FROM NUMBERS
    11. ORDER BY A, B
    12. INTO AA, BB
    13. DO
    14. BEGIN
    15. SM = AA + BB;
    16. DF = AA - BB;
    17. SUSPEND;
    18. END
    19. END
  2. Nested FOR SELECT loop:

    1. CREATE PROCEDURE RELFIELDS
    2. RETURNS (
    3. RELATION CHAR(32),
    4. POS INTEGER,
    5. FIELD CHAR(32))
    6. AS
    7. BEGIN
    8. FOR SELECT RDB$RELATION_NAME
    9. FROM RDB$RELATIONS
    10. ORDER BY 1
    11. INTO :RELATION
    12. DO
    13. BEGIN
    14. FOR SELECT
    15. RDB$FIELD_POSITION + 1,
    16. RDB$FIELD_NAME
    17. FROM RDB$RELATION_FIELDS
    18. WHERE
    19. RDB$RELATION_NAME = :RELATION
    20. ORDER BY RDB$FIELD_POSITION
    21. INTO :POS, :FIELD
    22. DO
    23. BEGIN
    24. IF (POS = 2) THEN
    25. RELATION = ' "';
    26. SUSPEND;
    27. END
    28. END
    29. END
  3. Using the AS CURSOR clause to surface a cursor for the positioned delete of a record:

    1. CREATE PROCEDURE DELTOWN (
    2. TOWNTODELETE VARCHAR(24))
    3. RETURNS (
    4. TOWN VARCHAR(24),
    5. POP INTEGER)
    6. AS
    7. BEGIN
    8. FOR SELECT TOWN, POP
    9. FROM TOWNS
    10. INTO :TOWN, :POP AS CURSOR TCUR
    11. DO
    12. BEGIN
    13. IF (:TOWN = :TOWNTODELETE) THEN
    14. -- Positional delete
    15. DELETE FROM TOWNS
    16. WHERE CURRENT OF TCUR;
    17. ELSE
    18. SUSPEND;
    19. END
    20. END
  4. Using an implicitly declared cursor as a cursor variable

    1. EXECUTE BLOCK
    2. RETURNS (o CHAR(63))
    3. AS
    4. BEGIN
    5. FOR SELECT rdb$relation_name AS name
    6. FROM rdb$relations AS CURSOR c
    7. DO
    8. BEGIN
    9. o = c.name;
    10. SUSPEND;
    11. END
    12. END
  5. Disambiguating cursor variables within queries

    1. EXECUTE BLOCK
    2. RETURNS (o1 CHAR(63), o2 CHAR(63))
    3. AS
    4. BEGIN
    5. FOR SELECT rdb$relation_name
    6. FROM rdb$relations
    7. WHERE
    8. rdb$relation_name = 'RDB$RELATIONS' AS CURSOR c
    9. DO
    10. BEGIN
    11. FOR SELECT
    12. -- with a prefix resolves as a cursor
    13. :c.rdb$relation_name x1,
    14. -- no prefix as an alias for the rdb$relations table
    15. c.rdb$relation_name x2
    16. FROM rdb$relations c
    17. WHERE
    18. rdb$relation_name = 'RDB$DATABASE' AS CURSOR d
    19. DO
    20. BEGIN
    21. o1 = d.x1;
    22. o2 = d.x2;
    23. SUSPEND;
    24. END
    25. END
    26. 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

7.7.17 FOR EXECUTE STATEMENT

Used forExecuting dynamically created SQL statements that return a row set

Available inPSQL

Syntax

  1. [label:]
  2. FOR <execute_statement> DO <compound_statement>

Table 7.7.17.1 FOR EXECUTE STATEMENT Statement Parameters

ArgumentDescription

label

Optional label for LEAVE and CONTINUE. Follows the rules for identifiers.

execute_stmt

An EXECUTE STATEMENT statement

compound_statement

A single statement, or a block of statements wrapped in BEGIN…​END, that performs all the processing for this FOR loop

The statement FOR EXECUTE STATEMENT is used, in a manner analogous to FOR SELECT, to loop through the result set of a dynamically executed query that returns multiple rows.

7.7.17.1 FOR EXECUTE STATEMENT Examples

Executing a dynamically constructed SELECT query that returns a data set

  1. CREATE PROCEDURE DynamicSampleThree (
  2. Q_FIELD_NAME VARCHAR(100),
  3. Q_TABLE_NAME VARCHAR(100)
  4. ) RETURNS(
  5. LINE VARCHAR(32000)
  6. )
  7. AS
  8. DECLARE VARIABLE P_ONE_LINE VARCHAR(100);
  9. BEGIN
  10. LINE = '';
  11. FOR
  12. EXECUTE STATEMENT
  13. 'SELECT T1.' || :Q_FIELD_NAME ||
  14. ' FROM ' || :Q_TABLE_NAME || ' T1 '
  15. INTO :P_ONE_LINE
  16. DO
  17. IF (:P_ONE_LINE IS NOT NULL) THEN
  18. LINE = :LINE || :P_ONE_LINE || ' ';
  19. SUSPEND;
  20. END

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

7.7.18 OPEN

Used forOpening a declared cursor

Available inPSQL

Syntax

  1. OPEN cursor_name;

Table 7.7.18.1 OPEN Statement Parameter

ArgumentDescription

cursor_name

Cursor name. A cursor with this name must be previously declared with a DECLARE CURSOR statement

An OPEN statement opens a previously declared cursor, executes its declared SELECT statement, and makes the first record of the result data set ready to fetch. OPEN can be applied only to cursors previously declared in a Section 7.7.4, DECLARE .. CURSOR statement.

Note

If the SELECT statement of the cursor has parameters, they must be declared as local variables or exist as input or output parameters before the cursor is declared. When the cursor is opened, the parameter is assigned the current value of the variable.

7.7.18.1 OPEN Examples

  1. Using the OPEN statement:

    1. SET TERM ^;
    2. CREATE OR ALTER PROCEDURE GET_RELATIONS_NAMES
    3. RETURNS (
    4. RNAME CHAR(63)
    5. )
    6. AS
    7. DECLARE C CURSOR FOR (
    8. SELECT RDB$RELATION_NAME
    9. FROM RDB$RELATIONS);
    10. BEGIN
    11. OPEN C;
    12. WHILE (1 = 1) DO
    13. BEGIN
    14. FETCH C INTO :RNAME;
    15. IF (ROW_COUNT = 0) THEN
    16. LEAVE;
    17. SUSPEND;
    18. END
    19. CLOSE C;
    20. END^
    21. SET TERM ;^
  2. A collection of scripts for creating views using a PSQL block with named cursors:

    1. EXECUTE BLOCK
    2. RETURNS (
    3. SCRIPT BLOB SUB_TYPE TEXT)
    4. AS
    5. DECLARE VARIABLE FIELDS VARCHAR(8191);
    6. DECLARE VARIABLE FIELD_NAME TYPE OF RDB$FIELD_NAME;
    7. DECLARE VARIABLE RELATION RDB$RELATION_NAME;
    8. DECLARE VARIABLE SOURCE TYPE OF COLUMN RDB$RELATIONS.RDB$VIEW_SOURCE;
    9. -- named cursor
    10. DECLARE VARIABLE CUR_R CURSOR FOR (
    11. SELECT
    12. RDB$RELATION_NAME,
    13. RDB$VIEW_SOURCE
    14. FROM
    15. RDB$RELATIONS
    16. WHERE
    17. RDB$VIEW_SOURCE IS NOT NULL);
    18. -- named cursor with local variable
    19. DECLARE CUR_F CURSOR FOR (
    20. SELECT
    21. RDB$FIELD_NAME
    22. FROM
    23. RDB$RELATION_FIELDS
    24. WHERE
    25. -- Important! The variable has to be declared earlier
    26. RDB$RELATION_NAME = :RELATION);
    27. BEGIN
    28. OPEN CUR_R;
    29. WHILE (1 = 1) DO
    30. BEGIN
    31. FETCH CUR_R
    32. INTO :RELATION, :SOURCE;
    33. IF (ROW_COUNT = 0) THEN
    34. LEAVE;
    35. FIELDS = NULL;
    36. -- The CUR_F cursor will use
    37. -- variable value of RELATION initialized above
    38. OPEN CUR_F;
    39. WHILE (1 = 1) DO
    40. BEGIN
    41. FETCH CUR_F
    42. INTO :FIELD_NAME;
    43. IF (ROW_COUNT = 0) THEN
    44. LEAVE;
    45. IF (FIELDS IS NULL) THEN
    46. FIELDS = TRIM(FIELD_NAME);
    47. ELSE
    48. FIELDS = FIELDS || ', ' || TRIM(FIELD_NAME);
    49. END
    50. CLOSE CUR_F;
    51. SCRIPT = 'CREATE VIEW ' || RELATION;
    52. IF (FIELDS IS NOT NULL) THEN
    53. SCRIPT = SCRIPT || ' (' || FIELDS || ')';
    54. SCRIPT = SCRIPT || ' AS ' || ASCII_CHAR(13);
    55. SCRIPT = SCRIPT || SOURCE;
    56. SUSPEND;
    57. END
    58. CLOSE CUR_R;
    59. END

See alsoSection 7.7.4, DECLARE .. CURSOR, Section 7.7.19, FETCH, Section 7.7.20, CLOSE

7.7.19 FETCH

Used forFetching successive records from a data set retrieved by a cursor

Available inPSQL

Syntax

  1. FETCH [<fetch_scroll> FROM] cursor_name
  2. [INTO [:]varname [, [:]varname ...]];
  3. <fetch_scroll> ::=
  4. NEXT | PRIOR | FIRST | LAST
  5. | RELATIVE n
  6. | ABSOLUTE n

Table 7.7.19.1 FETCH Statement Parameters

ArgumentDescription

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 first and successive rows 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 Section 7.7.4, 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 clause can be used for scrollable and forward-only cursors. Other clauses are only supported for scrollable cursors.

The Scroll 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 fetch moved 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 row 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).

7.7.19.1 FETCH Examples

  1. Using the FETCH statement:

    1. CREATE OR ALTER PROCEDURE GET_RELATIONS_NAMES
    2. RETURNS (RNAME CHAR(63))
    3. AS
    4. DECLARE C CURSOR FOR (
    5. SELECT RDB$RELATION_NAME
    6. FROM RDB$RELATIONS);
    7. BEGIN
    8. OPEN C;
    9. WHILE (1 = 1) DO
    10. BEGIN
    11. FETCH C INTO RNAME;
    12. IF (ROW_COUNT = 0) THEN
    13. LEAVE;
    14. SUSPEND;
    15. END
    16. CLOSE C;
    17. END
  2. Using the FETCH statement with nested cursors:

    1. EXECUTE BLOCK
    2. RETURNS (SCRIPT BLOB SUB_TYPE TEXT)
    3. AS
    4. DECLARE VARIABLE FIELDS VARCHAR (8191);
    5. DECLARE VARIABLE FIELD_NAME TYPE OF RDB$FIELD_NAME;
    6. DECLARE VARIABLE RELATION RDB$RELATION_NAME;
    7. DECLARE VARIABLE SRC TYPE OF COLUMN RDB$RELATIONS.RDB$VIEW_SOURCE;
    8. -- Named cursor declaration
    9. DECLARE VARIABLE CUR_R CURSOR FOR (
    10. SELECT
    11. RDB$RELATION_NAME,
    12. RDB$VIEW_SOURCE
    13. FROM RDB$RELATIONS
    14. WHERE RDB$VIEW_SOURCE IS NOT NULL);
    15. -- Declaring a named cursor in which
    16. -- a local variable is used
    17. DECLARE CUR_F CURSOR FOR (
    18. SELECT RDB$FIELD_NAME
    19. FROM RDB$RELATION_FIELDS
    20. WHERE
    21. -- It is important that the variable must be declared earlier
    22. RDB$RELATION_NAME =: RELATION);
    23. BEGIN
    24. OPEN CUR_R;
    25. WHILE (1 = 1) DO
    26. BEGIN
    27. FETCH CUR_R INTO RELATION, SRC;
    28. IF (ROW_COUNT = 0) THEN
    29. LEAVE;
    30. FIELDS = NULL;
    31. -- Cursor CUR_F will use the value
    32. -- the RELATION variable initialized above
    33. OPEN CUR_F;
    34. WHILE (1 = 1) DO
    35. BEGIN
    36. FETCH CUR_F INTO FIELD_NAME;
    37. IF (ROW_COUNT = 0) THEN
    38. LEAVE;
    39. IF (FIELDS IS NULL) THEN
    40. FIELDS = TRIM (FIELD_NAME);
    41. ELSE
    42. FIELDS = FIELDS || ',' || TRIM(FIELD_NAME);
    43. END
    44. CLOSE CUR_F;
    45. SCRIPT = 'CREATE VIEW' || RELATION;
    46. IF (FIELDS IS NOT NULL) THEN
    47. SCRIPT = SCRIPT || '(' || FIELDS || ')' ;
    48. SCRIPT = SCRIPT || 'AS' || ASCII_CHAR (13);
    49. SCRIPT = SCRIPT || SRC;
    50. SUSPEND;
    51. END
    52. CLOSE CUR_R;
    53. EN
  3. An example of using the FETCH statement with a scrollable cursor

  1. EXECUTE BLOCK
  2. RETURNS (N INT, RNAME CHAR (63))
  3. AS
  4. DECLARE C SCROLL CURSOR FOR (
  5. SELECT
  6. ROW_NUMBER() OVER (ORDER BY RDB$RELATION_NAME) AS N,
  7. RDB$RELATION_NAME
  8. FROM RDB$RELATIONS
  9. ORDER BY RDB$RELATION_NAME);
  10. BEGIN
  11. OPEN C;
  12. -- move to the first record (N = 1)
  13. FETCH FIRST FROM C;
  14. RNAME = C.RDB$RELATION_NAME;
  15. N = C.N;
  16. SUSPEND;
  17. -- move 1 record forward (N = 2)
  18. FETCH NEXT FROM C;
  19. RNAME = C.RDB$RELATION_NAME;
  20. N = C.N;
  21. SUSPEND;
  22. -- move to the fifth record (N = 5)
  23. FETCH ABSOLUTE 5 FROM C;
  24. RNAME = C.RDB$RELATION_NAME;
  25. N = C.N;
  26. SUSPEND;
  27. -- move 1 record backward (N = 4)
  28. FETCH PRIOR FROM C;
  29. RNAME = C.RDB$RELATION_NAME;
  30. N = C.N;
  31. SUSPEND;
  32. -- move 3 records forward (N = 7)
  33. FETCH RELATIVE 3 FROM C;
  34. RNAME = C.RDB$RELATION_NAME;
  35. N = C.N;
  36. SUSPEND;
  37. -- move back 5 records (N = 2)
  38. FETCH RELATIVE -5 FROM C;
  39. RNAME = C.RDB$RELATION_NAME;
  40. N = C.N;
  41. SUSPEND;
  42. -- move to the first record (N = 1)
  43. FETCH FIRST FROM C;
  44. RNAME = C.RDB$RELATION_NAME;
  45. N = C.N;
  46. SUSPEND;
  47. -- move to the last entry
  48. FETCH LAST FROM C;
  49. RNAME = C.RDB$RELATION_NAME;
  50. N = C.N;
  51. SUSPEND;
  52. CLOSE C;
  53. END

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

7.7.20 CLOSE

Used forClosing a declared cursor

Available inPSQL

Syntax

  1. CLOSE cursor_name;

Table 7.7.20.1 CLOSE Statement Parameter

ArgumentDescription

cursor_name

Cursor name. A cursor with this name must be previously declared with a DECLARE …​ CURSOR statement and opened by an OPEN statement

A CLOSE statement closes an open cursor. Any cursors that are still open will be automatically closed after the module code completes execution. Only a cursor that was declared with Section 7.7.4, DECLARE .. CURSOR can be closed with a CLOSE statement.

7.7.20.1 CLOSE Examples

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

7.7.21 IN AUTONOMOUS TRANSACTION

Used forExecuting a statement or a block of statements in an autonomous transaction

Available inPSQL

Syntax

  1. IN AUTONOMOUS TRANSACTION DO <compound_statement>

Table 7.7.21.1 IN AUTONOMOUS TRANSACTION Statement Parameter

ArgumentDescription

compound_statement

A single statement, or a block of statements

The IN AUTONOMOUS TRANSACTION statement enables execution of a statement or a block of statements in an autonomous transaction. Code running in an autonomous transaction will be committed right after its successful execution, regardless of the status of its parent transaction. This can be used when certain operations must not be rolled back, even if an error occurs in the parent transaction.

An autonomous transaction has the same isolation level as its parent transaction. Any exception that is thrown in the block of the autonomous transaction code will result in the autonomous transaction being rolled back and all changes made will be undone. If the code executes successfully, the autonomous transaction will be committed.

7.7.21.1 IN AUTONOMOUS TRANSACTION Examples

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

  1. CREATE TRIGGER TR_CONNECT ON CONNECT
  2. AS
  3. BEGIN
  4. -- Logging all attempts to connect to the database
  5. IN AUTONOMOUS TRANSACTION DO
  6. INSERT INTO LOG(MSG)
  7. VALUES ('USER ' || CURRENT_USER || ' CONNECTS.');
  8. IF (EXISTS(SELECT *
  9. FROM BLOCKED_USERS
  10. WHERE USERNAME = CURRENT_USER)) THEN
  11. BEGIN
  12. -- Logging that the attempt to connect
  13. -- to the database failed and sending
  14. -- a message about the event
  15. IN AUTONOMOUS TRANSACTION DO
  16. BEGIN
  17. INSERT INTO LOG(MSG)
  18. VALUES ('USER ' || CURRENT_USER || ' REFUSED.');
  19. POST_EVENT 'CONNECTION ATTEMPT BY BLOCKED USER!';
  20. END
  21. -- now calling an exception
  22. EXCEPTION EX_BADUSER;
  23. END
  24. END

See alsoTransaction Control

7.7.22 POST_EVENT

Used forNotifying listening clients about database events in a module

Available inPSQL

Syntax

  1. POST_EVENT event_name;

Table 7.7.22.1 POST_EVENT Statement Parameter

ArgumentDescription

event_name

Event name (message) limited to 127 bytes

The POST_EVENT statement notifies the event manager about the event, which saves it to an event table. When the transaction is committed, the event manager notifies applications that are signalling their interest in the event.

The event name can be some sort of code, or a short message: the choice is open as it is just a string up to 127 bytes.

The content of the string can be a string literal, a variable or any valid SQL expression that resolves to a string.

7.7.22.1 POST_EVENT Examples

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

  1. CREATE TRIGGER POST_NEW_ORDER FOR SALES
  2. ACTIVE AFTER INSERT POSITION 0
  3. AS
  4. BEGIN
  5. POST_EVENT 'new_order';
  6. END

7.7.23 RETURN

Used forReturn a value from a stored function

Available inPSQL

Syntax

  1. RETURN value;

Table 7.7.23.1 RETURN Statement Parameter

ArgumentDescription

value

Expression with the value to return; Can be any expression type-compatible with the return type of the function

The RETURN statement ends the execution of a function and returns the value of the expression value.

RETURN can only be used in PSQL functions (stored and local functions).

7.7.23.1 RETURN Examples

See CREATE FUNCTION Examples