Creating MySQL Stored Routines for Non-DBAs – Part One

Stored routines are fairly new to MySQL, but they have been available in other databases for some time.  A stored routine has the following advantages:

  • Speed!  Stored routines use MySQL server caching technology.
  • Speed!  Repetitive tasks can be handled at the database level therefore reducing the amount of “back and forth” network traffic.
  • Speed!  Stored routines are written in SQL.  If you switch hosts and have to use a different scripting language, you don’t have to rewrite your stored routine.

One of the stored routines available in MySQL is the stored function. In our scenario, we have a table that contains payment records for our customers.  In this post we are going to create a stored function that returns the total amount a customer has paid across all of their payments.

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 found 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 payment table that is in the Sakila database.

In this post we will also be working with phpMyAdmin. This program is very common and wide spread. If you have a web host, you probably have access to phpMyAdmin to work with your databases. If you have command line access to your MySQL databases, the examples in this post should work just as well.

Creating A Stored Function

The first step in creating a stored function is to inform MySQL what you will be using as a delimiter. There’s nothing worse than creating a very large stored routine and then realizing you forgot to provide a delimiter and MySQL stops at the first semi-colon it encounters. Therefore, the first thing is to declare a delimiter. You can see in the image below, we are using // for a delimiter:

image_01

Our next step is to use the CREATE FUNCTION command. We also need to provide a name for our new function. If our new function is going to accept any parameters, we must also provide names and types for each. We are going to call our function total_paid_by_customer.

Our function accepts an integer which represents the customer id number. We are going to call this parameter cust_id. Our SQL window in phpMyAdmin now looks like this:

image_02

If you wanted to accept more than one parameter, you would separate each with a comma.

Stored functions always have a return value. Therefore, the next step in creating our MySQL stored function is to state our return value types. We are going to return a 5 digit number with 2 characters on the right side of the decimal point. We simply add RETURNS DECIMAL(5,2) to the next line. Our SQL window in phpMyAdmin now looks like this:

image_03

The next step is to inform MySQL that we are starting the function body. We do that by typing BEGIN on the next line. Our SQL window in phpMyAdmin now looks like this:

image_04

For the body of our new MySQL function, we are going to declare a variable and its type so we can use select into in order to store the results of our SQL SUM statement. We also want to include a return statement to return our select into final value. Once we type all of this out, the last seven lines in our SQL window in phpMyAdmin looks like this:

image_05

Notice, the 2 character indentation in the body of the function, it’s not necessary, but it makes it easier to separate out the body of the function.

Finally, we can tell MySQL that we are ending the body of our function. We do that by typing END on the next line. Remember to type your delimiter after the END command as well; otherwise you will probably receive an error when you run the CREATE FUNCTION command. The last seven lines of our function now look like this:

image_06

Now we are ready to click on the Go button located below and to the right of our SQL window in phpMyAdmin. Hopefully, once you click the button, you don’t receive any errors. If you do, they are most likely related to the syntax of what you typed. If you just want to copy and paste the function into phpMyAdmin, here’s the code:

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

//

Now we are going to test our new function. In the phpMyAdmin SQL window type a SELECT statement that selects from the function name along with a customer id number. Ours looks like this:

image_07

When we run our function and pass in the customer id of 2, we receive the following results:

image_08

Our function worked. Notice the total that customer number 2 has paid is $128.73.

Wrap Up

In this post we covered stored functions which are part of the stored routine family. Right now you might be thinking stored functions are just a fancy way to perform a simple query.

You might not realize the elegance or value of a stored function at this point, but you will as this series continues. Stay tuned for the next segment where we’ll introduce you to the stored function’s big brother, the stored procedure.

Comments [4]

  1. I pasted the sample SQL code into phpMyAdmin and received this error:

    MySQL said:

    #1418 – This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

    Is there a solution?

  2. Add DETERMINISTIC after RETURNS DECIMAL(5,2) and before BEGIN it will execute successfully

Leave a Reply

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