DECLARE

DECLARE marks the declarative part of a scripting block and declares one or more variables, RESULTSETs, or exceptions for that block.

See also BEGIN ... END and EXCEPTION as other sections of a script block.

Syntax

DECLARE
  {variable | resultset | exception};
  [{variable | resultset | exception}; ...]
[BEGIN ...]
  ... statements;
[EXCEPTION ...]
[END;]

The DECLARE and EXCEPTION sections are optional within a block.

Syntax: Variable Declaration

DECLARE
    var_name [type] [ DEFAULT {expr | := expr}];

Arguments

var_name: Variable name.

---

type (optional): SQL data type of the variable.

---

default expr or default := expr (optional): Keyword for default value, which can be specified using expr. expr and type must be compatible data types.

Syntax: RESULTSET Declaration

DECLARE
  resultset_name RESULTSET [ { DEFAULT | := } (query) ];

Arguments

resultset_name: Resultset name. Must be unique within the current scope.

---

DEFAULT (query) or := (query) (optional): Keyword for default value, which can be specified using query.

Syntax: Exception Declaration

DECLARE
  exception_name EXCEPTION [ ( exception_number , 'exception_message' ) ] ;

Arguments

exception_name: Name of the exception. Must be unique within the current scope.

---

exception_number (optional): Unique identifier number for the exception, as an integer between -20,000 and -20,999. Should not be used for multiple exceptions in use at the same time. Default: -20,000

---

exception_message (optional): A user-defined message to describe the exception. It must not contain double quotes. Default is an empty string.

Usage Notes

• All database objects (e.g., tables) created in a block between BEGIN and END are also available outside the block.
• Variables declared in the DECLARE section are only available inside the block.
• To declare a variable in the section between BEGIN and END, the LET statement can be used.

Example 1 | Variable Declaration

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

+------+-------+---------------+
| my1  | my3   | my2           |
+------+-------+---------------+
| Meta | Kraft | MetaKraftWerk |
+------+-------+---------------+

Example 2 | EXCEPTION Declaration

DECLARE
  count INTEGER;
  my_error EXCEPTION (-20001, 'I have triggered this exception at your request.');
BEGIN
  LET count INTEGER := 0;
  IF (TRUE) THEN
    RAISE my_error;
  END IF;
  count := count + 1;
  RETURN count;
END;

I have triggered this exception at your request.
 at mkw_doku.test_abfragen (1:1)