MySQL之存储过程，存储函数，触发器详解

最新推荐文章于 2025-09-24 16:20:48 发布

原创

最新推荐文章于 2025-09-24 16:20:48 发布 · 1.6k 阅读

华子目录

存储过程
- 什么是存储过程？
- 特点
- 存储过程的创建
- 存储过程的调用
- 存储过程的查看
- 存储过程的删除
变量
- 系统变量
- - 查看系统变量
  - 设置系统变量
- 用户定义变量
- - 赋值
  - 使用
- 局部变量
- - 声明
  - 赋值
if条件
- if案例
参数
- 参数案例
case语法
- 语法一
- 语法二
- case案例
循环
- while循环
- - while循环案例
- repeat循环
- - repeat循环案例
- loop循环
- - loop循环案例
游标cursor
- 游标案例
- 条件处理程序handler
存储函数
- 存储函数案例
触发器
- 创建
- 查看
- 删除
触发器案例
- 插入数据时触发
- 修改数据时触发
- 删除数据时触发

存储过程

什么是存储过程？

存储过程是事先经过编译并存储在数据库中的一段 SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。
存储过程思想上很简单，就是数据库 SOL语言层面的代码封装与重用。

在这里插入图片描述

特点

封装，复用
可以接收参数，也可以返回数据
减少网络交互，效率提升

在这里插入图片描述

存储过程的创建

mysql> create procedure 存储过程名([参数列表])
	-> begin
	->     SQL语句;
	-> end;

在这里插入图片描述

存储过程的调用

mysql> call 存储过程名([参数列表]);

在这里插入图片描述
注：在命令行中，执行创建存储过程的SQL时，需要通过关键字delimiter指定SQL语句的结束符

mysql> delimiter @   #指定结束符
mysql> select * from employee@
+------+-----------+-----------------+--------+-------+--------+
| id   | name      | job             | salary | bonus | job_id |
+------+-----------+-----------------+--------+-------+--------+
|  101 | 麦当      | 后端研发        |  25000 |  5000 |   1003 |
|  102 | 咕咚      | 网络运维        |  15000 |  3000 |   1003 |
|  103 | 迪亚      | 测试工程师      |  12000 |  2000 |   1003 |
|  104 | 米龙      | 后端开发        |  20000 |  3500 |   1003 |
|  105 | 极光      | 前端开发        |  15000 |  2500 |   1003 |
|  106 | 村长      | 人力资源        |  10000 |   500 |   1001 |
|  107 | 五条人    | 销售工程师      |  14000 |  7000 |   1002 |
|  108 | 皇帝      | 董事长          |  30000 | 10000 |   1004 |
+------+-----------+-----------------+--------+-------+--------+


mysql> create procedure p1()
    -> begin
    -> select count(*) from employee;
    -> end@
Query OK, 0 rows affected (0.04 sec)


mysql> call p1()@
+----------+
| count(*) |
+----------+
|        8 |
+----------+

在这里插入图片描述

存储过程的查看

查询指定数据库的存储过程及状态信息

mysql> select * from information_schema.routines where routine_schema='XXX'  #XXX为库名

mysql> select * from information_schema.routines where routine_schema='huazi';  # 查看huazi库
+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+----------------+--------------+------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------------+----------------------+--------------------+
| SPECIFIC_NAME | ROUTINE_CATALOG | ROUTINE_SCHEMA | ROUTINE_NAME | ROUTINE_TYPE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | DTD_IDENTIFIER | ROUTINE_BODY | ROUTINE_DEFINITION                       | EXTERNAL_NAME | EXTERNAL_LANGUAGE | PARAMETER_STYLE | IS_DETERMINISTIC | SQL_DATA_ACCESS | SQL_PATH | SECURITY_TYPE | CREATED             | LAST_ALTERED        | SQL_MODE
                                                            | ROUTINE_COMMENT | DEFINER        | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | DATABASE_COLLATION |
