MySQL存储过程二

上一节存储过程封装的都是简单的select语句，直接使用被封装的语句就能完成。所以存储过程往往应用于更复杂的业务规则处理时更有效

看一个例子

这个例子使用元素比较多，解释一下：

COMMENT为表添加了一句注释；

-- 单行注释，注释跟在后面的内容，需要注意-- 后需要加一个空格才能生效；

（#注释内容        /*注释内容*/  这两种方法也能进行注释）

Declare用来声明变量，一句declare只能声明一个变量，变量必须先声明后使用

If...Then是进行条件判断的，基本语句如下：

If ... Then ... Else ... End If

这个存储过程完成了订单合计，并判断该订单是否需要增加营业税。taxable是一个布尔值（如果要增税为真，否则为假）。在存储体中定义了两个局部变量。并将结果存储到局部变量total中。if语句检查taxable是否为真，如果为真，则用另一条select语句增加营业税。最后将total结果保存到ototal中。

调用结果如下：

检查存储过程

SHOW  CREATE  PROCEDURE  过程名

为了获得包括何时、由谁创建等详细信息的存储过程列表，使用

Show procedure status  -- 会列出所有存储过程

可以添加过滤，比如

存储过程循环语句

1. while

WHILE (表达式)  DO 

...

END WHILE

看一个例子

创建了一个循环存储过程，重复向human插入5条记录。下面调用看一下结果

2.repeat

基本语句：

Repeat ...until 条件...END  Repeat；

同样 *** 作，使用repeat执行如下

MySQL 存储过程中，使用游标查询，返回的是结果集时，如何查看调用存储过程输出结果呢？

解决方案：存储过程不返回数据，但它能创建和填充另一个表。所以在存储过程运行中创建临时表。该临时表将保存存储过程中生成的结果集，在遍历游标时，用insert保存每条数据到临时表中。后续调用时可以用select语句查询临时表中的存储过程运行结果。

以下有 三种方式 使用游标创建一个存储过程，统计某一部门下的员工信息

方法一：Loop循环

调用存储过程：

方法二：While 循环

调用存储过程：

方法三：REPEAT 循环

调用存储过程：

上述三种实现方法在测试过程中遇到下述问题。

调用存储过程查询临时表输出结果时，会发现多循环了一次，像这样：

解决方法：

在遍历游标查询结果时，先判断游标的结束标志（done） 是否是为1，如果不是1，则向临时表中插入数据。

下面是"Baidu百科"关于公历“闰年”词条的算法说明

“闰年是公历中的名词。

普通年能被4整除且不能被100整除的为闰年。（如2004年就是闰年,1900年不是闰年）；

世纪年能被400整除的是闰年。(如2000年是闰年，1900年不是闰年)；

对于数值很大的年份,这年如果能被3200整除,并且能被172800整除则是闰年。如172800年是闰年，86400年不是闰年(因为虽然能被3200整除，但不能被172800整除)。

闰年(Leap Year)是为了弥补因人为历法规定造成的年度天数与地球实际公转周期的时间差而设立的。补上时间差的年份为闰年。闰年共有366天（1-12月分别为31天，29天，31天，30天，31天，30天，31天，31天，30天，31天，30天，31天）。”

根据上面算法用MySQL存储过程判断某个年份是否为闰年，请参考下列实验（注意：由于MySQL时间日期类型的年份取值范围为1000~9999，因此对于上述关于大数值年份的算法予以忽略）

-- 创建自定义存储函数”Is_Leap_Year“，用来判定某个年份是否为公历闰年

Drop Function if exists Is_Leap_Year

CREATE FUNCTION Is_Leap_Year (dt date) RETURNS Bit(1)

BEGIN

Declare result Bit

If Mod(Year(dt),400)=0 Or

(Mod(Year(dt),4)=0 And Mod(Year(dt),100)<>0) Then

Set result=1

Else

Set result=0

End If

RETURN result

END

-- 创建测试表并插入数据

Drop Table If Exists test

Create Table test

(id int auto_increment primary key,d1 DateTime)

Insert into test (d1) values

('2010-05-26'),('2011-06-16'),

('2012-07-23'),('2013-08-02'),

('2014-09-20'),('2015-10-27'),

('2016-11-24'),('2017-12-13')

-- 测试自定义函数Is_Leap_Year返回结果

select *,

AddDate(concat(year(d1),'-03-01'),-1)

as Last_Date_Feb_of_The_Year,

If(Is_Leap_Year(d1),'是','否')

as `Is_Leap_Year(d1)`

from test

实验截图

---