75%(4)3 out of 4 people found this document helpful
This preview shows page 1 - 5 out of 11 pages.
Ramoi Hammond Database Development II 1 | P a g eChapter Five Hands-On-Assignments Part I Assignment 5-1: Creating a Procedure Use these steps to create a procedure that allows a company employee to make corrections to a product's assigned name. Review the BB_PRODUCT table and identify the PRODUCT NAME and PRIMARY KEY columns. The procedure needs two IN parameters to identify the product ID and supply the new description. This procedure needs to perform only a DML action, so no OUT parameters are necessary. 1. In SOL Developer, create the following procedure: CREATE OR REPLACE PROCEDURE prod_name_sp (p_prodid IN bb_product.idproduct%TYPE, p_descrip IN bb_product.description%TYPE) IS BEGIN UPDATE bb_product SET description = p_descrip WHERE idproduct = p_prodid ; COMMIT; END;
Ramoi Hammond Database Development II 2 | P a g e2. Before testing the procedure, verify the current description value for product ID 1 with: SELECT * FROM bb_product; 3. Call the procedure with parameter values of 1 for the product ID and CapressoBar Model #388 for the description. BEGIN PROD_NAME_SP(1, 'CapressoBar Model #388'); END;
Ramoi Hammond Database Development II 3 | P a g e4. Verify that the update was successful by querying the table with: SELECT * FROM bb_product;.
Ramoi Hammond Database Development II 4 | P a g eAssignment 5-2: Using a Procedure with IN Parameters Follow these steps to create a procedure that allows a company employee to add a new product to the database. This procedure needs only IN parameters.