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 ;

　　

转载于:https://www.cnblogs.com/lhlong/p/5198195.html