Execute the following code, which creates the catalog_remove_product_from_category stored
procedure in your tshirtshop database. The catalog_remove_product_from_category stored procedure
verifies how many categories the product exists in. If the product exists in more than one category, it
just removes the product from the specified category (ID received as a parameter). If the product is associated
with a single category, it is removed completely from the database.
-- Create catalog_remove_product_from_category stored procedure
CREATE PROCEDURE catalog_remove_product_from_category(
IN inProductId INT, IN inCategoryId INT)
CHAPTER 11 ?– CATALOG ADMINISTRATION: PRODUCTS AND ATTRIBUTES 347
BEGIN
DECLARE productCategoryRowsCount INT;
SELECT count(*)
FROM product_category
WHERE product_id = inProductId
INTO productCategoryRowsCount;
IF productCategoryRowsCount = 1 THEN
CALL catalog_delete_product(inProductId);
SELECT 0;
ELSE
DELETE FROM product_category
WHERE category_id = inCategoryId AND product_id = inProductId;
SELECT 1;
END IF;
END$$
5. Execute the following code, which creates the catalog_get_categories stored procedure in your
tshirtshop database; catalog_get_categories simply returns all the categories from your catalog.
-- Create catalog_get_categories stored procedure
CREATE PROCEDURE catalog_get_categories()
BEGIN
SELECT category_id, name, description
FROM category
ORDER BY category_id;
END$$
6.
Pages:
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457