+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+----------------+--------------+------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------------+----------------------+--------------------+
| p1            | def             | huazi          | p1           | PROCEDURE    |           |                     NULL |                   NULL |              NULL |          NULL |               NULL | NULL               | NULL           | NULL           | SQL          | begin
select count(*) from employee;
end |          NULL | SQL               | SQL             | NO               | CONTAINS SQL    |     NULL | DEFINER       | 2024-03-04 23:02:54 | 2024-03-04 23:02:54 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |                 | root@localhost | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+----------------+--------------+------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------------+----------------------+--------------------+

查询某个存储过程的定义

mysql> show create procedure 存储过程名;

mysql> show create procedure p1;
+-----------+-----------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode                                                                                                              | Create Procedure
                                               | character_set_client | collation_connection | Database Collation |
+-----------+-----------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| p1        | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
begin
select count(*) from employee;
end | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+-----------+-----------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

存储过程的删除

mysql> drop procedure [if exists] 存储过程名;

在这里插入图片描述

变量

系统变量

系统变量是MySQL服务器提供的，不是用户定义的，属于服务器层面。分为全局变量（global），会话变量（session），默认是session

查看系统变量

#查看所有系统变量
mysql> show [session/global] variables; 


#模糊匹配查找变量
mysql> show [session/global] variables like '......';


#查看指定变量的值
mysql> select @@[session/global]系统变量名;

设置系统变量

mysql> set [session/global] 系统变量名 = 值;
或
mysql> set @@[session/global]系统变量名 = 值;

在这里插入图片描述

用户定义变量

用户定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接使用 @变量名就可以，其作用域为当前会话（在其他会话中无效）
注：用户定义的变量无需对其进行声明或初始化，只不过获取到的值为null（如果不给用户变量赋值就是null）

赋值

mysql> set @变量名=值;
或
mysql> set @变量名:=值;

mysql> select @变量名:=值;
或
mysql> select 字段 into @变量名 from 表名;

使用

mysql> select @变量名;

在这里插入图片描述

mysql> set @myname = '华子';
Query OK, 0 rows affected (0.00 sec)

mysql> set @myage = 18;
Query OK, 0 rows affected (0.00 sec)

mysql> set @mygender = '男',@myhobby = '编程';
Query OK, 0 rows affected (0.00 sec)


mysql> select @mycolor := 'red';
+-------------------+
| @mycolor := 'red' |
+-------------------+
| red               |
+-------------------+


mysql> select count(*) into @mycount from employee;
Query OK, 1 row affected (0.04 sec)


mysql> select @myname,@myage,@mygender,@myhobby,@mycolor,@mycount;
+---------+--------+-----------+----------+----------+----------+
| @myname | @myage | @mygender | @myhobby | @mycolor | @mycount |
+---------+--------+-----------+----------+----------+----------+
| 华子    |     18 | 男        | 编程     | red      |        8 |
+---------+--------+-----------+----------+----------+----------+


mysql> set @myage := 22;  #修改myage的值
Query OK, 0 rows affected (0.00 sec)


mysql> select @myage;
+--------+
| @myage |
+--------+
|     22 |
+--------+

在这里插入图片描述

局部变量

局部变量是根据需要定义的在局部生效的变量，访问之前，需要declare声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的begin … end块。

声明

mysql> declare 变量名 变量类型 [default 值];

变量类型和数据库字段类型一样：int，bigint，char，varchar，date，time等

赋值

mysql> set 变量名=值;
或
mysql> set 变量名:=值;
或
mysql> select 字段 into 变量名 from 表名 [where条件];

在这里插入图片描述

mysql> delimiter $


mysql> create procedure p2()
    -> begin
    -> declare employee_count int default 0;  #定义employee变量，并默认值为0
    -> select count(*) into employee_count from employee; #将查出来的count(*)结果赋值给employee_count变量
    -> select employee_count;
    -> end$
Query OK, 0 rows affected (0.01 sec)


mysql> call p2()$   #调用p2存储过程
+----------------+
| employee_count |
+----------------+
|              8 |
+----------------+


mysql> show create procedure p2$
+-----------+-----------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode
           | Create Procedure
                                                                 | character_set_client | collation_connection | Database Collation |
+-----------+-----------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| p2        | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `p2`()
begin
declare employee_count int default 0;
select count(*) into employee_count from employee;
select employee_count;
end | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+-----------+-----------------------------------------------------------------------------