MySQL 5.0 New Features: Stored Procedures

[admin]

MySQL 5.0 New Features: Stored Procedures

By Peter Gulutzan

Introduction
This 67 page guide is for long-time MySQL users who want to know "what's new" in version 5. The short answer is "stored procedures, triggers, views, information_schema". The long answer is the MySQL 5.0 New Features series, and this book is the first in the series.

What I'm hoping to do is make this look like a hands-on session where you, as if you're working it out yourself on your keyboard, can walk through sample problems.

To do this, I'll go through each little item, building up slowly. By the end, I'll be showing larger routines that do something useful, something that you might have thought was tough.

A Definition and an Example
A stored procedure is a procedure (like a subprogram in a regular computing language) that is stored (in the database). Correctly speaking, MySQL supports "routines" and there are two kinds of routines: stored procedures which you call, or functions whose return values you use in other SQL statements the same way that you use pre-installed MySQL functions like pi(). I'll use the word "stored procedures" more frequently than "routines" because it's what we've used in the past, and what people expect us to use.

A stored procedure has a name, a parameter list, and an SQL statement, which can contain many more SQL statements. There is new syntax for local variables, error handling, loop control, and IF conditions. Here is an example of a statement that creates a stored procedure.

Code: [Select]

CREATE PROCEDURE procedure1                /* name */
(IN parameter1 INTEGER)                    /* parameters */
BEGIN                                      /* start of block */
  DECLARE variable1 CHAR(10);                /* variables */
  IF parameter1 = 17 THEN                    /* start of IF */
    SET variable1 = 'birds';                   /* assignment */
  ELSE
    SET variable1 = 'beasts';                  /* assignment */
  END IF;                                   /* end of IF */
  INSERT INTO table1 VALUES (variable1);    /* statement */
END                                       /* end of block */
What I'm going to do is explain in detail all the things you can do with stored procedures. We'll also get into another new database object, triggers, because there is a tendency to associate triggers with stored procedures.

Why Stored Procedures
Stored procedures are something new for MySQL, so naturally you'll approach them with some caution. After all, there's no track record, no large body of user experience that proves they're the way to go. Nevertheless, you should start to think now about moving your code out of where it is now (an application host program, a UDF, a script), and into a stored procedure. The reasons for using procedures are compelling.

Stored procedures are proven technology! Yes, they are new in MySQL, but the same functionality exists in other DBMSs, and often precisely the same syntax too. So there are concepts that you can steal, there are people with experience whom you can consult or hire, there is third-party documentation (books or web pages) that you can read.

Stored procedures are fast! Well, we can't prove that for MySQL yet, and everyone's experience will vary. What we can say is that the MySQL server takes some advantage of caching, just as prepared statements do. There is no compilation, so an SQL stored procedure won't work as quickly as a procedure written with an external language such as C. The main speed gain comes from reduction of network traffic. If you have a repetitive task that requires checking, looping, multiple statements, and no user interaction, do it with a single call to a procedure that's stored on the server. Then there won't be messages going back and forth between server and client, for every step of the task.

Stored procedures are components! Suppose that you change your host language -- no problem, the logic is in the database not the application.

Stored procedures are portable! When you write your stored procedure in SQL, you know that it will run on every platform that MySQL runs on, without obliging you to install an additional runtime-environment package, or set permissions for program execution in the operating system, or deploy different packages if you have different computer types. That's the advantage of writing in SQL rather than in an external language like Java or C or PHP. Don't get me wrong about this: I know there are sometimes excellent reasons to support external-language routines, they just lack this particular advantage.

Stored procedures are stored! If you write a procedure with the right naming conventions, for example saying chequing_withdrawal for a bank transaction, then people who want to know about chequing can find your procedure. It's always available as 'source code' in the database itself. And it makes sense to link the data with the processes that operate on the data, as you might have heard in your programming-theory classes.

Stored procedures are migratory! MySQL adheres fairly closely to the SQL:2003 standard. Others (DB2, Mimer) also adhere. Others (Oracle, SQL Server) don't adhere but I'll be providing tips and tools that make it easier to take code written for another DBMS and plunking it into MySQL.

Read On
The whole article is long, so we thought it better to make it a PDF. To download, click here (no registration required!) You could even print out the PDF so you can peruse at your leisure.