(十四)全解MySQL之各方位事无巨细的剖析存储过程与触发器！

1
2
3
4
5
6
7
8
9
10
11
12
DELIMITER $
-- 创建的语法：指定名称、入参、出参
CREATE
PROCEDURE 存储过程名称(返回类型 参数名1 参数类型1, ....)
[ ...这里在后面讲... ]
-- 表示开始编写存储过程体
BEGIN
-- 具体组成存储过程的SQL语句....
-- 表示到这里为止，存储过程结束
END $
DELIMITER ;
复制代码

1
2
3
4
访问修饰符 返回类型 方法名(参数类型 参数名称, ....){
// 方法体...
}
复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
-- 查询用户表中的所有信息
select * from `zz_users`;
+---------+-----------+----------+----------+---------------------+
| user_id | user_name | user_sex | password | register_time
|
+---------+-----------+----------+----------+---------------------+
|
1 | 熊猫
| 女
| 6666
| 2022-08-14 15:22:01 |
|
2 | 竹子
| 男
| 1234
| 2022-09-14 16:17:44 |
|
3 | 子竹
| 男
| 4321
| 2022-09-16 07:42:21 |
|
4 | 黑熊
| 男
| 8888
| 2022-09-17 23:48:29 |
|
8 | 猫熊
| 女
| 8888
| 2022-09-27 17:22:29 |
+---------+-----------+----------+----------+---------------------+
-- 查询用户表的字段结构
desc `zz_users`;
+---------------+--------------+------+-----+---------+-------+
| Field
| Type
| Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| user_id
| int(8)
| NO
| PRI | NULL
|
|
| user_name
| varchar(255) | YES
| MUL | NULL
|
|
| user_sex
| varchar(255) | YES
|
| NULL
|
|
| password
| varchar(255) | YES
|
| NULL
|
|
| register_time | varchar(255) | YES
|
| NULL
|
|
+---------------+--------------+------+-----+---------+-------+
复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 改变结束标识为 $ 符号
DELIMITER $
CREATE
-- 定义存储过程的名称为：get_all_userInfo()
PROCEDURE get_all_userInfo()
BEGIN
-- 存储过程体：由一条查询全表的SQL组成
select * from `zz_users`;
-- 标识存储过程体结束
END $
-- 重置结束标识为 ; 符号
DELIMITER ;
复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
CALL get_all_userInfo();
+---------+-----------+----------+----------+---------------------+
| user_id | user_name | user_sex | password | register_time
|
+---------+-----------+----------+----------+---------------------+
|
1 | 熊猫
| 女
| 6666
| 2022-08-14 15:22:01 |
|
2 | 竹子
| 男
| 1234
| 2022-09-14 16:17:44 |
|
3 | 子竹
| 男
| 4321
| 2022-09-16 07:42:21 |
|
4 | 黑熊
| 男
| 8888
| 2022-09-17 23:48:29 |
|
8 | 猫熊
| 女
| 8888
| 2022-09-27 17:22:29 |
+---------+-----------+----------+----------+---------------------+
复制代码

1
2
3
4
5
6
7
8
9
10
-- 这里又将结束标识换成了 // 符号
DELIMITER //
CREATE
-- 在定义存储过程时，用 IN 声明了一个入参
PROCEDURE get_user_register_time(IN name varchar(255))
BEGIN
select `register_time` from `zz_users` where `user_name` = name;
END //
DELIMITER ;
复制代码

1
2
3
4
5
6
7
8
CALL get_user_register_time("竹子");
+---------------------+
| register_time
|
+---------------------+
| 2022-09-14 16:17:44 |
+---------------------+
复制代码

1
2
3
4
5
6
7
8
9
DELIMITER //
CREATE
-- 在定义存储过程时，用 OUT 声明了一个返回值
PROCEDURE get_user_password(OUT userPassword varchar(255))
BEGIN
select `password` into userPassword from `zz_users` where `user_id` = 1;
END //
DELIMITER ;
复制代码

1
2
3
4
5
6
7
8
9
10
CALL get_user_password(@userPassword);
select @userPassword;
+---------------+
| @userPassword |
+---------------+
|
6666
|
+---------------+
复制代码

