Hi All,
I am writing this blog post about Anonymous Block Do..Begin in SAP HANA WEB IDE with an simple example. We can write complex DML statements too but below example will give an idea on how to formulate and write Anonymous Block Do..Begin in SAP HANA WEB IDE.
So what are Anonymous Block Do..Begin in SAP HANA WEB IDE or HANA Studio or Eclipse IDE ?
Anonymous block is an executable DML statement which can contain imperative or declarative statements.
All SQL Script statements supported in procedures are also supported in anonymous blocks. Compared to procedures, an anonymous block has no corresponding object created in the metadata catalog.
An anonymous block is defined and executed in a single step by using the following syntax:
DO [(<parameter_clause>)] BEGIN [SEQUENTIAL EXECUTION] <body> END <body> ::= !! supports the same feature set as procedure did
Let us write a small example to update a table with a for loop.
SET SCHEMA "MYTESTSCHEMA";
Do ()
BEGIN
declare i integere;
declare v_input1 NVARCHAR(10);
declare v_input2 NVARCHAR(500);
declare v_input3 NVARCHAR(10);
declare v_input4 NVARCHAR(10);
Input_Data = select column1,column2,column3,colum4 from "SAPHANADB"."MYTESTSCHEMA".db.MAIN_BKP" (selecting sample columns from a table)
For i in 1 ..RECORD_COUNT(:Input_data)
Do
v_input1 = :Input_Data."column1"[:i];
v_input2 = :Input_Data."column2"[:i];
v_input3 = :Input_Data."column3"[:i];
v_input4 = :Input_Data."column4"[:i];
Update "SAPHANADB"."MYTESTSCHEMA".db.MAIN_Table" A
Set A."STATUS" = '020'
WHERE A.Employee_Name = :v_Input1
and A.Employee_Address = :v_Input2
and A.Manager_Name = :v_Input3
and A.Employee_Salary = :v_Input4
and A.Employee_HireDate = '20220417';
END FOR;
END;
Similarly we can write Anonymous Block by doing a UNION ALL of multiple select statements.
This is a small example but this blog post will help to write the Anonymous Block in your project for DML statements.