SAP HANA: Functions in Procedures

 SAP HANA: Functions in Procedures:

Example: Sales Procedure

Scenario:

            Whenever I enter a record into the sales table, it will affect the other tables based on that entry. Such as if I make an entry in “SALES_PROC” for 20 PEN sold it will affect the “PRODUCT_MASTER_PROC” table Stock.

            And also “SALES_AMOUNT” column in “SALES_PROC” table will be calculated automatically based “USP” (Unit Selling Price) column in a “PRODUCT_ITEM_PROC” table.

Let’s see for details, here I used 3 tables named as "PRODUCT_MASTER_PROC", "PRODUCT_ITEM_PROC" and "SALES_PROC" as shown below:

Table 1: Product_Master_Proc

Table 2: PRODUCT_ITEM_PROC

Table 3: SALES_PROC

CODE:

To create a PRODUCT_MASTER_PROC table:

create column table "KABIL_PRACTICE"."PRODUCT_MASTER_PROC"

(

"P_ID" integer Primary Key,

"P_NAME" nvarchar(35),

"C_ID" integer,

"Category" nvarchar(35),

"Stock" integer

);

To insert a record in a PRODUCT_MASTER_PROC table:

insert into "KABIL_PRACTICE"."PRODUCT_MASTER_PROC" values (1,'PEN',1,'STAIONARY',150);

insert into "KABIL_PRACTICE"."PRODUCT_MASTER_PROC" values (2,'NOTEBOOK',1,'STAIONARY',100);

insert into "KABIL_PRACTICE"."PRODUCT_MASTER_PROC" values (3,'CHAIR',2,'FURNITURE',150);

insert into "KABIL_PRACTICE"."PRODUCT_MASTER_PROC" values (4,'TABLE',2,'FURNITURE',150);

insert into "KABIL_PRACTICE"."PRODUCT_MASTER_PROC" values (5,'FAN',3,'ELECTRICALS',150);

insert into "KABIL_PRACTICE"."PRODUCT_MASTER_PROC" values (6,'LIGHT',3,'ELECTRICALS',150);

To create a PRODUCT_ITEM_PROC table:

create column table "KABIL_PRACTICE"."PRODUCT_ITEM_PROC"

(

"P_ID" integer primary key,

"P_NAME" nvarchar(35),

"C_ID" integer,

"USP" Decimal (34,2),

foreign key("P_ID") references "KABIL_PRACTICE"."PRODUCT_MASTER_PROC"("P_ID")

);

To insert a record in a PRODUCT_ITEM_PROC table:

insert into "KABIL_PRACTICE"."PRODUCT_ITEM_PROC" values (1,'PEN',1,10);

insert into "KABIL_PRACTICE"."PRODUCT_ITEM_PROC" values (2,'NOTEBOOK',1,30);

insert into "KABIL_PRACTICE"."PRODUCT_ITEM_PROC" values (3,'CHAIR',2,150);

insert into "KABIL_PRACTICE"."PRODUCT_ITEM_PROC" values (4,'TABLE',2,1000);

insert into "KABIL_PRACTICE"."PRODUCT_ITEM_PROC" values (5,'FAN',3,650);

insert into "KABIL_PRACTICE"."PRODUCT_ITEM_PROC" values (6,'LIGHT',3,100);

To create a SALES_PROC table:

create column table "KABIL_PRACTICE"."SALES_PROC"

(

"S_ID" integer primary key generated by default as IDENTITY,

"P_ID" integer,

"P_NAME" nvarchar(35),

"QTY" integer,

"Sales_AMount" Decimal(34,2),

foreign key ("P_ID") References "KABIL_PRACTICE"."PRODUCT_ITEM_PROC" ("P_ID")

);

TABLE TYPE:

A table type is

·         Similar to a database table but we can’t insert/update/delete. It contains only structure.

·         Used to define parameters for a procedure that represent tabular results.

To create a Table Type:

create type "KABIL_PRACTICE"."TT_SALES_PRODUCT_INVENTORY" as table

(

"S_ID" integer primary key,

"P_ID" integer,

"P_NAME" nvarchar(35),

"QTY" integer,

"Sales_AMount" Decimal(34,2)

)

To Create a Procedure:

