Mysql小结( 存储过程&存储函数&触发器 )

发布时间：2023-07-31 09:30

存储过程：

特点：入参必须定义参数种类，类型in被call则直接出结果，其余两种都类型得出结果的会被封装在指定的对象里，需另外再select这个对象才会出结果，下面见详解：

创建存储过程语法如下 —— 参数种类： in，out ，inout 参数：即入参的数据 数据类型：即规定入参数据的类型，如varchar,int…

CREATE PROCEDURE 存储过程名称(  参数的种类1 参数1 数据类型1
                            ,参数的种类2 参数2 数据类型2)
                            BEGIN
                                处理内容
                           END

调用存储过程语法如下：

CALL 存储过程名称(参数，....)

注意: 由于sql语句结束的分隔符是";"，所以在创建存储过程之前，必须要用DELIMITER + [自定义分隔符]，来自定义分隔符，然后再存储过程结尾，用这个自定义的分隔符将存储过程与其他sql语句分开来，实例如下：

-- 改变分隔符
DELIMITER //
-- 创建存储过程
mysql> create procedure sp_search_user (in name varchar(20));
    -> begin
    -> if name is null or name='' then
    ->    select * from user;
    -> else 
    ->    select * from user where username like name;
    -> end if;
    -> end
    -> // -- 执行sql
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ; -- 恢复分隔符

定义in则只会有入参，而且不会将结果封装在一个对象里，call后得出的结果会直接返回。

 create procedure p1(in n int)
 begin
 declare total int default 0; // 申明变量用 declare
 declare num int default 0;
 while num < n do
 set num:=num+1;  // 对一个变量赋值用 set 
 set total:=total+num;
 end while;
 select total;
 end
 
 call p1(10)  //调用存储过程用call

定义out则没有入参，而且得出的结果会封装在out自定义的对象里，执行完call存储过程后需要再单独查询这个out对象才有结果。注意这个out对象名必须是 @ 开头。

 create procedure p2(in n int,out total int)
 begin
 declare num int default 0;
 set total:=0;
 while num < n do
 set num:=num+1;
 set total:=total+num;
 end while;
 end
 
 call p2(100,@sum) //这里的@sum就是我定义用来接收处处total的值
 select @sum

定义inout则代表这个对象即是入参对象，也是结果得出后被重新封装的出参对象。

create procedure p3(inout age int)
begin
set age:=age+10;
end

set @currentAge=8
call p3(@currentAge)
select @currentAge

删除存储过程语法如下：

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

查看存储过程状态语法以及实例如下：

SHOW PROCEDURE STATUS [LIKE 'pattern']

-- 查看的sp_order_sum_price状态
mysql> SHOW PROCEDURE STATUS LIKE 'sp_order_sum_price' \G;
*************************** 1. row ***************************
                  Db: webshop
                Name: sp_order_sum_price
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2017-04-17 09:38:52
             Created: 2017-04-17 09:38:52
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

存储函数：

特点：和存储过程的区别就是1.不用定义入参种类。2.在创建语句中需要加一个returns(预先指定返回值的类型)

创建存储过程语法如下：

CREATE FUNCTION 函数([参数类型 数据类型[,….]]) RETURNS 返回类型 
　　BEGIN
　　　  SQL语句.....
　　　　RETURN (返回的数据)
　　END

案例参考见下方（获取一个订单的总价，并判断是否需要营业税收）：

DELIMITER //
mysql> create procedure sp_ordertotal( in onnumber int,in taxable boolean ,out ototal decimal(8,2) )
    -> begin
    -> -- 定义变量：总价
    -> declare total decimal(8,2);
    -> -- 定义默认税收率
    -> declare taxrate int default 6;
    -> -- 关联查询并计算总价
    -> select sum( price * items_num) from orderdetail  as od 
    ->  inner join items as it  on it.id=od.items_id
    ->  where od.orders_id = onnumber 
    ->  into total; --赋值
    -> -- 判断是否需要营业税收
    -> if taxable then 
    ->  select total + (total/100 * taxrate) into total;
    -> end if;
    -> -- 赋值给输出参数
    -> select total into ototal;
    -> end
    -> //
Query OK, 0 rows affected (0.01 sec)
DELIMITER ; 

-- 执行存储过程
mysql> call sp_ordertotal(3,false,@total)//
Query OK, 1 row affected (0.00 sec)

-- 查询总价
mysql> select @total //
+----------+
| @total   |
+----------+
| 41244.90 |
+----------+
1 row in set (0.00 sec)

删除存储函数语法如下：

DROP FUNCTION [IF EXISTS] fn_name;

下面简要总结一下存储过程和存储函数的区别：

存储过程可以有多个in,out,inout参数，而存储函数只有输入参数类型，而且不能带in
存储过程实现的功能要复杂一些；而存储函数的单一功能性(针对性)更强。
存储过程可以返回多个值；存储函数只能有一个返回值。
存储过程一般独立的来执行；而存储函数可以作为其他SQL语句的组成部分来出现。
存储过程可以调用存储函数。但函数不能调用存储过程。

