CPSC-110 Spring 2009
Lab 8
Thursday April 2, 2009
Professor Heidi Ellis

Be sure that the professor or TA checks off your work before leaving!!!

Objectives:

In this lab we will write and execute stored procedures in MySQL that take a parameter. We will once again be working with our police database.

An INT Parameter

Since one of the advantages of stored procedures is reuse, how can we take advantage of this reuse? Lets look again at the below35 function which returns the names of all people in the incident table that are under the age of 35. Below is the code. Note that the statements for changing the delimiter and dropping previous versions of the procedure have been omitted for brevity. 
create procedure under35()
begin
  select name 
  from incident 
  where age < 35;
end
What if we wanted to retrieve the names of all people in the incident table that are under 40? Under 25? Under 36? One option would be to write procedures for each age. This would result in a series of similar procedures such as: 
create procedure under25()
begin
  select name 
  from incident 
  where age < 25;
end 

create procedure under45()
begin
  select name 
  from incident 
  where age < 45;
end
But this would result in lots and lots of procedures with very similar code. Lets look at how we can be more efficient by looking at the similarities among the procedures. Exactly what are the two things that must change between different versions?
  1. The name of the procedure.
  2. The value to which age is compared.
What if we could name the procedure using a single name, say belowAge and had a way to "pass" a value into the procedure and let the system compare the age against this value? This would work like unknown values in algebra for example: x = y + 10 where the variable y could hold any number and the variable x would be set to the result of adding 10 to whatever value is held in y.

It turns out that we can do something similar by using a parameter in the procedure heading. A parameter is a variable which can hold values provided during the procedure call and provide (pass) those values to the procedure. The procedure uses the values assigned to its parameters to perform whatever task the procedure is supposed to accomplish. Procedures may have zero or more parameters. The under35 procedure has zero parameters.

There are several rules by which parameters must abide: We can define a belowAge procedure with a single parameter a that is of type INT as: 
create procedure belowAge(a INT)
begin
 select name 
 from incident 
 where age < a; 
end
We can now call the belowAge procedure with a variety of different ages that we would like the system to process and return the names of those people in the incident table whose age is below the number: 
call belowAge(25)
would return a list of the names of all people in the incident table with ages less than 25. Similarly: 
call belowAge(35)
and 
call belowAge(45)
would return a list of the names of all people in the incident table with ages less than 35 and 45 respectively.

The Mode of a Paramater

A parameter may have one of three modes which describe how it is used within the procedure: Pair Programming:
  1. Locate your partner and introduce yourself:
    2. Abby AldermanAndrea Titone 
    Cory DiBenedettoRichard Curtin 
    Briggs ElwellElise Galbo 
    Nikki LustigBrooke HaynesWhitney Merrill
    Ryan PiacentiniBrenna Fox 
    Matt VibertShanice Smith 
    Robert PerrymanAlyssa Cockrell 
    Ben McDanielPreston Maddock 
  2. Select one person's account in which to work.
  3. Select one person to start as "driver". Change driver for each new procedure created.
  4. Using KWrite (or another text editor), create a new file called lab8.txt. Put both team members' names in the document.
  5. Copy the procedure code for each procedure created into the lab8.txt file.
  6. Email the completed file to the partner in whose account you were not working so that they have a copy.
  7. Print the lab8.txt file and hand it in at the end of lab.
 Define your own belowAge procedure as above and call it using different parameters. What happens if you call belowAge and pass it a string such as "ff"?

A String Parameter

Now lets look at how we would retrieve the incident ID and name of a person named "Smith". The SQL code for the query (not procedure code) to accomplish this is: 
 
select incident_id, name 
from incident 
where name like "%Smith%";
The retrieveSmith procedure would look like: 
create procedure retrieveSmith()
begin
  select incident_id, name 
  from incident 
  where name like "%Smith%"; 
end
And we would call the retrieveSmith procedure as: 
call retrieveSmith()
What do we need to change to make the procedure work on a variety of names? We will need to give the procedure a more general name and provide a parameter that takes the name to be retrieved. A retrieveName procedure would look like: 
create procedure retrieveName(n VARCHAR(30))
begin
   select incident_id, name 
   from incident 
   where name like n;
end
Note that since we are passing string data, we must enclose the name we are searching for in double quotes. Also note that we are using the like statement to compare for the name. If you want to retrieve partial matches, the call to the retrieveName must include the use of the percent sign (%): 
call retrieveName("%Smith%")
Define your own retrieveName procedure as above and call it using different parameters.

Try it on your own

Now write stored procedures for as many of the items below as you have time for. Copy the procedures into the lab8.txt file and hand in at the end of class.
  1. Create a procedure called retrieveByInfraction that returns all of the names of people who have committed a particular infraction based on the infraction ID.
  2. Create a procedure called retrieveByFine that returns all of the names and ages of people who have received a fine greater than some value.
  3. Create a procedure called retrieveByAgeFine that returns all of the names and addresses of people that are less than a provided age and have committed a particular infraction. Note that for this procedure, you will need to define two parameters. The parameter definitions should be separated by a comma: (a INT, i VARCHAR(5))
  4. Create a procedure called retrieveByDateandTime that returns the names and ages of all people who have received an infraction after a specified date with a fine between an upper and lower bound. A call to this procedure would look like: 
      call retrieveByDateandFine("2006-09-01", 100, 200)

Conditionals

There are many cases where we want to execute part or all of a procedure conditionally, where you want to do something only if some condition is true or false. This is known as conditional execution since some part of the code is only executed if some condition is true. The IF statement lets you test the truth of an expression contained within parentheses. Lets look again at the belowAge procedure that takes a single parameter: 
create procedure belowAge(a INT)
begin
 select name 
 from incident 
 where age < a;
end
What if we only want to do the select statement if the age is a positive number? Lets add an if statement to check that a is greater than zero before we execute the procedure. 
create procedure underAge(a TINYINT(3))
begin
  if (a > 0)
  then select name 
       from incident 
       where age > a;
  end if;   -- Don't forget the semicolon at the end of "end if"
end
Note: Create the underAge procedure as above. Execute the procedure several times with different data values to test both positive ages and negative ages.

Now, in addition to the functionality above, we would like to print out an error statement if the age is negative or zero? This is the case where the conditional evaluates to false and is known as the else statement. 
create procedure underAge(a TINYINT(3))
begin
  if (a > 0)
  then select name 
       from incident 
	   where age > a;                                   -- Note semicolons on both 
  else select "Error: age must be a positive number.";  -- "then" and "else" statements
  end if;
end
Modify the underAge procedure as above. Execute several times with different data values to test both positive ages and negative ages.

Development Hints for Stored Procedures

Try it on your own

Now write stored procedures for the items below. Copy the procedures into the lab8.txt file and hand in at the end of class.
  1. Create or modify a procedure called retrieveByFine that returns all of the names and ages of people who have received a fine greater than some value. Use a conditional to only retrieve the names and ages if the value of the fine is positive.
  2. Create or modify a procedure called retrieveByAgeFine that returns all of the names and addresses of people that are less than a provided age and have committed a particular infraction. Note that for this procedure, you will need to define two parameters. The parameter definitions should be separated by a comma: (a TINYINT, i VARCHAR(5)). Check that the age is positive and print out an error message if the age is not positive.
  3. Modify the retrieveByAgeFine procedure to check that the age is greater than 0 and less than 100. This will require a combination conditional statement that looks like: ((a > 0) and (a < 100)). Modify the error message accordingly.
  4. Create a procedure called retrieveFinesBetween that returns all of the names of people who have fines between two values. Include a conditional that checks that both fine values are greater than zero and also that the second fine value is greater than the first. If any of these conditions are not true, then print an error message.
You're done!! Be sure to have the professor or TA check off your work before you leave!!