Creating MySQL Stored Routines for Non-DBAs – Part Three

In part one of this three part series we covered how to create a stored function. In part two of this series we covered how to create a stored procedure.

In this final part of the series, we are going to show you how to tie it all together, how to take it to the next level and show you one or two “tricks of the trade” when working with stored functions and stored procedures.

Preparation

Stored routines require MySQL 5.0.1 or higher. Make sure you are using at least MySQL 5.0.1. Once again, we are going to work with the example database from MySQL called Sakila. It can be downloaded from MySQL at http://downloads.mysql.com/docs/. At the time of this post, the file is called sakila-db and it’s available as a zip or tarball. Specifically, we are going to work with the film table that is in the Sakila database.

For this post, we will be working with several pieces of lengthy code. It will be easier if we work directly with the MySQL command line instead of phpMyAdmin. On a Windows system, assuming you already have MySQL installed, the click path for launching the MySQL Command Line client should be:

Start -> All Programs -> Mysql Server -> MySQL Command Line Client

If you launch the MySQL Command Line Client and it asks for a password, type in the MySQL Server password. You more than likely created this password when you installed the MySQL Server. If you can’t remember your password, please visit this page in the MySQL documentation for assistance.

Also, the following two commands are new to this series. Using the MySQL command line, these two commands will show you a list of existing procedures or functions.

show procedure status \G;

show function status \G;

Our list of procedures looks like this:

image_01

Our list of functions looks like this:

image_02

If you want to see the particulars regarding a specific procedure or function, you would use the following command:

show create procedure <procedure name> \G;

show create function <function name> \G;

Of course, you would replace <procedure name> or <function name> with the actual name of the stored routine you wish to view. Also, when you review a function or procedure with the commands above, you will see a lot of extra text that you probably didn’t use when creating the function or routine. Ignore that extra text for now. When we use the show create procedure command for the procedure film_in_stock (which comes with the Sakila database) we see the following:

image_03

Enough with the preparation, let’s get started!

Tie It Together

If you remember from the first post in this series, we created a stored function that accepted a customer id number as a parameter and then displayed the amount that customer paid in total over all of their payments. Let’s pretend the bosses are setting up a reward for the customer that has spent the most at our stores.

The bosses will send this patron an e-mail coupon. The bosses need a routine that finds the customer that has spent the most, the customer’s name and the customer’s email address. We are going to rebuild that original function to only return the customer id of the customer that has spent the most at our store. Our original stored function looked like this:

CREATE FUNCTION total_paid_by_customer(cust_id INT)

RETURNS DECIMAL(5,2)

BEGIN

DECLARE total_sales DECIMAL(5,2);

SELECT SUM(payment.amount) INTO total_sales

FROM payment

WHERE payment.customer_id = cust_id;

RETURN total_sales;

END

//

Our new and improved function looks like this:

delimiter //

DROP FUNCTION IF EXISTS total_paid_by_customer//

CREATE FUNCTION total_paid_by_customer()

RETURNS SMALLINT(5)

BEGIN

DECLARE winner SMALLINT(5);

SELECT customer_id INTO winner

FROM payment

GROUP BY customer_id

ORDER BY SUM(payment.amount) DESC LIMIT 1;

RETURN winner;

END

//

delimiter ;

If you are having trouble reading our new and improved function, basically, it is selecting the customer id of the customer that has spent the largest amount of money with our company. Also, it returns the customer id as the variable winner.

Next, we need to take the information from the function and use it to gather the customer’s first name, last name and email address. Our procedure looks like this:

delimiter //

DROP PROCEDURE IF EXISTS get_winner//

CREATE PROCEDURE get_winner(OUT winner smallint(5), OUT firstName VARCHAR(45), OUT lastName VARCHAR(45), OUT emailAddress VARCHAR(50))

BEGIN

SELECT total_paid_by_customer() INTO winner;

SELECT customer.first_name, customer.last_name, customer.email INTO firstName, lastName, emailAddress

FROM customer

WHERE customer.customer_id = winner;

END;

//

delimiter ;

Now that you have created your procedure, it’s time to CALL it and see your results. In part two of this series we stated that procedures have no return value. So, at this point, you are probably wondering how you are going to see any results when you CALL your stored procedure.

Tricks of the Trade

One of the “tricks of the trade” is setting session variables in order to see the results of a procedure. Session variables are set using the @ symbol in front of the variable name. Remember, session variables are only valid for your current session. Don’t CALL your procedure with session variables and then next week run a select statement with the session variables and expect to see results.

For our stored procedure we want to use the following command to CALL the procedure:

CALL get_winner(@winner, @firstName, @lastName, @emailAddress);

As you can see, we CALL the procedure and we provide session versions of our OUT variables. This command runs our procedure and fills the session variables with the needed values.

Next, we need to use a SELECT command with our session variables to see the results of our procedure. Our SELECT command looks like this:

SELECT @firstName as Winner_First_Name, @lastName as Winner_Last_Name, @emailAddress as Winner_Email;

If everything went according to plan, you should see something like our results:

image_04

As you can see, Karl Seal with email address karl.seal@sakilacustomer.org is our winner! Good job Karl!

Just the F.A.Q.’s

Why didn’t we use the ALTER command on the original function?  You can’t use the ALTER command on parameters or bodies of stored routines.

When creating the procedure, if you get a warning about not being able to return a result set, make sure you are selecting INTO a variable. A straight SELECT or a SELECT AS will not work with functions.

If one of the fields being returned by the procedure is NULL when using MySQL Command Line client or if it returns an empty BLOB in phpMyAdmin it’s more than likely because you forgot to precede a parameter declaration with the OUT statement in your CREATE PROCEDURE statement.

Wrap Up

This is the final post in a three part series regarding stored routines in MySQL. In this post we covered how to view information regarding stored routines. We covered how to ALTER (without ALTERing) a function.

We covered how to call a function from within a procedure. We also learned how to see the results of a procedure even though procedures don’t have return values.

Overall, in this series we covered a lot of material. Although stored routines are often thought of as advanced topics, don’t be afraid to try new things and experiment. Remember to always make a backup of your data first and everything should be fine.

Leave a Reply

Your email address will not be published. Required fields are marked *