product_id = p.product_id
WHERE sc.cart_id = inCartId AND sc.buy_now;
END$$
The shopping_cart_get_products stored procedure returns the items in the shopping cart mentioned
by the inCartId parameter. Because the shopping_cart table stores the product_id for each product
it stores, you need to join the shopping_cart and product tables to get the information you need.
Note that some of the items can have discounted prices. When an item has a discounted price (which happens
when its discounted_price value is different from 0), then its discounted price should be used for
calculations. Otherwise, its list price should be used. The following expression returns discounted_price
if different from 0; otherwise, it returns price.
COALESCE(NULLIF(p.discounted_price, 0), p.price)
?– Note This is the first time you??™ve worked with the COALESCE and NULLIF conditional expressions, so we??™ll
now explain what they do. COALESCE can receive any number of parameters, and it returns the first one that
is not NULL. NULLIF receives two parameters and returns NULL if they??™re equal; otherwise, it returns the first
of the parameters. In our case, we use NULLIF to test whether the p.discounted_price is 0; if this condition
is true, NULLIF returns false, and the COALESCE function will return p.price. If p.discounted_price
is different from 0, the whole expression returns p.
Pages:
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480