Creating Stored Programs

The CREATE PROCEDURE, CREATE FUNCTION, and CREATE TRIGGER statements allow you to create the various stored program objects: procedures, functions, and triggers.

You are no doubt familiar with the CREATE statements used to create tables, indexes, and other objects. There are some minor differences between the process of creating these objects and the process of creating stored programs. In addition to describing these differences, the following subsections describe the various environments in which you can issue the CREATE PROCEDURE, CREATE FUNCTION, and CREATE TRIGGER statements.

Before we dig into the syntax for creating and maintaining stored programs, let's look at the mechanics of editing the stored program text and submitting it to MySQL. There are three main ways you can edit your stored program code and submit it to MySQL:

• Edit the stored program using a standard editor such as vi, Emacs, or Notepad, and then use the MySQL command-line console to submit the statements.
• Edit and create the stored program inside the MySQL Query Browser.
• Use a third-party graphical toolsuch as Quest Software's Toad for MySQLto create the stored program.

7.1.1. Editing Stored Programs Using a System Editor

It is not a good idea to create a stored program by typing code directly into the MySQL command-line client. Instead, we normally use a GUI program such as the MySQL Query Browser (see the next section, "Using the MySQL Query Browser") or use a text editor or program editor to create the procedure and then load it into the database using the MySQL command-line client.

In Figure 7-1 we demonstrate creating a stored procedure using the Emacs editor on Linux. Emacs allows you to create a "shell" windowshown in the lower half of the Emacs window in Figure 7-1in which you can execute the MySQL client.

Figure 7-1. Editing a stored program in Linux with Emacs

In the top window in Figure 7-1, we create a text file called helloworld.sql. It contains a DROP PROCEDURE statementused to delete the procedure in case it already existsand a CREATE PROCEDURE statement.

In the lower window, we execute the MySQL command-line client and then use the SOURCE statement to execute the commands held in the external file. Our stored procedure is now created.

In Windows, we could use a text or program editor, such as Notepad, and run the MySQL client in a separate window. Figure 7-2 shows how to do that.

Figure 7-2. Editing a stored program in Windows with Notepad

 

7.1.2. Using the MySQL Query Browser

Using a text editor and the command-line client to edit and create a stored program is certainly feasible, as shown in the previous section, but it is hardly an efficient or productive process. Your stored program development will probably be faster and more pleasurable if you use a specialized graphical tool to create your program.

MySQL provides a graphical toolthe MySQL Query Browser (introduced in Chapter 1)to help us edit and create stored programs. The Query Browser also allows us to execute simple SQL statements and perform some basic schema management. Let's walk through the steps required to create a procedure using the Query Browser.

First we invoke the Create Stored Procedure/Function option from the Script menu, as shown in Figure 7-3. This opens the Create Stored Procedure dialog box (see Figure 7-4).

Figure 7-3. Creating a stored procedure in the Query Browser (step 1)

In the dialog box, type the name of the stored program and click the appropriate button to create either a stored procedure or a stored function.

The MySQL Query Browser loads a template file for the stored program. Into this template we can enter the stored program code. In this case, we simply add the SELECT 'Hello World'; text, as shown in Figure 7-5.

Finally, we click the Execute button to execute the script and create our procedure. Make sure that you use the Execute option in the Script menu (middle left of the window) rather than the Execute button (upper right). If we are successful, the procedure name should appear in the Schemata window on the right, as shown in Figure 7-6.

Figure 7-4. Creating a stored procedure in the Query Browser (step 2)

Our stored procedure has now been created.

7.1.3. Using Third-Party Tools

The MySQL Query Browser is a fine tool for creating and maintaining stored programs. However, there are many tools on the market that provide additional features such as code formatting, improved editing features, and more powerful administration and schema management capabilities. Some of these products are also able to work with other RDBMS systems such as Oracle and SQL Server.

Quest Software's Toad for MySQL, illustrated in Figure 7-7, is such an Integrated Development Environment (IDE) product. Toad is a standard in the Oracle community for stored program (PL/SQL) development and is available for Oracle, DB2, and SQL Server as well as for MySQL.

Figure 7-5. Creating a stored procedure in the Query Browser (step 3)

Figure 7-6. Creating a stored procedure in the Query Browser (step 4)

Figure 7-7. Editing stored programs with Toad for MySQL

 

7.1.4. Handling Semicolons in Stored Program Code

When you type the text of a stored program, you will need to deal with the issue of semicolons in your code.

MySQL uses the semicolon to mark the end of a SQL statement. However, stored programs usually contain semicolons within the program code, and this can cause MySQL to get rather confused. For instance, in Example 7-1, note that while we are typing in the text of a stored procedure, the first semicolon in the stored procedure causes MySQL to try to compile the procedure, causing an error because the stored procedure code is not yet complete.

Example 7-1. Semicolons indicate end of SQL statement, causing an error when creating a stored procedure

Welcome to the MySQL monitor. Commands end with; or g.
Your MySQL connection id is 2 to server version: 5.0.16-nightly-20051017-log

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

mysql> CREATE PROCEDURE HelloWorld( )
 -> BEGIN
 -> SELECT 'Hello World';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'SELECT 'Hello
World'' at line 3
mysql>

To avoid this kind of error, we need to inform MySQL that we are not going to use semicolons to define the end of a statement. In Example 7-2 we use the DELIMITER statement to change the delimiter from ";" to "$$", allowing us to successfully create the procedure.

Example 7-2. Using a nondefault delimiter when creating a stored object

mysql> DELIMITER $$

mysql> CREATE PROCEDURE HelloWorld( )
 -> BEGIN
 -> SELECT 'Hello World';
 -> END$$
Query OK, 0 rows affected (0.00 sec)