第六章 存储过程

在本章节中需要理解存储过程的概念，会创建、修改、删除、查看存储过程，掌握存储过程的流程控制语句。理解游标的概念并会使用游标，本章任务在于掌握存储的基本概念，创建存储过程统计课程合格率和优秀率，创建存储过程将用户购物车中选购的商品生成订单。

6.1 任务1：掌握存储过程的基本概念

了解什么是存储过程，认识存储过程的有点及应用中的适用场景

6.1.1 认识存储过程

        在前面的章节中饭，我们学习了使用SQL语句进行数据库的各种操作。在实际应用中，我们需要执行SQL语句来获取数据或完成某项数据库操作。在复杂的应用需求下，我们经常会遇到一些问题。例如，在网上书店应用中，不同出版社需要每月统计其书籍销售情况，并希望将销量数据存储在临时表中进行分析。要完成这项工作。基本步骤如下。
        (1)使用DROP删除已有的临时数据。
        (2)设定出版社名称。
        (3)根据出版社名称获得出版社编号。
        (4)根据出版社编号查询当月销量。并使用查询结果创建新的临时表。
        可以看出，每个步骤都需要执行一个SQL语句，要完成这个需求，需要执行多个SQL语句。另外，为了实现不同出版社都可以查询书籍销量的需求，需要动态设定出版社名称，而不是每次重新修改SQL代码中的出版社名称。考虑每月都要执行相同的操作进行数据分析，为了更便捷的解决这内问题，可以使用MYSQL的存储过程来实现。那么，什么是存储过程呢。
        存储过程(stored procedure)可简称为课程(procedure)，是一组为了完成特定功能的SQL语句的集合，经编译后存储在数据库中。用户可以制定存储过程的病程并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象。从MYSQL5.0版本开始支持存储过程，使数据库引擎更加灵活和强大。
        因此，存储过程就是SQL语句层面上的代码封装与重用，通过存储过程可解决网上书店的问题，具体的实现实现模式如下：


删除临时表=====>>>设定出版社名称====>获取编号====>查询创建新表。
 

通过上图克制，存储过程将一系列SQL语句组合起来，完成一个特定的工作，一旦存储过程编写完成，调用存储过程就变得非常简单。

6.1.2 MySQL存储过程的优缺点

        基于存储过程的特点，他在实际应用中的优势体现在以下方面。
        (1)减少网络流量。存储过程一般存储在数据库服务器上，应用程序不需要发送多个SQL语句，而只需要发送存储过程的名称和参数，因此有助于减少应用程和数据库服务器之间的网络流量，提升访问速度。
        (2)提升执行速度。MySQL中对于存储过程是按需编译的，并将编译好的存储过程放在缓存中，如果多次重复调用这个存储斑斑，则使用缓存中的编译版本。
        (3)减少链接数据库的次数。对于比较复杂的数据操作(如需对多个表进行查询、增加、删除、更新操作)，如果通过前端应用程序来实现，需要很多条SQL语句可能需要多次链接数据库，如果使用存储过程，只需要应用程序连接一次数据库即可。
        (4)安全性高。数据库管理员可以对访问数据库存储过程的应用程序授予权限，而不提供基础数据表的访问权限，在某种程度上保证了数据库系统的安全性。安全级别比较高的应用，如银行系统多使用存储过程。
        (5)高复用性。存储过程是封装的一个特定的功能块，对于任何应用程序都是可复用和透明的。因此，对于已有的存储过程，只要向应用程序提供调用接口，应用程序的开发人员就不必再重新编写已支持的功能。而数据库管理员可以随时对存储过程的实现源码进行调整，对应用程序本身没有任何影响。
        当然，存储过程也有一些缺点。例如可以执行查。考虑存储过程是绑定在数据库上的，因此如果需要个更换其他厂商的数据库，(如将MySQL数据库更换为Oracle数据库)，已有的存储过程需要重新编写。
        在实际应用开发中，要根据业务需求决定是否使用存储过程。对于应用中特别复杂的数据处理，如复杂的报表统计，涉及多条件多表的联合查询过程，可以用存储过程来实现。

6.2 任务2：创建存储过程统计课程合格率和优秀率。

任务目标：了解存储过程的创建语法，理解存储过程的参数，会查看存储过程定义和创建代码。

6.2.1 创建和调用存储过程

1 创建存储过程

MySQL中使用CREATE PROCEDURE创建存储过程。语法格式如下。
CREATE PROCEDURE 过程名 ([过程参数[,...]])
[特性]
存储过程体
        存储过程的创建语法格式中，[特性]部分为可选项，用来调整存储过程的行为。下面对一些常用的特性进行说明。
