mysql存储过程:事先经过编译并且存储在数据库中的一段SQL语句集合。调用存储过程可以减少数据库和应用程序间传输的网络性能消耗
目录
1、创建存储过程和调用
创建存储过程语句
CREATE PROCEDURE 存储过程名称([参数])
BEGIN
内容
END;
调用存储过程使用CALL
调用存储过程语句
CALL 存储过程名称([参数])
1.1、无参存储过程
示例
创建无参存储过程,查看数据库版本
CREATE PROCEDURE p_version()
BEGIN
SELECT VERSION();
END;
注意:笔者这里使用的是 navicat 和 mysql5.7.25 版本,如果读者使用的是 mysql 命令行客户端,需要使用 DELIMITER $$ 定义结束符,因为上边语句中的 ";" 会被mysql 命令行客户端识别为结束符,因此在有";"的函数体中,需要读者额外定义结束符,如下面代码
DELIMITER $$
CREATE PROCEDURE p_version()
BEGIN
SELECT VERSION();
END $$
后面的代码笔者都是直接在navicat上执行,因此不再重复说明 DELIMITER $$ 定义结束符的事
调用
CALL p_version();
1.2、有参存储过程
声明存储过程参数需要3个关键字
IN
OUT
INOUT
IN:表示输入的参数
OUT:表示输出的参数
INOUT:即可以表示输入的参数,也可以表示输出的参数
示例sql
CREATE PROCEDURE p_name(IN userid int, OUT username varchar(20))
BEGIN
SELECT name INTO username from user where id = userid;
END;
数据库中有张user表,有1条数据,上面存储过程传入id,返回user的name字段
上面sql中的 name INTO username,是将user表的name赋值给存储过程的 username,这个INTO后面会详细说明,这里先看运行效果
调用存储过程
CALL p_name(1, @username);
SELECT @username;
@username是一个变量,用来接收存储过程输出的值,再通过SELECT @username;显示出来
运行效果
1.3、mysql变量
mysql变量有系统变量、用户自定义变量、局部变量
1.3.1、系统变量
系统变量:是mysql提供的,用户不能定义,分全局变量(GLOBAL)、会话变量(SESSION)
系统变量是2个@表示,即@@
查看所有系统变量(GLOBAL)
SHOW GLOBAL VARIABLES;
查看所有会话变量(SESSION)
SHOW SESSION VARIABLES;
查看某一系统变量(GLOBAL)
SELECT @@GLOBAL.变量名
查看某一会话变量(SESSION)
SELECT @@SESSION.变量名
设置系统变量(GLOBAL)
SET GLOBAL 变量名 = 值
设置会话变量(SESSION)
SET SESSION 变量名 = 值
注意:这里设置的系统变量(GLOBAL)或会话变量(SESSION),当mysql服务器重启后都会失效;如果想长期有效需要在mysql配置文件中设置
示例
SHOW GLOBAL VARIABLES;
SHOW SESSION VARIABLES;
SELECT @@GLOBAL.autocommit;
SELECT @@SESSION.autocommit;
SET SESSION autocommit = 0;
运行效果
1.3.2、用户自定义变量
用户自定义变量:是用户自己定义的变量,不用提前声明,在使用的时候直接 @变量名 即可,作用域是当前连接,用1个@表示
赋值,有4种方式
SET @变量名 = 值
SET @变量名 := 值
SELECT @变量名 := 值
SELECT 字段名 INTO @变量名 FROM 表名
使用用户自定义变量
SELECT @变量名
示例
SET @demo_name = '雾失楼台';
SET @demo_age := 18;
SELECT @demo_gender := '女';
SELECT name INTO @demo_username FROM user;
SELECT @demo_name;
SELECT @demo_age,@demo_gender,@demo_username;
运行效果
1.3.3、局部变量
局部变量:在局部生效的变量,使用之前需要用 DECLARE 关键字先声明,可作为存储过程内的局部变量和输入参数,作用范围是局部变量声明的存储过程的BEGIN END间
声明
DECLARE 变量名 变量类型
变量类型就是数据库字段的类型,int、varchar、date等
赋值,有3种方式
SET 变量名 = 值
SET 变量名 := 值
SELECT 字段名 INTO 变量名 FROM 表名
使用局部变量
SELECT 变量名
示例
CREATE PROCEDURE p_localvar()
BEGIN
DECLARE demo_name VARCHAR(20);
SELECT name into demo_name from user;
SELECT demo_name;
END;
CALL p_localvar();
CREATE PROCEDURE p_localvar2()
BEGIN
DECLARE demo_name VARCHAR(20);
SET demo_name = '尊前谈笑人依旧';
SELECT demo_name;
END;
CALL p_localvar2();
CREATE PROCEDURE p_localvar3()
BEGIN
DECLARE demo_name VARCHAR(20);
SET demo_name := '雨打梨花深闭门,忘了青春,误了青春';
SELECT demo_name;
END;
CALL p_localvar3();
运行效果
2、存储过程中条件判断 if 的使用
存储过程中可以写 if 条件判断
语法格式
IF 条件1 THEN
内容
ELSEIF 条件2 THEN
内容
ELSE
内容
END IF;
示例
CREATE PROCEDURE p_judging_age(IN age int)
BEGIN
DECLARE content VARCHAR(20);
IF age < 18 THEN
SET content := '未成年人';
ELSEIF 18<= age AND age <=65 THEN
SET content := '青年人';
ELSEIF 66<= age && age <=79 THEN
SET content := '中年人';
ELSE
SET content := '老年人';
END IF;
SELECT content;
END;
if 中多条件可以使用 AND 或 OR
CALL p_judging_age(17);
CALL p_judging_age(18);
CALL p_judging_age(19);
CALL p_judging_age(65);
CALL p_judging_age(66);
CALL p_judging_age(98);
运行效果
3、存储过程中case的使用
case when的作用和 if 判断类似
语法格式1
CASE case_value
WHEN when_value1 THEN
statement_list1
WHEN when_value2 THEN
statement_list2
ELSE
statement_list3
END CASE;
当case_value的值符合when_case1时,执行statement_list1
当case_value的值符合when_case2时,执行statement_list2
其他情况执行 ELSE 中的statement_list3
语法格式2
CASE
WHEN search_condition1 THEN
statement_list1
WHEN search_condition2 THEN
statement_list2
ELSE
statement_list3
END CASE;
当search_condition1条件成立时,执行statement_list1
当search_condition2条件成立时,执行statement_list2
其他情况执行statement_list3
示例语法格式1
先创建一张userinfo表,用于测试
DROP TABLE IF EXISTS `userinfo`;
CREATE TABLE `userinfo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`age` int(11) NULL DEFAULT NULL,
`gender` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of userinfo
-- ----------------------------
INSERT INTO `userinfo` VALUES (1, '李白', 27, 'm');
INSERT INTO `userinfo` VALUES (2, '朱淑真', 18, 'f');
INSERT INTO `userinfo` VALUES (3, '张先', 53, NULL);
userinfo表中有3条数据
下面存储过程传入 userinfo 表 id,显示对应 id 用户的性别信息
CREATE PROCEDURE p_judging_gender(IN userid int)
BEGIN
DECLARE user_gender CHAR(1);
DECLARE gender_string VARCHAR(10);
SELECT gender INTO user_gender from userinfo where id = userid;
CASE user_gender
WHEN 'm' THEN
SET gender_string := '男';
WHEN 'f' THEN
SET gender_string := '女';
ELSE
SET gender_string := '未知';
END CASE;
SELECT gender_string;
END;
调用
CALL p_judging_gender(1);
CALL p_judging_gender(2);
CALL p_judging_gender(3);
运行效果
示例语法格式2
还是上面的例子,传入 userinfo 表 id,显示对应 id 用户的性别信息,换一种写法
CREATE PROCEDURE p_judging_gender2(IN userid int)
BEGIN
DECLARE user_gender CHAR(1);
DECLARE gender_string VARCHAR(10);
SELECT gender INTO user_gender from userinfo where id = userid;
CASE
WHEN user_gender = 'm' THEN
SET gender_string := '男';
WHEN user_gender = 'f' THEN
SET gender_string := '女';
ELSE
SET gender_string := '未知';
END CASE;
SELECT gender_string;
END;
调用
CALL p_judging_gender2(1);
CALL p_judging_gender2(2);
CALL p_judging_gender2(3);
运行效果
4、存储过程中循环的使用
在存储过程中可以写循环
4.1、while 循环
while循环语法格式
WHILE 条件 DO
内容
END WHILE;
条件为true时,执行内容
示例
CREATE PROCEDURE p_circulate()
BEGIN
DECLARE num int;
SET num := 10;
WHILE num > 0 DO
SELECT num;
set num := num - 1;
END WHILE;
END;
调用
CALL p_circulate();
运行效果
4.2、repeat 循环
repeat循环语法格式
REPEAT
内容
UNTIL 条件
END REPEAT;
repeat 循环先执行内容,然后判断条件,如果条件为 true 则退出循环,否则继续循环
示例
CREATE PROCEDURE p_circulate_repeat()
BEGIN
DECLARE num int;
SET num := 10;
REPEAT
SELECT num;
set num := num - 1;
UNTIL num <= 0
END REPEAT;
END;
调用
CALL p_circulate_repeat();
运行效果
4.3、loop 循环
语法格式
label: LOOP
statement_listIF exit_condition THEN
LEAVE label;
END IF;
END LOOP label;
label是一个标记,通过它控制循环的结束
loop 循环中使用 LEAVE label; 退出循环,如果loop中没有 LEAVE label;则是死循环
除了LEAVE外循环中还可以有 ITERATE,ITERATE的作用是跳过当前循环剩下的内容,直接进入下一次循环
示例1
CREATE PROCEDURE p_circulate_loop()
BEGIN
DECLARE num int;
SET num := 10;
looplabel: LOOP
IF num <= 0 THEN
LEAVE looplabel;
END IF;
SELECT num;
SET num := num - 1;
END LOOP looplabel;
END;
调用
CALL p_circulate_loop();
运行效果
示例2
加入 ITERATE,当 num = 5 时跳过
CREATE PROCEDURE p_circulate_loop2()
BEGIN
DECLARE num int;
SET num := 10;
looplabel: LOOP
IF num <= 0 THEN
LEAVE looplabel;
END IF;
IF num = 5 THEN
SET num := num - 1;
ITERATE looplabel;
END IF;
SELECT num;
SET num := num - 1;
END LOOP looplabel;
END;
调用
CALL p_circulate_loop2();
运行效果
没有输出5
5、存储过程-游标 CURSOR
游标:是保存查询结果集的类型,在存储过程和自定义函数中可以使用游标对结果集进行处理。游标的使用包括声明、打开(OPEN)、获取(FETCH)、关闭(CLOSE)
声明
DECLARE 游标名称 CURSOR FOR 查询语句;
打开
OPEN 游标名称;
获取
FETCH 游标名称 INTO 变量;
关闭
CLOSE 游标名称;
示例
CREATE PROCEDURE p_cursor()
BEGIN
DECLARE username VARCHAR(10);
DECLARE userage int;
DECLARE count int;
DECLARE userinfo_cursor CURSOR FOR SELECT name, age FROM userinfo;
SELECT count(*) INTO count FROM userinfo;
OPEN userinfo_cursor;
WHILE count > 0 DO
FETCH userinfo_cursor INTO username, userage;
SET count := count - 1;
SELECT username, userage;
END WHILE;
CLOSE userinfo_cursor;
END;
查询 userinfo 表 name age 字段
查询 userinfo 表总数据条数用来计算循环次数,当然这里结束循环也可以使用 mysql自带的条件处理程序,这里不做过多介绍
调用
CALL p_cursor();
运行效果
至此完