SAP HANA 2.0 - Database Procedures - Snippets
DATABASE PROCEDURE USING 'FOR' TO CREATE A TABLE & USAGE OF EXIT HANDLER:
CREATE PROCEDURE ZAG_FOR_001 (out count1 integer)
LANGUAGE SQLSCRIPT
DEFAULT SCHEMA "<ENTER_SCHEMA>"
AS
BEGIN
DECLARE i INTEGER = 0;
BEGIN
DECLARE EXIT HANDLER FOR SQL_ERROR_CODE 288
DROP TABLE ZAG_EMP_001;
CREATE COLUMN TABLE zag_emp_001 ( empid integer, empname varchar(20), primary key(empid) );
END;
FOR i in 1..10 DO
INSERT INTO zag_emp_001 VALUES (:i, 'Employee: ' || :i);
END FOR;
count1 = i;
select count(*) into count1 from <ENTER.SCHEMA>.zag_emp_001;
END;
DATABASE PROCEDURE USING CURSOR:
CREATE OR REPLACE PROCEDURE ZAG_CURSOR_001(OUT ev_matnr varchar(40))
LANGUAGE SQLSCRIPT
DEFAULT SCHEMA "UMAMAHESWARA"
AS
BEGIN
DECLARE lv_matnr varchar(40);
DECLARE CURSOR c1 FOR SELECT MATNR FROM SAPHANADB.MARA;
OPEN c1;
FETCH c1 INTO lv_matnr;
select lv_matnr into ev_matnr from dummy;
CLOSE c1;
END;
DATABASE PROCEDURE USING INLINE TABLE DECLARATION:
CREATE PROCEDURE ZAG_INLINE_TABLE_001(OUT et_mara TABLE(matnr varchar(40), ERSDA date))
LANGUAGE SQLSCRIPT
DEFAULT SCHEMA "<SCHEMA_NAME>"
AS
BEGIN
et_mara = select matnr, ersda from saphanadb.mara;
END;
Now, the same with an input parameter.
CREATE OR REPLACE PROCEDURE ZAG_INLINE_TABLE_001(IN mtart varchar(4), OUT et_mara TABLE(matnr varchar(40), ERSDA date))
LANGUAGE SQLSCRIPT
DEFAULT SCHEMA "<SCHEMA_NAME"
AS
BEGIN
et_mara = select matnr, ersda from saphanadb.mara where mtart = :mtart;
END;
DATABASE PROCEDURE TO CREATE SIMPLE TABLE WITH DATA (The below code works only in HANA 2.0):
CREATE OR REPLACE PROCEDURE ZAG_SIMPLE_TABLE ( OUT mytab TABLE (sno integer, amount integer), OUT cnt integer )
LANGUAGE SQLSCRIPT
DEFAULT SCHEMA "<SCHEMA_NAME>"
AS
BEGIN
mytab.sno[1] = 1;
mytab.amount[1] = 1000;
mytab.sno[2] = 2;
mytab.amount[2] = 2000;
:mytab.insert( (3, 3000),3 );
if(is_empty(:mytab)) THEN
SIGNAL SQL_ERROR_CODE 13000 set message_text = 'Custom text';
ELSE
cnt = record_count(:mytab);
END IF;
END;
To execute: CALL ZAG_SIMPLE_TABLE(?)
DATABASE PROCEDURE USING 'EXECUTE IMMEDIATE':
CREATE OR REPLACE PROCEDURE ZAG_DYN_TAB_001 ( IN TABNAME VARCHAR(60) )
LANGUAGE SQLSCRIPT
DEFAULT SCHEMA "<ENTER_SCHEMA>"
AS
BEGIN
EXECUTE IMMEDIATE 'SELECT COUNT(*) AS COUNT FROM ' || :TABNAME;
END;
Explanation: EXECUTE IMMEDIATE executes the SQL statement passed in a string argument. The results of queries
executed with EXECUTE IMMEDIATE are appended to the result iterator of the procedure.
DATABASE PROCEDURE WITH A SIMPLE ARRAY:
CREATE OR REPLACE PROCEDURE ZAG_PREPARE_ARRAY ( out val integer )
LANGUAGE SQLSCRIPT
DEFAULT SCHEMA "<ENTER_SCHEMA>"
AS
BEGIN
DECLARE arr TINYINT ARRAY = array (1,2,3,4,5);
DECLARE index_arr INTEGER ARRAY = array (10,20);
arr[1] = :index_arr[1];
arr[2] = :index_arr[2];
val = :arr[1];
END;
DATABASE PROCEDURE WITH INPUT FOR A SIMPLE CURSOR:
CREATE PROCEDURE ZAG_CURSOR_INP_001(IN cat varchar(20), out pid varchar(20), out pid1 varchar(20))
LANGUAGE SQLSCRIPT
DEFAULT SCHEMA "<SCHEMA_NAME>"
AS
BEGIN
DECLARE wa varchar(20);
DECLARE wa1 varchar(20);
--Declaration of Cursor
DECLARE CURSOR c1 (icat varchar(20)) FOR SELECT MATNR FROM SAPHANADB.MARA
WHERE MTART = :icat;
OPEN c1(:cat);
--Read table itab into wa index 1
FETCH c1 INTO wa;
pid = wa;
--Read table itab into wa index 2
FETCH c1 INTO wa1;
pid1 = wa1;
CLOSE c1;
END;
DATABASE PROCEDURE WITH CURSOR AND FOR:
CREATE OR REPLACE PROCEDURE ZAG_CURSOR_FOR_001(IN cat varchar(20), out pid varchar(20), out pid1 varchar(20))
LANGUAGE SQLSCRIPT
DEFAULT SCHEMA "<SCHEMA_NAME>"
AS
BEGIN
DECLARE wa varchar(20);
DECLARE allProds varchar(20) ARRAY;
--Declaration of Cursor
DECLARE CURSOR c1 (icat varchar(20)) FOR SELECT MATNR FROM SAPHANADB.MARA
WHERE MTART = :icat;
FOR wa AS c1(:cat) DO
allProds[c1::rowcount] = wa.matnr;
END FOR;
pid = :allProds[1];
pid1 = :allProds[2];
END;
DATABASE PROCEDURE WITH CURSOR, FOR and UNNEST Function:
UNNEST Function is used to convert ARRAY to a TABLE
CREATE OR REPLACE PROCEDURE ZAG_CURSOR_FOR_002(IN cat varchar(20), out pid table(matnr varchar(20)))
LANGUAGE SQLSCRIPT
DEFAULT SCHEMA "<SCHEMA_NAME>"
AS
BEGIN
DECLARE wa varchar(20);
DECLARE allProds varchar(20) ARRAY;
--Declaration of Cursor
DECLARE CURSOR c1 (icat varchar(20)) FOR SELECT MATNR FROM SAPHANADB.MARA
WHERE MTART = :icat;
FOR wa AS c1(:cat) DO
allProds[c1::rowcount] = wa.matnr;
END FOR;
--UNNEST Function - Helps to convert an ARRAY to a TABLE
pid = UNNEST(:allprods) as (matnr);
END;
DATABASE PROCEDURE WITH CURSOR, FOR and UNNEST Function - With Multiple ARRAYs:
CREATE OR REPLACE PROCEDURE ZAG_CURSOR_FOR_003(IN cat varchar(20),
out pid table(Material varchar(40), MaterialType varchar(4)) )
LANGUAGE SQLSCRIPT
DEFAULT SCHEMA "<SCHEMA_NAME>"
AS
BEGIN
DECLARE wa varchar(20);
DECLARE allProds varchar(40) ARRAY;
DECLARE mattype varchar(4) ARRAY;
--Declaration of Cursor
DECLARE CURSOR c1 (icat varchar(20)) FOR SELECT MATNR, MTART FROM SAPHANADB.MARA
WHERE MTART = :icat;
FOR wa AS c1(:cat) DO
allProds[c1::rowcount] = wa.matnr;
mattype[c1::rowcount] = wa.mtart;
END FOR;
--UNNEST Function - Helps to convert an ARRAY to a TABLE
pid = UNNEST(:allprods, :mattype) as (Material, MaterialType);
END;
DATABASE PROCEDURE TO DISPLAY TABLE DATA IN OUTPUT:
Comments
Post a Comment