What Is Imperative Logic?
Let’s begin by establishing that declarative logic is free from side effects. That means there is no change of status for the underlying objects at either the database or at the application level. In other words, declarative logic works on a read-only basis. By focusing purely on read-only operations, SAP HANA is able to fully optimize the whole processing logic and parallelize as much of the execution as possible.
Writing code using only declarative language limits what you can achieve, and sometimes you need to control the data flow logic using imperative (sometimes called orchestration) language. For these situations, SQLScript supports the use of imperative language.
Imperative language is used to implement data-flow and control-flow logic using constructs such as loops and conditionals. When you include these constructs, the code then executes procedurally and follows the logic of the loop or conditional. You should bear in mind that this reduces the possibilities for SAP HANA to apply optimizations to the code.
Examples of imperative logic:
Scalar variable manipulation
DDL/DML logic
FOR & WHILE loops
Emptiness checks
Branching logic based on some conditions, for example, IF-ELSE
Executed procedurally, but the SQLScript engine will still continue to make all efforts to optimize for parallelization
Note
The imperative logic is usually represented as a single node in the dataflow graph; thus, it is executed sequentially, and so the opportunities for optimizations are limited.
As a caution to the reader, for the next several lessons, we will be discussing syntaxes and structures that are explained in terms of each other. Structure A will be demonstrated within an example of Structure B, which hasn't been presented yet. Structure B will be shown in terms of Structure C, which in turn will be shown in terms of Structures D and E, which will be shown in terms of Structure A. Unfortunately, there is no one base structure that doesn't reference at least one other, so we will have to include warnings that a given item is yet to be discussed. At the conclusion of this unit and the next, you should review both in their entirety to understand everything in context.
Variables
Scalar Variables
- Scalar variables store a typed value under a name.
They may be initialized empty (initial NULL value) or with a declared value:
- DECLARE local_var int;
- DECLARE C int = 5;
- DECLARE a int DEFAULT 0;
Assignment done using "=":
It can be referenced by using ":" as the prefix to the variable name.
local_var = :C + :a + 3 ;
The local scalar and table variables are declared in the functions or the procedures by using the DECLARE keyword. They are optionally initialized in the declaration.
The variables can also be flagged as read-only by using the CONSTANT keyword, and the consequence is that you cannot override the variable anymore. Also, if you use the CONSTANT keyword, it should have a default value and cannot be NULL.
The value in a variable is accessed or retrieved by prefixing its name with a colon (:). Please note that the colon notation will have several different uses in SQLScript, but this is the most common. When you want to assign a value to another variable, use '='. The left side will contain the variable whose value will be updated, as below:
1local_var = :C + :a + 3 ; In this example, the value of variables C and a are retrieved and added together with the constant value 3, then the sum is stored in the variable local_variable. If the syntax example above is the source of the variables, the local_variable is set to a value of 5 + 0 + 3 = 8.
An assignment is possible more than once, and it will overwrite the previous value stored in the scalar variable. At the same time, the SQLScript supports local variable declaration in a nested block (bounded by BEGIN and END). Local variables are only visible in the scope of the code block in which they are declared and defined, meaning the variable value inside the nested block is assigned inside the nested block. For example, if the variable is only declared outside the nested block, but also assigned inside the nested block, the inside assignment will override the value assigned outside of that nested block.
It is also possible to define local variables inside WHILE / FOR / IF-ELSE control structures (to be discussed later).
Table Variables
- Table variables store a set of values in a table-like structure under a single name.
- The table variable will have column name and type declarations, and some number of value sets (rows).
Defined by using the DECLARE keyword.
- May be declared as explicit an column name/type list, as matching the structure of a query or function's output, or based on a table type
As with scalar variables, may be initialized empty or with a dataset declaration:
- DECLARE temp TABLE (n int);
- DECLARE inTab MY_TABLE_TYPE = UNNEST (:arr) as (i);
- DECLARE temp CONSTANT MY_TABLE_TYPE DEFAULT SELECT * FROM TABLE;
- DECLARE outTab MY_TABLE_TYPE;
Assignment done using "=":
It can be referenced by using ":" as the prefix in the variable.
outTab = SELECT * FROM :inTab;
Note
Note
Global Session Variables
If there is a need to pass the value of a variable between procedures and functions that are running in the same session, global session variables can be used in SQLScript. This variable value is only available in that session and not visible from another session. The session variables are of type STRING, and if it is not of type STRING, it will implicitly be converted to type STRING.
- Used to share a scalar value between procedures and functions that are running only in the same session.
- Not visible from another session.
Set in a procedure or a function.
SET 'MY_SES_VAR' = :new_value ;
Retrieve the session variable using SESSION_CONTEXT() function.
var = SESSION_CONTEXT('MY_SES_VAR');
- Default value is NULL.
Can be reset to NULL using UNSET.
UNSET 'MY_VAR’ ;
- Cannot be used in read-only procedures or functions.
The global session variables are set to NULL by default. They can be set and unset (reset to NULL) using the SET and UNSET command inside the procedures or functions.
Working with Arrays
An array is an indexed collection of elements of a single data type. In the following section, we explore the varying ways to define and use arrays in SQLScript. The array can be used to set, return, or remove elements in an array, concatenate two arrays into a single array, turn arrays from multiple arrays into a table using the UNNEST function, and so on.
- Developers are allowed to define and construct arrays within SQLScript.
- An array is an indexed collection of elements of a single data type.
- Operations supported:
Set and access elements by index using <array>[<index>]
Add and remove elements from the ends of an array
Concatenate two arrays using CONCAT or ||
Convert arrays into a table using UNNEST()
Convert a table column into an array using ARRAY_AGG()
Determine the length of the array using CARDINALITY()
The following examples will show imperative logic in the context of procedures, the syntax for which has not yet been described. For the moment, we will use pseudocode to represent procedures so that we can focus on the imperative logic structures.
123456789101112131415161718192021222324252627282930313233343536373839-- START OF PSEUDOCODE
-- Procedure is named "STUDENT01.PRODUCT_ARRAY", provides one output parameter
-- Parameter "output_table" is a tablelike structure, based on an existing structure called IT_PRODUCT_SALES
-- The structure of IT_PRODUCT_SALES will be compatible with the data stored in it at the end of the procedure:
-- two varchar(20) columns and a decimal(15,2) column
-- Procedure logic is bounded by BEGIN and END
-- END OF PSUEDOCODE
BEGIN
-- declare three arrays for three ordered sets of data that will be populated later:
DECLARE productid VARCHAR(20) ARRAY;
DECLARE category VARCHAR(20) ARRAY;
DECLARE price DECIMAL(15,2) ARRAY;
-- populate the productid array with three address-ordered values
-- note the := notation, where the value to the right is retrieved and sent to the left
productid[1] := 'ProductA';
productid[2] := 'ProductB';
productid[3] := 'ProductC';
-- similarly, populate the category and price arrays
category[1] := 'CategoryA';
category[2] := 'CategoryB';
category[3] := 'CategoryC';
price[1] := 19.99;
price[2] := 29.99;
price[3] := 39.99;
-- populate the parameter output_table from the arrays; each row in the tablelike parameter will
-- be given the [1] addressed array values in the first row, the [2] address array values in the
-- second row, etc, and assigned to columns named in the AS clause
-- note the : notation, which is used to retrieve values from the named arrays
output_table = UNNEST(:productid, :category, :price)
AS (PRODUCTID, CATEGORY, PRICE);
-- when called, this procedure will return output_table with three rows
END;As shown in the example, the UNNEST() function converts one or more arrays into a table. The result table includes a row for each element of the specified array. The result of the UNNEST() function needs to be assigned to a table variable.
The ARRAY_AGG() function converts a column of a single table into an array. While converting the column of a table using the ARRAY_AGG() function, the columns can be placed in the array using ORDERY BY to do it in ascending or descending order, and also to place any NULLs at the beginning or in the end using NULLS FIRST or NULLS LAST.
1ARRAY_AGG(<table_name>.<column_name> [ORDER BY {<expression> [ASC | DESC] [NULLS FIRST | NULLS LAST]}) ; Access Any Cell
The index-based cell access allows you to access any cell (read/write) of an intermediate table variable or table parameter directly.
1<table_variable>.<column_name>[<index>]The index is a number which can be a number between 1 to 2^31 or an SQL expression, or a Scalar UDF that returns a number. Reading and writing can be performed on a cell of a table variable using the index value.
When writing a value to the table variable, use the following syntax:
1ex_emp.emp_id[1] = '10001';When reading, you must precede the table variable with a ":"
1outvar = :intab.B[100];123456789101112131415161718192021222324-- START OF PSEUDOCODE
-- Procedure is named "HA150::build_emp_list", provides one output parameter
-- Parameter "ex_emp" is a tablelike structure with four columns:
-- emp_id, name.first, name.last are nvarchar(10, and saleamount is integer
-- Procedure is read only
-- Procedure logic is bounded by BEGIN and END
-- END OF PSUEDOCODE
BEGIN
-- Assign values to the first row of ex_emp; note the colon notation is not used as the values
-- assigned are not being retrieved from anywhere
ex_emp.emp_id[1] = '10001';
ex_emp."name.first"[1] = 'Mickey';
ex_emp."name.last"[1] = 'Mouse';
ex_emp.saleamount[1] = 2000;
-- Similarly, assign values to the second row
ex_emp.emp_id[2] = '10002';
ex_emp."name.first"[2] = 'Don';
ex_emp."name.last"[2] = 'Duck';
ex_emp.saleamount[2] = 3000;
-- when called, this procedure will return ex_emp with two rows
END;Restrictions:
Physical tables cannot be accessed.
Not applicable in SQL queries like SELECT :MY_TABLE_VAR.COL[55] AS A FROM DUMMY. You need to assign the value to be used to a scalar variable first.
Arrays can be used to support data manipulation and conversion.
1234567891011121314151617181920212223242526272829303132-- START OF PSEUDOCODE
-- Function is named "Convert_OfficialsHours" and takes one input parameter and returns one output value
-- Parameter im_to is varchar(1) and indicates the target time unit to convert accrued vacation hours to
-- Output value lv_factor is decimal(5,2) and will hold the conversion of hours into the specified unit
-- A table variable for holding calculations, lt_conversion, is created based on the output of an UNNEST()
-- Functions are read only by definition
-- Function logic is bounded by BEGIN and END
-- END OF PSEUDOCODE
BEGIN
-- declare two arrays for two ordered sets of data that will be used in a calculation later
-- array "unit" will indicate unit of time: minute, hour, or day
-- array "factor" is a multiplier to convert hours to target unit
DECLARE unit VARCHAR(1) ARRAY;
DECLARE factor DEC(5,2) ARRAY;
-- populate the arrays with conversion units and multiplier factors
-- the formatting of the statements is to make it clear which unit goes with which factor
unit[1] := 'm'; factor[1] := 60;
unit[2] := 'h'; factor[2] := 1;
unit[3] := 'd'; factor[3] := 1.0/24.0;
lt_conversion = UNNEST(:unit, :factor)
AS (Unit, Factor);
SELECT COALESCE(Factor, 1.0) INTO lv_factor
FROM :lt_conversion
WHERE Unit = :im_to;
-- additional syntax omitted, including return of lv_factor to the calling SELECT calling this function
END;The above syntax performs the following tasks:
- The UNNEST() function converts the referenced arrays into a tablelike variable, with specified column names.
- In this case, the unit and factor arrays become columns named Unit and Factor in table variable lt_conversion.
- The COALESCE() function returns the first non-NULL value from its input arguments.
- In this case, the :im_to parameter is searched in the lt_conversion table variable and the appropriate value of Factor is returned.
- If the :im_to value is invalid, no rows are returned and Factor is NULL.
If no rows are returned and Factor is NULL, COALESCE() skips the value of Factor and returns a conversion ratio of 1.0.
There are certainly easier ways to convert a value from hours to minutes or days (CASE, IF, and a lookup table are just some possibilities), but this example allowed a relatively intuitive demonstration of array operations. Also note that an array was chosen instead of creating a table for conversion factors because tables (or other permanent structures) cannot be created in functions.
