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.
(written 2009-10-09)

 S163 - Stored Procedures in MySQL
  [2447] MySQL stored procedures / their use on the web from PHP - (2009-10-10)
  [2749] Delegate Question - defining MySQL table relationships as you create the tables - (2010-05-02)

Back to Securing MySQL on a production server

Previous and next or Horse's mouth home

Forward to MySQL stored procedures / their use on the web from PHP

Family Gathering at 404, The Spa
Four aspects - Chamber, Transport, Courses and Hotel
MySQL - efficiency and other topics
Learn about MySQL stored procedures
Securing MySQL on a production server
Potted MySQL installation
Contrasting Cambridge, Bristol and Wiltshire
Variable storage - Perl, Tcl and Python compared
Not your cup of tea?