Objectives:

• Understand and implement a simple stored procedure.

Stored Procedures

1. Prompt the user to enter the amount to convert.
2. Prompt the user to enter the "from" currency and the "to" currency.
3. Read the amount and currencies into the program.
4. Convert the amount of the "from" currency into the "to" currency using a formula similar to: tocurrency = fromcurrency * conversionfactor.
5. Display the resulting conversion.

Why do we need stored procedures?

• Reuse: Stored procedures allow the same functionality to be available to a variety of users and programs that access a database. This functionality is available via a single procedure call rather than having to retype the SQL every time.
• Improved performance: Stored procedures can be pre-compiled meaning that they will execute faster when they are called as compared to the equivalent SQL code.
• Maintainability: A stored procedure that can be called by multiple users provides a single point of change if a change in the functionality is necessary.
• Security: Using regular SQL, security is set on a user basis and each user would need to be granted permission to access the database. This becomes difficult when new users are added or when changing the permissions of existing users. Stored procedures can be constructed such that permissions are set up for the individual stored procedure.

Creating and Using a Stored Procedure

1. Creating the stored procedure: In this first step, the actual code for the procedure is defined. In other words, you will write the statements to be executed in order to carry out the procedure. The process of creating and editing a stored procedure happens once. Once a procedure has been correctly defined, it typically does not need to be modified afterwards.
2. Running or executing the stored procedure: Once a procedure has been created, it may be run or executed any number of times.

What Does a Stored Procedure Look Like?

CREATE PROCEDURE SayHello() 
select "Hello World";

DELIMITER $$                                 -- Change the delimiter so that we can 
                                             -- use semicolons within our procedure code.
DROP PROCEDURE IF EXISTS `police`.`temp` $$  -- If the procedure exists already, remove it. 
CREATE PROCEDURE `police`.`SayHello` ()      -- Creates the procedure
BEGIN                                        -- Begin execution here
   select "Hello World";                     -- Executable part of the procedure.
END $$                                       -- End execution here
DELIMITER ;

First Stored Procedure

Step 1: Creating a Stored Procedure

1. Open the MySQL query browser.
2. Select script/Create Stored Procedure/Function menu options. A window should pop up that looks similar to the following:
   
   
   
3. Enter the name of the procedure that you would like to create in the text box (temp) and press the Create Procedure button.
4. This will open a script window in the main query window. The script window different from the query window we have used up to this point and is identifiable because it has an icon with text lines. The query window has a lightening bolt. You MUST enter your code using a script window, not a query window:




5. Correct entry of a procedure name results in a template being shown:

   DELIMITER $$
   
   DROP PROCEDURE IF EXISTS `police`.`temp` $$
   CREATE PROCEDURE `police`.`temp` ()
   BEGIN
   
   END $$
   
   DELIMITER ;
   

   Remember that the DELIMITER statements are required and that the DROP PROCEDURE statement deletes any previous versions of the procedure.
6. On the blank line between the BEGIN and END statements write: select "Your name"; replacing "Your name" with your name.
7. Press the Execute button or select the script/execute menu to check the stored procedure code for any errors. If no errors occur you should see the words "Query Executed" in the lower left corner of the Query Browser screen. This means that you are ready to call your stored procedure.
8. When you have successfully created a new stored procedure, you will see the name of the stored procedure listed within the definition of the database on which the procedure is defined. Stored procedures are identified by a blue box containing gears (as opposed to the blue tables used to designate relations). Below is a picture of the police database showing several procedures:
   
   
   

Step 2: Running a Stored Procedure

1. To run the stored procedure, open a new query window and use the call key word followed by the name of the procedure. Make sure that there are no blank spaces between the end of the procedure name and the open and close paretheses. Note that the open and close parentheses are required. The presence of the parentheses tells MySQL that you are calling a procedure. A call to the temp procedure looks like:
   
   
   
2. Don't forget that the creation of the stored procedure must occur in a script window (not a query window) while the execution of the query must occur in a query window.
3. Don't forget to include the open and closed parens at the end of the call.
4. If you would like to edit a stored procedure, right click over the procedure name and select Edit in the query browser.

Second Stored Procedure

select name 
from incident 
where age < 35;

DELIMITER $$

DROP PROCEDURE IF EXISTS `police`.`under35` $$
CREATE PROCEDURE `under35`()
BEGIN
    select name 
    from incident 
    where age < 35;
END $$

DELIMITER ;

Third Stored Procedure

  select avg(fine)
  from incident;

Try it on your own

1. Create a procedure called retrieveSmith that returns all of the names of people in the incident table who have "Smith" in their names.
2. Create a procedure called infraction3 that returns the names and ages of everyone in the incident table with infraction ID 3.
3. Create a procedure called totalFines that returns the sum of all fines in the incident table.
4. Create a procedure called likelyCars that returns the make and model of the cars that have been involved in an incident with a fine greater than or equal to $300.
5. Create a procedure called likelyDrivers that retrieves the names and ages of people who drive a car younger than 2006.
6. Create a procedure called cleanOwners that retrieves the owners of vehicles that have not been involved in a traffic incident. Hint: You'll need to use a sub-query.