Ramoi_Hammond_Chapter_Five_HandsOn.pdf - Ramoi Hammond Database Development II Chapter Five Hands-On-Assignments Part I Assignment 5-1 Creating a

Ramoi_Hammond_Chapter_Five_HandsOn.pdf - Ramoi Hammond...

This preview shows page 1 - 5 out of 11 pages.

Ramoi Hammond Database Development II 1 | P a g e Chapter 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;
Image of page 1
Ramoi Hammond Database Development II 2 | P a g e 2. 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;
Image of page 2
Ramoi Hammond Database Development II 3 | P a g e 4. Verify that the update was successful by querying the table with: SELECT * FROM bb_product;.
Image of page 3
Ramoi Hammond Database Development II 4 | P a g e Assignment 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.
Image of page 4
Image of page 5

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture