Skip navigation

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.

One Comment

  1. You are of course absoutely right, most php coders wont use stored procedures, triggers, etc – how those that have studied (eg understand) database principles will definitly for very obvious reasons.. Personally I have come to PHP from C++, so I definitly will!!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: