目录
- 1. 存储过程
- 2. 局部变量
- 3. 条件分支
- 3.1 IF 语句
- 3.2 CASE 语句
- 4. 循环语句
- 4.1 WHILE 语句
- 4.2 REPEAT 语句
- 4.3 LOOP和LEAVE语句
- 4.4 LOOP和ITERATE语句
- 5. 存储过程应用示例
- 参考书籍
1. 存储过程
-
要创建存储过程,需要用到
CREATE
语句:CREATE PROCEDURE 存储过程名() BEGIN存储过程体 END ;
-
要调用存储过程,需要用到
CALL
语句:CALL 存储过程名();
-
要修改存储过程,需要用到
ALTER
语句:ALTER PROCEDURE 存储过程名 [COMMENT 'string'| LANGUAGE SQL| {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }| SQL SECURITY { DEFINER | INVOKER } }
-
要删除存储过程,需要用到
ALTER
语句:DROP PROCEDURE [IF EXISTS] 存储过程名
2. 局部变量
在过程体中可以使用 DECLARE
语句声明局部变量,用来存储临时的结果。它仅允许出现在 BEGIN...END
语句内部,且必须在所有其他语句之前。
语法格式:
DECLARE 变量名,... 类型 [DEFAULT 值]
局部变量可以通过 SET
语句赋值和 SELECT
语句显示。
SET 变量名 = 值, ...
SELECT 变量名, ...
【例】给局部变量赋值例子。
set @a=1;
select count(*) into @c from stu;select @a, @c;
【例】创建存储过程 p4
输出平方数。
drop PROCEDURE if EXISTS p4;
# delimiter可以设置结束符,如经过下面设置后结束符为$,不是;
delimiter $
# inout : 先输入,再输出参数
create procedure p4(inout a int, inout b int)
beginset a=a*a;set b=b*b;
end$
delimiter ;set @a=3;
set @b=4;
call p4(@a, @b);
select @a, @b;
【例】在一个存储过程 p2
中声明局部变量,显示登录用户是“合法用户”还是“非法用户”。
先自行创建一张表 user
,内容如下(手动输入或者通过存储过程添加数据都可,id
可以和下面的图片不一样):
同样可以用存储过程 p1
添加数据:
# 创建存储过程(循环创建)
drop PROCEDURE if EXISTS p1;
# delimiter可以设置结束符,如经过下面设置后结束符为$,不是;
delimiter $
create procedure p1(in n int)
begindeclare i int default(1);while i <= n doinsert into user values (null, concat('user', i), '12345');set i = i + 1;end while;end$
delimiter ;
call p1(10); # 运行过程p1,加入数据
判断用户状态的存储过程 p2
:
CREATE DEFINER=`root`@`localhost` PROCEDURE `p2`(in uname char(20), in psword char(20))
begindeclare usercount int;select count(*) into usercountfrom userwhere `username` = uname and `password` = psword;select if(usercount > 0, '合法用户', '非法用户');
end
注:DEFINER=`root`@`localhost` 是指定了一个 MySQL
帐户,
执行存储过程:
call p2('user1', '12345');
call p2('user1', '123456');
3. 条件分支
3.1 IF 语句
语法格式:
IF 条件1 THEN 语句序列1
[ELSEIF 条件2 THEN 语句序列2]
...
[ELSE 语句序列0]
END IF
3.2 CASE 语句
语法格式 1:
CASE 表达式WHEN 值1 THEN 语句序列1[WHEN 值2 THEN 语句序列2] ...[ELSE 语句序列0]
END CASE
语法格式 2:
CASEWHEN 条件1 THEN 语句序列1[WHEN 条件2 THEN 语句序列2] ...[ELSE 语句序列0]
END CASE
4. 循环语句
4.1 WHILE 语句
WHILE
语句是先判断条件再执行语句!
语法格式:
WHILE 条件 DO语句序列
END WHILE
4.2 REPEAT 语句
REPEAT
语句是先执行语句序列再判断条件!
语法格式:
REPEAT语句序列
UNTIL 条件 END REPEAT
4.3 LOOP和LEAVE语句
LOOP
和 LEAVE
语句是通过语句体控制循环结束!
语法格式:
[标签:] LOOP语句序列LEAVE标签...
END LOOP [标签]
4.4 LOOP和ITERATE语句
LOOP
和 ITERATE
语句是跳转到循环开始!
在 WHILE
、REPEAT
或 LOOP
循环体内执行到 ITERATE
语句,就跳转到循环开始继续执行。
ITERATE 标签
注:ITERATE
语句与 LEAVE
区别在于,LEAVE
是离开一个循环,而 ITERATE
语句则是重新开始一个循环。
5. 存储过程应用示例
【例 1】创建存储过程 p3
输出数据库 score
中的表 score
中成绩大于等于 90
的人数。
注:数据库 score
在前面的习题讲解中已创建!
drop PROCEDURE if EXISTS p3;
# delimiter可以设置结束符,如经过下面设置后结束符为$,不是;
delimiter $
# out : 输出参数
create procedure p3(out count int)
beginselect count(*) into countfrom scorewhere score.score >= 90;
end$
delimiter ;set @c=1;
call p3(@c);
select @c;
【例 2】编写存储过程 p_updatescore
,将某个学院的所有学生的某门功课成绩+n
,然后调用该存储过程,将数学学院的学生数学成绩+1
。
drop procedure if exists p_updatescore;
delimiter $
create procedure p_updatescore(in dname char(20), in lname char(20), in addscore int)
begindeclare did int; # 部门编号declare lid int; # 课程编号select department.id into didfrom departmentwhere department.`name` = dname;select lesson.lessonid into lidfrom lessonwhere lesson.lessonName = lname;update scoreset score.score = score.score + addscorewhere stuid in(select stu.idfrom stuwhere stu.departmentId = did) and LessonId = lid;end$
delimiter ;# 加分前的数学成绩表
# 注意这里的成绩表不止是数学学院的数学成绩哦,还有别的学院的同学,而我们只对数学学院的同学进行加分 ^_^
select score.score from scorewhere LessonId = '101';call p_updatescore('数学学院', '数学', 1);# 加分后的数学成绩表
select score.score from scorewhere LessonId = '101';
参考书籍
《MySQL实用教程(第4版)》
上一篇文章:【数据库——MySQL】(11)查询和视图练习及讲解