Mysql(7)---存储过程
存储过程接下来会有三篇相关博客
- 第一篇存储过程常用语法。
- 第二篇存储过程中的游标。
- 第三篇单独讲一个实际开发过程中复杂的真实的案例。
一、概述
1、什么是存储过程
概述
:简单的说,就是一组SQL语句集
,功能强大,可以实现一些比较复杂
的逻辑功能,类似于JAVA语言中的方法;
说明
:存储过程跟触发器有点类似,都是一组SQL集,但是存储过程是主动调用的,且功能比触发器更加强大,触发器是某件事触发后自动调用。
2、优点
- 提高代码的重用性
- 简化操作
- 减少了编译次数并且减少了和数据库服务器的连接次数,提高了效率
二、delimiter命令
讲存储过程先讲下delimiter命令。我们都知道sql语句默认都是以分号';'解释。如果下select * from test_table;
这个会有一个问题对于存储过程:
CREATE PROCEDURE `proc_if`(IN type int)
BEGIN
DECLARE c varchar(500);
IF type = 0 THEN
set c = 'param is 0';
ELSEIF type = 1 THEN
set c = 'param is 1';
ELSE
set c = 'param is others, not 0 or 1';
END IF;
select c;
END;
对于上面的存储过程,它们应该是一个整体,应该是一起执行,而不是遇到分号
就执行。默认情况下,不可能等到用户把这些语句全部输入完之后,再执行整段语句。 因为mysql一遇到分号,它就要自动执行。 即,在语句遇到';'时,mysql解释器就要执行了。 这种情况下,就需要事先把delimiter换成其它符号,如//或$$。
这个时候delimiter
命令就起作用了。
示例
-- 这路我们讲默认的 ; 结尾改成 $ 再执行下面语句
DELIMITER $
select * from mall_pro ;
select * from member ;
会发现能之前能正常执行的语句这里报错了,因为现在修改结尾标志为 $
如果我们改成:
select * from mall_pro $
select * from member $
重点
:delimiter作用域是会话级别的,当你设置了DELIMITER $
那么在当前会话级别都是变成以$结束。
附一个详细讲delimiter的博客:MySql中 delimiter 详解
三、存储过程语法
1、创建
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
# 存储过程体(一组合法的SQL语句)
END
2、参数说明
1) 参数列表包含三部分
参数模式 参数名 参数类型
举例:
in stuname varchar(20)
也可以写成stuname varchar(20) 但最好把 in 加上。
2) 参数模式
in:该参数可以作为输入,也就是该参数需要调用方传入值。
out:该参数可以作为输出,也就是该参数可以作为返回值。
inout:该参数既可以作为输入又可以作为输出,也就是该参数既需要传入值,又可以返回值。
3、调用
CALL 存储过程名(实参列表);
4、删除
如果存在该存储过程 则删除该存储过程。
drop procedure if exists 存储过程名称
重点
:存储过程体中的每条sql语句的结尾要求必须加分号
。
注意
:如果存储过程体仅仅只有一句话,begin end可以省略。
注意
:存储过程的结尾可以使用 delimiter 重新设置(一般如果存储过程中存在多个分号结尾,就可以使用delimiter)
5、示例
1)空参列表
-- 案例:插入到admin表中五条记录
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin(username,`password`)
VALUES('john1','0000'),('lily','0000'),('rose','0000'),('jack','0000'),('tom','0000');
END $
-- 调用
CALL myp1()$
2):创建带in模式参数的存储过程
-- 创建存储过程实现 根据女神名,查询对应的男神信息
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
SELECT bo.*
FROM boys bo
RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
WHERE b.name=beautyName;
END $
-- 调用
CALL myp2('柳岩')$
注意
:如果传参带有中文,如果上面这样会报字符转换错误,需要将VARCHAR(20)
改成NVARCHAR(20)
,这个我会将在 Mysql(10)---自定义函数 博客中说明。
3) :创建存储过程实现,用户是否登录成功
CREATE PROCEDURE myp4(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
DECLARE result INT DEFAULT 0;# 声明并初始化
SELECT COUNT(*) INTO result# 赋值
FROM admin
WHERE admin.username = username
AND admin.password = PASSWORD;
SELECT IF(result>0,'成功','失败');# 使用
END $
-- 调用
CALL myp3('张飞','8888')$
4) 带有IN 和 OUT 参数
CREATE PROCEDURE myp7(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT usercp INT)
BEGIN
SELECT boys.boyname ,boys.usercp INTO boyname,usercp
FROM boys
RIGHT JOIN
beauty b ON b.boyfriend_id = boys.id
WHERE b.name=beautyName ;
END $
-- 调用
CALL myp7('小昭',@name,@cp)$ # 注意OUT的变量一定要是用户自定义的用户变量。
SELECT @name,@cp$
5) 创建带inout模式参数的存储过程
-- 传入a和b两个值,最终a和b都翻倍并返回
DELIMITER $
CREATE PROCEDURE myp8(INOUT a INT ,INOUT b INT)
BEGIN
SET a=a*2;
SET b=b*2;
END $
-- 调用
SET @m=10$
SET @n=20$
CALL myp8(@m,@n)$
-- 输出 20 和 40
SELECT @m,@n$
这里都是举了写简单的例子,后面会根据实际开发过程写一个复杂的存储过程。
三、流程控制结构
我们知道java对于流程控制有:if、switch。对于Mysql也一样,它有它自己的流程控制语句,下面我们一个一个来分析。
1、IF语句
1) if函数
语法:if(条件,值1,值2)
功能:实现双分支
应用在begin end中或外面
2) if结构
-- 如果expression为true 执行 statements
IF expression THEN
statements;
END IF; -- 有IF一定要有 END IF
-- 如果expression为true 执行 statements 否则执行else-statements
IF expression THEN
statements;
ELSE
else-statements;
END IF;
-- 不说了。
IF expression THEN
statements;
ELSEIF elseif-expression THEN
elseif-statements;
...
ELSE
else-statements;
END IF;
重点
:IF结构最后都需要END IF
;结尾。
3)示例
DELIMITER $
CREATE PROCEDURE test_if(score FLOAT)
BEGIN
DECLARE ch CHAR DEFAULT 'A';
IF score>90 THEN SET ch='A';
ELSEIF score>80 THEN SET ch='B';
ELSEIF score>60 THEN SET ch='C';
ELSE SET ch='D';
END IF;
select ch;
END $
call test_if(87)
-- 输出 B
2.case结构
1、语法
-- 情况1:类似于switch
case 变量或表达式
when 值1 then 语句1;
when 值2 then 语句2;
...
else 语句n;
end
-- 情况2:
case
when 条件1 then 语句1;
when 条件2 then 语句2;
...
else 语句n;
end
-- 应用在begin end 中或外面
2、示例
DELIMITER $
CREATE PROCEDURE test_case(in score FLOAT)
BEGIN
DECLARE ch CHAR DEFAULT 'A';
CASE
WHEN score>90 THEN SET ch='A';
WHEN score>80 THEN SET ch='B';
WHEN score>60 THEN SET ch='C';
ELSE SET ch='D';
END CASE;
select ch;
END $
call test_case(56)$
-- 输出 D
五、循环结构
对于java循环结构有:for、while、do-while。而对于mysql则有:while、loop、repeat
。
还有很重要的一点,对于java跳出循环有:continue 和 break。对于mysql也有自己跳出循环命令。
iterate: 类似于 continue,继续,结束本次循环,继续下一次
leave: 类似于 break,跳出,结束当前所在的循环
-- 至于它们怎么用,下面会举例说明
1.while
特点
:先判断后执行。(相当于java中while)
1)语法
【标签:】while 循环条件 do
循环体;
end while【 标签】;
-- 当你需要用到 iterate 或者 leave 时就需要用到标签。如果不需要用到这两个那么可以不需要标签
2)示例
-- 案例:1+2+...100
DELIMITER $
drop procedure if exists `pro_while1` $
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
DECLARE total INT DEFAULT 0;
DECLARE i INT DEFAULT 1;
WHILE i<=insertCount DO
set total:=total+i;
SET i=i+1;
END WHILE;
select total;
END $
-- 输出:5050
CALL pro_while1(100)$
3)带有leave语句示例
-- 案例:1+2+...100
DELIMITER $
drop procedure if exists `pro_while1` $
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
DECLARE total INT DEFAULT 0;
DECLARE i INT DEFAULT 1;
a:WHILE i<=insertCount DO
IF i=11 THEN LEAVE a; #当i=11是跳出循环 这里就需要用到标签了
END IF;
set total:=total+i;
SET i=i+1;
END WHILE a;
select total;
END $
-- 输出:55
CALL pro_while1(100)$
这里就用到标签
(这里为a)了。
2、repeat
1) 语法
特点
:先执行后判断。(相当于Do-while)
【标签:】repeat
循环体;
until 结束循环的条件
end repeat 【标签】;
2)示例
-- 案例:1+2+...100
DELIMITER $
drop procedure if exists `pro_while1` $
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
DECLARE total INT DEFAULT 0;
DECLARE i INT DEFAULT 1;
repeat
set total:=total+i;
SET i=i+1;
until i=10 #这里不需要分号
END repeat;
select total;
END $
-- 输出:45
CALL pro_while1(100)$
3、loop
特点
:简单死循环。(相当于while(true))
1)语法
【标签:】loop
循环体;
end loop 【标签】;
2) 示例
-- 案例:1+2+...100
DELIMITER $
drop procedure if exists `pro_while1` $
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
DECLARE total INT DEFAULT 0;
DECLARE i INT DEFAULT 1;
a:loop
IF i=11 THEN
LEAVE a;
END IF;
set total:=total+i;
SET i=i+1;
END loop a;
select total;
END $
-- 输出:55
CALL pro_while1(100)$
注意
: 有while一定要有 end while。有repeat一定要有end repeat。有loop一定要有end loop。
只要自己变优秀了,其他的事情才会跟着好起来(少将9)