Advanced Use of MySQL Stored Procedures

Date Published: 24/06/2009 13:21

A short while ago I wrote an article about using stored procedures in MySQL and extending MySQLi in PHP to execute them. I'm now going quickly cover some more advanced techniques you can use to reduce the amount of data transferred between database and application.

Recap of the Basics

First a quick recap of the basics for people who didn't read my original article. Stored procedures are pieces of SQL code stored on the database server which can be called by name from clients. Stored procedures can handle executing multiple SQL commands, taking parameters and returning datasets. Stored procedures have been around in other database systems for many years but not until recently has this functionality been available in MySQL. The format of an SQL query to create a stored procedure looks something like the below.

DELIMITER $$

CREATE PROCEDURE `sdpExampleProcedure`
   (
      parameter1 VARCHAR(255),
      parameter2 INTEGER
   )
   BEGIN
      /*
      SQL Queries split witn semi colons (;)
      using parameter 1 and parameter 2.
      */
   END$$

DELIMITER ;

Note the delimiter commands at the start and finish which changes how MySQL splits your command up. Then to call the above procedure you can use the following command.

CALL `sdpExampleProcedure`('testdata', '11');

What can I do with Stored Procedures and Why?

Stored procedures can be used to group SQL commands and add logic to your SQL statements, whilst reducing the quantity of data transferred between your application and your database. For example, managing session in a PHP web application. If you want to update the session record when a request is made for an existing session, or create new session altogether you could do all this along with checks in one stored procedure. This would normally consist of using a SELECT command to get all the request data from the database regarding the given session ID, then programming your code to execute an INSERT or an UPDATE command depending on how many rows came back. With stored procedures you can include all this in one simple SQL command which simply returns true for when a new session is made, and false for when a session is updated.

Declaring and Setting Variables in Your Stored Procedures

In addition to the parameters defined, which are passed during the call it is possible to define variables to work with during execution. To do this you can use the DECLARE command, stating the name and data type of the variable. In the example below I define a three variables called "session_count", "session_id" and "session_content", of data types INTEGER, VARCHAR (with length of 32) and TEXT respectively.

DECLARE session_count INTEGER;
DECLARE session_id VARCHAR(32);
DECLARE session_content TEXT;

Now you have declared your variables you can set them values using the SET command. Once these variables have a value you can use them later on in your procedure.

SET session_count = 1;
SET session_id = '699d571326815e40b8e1ae99af04563c';
SET session_content = 'Here is some session content';

If you know the value of your variable at the time when you declare it, you can use the DEFAULT command to set it. Below I have condensed the above 6 commands in to just 3 using this technique.

DECLARE session_count INTEGER DEFAULT 1;
DECLARE session_id VARCHAR(32) DEFAULT '699d571326815e40b8e1ae99af04563c';
DECLARE session_content TEXT DEFAULT 'Here is some session content';

Giving Variables Values from SELECT Queries

One excellent use of variables in your stored procedures is to use the result of a SELECT query to set the value of an existing variable. In other database systems this is quite simple, however in MySQL although it is simple, the syntax is some what unexpected. In order to put a value from a SELECT query into a variable there is an extra line which needs adding to the SELECT query just before the definition of which table the data will be pulled from. The INTO command puts the data into the variable defined. Here is an example to illustrate its use.

DECLARE session_count INTEGER;

SELECT COUNT(*)
INTO session_count
FROM `tblSessions`;

The session_count variable now has the value returned from the SELECT query counting the number of rows in the sessions table. It is also possible to feed values into multiple variables such as the example below.

DECLARE user VARCHAR(15);
DECLARE pass VARCHAR(32);

SELECT `strUserName`, `strPassword`
INTO user, pass
FROM `tblUsers`
LIMIT 0, 1;

Controlling the Flow of Your Stored Procedure

Now we have set up variables and assigned them values, we can control the flow of our procedure tp decide where to go next. MySQL supports a handful of flow controls which you will be used to seeing in fully implemented programming languages.

The IF Statement

The IF statement is of course the most widely known flow control and is very similar in MySQL to anything else. The command starts with IF, followed by a logic operation returning true or false, with the first line ending in THEN. Actions which need to happen after returning true can then be defined with an ELSE command being used if needed. The IF statement is ended with the command END IF; (make a note of the semi-colon, a common trouble maker). The below statement checks is the variable "session_count" is more than 0, if so returns a dataset of true, otherwise returns false.

