Appearance

Guides>Scripting Guide>Variables

Declare and use variables ​

Variables can be used in expressions, scripting statements, and SQL statements.

Declaring ​

In order to use a variable, it must first be declared. During the declaration, the data type of the variable is also determined. The data type can either be specified explicitly or "inferred" from the specified initial value by MKW. MKW then tries to select the appropriate type for the variable.

The variable declaration can be done in one of the following ways:

sql
DECLARE                                     -- within a declare clause
...
var_name var_type;

var_name DEFAULT expr;

var_name var_type DEFAULT expr;
...

OR 

BEGIN                                       -- within a scripting block
... 

LET var_name var_type {DEFAULT | :=} expr;

LET var_name {DEFAULT | :=} expr;
...

END;

Arguments ​

var_name: The name of the variable, which follows the naming rules for objects.

var_type: The data type of the variable, which can be an SQL Data type, RESULTSET or EXCEPTION.

DEFAULT expr: Keyword DEFAULT assigns the default value expr to this variable.

Important

If both data type and expression are specified, they must be compatible with each other.

If this is not the case, you must explicitly convert the expression to the compatible data type.

Example:

sql
DECLARE
  my_rs RESULTSET;                   
  my_var DECIMAL DEFAULT 0; 
BEGIN
    ... 
    LET my_var1 INTEGER := 42;
    ... 
END;

Data types ​

The data type can either be specified explicitly or "inferred" from the specified initial value by MKW. MKW then tries to select the appropriate type for the variable.

It is safest to specify the data type explicitly, even if a default value is specified.

If the data type cannot be detected, an error is raised.

Example:

sql
DECLARE
  i1 INTEGER;
  i2 INTEGER;
  i3 DEFAULT 1.0;                           -- implicit data type declaration
  s1 STRING;
  s2 STRING;
BEGIN
  s1 := '1'; 
  s2 := '"' || 1 || '"' || ' declared as a string'; 
  i1 := s1::INTEGER;                        -- cast explicit
  i2 := i1 + 1; 
  i3 := i2 / 110;                           -- cast implicit
  LET i4 INTEGER := s1::INTEGER + 2;        -- declaration out of DECLARE section
  SELECT :s2 as EXPR_VALUE
  UNION  
  SELECT 'Explicit Cast (:i2): s1::INTEGER + 1=' || :i2 as EXPR_VALUE
  UNION 
  SELECT 'Implicit Cast of s1 (:i4): s1 + 2=' || :i4 as EXPR_VALUE
  UNION 
  SELECT 'Without Cast (:i3):  i2 / 110=' || :i3  as EXPR_VALUE
  ;
END;
txt
+--------------------------------------------------------------------------------+
| EXPR_VALUE                                                                     |
+--------------------------------------------------------------------------------+
| "1" declared as a string                                                       |
| Explicit Cast (:i2): s1::INTEGER + 1=2                                         |
| Implicit Cast of s1 (:i4): s1 + 2=3                                            |
| Without Cast (:i3): DIV0(i2,110)=0                                             |
+--------------------------------------------------------------------------------+

Scope of variable declarations ​

A variable declared in a DECLARE clause of a scripting block can be used in all sub-blocks nested in this block. It does not matter whether it is a value, a resultset or an exception.

If an object (variable) is declared in a block whose name is already used for another object in the outer block, this declaration will apply to the current block and all blocks nested in it. So the current block is first searched for the name of an object that was referenced. Then outer blocks are included in the search step by step until the object with the name being searched for is found.

! An exception declared within a stored procedure can only be thrown by this procedure. Both stored procedures called by this stored procedure and stored procedures calling this procedure cannot throw this exception.

Variable Scope Example ​

In the following example, variables and parameters have the same name but different scopes. The script consists of nested blocks: outer, middle and inner block.

The Variable with the name PARAMETER_VALUE changes its value due to the scope in which it is declared and used.

As you can see, assigning the value to the variable PARAMETER_VALUE in the inner block has no effect on the PARAMETER_VALUE of the middle and outer blocks, because although the variable PARAMETER_VALUE in the inner block has the same name as in the middle and outer blocks, it is a different variable and can only be accessed within the scope of the inner block.

The same situation is with the PARAMETER_VALUE variable regarding middle and outer block.

NUMERATOR variable keeps its value after END-clause of the inner and middle blocks (Column NUM in the MY_RESULTS table below), due to the only declaration in the outer block: It is the same variable at all levels of the script.

sql
CREATE TABLE IF NOT EXISTS my_folder.MY_RESULTS (NUM INTEGER, PARAMETER_VALUE_COL STRING);
DECLARE 
    PARAMETER_VALUE STRING DEFAULT 'OUTER DEFAULT VALUE';
    NUMERATOR INTEGER; 
