mysql-存储过程
http://blog.sina.com.cn/s/blog_86fe5b440100wdyt.html
① 声明存储过程,
名称为Pro_name,带有 类型为datetime 的输入参数i_param_name 和类型为varchar(8)的输出参数 o_param_name的存储过程
CREATE PROCEDURE Pro_name(IN `i_param_name` datetime , OUT `o_param_name` varchar(8))
② DELIMITER // 声明语句结束符,用于区分;
③ BEGIN .... END 存储过程开始和结束符号
④ DECLARE w_varshiftno varchar(50); 变量定义
⑤ set shiftno= w_varshiftno; 变量赋值
⑥ 参数输入必须加单引号 'i_param_value'
eg 1 ,带有输入输出的存储过程
-- ---------------------------- -- Procedure structure for usp_Getshifts -- ---------------------------- DROP PROCEDURE IF EXISTS `usp_Getshifts`; DELIMITER ;; CREATE DEFINER=`root`@`%` PROCEDURE `usp_Getshifts`(IN `nowtime` datetime , OUT `shiftno` varchar(8)) BEGIN DECLARE w_varshiftno varchar(50); DECLARE w_time varchar(50); DECLARE w_varcnt int; set w_varcnt=0; set w_time=date_format(CAST(nowtime AS datetime),'%H:%i:%s'); set w_varcnt=(select count(sft_no) as cnt from m_Shifts where status=1 and begintime<=w_time and overtime>w_time ); if (w_varcnt>0) then set w_varshiftno=(select sft_no from m_Shifts where status=1 and begintime<=w_time and overtime>w_time ); else set w_varshiftno=(select sft_no from m_Shifts where status=1 and begintime> overtime and !(begintime<=w_time and overtime>w_time)); end if; set shiftno= w_varshiftno; END ;; DELIMITER ;
eg 2
-- ---------------------------- -- Procedure structure for usp_Del -- ---------------------------- DROP PROCEDURE IF EXISTS `usp_Del`; DELIMITER ;; CREATE DEFINER=`root`@`%` PROCEDURE `usp_Del`(IN `i_db_name varchar(30))
BEGIN
/**构建sql语句,方法 CONCAT(str1,str2,...)为连接字符串*/ SET @SqlCmd =CONCAT( 'DROP TABLE IF EXISTS ', i_db_name,'.t_checkdetail_l01'); PREPARE stmt FROM @SqlCmd; /*执行SQL*/ EXECUTE stmt ;
END ;; DELIMITER ;