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):

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++, \\\"[email protected]\\\");
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.