Main Content

Learn about MySQL stored procedures

Archive - Originally posted on "The Horse's Mouth" - 2009-10-09 20:26:41 - Graham Ellis

Current releases of MySQL include "stored procedures". What are they?

You may start by considering stored procedures to be "Macros" - a series of commands bunched together as one, or a complex command which is held within the MySQL daemon and called up by just a simple call. But they then go much further than that, with variables, conditionals, functions and loops ...

Let's set up and use a simple stored procedure:

mysql> DELIMITER !!!
mysql>
mysql> CREATE PROCEDURE orgtypecount() READS SQL DATA
    -> BEGIN
    -> SET @what = 'SELECT count(oid) from orgtype';
    -> PREPARE summat FROM @what;
    -> EXECUTE summat;
    -> END;
    ->
    -> !!!
Query OK, 0 rows affected (0.00 sec)
 
mysql>
mysql> DELIMITER ;

That's a procedure called "orgtypecount" which counts the number of rows in a table called "orgtype" which have a NOT NULL value in the oid column. But you don't have to type the full command in every time you run it now that it's a stored procedure:

mysql> call orgtypecount();
+------------+
| count(oid) |
+------------+
|          4 |
+------------+
1 row in set (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
mysql>

Wasn't that easy ;-) ... well, as a starter it was, but stored procedures have a complete programming language to them. From a niche interest a while ago, they're becoming more important as time passes, and I'm now adding a handful of examples to our public MySQL course and I'm happy to go into further details on private courses.