MySQL 5 Stored Procedures Cheat Sheet

I will add more when I stumble upon commands I can’t remember the syntax of.

To list all stored procedures:


mysql> SHOW PROCEDURE STATUS;

Example output (with cropped dates and times for readable table):

Db Name Type Definer Modified Created Security_type Comment
mydb store_user PROCEDURE root 2007-0.. 2007-0.. DEFINER
mydb store_cart PROCEDURE root 2007-0.. 2007-0.. DEFINER
mydb store_settings PROCEDURE root 2007-0.. 2007-0.. DEFINER


To create a new procedure


mysql> DELIMITER $$
mysql> CREATE PROCEDURE adduser(IN username VARCHAR(32), IN emailaddress VARCHAR(32))
BEGIN
INSERT INTO users VALUES (username, emailaddress);
END
$$

To show a stored procedure


mysql> SHOW CREATE PROCEDURE `userdb`.`adduser`\\G

Example output:

*************************** 1. row ***************************
Procedure: adduser
sql_mode:
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `adduser`(IN username varchar(32), IN emailaddress(32))
BEGIN
INSERT INTO users VALUES (username, emailaddress);
END
1 row in set (0.00 sec)

To call a stored procedure from Java


Connection con = DriverManager.getConnection(url, user, password);
CallableStatement statement = con.prepareCall(\"{call userdb.adduser(?, ?)}\");
int i = 0;
statement.setString(i++, \"myuser\");
statement.setString(i++, \"myuser@spammealot.com\");
statement.execute();

For this to work you must give the user SELECT privilege to the proc table in the MySQL database, where the stored procedures are stored. Do it like this:

GRANT SELECT ON `mysql`.`proc` TO \'mysqluser\'@\'myhost\';

For a list of more commands, see my MySQL Commands Cheat Sheet.