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:

SAP HANA: Dynamic Filter Using Procedure

Dynamic Filter in HANA

In this article, we will show an example on - How to implement and call dynamic filtering in a procedure.

Introduction:

·         SAP does not recommend the use of dynamic SQL (EXEC statement) when developing SQLScript procedures.

·         For dynamic SQL, we now have a new statement in SQLScript called APPLY_FILTER.

This statement accepts two parameters.

·         The first parameter is the dataset in which you want to apply the filter.  This dataset can be a database table, database view, HANA attribute or calculation view, or even an intermediate table variable.

·         The second parameter is the filter condition itself. This would be very similar syntax that you would use in the WHERE clause of a SELECT statement. 

In this example, we are going to use table PRODUCT and pass the filter condition dynamically while calling the procedure. The output will be sent to output using a table type.

Create Table:

Copy and paste the below script in SQL editor and execute.

Note: If you already have created the PRODUCT table in previous example, then skip this step.

To Create a Table:

create column table "KABIL_PRACTICE"."PRODUCT_DF"(

      "PRODUCT_ID" VARCHAR(10),

      "PRODUCT_NAME" VARCHAR (100),

    "CATEGORY" VARCHAR(100),

      primary key ("PRODUCT_ID")

);

To Insert a records into the Table:

insert into "KABIL_PRACTICE"."PRODUCT_DF" values('P1','Shirts', 'Clothes');

insert into "KABIL_PRACTICE"."PRODUCT_DF" values('P2','Jackets', 'Clothes');

insert into "KABIL_PRACTICE"."PRODUCT_DF" values('P3','Trousers', 'Clothes');

insert into "KABIL_PRACTICE"."PRODUCT_DF" values('P4','Coats', 'Clothes');

insert into "KABIL_PRACTICE"."PRODUCT_DF" values('P5','Purse', 'Accessories');

To Create a Table Type:

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

      "PRODUCT_ID" VARCHAR(10),

      "PRODUCT_NAME" VARCHAR (100),

      "CATEGORY" VARCHAR (100)

);

To Create a Procedure:

CREATE PROCEDURE "KABIL_PRACTICE"."PROCEDURE_DYNAMIC_FILTER"(

        IN im_filter_string VARCHAR(5000),

        OUT output_table "KABIL_PRACTICE"."TT_PRODUCT_DF" )

    LANGUAGE SQLSCRIPT

    SQL SECURITY INVOKER

    AS

/*********BEGIN PROCEDURE SCRIPT ************/

BEGIN

    output_table = APPLY_FILTER("KABIL_PRACTICE"."PRODUCT_DF", :im_filter_string) ;

END;

To Call a Procedure:

CALL "KABIL_PRACTICE"."PROCEDURE_DYNAMIC_FILTER"(im_filter_string => '"CATEGORY" = ''Clothes''', output_table => ?);

Result:

Thank you...

Please Share Your Comments...