Address the input values or parameters in the same

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 ID
CREATE 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)
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.

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 the
procedure 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;