IF session_count > 0 THEN
   SELECT 1;
ELSE
   SELECT 0;
END IF;

The CASE Statement

MySQL includes an implementation of the switch case like most mainstream programming languages. There is no drop through between cases like in languages such as PHP but it does have a default case for when no conditions are matched. Here a switch case is attempting to catch a variable called "catchme" based on its conditions. The default case would be called, as "catchme" does not match any of the conditions defined within the switch case.

DECLARE catchme INTEGER DEFAULT 10;

CASE catchme
   WHEN 2 THEN SET catchme = catchme + 2;
   WHEN 5 THEN SET catchme = catchme + 5;
   ELSE
      SET catchme = 50;
END CASE

The WHILE Loop

The while loop, a highly useful, common tool used in most programming languages ever made by man. MySQL is no exception. The syntax is very similar to most making it easy to use but here is a summary. The main keywords you need to remember for using a MySQL while loop are WHILE, DO and END WHILE;. When defining a while loop you can give it a label which is used by further commands to control the loop. The label goes before the while loop definition and after the loop end. The following simple example creates a WHILE loop called "iterwhile" which iterates while the variable called "iter" is less than 9.

DECLARE iter INTEGER DEFAULT 0;

iterwhile: WHILE iter < 9 DO
   SET iter = iter + 1;
END WHILE iterwhile;

The LOOP Loop

The LOOP command very simply does just that. It loops infinitely until you command it to stop using the LEAVE command. Like the WHILE loop it can be named with a label in its definition so that it can be targeted by later commands in order to control it. Here is a LOOP called "iterloop" which does the same as the WHILE example above, but uses an IF statement and the LEAVE command to stop.

DECLARE iter INTEGER DEFAULT 0;

iterloop: LOOP
   IF iter < 9 THEN
      SET iter = iter + 1;
   ELSE
      LEAVE iterloop;
   END IF;
END LOOP iterloop;

The REPEAT Loop

The REPEAT command is essentially a WHILE loop which checks if the condition is matched at the end of each iteration as opposed to at the start. This means that when you use a REPEAT loop the statements within that loop will always be executed at least once. Heres the REPEAT equivalent of the WHILE and LOOP commands above.

DECLARE iter INTEGER DEFAULT 0;

iterrepeat: REPEAT
   SET iter = iter + 1;
UNTIL iter < 9
END REPEAT iterrepeat;

The LEAVE and ITERATE Commands

The LEAVE and ITERATE commands are similar to the break and continue commands used in many other programming languages. The can be applied to any WHILE, LOOP or REPEAT loops which are currently active by referencing the label given to the loop. The below uses both LEAVE and ITERATE commands within a LOOP called "testloop".

DECLARE iter INTEGER DEFAULT 0;

testloop: LOOP
   IF iter = 10 THEN
      SET iter = iter + 12;
      ITERATE testloop;
   END IF;
   IF iter > 126 THEN
      LEAVE testloop;
   END IF;
   SET iter = iter + 1;
END LOOP testloop;

Conclusion

A mixture of these commands can really add some power to your use of SQL. Not only does it allow you to reduce network traffic but you can also reduce the number of calls made to your database significantly, making things that little better organised. If you are just starting out using these techniques and are having difficulty with SQL syntax errors make sure you check your commands individually before you put them all together into one procedure. Keep an eye on your semi-colons (;) too, they can cause a whole heap of problems when not used in the right place so ensure you can tell one command from another.

Comments

Sorry comments are currently disabled for maintenence

5 Most Recent Articles

Manually Triggering Events in ASP.NET from JavaScript

A quick guide for ASP.NET developers on how to manually trigger ASP.NET events from JavaScript.

Advanced Use of MySQL Stored Procedures

An article for users of MySQL databases describing how they can use advanced stored procedures to improve efficiently in their applications.

Using MySQL Stored Procedures and Extending MySQLi in PHP

A guide for LAMP developers to using stored procedures in MySQL and extending the MySQLi class.

Reading and Writing to Excel Spreadsheets in Python

An introduction to using the xlwt and xlrd modules for python to interact with Microsoft Excel spreadsheets.

Interact with the Web Using Python and the HTTP Library

This is an introduction to making HTTP requests from a python script/application using httplib.

Sponsors