(1)LANGUAGE SQL:表示存储过程语言，默认为SQL。
(2){CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}:表示存储过程要做哪类工作，默认值为CONTAINS SQL。
(3)SQL SECURITY{DEFINER|INVOKER}:用来指定存储过程是使用定义者的许可来执行，还是使用调用这的许可来执行，默认值是DEFINER(定义者许可)。
COMMENT'string':存储过程的注释信息。
下面结合一个具体示例来学习存储过程创建过程

【示例1】
编写存储过程，输出学生总人数

DELIMITER//#声明分隔符
CREATE PROCEDURE proc_student_countStu()
BEGIN #过程体开始
SELECT count(*) FROM student;
END// #过程体结束
DELIMITER;#恢复默认分隔符
以上代码创建的存储过程名称为proc_student_countStu,存储过程执行查询语句，获取学生表中学生的总人数，在存储过程的创建代码中，需要注意以下几点关键语法。
(1)声明语句分隔符
MySQL中默认使用‘；’作为分隔符，使用DELIMITER关键字可以改变分隔符。在创建存储过程之前，首先声明分隔符，具体戴拿如下所示
DELIMITER $$ 或者DELIMITER //
如果没有声明分隔符，那么编译器会报错。因此要先用DELIMITER关键字声明当前段的分隔符，这样MySQL才不会把他当作普通地SQL语句来执行，注意最后要把分隔符还原：
DELIMITER;
(2)定义存储过程的参数
MySQL存储过程的参数用在存储过程的定义中。参数包括以下三种类型。
IN:指的是输入参数，该参数的值必须在调用存储过程是制定。在存储过程中可以使用该参数，但是不能被返回。
OUT :指的输出参数，该参数可以在存储过程中发生改变并可以返回。
INOUT:指输出/输出参数。该参数的值在调用存储过程时制定，在存储过程中可以被改变和返回。
具体代码如下：[IN|OUT|INPOUT]参数名 类型
如果需要定义多个参数，需要用","进行分割，示例一的代码没有定义参数。
(3)过程体的标识
在定义存储过程的过程体时，需要标识开始和介绍，具体代码如下
BEGIN...END
END后面必须使用已设置的分隔符来进行结束，
2调用从存储过程
在创建存储过程之后，如何进行调用呢，MySQL中使用CALL关键字调用存储过程，语法非常简单
CALL 存储过程名();
存储过程的调用类似于JAVA中的方法调用。圆括号中根据存储过程的定义包含相应的参数。

【示例2】调用示例1创建的存储过程proc_student_countStu.

比起执行单独的SQL语句，存储过程最大的优势是将一系列SQL语句集合起来，允许使用参数，这也使得开发过程变得更加灵活。
【示例3】使用存储过程输出参数和学生总人数

#创建存储过程
DELIMITER //
CREATE PROCEDURE proc_student_countStu(OUT stuNum INT)
BEGIN
SELECT COUNT(*) INTO stuNum FROM student;
END //
DELIMITER ;
#调用并输出学生信息
CALL proc_student_countStu(@stuNum);
SELECT @stuNum;
在示例3代码中，定义了输出参数stuNum,数据类型是INT ,在查询语句中使用SELECT INTO将查询获得的学生人数放入输出参数。调用存储过程时，必须在过程名后面的圆括号中包含与定义过程相匹配的参数，如@stuNum.这里我们班@stuNum称为用户变量。调用过程结束，就可以使用SELECT语句查看输出结果。
3 存储过程中的变量
        类似JAVA等其他编程语言，在MySQL中定义了存储过程时可以使用变量。声明变量的语法如下：DECLARE 变量名[,变量名] 数据类型 [DEFAULT 值];
例如声明交易时间的变量trade_time,并默认设置值为2019-08-10
DECLARE trade_time date DEFAULT '2019-07-10';
注意在定义存储过程中，所有局部变量的声明一定要放在存储过程体的开始，否则会提示语法错误。
声明变量后，可以给变量进行赋值。语法格式如下所示：
SET 变量名=表达式/值[,变量名=表达式];
例如，设置变量total的值为100
SET total=100；
在MySQL中，变量包括两种：用户自定义变量和系统变量，这里重点讲解自定义变量。
        MySQL用户自定义变量包括局部变量和会话变量。局部变量一般用于SQL的语句块中，如存储过程中的BEGIN和END语句块。其作用于仅限于定义该变量的语句块张，生命周期也仅限于该存储过程的调用其间，在存储过程执行到END时，局部变量会被释放。
        会话变量时服务器为每个客户端连接维护的变量，跟MYSQL客户端是绑定的，会话变量也称为用户变量。用户变量可以暂存值，并传递给同一链接中的其他SQL语句进行使用，当MySQL客户端连接退出时，用户变量就会释放，用户变量创建时一般以@开头，形式为@变量名。

