Address the input values or parameters in the same order as in the preceding

Address the input values or parameters in the same

This preview shows page 2 - 6 out of 8 pages.

image filename, price, and active status. Address the input values or parameters in the same order as in the preceding sentence.2.Call the procedure with these parameter values: (‘Roasted Blend’, ‘Well-balanced mix of roasted beans, a medium body’, ‘roasted. jpg’, 9.50, 1).3.Check whether the update was successful by querying the BB_PRODUCT table.The Primary Key is the idproduct. So, I use sequencename.nextval in order to insert a new IDCREATE OR REPLACE PROCEDURE prod_add_sp
Image of page 2
(p_name IN bb_product.productname%TYPE, p_description IN bb_product.description%TYPE, p_image IN bb_product.productimage%TYPE, p_price IN bb_product.price%TYPE, p_active IN bb_product.active%TYPE) IS BEGIN INSERT INTO bb_product (idproduct, productname, description, productimage, price, active) VALUES (bb_prodid_seq.nextval, p_name, p_description, p_image, p_price, p_active); COMMIT; END; / BEGIN prod_add_sp('Roasted Blend', 'Well-balanced mix of roasted beans, a medium body', 'roasted.jpg', 9.50, 1); END; / SELECT * FROM bb_product; Assignment 5-3: Calculating the Tax on an Order Follow these steps to create a procedure for calculating the tax on an order. The BB_TAX table contains states that require submitting taxes for Internet sales. If the state isn't listed in the table, no tax should be assessed on the order. The shopper's state and basket subtotal are the inputs to the procedure, and the tax amount should be returned.
Image of page 3
1.In SOL Developer, create a procedure named TAX_COST_SP. Remember that the state and subtotal values are inputs to the procedure, which should return the tax amount. Review the BB_TAX table, which contains the tax rate for each applicable state.Call the procedure with the values VA for the state and $100 for the subtotal. Display the tax amount theprocedure returns. (It should be $4.50.) CREATE OR REPLACE PROCEDURE tax_cost_sp (p_state IN bb_tax.state%TYPE, p_subtotal IN NUMBER, p_taxamount OUT NUMBER) IS BEGIN SELECT (taxrate * p_subtotal) INTO p_taxamount FROM bb_tax WHERE state = p_state; EXCEPTION WHEN no_data_found THEN p_taxamount := 0; END; / DECLARE lv_tax_num NUMBER; BEGIN tax_cost_sp ('VA', '100', lv_tax_num); DBMS_OUTPUT.PUT_LINE('Tax Amount = ' || lv_tax_num); END;
Image of page 4
Image of page 5
Image of page 6

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture