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)