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.
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.
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"
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.
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>]
In SQLScript you can create dynamic SQL using one of the following commands: EXEC and EXECUTE IMMEDIATE.
In SQLScript there are two different logic containers:
- and User-Defined Function.
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.