Exercise: Implementing the Orders Administration Page
1. We start by creating the necessary stored procedures. Use phpMyAdmin to create the stored procedures
described in the following steps. Don??™t forget to set the $$ delimiter before executing the code of eachstep.
2. Execute the following code, which creates the orders_get_most_recent_orders stored procedure in
your tshirtshop database. This procedure returns the most recent orders. The SELECT statement uses
the LIMIT clause to limit the number of returned rows to the value of the inHowMany input parameter. The
ORDER BY DESC clause, used to sort the results in descending order, is set so the most recent orders will be
listed first.
-- Create orders_get_most_recent_orders stored procedure
CREATE PROCEDURE orders_get_most_recent_orders(IN inHowMany INT)
BEGIN
PREPARE statement FROM
"SELECT order_id, total_amount, created_on,
shipped_on, status, customer_name
FROM orders
ORDER BY created_on DESC
LIMIT ?";
SET @p1 = inHowMany;
EXECUTE statement USING @p1;
END$$
3. Execute the following code to create the orders_get_orders_between_dates stored procedure. This
procedure returns the orders whose creation date is between inStartDate and inEndDate. The results
are sorted descending by creation date.
-- Create orders_get_orders_between_dates stored procedure
CREATE PROCEDURE orders_get_orders_between_dates(
IN inStartDate DATETIME, IN inEndDate DATETIME)
BEGIN
SELECT order_id, total_amount, created_on,
shipped_on, status, customer_name
FROM orders
WHERE created_on >= inStartDate AND created_on <= inEndDate
ORDER BY created_on DESC;
END$$
4.
Pages:
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565