Firebird Documentation IndexFirebird 2.5 Language Ref. UpdateDML statements → EXECUTE BLOCK
Firebird Home Firebird Home Prev: DELETEFirebird Documentation IndexUp: DML statementsNext: EXECUTE PROCEDURE

EXECUTE BLOCK

COLLATE in variable and parameter declarations
NOT NULL in variable and parameter declarations
Domains instead of datatypes
TYPE OF COLUMN in parameter and variable declarations

Available in: DSQL

Added in: 2.0

Changed in: 2.1, 2.5

Description: Executes a block of PSQL code as if it were a stored procedure, optionally with input and output parameters and variable declarations. This allows the user to perform “on-the-fly” PSQL within a DSQL context.

Syntax: 

EXECUTE BLOCK [(<inparams>)]
     [RETURNS (<outparams>)]
AS
   [<declarations>]
BEGIN
   [<PSQL statements>]
END

<inparams>      ::=  <param_decl> = ? [, <inparams> ]
<outparams>     ::=  <param_decl>     [, <outparams>]
<param_decl>    ::=  paramname <type> [NOT NULL] [COLLATE collation]
<type>          ::=  sql_datatype | [TYPE OF] domain | TYPE OF COLUMN rel.col
<declarations>  ::=  See PSQL::DECLARE for the exact syntax

Examples: 

This example injects the numbers 0 through 127 and their corresponding ASCII characters into the table ASCIITABLE:

execute block
as
declare i int = 0;
begin
  while (i < 128) do
  begin
    insert into AsciiTable values (:i, ascii_char(:i));
    i = i + 1;
  end
end

The next example calculates the geometric mean of two numbers and returns it to the user:

execute block (x double precision = ?, y double precision = ?)
returns (gmean double precision)
as
begin
  gmean = sqrt(x*y);
  suspend;
end

Because this block has input parameters, it has to be prepared first. Then the parameters can be set and the block executed. It depends on the client software how this must be done and even if it is possible at all – see the notes below.

Our last example takes two integer values, smallest and largest. For all the numbers in the range smallest .. largest, the block outputs the number itself, its square, its cube and its fourth power.

execute block (smallest int = ?, largest int = ?)
returns (number int, square bigint, cube bigint, fourth bigint)
as
begin
  number = smallest;
  while (number <= largest) do
  begin
    square = number * number;
    cube   = number * square;
    fourth = number * cube;
    suspend;
    number = number + 1;
  end
end

Again, it depends on the client software if and how you can set the parameter values.

Notes: 

COLLATE in variable and parameter declarations

Changed in: 2.1

Description: Firebird 2.1 and up allow COLLATE clauses in declarations of input/output parameters and local variables.

Example: 

execute block
  (es_1 varchar(20) character set iso8859_1 collate es_es = ?)
returns
  (nl_1 varchar(20) character set iso8859_1 collate du_nl)
as
  declare s_temp varchar(100) character set utf8 collate unicode;
begin
  ...
  ...
end

NOT NULL in variable and parameter declarations

Changed in: 2.1

Description: Firebird 2.1 and up allow NOT NULL constraints in declarations of input/output parameters and local variables.

Example: 

execute block (a int not null = ?, b int not null = ?)
returns (product bigint not null, message varchar(20) not null)
as
  declare useless_dummy timestamp not null;
begin
  product = a*b;
  if (product < 0) then message = 'This is below zero.';
  else if (product > 0) then message = 'This is above zero.';
  else message = 'This must be zero.';
  suspend;
end

Domains instead of datatypes

Changed in: 2.1

Description: Firebird 2.1 and up allow the use of domains instead of SQL datatypes when declaring input/output parameters and local variables. With the “TYPE OF” modifier only the domain's type is used, not its NOT NULL setting, CHECK constraint and/or default value. If the domain is of a text type, its character set and collation are always included.

Example: 

execute block (a my_domain = ?, b type of my_other_domain = ?)
returns (p my_third_domain)
as
  declare s_temp type of my_third_domain;
begin
  ...
  ...
end

Warning

For input parameters, the collation that comes with the domain is not taken into consideration when comparisons (e.g. equality tests) are made. This is caused by a bug that has been fixed for Firebird 3.

TYPE OF COLUMN in parameter and variable declarations

Added in: 2.5

Description: Analogous to the “TYPE OF domain” syntax supported since version 2.1, it is now also possible to declare variables and parameters as having the type of an existing table or view column. Only the type itself is used; in the case of string types, this includes the character set and the collation. Constraints and default values are never copied from the source column.

Example: 

create table numbers (
  bignum   numeric(18),
  smallnum numeric(9)
)

execute block (dividend  type of column numbers.bignum = ?,
               divisor   type of column numbers.smallnum = ?)
      returns (quotient  type of column numbers.bignum,
               remainder type of column numbers.smallnum)
as
begin
  quotient  = dividend / divisor;
  remainder = mod (dividend, divisor);
  suspend;
end

Warning

For input parameters, the collation that comes with the column's type is not taken into consideration when comparisons (e.g. equality tests) are made. For local variables, the behaviour varies. This is caused by a bug that has been fixed for Firebird 3.

Prev: DELETEFirebird Documentation IndexUp: DML statementsNext: EXECUTE PROCEDURE
Firebird Documentation IndexFirebird 2.5 Language Ref. UpdateDML statements → EXECUTE BLOCK