1
2
3
4
5
6
7
8
9
DELIMITER $
CREATE
-- 在定义存储过程时，用 OUT 声明了一个返回值
PROCEDURE get_user_id(INOUT parameters varchar(255))
BEGIN
select `user_id` into parameters from `zz_users` where `user_name` = parameters;
END $
DELIMITER ;
复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 先定义一个变量
set @parameters = "熊猫";
-- 将定义的变量在调用时传入
CALL get_user_id(@parameters);
-- 再次查询定义的变量
select @parameters;
+-------------+
| @parameters |
+-------------+
|
1
|
+-------------+
复制代码

1
2
3
4
5
6
7
public void getUserID(Object obj){
obj = 1;
}
Object obj = new Object("熊猫");
getUserID(obj);
System.out.println(obj);
复制代码

1
2
3
4
5
-- 查看某个系统变量
select @@xxx;
-- 修改某个系统变量
set @@xxx = "xxx";
复制代码

1
2
3
set @变量名称 = 变量值;
select @变量名称;
复制代码

1
2
3
4
5
-- 将用户表的总行数赋值给 row_count 变量
select @row_count := count(*) from `zz_users`;
-- 将 user_id 的平均值赋给 avg_user_id 变量
select avg(user_id) into @avg_user_id from `zz_users`;
复制代码

1
2
DECLARE 变量名称 数据类型 default 默认值;
复制代码

1
2
DECLARE message varchar(255) default "not message";
复制代码

1
2
3
4
5
6
-- 赋值方式一
SET message = 变量值;
SET message := 变量值;
-- 赋值方式二
select 字段名或函数 into message from 表名;
复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
DELIMITER //
CREATE
-- 定义了一个 求两数之和 的存储过程
PROCEDURE add_value(IN number1 int(8), OUT result int(8))
BEGIN
-- 这里定义了一个局部变量：number2，默认值为 666
DECLARE number2 int(8) default 666;
-- 将两个数字相加，计算得到的和放入用户变量 result 中
SET result := number1 + number2;
END //
DELIMITER ;
-- 定义一个用户变量，接收调用存储过程后得到的和
SET @result = 0;
-- 调用存储过程，传入一个数字 888 以及接收结果的 result 变量
CALL add_value(888,@result);
-- 查询计算后的和
SELECT @result;
+---------+
| @result |
+---------+
|
1554 |
+---------+
复制代码

1
2
3
4
5
6
7
8
IF 条件判断 THEN
-- 分支操作.....
ELSEIF 条件判断 THWN
-- 分支操作.....
ELSE
-- 分支操作.....
END IF
复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DELIMITER $
CREATE
PROCEDURE if_user_age(IN age int, OUT msg varchar(255))
BEGIN
IF age < 18 THEN
SET msg := '未成年';
ELSEIF age = 18 THEN
SET msg := '刚成年';
ELSE
SET msg := '已成年';
END IF;
END $
DELIMITER ;
复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
SET @msg := "Not Data";
CALL if_user_age(16,@msg);
SELECT @msg;
+---------+
|
@msg
|
+---------+
|
未成年 |
+---------+
CALL if_user_age(18,@msg);
SELECT @msg;
+---------+
|
@msg
|
+---------+
|
刚成年 |
+---------+
CALL if_user_age(25,@msg);
SELECT @msg;
+---------+
|
@msg
|
+---------+
|
已成年 |
+---------+
复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 第一种语法
CASE 变量
WHEN 值1 THEN
-- 分支操作1....
WHEN 值2 THEN
-- 分支操作2....
.....
ELSE
-- 分支操作n....
END CASE;
-- 第二种语法
CASE
WHEN 条件判断1 THEN
-- 分支操作1....
WHEN 条件判断2 THEN
-- 分支操作2....
.....
ELSE
-- 分支操作n....
END CASE;
复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DELIMITER $
CREATE
PROCEDURE test_case(IN n int)
BEGIN
CASE n
WHEN 1 THEN
select '竹子真的帅';
WHEN 2 THEN
select '熊猫真好看';
ELSE
select '两个都好看';
END CASE;
END $
DELIMITER ;
复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
CALL test_case(1);
+---------------+
|
竹子真的帅
|
+---------------+
|
竹子真的帅
|
+---------------+
CALL test_case(2);
+---------------+
|
熊猫真好看
|
+---------------+
|
熊猫真好看
|
+---------------+
CALL test_case(3);
+---------------+
|
两个都好看
|
+---------------+
|
两个都好看
|
+---------------+
复制代码

