Table of Contents

SAP HANA - SQLScript

About

The SAP HANA database has its own scripting language named SQLScript.

SQLScript operate on tables using SQL queries for set processing.

SqlScript implements the Orchestration logic (control-flow logic) using imperative language constructs such as loops and conditionals on top of the data-flow logic (Declarative Logic, Sql)

HANA use an intermediate language L for translating SQLScript into a calculation model.

Procedure

Procedures can be written in the following languages:

There are additional libraries of procedures, called SAP HANA :

that can be called via SQL or from within another procedure.

Tools

GUI

SQLScript Editor

The SAP HANA SQLScript editor studio allows you to create, edit, activate and debug stored procedures.

In studio, open the SQL Console 

SELECT TOP 1000 * FROM "SYS"."TABLES"

Sap Hana Cloud Sql Console Select All Tables

Console

For a cloud instance, you need first to create a database tunnel: Automating the Use of Database Tunnels (Remote Database Access in Persistence Service). A database tunnel allows to use database tools, such as the SAP HANA studio or Eclipse Data Tools Platform, to connect to the remote database instance. It provides direct access to a schema and allows to manipulate it at database level.

Hdbsql

SAP HANA - Hdbsql

JDBC

SAP HANA - Java Driver (ngdbc.jar) 

java -jar ngdbc.jar -u <user,password>
                   [-n <hostname:port>]
                   [-i <instance_number>]
                   [-d <database_name>]
                   [-o <connect option>]
                   [-c <sql command>]

See JDBC Command-Line Connection Options

List

Anonymous Block

Anonymous%20Block

Dynamic Sql

In SQLScript you can create dynamic SQL using one of the following commands: EXEC and EXECUTE IMMEDIATE.

Logic Container

In SQLScript there are two different logic containers:

Procedure

CREATE PROCEDURE orchestrationProc LANGUAGE SQLSCRIPT READS SQL DATA
AS 
BEGIN  
	DECLARE v_id BIGINT;  
    DECLARE v_name VARCHAR(30);  
    DECLARE v_pmnt BIGINT;  
    DECLARE v_msg VARCHAR(200);  
    DECLARE CURSOR c_cursor1 (p_payment BIGINT) FOR   
		SELECT id, name, payment FROM control_tab   
			WHERE payment > :p_payment   
			ORDER BY id ASC;  
    CALL init_proc();   
	OPEN c_cursor1(250000);   
    FETCH c_cursor1 INTO v_id, v_name, v_pmnt;   
    v_msg = :v_name || ' (id ' || :v_id || ') earns ' || :v_pmnt || ' $.';   
	INSERT INTO message_box VALUES (:v_msg, CURRENT_TIMESTAMP);
    CLOSE c_cursor1; 
END  

CREATE TABLE message_box (p_msg VARCHAR(200), tstamp TIMESTAMP);

Procedures allows you to describe a sequence of data transformations on data passed as input and database tables (SQLScript or R) Procedure Home Create Procedure

User defined function

The User-Defined Function container is separated into Scalar User-Defined Function and Table User-Defined Function.

Documentation / Reference