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 7Creating and Maintaining Stored Programs

Chapter 8Transaction Management

Chapter 9MySQL Built-in Functions

Chapter 10Stored Functions

Chapter 11Triggers

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.

Editing a stored program in Linux with Emacs

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.

Editing a stored program in Windows with Notepad

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).

Creating a stored procedure in the Query Browser (step 1)

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.

Creating a stored procedure in the Query Browser (step 2)

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.

Creating a stored procedure in the Query Browser (step 3)

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

Creating a stored procedure in the Query Browser (step 4)

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

Editing stored programs with Toad for MySQL

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.

Editing a stored program in place with the MySQL Query Browser

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.

Toad for MySQL provides integration with version control systems

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.

Maintaining stored program source code in a source 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).


[*A strict interpretation of the ANSI standard suggests that NO SQL is only applicable for non-SQL languages (PHP, Java, etc.). Although NO SQL is arguably only really intended for non-SQL stored procedures, the current behavior of MySQL makes the NO SQL clause the best choice when you must specify a SQL clause for a function that performs no database accesses.

Getting Information About Stored Programs

This section describes ways you can retrieve information about the stored programs that exist in your database.

In releases of MySQL prior to 5.0, extracting information about objects in the database was achieved by issuing SHOW statements. MySQL has extended the SHOW statement in version 5 to include information about stored programs.

However, in 5.0, MySQL also introduced the INFORMATION_SCHEMA database, which contains various tables that provide information about the objects that exist within the server. These tables are typically referred to as the data dictionary or as server metadata.

If you are a long-time user of the MySQL server, then using SHOW statements may seem a more natural approach to obtaining information about stored programs. However, the INFORMATION_SCHEMA tables—in addition to being ANSI standard—have the advantage of being amenable to various handy SELECT operations, such as grouping, counting, joining, and advanced filtering operations. You can also use INFORMATION_SCHEMA tables within your stored program code—something that is not practical with SHOW statement output.

SHOW PROCEDURE/FUNCTION STATUS

The SHOW PROCEDURE STATUS and SHOW FUNCTION STATUS statements return information about the stored programs within the server. The syntax of this form of the SHOW statement is:

    SHOW {PROCEDURE|FUNCTION} STATUS [LIKE pattern]

Figure 7-11 provides an example of SHOW PROCEDURE status output.

SHOW PROCEDURE STATUS

Figure 7-11. SHOW PROCEDURE STATUS

SHOW CREATE PROCEDURE/FUNCTION

The SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION statements return the CREATE statement necessary to re-create a particular stored program. Figure 7-12 shows the output of this version of SHOW. Note that we used the "View Field pop-up editor" right-click option to load the text output returned by this statement into a more readable Field Viewer window.

INFORMATION_SCHEMA.ROUTINES Table

The INFORMATION_SCHEMA.ROUTINES table returns a variety of information about stored procedures and functions. You can use the WHERE clause and column lists within the SELECT statement to format this output in various interesting ways.

SHOW CREATE FUNCTION

Figure 7-12. SHOW CREATE FUNCTION

This table does not contain information about triggers, but you can retrieve trigger information from the INFORMATION_SCHEMA.TRIGGERS table described in the next section.

Figure 7-13 shows the structure of the INFORMATION_SCHEMA.ROUTINES table.

You can use INFORMATION_SCHEMA.ROUTINES to return any of the data returned by the SHOW PROCEDURE STATUS, SHOW FUNCTION STATUS, SHOW CREATE PROCEDURE, and SHOW CREATE FUNCTION statements. For instance, in Figure 7-14, we produce a report that includes both the procedure/function definitions and other information about these programs.

INFORMATION_SCHEMA.TRIGGERS Table

The INFORMATION_SCHEMA.TRIGGERS table contains details about all of the triggers that are defined on the MySQL server. Figure 7-15 shows the output from a query against this table (using the "View Field pop-up editor" right-click option to view the contents of theaction_statement column).

Structure of the INFORMATION_SCHEMA.ROUTINES table

Figure 7-13. Structure of the INFORMATION_SCHEMA.ROUTINES table

Conclusion

In this chapter we looked at the process of creating and managing stored objects (procedures, functions, and triggers). Let's conclude with an outline of what we regard as the best practices for creating and managing stored objects:

§  Make sure that the reference (e.g., official) copy of each of your stored programs exists as a file on disk, not as the copy stored in the MySQL server. Stored programs might need to be shared between multiple servers, and you therefore need at least one copy—not on a server—that represents the current version.

§  Use a version control system to maintain a copy of any version of a stored program that is deployed to a MySQL server. In other words, subject stored program code to the same discipline that you apply to other program code.

§  When you are editing a stored program, check it out of the source control system and load the checked-out copy into the MySQL Query Browser or other tool.

Viewing the INFORMATION_SCHEMA.ROUTINES table

Figure 7-14. Viewing the INFORMATION_SCHEMA.ROUTINES table

§  When you are satisfied with your changes, save the stored program code to a disk file and check it into the version control system.

§  Deploy the stored program by creating command-line routines using the MySQL client program, and embed these into Make files or other build/deploy scripts that you can use to apply schema changes and other server object changes.

Viewing the INFORMATION_SCHEMA.TRIGGERS table

Figure 7-15. Viewing the INFORMATION_SCHEMA.TRIGGERS table