存储过程

在MYSQL中，可以定义一段程序放在数据库中，这样的程序称为存储过程，存储过程是数据库对象之一。存储过程实质上就是一段代码，它可以由声明式SQL语句（CREATE,SELECT,UPDATE）和过程式SQL语句(IF-THEN-ELSE) 组成，存储过程可以由程序，触发器或者另外一个存储过程来调用，从而激活它。

一. 存储过程的创建和使用

存储程序可以分为存储过程和函数。存储过程和函数的操作主要包括创建存储过程和函数、调用存储过程和函数、查看存储过程和函数，以及修改和删除存储过程和函数。

1. 创建存储过程

语法：

CREATE PROCEDURE proc_name ( [proc_parameter[,…] )[begin]routine_body[end]

2. 删除存储过程

删除存储过程使用SQL语句DROP PROCEDURE来实现，其语法形式如下：

DROP PROCEDURE proc_name 

3. 查看存储过程

使用SHOW CREATE语句可以查看存储过程的定义语句，语法形式如下：

SHOW CREATE PROCEDURE pf_name;

4. 调用存储过程

通常使用关键字CALL调用存储过程，其语法形式如下：

CALL procedure_name([parameter[,…]]);

5. 例题

[例1.1] 创建一个名为p_snum的简单存储过程，用于获取所有的学生总数

CREATE PROCEDURE p_snum()BEGIN​    SELECT COUNT(*)​    FROM student;END;#调用CALL p_snum();

[课堂练习1]创建一个名为p_cnum的存储过程，用来获取没有班长的班级信息，建立这个存储过程并调用

CREATE PROCEDURE p_cnum()BEGIN​    SELECT * FROM class WHERE Monitor is NULL;END;CALL p_cnum();

二. 变量

变量是表达式中最基本的元素，可用于存储临时数据。

1. 系统变量

说明：变量由系统提供的，不用自定义
语法：
①查看系统变量
show 【global|session 】variables like ‘’; 如果没有显式声明global还是session，则默认是session
②查看指定的系统变量的值
select @@【global|session】.变量名; 如果没有显式声明global还是session，则默认是session
③为系统变量赋值
方式一：
set 【global|session 】 变量名=值; 如果没有显式声明global还是session，则默认是session
方式二：
set @@global.变量名=值;
set @@变量名=值；

1.1 全局变量

服务器层面上的，必须拥有super权限才能为系统变量赋值，作用域为整个服务器，也就是针对于所有连接（会话）有效

1.2 会话变量

服务器为每一个连接的客户端都提供了系统变量，作用域为当前的连接（会话）

2. 自定义变量

2.1 用户变量

说明：

用户变量（User-Defined Variables）：带有前缀@，只能被定义它的用户使用，作用于当前整个连接，当前连接断开后，所定义的用户变量会被全部释放。用户变量不用提前定义就可以直接使用。

作用域：针对于当前连接（会话）生效
位置：begin end里面，也可以放在外面
使用：

①声明并赋值：
set @变量名=值;或
set @变量名:=值;或
select @变量名:=值;

②更新值
方式一：
set @变量名=值;或
set @变量名:=值;或
select @变量名:=值;
方式二：
select xx into @变量名 from 表;

③使用
select @变量名;

2.2 局部变量

说明：

局部变量（Local Variables）：没有前缀，一般用于SQL语句块中，比如存储过程的BEGIN…END中。局部变量使用前需要先通过DECLARE声明。如没有声明，则初始值为NULL。

作用域：仅仅在定义它的begin end中有效
位置：只能放在begin end中，而且只能放在第一句
使用：
①声明
declare 变量名 类型 【default 值】;

局部变量可以在子程序中定义并应用，其作用范围是BEGIN…END语句块。

在存储过程中使用DECLARE语句定义局部变量，其语法形式如下：

DECLARE var_name[,…] type [DEFAULT value];

declare str1, str2 varchar(6);

②赋值或更新

定义变量之后，可以使用SET关键字为变量赋值，语法形式如下：

SET var_name = expr [,var_name = expr] …;

ET var1 = 3; 或 set @var1=3；

另外，也可以使用SELECT…INTO…查询语句将查询结果赋给变量，这要求查询结果必须只有一行，具体语法形式如下：

SELECT col_name[,……] INTO var_name[,……] FROM table_name;

方式一：
set 变量名=值;或
set 变量名:=值;或
select @变量名:=值;
方式二：
select xx into 变量名 from 表;

③使用
select 变量名;

三. 存储过程参数

3.1 说明：

当存储过程涉及到输入输出值时，则需要用到参数：

参数：设置 MySQL存储过程的参数，用在存储过程的定义，共有三种参数类型:IN,OUT,INOUT

基本语句：

CREATE PROCEDURE([[IN |OUT |INOUT ] 参数名 数据类形…])

IN 输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被改变返回，为默认值

OUT 输出参数:该值可在存储过程内部被改变，并可返回

INOUT 输入输出参数:调用时指定，并且可被改变和返回

3.2 例题

[例3.1]创建一个存储过程，将输入的值加2后输出

CREATE PROCEDURE p_inout(INOUT result int )BEGIN​    SELECT result;​    SET result = result + 2;​    SELECT result;END;SET @a = 10;CALL p_inout(@a);SELECT @a;

[例3.2]创建一个存储过程，输入某个班级，输出某个班级的人数

CREATE PROCEDURE p_class(IN c char(8),OUT num int)BEGIN​    SELECT COUNT(*) INTO num FROM student WHERE classid = c;end;CALL p_class('Cs010901',@b)SELECT @b

[课堂练习1]创建一个名为p_garde的存储过程，通过输入的学生号和课程号，得到该同学该门课的成绩

CREATE PROCEDURE p_grade(IN SID char(12),IN CID CHAR(8),OUT score int)BEGIN​    SELECT grade INTO score FROM grade WHERE SID = studentid AND CID = courseid;END;CALL   p_grade('St0109010001','Dp010001',@gra);SELECT @gra;

[课堂练习2]建立一个存储过程p_add_grade，这个存储过程作用是可以给某个同学某门课程加两分（表内数据直接加2分）

CREATE PROCEDURE p_add_grade(IN SID char(12),IN CID CHAR(8))
BEGINUPDATE grade SET grade=grade+2 WHERE SID = studentid AND CID = courseid;
END;CALL	p_add_grade('St0109010001','Dp010001');

执行存储过程前：

执行后：

四. 流程控制

在MySQL中，常见的过程式SQL语句可以用在一个存储过程体中。例如：IF语句、CASE语句、LOOP语句、WHILE语句等。

1. IF语句

IF-THEN-ELSE语句可根据不同的条件执行不同的操作。

语法格式：

IF search_condition THEN statement_list[ELSEIF search_condition THEN statement_list ] ...[ELSE statement_list]END IF

[例]创建存储过程，判断两个输入的参数哪一个更大，并输出较大的参数值。

CREATE PROCEDURE p_max(IN a int,IN b INT,OUT max INT)BEGIN​    if a>b​       THEN SELECT 'a更大';​       SET max = a;​    ELSE​       SELECT 'b更大';​       SET max = b;​    END if;END;CALL p_max(3,4,@max);SELECT @max;

[课堂练习]将p_garde的存储过程输出的成绩传递给p_pass函数，该函数可以通过输入的成绩，输出该成绩是否及格。完成p_pass函数并验证

CREATE PROCEDURE p_pass(IN score int,OUT result char(4))BEGIN​    if score>60​       THEN SET result = '及格';​    ELSE ​       SET result = '不及格';​    END if;end;CALL p_pass(@gra,@b);SELECT @b;

将p_grade和p_pass存储过程的功能合并到一个存储过程p_grade_pass中，输入学号和课程号，输出是否及格

CREATE PROCEDURE p_grade_pass(IN SID varchar(20),IN CID varCHAR(20),OUT result varchar(20))
BEGINDECLARE score double;SELECT grade into scoreFROM grade WHERE SID = studentid AND CID = courseid;if score>60THEN SET result = '及格';ELSE SET result = '不及格';END if;
END;CALL p_grade_pass('St0109010001','Dp010001',@er);
SELECT @er;

1.创建一个存储过程，输入课程号，输出该课程不及格人数

CREATE PROCEDURE past(IN cos VARCHAR(20), OUT num INT)BEGIN​     SELECT count(grade) INTO num​     FROM grade​     WHERE grade<60​     and courseid = cos;ENDCALL past('Dp010003', @a);SELECT @a;

2.在grade表中新加一列grade_level,通过存储过程输入学号和课程号，得到成绩，60分以下不及格，60-80良好，80以上优秀，根据成绩推断对应的等级填入该选课记录的grade_level列，并输出“XX同学XX课程及格/良好/优秀”

#在grade表中新加一列grade_levelALTER TABLE grade ADD grade_level VARCHAR(20);CREATE PROCEDURE add_level(IN sid VARCHAR(20),IN coid VARCHAR(20),OUT result VARCHAR(50))BEGIN​         DECLARE fs INT;​         SELECT grade INTO fs from  grade​         WHERE studentid = sid AND courseid    = coid;  ​         ​         if fs<60 THEN ​             UPDATE grade SET grade_level = "不及格" WHERE studentid = sid AND courseid   = coid;​             SET result = CONCAT(sid,"同学",coid,"课程不及格");​         ELSEIF fs<80 THEN​             UPDATE grade SET grade_level = "良好" WHERE studentid = sid AND courseid    = coid;​             SET result = CONCAT(sid,"同学",coid,"课程良好");​         ELSE   ​             UPDATE grade SET grade_level = "优秀" WHERE studentid = sid AND courseid    = coid;​             SET result = CONCAT(sid,"同学",coid,"课程优秀");​         END if;ENDCALL add_level('St0109010002','Dp010001',@A)SELECT @A

2. 循环语句

MySQL支持3条用来创建循环的语句：WHILE、REPEAT和LOOP语句。

WHILE语句语法格式为：

WHILE search_condition DO​    statement_listEND WHILE 

位置：

只能放在begin end中

特点： 都能实现循环结构

对比：

①这三种循环都可以省略名称，但如果循环中添加了循环控制语句（leave或iterate）则必须添加名称
②
loop 一般用于实现简单的死循环
while 先判断后执行
repeat 先执行后判断，无条件至少执行一次

1、while
语法：
【名称:】while 循环条件 do
循环体
end while 【名称】;
2、loop
语法：
【名称：】loop
循环体
end loop 【名称】;

3、repeat
语法：
【名称:】repeat
循环体
until 结束条件
end repeat 【名称】;

循环控制语句
leave：类似于break，用于跳出所在的循环
iterate：类似于continue，用于结束本次循环，继续下一次

定义一个存储过程，初始变量值为5，不断减1，当变量值为0时停止。

CREATE PROCEDURE my_num()
BEGINDECLARE a int DEFAULT 5;WHILE a!=0 DOSET a=a-1;END WHILE;SELECT a;
ENDCALL  my_num();

REPEAT语句格式如下：

REPEATstatement_listUNTIL search_conditionEND REPEAT （repeat首先执行statement_list语句，再判断search_condition是否为真）

[课堂练习]定义一个存储过程，输入一个值，如果大于0，则不断减1，当变量值为0时停止。（用repeart）

#法一：
CREATE PROCEDURE my_num2(IN x int)
BEGINREPEATSET x=x-1;	UNTIL x=0 END REPEAT;SELECT x;#测试最后x的值为0
ENDCALL my_num2(2)#法二：
CREATE PROCEDURE myp_2(IN x int)
BEGIN WHILE x>0 DOSET x = x-1;END WHILE;SELECT x;
ENDCALL myp_2(2)

五. 异常机制处理

条件和处理程序是MySQL提供的一种异常处理机制，定义条件是事先定义程序执行过程中可能会遇到的问题；定义处理程序是定义在遇到问题时执行的相应处理方法，并且保证存储过程和在遇到问题时不终止。

[例]创建一个表 ：CREATE TABLE t_handler(s1 INT, PRIMARY KEY (s1));

编辑一个存储过程，往t_handler表中插入两次“1”，要求程序不报错继续运行

未加异常处理前：

CREATE PROCEDURE p_test()
BEGININSERT INTO  t_handler VALUES (1);INSERT INTO  t_handler VALUES (1);
ENDCALL p_test();

加了异常处理后：

CREATE PROCEDURE p_test()
BEGINDECLARE CONTINUE HANDLER FOR 1062 SET @info = '主键重复';INSERT INTO  t_handler VALUES (1);INSERT INTO  t_handler VALUES (1);
END
CALL p_test();

如果需要在遇到主键重复的错误提醒时，程序不报错，仍然继续运行该怎么办？

那就在存储过程中加入自定义异常机制处理程序语句咩：
DECLARE CONTINUE HANDLER FOR 1062 SET @info = '主键重复';

例题.创建一个存储过程p_insert1，作用是向student表中插入一行数据(‘091101’, ‘陶伟’, ‘男’, NULL,NULL,NULL, ‘Cs010901’)。再创建另外一个存储过程p_insert2，接收一个数值，让背后在p_insert2中调用p_insert1，如果输入的值是0，将091101号同学改名为刘英，如果输入值是1，删除091101这个同学

create procedure p_insert1()
insert into student values('091101', '陶伟', '男', NULL,NULL,NULL, 'Cs010901');CREATE PROCEDURE p_insert2 ( IN num INT ) BEGINDECLARECONTINUE HANDLER FOR 1062 SET @info = '主键重复';CALL p_insert1 ();IFnum = 0 THENUPDATE student SET studentname = "刘英" WHEREstudentid = "091101";ELSE DELETE FROMstudent WHEREstudentid = "091101";END IF;
END;call p_insert2(0)
call p_insert2(1)

六. 游标

MySQL游标概念与用法详解

mysql游标的用法及作用

游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制，游标相当于一个指针，可以遍历结果集。

1. 声明游标

语法格式：

DECLARE cursor_name CURSOR FOR select_statement

下面的定义符合一个游标声明：

declare xs_cur1 cursor   forselect 学号,姓名,性别,出生日期,总学分​    from student​    where 专业名 = '计算机';

2. 打开游标

声明游标后，要使用游标从中提取数据，就必须先打开游标。在MySQL中，使用OPEN语句打开游标，其格式为：

OPEN cursor_name

3. 读取数据

游标打开后，就可以使用FETCH…INTO语句从中读取数据。

语法格式：

FETCH cursor_name INTO var_name [, var_name] …

4. 关闭游标

游标使用完以后，要及时关闭。关闭游标使用CLOSE语句，格式为：

CLOSE cursor_name

语句参数的含义与OPEN语句中相同。例如：

CLOSE xs_cur2

将关闭游标xs_cur2。

5. 例题

[例]创建一个存储过程，计算grade表中行的数目。

CREATE PROCEDURE rowtest () BEGINDECLARE mark,num INT DEFAULT 0;DECLAREcur CURSOR FOR SELECT COUNT(*) FROM grade; DECLARECONTINUE HANDLER FOR SQLSTATE '02000' SET mark = 1;OPEN cur;REPEATFETCH cur INTO num;UNTIL mark END REPEAT;CLOSE cur;SELECT num;
END;
CALL rowtest ();//71

[课堂练习]在grade表中加入一个新列grade_level,通过存储过程给每一个成绩加入其等级放在grade_level列中，60分以下不及格，60分-80及格，80往上，优秀。

法一：（提前做的，有点乱）

SELECT CourseID,StudentID,grade,(CASE when grade<60 THEN '不及格'WHEN grade<80 THEN '及格'ELSE '优秀' END)AS grade_level
FROM grade;
#加入一个新列grade_level
ALTER TABLE grade 
ADD grade_level VARCHAR(15)CREATE PROCEDURE addgra_lever()
BEGINDECLARE sid,cid VARCHAR(20) DEFAULT null;DECLARE dj VARCHAR(15);declare done int default false; DECLARE cur CURSOR FOR SELECT CourseID,StudentID,(CASE when grade<60 THEN '不及格'WHEN grade<80 THEN '及格'ELSE '优秀' END)AS grade_levelFROM grade;declare continue HANDLER for not found set done = true; OPEN cur;FETCH cur INTO cid,sid,dj;WHilE(not done) doUPDATE grade SET grade.grade_level = dj WHERE StudentID = sid AND CourseID = cid;FETCH cur INTO cid,sid,dj;END while;CLOSE cur;
ENDCALL addgra_lever();

法二：（老师讲了游标后做的，还行吧）

CREATE PROCEDURE t_add()
BEGINDECLARE xh varchar(20);//放学号DECLARE cid VARCHAR(20);//放课程号DECLARE gra int;//放成绩DECLARE found boolean DEFAULT TRUE;--创建游标，并存储数据 DECLARE number CURSOR FORSELECT StudentID,CourseID,grade FROM grade;--游标中的内容执行完后将found设置为false DECLARE CONTINUE HANDLER FOR  NOT found SET found = false;--打开游标  OPEN number;--先取一次游标中的值，你不取的话在循环里面，他咋知道gra是多少呢 FETCH number into xh,cid,gra;--执行循环while found DOif gra<60 THEN--执行更新操作  UPDATE grade SET grade_level = "不及格" WHERE StudentID = xh AND CourseID = cid;ELSEIF gra<80  THEN --执行更新操作  UPDATE grade SET grade_level = "良好" WHERE StudentID = xh AND CourseID = cid;ELSE--执行更新操作  UPDATE grade set grade_level = "优秀" WHERE StudentID = xh AND CourseID = cid;end	if;--重复取游标中的值，FETCH number into xh,cid,gra;end while ;--释放游标 CLOSE number;
ENDCALL t_add();

七. 实验题：

1. 创建存储过程scg，返回所有学生的学号，所选课程号，成绩并调用该存储过程

CREATE PROCEDURE scg()BEGINSELECT studentid,CourseID,gradeFROM grade;END;CALL scg();

2. 创建存储过程avg_grade，查询整个成绩表的平均分，若平均分大于85，输出该分数，若平均分小于85，输出‘继续努力‘。

#查询整个成绩表的平均分
SELECT avg(grade)
FROM grade
#删除存储过程
DROP PROCEDURE avg_grade;CREATE PROCEDURE avg_grade(OUT result VARCHAR(20))
BEGIN
#定义局部变量存放平均分分数DECLARE score DOUBLE;SELECT AVG(grade) INTO score FROM grade;SET result =  IF(score>85,score,"继续努力");
END;CALL avg_grade(@str);
SELECT @str

3. 创建存储过程student_grade，返回某一的学生某一课程的分数，并调用该存储过程。（该题要用out参数）

CREATE PROCEDURE student_grade(OUT score DOUBLE,IN sid VARCHAR(20),IN coid VARCHAR(20))
BEGINSELECT grade INTO scoreFROM gradeWHERE studentid = sidAND CourseID = coid;
END;CALL student_grade(@sc,"St0109010001","Dp010001")
SELECT @sc

grade表中的数据：

结果：

4. 若60分以下为不及格，60-80是良好，80-100是优秀，建立一个存储过程p_count_grade，输入课程名，计算该课程每个分数段各有多少人.（结果如图）

先用select实现一下：

#select实现
SELECT coursename,(CASE when grade<60 then  "不及格"WHEN grade<80 THEN  "良好"ELSE "优秀" END) AS "成绩评价",count(*) AS 人数
FROM grade as g ,course as co
WHERE g.courseid = co.CourseID
AND CourseName = "JAVA程序设计"
GROUP BY coursename,成绩评价

CREATE PROCEDURE p_count_grade(IN coname VARCHAR(20))
BEGINSELECT coursename,(CASE when grade<60 then  "不及格"WHEN grade<80 THEN  "良好"ELSE "优秀" END) AS "成绩评价",count(*) AS 人数FROM grade as g ,course as coWHERE g.courseid = co.CourseIDAND CourseName = conameGROUP BY coursename,成绩评价;
ENDCALL p_count_grade("JAVA程序设计")

5. 创建存储过程grade_change,在该存储过程中调用存储过程student_grade（第3小题）,若该名同学成绩大于60，则将该成绩加20分储存在grade表中并输出修改成功，.如果小于60分，则删除该成绩记录并输出修改成功。创建完成后，调用该存储过程，将两种结果都展示一遍，

CREATE PROCEDURE grade_change(OUT a_score DOUBLE,OUT b_score DOUBLE,IN sid VARCHAR(20),IN coid VARCHAR(20))
BEGINCALL student_grade(@sc,sid,coid);IF @sc>60 THENset b_score = @sc+20;SELECT "修改成功";UPDATE grade SET grade = b_score WHERE studentid = sid;ELSEif @sc<=60 THENdelete from grade WHERE  studentid = sid;SELECT "修改成功";END IF;
ENDCALL grade_change(@a,@b,'091101','Dp030001')

---

当分数为30分小于60分时，grade表中有72条数据，执行了该存储过程后，就只剩下71条记录了，那条30分的记录被删除了。

6. 创建一个存储过程proce_inout，输入一个数字，如果该数字大于70，则不断减5，直到小于70，如果小于等于70，则不断加5，直到大于70，然后输出处理后的数字（该存储过程只能用一个参数）

CREATE PROCEDURE proce_inout(INOUT num int)
BEGINIF (num>70) THENWHILE num>70 DOSET num = num - 5;END while;ELSEIF (num<=70) THENWHILE num<=70 DOSET num = num + 5;END WHILE;END if;SELECT num;
END
SET @z = 79;
CALL  proce_inout(@z);
SET @t = 62;
CALL  proce_inout(@t);

7. 在grade表中新建一列绩点列列名为level，然后创建一个存储过程，查询每个同学每门课分数并使用游标做如下处理，如果分数大于80分，写入绩点为4，如果获得60-80分，绩点为2，小于60，绩点为0，完成后，输出每个同学的总绩点为多少。

#新建一列level
ALTER TABLE grade ADD level int ;CREATE PROCEDURE tao_p()
BEGINDECLARE sid,coid VARCHAR(20);DECLARE gra int;DECLARE found boolean DEFAULT true;DECLARE cur CURSOR FORSELECT StudentID,CourseID,grade FROM grade;DECLARE CONTINUE HANDLER FOR NOT found SET found = FALSE;OPEN cur;FETCH cur INTO sid,coid,gra;while found doif gra<60 THENUPDATE Grade SET level = 0 WHERE studentid = sid AND CourseID = coid;ELSEIF gra<=80 THENUPDATE Grade SET level = 2 WHERE studentid = sid AND CourseID = coid;ELSE UPDATE Grade SET level = 4 WHERE studentid = sid AND CourseID = coid;END if;FETCH cur INTO sid,coid,gra;END while;CLOSE cur;SELECT StudentID AS 学生学号,SUM(level) AS 学生总绩点FROM gradeGROUP BY StudentID;
ENDCALL tao_p();

执行存储过程前：

执行存储过程后：