BEGIN                                                             -- OUTER BLOCK
    NUMERATOR :=1;
    PARAMETER_VALUE := 'DEFINED IN OUTER BLOCK: NUMERATOR = ' || :NUMERATOR::STRING;
  DECLARE
    PARAMETER_VALUE STRING DEFAULT 'MIDDLE DEFAULT VALUE';
  BEGIN    
    NUMERATOR :=:NUMERATOR+1;                                     -- MIDDLE BLOCK 
    PARAMETER_VALUE := 'DEFINED IN MIDDLE BLOCK: NUMERATOR = ' || :NUMERATOR::STRING;
    DECLARE
      PARAMETER_VALUE STRING  DEFAULT 'INNER DEFAULT VALUE';                    
    BEGIN                                                         -- INNER BLOCK 
      NUMERATOR :=:NUMERATOR+1;
      PARAMETER_VALUE := 'DEFINED IN INNER BLOCK: NUMERATOR = ' || :NUMERATOR::STRING;
      -- First, insert inner block PARAMETER_VALUE value. 
      -- The value of NUMERATOR is already = 3. 
      INSERT INTO my_folder.MY_RESULTS (NUM, PARAMETER_VALUE_COL) VALUES (:NUMERATOR,:PARAMETER_VALUE);
    END;
    -- Then insert middle block PARAMETER_VALUE value. 
    -- NUMERATOR is still = 3, since NUMERATOR variable is only declared in outer block.
    -- The PARAMETER_VALUE Variable of the middle Block scope has already NUMERATOR-Value of the middle Block.  
    INSERT INTO my_folder.MY_RESULTS (NUM, PARAMETER_VALUE_COL) VALUES (:NUMERATOR, :PARAMETER_VALUE);  
  END;
  -- Finally outer block PARAMETER_VALUE value.
  -- NUMERATOR is still = 3, since NUMERATOR variable is only declared in outer block.
  -- The PARAMETER_VALUE Variable of the outer Block scope has already NUMERATOR-Value of the outer Block.  
  INSERT INTO my_folder.MY_RESULTS (NUM, PARAMETER_VALUE_COL) VALUES (:NUMERATOR, :PARAMETER_VALUE);
END;
SELECT * FROM my_folder.MY_RESULTS;

Output:

+-----+------------------------------------------+
| NUM | PARAMETER_VALUE_COL                      |
+-----+------------------------------------------+
| 3   | DEFINED IN INNER BLOCK: NUMERATOR = 3    |
| 3   | DEFINED IN MIDDLE BLOCK: NUMERATOR = 2   |
| 3   | DEFINED IN OUTER BLOCK: NUMERATOR = 1    |
+-----+------------------------------------------+

Assigning a value ​

The operator := is used to assign a value to a declared variable:

sql
...
var_name := expr;
...

Arguments ​

var_name: The name of the variable, which follows the naming rules for objects.

expr: evaluated expr value is assigned to the variable. The data type of the resulting value of the evaluated expression must be compatible with the data type of the variable. If this is not the case, this value must be explicitly converted to the data type compatible with the variable.

Built-in SQL functions and user-defined functions can also be used as expressions.

Use SQL built-in Functions ​

sql
DECLARE
  v1 DECIMAL;
  v2 DECIMAL;
BEGIN
  v1 := SIN(45); 
  v2 := v1 / 110; 
  LET v3 INTEGER := v1 / 110; 
  SELECT 'v1 = ' || :v1 as EXPR_VALUE
  UNION 
  SELECT 'v2 = ' || :v2 as EXPR_VALUE 
  UNION
  SELECT 'v3 = ' || :v3 as EXPR_VALUE
  ;
END;
txt
+---------------------+
| EXPR_VALUE          |
+---------------------+
| v1 = 0.8509035245   |
| v2 = 0.007735486586 |
| v3 = 0              |
+---------------------+

Setting a variable to the return value of a stored procedure ​

There is a difference between capturing scalar return values ​​and table returns from a stored procedure.

Scalar return values ​

To access a scalar return value use the INTO :var_name clause with the Procedure CALL statement .

sql
CREATE OR REPLACE PROCEDURE my_folder.return_a_value
  RETURNS INTEGER
AS
BEGIN
  RETURN 3; 
END; 

---------------------- Acces return values of a stored procedure
DECLARE
    return_me INTEGER; 
BEGIN
    CALL my_folder.return_a_value() INTO :return_me; 
    SELECT 'Return value is: ' || :return_me AS MESSAGE; 
END;
txt
+------------------------+
| anonymous block        |
+------------------------+
| Return value is: 3     |
+------------------------+

Using a variable ​

Variables can be used in expressions and with other Scripting language elements (i. e. RETURN).

sql
DECLARE
    temperature INTEGER DEFAULT 45;
BEGIN
    LET for_return := 'Temperature is C ' || temperature::STRING;
    RETURN for_return;
END;
! A variable to be used in the EXCEPTION section of a block must be declared in the DECLARE section or passed as an argument to a stored procedure. Such a variable cannot be declared in the BEGIN ... END section. See also Variables in EXCEPTION section.

Variable binding: variables usage in a SQL statement ​

To access a variable within an SQL query, ":" (colon) is used before the variable name.

For example:

sql
...
    UPDATE CAR_DEALERS_BP SET                                     
           BONUS_POINTS = :new_BONUS_POINTS   
           WHERE DEALER_ID = :cur_DEALER_ID; 
...

To prefix the variable with a colon is not needed in the following cases:

  • When using a variable in an expression

e.g. by building a string containing a SQL statement to execute

sql
 LET my_query := 'SELECT * FROM CAR_DEALERS_BP WHERE id = ' || cur_DEALER_ID;

where cur_DEALER_ID is a variable.

e.g. in RETURN clause

sql
RETURN :for_return;

-- equal to 

RETURN for_return;

where for_return is a variable.

© 2025 MetaKraftwerk GmbH. All Rights Reserved.