Create Procedure   «Prev  Next»

Add function to procedure in Oracle

Course project: Add a function to a procedure

  1. First, you call the function within a calculation by typing
    V_TAX := V_TOTAL_ITEM * GET_PERCENT(V_CUST_ID);
    
    and pressing Enter.
  2. 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;