【示例4】创建存储过程，通过用户输入的课程编号和学生姓名，动态查询该学生最后一次考试的成绩。分析如下：将课程编号和学生姓名作为输入参数，在调用存储过程时根据输入值动态变化。设置。设置考试成绩为输出参数，允许调用者输出查看：
#创建存储过程
DELIMITER //
CREATE PROCEDURE proc_result_GetResultByStuNameAndSubNo (IN stu_name VARCHAR(50),IN sub_no INT,OUT stu_result INT)
BEGIN
DECLARE exam_date Date;
SELECT MAX(examData) INTO exam_date FROM results WHERE subjectNo=sub_no;
SELECT exam_date;
SELECT studentResult INTO stu_result FROM results R INNER JOIN student S ON S.studentNo=R.studentNo WHERE R.subjectNo=sub_no AND S.studentName=stu_name AND examData=exam_date;
END //
DELIMITER ;
#设置用户变量@stuName为‘lwc’
SET @stuName='LWC';
#设置用户变量@subNo为1
SET @subNo=1;
#调用存储过程
CALL proc_result_GetResultBystuNameAndSubNo(@stuName,@subNo,@result)
#输出该学生相关课程的成绩查询结果
SELECT @results;

在存储过程的过程体中，使用DECLARE关键字声明变量exam_date,这里的exam_date称为局部变量，他的作用范围是在BEGIN...END过程体中。DECLARE关键字通常用来声明局部变量。在MySQL客户端连接中，设置用户变量@subNo,@subName和@result，并作为参数进行存储过程的调用。输入参数@subNo和subName,它们的值被传入存储过程，当存储过程执行完毕，用户变量@result被赋值，通过SELECT语句可以查看用户变量的值。
        注意SELECT INTO语句可以一次给多个变量赋值，例如SELECT studentNo,studentName INTO stu_no,stu_name FROM student WHERE studentNo='10001';

【示例5】

        创建一个存储过程procl,设置用户变量并赋值为‘王明’,创建另一个存储过程proc2,输出已赋值的用户变量信息。
分析如下，该示例演示会话在不同存储过程间传递：
#创建存储过程procl
DELIMITER //
CREATE PROCEDURE procl()
BEGIN
SET @name='wm';
END //
DELIMITER //

# 创建存储过程proc2
DELIMITER //
CREATE PROCEDURE proc2()
BEGIN
SELECT CONCAT('name',@name);
END //
DELIMITER ;
由上图可知，调用存储过程procl后，用户变量变量@name被赋值为‘wm’，后续调用proc2时，通过CONCAT函数将字符串‘NAME’和用户变量@name连接输出。用户变量@name的生命周期在MySQL连接关闭后结束

4 使用Navicat工具创建存储过程

存储过程作为多条SQL语句的集合，在实际开发中有广泛的应用。如果能更便捷的编写和调试存储过程，Navicat客户端工具提供了良好的开发环境，比MySQL命令行操作更加便捷。下面介绍Navicat工具创建和运行存储过程的基本步骤
(1)创建存储过程
首先，右击myschool数据库下的‘函数’节点，在弹出的快捷菜单中选择‘新建函数’选项，在右侧区域会自动创建存储过程模版。
然后在程序模版中完成存储过程的编写。因为Navicat客户端工具默认知道用户目前创建的存储过程，因此在Navicat中编写存储过程时，不需要再使用DELIMITER来声明分隔符。
        注意：在默认情况下，系统在新建函数中自动打开‘函数向导’，向导帮助用户通过可视化界面输入存储过程名称，参数列表来完成存储过程模版的创建。用户可以通过工具->选项->常规，取消勾选显示函数向导复选框关闭函数向导。
(2)运行存储过程
存储过程编写完毕后，单机保存按钮，存储过程将自动保存在myschool数据库函数节点下
存储过程保存完毕后，单击运行按钮可调用存储过程，
单击确定按钮，执行存储过程并输出结果：
Navicat客户端工具提供了可视化创建和执行存储过程，使存储过程的开发和管理变得更加轻松。

5 设置用户权限

在上图中，创建的存储过程模版自动增加了DEFINER关键字，他作为一个可选项，用于规定对存储过程访问的安全控制。在MySQL中，通过DEFINER属性和SQL SECURITY特性来控制存储过程的执行权限，关键语法如下所示：
CREATE
        [DEFINER={user|CURRENT_USER}] #定义DEFINER
PROCEDURE 存储过程名
        [SQL SECURITY {DEFINER|INVOKER}|...]#特性
