抑郁症健康 > MySQL的存储过程你了解吗？来看这篇文章保证你不会后悔！

MySQL的存储过程你了解吗？来看这篇文章保证你不会后悔！

时间：2019-01-25 10:34:32

相关推荐

文章目录

存储过程一、存储过程和概述1.1 使用存储过程的优点二、创建并调用存储过程2.1 实践三、关于存储过程和的表达式3.1 变量的分类3.2 在存储过程中应用变量3.3 实践四、异常处理五、游标5.1 定义游标5.2 打开游标5.3 使用游标5.4 关闭游标5.5 实践六、课前练习以及实验题6.1 课前练习6.2 实验题

存储过程

一、存储过程和概述

针对表的一个完整操作有时候并不是单条SQL语句就能实现的，而是需要一组SQL语句来实现。例如，当老师不慎填写错误，要对一个同学的期末成绩进行修改，一般需要考虑以下几步：
可将一个完整操作中所包含的多条SQL语句创建为存储过程，以方便应用。

1.1 使用存储过程的优点

存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。当对数据库进行复杂操作时（如对多个表进行Update、Insert、Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。存储过程可以重复使用，可减少数据库开发人员的工作量。安全性高，可设定只有某此用户才具有对指定存储过程的使用权。

二、创建并调用存储过程

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

创建存储过程使用SQL语句CREATE PROCEDURE来实现，其语法形式如下：

CREATE PROCEDURE proc_name ( proc_parameter[,…] )beginroutine_bodyend

proc_parameter表示存储过程的参数，参数形式如下：

[ IN | OUT | INOUT ] parameter_name TYPE

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

DROP PROCEDURE proc_name

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

SHOW CREATE PROCEDURE pf_name;

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

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

2.1 实践

题目一：创建一个名为p_snum的简单存储过程，用于获取所有的学生总数

在创建存储过程前首先登录MySQL，并选择数据库xkgl。然后执行以下语句

mysql-> CREATE PROCEDURE p_snum()-> SELECT COUNT(*) FROM student;Query OK, 0 rows affected (0.03 sec)

