What’s New in HANA 2.0 SPS04 – SQLScript part 1

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.

Recursive SQLScript

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 …​

JDBC support

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)

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;

In Operator

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

  …

END IF; 

IN operator with subquery

IF :im_product_cat IN (select category from “MD.Products” where key = 1)

  …

END IF; 

EXISTS Operator

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