让我们首先创建一个存储过程-mysql>DELIMITER//
mysql>CREATEPROCEDUREExtenddatesWithMonthdemo(INdate1datetime,INNumberOfMonthint)
->BEGIN
->SELECTDATE_ADD(date1,INTERVALNumberOfMonthMONTH)ASExtendDate;
->END;
->//
mysql>DELIMITER;
现在,使用help SHOW CREATE命令检查存储过程是否存在。
查询如下-mysql>SHOWCREATEPROCEDUREExtenddatesWithMonthdemo;
Thefollowingistheoutputdisplayingthedetailsofthestoredprocedurewecreatedabove:
+--------------------------+--------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
|Procedure|sql_mode|CreateProcedure|character_set_client|collation_connection|DatabaseCollation|
+--------------------------+--------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
|ExtenddatesWithMonthdemo|STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION|CREATEDEFINER=`root`@`%`PROCEDURE`ExtenddatesWithMonthdemo`(INdate1datetime,INNumberOfMonthint)
BEGIN
SELECTDATE_ADD(date1,INTERVALNumberOfMonthMONTH)ASExtendDate;
END|utf8|utf8_general_ci|utf8_general_ci|
+--------------------------+--------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1rowinset(0.00sec)
借助CALL命令调用存储过程。查询如下-mysql>callExtenddatesWithMonthdemo('-02-13',6);
输出结果+---------------------+
|ExtendDate|
+---------------------+
|-08-1300:00:00|
+---------------------+
1rowinset(0.10sec)