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.

0 Comments

Thanks for visiting our site : sapguidanceconsultant.blogspot.com
For Fast Response message us through "Contact Form".

Post a Comment

Thanks for visiting our site : sapguidanceconsultant.blogspot.com
For Fast Response message us through "Contact Form".

Post a Comment (0)

Previous Post Next Post