100%(6)6 out of 6 people found this document helpful
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
(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)ISBEGININSERT 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;/BEGINprod_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 OrderFollow 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.
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)ISBEGIN SELECT (taxrate * p_subtotal)INTO p_taxamountFROM bb_taxWHERE state = p_state;EXCEPTIONWHEN no_data_found THENp_taxamount := 0;END;/DECLARElv_tax_num NUMBER;BEGINtax_cost_sp ('VA', '100', lv_tax_num);DBMS_OUTPUT.PUT_LINE('Tax Amount = ' || lv_tax_num);END;