Cursor in HANA

In this example we will learn – How to use CURSOR in HANA.

Introduction:

A cursor is a set of rows together with a pointer that identifies a current row.

Cursors are defined after the signature of the procedure and before the procedure’s body. 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 is 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.
In this example, we need to update the sales price of each record. We will pass the increased rate and use a cursor to update each record.
Create Table:

CREATE COLUMN TABLE <SCHEMA_NAME>.PRODUCT_DETAILS (
          PRODUCT_ID INTEGER PRIMARY KEY,
          PRODUCT_NAME VARCHAR(100),
          PRICE FLOAT
);
INSERT INTO "<SCHEMA_NAME>"."PRODUCT_DETAILS" VALUES(1,'SHIRTS', 500);
INSERT INTO "<SCHEMA_NAME>"."PRODUCT_DETAILS" VALUES(2,'JACKETS', 2000);
INSERT INTO "<SCHEMA_NAME>"."PRODUCT_DETAILS" VALUES(3,'TROUSERS', 1000);
INSERT INTO "<SCHEMA_NAME>"."PRODUCT_DETAILS" VALUES(4,'COATS', 5000);
INSERT INTO "<SCHEMA_NAME>"."PRODUCT_DETAILS" VALUES(5,'PURSE', 800);


Create Table Type:

CREATE TYPE "<SCHEMA_NAME>"."TT_PRODUCT_DETAILS" AS TABLE(
          PRODUCT_ID INTEGER PRIMARY KEY,
          PRODUCT_NAME VARCHAR(100),
          PRICE FLOAT
);

Create procedure:

Copy and paste the below script to create the procedure.
--REPLACE <SCHEMA_NAME> WITH YOUR SCHEMA NAME
CREATE PROCEDURE "<SCHEMA_NAME>"."CURSOR_EXAMPLE" (
        IN ip_rate DECIMAL(15,2),
        OUT ex_products "<SCHEMA_NAME>"."TT_PRODUCT_ DETAILS")
    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 "<SCHEMA_NAME>"."PRODUCT_DETAILS";
 FOR cur_row as c_products DO
    v_new_price := cur_row.PRICE + (cur_row.PRICE * :ip_rate);
    UPDATE "<SCHEMA_NAME>"."PRODUCT_DETAILS"
    SET PRICE = v_new_price where PRODUCT_ID = cur_row.PRODUCT_ID;
  END FOR;
 ex_products = select PRODUCT_ID, PRODUCT_NAME, PRICE
                    FROM "<SCHEMA_NAME>"."PRODUCT_DETAILS" ;
END;

Call procedure:

Call the procedure using below statement.
CALL "<SCHEMA_NAME>"."CURSOR_EXAMPLE"(.5, ?);