SAP HANA: Cursor in Procedure

What is Cursor?

·         Cursor is used to fetch single rows from the result set returned by a query.

·         Cursors can be defined either after the signature of the procedure and before the procedure’s body or at the beginning of a block with the DECLARE token.

·         The cursor is defined with a name, optionally a list of parameters, and an SQL SELECT statement.

·         The cursor provides the functionality to iterate through a query result row-by-row.

·         Updating cursors are not supported.

Note:

Avoid using cursors when it is possible to express the same logic with SQL. You should do this as cursors cannot be optimized the same way SQL can.

To Create a Table:

CREATE COLUMN TABLE "KABIL_PRACTICE"."PRODUCT_DETAILS_CUR" (

          "PRODUCT_ID" INTEGER PRIMARY KEY,

          "PRODUCT_NAME" VARCHAR(100),

          "PRICE" FLOAT

);

To insert records into the table:

INSERT INTO "KABIL_PRACTICE"."PRODUCT_DETAILS_CUR" VALUES(1,'SHIRTS', 500);

INSERT INTO "KABIL_PRACTICE"."PRODUCT_DETAILS_CUR" VALUES(2,'JACKETS', 2000);

INSERT INTO "KABIL_PRACTICE"."PRODUCT_DETAILS_CUR" VALUES(3,'TROUSERS', 1000);

INSERT INTO "KABIL_PRACTICE"."PRODUCT_DETAILS_CUR" VALUES(4,'COATS', 5000);

INSERT INTO "KABIL_PRACTICE"."PRODUCT_DETAILS_CUR" VALUES(5,'PURSE', 800);

To Create a Table Type:

CREATE TYPE "KABIL_PRACTICE"."TT_PRODUCT_DETAILS_CUR" AS TABLE(

          "PRODUCT_ID" INTEGER PRIMARY KEY ,

          "PRODUCT_NAME" VARCHAR(100),

          "PRICE" FLOAT

);

To Drop a Table Type:

drop type "KABIL_PRACTICE"."TT_PRODUCT_DETAILS_CUR";

To Create a Procedure:

CREATE PROCEDURE "KABIL_PRACTICE"."CURSOR_EXAMPLE" (

        IN ip_rate DECIMAL(15,2),

        OUT ex_products "KABIL_PRACTICE"."TT_PRODUCT_DETAILS_CUR")

    LANGUAGE SQLSCRIPT

    SQL SECURITY INVOKER

    AS

BEGIN

/*****************************

    Write your procedure logic

 *****************************/

 DECLARE v_new_price DECIMAL(15,2);

 DECLARE CURSOR c_products FOR

 SELECT PRODUCT_ID, PRODUCT_NAME, PRICE

        FROM "KABIL_PRACTICE"."PRODUCT_DETAILS_CUR";

 FOR cur_row as c_products DO

    v_new_price := cur_row.PRICE + (cur_row.PRICE * :ip_rate);

    UPDATE "KABIL_PRACTICE"."PRODUCT_DETAILS_CUR"

    SET PRICE = v_new_price where PRODUCT_ID = cur_row.PRODUCT_ID;

  END FOR;

 ex_products = select PRODUCT_ID, PRODUCT_NAME, PRICE

                    FROM "KABIL_PRACTICE"."PRODUCT_DETAILS_CUR" ;

END;

To Call a Procedure:

call "KABIL_PRACTICE"."CURSOR_EXAMPLE" (.5,?);

Result: