Featured Database Articles

MySQL Stored Procedures: Part 3

Introduction

In part 3 of the ongoing
series about MySQL's stored procedures, we look at handlers and cursors in
particular - both logical constructs that allow added functionality. Handlers
allow you to run statements if a certain condition is met, while cursors,
although only nominally supported in MySQL 5, allow looping through a
resultset, processing it row by row. If you have not yet done so, look at part
1 and part 2
of the series first, especially if stored procedures are new to you.

Handlers and error handling

With stored procedures
allowing the DBMS to grapple with concepts that beforehand were only dealt with
in the murkier programming world, there is a clear need for a more elegant way
of handling errors and exceptions. Enter the handler. There are two types of
handler supported by MySQL - EXIT handlers that immediately exit the current
BEGIN/END block, and CONTINUE handlers that allow processing to continue after
the handler actions have been performed (the UNDO handler that may be familiar
to users of other DBMS' is not yet supported). Below is an example. Remember
that we are still using the | character as a delimiter, as outlined in part 1
of the series.

So, what happened here?
We declared a condition, called 'unknown column'. It is a condition that occurs
when SQLSTATE 42S22 is reached, which is when there is an unknown column. You
can find a full list of error codes and messages on the MySQL site.
Next, we declare an exit handler for the 'unknown column' condition, declared
above. The handler simply displays the message error error whoop whoop. The actual body of the procedure
consists of two statements, SELECT aha,
which is designed to trigger SQLSTATE 42S22, and SELECT 'continuing', which is never actually executed as,
being an exit handler, the procedure is immediately exited when the condition
is met. So, when we call sp3(),
the SELECT statement triggers the condition, and the message is displayed. Let's
change this to use a CONTINUE handler, and see the difference.

As expected, the
procedure continues executing after the error, and this time the SELECT 'continuing' statement is run.

Here is another
procedure. What do you think it will do? If we want to display the error error and still handling messages as part of the
handler, after reaching the aha statement, and then continue with the continuing statement, will this achieve
that?

The answer is clearly no.
I hope that you were eagle-eyed enough to spot the misleading indentation. The SELECT 'still handling the error'; is
actually part of the main procedure body, and not part of the error handler.
Since we have no BEGIN or END statements as part of the handler,
it consists of the one statement only. Here is what will achieve what we
actually intended.

Variations and uses

For the examples above,
we declared a condition to detect an SQLSTATE error. There are other ways. Firstly,
handlers can be declared for specific error codes directly - you don't always
need to go via an intermediate condition, although doing so is more useful in
that the condition name can (and should) be descriptive, so there is no need to
refer to a list of error codes at a later stage. The error code can also either
be the SQLSTATE error number, as above, or the MySQL error code, as well as one
of the more generic SQLWARNING,
for all errors with an SQLSTATE beginning with 01, NOT FOUND for all errors with an SQLSTATE beginning with
02, or SQLEXCEPTION for all
others. Below is a procedure that acts in a similar manner to our earlier
examples. This time we use the MySQL Error code 1054, which is almost
equivalent to SQLSTATE 42S22, and we also skip the condition:

That's enough examples
for now - hopefully you are starting to consider some practical uses for this.
The handlers could ROLLBACK
statements, or log to an error table. Moreover, the statements could be as
complex as required, incorporating all the loops and conditions we looked at in
the previous article.