I will add more when I stumble upon commands I can’t remember the syntax of.
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 |
mysql> DELIMITER $$
mysql> CREATE PROCEDURE adduser(IN username VARCHAR(32), IN emailaddress VARCHAR(32))
BEGIN
INSERT INTO users VALUES (username, emailaddress);
END
$$
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)
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.