Execute the following code, which adds the column tax_id and a new index to the orders table from the
tshirtshop database:
-- Adding a new field named tax_id to orders table
ALTER TABLE orders ADD COLUMN tax_id INT;
-- Adding a new index to orders table
CREATE INDEX `idx_orders_tax_id` ON `orders` (`tax_id`);
8. Delete the current shopping_cart_create_order stored procedure, and create a new one that takes
into consideration the new changes made to the orders table (don??™t forget to set the delimiter to $$):
-- Drop shopping_cart_create_order stored procedure
DROP PROCEDURE shopping_cart_create_order$$
-- Create shopping_cart_create_order stored procedure
CREATE PROCEDURE shopping_cart_create_order(IN inCartId CHAR(32),
IN inCustomerId INT, IN inShippingId INT, IN inTaxId INT)
BEGIN
DECLARE orderId INT;
-- Insert a new record into orders and obtain the new order ID
INSERT INTO orders (created_on, customer_id, shipping_id, tax_id) VALUES
(NOW(), inCustomerId, inShippingId, inTaxId);
-- Obtain the new Order ID
SELECT LAST_INSERT_ID() INTO orderId;
-- Insert order details in order_detail table
INSERT INTO order_detail (order_id, product_id, attributes,
product_name, quantity, unit_cost)
SELECT orderId, p.product_id, sc.attributes, p.name, sc.quantity,
COALESCE(NULLIF(p.discounted_price, 0), p.price) AS unit_cost
FROM shopping_cart sc
INNER JOIN product p
ON sc.
Pages:
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675