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,?);
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.
Thank you for Visiting...
Share your comments...
