MySQL Stored Procedure Programming (2009)

Part I. Stored Programming Fundamentals

Chapter 2. MySQL Stored Programming Tutorial

MySQL stored programming is a complex topic. We offer this chapter to introduce you to the main and common tasks you will need to perform, including:

§ How to create a stored program

§ How to pass information in and out of the stored program

§ How to interact with the database

§ How to create procedures, functions, and triggers in the MySQL stored program language

We don't go into detail in this chapter. Our purpose is to get you started and to give you some appreciation of how stored programs work. Later chapters will explore in detail all of the topics touched on in this chapter.

What You Will Need

To follow along with the examples in this tutorial , you will need:

§ A MySQL 5 server

§ A text editor such as vi, emacs, or Notepad

§ The MySQL Query Browser

You can get the MySQL server and MySQL Query Browser from http://dev.mysql.com.

Our First Stored Procedure

We'll start by creating a very simple stored procedure. To do this, you need an editing environment in which to write the stored procedure and a tool that can submit the stored procedure code to the MySQL server.

You can use just about any editor to write your code. Options for compiling that code into MySQL include:

§ The MySQL command-line client

§ The MySQL Query Browser

§ A third-party tool such as Toad for MySQL

In this chapter, we won't make any assumptions about what tools you have installed, so we'll start with the good old MySQL command-line client.

Let's connect to the MySQL server on the local host at port 3306 using the root account. We'll use the preinstalled "test" database in Example 2-1.

Example 2-1. Connecting to the MySQL command-line client

[gharriso@guyh-rh4-vm2 ~]$mysql -uroot -psecret -hlocalhost

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1 to server version: 5.0.16-nightly-20051017-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Creating the Procedure

You can create a stored program with the CREATE PROCEDURE , CREATE FUNCTION , or CREATE TRIGGER statement. It is possible to enter these statements directly at the MySQL command line, but this is not practical for stored programs of more than trivial length, so the best thing for us to do is to create a text file containing our stored program text. Then we can submit this file to the database using the command-line client or another tool.

We will use the MySQL Query Browser as a text editor in this example. If you don't have this tool, you can download it from http://dev.mysql.com/downloads/. Alternately, you could use an OS text editor such as vi, emacs, or Notepad. We like the MySQL Query Browser because of its built-in help system, syntax highlighting, ability to run SQL statements, and lots of other features.

Follow these steps:

1. Run the MySQL Query browser. On Windows, from the Start menu select Programs → MySQL → MySQL Query Browser. On Linux, type mysql-query-browser from the command line.

2. Select File → New Script tab from the menu to create a blank script window.

3. Enter your stored program command text.

Figure 2-1 shows our first stored procedure.

We then use the File → Save As menu option to save our file so that we can execute it from the mysql client.

Figure 2-1. A first stored procedure

This first stored procedure is very simple, but let's examine it line by line to make sure you understand it completely:

With our definition stored in a file, we can now use the mysql client to create and then execute the HelloWorld stored procedure, as shown in Example 2-2.

Example 2-2. Creating our first stored procedure

$ mysql -uroot -psecret -Dprod

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 16 to server version: 5.0.18-nightly-20051208-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SOURCEHelloWorld.sql