create procedure "KABIL_PRACTICE"."PROC_SALES_INVENTORY"

(

IN "P_ID" integer,

IN "P_NAME" nvarchar(35),

IN "QTY" integer,

OUT Result "KABIL_PRACTICE"."TT_SALES_PRODUCT_INVENTORY"

)

language SQLSCRIPT

as begin

declare PROD_ID integer;

declare INV integer;

declare EP decimal (34,2);

declare SALES_AMOUNT Decimal(34,2);

select count(*) into PROD_ID from "KABIL_PRACTICE"."PRODUCT_ITEM_PROC" where "P_ID" = :P_ID;

select "Stock" into INV from "KABIL_PRACTICE"."PRODUCT_MASTER_PROC" where "P_ID" =:P_ID;

select "USP" into EP from "KABIL_PRACTICE"."PRODUCT_ITEM_PROC" where "P_ID" =:P_ID;

IF INV >= :QTY THEN

SALES_AMOUNT := (:QTY * :EP);

insert into "KABIL_PRACTICE"."SALES_PROC" ("P_ID","P_NAME","QTY","Sales_AMount") values (:P_ID,:P_NAME,:QTY,:SALES_AMOUNT);

update "KABIL_PRACTICE"."PRODUCT_MASTER_PROC" set "Stock" = "Stock" - :QTY where  "P_ID" = :P_ID;

END IF;

Result = select * from "KABIL_PRACTICE"."SALES_PROC";

end;

To Call a Procedure:

call "KABIL_PRACTICE"."PROC_SALES_INVENTORY" (1,'PEN',30,?);

 Note:

Here, I make an entry by mentioning “Product_Id”, “Product_Name” and “Quantity” alone.

Result:

Let check the “PRODUT_MASTER_PROC” table

Now check the “PRODUCT_ITEM_PROC and “SALES_PROC” Table

How to Use Function in Procedure:

            From the above, we can see the “SALES_AMOUNT” is calculated by performing multiplication of quantity with a unit selling price. The same logic can be implemented by using the function.

Example:

To Create a Function:

create function "KABIL_PRACTICE"."SALES_AMOUNT"

 ( Q integer, USP integer)

 returns result decimal (34,2)

 language SQLSCRIPT

 SQL SECURITY INVOKER as

 Begin

 result := :Q * :USP;

 END;

To use function in Procedure:

create procedure "KABIL_PRACTICE"."PROC_SALES_INVENTORY_EHE"

(

IN "P_ID" integer,

IN "P_NAME" nvarchar(35),

IN "QTY" integer,

OUT Result "KABIL_PRACTICE"."TT_SALES_PRODUCT_INVENTORY"

)

language SQLSCRIPT

as begin

declare PROD_ID integer;

declare INV integer;

declare EP decimal (34,2);

declare SALES_AMOUNT Decimal(34,2);

DECLARE empty_name CONDITION FOR SQL_ERROR_CODE 10001;

DECLARE EXIT HANDLER FOR empty_name

begin

SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;

end;

select count(*) into PROD_ID from "KABIL_PRACTICE"."PRODUCT_ITEM_PROC" where "P_ID" = :P_ID;

select "Stock" into INV from "KABIL_PRACTICE"."PRODUCT_MASTER_PROC" where "P_ID" =:P_ID;

select "USP" into EP from "KABIL_PRACTICE"."PRODUCT_ITEM_PROC" where "P_ID" =:P_ID;

IF INV >= :QTY THEN

SALES_AMOUNT := "KABIL_PRACTICE"."SALES_AMOUNT"(:QTY,:EP);

(or)

/*--select "KABIL_PRACTICE"."SALES_AMOUNT"(:QTY,:EP) into SALES_AMOUNT from dummy;---*/

insert into "KABIL_PRACTICE"."SALES_PROC" ("P_ID","P_NAME","QTY","Sales_AMount") values (:P_ID,:P_NAME,:QTY,:SALES_AMOUNT);

update "KABIL_PRACTICE"."PRODUCT_MASTER_PROC" set "Stock" = "Stock" - :QTY where  "P_ID" = :P_ID;

END IF;

Result = select * from "KABIL_PRACTICE"."SALES_PROC";

end;

Note:

The highlighted area shows the various ways to use function in HANA Procedures.