After a shopper completes an order, a procedure is called to update the followin

Question

After a shopper completes an order, a procedure is called to update the following columns in the Basket table: orderplaced, subtotal, shipping, tax and total. The value 1 entered in the ordereplaced column indicates that the shopper has completed an order. Inputs to the procedure are the basket id and amounts for the subtotal, shipping,, tax, and total. 1. in sql , create a procedure named basket_confirm_sp that accepts the input values specified in the preceding description. Keep in mind that you're modifying an existing row of the bb_basket table in this procedure. 4.Call the procedure with the following parameter values: 17, 64.80, 8.00, 1.94, 74.74. As mentioned, these values represent the basket ID and the amounts for the subtotal, shipping, tax and total.

Explanation / Answer

CREATE OR REPLACE PROCEDURE case_update_sp (p_idbasket IN NUMBER) IS lv_subtotal_num bb_basket.subtotal%TYPE; lv_shipping_num bb_basket.shipping%TYPE; lv_taxamt_num bb_basket.tax%TYPE; lv_total_num bb_basket.total%TYPE; BEGIN SELECT case_subtotal_sf(p_idbasket) INTO lv_subtotal_num FROM bb_basket WHERE idbasket = p_idbasket; SELECT case_shipping_sf(p_idbasket), case_taxamt_sf(p_idbasket, lv_subtotal_num) INTO lv_shipping_num, lv_taxamt_num FROM bb_basket WHERE idbasket = p_idbasket; lv_total_num := lv_subtotal_num + lv_shipping_num + lv_taxamt_num; UPDATE bb_basket SET orderplaced = 1, subtotal = lv_subtotal_num, shipping = lv_shipping_num , tax = lv_taxamt_num, total = lv_total_num WHERE idbasket = p_idbasket; COMMIT; END case_update_sp; / order of values may not be correct..but hope ths helps :) Rate me points if it helps..thank you :)

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.