Query OK, 0 rows affected, 1 warning (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALLHelloWorld( ) $$

+-------------+

| Hello World |

+-------------+

| Hello World |

+-------------+

1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>

Here is an explanation of the MySQL commands used to get all this to work:

Creating the Procedure Using the MySQL Query Browser

In this tutorial—and indeed throughout this book—we will mostly create and demonstrate stored programs the old-fashioned way: using the MySQL command-line client to create the stored program. By doing this, you'll always be able to duplicate the examples. However, you do have the option of using a GUI tool to create stored programs: there are a number of good third-party GUI tools for MySQL available, and you always have the option of installing and using the MySQL Query Browser, available from http://dev.mysql.com/downloads/.

In this section we offer a brief overview of creating a stored procedure using the MySQL Query Browser. Using the Query Browser is certainly a more user-friendly way of creating stored programs, although it might not be available on all platforms, and you may prefer to use the MySQL command line or the various third-party alternatives.

On Windows, you launch the Query Browser (if installed) from the Start menu option Programs → MySQL → MySQL Query Browser. On Linux, you type mysql-query-browser.

When the Query Browser launches, it prompts you for connection details for your MySQL server. Once you have provided these, a blank GUI window appears. From this window, select Script and then Create Stored Procedure. You will be prompted for the name of the stored program to create, after which an empty template for the stored program will be displayed. An example of such a template is shown in Figure 2-2.

Figure 2-2. Creating a stored procedure in the MySQL Query Browser

You can then enter the text of the stored procedure at the appropriate point (between the BEGIN and END statements—the cursor is handily positioned there automatically). Once you have finished entering our text, simply click the Execute button to create the stored procedure. If an error occurs, the Query Browser highlights the line and displays the error in the lower half of the Query Browser window. Otherwise, you'll see the name of the new stored procedure appear in the Schemata tab to the left of the stored procedure, as shown in Figure 2-3.

To execute the stored procedure, double-click on the name of the procedure within the Schemata tab. An appropriate CALL statement will be pasted into the execution window above the stored procedure. Clicking on the Execute button to the right of the CALL statement executes the stored procedure and displays a results window, as shown in Figure 2-4.

Figure 2-3. Stored procedure is created by clicking the Execute button

We hope this brief example gives you a feel for the general process of creating and executing a stored procedure in the MySQL Query Browser. The Query Browser offers a convenient environment for the development of stored programs, but it is really up to you whether to use the Query Browser, a third-party tool, or simply your favorite editor and the MySQL command-line client.

Variables

Local variables can be declared within stored procedures using the DECLARE statement. Variable names follow the same naming rules as MySQL table column names and can be of any MySQL data type. You can give variables an initial value with the DEFAULT clause and assign them new values using the SET command, as shown in Figure 2-5.

Parameters

Most of the stored programs you write will include one or more parameters. Parameters make stored programs much more flexible and therefore more useful. Next, let's create a stored procedure that accepts parameters.

Figure 2-4. Executing the stored procedure in the Query Browser

The stored procedure shown in Figure 2-6 accepts an integer parameter, input_number, and calculates the square root of that number. The resulting number is returned as a result set.

Place parameters within parentheses that are located immediately after the name of the stored procedure. Each parameter has a name, a data type, and, optionally, a mode. Valid modes are IN (read-only), INOUT (read-write), and OUT (write-only). No parameter mode appears in Figure 2-6, because IN is the default and this is an IN parameter.

We'll take a closer look at parameter modes following this example.

In addition to the parameter, this stored procedure introduces two other features of MySQL stored programs:

DECLARE

A statement used to create local variables for use in the stored program. In this case, we create a floating-point number called l_sqrt.

Figure 2-5. Examples of variables in stored procedures

SET

A statement used to assign a value to a variable. In this case, we assign the square root of our input parameter (using the built-in SQRT function) to the floating-point number we created with the DECLARE command.

We can run this script, and test the resulting stored procedure in the MySQL client, as shown in Example 2-3.

Example 2-3. Creating and executing a stored procedure with a parameter

mysql> SOURCEmy_sqrt.sql

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALLmy_sqrt(12)$$

+-----------------+

| l_sqrt |

+-----------------+

| 3.4641016151378 |

+-----------------+

1 row in set (0.12 sec)

Query OK, 0 rows affected (0.12 sec)

Figure 2-6. A stored procedure with parameters

Parameter Modes

Parameters in MySQL can be defined as IN, OUT, or INOUT:

IN

This mode is the default. It indicates that the parameter can be passed into the stored program but that any modifications are not returned to the calling program.

OUT

This mode means that the stored program can assign a value to the parameter, and that value will be passed back to the calling program.

INOUT

This mode means that the stored program can read the parameter and that the calling program can see any modifications that the stored program may make to that parameter.

You can use all of these parameter modes in stored procedures, but only the IN mode in stored functions (see the later See "Stored Functions" section).

Let's change our square root program so that it puts the result of its calculations into an OUT variable, as shown in Figure 2-7.

Figure 2-7. Example of using OUT parameter in a stored procedure

In the MySQL client, we now have to provide a variable to hold the value of the OUT parameter. After the stored procedure has finished executing, we can look at that variable to retrieve the output, as shown in Example 2-4.

Example 2-4. Creating and executing a stored procedure with an OUT parameter

mysql> SOURCEmy_sqrt2.sql

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

mysql> CALLmy_sqrt(12,@out_value) $$

Query OK, 0 rows affected (0.03 sec)

mysql> SELECT@out_value $$

+-----------------+

| @out_value |

+-----------------+

| 3.4641016151378 |

+-----------------+

1 row in set (0.00 sec)

Conditional Execution

You can control the flow of execution in your stored program by using IF or CASE statements. Both have roughly the same functionality; we will demonstrate the use of IF in this tutorial, as it's probably the most familiar of the two constructs.

Figure 2-8 shows a stored program that works out the discounted rate for a purchase based on the size of the purchase, and Example 2-5 shows its execution. Purchases over $500 get a 20% discount, while purchases over $100 get a 10% discount.

Figure 2-8. Conditional execution with the IF statement

Example 2-5. Creating and executing a stored procedure that contains an IF statement

mysql> SOURCEdiscounted_price.sql

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALLdiscounted_price(300,@new_price) $$

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT@new_price$$

+------------+

| @new_price |

+------------+

| 270.0 |

+------------+

1 row in set (0.00 sec)

The IF statement allows you to test the truth of an expression such as normal_price > 500 and take appropriate action based on the result of the expression. As with other programming languages, the ELSEIF clause is used for all conditional branches after the initial IF. The ELSE clause is executed if the Boolean expressions in the IF and ELSEIF clauses all evaluate to false.

CASE has very similar functionality, and may be preferable when you are comparing a single expression against a set of possible distinct values. The two conditional statements are explored and contrasted in Chapter 4.

Loops

Loops allow stored programs to execute statements repetitively. The MySQL stored program language offers three types of loops :

§ Simple loops using the LOOP and END LOOP clauses

§ Loops that continue while a condition is true, using the WHILE and END WHILE clauses

§ Loops that continue until a condition is true, using the REPEAT and UNTIL clauses

With all three loop types, you terminate execution of the loop with the LEAVE statement.

All three types of loops are described in detail in Chapter 4; we'll only demonstrate the LOOP-LEAVE-END LOOP (simple loop) sequence in this tutorial.

Figure 2-9 shows a very simple loop.

Figure 2-9. A simple loop inside a stored procedure

Here is an explanation of the activity in this stored procedure:

Dealing with Errors

When an error occurs in a stored program, the default behavior of MySQL is to terminate execution of the program and pass the error out to the calling program. If you need a different kind of response to an error, you create an error handler that defines the way in which the stored program should respond to one or more error conditions.

The following are two relatively common scenarios that call for the definition of error handlers:

§ If you think that an embedded SQL statement might return no rows, or you need to fetch all the rows from a SELECT statement using a cursor, a NOT FOUND error handler will prevent the stored program from terminating prematurely.

§ If you think that a SQL statement might return an error (a constraint violation, for instance), you may need to create a handler to prevent program termination. The handler will, instead, allow you to process the error and continue program execution.

Chapter 6 describes in detail how to use error handlers. An example of using a NOT FOUND error handler with a cursor is shown in the next section.

Interacting with the Database

Most stored programs involve some kind of interaction with database tables. There are four main types of interactions:

§ Store the results of a SQL statement that returns a single row into local variables.

§ Create a "cursor" that allows the stored program to iterate through the rows returned by a SQL statement.

§ Execute a SQL statement, returning the result set(s) to the calling program.

§ Embed a SQL statement that does not return a result set, such as INSERT, UPDATE, DELETE, etc.

The following sections look briefly at each type of interaction.

Tip

To run the examples in this section of the chapter, you should install the book's sample database, available at this book's web site (see the Preface for details).

SELECTing INTO Local Variables

Use the SELECT INTO syntax when you are querying information from a single row of data (whether retrieved from a single row, an aggregate of many rows, or a join of multiple tables). In this case, you include an INTO clause "inside" the SELECT statement that tells MySQL where to put the data retrieved by the query.

Figure 2-10 shows a stored procedure that obtains and then displays the total sales for the specified customer ID. Figure 2-6 executes the procedure.

Figure 2-10. A stored procedure with an embedded SELECT INTO statement

Example 2-6. Executing a stored procedure that includes a SELECT INTO statement

mysql> CALL customer_sales(2) $$

+--------------------------------------------------------------+

| CONCAT('Total sales for ',in_customer_id,' is ',total_sales) |

+--------------------------------------------------------------+

| Total sales for 2 is 7632237 |

+--------------------------------------------------------------+

1 row in set (18.29 sec)

Query OK, 0 rows affected (18.29 sec)

Using Cursors

SELECT INTO is fine for single-row queries, but many applications require the querying of multiple rows of data. You will use a cursor in MySQL to accomplish this. A cursor lets you fetch one or more rows from a SQL result set into stored program variables, usually with the intention of performing some row-by-row processing on the result set.

The stored procedure in Figure 2-11 uses a cursor to fetch all rows from the employees table.

Here is an explanation of the significant lines in this procedure:

Figure 2-11. Using a cursor in a stored procedure

Returning Result Sets from Stored Procedures

An unbounded SELECT statement—one not associated with an INTO clause or a cursor—returns its result set to the calling program. We have used this form of interaction between a stored procedure and the database quite a few times already in this book, using simple SELECTs to return some kind of status or result from a stored procedure. So far, we've used only single-row result sets, but we could equally include a complex SQL statement that returns multiple rows within the stored procedure.

If we execute such a stored procedure from the MySQL command line, the results are returned to us in the same way as if we executed a SELECT or SHOW statement. Figure 2-12 shows a stored procedure that contains such an unbounded SELECT statement.

Figure 2-12. An unbounded SELECT statement in a stored procedure

If we execute the stored procedure and supply an appropriate value for the input parameter, the results of the SELECT within the stored procedure are returned. In Figure 2-13 we see the results of the SELECT statement being returned from the stored procedure call from within the MySQL Query Browser.

Figure 2-13. Results returned from a stored procedure that has an unbounded SELECT

Note that a stored program call can return more than one result set. This creates special challenges for the calling program, which we discuss—for each specific programming language—in Chapters 13 through 17.

Embedding Non-SELECTs

"Simple" SQL statements that do not return results can also be embedded in your stored programs. These statements include DML statements such as UPDATE, INSERT, and DELETE and may also include certain DDL statements such as CREATE TABLE. Some statements—specifically those that create or manipulate stored programs—are not allowed; these are outlined in Chapter 5.

Figure 2-14 shows a stored procedure that includes an update operation. The UPDATE statement is enclosed in some validation logic that prevents the update from proceeding if the input values are invalid.

Figure 2-14. Stored procedure with an embedded UPDATE

Calling Stored Programs from Stored Programs

Calling one stored program from another is perfectly simple. You do this with the CALL statement, just as you would from the MySQL command-line client.

Figure 2-15 shows a simple stored procedure that chooses between two stored procedures based on an input parameter. The output of the stored procedure (l_bonus_amount is populated from an OUT parameter) is passed to a third procedure.

Here is an explanation of the significant lines:

Figure 2-15. Example of calling one stored procedure from another

Putting It All Together

In Example 2-7 we show a stored procedure that uses all the features of the stored program language we have covered so far in this tutorial.

Example 2-7. A more complex stored procedure

1 CREATE PROCEDURE putting_it_all_together(in_department_id INT)

2 MODIFIES SQL DATA

3 BEGIN

4 DECLARE l_employee_id INT;

5 DECLARE l_salary NUMERIC(8,2);

6 DECLARE l_department_id INT;

7 DECLARE l_new_salary NUMERIC(8,2);

8 DECLARE done INT DEFAULT 0;

9

10 DECLARE cur1 CURSOR FOR

11 SELECT employee_id, salary, department_id

12 FROM employees

13 WHERE department_id=in_department_id;

14

15

16 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

17

18 CREATE TEMPORARY TABLE IF NOT EXISTS emp_raises

19 (employee_id INT, department_id INT, new_salary NUMERIC(8,2));

20

21 OPEN cur1;

22 emp_loop: LOOP

23

24 FETCH cur1 INTO l_employee_id, l_salary, l_department_id;

25

26 IF done=1 THEN /* No more rows*/

27 LEAVE emp_loop;

28 END IF;

29

30 CALL new_salary(l_employee_id,l_new_salary); /*get new salary*/

31

32 IF (l_new_salary<>l_salary) THEN /*Salary changed*/

33

34 UPDATE employees

35 SET salary=l_new_salary

36 WHERE employee_id=l_employee_id;

37 /* Keep track of changed salaries*/

38 INSERT INTO emp_raises (employee_id,department_id,new_salary)

39 VALUES (l_employee_id,l_department_id,l_new_salary);

40 END IF;

41

42 END LOOP emp_loop;

43 CLOSE cur1;

44 /* Print out the changed salaries*/

45 SELECT employee_id,department_id,new_salary from emp_raises

46 ORDER BY employee_id;

47 END;

This is the most complex procedure we have written so far, so let's go through it line by line:

When this stored procedure is executed from the MySQL command line with the parameter of department_id set to 18, a list of updated salaries is printed as shown in Example 2-8.

Example 2-8. Output from the "putting it all together" example

mysql> CALL putting_it_all_together(18) //

+-------------+---------------+------------+

| employee_id | department_id | new_salary |

+-------------+---------------+------------+

| 396 | 18 | 75560.00 |

| 990 | 18 | 118347.00 |

+-------------+---------------+------------+

2 rows in set (0.23 sec)

Query OK, 0 rows affected (0.23 sec)

Stored Functions

Stored functions are similar to stored procedures: they are named program units that contain one or more MySQL statements. They differ from procedures in the following ways:

§ The parameter list of a function may contain only IN parameters. OUT and INOUT parameters are not allowed. Specifying the IN keyword is neither required nor allowed.

§ The function itself must return a single value, whose type is defined in the header of the function.

§ Functions can be called from within SQL statements.

§ A function may not return a result set.

Generally, you should consider using a stored function rather than a stored procedure when you have a program whose sole purpose is to compute and return a single value or when you want to create a user-defined function for use within SQL statements.

Figure 2-16 shows a function that implements the same functionality found in the discount_price stored procedure we created earlier in this chapter.

Figure 2-16. A stored function

The following table explains a few things that set apart this function from its stored procedure equivalent:

Example 2-9 shows calling this function from within a SQL statement.

Example 2-9. Calling a stored function from a SELECT statement

mysql> SELECT f_discount_price(300) $$

+-----------------------+

| f_discount_price(300) |

+-----------------------+

| 270.0 |

+-----------------------+

We can also call this function from within another stored program (procedure, function, or trigger), or any place that we could use a built-in MySQL function.

Triggers

A trigger is a special type of stored program that fires when a table is modified by an INSERT, UPDATE, or DELETE (DML) statement. Triggers implement functionality that must take place whenever a certain change occurs to the table. Because triggers are attached directly to the table, application code cannot bypass database triggers.

Typical uses of triggers include the implementation of critical business logic, the denormalization of data for performance reasons, and the auditing of changes made to a table. Triggers can be defined to fire before or after a specific DML statement executes.

In Figure 2-17, we create a trigger that fires before any INSERT statement completes against the sales table. It automatically applies free shipping and discounts to orders of a specified value.

Figure 2-17. A database trigger

Here is an explanation of the trigger definition:

The effect of the trigger is to automatically set the value of the free_shipping and discount columns. Consider the INSERT statement shown in Example 2-10.

Example 2-10. An INSERT into the sales table

INSERT INTO sales

(customer_id, product_id, sale_date, quantity, sale_value,

department_id, sales_rep_id)

VALUES(20,10,now( ),20,10034,4,12)

The sale is valued at $10,034 and, as such, is eligible for a 15% discount and free shipping. Example 2-11 demonstrates that the trigger correctly set these values.

Example 2-11. A trigger automatically populates the free_shipping and discount columns

mysql> SELECT sale_value,free_shipping,discount

-> FROM sales

-> WHERE sales_id=2500003;

+------------+---------------+----------+

| sale_value | free_shipping | discount |

+------------+---------------+----------+

| 10034 | Y | 1505 |

+------------+---------------+----------+

1 row in set (0.22 sec)

Using a trigger to maintain the free_shipping and discount columns ensures that the columns are correctly maintained regardless of the SQL statements that might be executed from PHP , C#, or Java, or even from the MySQL command-line client.

Calling a Stored Procedure from PHP

We've shown you how to call stored programs from the MySQL command-line client, from the MySQL Query Browser, and from another stored program. In the real world, however, you are more likely to call a stored program from another programming environment, such as PHP, Java, Perl, Python, or .NET. We discuss the details of using stored programs within each of these environments in Chapters Chapter 12 through Chapter 17.

For now, let's look at how you can call a stored procedure (shown in Figure 2-18) from PHP, which is probably the development environment most commonly used in conjunction with MySQL.

When interacting with MySQL from PHP, we can choose between the database-independent PEAR::DB extension, the mysqli (MySQL "improved") extension, and the more recent PHP Data Objects (PDO) extension. In this example we will use the mysqli extension. Chapter 13 describes the details of these extensions.

Figure 2-19 shows PHP code that connects to the MySQL server and calls the stored procedure. We won't step through the code here, but we hope that it will give you a sense of how stored programs can be used in web and other applications.

Figure 2-18. Stored procedure to be called from PHP

The PHP program prompts the user to specify a department ID; it then calls the stored procedure employee_list to retrieve a list of employees that belong to that department. Figure 2-20 shows the output displayed by the PHP/stored procedure example.

Conclusion

In this chapter we presented a brief "getting started" tutorial that introduced you to the basics of MySQL stored programs. We showed you how to:

§ Create a simple "Hello World" stored procedure.

§ Define local variables and procedure parameters.

§ Perform conditional execution with the IF statement.

§ Perform iterative processing with simple loops.

§ Include SQL statements inside stored procedures, including how to perform row-at-a-time processing with cursors.

§ Call a stored program from another stored program.

§ Create a stored function (and differentiate stored functions from stored procedures).

§ Create a trigger on a table to automate denormalization.

§ Call a stored procedure from PHP.

Figure 2-19. Sample PHP program calling a stored procedure

You may now be tempted to put down this book and start writing MySQL stored programs. If so, we congratulate you on your enthusiasm. May we suggest, however, that you first spend some time reading more detailed explanations of each of these areas of functionality in the following chapters? That way, you are likely to make fewer mistakes and write higher-quality code.

Figure 2-20. Output from our PHP example