MySQL存储函数和触发器

MySQL存储过程和函数

1.存储过程和函数的概念

• 存储过程和函数是 事先经过编译并存储在数据库中的一段 SQL 语句的集合

2.存储过程和函数的好处

• 存储过程和函数可以重复使用，减轻开发人员的工作量。类似于java中方法可以多次调用
• 减少网络流量，存储过程和函数位于服务器上，调用的时候只需要传递名称和参数即可
• 减少数据在数据库和应用服务器之间的传输，可以提高数据处理的效率
• 将一些业务逻辑在数据库层面来实现，可以减少代码层面的业务处理

3.存储过程和函数的区别

• 函数必须有返回值
• 存储过程没有返回值
  ```sql / 该关键字用来声明sql语句的分隔符，告诉MySQL该段命令已经结束！ sql语句默认的分隔符是分号，但是有的时候我们需要一条功能sql语句中包含分号，但是并不作为结束标识。 这个时候就可以使用DELIMITER来指定分隔符了！ / — 标准语法 DELIMITER 分隔符

— 创建存储过程 CREATE PROCEDURE 存储过程名称(参数…) BEGIN sql语句; END[分隔符]

— 调用存储过程 CALL 存储过程名称(实际参数);

— 删除存储过程 DROP PROCEDURE [IF EXISTS] 存储过程名称;

— 查看存储过程 SHOW PROCEDURE STATUS LIKE ‘存储过程名称’; — 状态 SHOW CREATE PROCEDURE 存储过程名称; — 语句

— 查询数据库中所有的存储过程 标准语法 SELECT * FROM mysql.proc WHERE db=’数据库名称’;

<a name="eD1yV"></a>
## 存储函数
-  存储函数和存储过程是非常相似的。存储函数可以做的事情，存储过程也可以做到！     
-  存储函数有返回值，存储过程没有返回值(参数的out其实也相当于是返回数据了)     
```sql
-- 创建存储函数
DELIMITER $
-- 标准语法
CREATE FUNCTION 函数名称([参数 数据类型])
RETURNS 返回值类型
BEGIN
    执行的sql语句;
    RETURN 结果;
END$
DELIMITER ;
-- 调用存储函数
SELECT 函数名称(实际参数);
-- 删除存储函数
DROP FUNCTION 函数名称;
/*
    定义存储函数，获取学生表中成绩大于95分的学生数量
*/
DELIMITER $
CREATE FUNCTION fun_test1()
RETURNS INT
BEGIN
    -- 定义统计变量
    DECLARE result INT;
    -- 查询成绩大于95分的学生数量，给统计变量赋值
    SELECT COUNT(*) INTO result FROM student WHERE score > 95;
    -- 返回统计结果
    RETURN result;
END$
DELIMITER ;
-- 调用fun_test1存储函数
SELECT fun_test1();

MySQL触发器

1.触发器的概念

• 触发器是与表有关的数据库对象，可以在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句。触发器的这种特性可以协助应用在数据库端确保数据的完整性 、日志记录 、数据校验等操作 。
• 使用别名 NEW 和 OLD 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。
  | 触发器类型 | OLD的含义 | NEW的含义 | | —- | —- | —- | | INSERT 型触发器 | 无 (因为插入前状态无数据) | NEW 表示将要或者已经新增的数据 | | UPDATE 型触发器 | OLD 表示修改之前的数据 | NEW 表示将要或已经修改后的数据 | | DELETE 型触发器 | OLD 表示将要或者已经删除的数据 | 无 (因为删除后状态无数据) |

-- 创建触发器
DELIMITER $
CREATE TRIGGER 触发器名称
BEFORE|AFTER INSERT|UPDATE|DELETE
ON 表名
[FOR EACH ROW]  -- 行级触发器
BEGIN
    触发器要执行的功能;
END$
DELIMITER ;
/*
*/
-- 创建db9数据库
CREATE DATABASE db9 character set utf8;
-- 使用db9数据库
USE db9;
-- 创建账户表account
CREATE TABLE account(
    id INT PRIMARY KEY AUTO_INCREMENT,    -- 账户id
    NAME VARCHAR(20),                    -- 姓名
    money DOUBLE                        -- 余额
);
-- 添加数据
INSERT INTO account VALUES (NULL,'张三',1000),(NULL,'李四',2000);
-- 创建日志表account_log
CREATE TABLE account_log(
    id INT PRIMARY KEY AUTO_INCREMENT,    -- 日志id
    operation VARCHAR(20),                -- 操作类型 (insert update delete)
    operation_time DATETIME,            -- 操作时间
    operation_id INT,                    -- 操作表的id
    operation_params VARCHAR(200)       -- 操作参数
);

创建INSERT触发器

-- 创建INSERT触发器
DELIMITER $
CREATE TRIGGER account_insert
AFTER INSERT
ON account
FOR EACH ROW
BEGIN
    INSERT INTO account_log VALUES (NULL,'INSERT',NOW(),new.id,CONCAT('插入后{id=',new.id,',name=',new.name,',money=',new.money,'}'));
END$
DELIMITER ;
-- 向account表添加记录
INSERT INTO account VALUES (NULL,'王五',3000);
-- 查询account表
SELECT * FROM account;
-- 查询日志表
SELECT * FROM account_log;

创建UPDATE触发器

-- 创建UPDATE触发器
DELIMITER $
CREATE TRIGGER account_update
AFTER UPDATE
ON account
FOR EACH ROW
BEGIN
    INSERT INTO account_log VALUES (NULL,'UPDATE',NOW(),new.id,CONCAT('修改前{id=',old.id,',name=',old.name,',money=',old.money,'}','修改后{id=',new.id,',name=',new.name,',money=',new.money,'}'));
END$
DELIMITER ;
-- 修改account表
UPDATE account SET money=3500 WHERE id=3;
-- 查询account表
SELECT * FROM account;
-- 查询日志表
SELECT * FROM account_log;

创建DELETE触发器

-- 创建DELETE触发器
DELIMITER $
CREATE TRIGGER account_delete
AFTER DELETE
ON account
FOR EACH ROW
BEGIN
    INSERT INTO account_log VALUES (NULL,'DELETE',NOW(),old.id,CONCAT('删除前{id=',old.id,',name=',old.name,',money=',old.money,'}'));
END$
DELIMITER ;
-- 删除account表数据
DELETE FROM account WHERE id=3;
-- 查询account表
SELECT * FROM account;
-- 查询日志表
SELECT * FROM account_log;

查看触发器

-- 标准语法
SHOW TRIGGERS;
-- 查看触发器
SHOW TRIGGERS;

删除触发器

-- 标准语法
DROP TRIGGER 触发器名称;
-- 删除DELETE触发器
DROP TRIGGER account_delete;