SAP HANA SQL Stored Procedure Tutorial
What are SQL Stored Procedures?
A procedure is a unit/module that perform a specific task. This procedure can be combined to form larger programs. This basically forms the ‘Modular Design’. A procedure can be invoked by another procedure which is called the calling program.
Procedures are re-useable processing block with a specific sequence of data transformation. The procedure can have multi-input/output parameters. The procedure can be created as read-only or read-write.
An SQL Procedure can be created at –
- At Schema Level(Catalog Node)
- At Package Level(Content Node)
Stored Procedure syntax in SAP HANA is as shown below –
SYNTAX
CREATE PROCEDURE <proc_name> [(<parameter_clause>)] [LANGUAGE <lang>] [SQL SECURITY <mode>] [DEFAULT SCHEMA <default_schema_name>] [READS SQL DATA [WITH RESULT VIEW <view_name>]] AS {BEGIN [SEQUENTIAL EXECUTION] <procedure_body> END | HEADER ONLY }
The CREATE PROCEDURE statement creates a procedure using the mention programming language <lang>.
SYNTAX ELEMENTS
ELEMENTS | DESCRIPTION |
---|---|
<proc_name> | Procedure Name |
<parameter_clause> | The parameter is defined here. IN, OUT, INOUT parameter is there. Each parameter is marked using the keywords IN/OUT/INOUT
• IN – Used for Pass Value To procedure as INPUT. It is Read Only parameter. • OUT – Used for Return Value from Procedure as OUTPUT. • INOUT – Used for Pass and Return Value To Procedure by same parameter. |
LANGUAGE <Lang> | Defines the programming language used in the procedure. Default: SQLSCRIPT |
SQL SECURITY <mode> | Specifies the security mode of the procedure. Default: DEFINER
• DEFINER – Specifies that the execution of the procedure is performed with the privileges of the definer of the procedure. • INVOKER – Specifies that the execution of the procedure is performed with the privileges of the invoker of the procedure. |
<default_schema_name> | It defines the schema for unqualified objects in the procedure body. If nothing is define, then the current schema of the session is used for the procedure. |
READS SQL DATA | It marks the procedure as being read-only, it means the procedure does not modify the database data or its structure and that the procedure does not contain DDL or DML statements. This procedure only calls other read-only procedures. |
WITH RESULT VIEW <view_name> | It defines the result view to be used as the output of a read-only procedure. If a result view is specified for a procedure, then it can be called by an SQL statement in the same process as a table or view. |
SEQUENTIAL EXECUTION | This statement will force sequential execution of the procedure logic. No parallelism takes place. |
<procedure body> | It defines the main body of the procedure based on the programming language selected. |
HEADER ONLY | If Header Only is used, then only procedure properties are created with OID. |