discounted_price.
6. Execute the following code, which creates the shopping_cart_get_saved_products stored procedure
in your tshirtshop database:
-- Create shopping_cart_get_saved_products stored procedure
CREATE PROCEDURE shopping_cart_get_saved_products(IN inCartId CHAR(32))
BEGIN
SELECT sc.item_id, p.name, sc.attributes,
COALESCE(NULLIF(p.discounted_price, 0), p.price) AS price
FROM shopping_cart sc
INNER JOIN product p
ON sc.product_id = p.product_id
WHERE sc.cart_id = inCartId AND NOT sc.buy_now;
END$$
CHAPTER 12 ?– CREATING YOUR OWN SHOPPING CART 370
The shopping_cart_get_saved_products stored procedure returns the items saved for later in the
shopping cart specified by the inCartId parameter.
7. Execute the following code, which creates the shopping_cart_get_total_amount stored procedure in
your tshirtshop database:
-- Create shopping_cart_get_total_amount stored procedure
CREATE PROCEDURE shopping_cart_get_total_amount(IN inCartId CHAR(32))
BEGIN
SELECT SUM(COALESCE(NULLIF(p.discounted_price, 0), p.price)
* sc.quantity) AS total_amount
FROM shopping_cart sc
INNER JOIN product p
ON sc.product_id = p.product_id
WHERE sc.cart_id = inCartId AND sc.buy_now;
END$$
The shopping_cart_get_total_amount stored procedure returns the total value of the items in the
shopping cart before applicable taxes and shipping charges. This is called when displaying the total amount
for the shopping cart.
Pages:
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481