You can see this data in Figure 14-6.
-- Create orders_get_order_info stored procedure
CREATE PROCEDURE orders_get_order_info(IN inOrderId INT)
CHAPTER 14 ?– ACCEPTING CUSTOMER ORDERS 453
BEGIN
SELECT order_id, total_amount, created_on, shipped_on, status,
comments, customer_name, shipping_address, customer_email
FROM orders
WHERE order_id = inOrderId;
END$$
3. Execute the following code, which creates the orders_get_order_details stored procedure in your
tshirtshop database. This procedure returns the products that belong to a particular order. This data is
used to populate the table containing the order details, situated at the bottom of the page.
-- Create orders_get_order_details stored procedure
CREATE PROCEDURE orders_get_order_details(IN inOrderId INT)
BEGIN
SELECT order_id, product_id, attributes, product_name,
quantity, unit_cost, (quantity * unit_cost) AS subtotal
FROM order_detail
WHERE order_id = inOrderId;
END$$
4. Execute this code, which creates the stored procedure orders_update_order stored procedure. This is
called when the administrator updates an order in edit mode; it updates the details of an order.
-- Create orders_update_order stored procedure
CREATE PROCEDURE orders_update_order(IN inOrderId INT, IN inStatus INT,
IN inComments VARCHAR(255), IN inCustomerName VARCHAR(50),
IN inShippingAddress VARCHAR(255), IN inCustomerEmail VARCHAR(50))
BEGIN
DECLARE currentStatus INT;
SELECT status
FROM orders
WHERE order_id = inOrderId
INTO currentStatus;
IF inStatus != currentStatus AND (inStatus = 0 OR inStatus = 1) THEN
UPDATE orders SET shipped_on = NULL WHERE order_id = inOrderId;
ELSEIF inStatus != currentStatus AND inStatus = 2 THEN
UPDATE orders SET shipped_on = NOW() WHERE order_id = inOrderId;
END IF;
UPDATE orders
SET status = inStatus, comments = inComments,
customer_name = inCustomerName,
shipping_address = inShippingAddress,
customer_email = inCustomerEmail
WHERE order_id = inOrderId;
END$$
CHAPTER 14 ?– ACCEPTING CUSTOMER ORDERS 454
5.
Pages:
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573