MySQL Tutorial/Data Dictionary/routines
Check information_schema.routines
<source lang="sql">
mysql> mysql> mysql> DELIMITER $$ mysql> mysql> CREATE PROCEDURE myProc(
-> in_start_date DATE, -> in_end_date DATE) -> DETERMINISTIC -> BEGIN -> DECLARE l_first_name,l_last_name VARCHAR(60); -> DECLARE l_id INT DEFAULT 0; -> -> SELECT id,first_name,last_name -> FROM employee -> WHERE start_date BETWEEN in_start_date AND in_end_date; -> -> END $$
Query OK, 0 rows affected (0.00 sec) mysql> mysql> DELIMITER ; mysql> SELECT CONCAT(routine_schema,".",routine_name),
-> routine_type,routine_definition -> FROM information_schema.routines;
+-----------------------------------------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | CONCAT(routine_schema,".",routine_name) | routine_type | routine_definition | +-----------------------------------------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test.myProc | PROCEDURE | BEGIN
DECLARE l_first_name,l_last_name VARCHAR(60); DECLARE l_id INT DEFAULT 0; SELECT id,first_name,last_name FROM employee WHERE start_date BETWEEN in_start_date AND in_end_date;
END | | test.simpleproc | PROCEDURE | BEGIN
SELECT COUNT(*) INTO param1 FROM employee;
END | +-----------------------------------------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.01 sec) mysql> mysql> drop PROCEDURE myProc; Query OK, 0 rows affected (0.00 sec)</source>