MySQL Stored Procedure Programming

What Is a Stored Program?

A database stored program—sometimes called a stored module or a stored routine—is a computer program (a series of instructions associated with a name) that is stored within, and executes within, the database server. The source code and (sometimes) any compiled version of the stored program are almost always held within the database server’s system tables as well. When the program is executed, it is executed within the memory address of a database server process or thread.

There are three major types of MySQL stored programs:

Stored procedures

Stored procedures are the most common type of stored program. A stored procedure is a generic program unit that is executed on request and that can accept multiple input and output parameters.

Stored functions

Stored functions are similar to stored procedures , but their execution results in the return of a single value. Most importantly, a stored function can be used within a standard SQL statement, allowing the programmer to effectively extend the capabilities of the SQL language.

Triggers

Triggers are stored programs that are activated in response to, or are triggered by, an activity within the database. Typically, a trigger will be invoked in response to a DML operation (INSERT, UPDATE, DELETE) against a database table. Triggers can be used for data validation or for the automation of denormalization.

Tip

Other databases offer additional types of stored programs , including packages and classes, both of which allow you to define or collect multiple procedures and functions within a single, named context. MySQL does not currently support such structures—in MySQL, each stored program is a standalone entity.

Throughout this book, we are going to use the term stored programs to refer to stored procedures, functions, and triggers , and the term stored program language to refer to the language used to write these programs. Most of the facilities in the stored program language are applicable across procedures, functions, and triggers; however, both functions and triggers have strict limitations on the language features that may be used with them. Thus, we dedicate a chapter to each of these program types in which we explain these limitations.

Why Use Stored Programs?

Developers have a multitude of programming languages from which to choose. Many of these are not database languages, which means that the code written in these languages does not reside in, nor is it managed by, a database server. Stored programs offer some very important advantages over more general-purpose languages, including:

• The use of stored programs can lead to a more secure database.

• Stored programs offer a mechanism to abstract data access routines, which can improve the maintainability of your code as underlying data structures evolve.

• Stored programs can reduce network traffic, because the program can work on the data from within the server, rather than having to transfer the data across the network.

• Stored programs can be used to implement common routines accessible from multiple applications—possibly using otherwise incompatible frameworks—executed either within or from outside the database server.

• Database-centric logic can be isolated in stored programs and implemented by programmers with more specialized, database experience.

• The use of stored programs can, under some circumstances, improve the portability of your application.

While this is an impressive list of advantages (many of which will be explored in greater detail in this book), we do not recommend that you immediately move all your application logic into stored programs. In today’s rich and complex world of software technology, you need to understand the strengths and weaknesses of each possible element in your software configuration, and figure out how to maximize each element. We spend most of Chapter 12 evaluating how and where to apply MySQL stored programs.

The bottom line is that, used correctly, stored programs—procedures, functions, and triggers—can improve the performance, security, maintainability, and reliability of your applications.

Subsequent chapters will explore how to construct MySQL stored programs and use them to best advantage. Before plunging into the details, however, let’s look at how the technology developed and take a quick tour of language capabilities.

A Brief History of MySQL

MySQL has its roots in an in-house (non-SQL) database system called Unireg used by the Swedish company TcX that was first developed in the 1980s and optimized for data warehousing. The author of Unireg, Michael “Monty” Widenius, added a SQL interface to Unireg in 1995, thus creating the first version of MySQL. David Axmark, from Detron HB , approached Monty proposing to release MySQL to the world under a “dual licensing” model that would allow widespread free use, but would still allow for commercial advantage. Together with Allan Larsson, David and Monty became the founders of the MySQL company.

The first widely available version of MySQL was 3.11, which was released in mid-1996. Adoption of MySQL grew rapidly—paralleling the adoption of other related open source technologies. By the year 2005, MySQL could lay claim to over 6 million installations of the MySQL database.

Version 3 of MySQL, while suitable for many types of applications (particularly read-intensive web applications), lacked many of the features normally considered mandatory in a relational database. For instance, transactions, views, and subqueries were not initially supported.

However, the MySQL system was designed to support a particularly extensible data access architecture, in which the SQL layer was decoupled from the underlying data and file access layer. This allowed custom “storage engines” to be employed in place of—or in combination with—the native ISAM (Indexed Sequential Access Method) -based MySQL engine. The Berkeley-DB (BDB ) database (from Sleepycat Software ) was integrated as an optional storage engine in version 3.23.34 in early 2001. BDB provided MySQL with its initial transaction processing capability. At about the same time, the open source InnoDB storage engine became available and quickly became a natively available option for MySQL users.

The 4.0 release in early 2002 fully incorporated the InnoDB option, making transactions easily available for all MySQL users, and also added improved replication capabilities. The 4.1 release in early 2004 built on the 4.0 release and included—among many other improvements—support for subqueries and Unicode character sets.

With the 5.0 release of MySQL in late 2005, MySQL took an important step closer to functional parity with commercial RDBMS systems; it introduced stored procedures , functions, and triggers , the addition of a data dictionary (the SQL-standard INFORMATION_SCHEMA), and support for updateable views.

The 5.1 release, scheduled for the second half of 2006, will add important factilities such as an internal scheduler, table partitioning, row-based replication, and many other significant enhancements.

MySQL Stored Procedures, Functions, and Triggers

MySQL chose to implement its stored program language within the MySQL server as a subset of the ANSI SQL:2003 SQL/PSM (Persistent Stored Module) specification. What a mouthful! Essentially, MySQL stored programs—procedures, functions, and triggers—comply with the only available open standard for these types of programs—the ANSI standard.

Many MySQL and open source aficionados had been hoping for a stored program language implementation based on an open source language such as PHP or Python. Others anticipated a Java?-based implementation. However, by using the ANSI specification—the same specification adopted within IBM’s DB2 database—MySQL has taken advantage of years of work done by the ANSI committee, which included representatives from all of the major RDBMS companies.

The MySQL stored program language is a block-structured language (like Pascal) that includes familiar commands for manipulating variables, implementing conditional execution, performing iterative processing, and handling errors. Users of existing stored program languages, such as Oracle’s PL/SQL or SQL Server’s Transact-SQL, will find features of the language very familiar. Programmers familiar with other languages, such as PHP or Java, might consider the language somewhat simplistic, but they will find that it is easy to learn and that it is well matched to the common requirements of database programming.