Implementing Cursor in SAP HANA Procedure

[pallab_haldar]

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.