Add function to procedure in Oracle
Course project: Add a function to a procedure
- First, you call the function within a calculation by typing
V_TAX := V_TOTAL_ITEM * GET_PERCENT(V_CUST_ID);
and pressing Enter.
- Next, you add one line to the UPDATE command so that the tax is updated. Type
TAX_AMOUNT = V_TAX
and press Enter.
The completed procedure should look like this:
CREATE OR REPLACE PROCEDURE SHIPPING_FEE
(I_SALES_ID IN NUMBER) AS
BEGIN
DECLARE
V_SHIPPING_FEE NUMBER;
V_TOTAL_ITEM NUMBER;
V_TOTAL_SALE NUMBER;
V_TAX NUMBER;
V_CUST_ID NUMBER;
BEGIN
-- query the table now.
SELECT TOTAL_SALE_AMOUNT,
TOTAL_ITEM_AMOUNT, TAX_AMOUNT, CUST_ID
INTO
V_TOTAL_SALE,V_TOTAL_ITEM, V_TAX, V_CUST_ID
FROM CUSTOMER_SALE
WHERE SALES_ID = I_SALES_ID;
V_TAX := V_TOTAL_ITEM * GET_PERCENT(V_CUST_ID);
V_SHIPPING_FEE := V_TOTAL_ITEM * .10;
V_TOTAL_SALE :=
V_TOTAL_ITEM + V_SHIPPING_FEE + V_TAX;
-- update the table now.
UPDATE CUSTOMER_SALE
SET TOTAL_SALE_AMOUNT = V_TOTAL_SALE,
SHIPPING_HANDLING_FEE = V_SHIPPING_FEE,
TAX_AMOUNT = V_TAX
WHERE SALES_ID = I_SALES_ID;
END;
END;