1
2
3
4
循环名称:LOOP
-- 循环体....
END LOOP 循环名称;
复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
DELIMITER $
CREATE
PROCEDURE test_loop(IN num int)
BEGIN
-- 定义一个局部变量：num
DECLARE num int(8) default 1;
add_loop:LOOP
-- 一直循环对num + 1
SET num = num + 1;
-- 当num被加到100时
IF num >= 100 THEN
-- 跳出循环
LEAVE add_loop;
END IF;
END LOOP add_loop;
select num;
END $
DELIMITER ;
复制代码

1
2
3
4
5
6
7
8
9
10
11
CALL test_loop();
+--------+
|
num
|
+--------+
|
100
|
+--------+
复制代码

1
2
3
4
【循环名称】:WHILE 循环条件 DO
-- 循环体....
END WHILE 【循环名称】;
复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DELIMITER $
CREATE
PROCEDURE test_while()
BEGIN
-- 定义一个局部变量：num
DECLARE num int(8) default 1;
-- 循环对num=1，当<=10时退出
WHILE num <= 10 DO
-- 一直循环对num + 1
SET num = num + 1;
END WHILE;
-- 最后查询一下num值
SELECT num;
END $
DELIMITER ;
复制代码

1
2
3
4
5
6
7
8
9
10
11
CALL test_while();
+-------+
|
num
|
+-------+
|
11
|
+-------+
复制代码

1
2
3
4
5
【循环名称】:REPEAT
-- 循环体....
UNTIL 结束循环的条件判断
END REPEAT 【循环名称】;
复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DELIMITER $
CREATE
PROCEDURE test_repeat()
BEGIN
-- 定义一个局部变量：num
DECLARE num int(8) default 1;
REPEAT
SET num = num + 1;
UNTIL num>=10
END REPEAT;
-- 最后查询一下num值
SELECT num;
END $
DELIMITER ;
复制代码

1
2
3
4
5
6
7
8
9
10
11
CALL test_repeat();
+-------+
|
num
|
+-------+
|
10
|
+-------+
复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 测试LEAVE关键字终止循环
DELIMITER $
CREATE
PROCEDURE test_leave()
BEGIN
-- 定义一个局部变量：num
DECLARE num int(8) default 1;
add_while:WHILE TRUE DO
-- 对num持续做+1
SET num = num + 1;
-- 如果num=10，用LEAVE终止循环
IF num = 10 THEN
LEAVE add_while;
END IF;
END WHILE add_while;
-- 最后查询一下num值
SELECT num;
END $
DELIMITER ;
复制代码

1
2
3
4
5
6
7
8
9
10
11
CALL test_leave();
+-------+
|
num
|
+-------+
|
10
|
+-------+
复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- 测试ITERATE关键字跳出循环
DELIMITER $
CREATE
PROCEDURE test_iterate()
BEGIN
-- 定义一个局部变量：num
DECLARE num int(8) default 0;
-- 定义一个局部变量用来统计偶数和
DECLARE even_sum int(8) default 0;
sum_while:WHILE num <= 100 DO
-- 对num持续做+1
SET num = num + 1;
-- 如果num=10，用LEAVE终止循环
IF num % 2 = 0 THEN
SET even_sum = even_sum + num;
ELSE
-- 如果num不为偶数则跳过本次循环
ITERATE sum_while;
END IF;
END WHILE sum_while;
-- 最后查询一下偶数之和
SELECT even_sum;
END $
DELIMITER ;
复制代码

1
2
3
4
5
6
7
8
9
10
+------------+
|
even_sum
|
+------------+
|
2550
|
+------------+
复制代码

