SAP HANA

SQL Scripts in SAP HANA

webadmin

One of the best techniques to achieve code-to-data paradigm, allow developer to write more complex logic which is not possible with simple views in HANA. This helps us to squeeze the best performance out of HANA DB.

This is DB programming language.

SQL script is a collection of extensions to SQL (DDL. DML, DQL, DCL). The purpose of SQL script is to provide procedural capabilities to SQL. It allows developers to write data intensive logic inside the heart of DB.

Q. Types of SQL Script Statements.

  1. Declarative Logic or Data Flow Logic [Pure SQL: – Sequence of DQL]. This code gets converted into data flow graph. HANA process in Parallel.
  2. Imperative Logic or Orchestration logic [IF, ELSE, LOOP, WHILE, FOR]. HANA will not process it in parallel.

Q. What kicks you out of parallel mode?

  • Local Variable or Scaler Parameters usage.
  • Inside a processing block, if we use DML/DDL
  • If system encounters any Imperative logic like IF, LOOP.
  • SQL statements which are not assigned to any implicit variables.

Best Practices:

  • Split the codes in different containers which can be parallelized.
  • Put all the imperative logic at the end, so most of the HANA runs the logic in parallel modes.

Containers: –

Containers is a block of SQL Script code. There are 3 types of containers.

  1. Anonymous Block: – Logic which we never need again. Never stored in DB.
  2. Procedure: – Stores the code in DB. Reusability. STORED PROCEDURE.
  3. Functions: –
    a. Scaler Function (SUDF): If returns single value.
    b. Table Function (TUDF): If returns table value.

Important Points: –

  • SQL Script is not case-sensitive just like ABAP.
  • Every statement in SQL Script ends with semi-column (:).
  • When we declare a variable and want to use the variable in the code, we must use colon symbol before the same (:), but not when we are assigning value to the variable
Declare x integer, Declare y integer.
X = 10;
Y = 20;
Z = :X + :Y
  • If we want to output data from SQL Script code, everything evolves around SQL. We can use a table called dummy to show output on console from variables.
  • When we are working in SQL console, system will set the default schema (session schema) to your user schema. Session Schema will be seen by running the below SQL. “Select CURRENT_SCHEMA from dummy;”.
  • If you do not assign value to a variable, the value will be assigned as NULL.

1. Anonyms block: –

DO (IN x integer => ?, IN y integer => ?) begin
declare z integer;
z = :x + :y;
select z from dummy;
End;

2. Procedure: – Can have multiple Input, multiple output, multiple in-out parameter

CREATE PROCEDURE decadd (IN x integer, IN y integer, OUT z integer)
LANGUAGE sqlscript as
begin
z = :x + :y;
End;

Exception Handling: –

CREATE PROCEDURE premtab ()
LANGUAGE sqlscript as 
BEGIN

 declare i integer;
 
 declare exit handler for sqlexception
  Select 'There is an error occured, please contact developer '
   as Error_Details,
   ::SQL_ERROR_CODE as error_code,
   ::SQL_ERROR_MESSAGE as error_message from dummy;
  
create table prem_emp( id integer, name varchar (30), primary key (id) );

for i in 0..10 do 
insert into prem_emp values ( :i, 'Employee' || :i );
end for;

END;

Any Error caught in exception can be identified by using this exception statements.

Execution Result:-

3. Functions: – Always have one return parameter and based on the return we will decide if this is a scalar or table function.

a. Scaler Function (SUDF): If returns single value.

CREATE FUNCTION area_of_circle( radius integer )
returns area decimal(5,2) as 

BEGIN
area = 3.14 * radius * radius;
END;

b. Table Function (TUDF): If returns table value.

create FUNCTION get_vendor_data( )
returns table ( lifnr  varchar(10) , name1 varchar(35) , adrnr varchar(10) )
as 

BEGIN
return select lifnr, name1, adrnr from "ZBW_IBMPRS"."STA_LFA1";
END;

How to Declare Variable in HANA

Syntax: DECLARE variableName variabletype.

  • Scalar Variable.
  • Arrays
  • Table Variables.
    • Implicit
    • Explicit
      • Using Table
      • Table Type

Numeric: TINYINT, SMALLINT, INT, BIGINT, INTEGER, DOUBLE, DECIMAL () / Char: VARCHAR, NVARCHAR, ALPHANUM/Date: TIMESTAMP, DATETIME, DATE/Binary: VARBINARY/Large Object: CLOB, BLOB, NLOB.

  • Scalar Variable: – Syntax for IF Condition
IF Condition THEN
---code
END IF.\

WHILE condition DO
---condition
END WHILE.

FOR i IN start...end DO
----fixed count.
END FOR.
  • Arrays

Store multiple values of same data type. Cannot have multiple columns.

DECLARE arr_name integer ARRAY := ARRAY (val1, val2, ….);

We cannot return an array out of procedure.

Cursors: – Cursors are handler for BD memory. Curser are bound to q query. It is possible to pass parameter to cursors. Cursors never pass-through database optimizer

Declare=> Open=>  Fetch=> Close.

Using a simple curser doesn’t make sense, unless using with Primary Key.

Unnest is a built-in function in HANA function to map data from an array to table.

  • Table Variables.

It can return this out as an output parameter of the procedure.

  • Implicit: – Automatically create an Internal table when declared.
Ex: - it_data = select * from “table” where price > 1000;
  • Explicit: –

i. Using table: –

Example: – Working with simple table example

Stored Procedure: For simple table return

CREATE PROCEDURE prem_tabfunction (out employees table 
( emp_id integer,
  emp_name varchar (80),
  salary integer,
  curr varchar (3)
) )
LANGUAGE sqlscript
default schema ZBW_IBMPRS as 

