mysql存储过程中as_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) astable1;END &&DELIMITER ;

存储函数

注意 最后的END使用&&结束

DELIMITER&&

CREATE FUNCTION `record_auto_update`(dayIndex varchar(4)) RETURNS varchar(11) CHARSET utf8 READS SQL DATA DETERMINISTICBEGIN

SET @dateS = date_add(curdate(), interval dayIndex day);SET @dateE = date_add(@dateS, interval 1 day);

#获取日期作为统计IDSET @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@dateS AND paid_at@dateS AND paid_at@dateS AND paid_at

#如果某种支付类型没有订单，会导致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} '名称';