MySQL Tutorial/Data Dictionary/routines — различия между версиями

Материал из SQL эксперт
Перейти к: навигация, поиск
м (1 версия)
 
(нет различий)

Текущая версия на 09:53, 26 мая 2010

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