BEGIN
:employees.insert( (100, 'Prem Shanker', 97000, 'INR'), 1);
:employees.insert( (200, 'Shikha Sinha', 235000, 'INR'), 2);
:employees.insert( (300, 'Siddhi Sinha', 25000, 'EUR'), 3);

END;

ii. Using Table type: – If we need to return data out in specified structure every time from different procedures, then we can create a reusable table type and define output using the same type. It will be stored in current schema.

Create reusable table type

create  type tt_prem as table
( kunnr nvarchar (80),
  land1 varchar (80),
  name1 varchar (80),
  erdat date,
  bukrs varchar(4)
)

Stored Procedure: For replacement of Loop [ Not using loop in the SQL Script] with simple table return

CREATE PROCEDURE prem_loop_table (in ip_country varchar (2), out customer best.tt_prem)
LANGUAGE sqlscript
default schema ZBW_IBMPRS as 

BEGIN
declare rec_count, i integer;

it_kna1 = SELECT a.kunnr, a.land1, a.name1, a.erdat, b.bukrs
 		FROM "ZBW_IBMPRS"."STA_KNA1" as a 
 		INNER JOIN "ZBW_IBMPRS"."STA_KNB1" as b
 		on a.kunnr = b.kunnr
		WHERE land1 = ip_country;
		
rec_count = record_count(:it_kna1);

for i in 1..:rec_count do 
   :customer.insert( (:it_kna1.kunnr[i],
                      :it_kna1.land1[i],
                      :it_kna1.name1[i],
                      :it_kna1.erdat[i],
                      :it_kna1.bukrs[i] ) , :i);
end for;

select rec_count from dummy;      

END;

Q. How can we consume the HANA Stored Procedure in ABAP layer?

Create Procedure Proxy.

If a procedure is already present in the ABAP Schema, we can create a Procedure Proxy and consume the HANA Procedure. Then Create ABAP program to call the Procedure Proxy.

However the lifecycle management becomes difficult here, as we have to make sure that the Proxy objects are in sync with HANA Objects. Also all these(Proxy, HANA Proc and ABAP Program) reach together into Q or P system.

SAP has introduced another approach for Procedure (SQL Script Code) called AMDP

AMDP is a Substitute of Procedure and Procedure Proxy.

Lifecycle management is also simplified.

Just transport AMDP, and when the AMDP is called for the first time, HANA procedure is automatically created.

AMDP – ABAP Manages Database Procedure

AMDP is just another container of SQL Script code which is kept in ABAP side. It is ABAP global class created in SE24 or in ADT.

ABAP Class => AMDP Marker Interface – IF_AMDP_MARKER_HDB

The Main benefit of AMDP: –

  1. It is top-down approach.
  2. No life cycle challenges.
  3. Easy to code SQL script even w/o having access to HANA.

Important Points: –

  • Typically, AMDP is a static method of an ABAP class.
  • This method is having input and export parameter only pass by value.
  • No nested table/structure allowed for parameters.
  • AMDP only works if your DB is HANA DB.
  • An AMDP class can also contains normal ABAP methods as well.
  • Method with returning parameter cannot be implemented using AMDP.
  • The HANA procedure will be created not when you activate the AMDP rather when you call it for the first time.
  • When we call AMDP it will call HANA DB Procedures. Code-to-data paradigm.
  • When we want to use DB table in AMDP, it must be specified in using clause.

Creating AMDP Procedure

CLASS ZBW_AMDP_PREM DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC .

  PUBLIC SECTION.

  interfaces if_amdp_marker_hdb.
  class-methods get_vbak exporting value(Sales_order) type ztt_vbak_prem.

  PROTECTED SECTION.
  PRIVATE SECTION.
ENDCLASS.

CLASS ZBW_AMDP_PREM IMPLEMENTATION.

method get_vbak by database procedure for hdb language sqlscript options read-only using vbak.

declare rec_count, i integer;
declare lv_client varchar(3);
declare lv_user varchar(10);
declare lv_today date;
declare lv_curr varchar(3);

select current_date into lv_today from dummy;
i = ::current_line_number;

select session_context('CLIENT') ,
ucase (session_context( 'APPLICATIONUSER'))
into lv_client, lv_user
from dummy;


Sales_order = select vbeln, erdat,  netwr, 'USD'  as waerk,
vkorg, spart, 'x' as tagging, 7 as open_days
from vbak;

rec_count = record_count(:Sales_order);
select rec_count, lv_client, lv_user, lv_today, i from dummy;

select floor(14.5) "floor" from dummy;

endmethod.
ENDCLASS.

Additional functions are also used here like “session_context”, “floor” , “record_count”, “current_date”, “current_line_number”. These are not nessesary for the above code, but I am just showcasing that we can make use of many standard given functions for SQL Script.

Q. How to call the AMDP Class ?

It can be called inside an ABAP Program.

ABAP Program to call AMDP class

*&---------------------------------------------------------------------*
*& Report zcall_amdp_vbak
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT ZCALL_AMDP_VBAK.

ZBW_AMDP_PREM=>GET_VBAK(
  IMPORTING
    SALES_ORDER = data(itab)
).

cl_demo_output=>DISPLAY_DATA(
  EXPORTING
    VALUE   =  itab
).

ABAP Program Execution Result for AMDP Class

Hence, with SQL Script we can write Anonymous Block, Stored Procedure, Functions (Scaler and Table) as well as AMDP. The codes(SQL Script) are re-usable in all the mentioned objects. This is the biggest advantage of using SQL Script from a developer perspective.