Available in: DSQL
Added in: 2.0
Changed in: 2.1
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 <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
set term #; execute block (...) as begin statement1; statement2; end # set term ;#
In Firebird's isql client you must set the terminator to something other than “;” before you type in the EXECUTE BLOCK statement. Otherwise isql, being line-oriented, will try to execute the part you have entered as soon as it encounters the first semicolon.
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
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
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.
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