深入解析MySQL(6)——存储过程、游标与触发器

1.存储过程

概念：存储过程是一组预编译的SQL语句集合，存储在数据库中，可通过名称调用。支持条件判断、循环等逻辑控制，适合封装复杂业务逻辑

优势：性能优化：存储过程在创建时已经经过编译和优化，形成可执行的二进制代码，直接存储在数据库服务器中。性能高于单独执行时需要解析、编译的SQL语句代码复用：存储过程可以重复调用(类似C语言函数/Java方法)安全性高：避免应用程序直接访问数据库，通过存储过程间接访问(结合参数化查询)，从而保证安全性降低耦合：当表结构发生变化时，只需要修改相应的存储过程，应用程序变动较小

缺点：移植性差：不同数据库创建存储过程的语法不同不适合高并发场景：高并发场景下，存储过程会进一步降低数据库的性能

语法：

-- 修改SQL语句结束符为//delimiter//-- 创建存储过程createprocedureifnotexists 存储过程名(参数列表)begin-- SQL语句end//-- 还原SQL语句结束符为;delimiter;-- 调用存储过程call 存储过程名(参数列表);-- 删除存储过程dropprocedureifexists 存储过程名;-- 查看当前数据库已创建的存储过程select*from information_schema.routines where routine_schema = 数据库名;

在MySQL中，存储过程、函数等数据库对象的信息可以通过information_schema(数据库)中的routines(数据表)系统视图查询。这个视图包含所有数据库中的存储过程和函数的元数据信息routine_schema：所属的数据库名routine_name：存储过程/函数名称routine_type：对象类型(procedure或function)data_type：函数返回值类型，存储过程没有返回值routine_definition：存储过程/函数的定义语句

2.变量

2.1 系统变量

系统变量分为全局变量和会话变量会话变量：仅影响当前会话连接，每个客户端连接可以有自己的会话变量设置(默认从全局变量继承其初始值)

全局变量：影响整个服务器运行环境，MySQL服务器启动时从my.ini文件(默认文件路径如下)读取初始值

-- 查询全局/会话变量show[global|session] variables [like...];select @@[global|session].系统变量名;-- 修改全局/会话变量set[global|session] 系统变量名 = 值;set @@[global|session].系统变量名 = 值;

未指定[global|session]时默认查询/修改session变量上述修改操作仅为内存级修改，MySQL服务器启动时从my.ini文件读取初始值

2.2 用户变量

用户变量以@符号开头，作用域为当前会话连接，不需要提前声明。常在存储过程中作为参数传递，用于存储查询中间结果

-- 设置用户变量set@user_demo= 值;-- 查询用户变量select@user_demo;-- 设置并查询用户变量select@user_demo ：= 值;

2.3 局部变量

局部变量仅存在于存储过程、函数、触发器中，使用declare声明

delimiter//createprocedureifnotexists query()begindeclare num intdefault0;set num :=100;select num;end//delimiter;call query();

3.SQL编程

3.1 参数列表/if

if语法：

if 条件1then-- sql语句1elseifthen--sql语句2else-- sql语句3endif;

示例：

-- 示例1delimiter//createprocedure demo_if(in score int,out result varchar(20))beginif score >=90thenset result :='优秀';elseif score >=80and score <90thenset result ='良好';elseif score >=70and score <80thenset result ='及格';elseset result ='不及格';endif;end//delimiter;call demo_if(60,@result);select@result;-- 示例2set@score :=70;delimiter//createprocedure demo2_if(inout score int)beginset score := score +10;end//delimiter;call demo_if_2(@score);select@score;

3.2 case

语法：

-- 简单case表达式：将一个表达式与一组简单值进行比较case case_value when when_value1 then result1 when when_value2 then result2 ...else else_result end-- 搜索case表达式：将一个表达式与一组独立的布尔表达式进行比较casewhen condition1 then result1 when condition2 then result2 ...else else_result end

示例：

-- 示例1delimiter//createprocedure demo_case(in code int,out result varchar(50))begincase code when0thenset result :='成功';when10001thenset result :='用户名或密码错误';when10002thenset result :='没有权限';when20001thenset result :='传入参数有误';when20002thenset result :='无结果';elseset result :='内部错误';endcase;end//delimiter;call demo_case(10002,@result);select@result;-- 示例2delimiter//createprocedure demo2_case(inmonthint,out result varchar(50))begincasewhenmonth>=1andmonth<=3thenset result ='第一节度';whenmonth>=4andmonth<=6thenset result ='第二节度';whenmonth>=7andmonth<=9thenset result ='第三节度';whenmonth>=10andmonth<=12thenset result ='第四节度';elseset result ='非法输入';endcase;end//delimiter;call demo2_case(7,@result);select@result;

3.3 while

语法：

-- search_condition：循环条件表达式-- statement_list：循环体内要执行的SQL语句while search_condition do statement_list endwhile;

示例：

