Execute this code, which creates the catalog_set_product_display_option stored procedure in your
tshirtshop database:
-- Create catalog_set_product_display_option stored procedure
CREATE PROCEDURE catalog_set_product_display_option(
IN inProductId INT, IN inDisplay SMALLINT)
BEGIN
UPDATE product SET display = inDisplay WHERE product_id = inProductId;
END$$
9. Execute the following code, which creates the catalog_assign_product_to_category stored procedure
in your tshirtshop database. The catalog_assign_product_to_category stored procedure
associates a product with a category by adding a (product_id, category_id) value pair into the product_
category table.
-- Create catalog_assign_product_to_category stored procedure
CREATE PROCEDURE catalog_assign_product_to_category(
IN inProductId INT, IN inCategoryId INT)
BEGIN
INSERT INTO product_category (product_id, category_id)
VALUES (inProductId, inCategoryId);
END$$
10. Execute the following code, which creates the catalog_assign_product_to_category stored procedure
in your tshirtshop database. The catalog_move_product_to_category stored procedure
removes a product from a category and places it in another one.
-- Create catalog_move_product_to_category stored procedure
CREATE PROCEDURE catalog_move_product_to_category(IN inProductId INT,
IN inSourceCategoryId INT, IN inTargetCategoryId INT)
BEGIN
UPDATE product_category
SET category_id = inTargetCategoryId
WHERE product_id = inProductId
AND category_id = inSourceCategoryId;
END$$
CHAPTER 11 ?– CATALOG ADMINISTRATION: PRODUCTS AND ATTRIBUTES 349
11.
Pages:
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459