EXECUTE BLOCK

CommentsProcedural SQL (PSQL) Statements

`EXECUTE BLOCK`

Creates an “anonymous” block of PSQL code in DSQL for immediate execution

Available in

DSQL

Syntax

EXECUTE BLOCK [(<inparams>)]
  [RETURNS (<outparams>)]
  <psql-module-body>

<inparams> ::=  <param_decl> = ? [, <inparams> ]

<outparams> ::=  <param_decl> [, <outparams>]

<param_decl> ::=
  paramname <domain_or_non_array_type> [NOT NULL] [COLLATE collation]

<domain_or_non_array_type> ::=
  !! See Scalar Data Types Syntax !!

<psql-module-body> ::=
  !! See Syntax of a Module Body !!

Table 1. Arguments for the `EXECUTE BLOCK` Statement Parameters
Argument	Description
param_decl	Name and description of an input or output parameter
paramname	The name of an input or output parameter of the procedural block, up to 63 characters long.The name must be unique among input and output parameters and local variables in the block
collation	Collation

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.

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.