Exercise: Adding the Stored Procedures
1. Use phpMyAdmin to execute and create the stored procedures described in the following steps, and don??™t
forget to set $$ as the delimiter before executing the code.
2. Execute the following code, which creates the catalog_update_product stored procedure to your
tshirtshop database. The catalog_update_product stored procedure updates the details of
a product using the data received through the inProductId, inName, inDescription, inPrice, and
inDiscountedPrice input parameters.
-- Create catalog_update_product stored procedure
CREATE PROCEDURE catalog_update_product(IN inProductId INT,
IN inName VARCHAR(100), IN inDescription VARCHAR(1000),
IN inPrice DECIMAL(10, 2), IN inDiscountedPrice DECIMAL(10, 2))
BEGIN
UPDATE product
SET name = inName, description = inDescription, price = inPrice,
discounted_price = inDiscountedPrice
WHERE product_id = inProductId;
END$$
3. Execute the following code, which creates the catalog_delete_product stored procedure to your
tshirtshop database. The catalog_delete_product stored procedure completely removes a product
from the catalog by deleting its entries in the product_attribute, product_category, and product
tables.
-- Create catalog_delete_product stored procedure
CREATE PROCEDURE catalog_delete_product(IN inProductId INT)
BEGIN
DELETE FROM product_attribute WHERE product_id = inProductId;
DELETE FROM product_category WHERE product_id = inProductId;
DELETE FROM product WHERE product_id = inProductId;
END$$
4.
Pages:
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456