Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
pallab_haldar
Active Participant
4,113
In some business scenario need to parse the data and need to implement the loop and using calculation view it is not possible. The Cursor in SAP HANA procedure can be used for other purpose also.

Let's implement one scenario by creating a procedure using cursor -

Cursor Syntax -
DO 
BEGIN
DECLARE CURSOR DEMO_CUR1 for
SELECT EMAIL ID FROM "PLB_MTA.DB_PLB"::"EMPLOYEE";
FOR CURSORROW AS DEMO_CUR1
DO

/* YOUR CODE/*

END FOR;
END

 

Scenario -

EMPLOYEE table Structure -


 

Now we want to update salary of those employee under salary 30000 to input target salary using cursor.
CREATE PROCEDURE "PLB_MTRA.DB_PLB"::"DEMO_CURSOR" (
IN TRGT_SALARY DECIMAL(10,2),
OUT EMP "PLB_MTRA.DB_PLB"::"EMPLOYEE")
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN

DECLARE NEW_SALARY DECIMAL(10,2);

DECLARE CURSOR DEMO_CUR1 for
SELECT EMP_ID,SALARY from FROM "PLB_MTA.DB_PLB"::"EMPLOYEE";
FOR CURSORROW AS DEMO_CUR1
DO

NEW_SALARY := CURSORROW.SALARY;
IF (:NEW_SALARY < 31000) then

NEW_SALARY := TRGT_SALARY;
UPDATE "PLB_MTRA.DB_PLB"::"EMPLOYEE"
SET SALARY = NEW_SALARY where EMP_ID = CURSORROW.EMP_ID;
END IF;
EMP = select EMP_ID, EMP_NAME, EMAIL_ID, SALARY FROM "PLB_MTA.DB_PLB"::"EMPLOYEE";
RETURN EMP;
END FOR;
END;

 

Call the Procedure  . You will get the updated data.
 CALL "PLB_MTRA.DB_PLB"::"DEMO_CURSOR" (60000,?);

You will get the below -


Hope it will help in your project for other scenario to write cursor in HANA procedure.
Labels in this area