Execute the following code, which creates the catalog_get_product_info stored procedure in your
tshirtshop database. The catalog_get_product_info stored procedure retrieves the product name,
description, price, discounted price, image, the second image, thumbnail, and display option for the product
identified by the product ID (inProductId).
-- Create catalog_get_product_info stored procedure
CREATE PROCEDURE catalog_get_product_info(IN inProductId INT)
BEGIN
SELECT product_id, name, description, price, discounted_price,
image, image_2, thumbnail, display
FROM product
WHERE product_id = inProductId;
END$$
7. Execute this code, which creates the catalog_get_categories_for_product stored procedure in
your tshirtshop database. The catalog_get_categories_for_product stored procedure returns
a list of the categories that belong to the specified product. Only their IDs and names are returned, because
this is the only information we??™re interested in.
CHAPTER 11 ?– CATALOG ADMINISTRATION: PRODUCTS AND ATTRIBUTES 348
-- Create catalog_get_categories_for_product stored procedure
CREATE PROCEDURE catalog_get_categories_for_product(IN inProductId INT)
BEGIN
SELECT c.category_id, c.department_id, c.name
FROM category c
JOIN product_category pc
ON c.category_id = pc.category_id
WHERE pc.product_id = inProductId
ORDER BY category_id;
END$$
8.
Pages:
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458