BEGIN
...
END
        DEFINER默认为当前用户，例如定义DEFINER='root@localhost'是指当前root用户。当然，如果创建者有SUPER权限，也可以制定DEFINER值为其他用户。能否访问该存储过程取决于该用户是否有调用该存储过程的权限以及是否有存储过程中SQL语句的SELECT的权限。
        SQL SECURITY特性可以指定DEFINER或INVOKER,用以指定是在定义者(DEFINER)或调用者(INVOKER)上下文中执行。如果省略定义SQL SECURITY特性，则默认为DEFINER上下文。
DEFINER和INVOKER决定了存储过程不同的执行方式。
        在DEFINER上下文中执行的存储过程使用其DEFINER属性指定的账户的权限执行。这些权限可能与调用这的权限完全不同。调用者必须具有引用对象的适当权限，但如果在存储过程执行期间，调用者的权限将被忽略，只有DEFINER账户权限有限。如果DEFINER账户具有很少的权限，则存储过程在其可执行的操作中相应的受到限制，如果DEFINER账户具有高权限(例如root用户)，则无论谁调用它，存储过程都可以执行强大的操作。
        在INVOKER上下文中执行的存储过程只能执行调用者的具有特权的操作，在存储过程执行期间，DEFINER属性无效。例如：
CREATE DEFINER='admin@localhost' PROCEDURE p1()
SQL SECURITY DEFINER
BEGIN
        UPDATE t1 SET counter=counter+1;
END;
       对于存储过程p1，任何对p1具有执行权限的用户都可以使用CALL进行调用。当存储过程执行的时候，取决于‘admin@localhost’的权限，这个账户必须有p1的执行权限以及对表t1的UPDATE权限，否则存储过程失败，再如：
CREATE DEFINER='admin'@localhost PROCEDURE p2()
SQL SECURITY INVOKER
BEGIN
        UPDATE t1 SET counter=counter+1;
END;
不同于p1,p2在INVOKER安全性上下文执行，因此调用执行取决于调用者的权限，如果调用者没有存储过程的执行权限或没有对数据表t1的UPDATE权限，则存储过程调用失败。

上机练习1：根据用户输入列出考试不及格的学生列表。
创建存储过程，根据用户输入的年级编号和课程名称，列出该年级该门课程考试不及格的学生列表。要求：对于输入的课程名称通过模糊查询进行匹配。
上机练习2：统计课程合格率和优秀率
创建存储过程，根据用户输入的课程名称，统计该门课程最近一次考试的合格率(合格率即分数>=60)和优秀率(优秀分数>=800)
 CREATE DEFINER='root'@localhost PROCEDURE
proc_result_calPassAndExcellentRate
(IN sub_name CARCHAR(50),OUT passRate Double,OUT excellentRate Double)
BEGIN
#省略部分代码
END //
(2)定义局部变量。
DECLARE sub_no INT;#查找课程编号。
DECLARE exam_date Datetime;#查找最近考试日期
DECLARE stuCount INT;#参加考试的总人数
DECLARE stuExcellentCount INT;#达到优秀的总人数
注意：所有局部变量的声明都要放在过程体的最开始部分。
(3)根据用户输入的课程名称，查询获得课程编号和该门课程最近一次考试的事件。查询该门课程最近一次考试的总人数、合格人数和优秀人数。
SELECT subjectNo INTO sub_no FROM subject WHERE subjectName=sub_name;
SELECT MAX(examData) INTO exam_date FROM results 
SELECT count(*) INTO studentCount FROM results WHERE subjectNo=sub_no AND examData=exam_date;
SELECT count(*) INTO stuPassCount FROM results WHERE subjectNo=sub_no AND Date(examData)=exam_date AND studentResult>=60;
SELECT count(*) INTO stuExcellentCount FROM results WHERE subjectNo=sub_no AND Date(examData)=exam_date AND studentResult>=80;

(4)计算合格率和优秀率
#计算合格率
SET PASSRATE=stuPassCount/stuCount*100;
#计算优秀率
SET excellentRate=stuExcellentCount/stuCount*100;

6.2.2 查看存储过程

前面学习了如何创建存储过程，那么创建后的存储过程该如何进行查看呢，MySQL提供了以下查看存储过程的方式。
1 查看存储过程的状态
类似查看数据库中的数据表信息，MySQL用户也可以查看数据库中已常见的存储过程。基本语法格式如下所示：
SHOW PROCEDURE STATUS;

