目录
- 1.存储过程
- 2.变量
- 2.1 系统变量
- 2.2 用户变量
- 2.3 局部变量
- 3.SQL编程
- 3.1 参数列表/if
- 3.2 case
- 3.3 while
- 3.4 repeat
- 3.5 loop
- 3.6 游标&条件处理程序
- 3.6.1 游标
- 3.6.2 条件处理程编程序
- 3.6.3 示例
- 3.7 存储函数
- 4.触发器
- 4.1 insert触发器
- 4.2 update触发器
- 4.1 delete触发器
1.存储过程
概念：存储过程是一组预编译的SQL语句集合，存储在数据库中，可通过名称调用。支持条件判断、循环等逻辑控制，适合封装复杂业务逻辑
优势：- 性能优化：存储过程在创建时已经经过编译和优化，形成可执行的二进制代码，直接存储在数据库服务器中。性能高于单独执行时需要解析、编译的SQL语句
- 代码复用：存储过程可以重复调用(类似C语言函数/Java方法)
- 安全性高：避免应用程序直接访问数据库，通过存储过程间接访问(结合参数化查询)，从而保证安全性
- 降低耦合：当表结构发生变化时，只需要修改相应的存储过程，应用程序变动较小
缺点：- 移植性差：不同数据库创建存储过程的语法不同
- 不适合高并发场景：高并发场景下，存储过程会进一步降低数据库的性能
语法：
-- 修改SQL语句结束符为//
delimiter //
-- 创建存储过程
create procedure if not exists 存储过程名(参数列表)
begin
-- SQL语句
end//
-- 还原SQL语句结束符为;
delimiter ;
-- 调用存储过程
call 存储过程名(参数列表);
-- 删除存储过程
drop procedure if exists 存储过程名;
-- 查看当前数据库已创建的存储过程
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 // create procedure if not exists query() begin declare num int default 0; set num := 100; select num; end// delimiter ; call query();
3.SQL编程
3.1 参数列表/if
| 类型 | 描述 |
|---|---|
| in | 输入型参数，调用存储过程时传入，默认参数类型 |
| out | 输出型参数，可以作为存储过程的返回值 |
| inout | 输入输出型参数 |
if语法：
if 条件1 then -- sql语句1 elseif then --sql语句2 else -- sql语句3 end if;
示例：
-- 示例1
delimiter //
create procedure demo_if(in score int,out result varchar(20))
begin
if score >= 90 then
set result := '优秀';
elseif score >= 80 and score < 90 then
set result = '良好';
elseif score >= 70 and score < 80 then
set result = '及格';
else
set result = '不及格';
end if;
end//
delimiter ;
call demo_if(60,@result);
select @result;
-- 示例2
set @score := 70;
delimiter //
create procedure demo2_if(inout score int)
begin
set 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表达式：将一个表达式与一组独立的布尔表达式进行比较
case
when condition1 then result1
when condition2 then result2
...
else else_result
end
示例：
-- 示例1
delimiter //
create procedure demo_case(in code int,out result varchar(50))
begin
case code
when 0 then
set result := '成功';
when 10001 then
set result := '用户名或密码错误';
when 10002 then
set result := '没有权限';
when 20001 then
set result := '传入参数有误';
when 20002 then
set result := '无结果';
else
set result := '内部错误';
end case;
end//
delimiter ;
call demo_case(10002,@result);
select @result;
-- 示例2
delimiter //
create procedure demo2_case(in month int,out result varchar(50))
begin
case
when month >=1 and month <= 3 then
set result = '第一节度';
when month >=4 and month <= 6 then
set result = '第二节度';
when month >=7 and month <= 9 then
set result = '第三节度';
when month >=10 and month <= 12 then
set result = '第四节度';
else
set result = '非法输入';
end case;
end//
delimiter ;
call demo2_case(7,@result);
select @result;
3.3 while
语法：
-- search_condition：循环条件表达式 -- statement_list：循环体内要执行的SQL语句 while search_condition do statement_list end while;
示例：
delimiter //
create procedure demo_while(in n int)
begin
declare sum int default 0;
while n > 0 do
set sum := sum + n;
set n := n - 1;
end while;
select sum;
end//
delimiter ;
call demo_while(100);
3.4 repeat
先执行一次
statement_list，再判断search_condition是否成立
语法：
repeat statement_list until search_condition end repeat;
示例：
delimit编程客栈er //
create procedure demo_repeat(in n int)
begin
declare sum int default 0;
repeat
set sum := sum + n;
set n := n - 1;
until n <= 0 end repeat;
select sum;
end//
delimiter ;
call demo_repeat(100);
3.5 loop
语法：
-- loop_label：标记loop的标签 loop_label:loop statement_list end loop loop_label;
控制loop循环的关键字：
- leave：用于退出循环，相当于其他语言中的break
- iterate：用于跳过当前迭代，继续下一次循环，相当于其他语言中的continue
示例：
-- 求1-100所有偶数的和
delimiter //
create procedure demo_loop(in n int)
begin
declare total int default 0;
sum: loop
if n <= 0 then
leave sum;
end if;
if n % 2 = 1 then
set n := n - 1;
iterate sum;
end if;
set total := total + n;
set n := n - 1;
end loop sum;
select total;
end//
delimiter ;
call demo_loop(100);
3.6 游标&条件处理程序
3.6.1 游标
游标(cursor)：是一种数据库对象，用于在存储过程或函数中逐行处理查询结果集。当游标遍历到结果集末尾时，会触发not found状态，需要结合条件处理程序结束循环，避免无限执行
语法：
-- 声明游标 declare cursor_name cursor for 查询语句 -- 打开游标 open cursor_name; -- 获取游标记录 fetch cursor_name into
3.6.2 条件处理程序
条件处理程序：用于捕获和处理SQL执行过程中的异常或特定状态，需与游标配合使用
语法：
declare handler_type handler for 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 示例
初始化数据：
-- 创建班级表
create table class (id int primary key);
-- 创建学生表
create table student(id int primary key auto_increment,name varchar(20),sno varchar(10),age int,gender tinyint default 1,enroll_date datetime default now(),class_id int,foreign key (class_id) references class(id));
-- 向班级表插入数据
insert into class values (1),(2);
-- 向学生表插入数据
insert into student (name,sno,age,class_id) values ('刘备','100001',18,1),('关羽','100002',18,1),('张飞','100003',18,2),('赵云','100004',18,2);
示例：
delimiter //
create procedure demo_cursor(in input_class_id int)
begin
-- 声明变量用于接收每一列的结果
declare student_name varchar(20);
declare class_id int;
declare flg bool default true;
-- 声明游标用于接收查询结果
declare demo_cursor cursor for
select 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;
-- 声明处理程序
declare continue handler for not found set flg := false;
-- 创建新表
create table class_new (id int primary key auto_increment,student_name varchar(20),class_id int,foreign key (class_id) references class(id)
);
-- 开启游标
open demo_cursor;
loop_read: loop
fetch demo_cursor into student_name,class_id;
if not flg then
leave loop_read;
end if;
insert into class_new values(null,student_name,class_id);
end loop 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直接调用
语法：
create function 存储函数名 (参数列表) returns 返回值类型 [characteristic]
characteristic：可选特性- deterministic：表明函数对于相同的输入参数总是返回相同的结果
- not deterministic：默认选项，表示函数可能对相同的输入返回不同的结果
- no sql：表示函数不包含任何SQL语句
- reads sql data：表示函数包含读取数据的SQL语句(select)
- modifies sql data：表示函数包含修改数据的SQL语句(insert/update/delete)
- contains sql：表示函数仅包含读取数据的SQL语句，不修改数据
示例：
create function func1(n int) returns int deterministic
begin
declare `time` int default 0;
while n > 0 do
set `time` := `time` + n;
set n := n - 1;
end while;
return `time`;
end;
select func1(100);
4.触发器
触发器：一种与表相关的数据库对象，在指定事件(如insert、update、delete)发生时自动执行预定义的SQL语句
触发器类型：
- before触发器：在触发事件执行前激活
- after触发器：在触发事件完成后再执行
2.从触发事件区分
- insert触发器：响应数据插入操作
- update触发器：响应数据更新操作
- delete触发器：响应数据删除操作
3.从作用粒度区分
- 行级触发器：针对受影响的每一行数据都会触发一次
- 语句级触发器：整个SQL语句执行完毕后仅触发一次(MySQL暂不支持)
触发器中的new和old
- new：表示触发事件中的新数据
- old：表示触发事件前的旧数据
语法：
create trigger 触发器名
{before|after} {insert|update|delete} on 表名
for each row
begin
-- 触发器逻辑
end;
初始化数据：
--编程客栈 创建日志表 create table log (id int primary key auto_increment,operation_type varchar(20) not null comment '操作类型',operation_time datetime not null comment '操作时间', operation_id int not null comment '操作的记录的id',operation_date varchar(500) comment '操作数据' );
4.1 insert触发器
-- insert触发器 create trigger trg_insert after insert on student for each row begin insert into 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; -- 向学生表插入数据 insert into 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-31 17:25:17 | 9 | 9,马超,200001,18,1,2025-10-31 17:25:17,1 | +----+----------------+---------------------+--------------+------------------------------------------+
4.2 update触发器
-- update触发器
create trigger trg_update after update on student for each row
begin
insert into log values (null,'update',now(),new.id,concat(' 更新前数据: ',old.id,',',old.name,',',old.s编程客栈no,',',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 = 2 where name = '马超';
执行结果：
mysql> select * from log; +----+----------------+---------------------+--------------+---------------------------------------------------------------------------------------------------------------+ | id | operation_type | operation_time | operation_id | operation_date | +----+----------------+---------------------+--------------+---------------------------------------------------------------------------------------------------------------+ | 3 | insert | 2025-10-31 17:40:14 | 10 | 10,马超,200001,18,1,2025-10-31 17:40:14,1 | | 4 | update | 2025-10-31 17:41:11 | 10 | 更新前数据: 10,马超,200001,18,1,2025-10-31 17:40:14,1 ,更新后数据: 10,马超,200001,20,1,2025-10-31 17:41:11,2 | +----+----------------+---------------------+--------------+---------------------------------------------------------------------------------------------------------------+
4.1 delete触发器
-- delete触发器
create trigger trg_delete after delete on student for each row
begin
insert into 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;
delete from student where name = '马超';
执行结果：
mysql> select * from student; +----+------+--------+------+--------+---------------------+----------+ | id | name | sno | age | gender | enroll_date | class_id | +----+------+--------+------+--------+---------------------+----------+ | 1 | 刘备 | 100001 | 18 | 1 | 2025-10-26 22:11:23 | 1 | | 2 | 关羽 | 100002 | 18 | 1 | 2025-10-26 22:11:23 | 1 | | 3 | 张飞 | 100003 | 18 | 1 | 2025-10-26 22:11:23 | 2 | | 4 | 赵云 | 100004 | 18 | 1 | 2025-10-26 22:11:23 | 2 | +----+------+--------+------+--------+---------------------+----------+ 4 rows in set (0.00 sec) mysql> select * from log; +----+----------------+---------------------+--------------+---------------------------------------------------------------------------------------------------------------+ | id | operation_type | operation_time | operation_id | operation_date | +----+----------------+---------------------+--------------+---------------------------------------------------------------------------------------------------------------+ | 3 | insert | 2025-10-31 17:40:14 | 10 | 10,马超,200001,18,1,2025-10-31 17:40:14,1 | | 4 | update | 2025-10-31 17:41:11 | 10 | 更新前数据: 10,马超,200001,18,1,2025-10-31 17:40:14,1 ,更新后数据: 10,马超,200001,20,1,2025-10-31 17:41:11,2 | | 5 | delete | 2025-10-31 17:47:09 | 10 | 删除的数据: 10,马超,200001,20,1,2025-10-31 17:41:11,2 编程客栈 | +----+----------------+---------------------+--------------+---------------------------------------------------------------------------------------------------------------+
到此这篇关于MySQL存储过程、游标与触发器的文章就介绍到这了,更多相关mysql存储过程、游标与触发器内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！
加载中,请稍侯......
精彩评论