Create Procedure   «Prev  Next»

Create replace Procedure [Course project]

Your solution should follow these steps:
  1. You began your procedure by typing CREATE OR REPLACE PROCEDURE SHIPPING_FEE and pressing Enter.
  2. To define the incoming parameter, you typed (I_SALES_ID IN NUMBER) AS and approved the entry by pressing Enter.
  3. You began typing in the declaration section to define the first variable. You typed V_SHIPPING_FEE NUMBER; and approved the entry by pressing Enter.
  4. Then you typed BEGIN and pressed Enter to start the main block of the procedure.
  5. Next, you typed SELECT TOTAL_SALE_AMOUNT, and pressed Enter. This was the first line of a query.
  6. You continued to define another part of the query by typing V_TOTAL_SALE, V_TOTAL_ITEM, V_TAX and pressing Enter.
  7. You finished the query by typing SALES_ID = I_SALES_ID; and pressing Enter.
  8. Next, you created a calculation for the shipping fee by typing V_SHIPPING_FEE := V_TOTAL_ITEM * .10; and pressing Enter.
  9. You finished the second calculation for the total sales by typing V_TOTAL_ITEM + V_SHIPPING_FEE + V_TAX; and pressing Enter.
  10. You create the beginning of an update command by typing UPDATE CUSTOMER_SALE and pressing Enter.
  11. Then you continue by defining the first column to be updated by typing SET TOTAL_SALE_AMOUNT = V_TOTAL_SALE, and pressing Enter.
  12. The rest of the UPDATE command was added for you. Now, you type END; and press Enter to complete the body of the procedure.
  13. To execute your block, you typed / at the SQL4 prompt and pressed Enter.
  14. SQL*Plus then displayed the result of the compilation of your block.
  15. Next, you executed the procedure by typing EXECUTE SHIPPING_FEE(107); and pressing Enter. You see the results of the command in the final screen.

The complete text of the procedure is: 
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;
BEGIN

-- query the table now.

SELECT TOTAL_SALE_AMOUNT,
TOTAL_ITEM_AMOUNT, TAX_AMOUNT
INTO
V_TOTAL_SALE,V_TOTAL_ITEM, V_TAX
FROM CUSTOMER_SALE
WHERE SALES_ID = I_SALES_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
WHERE SALES_ID = I_SALES_ID;
END;
END;