【示例6】查看myschool中创建的存储过程。
SHOW PROCEDURE STATUS WHERE DB='myschool';
除了通过指定数据库名来查询存储过程，还可以通过LIKE语句匹配查询存储过程名称，例如：
SHOW PROCEDURE STATUS LIKE '%student%';
MySQL还提供了其他方式查看存储过程状态，这里不做过多介绍。
2 查看存储过程的创建代码
除了查看存储过程的状态，还可以查看存储过程的创建代码。语法格式如下：
SHOW CREATE PROCEDURE 存储过程名；

【示例7】查看存储过程proc_student_countStu的创建代码
SHOW CREATE PROCEDURE proc_student_countStu;

6.2.3 修改存储过程

在MySQL中，使用ALTER PROCEDURE可以修改已经创建的存储过程，但是仅仅能够修改创建存储过程时定义的属性：
ALTER PROCEDURE 存储过程名[特性]

【示例8】修改存储过程proc_student_countStu的SQL SECURITY属性为INVOKER。
ALTER PROCEDURE proc_student_countStu SQL SQCURITY INVOKER;
在修改完成之后，使用SHOW PROCEDURE STATUS LIKE '%proc_student%';

通过ALTER PROCEDURE关键字只能修改存储过程中定义的属性，如果想修改存储过程中过程体的内容，则需要删除存储过程后重新创建存储过程。除此之外，可以通过Navicat客户端工具对其进行修改，修改后可直接保存。

6.2.4删除存储过程

类似删除数据库中的数据表，MySQL用户可以使用DROP PROCEDURE删除已经创建的存储过程：
DROP procedure 存储过程名；

【示例9】删除已经创建的存储过程 proc_student_countStu.
DROP procedure proc_student_countStu;

6.3 任务3：创建存储过程将用户购物车中选购的商品生成订单

任务目标：掌握存储过程的控制语句，会使用MySQL游标：前面我们了解了基本的存储过程结构及创建方法。在实际应用中，要解决复杂的问题，往往涉及复杂的流程控制。下面来了解MySQL存储过程的控制语句。

6.3.1 存储过程中的控制语句

        类似JAV语言中的流程控制语句，MySQL提供的控制语句包括条件语句、循环语句和迭代语句。
        1.条件语句
MySQL提供可两种条件语句，分别是IF-ELSEIF-ELSE条件语句、循环语句和迭代语句。
(1)IF_ELSEIF_ELSE条件语句。IF-ELSEIF-ELSE条件语句是最常用的一种条件语句。语法格式如下
IF 条件THEN 语句列表
        [ELSEIF 条件 THEN 语句列表]
        [ELSE 语句列表]
END IF;

【示例10】
        在myschool数据库中存在教室新水表，该表记录了各课程授课教师的基本薪资，具体的数据结构如下表：
 

列名称	数据类型	说明
id	INT	教师编号，逐渐，标识列从1开始，递增值为1
tName	VARCHAR	教师姓名，该列必填
subjectNo	INT	课程编号，该列必填，外键
tSalary	FLOAT	节本薪资，该列必填

create table salary(
id INT(4) PRIMARY KEY AUTO_INCREMENT,
tName VARCHAR(50) NOT NULL,
subjectNo INT(4) NOT NULL,
tSalary FLOAT NOT NULL
);
INSERT INTO salary (id,tName,subjectNo,tSalary) VALUES(1,'A','1',1000),(2,'B','2',1000),(3,'C','3',3000),(4,'D','4',5000);
学校根据授课教师的授课质量给与其相应的质量奖金。合格率100%的授课教师获得基本薪资20%的奖金，合格率80%及以上的授课教师获得基本薪资10%的奖金，合格率大于等于60%但低于80的授课老师获得基本薪资5%的奖金，合格率低于60%的授课教师奖金为0.编写存储过程，根据用户输入的教师姓名，计算授课教师实际获得奖金(假设一名教授只教授一门课程)
        分析如下：根据需求中的奖金规则，通过IF_ELSEIF_ELSE语句计算教师实际获得的奖金。
DELIMITER //
CREATE DEFINER='root'@localhost PROCEDURE proc_salary_calBonus(IN name VARCHAR(20),OUT bonus FLOAT)
BEGIN 
DECLARE t_salary FLOAT;
DECLARE sub_no INT;
DECLARE passRate FLOAT;
DELCARE exam_date Date;
SELECT subjectNo INTO sub_no WHERE t_name=name;
SELECT tSalary INTO t_salary FROM WHERE tName=name;
SELECT MAX(examData) INTO exam_date FROM results WHERE subjectNo=sub_no;
SET pass_rate=(SELECT COUNT(*) FROM results WHERE subjectNo=sub_no AND examData=exam_date and studentResult>=60)/(SELECT COUNT(*) FROM results WHERE subjectNo=sub_no AND examData=exam_date AND studentResult<80);
SELECT passRate AS 通过率,exam_date AS 考试日期；
#根据规则计算教师奖金
IF passrate=1 THEN
set bonus=t_salary*0.2;
ELSEIF passRate>=0.8 THEN
set bonus=t_salary*0.1;
ELSEIF passRate>=0.6 AND passRate <0.8 THEN
set bonus=t_salary*0.05;
ELSE
set bonus=0;
END IF;
END //
DELIMITER ;

