2. Execute this code, which creates the catalog_get_recommendations stored procedure in your
tshirtshop database:
-- Create catalog_get_recommendations stored procedure
CREATE PROCEDURE catalog_get_recommendations(
IN inProductId INT, IN inShortProductDescriptionLength INT)
BEGIN
PREPARE statement FROM
"SELECT od2.product_id, od2.product_name,
CHAPTER 15 ?– PRODUCT RECOMMENDATIONS 468
IF(LENGTH(p.description) <= ?, p.description,
CONCAT(LEFT(p.description, ?), '...')) AS description
FROM order_detail od1
JOIN order_detail od2 ON od1.order_id = od2.order_id
JOIN product p ON od2.product_id = p.product_id
WHERE od1.product_id = ? AND
od2.product_id != ?
GROUP BY od2.product_id
ORDER BY COUNT(od2.product_id) DESC
LIMIT 5";
SET @p1 = inShortProductDescriptionLength;
SET @p2 = inProductId;
EXECUTE statement USING @p1, @p1, @p2, @p2;
END$$
3. Execute the following code, which creates the shopping_cart_get_recommendations stored procedure
in your tshirtshop database:
-- Create shopping_cart_get_recommendations stored procedure
CREATE PROCEDURE shopping_cart_get_recommendations(
IN inCartId CHAR(32), IN inShortProductDescriptionLength INT)
BEGIN
PREPARE statement FROM
"-- Returns the products that exist in a list of orders
SELECT od1.product_id, od1.product_name,
IF(LENGTH(p.description) <= ?, p.description,
CONCAT(LEFT(p.description, ?), '.
Pages:
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589