MySQL存储过程和存储函数

存储过程和存储函数

MySQL的存储过程（stored procedure）和函数（stored function）统称为stored routines。

 

1.存储过程和函数的区别

函数只能通过return语句返回单个值或者表对象。而存储过程不允许执行return，但是通过out参数返回多个值。 函数是可以嵌入在sql中使用的，可以在select中调用，而存储过程不行。

函数限制比较多，比如不能用临时表，只能用表变量．还有一些函数都不可用等等．而存储过程的限制相对就比较少

一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。

当存储过程和函数被执行的时候，SQL Manager会到procedure cache中去取相应的查询语句，如果在procedure cache里没有相应的查询语句，SQL Manager就会对存储过程和函数进行编译。

Procedure cache中保存的是执行计划 (execution plan) ，当编译好之后就执行procedure cache中的execution plan，之后SQL SERVER会根据每个execution plan的实际情况来考虑是否要在cache中保存这个plan，评判的标准一个是这个execution plan可能被使用的频率；其次是生成这个plan的代价，也就是编译的耗时。保存在cache中的plan在下次执行时就不用再编译了。

 

2.创建存储过程和函数例句（自己实际场景中使用的）

存储过程
为了避免存储SQL中分号冲突，使用DELIMITER &&将MySQL结束符设置为&&，最后用DELIMITER ;恢复回来
注意 DELIMITER &&和DELIMITER ;之间的空格，不能省略，否则不会生效
注意 最后的END使用&&结束

DELIMITER &&
CREATE PROCEDURE `history_order_user`(OUT history_order_count int, OUT history_order_user int, OUT pay_again_user_count int)
BEGIN
select count(*) INTO history_order_count from t_payment_reckoning where pay_status=1;    
select count(distinct(openid)) INTO history_order_user from t_payment_reckoning where pay_status=1; 
select count(*) INTO pay_again_user_count from (select openid as mac from t_payment_reckoning where pay_status=1 group by openid having count(openid)>1 order by count(openid) desc) as table1;
END &&
DELIMITER ;

存储函数
注意 最后的END使用&&结束

DELIMITER &&
CREATE FUNCTION `record_auto_update`(dayIndex varchar(4)) RETURNS varchar(11) CHARSET utf8 READS SQL DATA DETERMINISTIC
BEGIN
SET @dateS = date_add(curdate(), interval dayIndex day);
SET @dateE = date_add(@dateS, interval 1 day);

#获取日期作为统计ID
SET @id = DATE_FORMAT(@dateS,'%y%m%d');

#分别查询4种支付类型订单总数和订单总额
SELECT COUNT(*),SUM(amount) INTO @WapWeixinCount,@WapWeixinAmount FROM t_payment_reckoning WHERE payment_type="WapWeixin" AND paid_at>@dateS AND paid_at<@dateE AND pay_status=1;
SELECT COUNT(*),SUM(amount) INTO @WapAlipayCount,@WapAlipayAmount FROM t_payment_reckoning WHERE payment_type="WapAlipay" AND paid_at>@dateS AND paid_at<@dateE AND pay_status=1;
SELECT COUNT(*),SUM(amount) INTO @PcWeixinCount,@PcWeixinAmount FROM t_payment_reckoning WHERE payment_type="PcWeixin" AND paid_at>@dateS AND paid_at<@dateE AND pay_status=1;
SELECT COUNT(*),SUM(amount) INTO @PcAlipayCount,@PcAlipayAmount FROM t_payment_reckoning WHERE payment_type="PcAlipay" AND paid_at>@dateS AND paid_at<@dateE AND pay_status=1;

#如果某种支付类型没有订单，会导致SUM(amount)为空
IF @WapWeixinAmount IS NULL THEN SET @WapWeixinAmount = 0; END IF;
IF @WapAlipayAmount IS NULL THEN SET @WapAlipayAmount = 0; END IF;
IF @PcWeixinAmount IS NULL THEN SET @PcWeixinAmount = 0; END IF;
IF @PcAlipayAmount IS NULL THEN SET @PcAlipayAmount = 0; END IF;

#将4种支付类型累计
SET @todayCount  = @WapWeixinCount+@WapAlipayCount+@PcWeixinCount+@PcAlipayCount;
SET @todayAmount = @WapWeixinAmount+@WapAlipayAmount+@PcWeixinAmount+@PcAlipayAmount;

#拼接4种类型的详细信息
SET @todayInfo = CONCAT(
"{'WapWeixin':{'count':",@WapWeixinCount,",'amount':",@WapWeixinAmount,"},'WapAlipay':{'count':",@WapAlipayCount,",'amount':",@WapAlipayAmount,
"},'PcWeixin':{'count':",@PcWeixinCount,",'amount':",@PcWeixinAmount,"},'PcAlipay':{'count':",@PcAlipayCount,",'amount':",@PcAlipayAmount,"}}"
);

#判断是新增还是更新
SELECT COUNT(*) INTO @idExist FROM t_payment_record WHERE id=@id;

IF @idExist>0 THEN
    UPDATE t_payment_record SET count=@todayCount,amount=@todayAmount,info=@todayInfo,updated_at=CURRENT_TIMESTAMP WHERE id=@id;
    RETURN "update";
ELSE
    INSERT INTO t_payment_record (id,count,amount,info,created_at,updated_at) VALUE (@id,@todayCount,@todayAmount,@todayInfo,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP);
    RETURN "insert";
END IF;

END &&
DELIMITER ;

 

3.查询存储过程和函数的状态，定义

　　SHOW {PROCEDURE|FUNCTION} STAUS [LIKE '名称'];

　　SHOW CREATE {PROCEDURE|FUNCTION} '名称';

 

4.删除存储过程和函数

　　DROP {PROCEDURE|FUNCTION} '名称';