Check information_schema.routines : Routines : Data Dictionary MySQL TUTORIALS


MySQL TUTORIALS » Data Dictionary » Routines »

 

Check information_schema.routines


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, 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                                                                                                                                                                |
+-----------------------------------------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
rows in set (0.01 sec)

mysql>
mysql> drop PROCEDURE myProc;
Query OK, rows affected (0.00 sec)



Leave a Comment / Note


 
Verification is used to prevent unwanted posts (spam). .

Follow Navioo On Twitter

MySQL TUTORIALS

 Navioo Data Dictionary
» Routines