(2)CASE条件语句。MySQL中的CASE条件语句有两种写法。
第一种语法格式如下所示。
CASE 
        WHEN 条件 THEN 语句列表
        [WHEN 条件 THEN 语句列表]
        [ELSE 语句列表]
END CASE;
CASE条件语句中，如果条件值为真，相应的SQL语句列表将被执行。如果没有条件匹配，在ELSE子句里的语句列表被执行。另外，CASE语句只返回第一个符合条件的值，剩下的部分将会自动忽略。
        第二种语法格式如下所示：
CASE 列名
        WHEN 条件值 THEN 语句列表
        [WHEN 条件值 THEN 语句列表]
        [ELSE 语句列表]
END CASE;
注意：CASE条件语句的两种写法可以实现相同的功能，在某种情况下(如做等值判断)，使用第二种语法格式更加简洁，但是因为CASE后面有列名，功能上会有一些限制。因此，使用时需要根据需求进行选择。

【示例11】

根据税率等级，计算教师因缴纳地个人所得税。税率等级表如表所示：
 

等级	全年应缴纳所得税	税率
1	不超过36000元	3%
2	36000元至144000元的部分	10%
3	144000元至300000元的部分	20%

计算个人所得税的方法为(工资-免增额)*对应的税率。工资低于5000的部分免征个人所得税
DELIMITER //
CREATE DEFINER=root@localhost PROCEDURE proc_salary_calTax(IN name varchar(20),OUT tax FLOAT)
BEGIN
DECLARE tax_level INT;
DECLARE t_salary FLOAT;
DECLARE tax_rate FLOAT;
DECLARE t_Salary INTO t_salary FROM salary WHERE tName=name;
IF t_salary<=36000 THEN
SET tax_level=1;
ELSEIF t_salary>36000 AND t_salary <=144000 THEN
SET tex_level=2;
ELSEIF t_salary>144000 AND t_salary<=300000 TEHN
SET tex_level=3;
END IF;
 

CASE 
        WHEN tax_level=1 THEN SET tax_rate=0.03;
        WHEN tax_level=2 THEN SET tax_rate=0.1;
        WHEN tax_level=3 THEN SET tax_rate=0.2;
END CASE;
SET tax=(t_salary-5000)*tax_rate;
END //
delimiter ;

2 循环语句
MySQL语句提供多种循环语句，包括LOOP循环、WHILE循环和REPEAT循环。
(1)LOOP循环语句。LOOP循环结构不需要判断初始条件直接执行循环体，直达遇到LEAVE语句才退出循环
[begin_label:]LOOP
        语句列表
END LOOP [end_label]
为了退出循环，需要结合LEAVE语句，具体的语法格式如下所示。
LEAVE label;
其中label为编号，标号可以用在BEGIN,REPEAT,WHILE或者LOOP语句前。LEAVE语句用来表示离开标号所标识的程序块。
【示例12】
编写存储过程，批量插入年级编号为3的三门课程信息，课程名称为Hibernate，SpringMVC和Project,各门课程默认学时为300.
        分析如下：通过循环语句插入课程信息，课程信息由存储过程调用者提供，考虑MySQL存储过程中不接受数组作为输入参数，因此将课程信息通过字符串提供，通过‘，’进行分隔，如'Hibernate,SpringMVC,Project',
DELIMITER //
CRFEATE DEFINER=root@localhost PROCEDURE proc_subject_insert(IN grade_id INT,IN courses VARCHAR(100))
BEGIN
DECLARE comma_pos INT;
DELCARE current_course VARCHAR(20);
loop_label:LOOP
SET comma_pos=LOCATE(',',courses);
SET current_course=SUBSTR(courses,1,comma_pos-1);
IF current_course <> ' ' THEN
SET courses=SUBSTR(courses,comma_pos-1);
ELSE 
set current_course=courses;
END IF;
INSERT INTO subject(gradeId,subjectName,classHour) VALUES(grade_id,current_course,300);
IF comma_pos=0 OR current_course=' 'THEN
LEAVE loop_label
END IF;

END LOOP loop_label
END //
DELIMITER;

(2)WHILE循环语句。WHILE循环语句是使用最普遍的循环语句。WHILE语句首先判断条件是否成立，如果成立则执行循环体。
【begin_label:】WHILE 条件 DO
        语句列表
