Workshop 7

This workshop consists of writing five simple stored procedures that will practice the following aspects:

  1. A single-statement stored procedure with no parameters
  2. A stored procedure with one IN parameter
  3. A stored procedure with one OUT parameter
  4. A stored procedure with one INOUT parameter
  5. A stored procedure that uses a cursor
WARNING: Do NOT submit an RTF file - you must submit a plain text file ONLY

I strongly suggest that you use MySQL Workbench to develop these procedures, rather than the command line client. The final problem is likely to take considerable time unless you pay very careful attention to the syntax (which is admittedly a bit weird).

Please submit a single file called procedures.sql that will create all of the five procedures. You must ensure that this file runs correctly - if necessary, simply omit any procedures that generate syntax errors, or none of your other procedures can be tested.

A script will load and call each procedure in turn, so they must also have exactly the correct names, as described below.

All of these will be run on the Classic Models database.

No parameters

Write a stored procedure totalPaid that calculates the total value of the payments in the payments table. (This is just a single SELECT statement wrapped up as a stored routine.)

One IN parameter

Write a stored procedure amountPaid(IN custNum INT) that calculates the total value of the payments made by the customer with customer number custNum.

For example, CALL amountPaid(103) should produce 23314.36 (how exactly this number is presented will depend on whether you are using the commandline client or the workbench).

One OUT parameter

Write a stored procedure toShip(OUT numToShip INT) that will assign the number of unshipped orders into the variable numToShip.

If this procedure is called with the command
CALL toShip (@num);
then the subsequent call
SELECT @num;
should return the number of unshipped orders.

Remember that you will need to use SELECT ... INTO in your stored procedure.

One INOUT parameter

A parameter can be declared to be INOUT if it is both an IN parameter and an OUT parameter.

Write a stored procedure nextCustomer(INOUT custNum INT) where the variable custNum is updated to contain the next customer number in numerical order.

For example, if the variable contains 112, then after the procedure is called, it should contain 114 (because there is no customer number 113). 

SET @custNum := 112;
CALL nextCustomer(@custNum);
SELECT @custNum;
+----------+
| @custNum |
+----------+
|      114 |
+----------+

Do not worry about making sure it works sensibly if the input is actually the highest customer number.

Using a cursor

We want to create a table in the database called cumulativePayments which will contain three columns. The first column is the date of a payment, the second column is the amount of the payment and the third column is the cumulative total amount of all the payments up to (and including) that date.

For example, from the payments table, we see that the earliest three payments are the following:

+-------------+----------+
| paymentDate | amount   |
+-------------+----------+
| 2003-01-16  | 10223.83 |
| 2003-01-28  | 10549.01 |
| 2003-01-30  |  5494.78 |
+-------------+----------+

So the first three rows of the cumulativePayments table should be

+------------+-----------+--------------------+
| date       | amount    | cumulativeAmount   |
+------------+-----------+--------------------+
| 2003-01-16 |  10223.83 |           10223.83 |
| 2003-01-28 |  10549.01 |           20772.84 |
| 2003-01-30 |   5494.78 |           26267.62 |

Write a stored routine cumulativePayments() that does not need any parameters, to create and populate this table. The following general outline of how to approach this problem may be useful:

  1. Your stored routine should start by creating the cumulativePayments table, using the CREATE TABLE command, dropping any existing table with the same name (using DROP TABLE IF EXISTS to avoid errors).
  2. You should then create a cursor for the query that lists all the payments made in increasing order of payment date.
  3. You will need five local variables, two to process the rows (numRows and numDone), one to keep track of the cumulative total of all the payments so far, and two that will be used in each iteration of the loop to hold that values for that particular row. You will need to DECLARE each variable and intialize it to the appropriate value.
  4. You should then use the cursor facilities to process each of the rows, one at a time. Each of the rows processed will reflect one new payment with a certain date and amount. The routine should then update the cumulative total and insert a new row into the cumulativePayments table.

MySQL workbench will help a little bit with the syntax, but you will need to carefully follow the example in lectures and keep the documentation handy.

Written with StackEdit.