delimiter//createprocedure demo_while(in n int)begindeclare sum intdefault0;while n >0doset sum := sum + n;set n := n -1;endwhile;select sum;end//delimiter;call demo_while(100);

3.4 repeat

先执行一次statement_list，再判断search_condition是否成立

语法：

repeat statement_list until search_condition endrepeat;

示例：

delimiter//createprocedure demo_repeat(in n int)begindeclare sum intdefault0;repeatset sum := sum + n;set n := n -1; until n <=0endrepeat;select sum;end//delimiter;call demo_repeat(100);

3.5 loop

语法：

-- loop_label：标记loop的标签 loop_label:loop statement_list endloop loop_label;

控制loop循环的关键字：leave：用于退出循环，相当于其他语言中的breakiterate：用于跳过当前迭代，继续下一次循环，相当于其他语言中的continue

示例：

-- 求1-100所有偶数的和delimiter//createprocedure demo_loop(in n int)begindeclare total intdefault0; sum: loopif n <=0thenleave sum;endif;if n %2=1thenset n := n -1;iterate sum;endif;set total := total + n;set n := n -1;endloop sum;select total;end//delimiter;call demo_loop(100);

3.6 游标&条件处理程序

3.6.1 游标

游标(cursor)：是一种数据库对象，用于在存储过程或函数中逐行处理查询结果集。当游标遍历到结果集末尾时，会触发not found状态，需要结合条件处理程序结束循环，避免无限执行

语法：

-- 声明游标declare cursor_name cursorfor 查询语句 -- 打开游标open cursor_name;-- 获取游标记录fetch cursor_name into

3.6.2 条件处理程序

条件处理程序：用于捕获和处理SQL执行过程中的异常或特定状态，需与游标配合使用

语法：

declare handler_type handlerfor condition statement;-- 1.handler_type：条件处理程序类型(1)continue-- 继续执行后续语句(2)exit-- 终止当前begin...end块 -- 2.condition：指定了处理程序会响应的条件类型(1) mysql_error_code -- MySQL错误码(2) sqlstate sqlstate_value -- 表示特定的SQL状态码(3)not found -- 捕获所有以'02'开头的sqlstate代码(4) sqlwarning -- 捕获所有以'01'开头的sqlstate代码的警告(5)sqlexception -- 捕获所有不以'00'(成功)、'01'(警告)、'02'(未找到)开头的sqlstate代码的错误-- 3.statement：SQL语句

3.6.3 示例

初始化数据：

-- 创建班级表createtable class (id intprimarykey);-- 创建学生表createtable student(id intprimarykeyauto_increment,name varchar(20),sno varchar(10),age int,gender tinyintdefault1,enroll_date datetimedefaultnow(),class_id int,foreignkey(class_id)references class(id));-- 向班级表插入数据insertinto class values(1),(2);-- 向学生表插入数据insertinto student (name,sno,age,class_id)values('刘备','100001',18,1),('关羽','100002',18,1),('张飞','100003',18,2),('赵云','100004',18,2);

示例：

delimiter//createprocedure demo_cursor(in input_class_id int)begin-- 声明变量用于接收每一列的结果declare student_name varchar(20);declare class_id int;declare flg booldefaulttrue;-- 声明游标用于接收查询结果declare demo_cursor cursorforselect s.name as student_name,c.id as id from student as s,class as c where s.class_id = c.id and s.class_id = input_class_id;-- 声明处理程序declarecontinuehandlerfornot found set flg :=false;-- 创建新表createtable class_new (id intprimarykeyauto_increment,student_name varchar(20),class_id int,foreignkey(class_id)references class(id));-- 开启游标open demo_cursor; loop_read: loopfetch demo_cursor into student_name,class_id;ifnot flg thenleave loop_read;endif;insertinto class_new values(null,student_name,class_id);endloop loop_read;-- 关闭游标close demo_cursor;end//delimiter;call demo_cursor(1);

运行结果：

mysql>select*from class_new;+----+--------------+----------+| id | student_name | class_id |+----+--------------+----------+|1| 刘备 |1||2| 关羽 |1|+----+--------------+----------+

3.7 存储函数

存储函数：是一种存储在数据库中的可重用代码块，接收参数并返回一个值。与存储过程不同，存储函数必须包含返回值，且可以使用select直接调用

语法：

createfunction 存储函数名 (参数列表)returns 返回值类型 [characteristic]

characteristic：可选特性deterministic：表明函数对于相同的输入参数总是返回相同的结果not deterministic：默认选项，表示函数可能对相同的输入返回不同的结果no sql：表示函数不包含任何SQL语句reads sql data：表示函数包含读取数据的SQL语句(select)modifies sql data：表示函数包含修改数据的SQL语句(insert/update/delete)contains sql：表示函数仅包含读取数据的SQL语句，不修改数据

示例：

createfunction func1(n int)returnsintdeterministicbegindeclare`time`intdefault0;while n >0doset`time` :=`time`+ n;set n := n -1;endwhile;return`time`;end;select func1(100);