END WHILE [end_label]

【示例13】已有测试表test包括id和val字段。id字段由1开始递增，VAL为产生的随机数，创建存储过程，根据输入的行数要求，批量插入测试数据；
DELIMITER //
CRETE DEFINER root@localhost PROCEDURE proc_test_insert(IN rows INT)
BEGIN
DECLARE rand_val FLOAT;
WHILE rows>0 DO
select RAND() INTO rand_val;
INSERT INTO test VALUES(NULL,rand_val);
rows=rows-1;
END WHILE;
END //
delimiter ;
(3)REPEAT循环语句。Repeat循环语句类似于LOOP循环语句，都不需要初始条件即可直接进入循环体。但是和LOOP循环语句不同的是，他有退出条件。因此REPEAT循环语句是执行一次操作后检查条件是否成立，如果成立，则结束循环，如果不成立，则继续执行下一次循环操作
[begin_label:]REPEAT
语句列表
UNTILL 条件
END REPEAT [end_label]

3 迭代语句 MySQL中，ITERATE关键字可以嵌入LOOP，WHERE和REPEAT程序块中，执行ITERATE语句就是重新返回程序块的开始位置重新执行
ITERATE lebel;

【示例14】在示例13创建test表的基础上，编写存储过程批量插入测试数据。要求：调用存储过程是输入需要增加的数据行数，随机产生的测试诗句必须大于0.5：
DELIMITER //
CREATE DEFINER=root@localhost PROCEDURE proc_test_insert2(INT rows INT)
BEGIN
DECLARE rand_val FLOAT;
loop_label:WHILE rows>0 DO
SELECT RAND() INTO radn_val;
IF rand_val<0.5 THEN
ITERATE loop_label;
END IF;
INSERT INTO test VALUES(NULL,rand_val);
SET rows=rows-1;
END WHILE loop_label:
END //
demiliter;

注意：在创建存储过程前，可以使用IF EXISTS语句检查存储过程是否存在，如果不存在则进行创建：

DELIMITER //
DROP PROCEDURE IF EXISTS myschool.proc_test_insert2 //
CREATE DEFINER root@localhost PROCEDURE proc_test_insert2(IN rows INT)
BEGIN

END //
delimiter ;
 

上机练习4 统计每月的订单数
网上商城订单系统数据库中存在订单表(order)和订单商品表(order_product),其数据结构分别如下所示，商家希望统计订单情况，请编写存储过程，统计今年制定季度中每月的订单总数

DELIMITER //
CREATE DEFINER=root@localhost PROCEDURE proc_order_calOrderByQuarter(IN quater INT)
BGEIN
SET @temp=1;
loop_label:LOOP
SELECT MONTH(createTime),COUNT(*) AS 订单总数 FROM order GROUP BY MONTH(createTime) WHERE MONTH(createTime)=(quater-1)*3+temp;
temp=temp+1;
IF temp>3 THEN
LEAVE loop_label;
END IF ;

END LOOP loop_label;

6.3.2 游标

1 游标的含义

在一些应用中，我们需要对查询的结果集进行处理。例如，批量更新用户数据，获取查询列表中某列数据的集合等。这时，都需要用到MySQL提供的游标(CURsor).游标允许便利SELECT语句返回的一组行数据，并对每一行数据进行处理。MySQL允许在存储过程中使用游标。游标具有以下特点。
(1)敏感性：是指服务是否为结果集创建副本。对于敏感游标，他直接指向实际数据，对于不敏感游标，使用的则是创建的副本数据。
(2)只读性：即无法通过游标更新原始表数据。
(3)不可滚动性：不能以相反的方向获取行，只能按照SELECT语句返回行，同样，也不能跳过行。

2 MySQL游标的使用
使用MySQL游标需要通过以下几个步骤来完成。
第一步：声明游标。
MySQL中使用DECLARE 关键字声明游标。具体的语法如下。
DECLARE 游标名称 CURSOR FOR SELECT 语句；
需要注意的是，游标的声明必须在变量的声明之后。声明游标时必须跟SELECT语句关联，关联的SELECT语句不能有INTO语句。
第2步：打开游标
使用OPEN语句打开一个已经生命的游标：
OPEN 游标名称；
OPEN语句将初始化游标的结果集，因此OPEN语句必须在结果集提取行之前使用
第3步：提取数据
使用FETCH [[NEXT] FROM] 游标名称 INTO 变量名 [,变量名]...;
适用FETCH语句获取与制定游标关联的SELECT雨具的下一行，并将游标指针移动到结果集的下一行。如果改行存在，则获取的列存储在变量中。FETCH语句中指定的输出变量树木必须与SELECT语句检索的列数想匹配。
第四步：关闭游标
当游标使用完毕后，需要使用CLOSE语句关闭游标，具体语法如下
CLOSE 游标名称；
在游标使用过程中，如果执行FETCH语句后没有更多的行可用，程序就会出现异常。为了察觉并处理这个异常，使用游标时必须声明一个NOT FOUND处理程序来处理游标找不到任何行的情况。声明NOT FOUND处理程序的基本语法如下：
DECLARE CONTINUE HANDLER FOR NOT FOUND 设置结束值；
        这里，声明NOT FOUND处理程序，设置一个变量值来标志结束，例如设置SET finished=1.NOT FOUND处理程序必须在存储过程中的变量和游标声明之后进行声明。在执行FETCH语句时，它将尝试读取结果集的下一行数据，如果游标已经到达结果集结尾，他将无法获取数据，这时将设置结束值。程序通过判断结束值来进行相应的处理。

