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.
Articles Related
Procedure
Procedures can be written in the following languages:
- SQLScript: The language that SAP HANA provides for writing procedures.
- R: An open-source programming language for statistical computing and graphics, which can be installed and integrated with SAP HANA.
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"
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
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>]
List
Anonymous Block
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
- and User-Defined Function.
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.