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>

2.2.1. 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. New Script tab from the menu to create a blank script window.
3. 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:

   Line	Explanation
   1	Issue the DELIMITER command to set '$$' as the end of a statement. Normally, MySQL regards ";" as the end of a statement, but since stored procedures contain semicolons in the procedure body, we need to use a different delimiter.
   3	Issue a DROP PROCEDURE IF EXISTS statement to remove the stored procedure if it already exists. If we don't do this, we will get an error if we then try to re-execute this file with modifications and the stored procedure exists.
   4	The CREATE PROCEDURE statement indicates the start of a stored procedure definition. Note that the stored procedure name "HelloWorld" is followed by an empty set of parentheses "( )". If our stored procedure had any parameters, they would be defined within these parentheses. This stored procedure has no parameters, but we need to include the parentheses anyway, or we will get a syntax error.
   5	The BEGIN statement indicates the start of the stored procedure program. All stored programs with more than a single statement must have at least one BEGIN and END block that defines the start and end of the stored program.
   6	This is the single executable statement in the procedure: a SELECT statement that returns "Hello World" to the calling program. As you will see later, SELECT statements in stored programs can return data to the console or calling program just like SELECT statements entered at the MySQL command line.
   7	The END statement terminates the stored procedure definition. Note that we ended the stored procedure definition with $$ so that MySQL knows that we have completed the CREATE PROCEDURE statement.

    

   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:

   Command	Explanation
   SOURCE HelloWorld.sql	Reads commands from the nominated file. In this case, we specify the file we just saved from the MySQL Query Browser. No errors are returned, so the stored procedure appears to have been created successfully.
   CALL HelloWorld( ) $$	Executes the stored procedure. Calling our stored procedure successfully results in "Hello World" being output as a result set. Note that we terminated the CALL command with '$$', since that is still what the DELIMITER is set to.

    

   2.2.2. Creating the Procedure Using the MySQL Query Browser

   In this tutorialand indeed throughout this bookwe 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 statementsthe 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.