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 操作#

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

1.3 案例#

[无参] 插入到 admin 表中 5 条记录；

[带 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 $

[带 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 $

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

[带 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 $

[带 INOUT 模式] 传入 a 和 b 两个变量，调用过程后，a 和 b 的值翻倍；

创建过程实现传入一个日期，格式化成：xx 年 xx 月 xx 日并返回；

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

创建过程实现传入 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 $

创建过程实现根据传入的条目数和起始索引，查询 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 操作#

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

2.3 案例#

[无参有返回值] 返回 beauty 表记录数；

[有参有返回] 根据员工名，返回其工资；

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 $

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

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 $

创建函数，传入两个 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 表的数据变更日志，包含增加、修改、删除。

首先创建一张日志表；

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;

创建 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 ;

创建 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 ;

创建 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;

posted @ 2020-09-06 19:08 tree6x7 阅读(200) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

阅读排行：
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 单线程的Redis速度为什么快？

Loading

◥(ฅºωºฅ)◤