1
2
3
4
5
6
7
8
9
-- ①声明（创建）游标
DECLARE 游标名称 CURSOR FOR select ...;
-- ②打开游标
OPEN 游标名称;
-- ③使用游标
FETCH 游标名称 INTO 变量名称;
-- ④关闭游标
CLOSE 游标名称;
复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
DELIMITER //
CREATE
PROCEDURE id_odd_number_sum(IN N int(8), OUT sum int(8))
BEGIN
-- 声明局部变量：
--
uid：用于记录每一个user_id
--
odd_id_count：记录奇数ID的个数
--
odd_id_sum：记录奇数ID的和
DECLARE uid int(8) DEFAULT 0;
DECLARE odd_id_count int(8) DEFAULT 0;
DECLARE odd_id_sum int(8) DEFAULT 0;
-- 声明一个游标：存储倒序的user_id结果集
DECLARE uid_cursor CURSOR FOR select user_id from zz_users order by user_id desc;
-- 打开游标
OPEN uid_cursor;
-- 使用游标
REPEAT
-- 将游标中的每一条user_id值，赋给user_id变量
FETCH uid_cursor INTO uid;
-- 如果当前user_id是奇数，则将ID值累加到sum中
IF uid % 2 != 0 THEN
SET odd_id_count = odd_id_count + 1;
SET odd_id_sum = odd_id_sum + uid;
END IF;
-- 根据传入的N来决定循环的次数
UNTIL odd_id_count >= N END REPEAT;
-- 将前N个奇数ID之和赋给外部变量：sum
SET sum = odd_id_sum;
-- 关闭游标
CLOSE uid_cursor;
END //
DELIMITER ;
复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
select * from zz_users;
+---------+-----------+----------+----------+---------------------+
| user_id | user_name | user_sex | password | register_time
|
+---------+-----------+----------+----------+---------------------+
|
1 | 熊猫
| 女
| 6666
| 2022-08-14 15:22:01 |
|
2 | 竹子
| 男
| 1234
| 2022-09-14 16:17:44 |
|
3 | 子竹
| 男
| 4321
| 2022-09-16 07:42:21 |
|
4 | 黑熊
| 男
| 8888
| 2022-09-17 23:48:29 |
|
8 | 猫熊
| 女
| 8888
| 2022-09-27 17:22:29 |
+---------+-----------+----------+----------+---------------------+
CALL id_odd_number_sum(2,@sum);
select @sum;
+------+
|
num |
+------+
|
4
|
+------+
复制代码

1
2
3
4
5
6
7
8
9
<parameterMap type="根据存储过程决定" id="自己命名">
<parameter property="存储过程参数1" jdbcType="数据类型" mode="IN"/>
<parameter property="存储过程参数2" jdbcType="数据类型" mode="IN"/>
<parameter property="存储过程参数3" jdbcType="数据类型" mode="OUT"/>
</parameterMap>
<insert id="和Dao接口的方法同名" parameterMap="上面的ID值" statementType="CALLABLE">
{call 存储过程名(?, ?, ?)}
</insert >
复制代码

1
2
3
4
5
-- 查看某张表的所有存储过程
select * from 表名.Routines where routine_type = "PROCEDURE";
-- 查看某张表的某个存储过程
select * from 表名.Routines where routine_name = "过程名" AND routine_type = "PROCEDURE";
复制代码

1
2
3
4
5
CREATE TRIGGER 触发器名称
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON 表名
FOR EACH ROW
-- 触发器的逻辑（代码块）;
复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 创建一张注册日志表
CREATE TABLE `register_log` (
-- 注册时间
`register_time` varchar(255),
-- 注册地
`register_address` varchar(255),
-- 注册设备
`register_facility` varchar(255)
)
ENGINE = InnoDB
CHARACTER SET = utf8
COLLATE = utf8_general_ci
ROW_FORMAT = Compact;
-- 在用户表上创建一个触发器
DELIMITER //
CREATE TRIGGER zz_users_insert_before
BEFORE INSERT ON zz_users
FOR EACH ROW
BEGIN
insert into `register_log` values(NOW(),"北京市海淀区","IOS");
END //
DELIMITER ;
复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 向用户表插入一条用户记录
INSERT INTO `zz_users` VALUES(9,"棕熊","男","0369","2022-10-17 23:48:29");
-- 查询注册日志表
select * from `register_log`;
+---------------------+--------------------+-------------------+
| register_time
| register_address
| register_facility |
+---------------------+--------------------+-------------------+
| 2022-10-18 18:52:50 | 北京市海淀区
| IOS
|
+---------------------+--------------------+-------------------+
复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 执行的修改语句
update `zz_users` set user_name = "粉熊" and user_sex = "女" where user_id = 9;
-- 用户表修改事件的触发器
DELIMITER //
CREATE TRIGGER zz_users_update_before
BEFORE UPDATE ON zz_users
FOR EACH ROW
BEGIN
DECLARE new_name varchar(255);
DECLARE old_name varchar(255);
-- 可以通过 NEW 关键字拿到修改后的新数据（粉熊）
SET new_name := NEW.user_name;
-- 可以通过 OLD 关键字拿到修改前的老数据（棕熊）
SET old_name := OLD.user_name;
END //
DELIMITER ;
复制代码