product_id = p.product_id
WHERE sc.cart_id = inCartId AND sc.buy_now;
-- Save the order's total amount
UPDATE orders
SET total_amount = (SELECT SUM(unit_cost * quantity)
FROM order_detail
CHAPTER 17 ?– STORING CUSTOMER ORDERS 562
WHERE order_id = orderId)
WHERE order_id = orderId;
-- Clear the shopping cart
CALL shopping_cart_empty(inCartId);
-- Return the Order ID
SELECT orderId;
END$$
9. Modify the orders_get_order_info stored procedure by deleting the old version and creating a new one
(don??™t forget to set the delimiter to $$):
-- Drop orders_get_order_info stored procedure
DROP PROCEDURE orders_get_order_info$$
-- Create orders_get_order_info stored procedure
CREATE PROCEDURE orders_get_order_info(IN inOrderId INT)
BEGIN
SELECT o.order_id, o.total_amount, o.created_on, o.shipped_on,
o.status, o.comments, o.customer_id, o.auth_code,
o.reference, o.shipping_id, s.shipping_type, s.shipping_cost,
o.tax_id, t.tax_type, t.tax_percentage
FROM orders o
INNER JOIN tax t
ON t.tax_id = o.tax_id
INNER JOIN shipping s
ON s.shipping_id = o.shipping_id
WHERE o.order_id = inOrderId;
END$$
10. Execute this code, which adds the orders_get_shipping_info stored procedure to the tshirtshop
database:
-- Create orders_get_shipping_info stored procedure
CREATE PROCEDURE orders_get_shipping_info(IN inShippingRegionId INT)
BEGIN
SELECT shipping_id, shipping_type, shipping_cost, shipping_region_id
FROM shipping
WHERE shipping_region_id = inShippingRegionId;
END$$
Modifying the Business Tier
To work with the new database tables and stored procedures, we need to make several changes
to business/shopping_cart.
Pages:
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676