04-存储过程&函数&触发器

1. 存储过程

过程化 SQL 块主要有 2 种类型，即命名块和匿名块。

比如常见的单条 SQL 就是匿名块。匿名块每次执行时都要进行编译，它不能被存储到 DB 中，也不能在其他过程化 SQL 块中调用。

过程和函数是命名块，它们被编译后保存在 DB 中，称为“持久性存储模块”，可以被反复调用，运行速度较快。

存储过程是由过程化 SQL 语句书写的过程，这个过程经过编译和优化后存储在 DB 服务器中，因此称它为“存储过程”，使用时只要调用即可。

1.1 定义

CREATE OR REPLACE PROCEDURE <存储过程名>([参数1, 参数2, ...]) /* 存储过程首部 */
BEGIN
    /* 存储过程体, 描述该存储过程的操作, 是一组 SQL 语句 */
END

• 存储过程包括〈过程首部〉和〈过程体〉；
• 在过程首部，“过程名”是 DB 服务器合法的对象标识；
• 参数列表包含 3 部分：参数模式 参数名 参数类型（e.g. IN stuName VARCHAR(20)）
  • IN 该参数可作为输入，调用方需要传入值到该参数中
  • OUT 可作为输出，也就是过程返回值
  • INOUT 既可以作为输入，又可以作为输出
• 过程体 // 如果只有一句话，BEGIN...END 可省
  • 存储过程体中的每条 SQL 语句的结尾要求必须加 ;
  • 存储过程的结尾可以使用 DELIMITER 重新设置

1.2 操作

1. 执行存储过程：CALL <过程名>(实参列表);；
2. 删除存储过程：DROP PROCEDURE <过程名>;；
3. 查看存储过程：SHOW CREATE PROCEDURE <过程名>;；

1.3 案例

1. [无参] 插入到 admin 表中 5 条记录；
   
