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 ;