触发器：

特点：与存储过程的不同就是创建方式和调用方式不同。它不需要主动去call，而是在执行完inset、update、delete性质的操作前后自动执行调用，除此之外，变量定义及流程语句同样都与存储过程几乎无差异，创建触发器语法如下所示。

trigger_name：触发器名称，用户自行定义

trigger_time：触发时机，BEFORE或AFTER

trigger_event：触发事件，具有insert、update、delete性质的事件（并非严格意义上的命令，所以LOAD DATA 和 REPLACE 语句也也属于触发器的触发事件之一）

tbl_name：表示在哪张表上建立触发器

trigger_stmt：触发器程序体（当前触发器被触发后需执行的语句，可以是一句SQL语句或者流程语句）

FOR EACH ROW ：在mysql中属于固定写法，指明触发器以行作为执行单位，也就是当用户执行删除命令删除3条数据，与删除动作相关的触发器也会被执行3次

CREATE TRIGGER trigger_name trigger_time
trigger_event ON tbl_name
FOR EACH ROW
BEGIN
trigger_stmt
END

实例如下（假设有user表和user_history表，现在要写一个触发器，在user表发生delete事件后，触发程序体 —— 将被delete的数据存于历史表user_history表中）：

-- user 表结构
mysql> desc user;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| name    | varchar(32)  | YES  | MUL | NULL    |                |
| pinyin  | varchar(32)  | YES  |     | NULL    |                |
| birth   | varchar(32)  | YES  |     | NULL    |                |
| sex     | char(1)      | YES  |     | NULL    |                |
| address | varchar(256) | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

-- 历史表 user_history 其中updated字段为删除日期
mysql> desc user_history;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| uid     | int(11)      | NO   |     | NULL    |       |
| name    | varchar(32)  | YES  |     | NULL    |       |
| pinyin  | varchar(32)  | YES  |     | NULL    |       |
| birth   | varchar(32)  | YES  |     | NULL    |       |
| sex     |     char(1)  | YES  |     | NULL    |       |
| address | varchar(255) | YES  |     | NULL    |       |
| updated | datetime     | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

DELIMITER //
-- 创建触发器
mysql> create trigger trg_user_history after delete
    ->   on user for each row
    -> begin
    ->  insert into user_history(uid,name,pinyin,birth,sex,address,updated)
    ->  values(OLD.id,OLD.name,OLD.pinyin,OLD.birth,OLD.sex,OLD.address,NOW());
    -> end
    -> //
Query OK, 0 rows affected (0.02 sec)
DELIMITER ;

上述sql中创建语句的形式与前面的存储过程或者存储函数都很类似，这里有点要注意的是，使用OLD/NEW关键字可以获取数据变更前后的记录，其中OLD用于AFTER时刻，而NEW用于BEFORE时刻的变更。如OLD.name表示从user表删除的记录的名称。INSERT操作一般使用NEW关键字，UPDATE操作一般使用NEW和OLD，而DELETE操作一般使用OLD。现在我们从user表删除一条数据，然后查看user_history表的数据。

-- 删除user中id为60的用户数据
mysql> delete from user where id =60;
Query OK, 1 row affected (0.00 sec)

-- 查看历史表
mysql> select * from user_history;
+----+-----+-------------+------------+---------------------+------+---------+---------------------+
| id | uid | name        | pinyin     | birth               | sex  | address | updated             |
+----+-----+-------------+------------+---------------------+------+---------+---------------------+
|  1 |  60 | 高余粮10    | gaoyuliang | 1999-01-01 00:00:00 | 1    | NULL    | 2017-04-21 09:39:23 |
+----+-----+-------------+------------+---------------------+------+---------+---------------------+
1 row in set (0.00 sec)

查看触发器状态以及实例如下：

SHOW TRIGGERS [FROM schema_name];

mysql> SHOW TRIGGERS \G;
*************************** 1. row ***************************
             Trigger: trg_user_history --触发器名称
               Event: DELETE  --触发事件
               Table: user --触发器作用的表
           Statement: begin
insert into user_history(uid,name,pinyin,birth,sex,address,updated)
values(OLD.id,OLD.name,OLD.pinyin,OLD.birth,OLD.sex,OLD.address,NOW());
end
              Timing: AFTER
             Created: 2017-04-21 09:27:56.58
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

删除触发器语法如下

DROP TRIGGER 触发器名称

若有没懂或者待优化的地方，欢迎留言指出，万分感谢 ~

参考博文：[ MySQL的进阶实战篇 ]、[存储过程，in，out，inout的区别]

Mysql小结( 存储过程&存储函数&触发器 )

存储过程：

存储函数：

触发器：

相关推荐