MySQL Stored Procedure Programming (2009)

Part II. Stored Program Construction

This part of the book describes how you can use the elements described in Part I to build functional and useful stored programs. In Chapter 7 we outline the commands available for creating and modifying stored programs and provide some advice on how to manage your stored program source code. Chapter 8 outlines transaction handling in stored programs, while Chapter 9 details the built-in functions that can be used in stored programs. Chapters 10 and 11 detail two "special" types of stored programs: Chapter 10 shows how you can create and use stored functions;Chapter 11 describes triggers, which are stored programs that are invoked in response to DML executed on a database table.

Chapter 7, Creating and Maintaining Stored Programs

Chapter 8, Transaction Management

Chapter 9, MySQL Built-in Functions

Chapter 10, Stored Functions

Chapter 11, Triggers

Chapter 7. Creating and Maintaining Stored Programs

In this chapter, we'll explain how to create, maintain, and delete stored programs .

By definition, a stored program exists in the database (it wouldn't be stored otherwise, right?). So the fundamental process of creating a stored program involves submitting SQL statements to MySQL, just as creating a table involves submitting the CREATE TABLE statement. The basic process of creating and maintaining a stored program is very similar to that of creating any other kind of database object: you write some SQL to create the object and you (hopefully) save that SQL somewhere safe so that you can reuse it later. At some later time you may alter the object (or drop and recreate it), and you may want to find out information about it.

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 tool—such as Quest Software's Toad for MySQL—to create the stored program.

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" window—shown in the lower half of the Emacs window in Figure 7-1—in 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 statement—used to delete the procedure in case it already exists—and 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

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 tool—the 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.

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

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)

Editing an Existing Stored Program

There are two approaches to editing the text of existing stored programs. The easiest —though probably not the best—way to edit an existing stored program is to use the MySQL Query Browser to edit the stored program in place. By "in place," we mean that you work directly with the copy of the stored program held in the database. A better way is to edit an external text file that contains the stored procedure code. We describe these approaches in the following subsections.

Editing a Program in Place

Editing a stored program in place is certainly easy, as shown in Figure 7-8. To edit an existing stored program in this way, you simply locate and select the stored program in the MySQL Query Browser's Schemata browser, right-click, and select Edit Procedure (or Edit Function) from the context menu. The relevant stored program code is loaded from the database into the edit window where you can make your changes. Clicking the Execute button runs the modified script and replaces the stored program in the database.

Figure 7-8. Editing a stored program in place with the MySQL Query Browser

Maintaining Stored Programs in External Files

There are a number of reasons why you may not want to edit stored programs in place, as we did in Figure 7-8:

§ When you retrieve the text for a stored program from the database (as Query Browser and other similar programs do), you may find that the text of the stored program is slightly different from the version you originally created. In particular, the name of the stored routine may be quoted and the name of the database prepended. This prepending of the database name is a bad idea if you want to migrate stored programs to other databases.

§ It is definitely best practice to use a source control system (such as Microsoft SourceSafe, Subversion, or CVS) to store each changed iteration of your stored program. This allows you to roll back changes to a stored program that turn out to be problematic, and allows you to retrieve a specific version of a program when multiple versions are in use.

Some third-party MySQL development tools allow you to load and save your stored program source directly into a version control system such as CVS. For instance, in Toad for MySQL we can check files in and out of CVS or SourceSafe from within our programming environment, as shown in Figure 7-9.

Figure 7-9. Toad for MySQL provides integration with version control systems

Regardless of whether your IDE directly supports integration with a version control system, you should still use version control to maintain stored program code. Rather than extract the stored program code from the database, you will extract it from an external file before editing, and you will save the external file—and check it into your version control system—when it is complete.

Figure 7-10 shows how we can perform these actions on a Linux system using the MySQL Query Browser as our editing environment and RCS as our version control system.

Figure 7-10. Maintaining stored program source code in a source control system

Let's work through the steps highlighted in Figure 7-10:

1. Before we get started, we need to extract the source file from the version control system and lock it for editing. In the RCS system this is done with the co -l command.

2. Now we can load the source file into an edit window in the MySQL Query Browser.

3. After making our edits, we can save our changes to the database by clicking the Execute button.

4. We can perform basic testing of the stored program by running it from within the Query Browser. Double-clicking the stored program name in the Schemata browser is one way to do this.

5. If we are satisfied that our changes are good, we can save them back to the disk file we originally loaded.

6. Now we check the changes back into version control. In RCS this is done with the ci command.

SQL Statements for Managing Stored Programs

This section summarizes the syntax of the statements used to create, modify, and remove stored programs from the database. This section provides only an overview; we'll drill down into many of the details of these statements in other chapters.

CREATE PROCEDURE

The CREATE PROCEDURE statement—you guessed it—creates a stored procedure. The syntax for the statement is:

CREATE PROCEDURE procedure_name ([parameter[,...])

[LANGUAGE SQL]

[ [NOT] DETERMINISTIC

]

[ {CONTAINS SQL|MODIFIES SQL DATA|READS SQL DATA|NO SQL} ]

[SQL SECURITY {DEFINER|INVOKER} ]

[COMMENT comment_string]procedure_statements

The procedure_name follows the normal conventions for the naming of database objects (see Chapter 3).

The parameter list consists of a comma-separated list of arguments that can be provided to the stored procedure. We spent quite a bit of time on parameters in Chapter 3, but to summarize, each parameter is of the form:

[{IN|OUT|INOUT} ] parameter_name datatype

By default, parameters are of the IN type: this means that their values must be specified by the calling program and that any modifications made to the parameter in the stored program cannot be accessed from the calling program. OUT parameters, on the other hand, can be modified by the stored program, and the modified values can be retrieved from the calling program.

An INOUT parameter acts as both an IN and an OUT parameter: the calling program can supply a value and can see whatever changes are made to the parameter inside the stored procedure.

The following are descriptions of the other keywords you can specify in the CREATE PROCEDURE statement:

LANGUAGE SQL

Indicates that the stored procedure uses the SQL:PSM standard stored procedure language. Since MySQL currently supports only those stored procedures written in this language, specifying this keyword is unnecessary at present. However, in future versions, MySQL might support stored procedures written in other languages (Java, for instance), and if this occurs, you may need to specify this keyword.

SQL SECURITY {DEFINER|INVOKER}

Determines whether the stored procedure should execute using the permissions of the user who created the stored procedure (DEFINER) or the permissions of the user who is currently executing the stored procedure (INVOKER). The default is DEFINER. We look at the implications of these two security modes in Chapter 18.

[NOT] DETERMINISTIC

Indicates whether the stored procedure will always return the same results if the same inputs are provided. For instance, an SQRT function is deterministic because the square root of a number never changes, while an AGE function is nondeterministic because people are getting older all the time (sigh). By default, MySQL will assume that a stored procedure (or function) is NOT DETERMINISTIC .

In fact, the only time this keyword is critical is when you are creating a stored function (but because the CREATE PROCEDURE syntax allows you to specify it, we mention it here): when binary logging is enabled, you need to specify either DETERMINISTIC or one of NO SQL or READS SQL DATA to create your function. This issue is examined in depth in Chapter 10.

NO SQL|CONTAINS SQL|READS SQL DATA|MODIFIES SQL DATA

Indicates the type of access to database data that the stored procedure will perform. If a program reads data from the database, you may specify the READS SQL DATA keyword. If the program modifies data in the database, you could specify MODIFIES SQL DATA. If the procedure or function performs no database accesses, you may specify NO SQL.^[*]

COMMENT comment_string

Specifies a comment that is stored in the database along with the procedure definition. You can see these comments in the INFORMATION_SCHEMA.ROUTINES table, in the output of SHOW PROCEDURE/FUNCTION STATUS, and in a SHOW CREATE PROCEDURE or SHOW CREATE FUNCTION statement.

The procedure code consists of one or more SQL or stored program language statements. If there is more than one statement—and there almost always will be—then the statements must be enclosed in a BEGIN-END block.

CREATE FUNCTION

The CREATE FUNCTION statement creates a stored function. This statement has a very similar syntax to CREATE PROCEDURE:

CREATE FUNCTION function_name ([parameter[,...])

RETURNS datatype

[LANGUAGE SQL]

[ [NOT] DETERMINISTIC ]

[ { CONTAINS SQL|NO SQL|MODIFIES SQL DATA|READS SQL DATA} ]

[SQL SECURITY {DEFINER|INVOKER} ]

[COMMENT comment_string]function_statements

There are only a few fundamental differences between the syntax of CREATE PROCEDURE and that of CREATE FUNCTION:

§ CREATE FUNCTION includes a mandatory RETURNS statement that specifies the data type that will be returned from the function call.

§ With CREATE FUNCTION, you cannot specify the IN, OUT, or INOUT modifiers to parameters. All parameters are implicitly IN parameters.

§ The function body must contain one or more RETURN statements, which terminate function execution and return the specified result to the calling program.

We look at stored functions in detail in Chapter 10.

CREATE TRIGGER

The CREATE TRIGGER statement creates a trigger. Its syntax follows:

CREATE [DEFINER = { user|CURRENT_USER }] TRIGGER trigger_name

{BEFORE|AFTER}

{UPDATE|INSERT|DELETE}

ON table_name

FOR EACH ROWtrigger_statements

As with other stored programs, the trigger name must conform to the general rules for naming objects, as outlined in Chapter 3. There are several differences between this statement syntax and that of CREATE PROCEDURE and CREATE FUNCTION:

DEFINER

This optional clause specifies the security privileges that the trigger code will assume when it is invoked. The default CURRENT_USER setting results in the trigger executing with the privileges of the account that executes the CREATE TRIGGER statement. Specifying a user allows the trigger to execute with the privileges of another account.

BEFORE or AFTER

These clauses control the sequence in which the trigger will fire—either before or after the triggering statement is executed.

UPDATE, INSERT, or DELETE

These clauses specify the type of DML statement that will cause the trigger to be invoked.

trigger_statements

This code can be one or more stored program language statements. If more than one statement is specified, they must all be contained within a BEGIN-END block.

Triggers are described in detail in Chapter 11.

ALTER PROCEDURE/FUNCTION

You can use the ALTER statement to change the SQL SECURITY characteristic of a stored procedure or stored function, or to change the comment associated with the procedure or function. This statement cannot currently be issued for triggers. The syntax of this statement is shown below:

ALTER {PROCEDURE|FUNCTION} procedure_or_function_name

[SQL SECURITY {DEFINER|INVOKER}]

[COMMENT comment_string ]

DROP PROCEDURE/FUNCTION/TRIGGER

You can use the DROP statement to remove a stored procedure, function, or trigger from the database:

DROP {PROCEDURE|FUNCTION|TRIGGER} [IF EXISTS] program_name

IF EXISTS is only valid for stored procedures and functions, not for triggers.

We frequently include a DROP PROCEDURE IF EXISTS statement in the same source file as our CREATE statement to remove the previous definition of the procedure before creating the new version (see Figure 7-10 for an example of this).