**题目二```：创建一个名为p_cnum()的存储过程，用来获取没有班长的班级信息，建立这个存储过程并调用

create procedure p_cnum()beginselect * from class where Monitor is NULL;end;# 调用call p_cnum();# 删除drop procedure p_cnum;

调用存储过程的执行结果与直接执行查询语句SELECT COUNT(*) FROM student;的执行结果相同，但是存储过程的好处在于处理逻辑都封装在数据库端，调用者不需要了解中间的处理逻辑，当处理逻辑发生变化时，只需要修改存储过程即可，而对调用者的程序完全没有影响。

题目三：创建一个名为p_snum_fsex的存储过程，用于获取所有的学生总数和学生的所有信息。

mysql> DELIMITER $$mysql> CREATE PROCEDURE p_snum_fsex()-> BEGIN-> SELECT COUNT(*) FROM student;-> SELECT * FROM student;-> END $$Query OK, 0 rows affected (0.03 sec)mysql> DELIMITER;

Tips：DELIMITER $$的作用是将语句的结束符;修改为$$，这样存储过程中的SQL语句结束符;就不会被MySQL解释成语句的结束而提示错误。在存储过程创建完成后，应使用DELIMITER ;语句将结束符修改为默认结束符。

三、关于存储过程和的表达式

3.1 变量的分类

用户变量（User-Defined Variables）：带有前缀@，只能被定义它的用户使用，作用于当前整个连接，当前连接断开后，所定义的用户变量会被全部释放。用户变量不用提前定义就可以直接使用。局部变量（Local Variables）：没有前缀，一般用于SQL语句块中，比如存储过程的BEGIN…END中。局部变量使用前需要先通过DECLARE声明。如没有声明，则初始值为NULL。

3.2 在存储过程中应用变量

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

定义变量

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

DECLARE var_name[,…] type [DEFAULT value];例如，定义一个INT类型的变量，名称为var1：DECLARE var1 INT;

Tips：变量的定义必须在复合语句开头，并且在任何其他语句前面。也就是说，DECLARE语句在存储过程和中使用时，必须出现在BEGIN…END语句块的最前面，并且变量名不区分大小写。可以一次声明多个相同类型的变量。

为变量赋值

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

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

为前面定义的变量var1赋值或者全部变量，具体如下

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

变量值可以为常量或者表达式。

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

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

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

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

基本语句：

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

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

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

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

3.3 实践

题目一：创建一个存储过程，将输入的值加2后输出

create procedure p_in(in a int)beginselect a;set a = a + 2;select a;end;# 调用call p_in(5);

题目二：创建一个存储过程，通过out参数向外输出2

create procedure demo_out_parameter(out p_out int)beginselect p_out;set p_out = 2;select p_out;end;call demo_out_parameter(@a);select @a;

题目三：[利用inout]创建一个存储过程，将输入的值加2后输出

create procedure p_inout(inout p_io int)beginselect p_io;set p_io = p_io + 2;select p_io;end;-- 输入数值为3，得到传出的输出值，然后查看set @a = 3;select @a;call p_inout(@a);select @a;

题目四：创建一个存储过程，输入某个班级，输出某个班级的人数

create procedure p_classnum(in claid char(10) ,out cnum int)beginselect count(*) from student where classid = claid;end;select * from class;# 查找Cs010901班级的人数call p_classnum('Cs010901',@num);

题目五：创建一个名为p_garde的存储过程，通过输入的学生号和课程号，得到该同学该门课的成绩，然后将成绩传递给p_pass函数，该函数可以通过输入的成绩，输出该成绩是否及格。

create procedure p_grade(in sid char(12),in cid char(8),out cj decimal(5,1))beginselect grade into cj from grade where studentid = sid and courseid = cid;end;create procedure p_pass(in cj decimal(5,1),out result char(10))begin if cj >= 60 then set result = '及格';else set result = '不及格';end if;end;select * from grade;call p_grade('St0109010001','Dp010001',@grade);call p_pass(@grade,@result);select @result;drop procedure p_grade;

扩展：将上面的合并到一个存储过程中

create procedure p_gp()begincall p_grade('St0109010001','Dp010001',@grade);call p_pass(@grade,@result);select @result;end;call p_gp();

题目六：建立一个存储过程p_add_grade,这个存储过程作用是可以给某个同学某门课程加两分(表内数据直接加2分)

create procedure p_add_grade(in sid char(15),in cid char(10),out g int)beginselect grade into g from grade where StudentID = sid and CourseID = cid;set g = g + 2;end;drop procedure p_add_grade;select * from grade;call p_add_grade('St0109010001','Dp010001',@grade);select @grade;

题目七：创建存储过程，判断两个输入的参数哪一个更大，并输出较大的参数值。

create procedure p_max(in a int,in b int ,out max int)beginif a>b then set max = a;elseif a<b then set max = b;else set max = a;end if;select max;end;# 调用call p_max(2,2,@m);# 删除drop procedure p_max;

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

create procedure p_sub()begindeclare a int default 5;while a > 0 doset a = a - 1;end while;select a;end;call p_sub();

题目九：定义一个存储过程，输入一个值，如果大于0，则不断减1，当变量值为0时停止。（用repeart）

create procedure p_repeat(in number int)beginrepeatwhile number > 0 doset number = number - 1;end while;select number;until number = 0end repeat;end;call p_repeat(6);

题目十：

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

create procedure p_insert1()begininsert into student values('091101', '陶伟', '男', NULL,NULL,NULL, 'Cs010901');end;call p_insert1();create procedure p_insert2(in a int)begincall p_insert1();if a = 0 then update student set StudentName = '刘英' where StudentID = '091101';elseif b = 1 then delete from student where StudentID = '091101';end if;end;call p_insert2(0);drop procedure p_insert1;drop procedure p_insert2;

实践题目十，需要用到异常机制的处理，上面的代码目前是由问题的，待我理解透彻之后，将代码放在下面，进行剖析。

四、异常处理

异常处理机制的应用

那么看到上面的代码是有问题的，我们可以通过异常处理机制来处理这样的问题。

定义条件和处理程序：

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

注意：数值类型的错误代码不要使用0，因为0表示成功而不是错误；字符串类型的错误代码不要使用’00’，因为’00’表示成功而不是错误。

在MySQL中定义条件使用DECLARE…CONDITION语句，其语法形式如下：

DECLARE condition_name CONDITION FOR [condition_type];

mysql_error_code：表示数值类型错误代码。sqlstate_value：表示长度为5的字符串类型错误代码。

create procedure p_insert1()begininsert into student values('091101', '陶伟', '男', NULL,NULL,NULL, 'Cs010901');end;call p_insert1();create procedure p_insert2(in a int)begindeclare continue handler for 1062 set @info='can not find';call p_insert1();if a = 0 then update student set StudentName = '刘英' where StudentID = '091101';elseif a = 1 then delete from student where StudentID = '091101';end if;end;call p_insert2(1);select * from student;drop procedure p_insert1;drop procedure p_insert2;

五、游标

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

5.1 定义游标

在MySQL中，使用DECLARE关键字来定义游标，其语法形式如下：

DECLARE cursor_name CURSOR FOR select_statement

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

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

5.2 打开游标

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

OPEN cursor_name

5.3 使用游标

使用游标的关键字是FETCH，其语法形式如下：

FETCH cursor_name INTO var_name [, var_name] …

5.4 关闭游标

关闭游标的关键字为CLOSE，其语法形式如下：

CLOSE cursor_name

5.5 实践

不使用MySQL语句中的count函数，自定义统计数据表的行数

create procedure compute (out number integer)begindeclare xh char(20);declare found boolean default true;declare number_xs cursor forselect studentid from grade;declare continue handler for not foundset found=false;set number=0;open number_xs;fetch number_xs into xh;while found doset number=number+1;fetch number_xs into xh;end while;close number_xs;end

六、课前练习以及实验题

6.1 课前练习

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

create procedure p_num(in cid char(8),out num int)beginselect count(*) into num from grade where grade.CourseID = cid and grade < 60;select @a;end;call p_num('Dp010001',@a);drop procedure p_num;

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

alter table grade add grade_level char(10);create procedure t_level(in sid char(20),in cid char(20),out result char(30))begindeclare score int;select grade into score from gradewhere studentid=sid and courseid=cid;if score<60then update grade set grade_level = "不及格" where studentid=sid and courseid=cid;set result = concat(sid,"同学",cid,"课程不及格");elseif score>=60 and score<80 then update grade set grade_level = "及格" where studentid=sid and courseid=cid;set result = concat(sid,"同学",cid,"课程及格");elseupdate grade set grade_level = "优秀" where studentid=sid and courseid=cid;set result = concat(sid,"同学",cid,"课程优秀");end if;end;select * from grade;drop procedure t_level;#删除存储过程call t_level("St0109010001","Dp010001",@res);select @res;

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

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

create procedure p_alllevel()begindeclare sid,cid varchar(20);declare score int;# 定义如果遇到错误，则使用boolean来判断是否中断declare found boolean default true;declare gLevel cursor for# 定义grade_level游标遍历范围select studentid,courseid,grade from grade;# 异常处理declare continue handler for not found set found = false;open gLevel;fetch gLevel into sid,cid,score;while found doif score<60then update grade set grade_level = "不及格" where studentid=sid and courseid=cid;elseif score>=60 and score<80 then update grade set grade_level = "及格" where studentid=sid and courseid=cid;elseupdate grade set grade_level = "优秀" where studentid=sid and courseid=cid;end if;fetch gLevel into sid,cid,score;end while;close gLevel;end;call p_alllevel();select * from grade;drop procedure p_alllevel;

6.2 实验题

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

create procedure scg()beginselect studentid,courseid,grade from grade;end;call scg();drop procedure scg;

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

create procedure avg_grade(out result char(20))begindeclare avg int;select avg(grade) into avg from grade;if avg>=85 thenset result = avg;elseif avg<85 then set result = "继续努力";else set result = "出现错误";end if;select result;end;call avg_grade(@res);drop procedure avg_grade;

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

create procedure student_grade(in sid char(20),in cid char(20),out res int)beginselect grade from gradewhere studentid = sid and courseid = cid;end;select * from grade; call student_grade('St0109010001','Dp010003',@res);drop procedure student_grade;

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

# 普通方法实现select 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 = "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,成绩评价;END;CALL p_count_grade("JAVA程序设计")select * from course;drop procedure p_count_grade;

创建存储过程grade_change,在该存储过程中调用存储过程student_grade（第3小题）,若该名同学成绩大于60，则将该成绩加20分储存在grade表中并输出修改成功,如果小于60分，

则删除该成绩记录并输出修改成功。创建完成后，调用该存储过程，将两种结果都展示一遍

create procedure grade_change(out m double,out n double,in sid varchar(20),in cid varchar(20))begincall student_grade(@sc,sid,cid);IF @sc>60 thenset n = @sc+20;select "修改成功";elseif @sc<=60 thendelete from grade where studentid = sid;select "修改成功";end if;end;call grade_change(@a,@b,'St0109010002','Dp010001');select @a,@b;drop procedure grade_change;

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

create procedure proce_inout(in num int)beginif (num>70) then while 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;call proce_inout(79);drop procedure proce_inout;

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

alter table grade add g_level int;select * from grade;create procedure score_level()begindeclare sid,cid varchar(20);declare score int;declare found boolean default true;declare gLevel cursor for# 定义游标遍历范围select studentid,courseid,grade from grade;# 异常处理declare continue handler for not foundset found = false;open gLevel;fetch gLevel into sid,cid,score;while found doif score<60then update grade set g_level = 0 where studentid=sid and courseid=cid;elseif score>=60 and score<80 then update grade set g_level = 2 where studentid=sid and courseid=cid;elseupdate grade set g_level = 4 where studentid=sid and courseid=cid;end if;# 使用游标fetch gLevel into sid,cid,score;# 结束循环end while;# 关闭游标close gLevel;select studentid,sum(g_level) from grade group by StudentID;end;call score_level();select * from grade;drop procedure score_level;