%%
# 纲要
> 主干纲要、Hint/线索/路标
# Q&A
#### 已明确
#### 待明确
> 当下仍存有的疑惑
**❓<font color="#c0504d"> 有什么问题？</font>**
# Buffer
## 闪念
> sudden idea
## 候选资料
> Read it later
%%
# 存储程序 Stored Program
**存储程序**（Stored Programs）是 MySQL 中的一种**数据库对象**，是**对一组 SQL 语句的==封装==**，可保存于数据库中**以供复用**。包括两种：
| | 说明 |
| -------------------------- | ---------------------------------------------------------------- |
| **存储过程**（Stored Procedure） | 预编译的一组 SQL 语句，可**通过名称被调用**。存储过程**可接受输入参数、输出参数**，执行逻辑并返回结果。 |
| **存储函数**（Stored Function） | 类似于存储过程，但它 **==返回一个值==**，通常用于计算或转换操作，**可嵌入到 SQL 查询中**，用作返回结果的函数。 |
## 存储过程与存储函数的差异
| | 存储过程 | 存储函数 |
| ------ | -------------------------------------------- | ------------------------------------------------------------------------------------------- |
| 调用方式 | **只能通过 `CALL` 语句显式调用** | 可直接**在表达式中调用** |
| 参数类型 | 支持`IN`、`OUT`、`INOUT`参数 | 只支持`IN`参数 |
| 返回值 | - 可通过设置多个`OUT` 或 `INOUT` 参数返回多个值 <br>- 无需标明 | - 必须且只能**返回一个值**<br>- 必须使用 `RETURNS` 语句标明**返回的数据类型** <br>- 必须使用 `RETURN` 语句**显式指定返回值**<br> |
| 执行的结果集 | ✔️ 显示打印 | ❌ 不显示 |
## 注意事项
> [!caution] 存储程序中 `DECLARE` 语句的声明次序
>
> 参见 [^1] (P22)
>
> ![[_attachment/02-开发笔记/08-数据库/SQL 数据库对象.assets/IMG-SQL 数据库对象-9D58A75A2283A1F90608CC5EF768EAD9.png|589]]
>
>
>
<br><br>
## 存储过程 Procedure
#### 参数前缀
存储过程的参数前缀：`IN`（**缺省默认**）、`OUT`、`INPUT`
![[_attachment/02-开发笔记/08-数据库/SQL 语句.assets/IMG-SQL 语句-A2D3C12227062A9DF78D29DBD2594C6E.png|615]]
#### 创建存储过程
```sql
-- 创建存储过程
CREATE PROCEDURE 存储过程名([参数列表])
BEGIN
需要执行的语句
END;
```
示例一：
```sql
-- 示例一:
DELIMITER $
CREATE PROCEDURE t1_operation(
[IN] m1_value INT,
[IN] n1_value CHAR(1)
)
BEGIN
SELECT * FROM t1;
INSERT INTO t1(m1, n1) VALUES(m1_value, n1_value);
SELECT * FROM t1;
END $
DELIMITER ;
-- 调用该存储过程
CALL t1_operation(4, 'd');
```
示例二：
```sql
-- 示例二: 指定参数的OUT前缀
DELIMITER $
CREATE PROCEDURE get_score_data(
OUT max_score DOUBLE,
OUT min_score DOUBLE,
OUT avg_score DOUBLE,
s VARCAHR(100)
)
BEGIN
SELECT MAX(score), MIN(score), AVG(score)
FROM student_score
WHERE subject = s
INTO max_score, min_score, avg_score -- 将查询结果赋给out参数.
END $
DELIMITER ;
-- 调用该存储过程
CALL get_score_data(@a, @b, @c, '母猪的产后护理');
SELECT @a, @b, @c; -- 三个变量以`OUT`参数传入, 在执行存储过程中被赋值
```
示例三：
```sql
-- 订单合计(计算是否需要对合计增加营业税
CREATE PROCEDURE ordertotal (
IN onumber INT, -- 订单批号
IN taxable BOOLEAN, -- 是否计入税额
OUT ototal DECIMAL(8, 2) -- 订单合计数额
) COMMENT 'Obtain oder total, optionally adding tax' -- 税率
BEGIN
DECLARE total DECIMAL(8, 2); -- 总额
DECLARE taxrate INT DEFAULT 6; -- 税率(百分数)
SELECT SUM(item_price * quantity)
FROM orderitems
WHERE order_num = onumber
INTO total;
IF taxable THEN
SELECT total+(total/100*taxrate) INTO total;
END IF;
SELECT total INTO ototal; -- 存入外部变量
-- SET ototal = total; -- 等效
END;
```
#### 调用存储过程
```sql
-- 调用存储过程
CALL 存储过程([参数列表]);
-- 示例:
CALL t1_operation(4, 'd');
```
#### 查看存储过程
```sql
-- 查看指定存储过程的定义
SHOW CREATE PROCEDURE 存储过程名;
```
#### 删除存储过程
```sql
-- 删除存储过程
DROP PROCEDURE [IF EXISTS] 存储过程名称
```
<br><br>
## 存储函数 Function
> [!caution] 创建存储函数时报错 "This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA"
>
> 原因： MySQL 需要明确了解**该函数在处理相同输入时是否总会返回相同的输出**（即 **确定性**），或者**是否会读取或修改数据库数据**，尤其是在涉及 `binary logging`（二进制日志记录）或 `replication`（主从复制）的情况下，MySQL 需要明确函数行为。
>
> 解决方案：**为函数声明 `DETERMINISTIC` 或其他属性**：
>
> - `DETERMINISTIC`：表示该函数对于相同的输入参数，始终返回相同的结果。
> - `NOT DETERMINISTIC`：表示该函数对于相同的输入参数，可能返回不同的结果 <br>（例如，使用了随机数、当前时间等）。
> - `NO SQL`：表示该函数不执行任何 SQL 语句。
> - `READS SQL DATA`：表示该函数读取了数据库中的数据，但不修改。
> - `MODIFIES SQL DATA`：表示该函数修改了数据库中的数据。
>
> 或者其他方式：（两种方式）：
>
> 1. 方式一：**更改全局设置** ——`SET GLOBAL log_bin_trust_function_creators=1`；
> 2. 方式二：**更改配置文件 `my.cnf`**—— `log-bin-trust-function-creators=1`
基本关键字：
- `DECALRE` 关键字声明变量；
- `SET` 关键字声明变量赋值；
- `RETURN` 关键字声明返回值;
#### 创建存储函数
> [!NOTE] 存储函数的参数不支持默认值
```sql
CREATE FUNCTION 存储函数名称([参数列表])
RETURN 返回值类型
[函数属性]
BEGIN
函数体内容
END;
```
示例一：
```sql
DELIMITER $
CREATE FUNCTION function_name (param1 INT, ...)
RETURNS INT -- 声明返回值类型
DETERMINISTIC
BEGIN
DECLARE result INT; -- 定义局部变量
SET result = param1 * 2;
RETURN result; -- 声明返回值
END$
DELIMITER ;
```
示例二：
```sql
DELIMITER $
CREATE FUNCTION avg_score(s VARCHAR(100))
RETURNS DOUBLE
READS SQL DATA -- 声明函数属性: 读取数据但不修改.
BEGIN
DECLARE res DOUBLE;
SET res = (SELECT AVG(score) FROM student_score WHERE subject = s);
RETURN res;
-- 等价语句:
-- RETURN (SELECT AVG(score) FROM student_score WHERE subject = s);
END $
DELIMITER ;
```
#### 查看存储函数
```sql
-- 查看存储函数定义
SHOW CREATE FUNCTION 存储函数名;
```
#### 删除存储函数
```sql
-- 删除存储函数
DROP FUNCTION 存储函数名;
```
<br>
### 函数逻辑
##### 判断语句
```sql
-- 判断语句:
IF 表达式 THEN
处理语句列表
[ELSEIF 表达式 THEN
处理语句列表]
...
[ELSE
处理语句列表]
END IF;
-- 使用示例:
DELIMITER $
CREATE FUNCTION condition_demo(i INT)
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
DECLARE result VARCHAR(10);
IF i = 1 THEN
SET result = '结果是1';
ELSEIF i = 2 THEN
SET result = '结果是2';
ELSEIF i = 3 THEN
SET result = '结果是3';
ELSE
SET result = '非法参数';
END IF;
RETURN result;
END$
DELIMITER ;
```
##### 循环语句
while 循环语句：
```SQL
-- while 循环语句
WHILE 表达式 do
处理语句列表
END WHILE;
-- 使用示例:
DELIMITER $
CREATE FUNCTION sum_all(n INT UNSIGNED)
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE result INT DEFAULT 0; -- 定义局部变量, 默认值0
DECLARE i INT DEFAULT 1; -- 定义局部变量, 默认值1
WHILE i <= n DO
SET result = result + i;
SET i = i + 1;
END WHILE;
RETURN result;
END $
DELIMITER ;
```
`REPEAT ... UNTIL ... END REPEAT; ` 循环语句：
```sql
-- REPREAT 循环
REPEAT
处理语句列表
UNTIL 表达式 END REPEAT; -- 当表达式为真时, 停止循环
-- 使用示例:
DELIMITER $
CREATE FUNCTION sum_all(n INT UNSIGNED)
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE result INT DEFAULT 0;
DECLARE i INT DEFAULT 1;
REPEAT
SET result = result + i;
SET i = i + 1；
UNTIL i > n END REPRET; -- 当 i > n 时, 跳出循环
RETURN result;
END $
DELIMITER ;
```
loop 语句：
略
<br><br><br>
# 游标 Cursor
SQL 中的**游标（Cursor）** 是一种数据库对象，用于**在 "存储过程" 或 "存储函数" 中==逐行处理==查询结果集**。
> [!NOTE]
>
> - 游标是 **==只读==** 的，不能通过游标直接修改数据，需使用额外 SQL 语句如 `UPDATE`
> - 存储过程结束后，若未显式关闭游标（`close`），则 MySQL 会自动关闭。
### 游标的使用
使用游标涉及几个步骤：**声明/创建游标**、**打开游标**、**获取数据**、**关闭游标**。
###### 创建游标
```sql
DECLARE 游标名 CURSOR FOR 查询语句;
```
###### 打开&关闭游标
打开" 意味着**执行查询语句**；"关闭" 意味**释放游标相关资源**
```sql
OPEN 游标名; -- "打开"意味着执行查询语句, 获取结果集, 游标指向第一条记录
CLOSE 游标名;
```
###### 获取数据
```sql
-- 将当前游标所指记录中的各列值依次赋给 `INTO` 之后的各个变量，
FETCH 游标名 INTO 变量1, 变量2, ... 变量n
```
### 示例
示例一：
```sql
DELIMITER $
CREATE PROCEDURE cursor_demo()
BEGIN
DECLARE m_value INT;
DECLARE n_value CHAR(1);
DECLARE record_count INT;
DECLARE i INT DEFAULT 0;
-- 创建游标——获取t1表中m1, n1两列的值.
DECLARE cur CURSOR FOR SELECT m1, n1 FROM t1;
SELECT COUNT(*) FROM t1 INTO record_count;
-- 打开游标: 执行查询语句
OPEN record_count;
WHILE i < record_count DO
FETCH cur INTO m_value, n_value -- 获取数据
SELECT m_value, n_value; -- 显示执行结果.
SET i = i + 1;
END WHILE;
-- 关闭游标
CLOSE cur;
END $
DELIMITER ;
```
示例二：
```sql
DELIMITER $
CREATE PROCEDURE process_employee_salareis()
BEGIN
-- 声明变量
DECLARE done INT DEFAULT FALSE;
DECLARE emp_id INT;
DECLARE emp_salary DECIMAL(10, 2);
-- 声明游标
DECLARE cur CURSOR FOR SELECT employee_id, salary FROM employees;
-- 声明处理结束条件(句柄)
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 打开游标
OPEN cur;
-- 循环读取游标中的数据
read_loop: LOOP
FETCH cur INTO emp_id, emp_salary;
IF done THEN
LEAVE read_loop;
END IF;
-- 在这里处理每行数据, 更新员工薪资.
UPDATE employees SET salary = emp_salary * 1.10
WHERE employee_id = emp_id;
END LOOP;
-- 关闭游标
CLOSE cur;
END $
DELIMITER ;
```
示例三：
```sql
DELIMITER $
CREATE PROCEDURE processorders()
BEGIN
-- 声明变量
DECLARE done BOOLEAN DEFAULT 0; -- 用于控制循环
DECLARE o INT; -- 订单号
DECLARE t DECIMAL(8, 2); -- 订单总额
-- 声明游标
DECLARE ordernumbers CURSOR FOR
SELECT order_num FROM orders;
-- 声明句柄
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
-- 创建表, 存储结果
CREATE TABLE IF NOT EXISTS ordertotals (
order_num INT,
total DECIMAL(8, 2)
);
-- 打开游标
OPEN ordernumbers;
-- 循环遍历: 为每一笔订单, 计算其订单总金额, 存入ordertotals表中.
REPEAT
-- 获取数据
FETCH ordernumbers INTO o;
-- 调用存储程序, 计算订单总额
CALL ordertotal(o, 1, t);
-- 插入指定表
INSERT INTO ordertotals(order_num, total) VALUES (o, t);
UNTIL done END REPEAT;
-- 关闭游标
CLOSE ordernumbers;
END $
DELIMITER ;
```
<br><br><br>
# 触发器 Trigger
触发器是 MySQL 的一种数据库对象，用以设置在**指定表发生数据修改（`INSERT`、`UPDATE`、`DELETE`操作）时**，**自动触发并执行一段预定义的 SQL 代码**。
特点：**==数据操作触发== & ==自动调用==**：在对指定表执行 `INSERT`、`UPDATE`、`DELETE` 操作时自动触发。
使用场景：
- **数据校验**：在插入或更新数据时自动验证输入的有效性。
- **日志审计**：自动记录对数据表的变更，如记录插入、更新、删除的操作日志。
- **数据同步**：当主表发生变动时，自动更新同步相关的从表
> [!caution] 触发器的限制：
>
> 1. **每个表的每种操作只能有一个触发器**：<br>MySQL 限制**一个表的同一操作只能有一个 `BEFORE` 和一个 `AFTER` 触发器**。
> 2. **不支持触发器的嵌套调用**：触发器的执行不能再触发另一个触发器。
> 3. **性能问题**：在高并发或大规模操作时，复杂的触发器可能会导致性能问题，因此不建议在大型应用中频繁使用复杂的触发器逻辑。
>
>
> [!caution] 视图不支持触发器，只有真实表才支持。
#### 创建触发器
创建触发器需给出的四个信息：
- 触发器名（唯一）
- 触发器**何时执行**：`BEFORE` 或 `AFTER`
- 触发器**响应的操作**：`INSERT` 或 `UPDATA` 或`DELETE`
- 触发器**关联的表**
```sql
CEREATE TRIGGER 触发器名
<BEFORE|AFTER> -- 定义触发器在"操作执行前"还是"执行后"执行触发器
<INSERT|DELETE|UPDATE> -- 指定触发器响应的操作
ON 表名 -- 触发器绑定的表
FOR EACH ROW
BEGIN
触发器逻辑
END;
```
> [!NOTE] 触发器中的 `NEW` 与 `OLD`
> ![[_attachment/02-开发笔记/08-数据库/SQL 语句.assets/IMG-SQL 语句-8D7D567B4E5145879D9A6E27967F1A41.png|561]]
> - `NEW` 代表将插入、或者将修改覆盖的新记录，其中各列的值可被修改；
> - `OLD` 代表修改或删除前的值，不能被修改，只读。
> [!caution] 触发器中不允许返回结果集！（即使用 `SELECT`）
示例一：
```sql
-- 使用触发器, 自动记录日志
DELIMITER $
CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
INSERT INTO employees_log (action, employee_name, log_time)
VALUES ('Insert', NEW.name, NOW());
END $
DELIMITER ;
```
示例二：
```sql
-- 使用触发器, 纠正插入值的范围只能是[1, 10].
DELIMITER $
CREATE TRIGGER bi_t1
BEFORE INSERT ON t1
FOR EACH ROW
BEGIN
IF NEW.m1 < 1 THEN
SET NEW.m1 = 1;
ELSEIF NEW.m1 > 10 THEN
SET NEW.m1 = 10;
END IF;
END $
DELIMITER ;
```
示例三：
```sql
DELIMITER $
CREATE TRIGGER deleteorder
BEFORE DELETE ON order
FOR EACH ROW
BEGIN
INSERT INTO archive_orders(order_num, order_date, cust_id)
VALUES(OLD.order_num, OLD.order_date, OLD.cust_id); -- 引用OLD, 其代表将被删除的行.
END $
DELIMITER ;
```
#### 查看触发器
```sql
-- 查看所有触发器的语句
SHOW TRIGGERS;
-- 查看指定触发器的定义语句
SHOW CREATE TRIGGER 触发器名;
```
#### 删除触发器
```sql
DROP TRIGGER 触发器名;
```
<br><br><br>
# 事件 Event
MySQL 中的事件（Event） 是一种**定时调度任务**，用于**在指定的时间或定期执行某些 SQL 语句**。
类似于操作系统中的 **Cron 作业** 或 **定时任务**。
事件支持两种自动执行方式：
- **在某个==确定时间点==执行**（一次性）
- **每==隔一段时间==执行一次**（周期性）
> [!caution] MySQL 默认未开启事件，可通过设置下列 "系统变量" 开启：
>
> - `SET GLOBAL event_scheduler = ON`
#### 创建事件
```sql
CREATE EVENT 事件名
ON SCHEDULE
<AT timestamp | EVERY interval [STARTS datetime][END datetime]>
DO
BEGIN
具体语句
END;
```
示例二：创建定时事件
```sql
DELIMITER $
CREATE EVENT insert
ON SCHEDULE
AT '2019-09-04 15:48:54'
DO
BEGIN
INSERT INTO t1(m1, n1) VALUES(6, 'f');
END$
DELIMITER ;
```
示例二：创建周期性事件
```sql
DELIMITER $
CREATE EVENT insert_t1
ON SCHEDULE
-- 若未指定事件开始/结束时间, 则将无限制地周期性执行
EVERY 1 HOUR [STARTS '2019-09-04 15:48:54'] [ENDS '2019-09-16 15:45:54']
DO
BEGIN
INSERT INTO t1(m1, n1) VALUES(6, 'f');
END$
DELIMITER ;
```
#### 查看事件
```sql
-- 查看当前数据库(指定数据库)中定义的所有事件
SHOW EVENTS [FROM database_name];
-- 查看指定事件的具体定义
SHOW CREATE EVENTS;
```
#### 删除事件
```sql
-- 删除事件
DROP EVENT 事件名;
```
<br><br>
# 参考资料
# Footnotes
[^1]: 《**MySQL 必知必会**》