【示例15】年底，学校对教师薪水进行调整，要求：创建存储过程，将myschool数据库的薪资表中的教师薪水整体上调5%。
对myschool数据库的薪资表中的教师薪水进行更新。这里需要使用游标进行处理：
DELIMITER //
CREATE DEFINER=root@localhost PROCEDURE proc_salary_updateAllSalary()
BEGIN
DECLARE old_salary FLOAT:
DECLARE new_salary FLOAT;
DECLARE t_id INT;
DECLARE finished INTEGER DFAULT 0;
#声明游标
DECLARE salary_cur CURSOR FOR SELECT id,tSalary FROM salary;
#声明NOT FOUND Handler
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished=1;
#打开光标
OPEN salary_cur;
update_loop:LOOP
#fetch数据
FETCH salary_cur INTO tid,old_salary;
IF finished=1 THEN
LEAVE update_loop:
END IF;
#薪水上调5%
SET new_salary=old_salary*(1+0.05);
UPDATE salary SET tSalary=New_salary WHERE id=t_id;
END LOOP update_loop;
END //
DELIMITER;

上机练习5 批量调整订单系统中商品的价格
网上商城开展促销活动，所有商品统一实行8折优惠，将商品表(product)中商品价格下调20%。
DELIMITER //
CREATE DEFINER=root@localhost PROCRDURE procdownprice()
BEGIN
DECLARE old_price FLOAT;
DECLARE p_id INT;
DECLARE finished INTERGER DEFAULT 0;
DECLARE CURSOR price_cur SELECT pId,pPrice FROM product;
DELCARE CONTINUE HANDLER FOR NOT FOUND SET FINISHED=1;
OPEN price_cur;
update_loop:LOOP
FETCH price_cur INTO p_id,old_price;
IF FINISHED =1 THEN
LEAVE update_loop;
ENDIF;
UPDATE product SET pPrice=old_price*0.8 WHERE p_id=pId;

END LOOP update_loop;
CLOSE salary_cur;
END //
delimiter;

上机练习6 将用户购物车中选购的商品生成订单。
用户在网上商城选购的商品都会放置在购物车中。穿件存储过程，将用户添加到购物车中商品生成用户订单。要求：调用存储过程时输入用户ID作为参数。
(1)创建存储过程名为proc_cart_createOrder.
CREATE DEFINER=root@localhost PROCEDURE proc_cart_createOrder(IN u_id INT)
(2)声明需要的内部变量以及游标。
(3)根据购物车表中的数据，计算输入用户ID所购买的订单总金额。注意：购物车表中状态(status)的为购物车中等待下订单的物品。
SELECT SUM(pNum*pPrice) INTO total_price FROM cart WHERE uId=u_id AND status=1 GROUP BY uId;
(4)根据购物车中的商品信息生成订单表order记录。首先，生成随机订单号。然后将订单信息插入ORDER表中。
SET rand_orderId=CEILING(rand()*100000000);
INSERT INTO order (orderId,payment,paymentType,postFees,status,createTime,updateTime,uId) VALUES(rand_orderId,total_price,1,post_fees,1,NOW(),NOW().u_id);
(5)通过循环结构，将购物车中的商品信息(可能是多条)添加到order_product表中，并将购物车中的信息状态设置为2.
OPEN cart_cur;
insert_loop:LOOP
FETCH cart_cur INTO p_id,p_num,p_name,p_price,cart_id;
IF finished=1 THEN
LEAVE insert_loop:
END IF;
INSERT INTO order_product(ordeId,pId,pNum,pName,pPrice,totalFees)VALUES(rand_orderId,p_id,p_num,p_name,p_price,p_num*p_price);
UPDATE cart SET status=2 where id=cart_id;
END LOOP insert_loop;
close cart_cur;