CPSC-110 Spring 2009
Lab 10
Tuesday, April 15, 2009
Professor Heidi Ellis
Be sure that the professor or TA checks off your work before leaving!!!
Objectives:
- Understand the use of looping statements to process data retrieved from the database in stored procedures.
In this lab we will write and execute stored procedures in MySQL that use looping statements to process data that we have retrieved from the database. We will once again be working with our police database.
Cursors
There are many times in stored procedures when we want to use a SELECT statement to retrieve data and then process that data within the stored procedure. For instance, totaling the fines that are below some given value. (Yes, I understand that this could be done using a simple SQL query, but we will look at how to do this ourselves within a stored procedure as a simple illustration of my point.)
The mechanism used in stored procedures to handle a SELECT statement that returns more than one row is a cursor. A cursor is an object that allows you to access the result set returned by a SELECT statement. A cursor lets the SQL select statement return more than one row and supports looping through that result set. The cursor is used to iterate through the rows in a result set and take action for each row individually. Cursors must be declared in the same location where other variables are declared and the definition of a query contains the SQL query that provides the result set that the cursor operates over. A cursor must be "opened" in order to operate over the result set and the cursor must be closed after the result set is processed. For example, the query
SELECT fine
FROM incidents
results in the result set shown below (when applied to my incidents table):
A cursor defined on this set would start by pointing to the first row, then the next, then the next and so on. The cursor would be defined by the statement:
DECLARE fineCursor CURSOR FOR
select fine from incidents;
This cursor declaration must follow all variable declarations and must contain a select statement. There are three operations that may be performed on a cursor:
Below is an example of a stored procedure which retrieves the first fine from the set of fines using a cursor. Note that the first row is fetched into the variable tempFine.
-- This procedure shows the use of a cursor to retrieve
-- the first row of a result set.
CREATE PROCEDURE `police`.`selectFirstFine` ()
BEGIN
DECLARE tempFine FLOAT; -- Variable to hold a single fine
-- Declare a cursor to iterate through a table. Only retrieves a single column.
DECLARE fineCursor CURSOR FOR
select fine from incidents;
OPEN fineCursor; -- Open the cursor
FETCH fineCursor into tempFine; -- Retrieve first row in table
CLOSE fineCursor; -- Close the cursor
SELECT tempFine; -- Display the first fine.
END $$
Pair Programming:
- Locate your partner and introduce yourself:
| Abby Alderman | Alyssa Cockrell | |
| Cory DiBenedetto | Preston Maddock | |
| Briggs Elwell | Andrea Titone | |
| Whitney Merrill | Nikki Lustig | |
| Ryan Piacentini | Elise Galbo | |
| Matt Vibert | Brooke Haynes | |
| Robert Perryman | Brenna Fox | |
| Ben McDaniel | Shanice Smith | Richard Curtin |
- Select one person's account in which to work.
- Select one person to start as "driver". Change driver for each new procedure created.
- Using KWrite (or another text editor), create a new file called lab10.txt. Put both team members' names in the document.
- Copy the procedure code for each procedure created into the lab10.txt file.
- Email the completed file to the partner in whose account you were not working so that they have a copy.
- Print the lab10.txt file and hand it in at the end of lab.
Try it:
- Implement the tempFine procedure in your police database.
- What happens if you insert a second FETCH statement after the first? What does the procedure display?
- What happens if you insert a third FETCH statement?
- Copy your procedure to your lab10.txt file for hand-in at the end of lab.
Now lets look at how we can combine cursors and loops to iterate through a result set table.
Error Handlers
In order to loop through the result set, we need a way to know when we've reached the end of the result set. We can't use a counter such as i as we don't know the number of rows in the result set and therefore don't know how big to set i. SQL provides a way around this using an error handler. Handlers are a MySQL construct that sets the value of a variable to be 1 when the end of a result set is encountered. Declaring a handler is a two-step process:
- Declared an INT variable to hold the value indicating the end of the result set.
- Declare the variable declared in the previous step as being a handler.
The handler defined below will equal one (the system automatically sets the value to one) when there are no more rows to be processed in a result set operated on by a cursor:
DECLARE noMoreFines INT;
-- Declaration of other variables and cursor in this space
DECLARE CONTINUE HANDLER FOR NOT FOUND SET noMoreFines=1;
Now we can write our program so that it loops and checks the value of the noMoreFines variable as the exit condition of the loop (noMoreFines = 1):
OPEN fineCursor; -- Open the cursor to point to first row in table
fine_loop:LOOP -- Open the loop
FETCH fineCursor into tempFine; -- Retrieve the next fine from the table
IF noMoreFines=1 -- Check if there are any more fines.
THEN LEAVE fine_loop; -- If so, leave the loop
END IF;
END LOOP;
CLOSE fineCursor; -- Close the cursor
Note that this loop doesn't do anything other than step its way through the result set.
Try it:
- Add this loop to your selectFirstFine stored procedure. Don't forget to add the declaration of the handler (noMoreFines). Note that the DECLARE HANDLER statement must come after the declaration of the cursor. What does the procedure display now?
- Copy your procedure to your lab10.txt file for hand-in at the end of lab.
A Full Example
Below is an example of a stored procedure that totals the fines for all fines below a provided value. Note the use of the parameter f which is the "provided value". Also note the use of the if statement to check if the current fine is greater than the provided value and if so, it adds the value to the total of the fines.
-- This procedure returns the total of the fines that are
-- below some provided figure.
CREATE PROCEDURE `totalFinesUnder`(f FLOAT)
BEGIN
DECLARE totalFine FLOAT; -- Total of the fines
DECLARE tempFine FLOAT; -- Variable to hold a single fine
DECLARE noMoreFines INT; -- Variable to check for no more data in the table
-- Declare a cursor to iterate through a table. Only retrieves a single column.
DECLARE fineCursor CURSOR FOR
select fine from incidents;
-- Declare a handler to detect when the last row in the table is reached
DECLARE CONTINUE HANDLER FOR NOT FOUND SET noMoreFines=1;
SET totalFine=0; -- Initialize the total of the fines to zero
OPEN fineCursor; -- Open the cursor to point to first row in table
fine_loop:LOOP -- Open the loop
FETCH fineCursor into tempFine; -- Retrieve the next fine from the table
IF noMoreFines=1 -- Check if there are any more fines.
THEN LEAVE fine_loop; -- If so, leave the loop
END IF;
IF tempFine < f -- Otherwise check to see if the fine is
-- greater than the parameter. If so, add the
-- fine to the total
THEN SET totalFine = totalFine + tempFine;
END IF;
END LOOP;
CLOSE fineCursor; -- Close the cursor
SELECT totalFine; -- Display the total of the fines
END $$
Try it:
- Implement the totalFinesUnder procedure.
- Now modify the procedure to total the fines that are less than some value and greater than some second value. Note that this will require you to add another parameter to the procedure and to modify the if statement.
- Copy your procedure to a text file for hand-in at the end of lab.
Development Hints for Stored Procedures
- If you become confused, you can right click on a procedure and remove it by selecting the Drop Procedure option.
- Existing procedures may be edited by right clicking on a procedure and selecting the Edit Procedure option.
- If possible, write a sample of the SQL code in a query window to make sure that the SQL code works. This will allow you to find problems more easily.
- Code a piece at a time. If you have three steps in your procedure, code the first step and test it by calling the stored procedure.
Try it on your own
Now write a stored procedure that will use a cursor and a handler to calculate the average age of the people in the incidents database. Copy the procedure into a separate file and hand in at the end of class. The steps of what you need to do are as follows:
- Declare variables to hold:
- A single age retrieved from the result set
- The total of the ages
- The number of people in the result set
- The average age
- The variable to check for no more data in the result set
- Declare a cursor to iterate through the incidents table. Note that you only need to retrieve the age column.
- Declare a handler to detect when the last row in the table is reached.
- Initialize the total age and number of people variables to zero.
- Open the cursor.
- Enter a loop to:
- Retrieve the next age from the table
- Check to see if there are any more ages. If not, leave the loop.
- If there are more ages, add the current age to the age total.
- Increment the counter for the number of people by one.
- Close the age cursor.
- Set the average age to be the total of the ages divided by the number of people. (The division operator is the forward slash.)
- Display the average age.
You're done!! Be sure to print and hand in your lab10.txt file before you leave!!