MySQL Stored Procedures 101

Why solve one problem when you can solve two? I’d heard a lot about how stored procedures(sprocs) in MySQL were pants but I’d never tried them myself. Then one day while talking to my friendly DBA he said they were ok to use now. I asked around and most devs I talked to warned against using them but I had a problem and sprocs seemed like an elegant solution.

It turns out they were a great solution but there wasn’t a lot of information about how to use them hence this article.

My basic problem was that I wanted to insert in to several tables using data that I’d just inserted in to several other tables. This would have been possible in PHP but I felt it was overly complicated and prone to errors. Step in sprocs.

What is a sproc? According to MySQL it is a stored routine. There are two types of stored routines: functions and sprocs. Functions return scalar values, can be called from inside a statement, can not be recursive (by default), and don’t allow queries to be run.

Sprocs on the other hand have no return value, have multiple INOUT params, and can be recursive. There are a couple of other differences around the routine being deterministic or returning result sets but that’s a bit too deep for now.

The basic structure for a sproc is:

CREATE PROCEDURE name(inout params)
BEGIN
DECLARE variables
DECLARE CURSORS
DECLARE handlers
STATMENTS
END

A very simple stored procedure is:

CREATE PROCEDURE example (IN p_counter INT)
BEGIN
END

Which can be called like this:

CALL example(10);

Let’s take a more concrete example where we have three tables: one for countries, one for continents and a join table between them. When a row is inserted into the country table I want to automatically join it to the continent table. The final schema defintion is up on gist.

Database EERD

A couple of rules before starting:

  • scope resolution sucks: prefix params with p and local variables with v
  • there are no arrays; use comma separated strings and treat as sets
  • error handling is a pain
  • syntax isn’t great so keep sprocs short
  • always run a query at the end as some implementations complain if nothing is returned
  • cast expressions before assignment

Implementation details:

  • one stored procedure taking two IN params: continent id, country name
  • foreign key constraints will prevent countries being added to continents that don’t exist
  • two queries: insert into Country and CountriesInContinent
CREATE PROCEDURE addCountry
(
IN p_idContinent SMALLINT,
IN p_countryName VARCHAR(100)
)
BEGIN
DECLARE v_lastInsertId MEDIUMINT;
INSERT INTO Country SET name = p_countryName;
SET v_lastInsertId = LAST_INSERT_ID();
INSERT INTO
CountriesInContinent
SET
idContinent = p_idContinent,
idCountry = v_lastInsertId;
SELECT v_lastInsertId;
END

This will cover off most cases but if the continent id is incorrect a country will be inserted and not associated with a continent.

mysql> CALL addCountry(99, 'Spain');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`places`.`countriesincontinent`, CONSTRAINT `fk_idContinent` FOREIGN KEY (`idContinent`) REFERENCES `Continent` (`idContinent`) ON DELETE CASCADE ON UPDATE NO ACTION)
mysql> CALL addCountry(2, 'Ireland');
+----------------+
| v_lastInsertId |
+----------------+
| 7 |
+----------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT count(*) FROM Country;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)

The fix for this is to wrap the INSERT statement in a transaction and to add error handlers as well.

Error handlers are required for exceptions and warnings. The ROLLBACK will abort the transaction and the RESIGNAL bubbles up the error message.

DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
ROLLBACK;
RESIGNAL;
END;

Now the sproc looks something like this:

CREATE PROCEDURE `addCountry`(
IN p_idContinent SMALLINT,
IN p_countryName VARCHAR(100)
)
BEGIN
DECLARE v_lastInsertId MEDIUMINT;
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
ROLLBACK;
RESIGNAL;
END;

START TRANSACTION;
INSERT INTO Country SET name = p_countryName;
    SET v_lastInsertId = LAST_INSERT_ID();
    INSERT INTO 
CountriesInContinent
SET
idContinent = p_idContinent,
idCountry = v_lastInsertId;
  COMMIT;
SELECT v_lastInsertId;
END

That’s the basic usage for stored procedures.