2. [带 IN 模式] 创建过程实现根据 beauty.name 查询对应的 boy 信息；

   CREATE PROCEDURE myPro2(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 $
   

3. [带 IN 模式] 创建过程实现用户是否登录成功；

   CREATE PROCEDURE myPro3(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 $
   

4. [带 OUT 模式] 根据 beauty.name，返回对应的 boy.boyName；
   
5. [带 OUT 模式] 根据 beauty.name，返回对应的 boy.id 和 boy.boyName；

   CREATE PROCEDURE myPro5(IN bName VARCHAR(20), OUT id VARCHAR(20), OUT name VARCHAR(20))
   BEGIN
       SELECT bo.id, bo.boyName INTO id, name
       FROM boys bo
       INNER JOIN beauty b ON bo.id = b.boyfriend_id
       WHERE b.name = bName;
   END $
   

6. [带 INOUT 模式] 传入 a 和 b 两个变量，调用过程后，a 和 b 的值翻倍；
   
7. 创建过程实现传入一个日期，格式化成：xx 年 xx 月 xx 日并返回；

   CREATE PROCEDURE myPro7(IN myDate DATETIME, OUT dateStr VARCHAR(30))
   BEGIN
       SELECT DATE_FORMAT(myDate, '%y 年 %m 月 %d 日') INTO dateStr;
   END $
   

8. 创建过程实现传入 beauty.name，返回：'beauty.name and boys.name' 格式的字符串；

   CREATE PROCEDURE myPro8(IN beautyName VARCHAR(20), OUT result VARCHAR(20))
   BEGIN
       SELECT CONCAT(beautyName, ' and ', IFNULL(boyName, 'NULL')) INTO result
       FROM boys bo
       RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
       WHERE b.name = beautyName;
   END $
   

9. 创建过程实现根据传入的条目数和起始索引，查询 beauty 表的记录；

   CREATE PROCEDURE myPro8(IN startIndex INT, IN size INT)
   BEGIN
       SELECT * FROM beauty LIMIT startIndex, size;
   END $
   

2. 函数

函数也称为“自定义函数”，因为是用户自己使用过程化 SQL 设计定义的。函数和存储过程类似，都是持久性存储模块。函数的定义和存储过程也类似，不同之处是函数必须指定返回的类型。

• 存储过程：可以有 0 个返回，也可以有多个返回，适合做批量插入，批量更新；
• 函数：有且只有 1 个返回，适合做处理数据后返回一个结果。

2.1 定义

CREATE FUNCTION <函数名>([参数名 参数类型 ...]) RETURNS <类型>
BEGIN
    /* 函数体 */
    return 值;
END $

2.2 操作

1. 调用函数：SELECT 函数名(参数列表)
2. 删除函数：DROP FUNCTION <函数名>
3. 查看函数：SHOW CREATE FUNCTION <函数名>

2.3 案例

1. [无参有返回值] 返回 beauty 表记录数；
   
2. [有参有返回] 根据员工名，返回其工资；

   CREATE FUNCTION func2(empName VARCHAR(20)) RETURNS DOUBLE
   BEGIN
       SET @sal=0; # 定义用户变量
       SELECT salary INTO @sal
       FROM employees WHERE last_name = empName;
       RETURN @sal;
   END $
   

3. 根据部门名返回该部门的平均工资；

   CREATE FUNCTION func3(deptName VARCHAR(20)) RETURNS DOUBLE
   BEGIN
       DECLARE avgSal DOUBLE;
       SELECT AVG(salary) INTO avgSal
       FROM employees e RIGHT JOIN departments d
       ON e.department_id = d.department_id
       WHERE d.department_name = deptName;
       RETURN avgSal;
   END $
   

4. 创建函数，传入两个 float，返回二者之和；

   CREATE FUNCTION func4(num1 FLOAT, num2 FLOAT) RETURNS FLOAT
   BEGIN
       DECLARE sum FLOAT DEFAULT 0;
       SET sum = num1 + num2;
       RETURN sum;
   END $
   

3. 触发器

触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的 SQL 语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性、日志记录、数据校验等操作 。

使用别名 OLD 和 NEW （也称为“行记录变量”）来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

3.1 创建

CREATE TRIGGER trigger_name
BEFORE/AFTER insert/update/delete
ON tbl_name
[ FOR EACH ROW ] -- 行级触发器
BEGIN
    trigger_stmt;
END;

e.g. 通过触发器记录 emp 表的数据变更日志，包含增加、修改、删除。

1. 首先创建一张日志表；

   create table emp_logs(
       id int(11) not null auto_increment,
       operation varchar(20) not null comment '操作类型, insert/update/delete',
       operate_time datetime not null comment '操作时间',
       operate_id int(11) not null comment '操作表的ID',
       operate_params varchar(500) comment '操作参数',
       primary key(`id`)
   )engine=innodb default charset=utf8;
   

2. 创建 insert 型触发器，完成插入数据时的日志记录；

   DELIMITER $
   CREATE TRIGGER emp_logs_insert_trigger
   AFTER insert
   ON emp
   for each row
   BEGIN
       insert into emp_logs (id,operation,operate_time,operate_id,operate_params)
       values (null, 'insert', now(), new.id, concat('插入后(id:', new.id, ',name:',
       new.name, ',age:', new.age, ',salary:', new.salary, ')'));
   END $
   DELIMITER ;
   

3. 创建 update 型触发器，完成更新数据时的日志记录；

   DELIMITER $
   CREATE TRIGGER emp_logs_update_trigger
   AFTER update
   ON emp
   for each row
   BEGIN
       insert into emp_logs (id,operation,operate_time,operate_id,operate_params)
       values(null, 'update', now(), new.id, concat('修改前(id:', old.id, ',name:',
       old.name, ',age:', old.age, ',salary:', old.salary, '),修改后(id', new.id,
       'name:', new.name, ',age:', new.age, ',salary:', new.salary, ')'));
   END $
   DELIMITER ;
   

4. 创建 delete 行的触发器，完成删除数据时的日志记录。

   DELIMITER $
   CREATE TRIGGER emp_logs_delete_trigger
   AFTER delete
   ON emp
   for each row
   BEGIN
       insert into emp_logs (id,operation,operate_time,operate_id,operate_params)
       values(null, 'delete', now(), old.id, concat('删除前(id:', old.id, ',name:',
       old.name, ',age:', old.age, ',salary:', old.salary, ')'));
   END $
   DELIMITER ;
   

3.2 删除

如果没有指定 schema_name，默认为当前数据库。

DROP TRIGGER [schema_name.]trigger_name

3.3 查看

可以通过执行 SHOW TRIGGERS 命令查看触发器的状态、语法等信息。

SHOW TRIGGERS\G;