文章目录
存储过程
在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))
BEGIN
UPDATE 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))
BEGIN
DECLARE score double;
SELECT grade into score
FROM grade
WHERE SID = studentid AND CID = courseid;
if score>60
THEN 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;
END
CALL past('Dp010003', @a);
SELECT @a;
2.在grade表中新加一列grade_level,通过存储过程输入学号和课程号,得到成绩,60分以下不及格,60-80良好,80以上优秀,根据成绩推断对应的等级填入该选课记录的grade_level列,并输出“XX同学XX课程及格/良好/优秀”
#在grade表中新加一列grade_level
ALTER 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;
END
CALL add_level('St0109010002','Dp010001',@A)
SELECT @A
2. 循环语句
MySQL支持3条用来创建循环的语句:WHILE、REPEAT和LOOP语句。
WHILE语句语法格式为:
WHILE search_condition DO
statement_list
END 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()
BEGIN
DECLARE a int DEFAULT 5;
WHILE a!=0 DO
SET a=a-1;
END WHILE;
SELECT a;
END
CALL my_num();
REPEAT语句格式如下:
REPEAT
statement_list
UNTIL search_condition
END REPEAT
(repeat首先执行statement_list语句,再判断search_condition是否为真)
[课堂练习]定义一个存储过程,输入一个值,如果大于0,则不断减1,当变量值为0时停止。(用repeart)
#法一:
CREATE PROCEDURE my_num2(IN x int)
BEGIN
REPEAT
SET x=x-1;
UNTIL x=0
END REPEAT;
SELECT x;#测试最后x的值为0
END
CALL my_num2(2)
#法二:
CREATE PROCEDURE myp_2(IN x int)
BEGIN
WHILE x>0 DO
SET x = x-1;
END WHILE;
SELECT x;
END
CALL myp_2(2)
五. 异常机制处理
条件和处理程序是MySQL提供的一种异常处理机制,定义条件是事先定义程序执行过程中可能会遇到的问题;定义处理程序是定义在遇到问题时执行的相应处理方法,并且保证存储过程和在遇到问题时不终止。
[例]创建一个表 :CREATE TABLE t_handler(s1 INT, PRIMARY KEY (s1));
编辑一个存储过程,往t_handler表中插入两次“1”,要求程序不报错继续运行
未加异常处理前:
CREATE PROCEDURE p_test()
BEGIN
INSERT INTO t_handler VALUES (1);
INSERT INTO t_handler VALUES (1);
END
CALL p_test();
加了异常处理后:
CREATE PROCEDURE p_test()
BEGIN
DECLARE 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 ) BEGIN
DECLARE
CONTINUE HANDLER FOR 1062
SET @info = '主键重复';
CALL p_insert1 ();
IF
num = 0 THEN
UPDATE student
SET studentname = "刘英"
WHERE
studentid = "091101";
ELSE DELETE
FROM
student
WHERE
studentid = "091101";
END IF;
END;
call p_insert2(0)
call p_insert2(1)
六. 游标
游标提供了一种对从表中检索出的数据进行操作的灵活手段,就本质而言,游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制,游标相当于一个指针,可以遍历结果集。
1. 声明游标
语法格式:
DECLARE cursor_name CURSOR FOR select_statement
下面的定义符合一个游标声明:
declare xs_cur1 cursor for
select 学号,姓名,性别,出生日期,总学分
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 () BEGIN
DECLARE mark,num INT DEFAULT 0;
DECLARE
cur CURSOR FOR SELECT COUNT(*) FROM grade;
DECLARE
CONTINUE HANDLER FOR SQLSTATE '02000'
SET mark = 1;
OPEN cur;
REPEAT
FETCH 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()
BEGIN
DECLARE 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_level
FROM grade;
declare continue HANDLER for not found set done = true;
OPEN cur;
FETCH cur INTO cid,sid,dj;
WHilE(not done) do
UPDATE grade SET grade.grade_level = dj WHERE StudentID = sid AND CourseID = cid;
FETCH cur INTO cid,sid,dj;
END while;
CLOSE cur;
END
CALL addgra_lever();
法二:(老师讲了游标后做的,还行吧)
CREATE PROCEDURE t_add()
BEGIN
DECLARE xh varchar(20);//放学号
DECLARE cid VARCHAR(20);//放课程号
DECLARE gra int;//放成绩
DECLARE found boolean DEFAULT TRUE;
--创建游标,并存储数据
DECLARE number CURSOR FOR
SELECT 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 DO
if 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;
END
CALL t_add();
七. 实验题:
1. 创建存储过程scg,返回所有学生的学号,所选课程号,成绩并调用该存储过程
CREATE PROCEDURE scg()
BEGIN
SELECT studentid,CourseID,grade
FROM 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))
BEGIN
SELECT grade INTO score
FROM grade
WHERE studentid = sid
AND 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))
BEGIN
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 = coname
GROUP BY coursename,成绩评价;
END
CALL 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))
BEGIN
CALL student_grade(@sc,sid,coid);
IF @sc>60 THEN
set b_score = @sc+20;SELECT "修改成功";
UPDATE grade SET grade = b_score WHERE studentid = sid;
ELSEif @sc<=60 THEN
delete from grade WHERE studentid = sid;
SELECT "修改成功";
END IF;
END
CALL 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)
BEGIN
IF (num>70) THEN
WHILE num>70 DO
SET num = num - 5;
END while;
ELSEIF (num<=70) THEN
WHILE num<=70 DO
SET 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()
BEGIN
DECLARE sid,coid VARCHAR(20);
DECLARE gra int;
DECLARE found boolean DEFAULT true;
DECLARE cur CURSOR FOR
SELECT StudentID,CourseID,grade FROM grade;
DECLARE CONTINUE HANDLER FOR NOT found SET found = FALSE;
OPEN cur;
FETCH cur INTO sid,coid,gra;
while found do
if gra<60 THEN
UPDATE Grade SET level = 0 WHERE studentid = sid AND CourseID = coid;
ELSEIF gra<=80 THEN
UPDATE 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 grade
GROUP BY StudentID;
END
CALL tao_p();
执行存储过程前:
执行存储过程后: