In continuation to What’s New in HANA 2.0 SPS04 – SQL, we will now look into what’s available as part of SQLScript in a two part series due to the long list of new features for it.
Functions or procedures can now call itself within the body recursively until a condition is met. Previously, this could only be achieved by rewriting the recursive logic to a more complex iterative logic and now it can be simplified with recursive calls.
Example of recursive procedure
create procedure factorial_proc(in i int, out j int) as begin if :i <= 1 then j = 1; else call factorial_proc(:i-1, j); j = :i * :j; end if; end; call factorial_proc(1, ?); -- returns 1 call factorial_proc(4, ?); -- returns 24 call factorial_proc(10, ?); -- returns 3628800
Example of recursive function
create function factorial_func(i int) returns j int as begin if :i <= 1 then j = 1; else j = :i * factorial_func(:i-1); end if; end; select factorial_func(1) from dummy; -- returns 1 select factorial_func(4) from dummy; -- returns 24 select factorial_func(10) from dummy; -- returns 3628800
Example of recursive table function
create function factorial_func2(i int) returns table(a int) as begin if :i <= 1 then return select 1 as a from dummy; else return select :i * a as a from factorial_func2(:i - 1); end if; end; select * from factorial_func2(1); -- returns 1 select * from factorial_func2(4); -- returns 24 select * from factorial_func2(10); -- returns 3628800
For procedures there is a call depth limit which by default is 32 but can be configured between 8 and 128 depth using the following configuration.
set '__SQLSCRIPT_MAX_CALL_DEPTH' = '50'; alter system alter configuration ('indexserver.ini', 'system') set ('sqlscript', 'max_call_depth') = '50' with reconfigure;
Control Cursor Holdability
By default SAP HANA holds the cursor for the resultset after commit but released after rollback. This is an issue when processing each row but due to some issue that the record needs to be rolled back and then iterate to the next record for continuous processing. For such a case, the resultset is released after the rollback and cannot iterate any further.
To resolve such issue, in SPS03 cursor holdability was first introduced to hold the resultset even after rollback.
- Declare cursor with holdability both for commit and for rollback
DECLARE CURSOR cursor_name WITH HOLD FOR …
- Declare cursor without holdability both for commit and for rollback
DECLARE CURSOR cursor_name WITHOUT HOLD FOR …
- Declare cursor with holdability for commit and without holdability for rollback
DECLARE CURSOR cursor_name FOR …
Cursor holdability is also extended for JDBC client interface extending the standard
- HOLD_CURSORS_OVER_COMMIT (standard)
- CLOSE CURSORS_AT_COMMIT (standard)
- HOLD_CURSORS_OVER_ROLLBACK (extended)
- HOLD_CURSORS_OVER_COMMIT_AND_ROLLBACK (extended)
Auto Type Derivation
When using a variable in SQLScript, the type of a variable had to be specified during its declaration, and it is now possible to declare a variable without specifying the type explicitly and let SQLScript determine the type automatically.
This auto type derivation can be used for scalar variables, tables and arrays.
- DECLARE <var> AUTO = <value>
Example of auto type derivation. For value 1.0, the derived type is DECIMAL.
declare lv_price auto = 1.0; declare la_product auto = array('HT-1000', 'HT-2000'); declare lt_products auto = select * from "MD.Products";
Any Table Type
In some cases a developer does not want to explicitly define the structure of a table parameter. The type is defined during DDL time as wildcard as table(…) and determined during query compilation time.
create procedure myproc1(out out_tab table(...)) as begin out_tab = select * from "MD.Products"; end;
The type can be determined even for nested call statements
create procedure myproc1( in in_tab table(...), out out_tab table(c int)) as begin out_tab = select * from :in_tab; end; create procedure myproc2 as begin it0 = select 1 c from test_tab; call myproc1(:it0, :out_tab); end;
Sorted Table Variables
A special kind of table variables designed to provide efficient access to its data records by a defined key which is very useful when working with large datasets. The data records of sorted table variables are always sorted by a search key which is specified in the data type of the variable. When accessing the data via the SQLScript search operator, the efficient binary search is utilized if possible.
The search key can be a subset of the table variable columns where the sequence of the columns determines the sort ordering.
Defining search key
The search key can be specified as part of a user defined table type:
CREATE TYPE <name> AS TABLE (<column list>) SQLSCRIPT SEARCH KEY(<key list>)
The search key can also be specified as part of a variable declaration:
DECLARE <name> TABLE(<column list>) SEARCH KEY(<key list>) DECLARE <name> <table type> SEARCH KEY(<key list>)
The search key can also be specified as part of a parameter definition
CREATE PROCEDURE <proc> (IN <param> TABLE(<column list>) SEARCH KEY(<key list>)) CREATE PROCEDURE <proc> (IN <param> <table type> SEARCH KEY(<key list>))
Using search key
When using search, binary search is utilized internally and the first matching record is returned. When using partial search key and no record is matched then a NULL value is returned if full search key is used, then a negative value is returned instead of a NULL.
e.g.) for table variable LT sorted by (B,A,C)
|:LT.SEARCH(B, 1)||Search column B for value 1 is binary searched|
|:LT.SEARCH((B, A), (1, 2))||Search column B, A for value pair (1,2) is binary searched|
|:LT.SEARCH((B, C), (1, 15))||Search column B, C but not utilizing binary search due to different sequence|
|:LT.SEARCH(A, 1)||Also, binary search cannot be utilized since it is not first column of search key|
Iterating over records with the same value
When searching with partial key and multiple values are matched only the first matching record is return. So, to get all the data that is match the result needs to be iterated. The following example shows how the to iterate using search key.
DECLARE pos int; DECLARE mytab TABLE (key1 int, key2 int, key3 int, value int) SEARCH KEY(key1, key2, key3); DECLARE keyval1, keyval2 int; ... pos = :mytab.SEARCH((key1, key2), (keyval1, keyval2)); IF pos > 0 THEN WHILE :mytab.key1[pos] = keyval1 AND :mytab.key2[pos] = keyval2 DO -- do something with the record at position "pos" ... pos = pos + 1; END WHILE; END IF;
SQLScript now supports the use of IN clauses as conditions in IF or WHILE statements
IF :im_product_cat IN ('Notebook', 'PC', 'Handheld') THEN ex_product_class = 'Asset'; ELSEIF :im_product_cat IN ('Mouse', 'Keyboard', 'Speaker') THEN ex_product_class = 'Other'; END IF;
The following are different use cases for the IN operator
IN operator with multiple expressions
IF (:key, :im_product_cat) NOT IN ((1,’Notebook’), (2, ‘PC’), (3, ‘Handheld’) THEN
IN operator with subquery
IF :im_product_cat IN (select category from “MD.Products” where key = 1)
SQLScript now supports the use of EXISTS clause as conditions in IF and WHILE statements. The operator evaluates to true if a subquery returns a non-empty result set and false otherwise.
WHILE :i < 100 AND EXISTS (SELECT * FROM "MD.Products" WHERE key = :i) DO i = :i + 1; ... END WHILE