6.8. EXECUTE BLOCK

6.8. `EXECUTE BLOCK`

Used forCreating an anonymous block of PSQL code in DSQL for immediate execution

Available inDSQL

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> ::=  <datatype> | [TYPE OF] domain | TYPE OF COLUMN rel.col
   | 
   |<datatype> ::=
   |    {SMALLINT | INTEGER | BIGINT}
   |  | {FLOAT | DOUBLE PRECISION}
   |  | {DATE | TIME | TIMESTAMP}
   |  | {DECIMAL | NUMERIC} [(precision [, scale])]
   |  | {CHAR | CHARACTER} [VARYING] | VARCHAR} [(size)]
   |    [CHARACTER SET charset]
   |  | {NCHAR | NATIONAL {CHARACTER | CHAR}} [VARYING] [(size)]
   |  | BLOB [SUB_TYPE {subtype_num | subtype_name}]
   |    [SEGMENT SIZE seglen] [CHARACTER SET charset]
   |  | BLOB [(seglen [, subtype_num])]
   | 
   |<declarations> ::= <declare_item> [<declare_item> ...]
   |<declare_item> ::= <declare_var>; | <declare_cursor>;

Table 6.19. Arguments for the EXECUTE BLOCK Statement Parameters

Argument	Description
param_decl	Name and description of an input or output parameter
declarations	A section for declaring local variables and named cursors
declare_var	Local variable declaration
declare_cursor	Declaration of a named cursor
paramname	The name of an input or output parameter of the procedural block, up to 31 characters long. The name must be unique among input and output parameters and local variables in the block
datatype	SQL data type
collation	Collation sequence
domain	Domain
rel	Name of a table or view
col	Name of a column in a table or view
precision	Precision. From 1 to 18
scale	Scale. From 0 to 18. It must be less than or equal to precision
size	The maximum size of a string, in characters
charset	Character set
subtype_num	`BLOB` subtype number
subtype_name	`BLOB` subtype mnemonic name
seglen	Segment size, it cannot be greater than 65,535

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

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

6.8.1. Input and output parameters

Executing a block without input parameters should be possible with every Firebird client that allows the user to enter his or her own DSQL statements. If there are input parameters, things get trickier: these parameters must get their values after the statement is prepared but before it is executed. This requires special provisions, which not every client application offers. (Firebird’s own isql, for one, doesn’t.)

The server only accepts question marks (?) as placeholders for the input values, not :a, :MyParam etc., or literal values. Client software may support the :xxx form though, and will preprocess it before sending it to the server.

If the block has output parameters, you must use SUSPEND or nothing will be returned.

Output is always returned in the form of a result set, just as with a SELECT statement. You can’t use RETURNING_VALUES or execute the block INTO some variables, even if there is only one result row.

6.8.2. Statement Terminators

Some SQL statement editors — specifically the isql utility that comes with Firebird and possibly some third-party editors — employ an internal convention that requires all statements to be terminated with a semi-colon. This creates a conflict with PSQL syntax when coding in these environments. If you are unacquainted with this problem and its solution, please study the details in the PSQL chapter in the section entitled Switching the Terminator in isql.

Prev	Up	Next
6.7. `EXECUTE PROCEDURE`	Home	Chapter 7. Procedural SQL (PSQL) Statements