4.触发器

触发器：一种与表相关的数据库对象，在指定事件(如insert、update、delete)发生时自动执行预定义的SQL语句触发器类型：
1.从触发时机区分before触发器：在触发事件执行前激活after触发器：在触发事件完成后再执行

2.从触发事件区分insert触发器：响应数据插入操作update触发器：响应数据更新操作delete触发器：响应数据删除操作

3.从作用粒度区分行级触发器：针对受影响的每一行数据都会触发一次语句级触发器：整个SQL语句执行完毕后仅触发一次(MySQL暂不支持)触发器中的new和oldnew：表示触发事件中的新数据old：表示触发事件前的旧数据

语法：

createtrigger 触发器名 {before|after} {insert|update|delete} on 表名 for each rowbegin-- 触发器逻辑end;

初始化数据：

-- 创建日志表createtable log (id intprimarykeyauto_increment,operation_type varchar(20)notnullcomment'操作类型',operation_time datetimenotnullcomment'操作时间', operation_id intnotnullcomment'操作的记录的id',operation_date varchar(500)comment'操作数据');

4.1 insert触发器

-- insert触发器createtrigger trg_insert afterinserton student for each rowbegininsertinto log values(null,'insert',now(),new.id,concat(new.id,',',new.name,',',new.sno,',',new.age,',',new.gender,',',new.enroll_date,',',new.class_id));end;-- 向学生表插入数据insertinto student values(null,'马超',200001,18,1,now(),1);

执行结果：

mysql>select*from log;+----+----------------+---------------------+--------------+------------------------------------------+| id | operation_type | operation_time | operation_id | operation_date |+----+----------------+---------------------+--------------+------------------------------------------+|1|insert|2025-10-3117:25:17|9|9,马超,200001,18,1,2025-10-3117:25:17,1|+----+----------------+---------------------+--------------+------------------------------------------+

4.2 update触发器

-- update触发器createtrigger trg_update afterupdateon student for each rowbegininsertinto log values(null,'update',now(),new.id,concat(' 更新前数据: ',old.id,',',old.name,',',old.sno,',',old.age,',',old.gender,',',old.enroll_date,',',old.class_id,' ,更新后数据: ',new.id,',',new.name,',',new.sno,',',new.age,',',new.gender,',',new.enroll_date,',',new.class_id));end;-- 更新学生表的数据update student set age =20,enroll_date =now(),class_id =2where name ='马超';

执行结果：

mysql>select*from log;+----+----------------+---------------------+--------------+---------------------------------------------------------------------------------------------------------------+| id | operation_type | operation_time | operation_id | operation_date |+----+----------------+---------------------+--------------+---------------------------------------------------------------------------------------------------------------+|3|insert|2025-10-3117:40:14|10|10,马超,200001,18,1,2025-10-3117:40:14,1||4|update|2025-10-3117:41:11|10| 更新前数据: 10,马超,200001,18,1,2025-10-3117:40:14,1,更新后数据: 10,马超,200001,20,1,2025-10-3117:41:11,2|+----+----------------+---------------------+--------------+---------------------------------------------------------------------------------------------------------------+

4.1 delete触发器

-- delete触发器createtrigger trg_delete afterdeleteon student for each rowbegininsertinto log values(null,'delete',now(),old.id,concat('删除的数据: ',old.id,',',old.name,',',old.sno,',',old.age,',',old.gender,',',old.enroll_date,',',old.class_id));end;deletefrom student where name ='马超';

执行结果：

mysql>select*from student;+----+------+--------+------+--------+---------------------+----------+| id | name | sno | age | gender | enroll_date | class_id |+----+------+--------+------+--------+---------------------+----------+|1| 刘备 |100001|18|1|2025-10-2622:11:23|1||2| 关羽 |100002|18|1|2025-10-2622:11:23|1||3| 张飞 |100003|18|1|2025-10-2622:11:23|2||4| 赵云 |100004|18|1|2025-10-2622:11:23|2|+----+------+--------+------+--------+---------------------+----------+4rowsinset(0.00 sec) mysql>select*from log;+----+----------------+---------------------+--------------+---------------------------------------------------------------------------------------------------------------+| id | operation_type | operation_time | operation_id | operation_date |+----+----------------+---------------------+--------------+---------------------------------------------------------------------------------------------------------------+|3|insert|2025-10-3117:40:14|10|10,马超,200001,18,1,2025-10-3117:40:14,1||4|update|2025-10-3117:41:11|10| 更新前数据: 10,马超,200001,18,1,2025-10-3117:40:14,1,更新后数据: 10,马超,200001,20,1,2025-10-3117:41:11,2||5|delete|2025-10-3117:47:09|10| 删除的数据: 10,马超,200001,20,1,2025-10-3117:41:11,2|+----+----------------+---------------------+--------------+---------------------------------------------------------------------------------------------------------------+