Blocks

Procedural code is written in a scripting block. This section describes the basics of the scripting blocks.

Structure of a block

DECLARE         -- (declaration of variables, etc.) ...

BEGIN           -- (SQL and scripting statements) ...
    EXCEPTION   -- (exceptions handling statements) ...
END;

Sections

DECLARE (optional): Declarations of variables, RESULTSETs or exceptions that can be used in this block can be declared either in the DECLARE section of the block or in the BEGIN ... END section of the block. This section is therefore optional.

You can declare: Variables, RESULTSETs and Exceptions.

BEGIN... ... END: SQL and scripting statements are written between BEGIN and END.

EXCEPTION (optional): Section for optional exception handling code for this Block.

Simple Block Example

A block only requires the keywords BEGIN and END, e.g.:

BEGIN
  CREATE TABLE my_table_1  (num_id INTEGER, ...);
  CREATE VIEW v_my_table_1 as select * from my_table_1;
END;

is an example of a valid block.

Any database objects created in a block (e.g. the tables or the views in our example above) can be used outside of the block too.

Variable Declaration

One way to declare a variable for use in the block is to do so in the DECLARE section of the block.

LET allows the declaration of a variable in the BEGIN … END section of the block. see "Declaring a variable" in Variables.

DECLARE
    my1 STRING;
    my2 STRING;
    my3 STRING DEFAULT 'Kraft';
BEGIN
    my1 := 'Meta';
    my2 := my1 || my3 || 'Werk';
    LET myRet := '-' || my2 || '-';
    RETURN myRet;
END;

+-----------------+
| anonymous block |
+-----------------+
| -MetaKraftWerk- |
+-----------------+

See Returning a value for more details on how to return values ​​from a block.

The variables declared in this way cannot be used outside of the block. See more about the scope of declarations in Variables.

Block in a stored procedure

A block can be used in the definition of a stored procedure. The following example creates a stored procedure that contains a script block:

CREATE OR REPLACE PROCEDURE talk_to_me()
RETURNS STRING
AS
DECLARE
    my1 STRING;
    my2 STRING;
    my3 STRING DEFAULT 'Kraft';
BEGIN
    my1 := 'Meta';
    my2 := my1 || my3 || 'Werk';
    RETURN my2;
END;

CALL talk_to_me();

+-----------------+
|   TALK_TO_ME    |
+-----------------+
|  MetaKraftWerk  |
+-----------------+

What is anonymous block and how to use it

An anonymous block is a block that is not part of a stored procedure. Such a block can be declared as a separate, standalone SQL statement.

The BEGIN statement that defines the block also executes the block.

The example in the "Variable declaration" Section is an anonymous block too.

DECLARE
    my1 STRING;
    my2 STRING;
    my3 STRING DEFAULT 'Kraft';
BEGIN
    my1 := 'Meta';
    my2 := my1 || my3 || 'Werk';
    RETURN my2;
END;

The column heading in the output is "anonymous block". The heading in the output of the stored procedure contains the name of the stored procedure (see Example in the "Block in a stored procedure" Section).

+-----------------+
| anonymous block |
+-----------------+
|  MetaKraftWerk  |
+-----------------+