SapHanaTutorial.Com HOME     Learning-Materials Interview-Q&A Certifications Quiz Online-Courses Forum Jobs Trendz FAQs  
     Explore The World of Hana With Us     
About Us
Contact Us
 Apps
X
HANA App
>>>
Hadoop App
>>>
Tutorial App on SAP HANA
This app is an All-In-One package to provide everything to HANA Lovers.

It contains
1. Courses on SAP HANA - Basics, Modeling and Administration
2. Multiple Quizzes on Overview, Modelling, Architeture, and Administration
3. Most popular articles on SAP HANA
4. Series of Interview questions to brushup your HANA skills
Tutorial App on Hadoop
This app is an All-In-One package to provide everything to Hadoop Lovers.

It contains
1. Courses on Hadoop - Basics and Advanced
2. Multiple Quizzes on Basics, MapReduce and HDFS
3. Most popular articles on Hadoop
4. Series of Interview questions to brushup your skills
Apps
HANA App
Hadoop App
';
Search
Stay Connected
Search Topics
Topic Index
+
-
SQLScript
+
-
Tables in SAP HANA
+
-
Procedure
+
-
Examples of Procedure
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, ?);

SAP HANA Procedure Example






Support us by sharing this article.



Explore More
Close X
Close X

Leave a Reply

Your email address will not be published. Required fields are marked *

Current day month ye@r *

 © 2017 : saphanatutorial.com, All rights reserved.  Privacy Policy