mysql stored procedures, functions

Well, MySQL 5.0 introduced stored procedures and functions.

Most people who are php programmers though, don’t know how to use them. nor will they ever bother. Why? The docs are simply to thin, scanty, and they definitely do not have enough examples. Some sections of the docs provide NO examples — for instance, check out the entry for declaring variables: You may declare and use variables within a routine.

That’s IT???

What’s annoying is when you go to the DECLARE variable section, all you see is:

DECLARE var_name[,...] type [DEFAULT value]

That’s ALL? What about a basic example?

Declaring and using variables in MySQL 5.0+

The first thing I’ll tell you that the docs don’t seem to is that YOU MUST DECLARE YOUR VARIABLES AT THE TOP OF THE BLOCK / ROUTINE THAT THEY ARE CONTAINED IN.

For instance, this stored function will work fine:

DELIMITER //
DROP FUNCTION IF EXISTS test //
CREATE FUNCTION test ( input_int int )
	RETURNS int
BEGIN
DECLARE var_returned int;

IF input_int > 20 THEN
	SET var_returned = 50;
END IF;

   return var_returned;
END //
DELIMITER ;

This one will NOT on account of the red line:

DELIMITER //
DROP FUNCTION IF EXISTS test //
CREATE FUNCTION test ( input_int int )
	RETURNS int
BEGIN
DECLARE var_returned int;

IF input_int > 20 THEN
	SET var_returned = 50;
END IF;

DECLARE second_var int;

   return var_returned;
END //
DELIMITER ;

But again this one will:

DELIMITER //
DROP FUNCTION IF EXISTS test //
CREATE FUNCTION test ( input_int int )
	RETURNS int
BEGIN
DECLARE var_returned int;
DECLARE second_var int;


IF input_int > 20 THEN
	SET var_returned = 50;
END IF;


   return var_returned;
END //
DELIMITER ;

BTW, you would try out the stored procedure listed above by:

mysql> SELECT test( 3 );
+-----------+
| test( 3 ) |
+-----------+
|      NULL |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT test( 500 );
+-------------+
| test( 500 ) |
+-------------+
|          50 |
+-------------+
1 row in set (0.00 sec)

Another tip… there’s a wicked-awesome post that explains how to do updates with a join @ this post.