1. 概念
MySQL中的存储过程指的是存储在数据库中的SQL语句集合。当创建好存储过程后，在运行时提供所需参数，存储过程就可以以代码指定的方式使用参数执行并返回值。
存储过程的特点包括：
- 封装与复用：可以把某一业务SQL封装在存储过程中，需要用到的时候直接调用即可，减少了网络交互，提升了通信速率。
- 接收参数与返回数据：在存储过程中，可以传递参数，也可以接收返回值。
- 编译与执行效率：存储过程只在创建时进行编译，以后每次执行存储过程都不需要在重新编译，而一般SQL语句每执行一次就编译一次，所以使用存储过程可提高数据库执行速度。
2. 基本语法
2.1 创建
-- 创建存储过程 create procedure 存储过程名 (参数没有就不传) begin SQL语句 end;-- 调用存储过程 call 存储过程名（传的参数，没有就不传）;
栗子
-- 创建存储过程
create procedure p1 ()
begin
select * from emp;
select count(*) from emp;
end;
-- 调用存储过程
call p1();
2.2 查看
-- 查看存储过程
-- select 字段名 from information_schema.ROUTINES where ROUTINE_SCHENA = 存储过程名
-- 查询指定数据库存的储过程及状态信息
-- show create procedure 存储过程名
-- 查询存储过程的定义
栗子
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'study';
show create procedure p1;2.3 删除
-- 删除存储过程 -- drop procedure (if exists) 存储过程名; 括号内容可省略
drop procedure if exists p1;
2.4 命令行注意事项：
-- 注意：在命令行中执行存储过程需修改SQL语句结束符号，修改结束符用关键字delimiter -- delimiter 符号
delimiter $$
3. 变量
概念
- 系统变量：系统变量是MySQL服务器定义的，用户只能修改其值，不能创建新的系统变量。系统变量分为全局变量和会话变量。全局变量对所有会话都有效，修改全局变量会影响所有新会话（但已存在的会话不受影响），直到MySQL服务器重启。会话变量则是在当前会话级别有效，其变更只会影响到当前会话。需要注意的是，对系统变量的修改在MySQL服务器重启后会恢复为默认值，要想永久修改，需要更改配置文件。
- 用户变量：用户变量是用户根据需要自己定义的变量，用户变量不用提前声明，在使用的时候直接用“@变量名”就可以。其作用域为当前连接。
- 局部变量：局部变量是根据需要在局部生效的变量，访问之前，需要DECLARE声明。在存储过程和函数中都可以定义和使用局部变量。
3.1 系统变量
-- 系统变量 global全局系统变量 ：所有会话生效 session当前会话变量：当前会话生效
-- 查看系统变量（括号二选一） -- show (global/session) variables; 查看所有的系统变量
show session variables;
show global variables;-- show (global/session) variables like ''; 模糊匹配系统变量
show global variables like 'auto%';
-- select @@系统变量名 查看指定系统变量
select @@autocommit;
-- 设置系统变量（不加global或者session会默认修改当前会话的变量）
-- 当服务器重启会自动回复到初始值，若要永久修改可在配置文件 /etc/my.cnf 中修改
-- set (session/global) 系统变量名 = '值'; 设置指定系统变量的值
set @@autocommit = 1;
3.2 用户变量
-- 用户变量
-- 用户变量不需要提取声明或者初始化，未初始化的用户变量默认赋值null；
-- 设置用户变量
-- set @用户变量名1 = 值， @用户变量名2 = 值， ...;
-- set @用户变量名1 := 值, @用户变量名2 := 值, ...;
-- SQL中= 和 := 都能赋值
select @niubi;
set @niubi := 666;
-- select @用户变量名1 := 值, @用户变量名2 := 值, ...;
select @niuhai := 999;
select @niuhai;
-- select count(*) into @用户变量名 from 表名;
-- 将查询表统计返回的结果赋值给用户变量
select count(*) into @赋值结果 from emp;
select @赋值结果;3.3 局部变量
-- 作用域只要begin和end之间生效，需要declare提前声明
-- declare 变量名 变量类型 default 默认值
-- 赋值 变量名 = 值
create procedure p1()
begin
declare emp_count int default 0;
select count(*) into emp_count from emp;
select emp_count;
end;
call p1;4. 流程控制语句
两类语句
条件控制语句：if、case语句
循环控制语句：
4.1 if
-- if判断条件的使用
-- if 条件1 then
-- 满足条件1执行的语句
-- elseif 条件2 then
-- 满足条件2执行的语句
-- else
-- 不满足以上所有条件执行的语句
-- end if;
参数
-- 创建过程
-- 传递变量进行判断
create procedure p3(in source int)
begin
-- 声明局部变量
declare result varchar(10);
if source >= 80 then
set result = '优秀';
elseif source >= 60 then
set result = '及格';
else
set result = '不及格';
end if;
-- 查询结果
select result;
end;
-- 调用过程
call p3(99);练习
-- 练习2：将传入的200分制的分数换算成100分制并返回
create procedure p5(inout score double)
-- inout修饰的变量可以输入和输出参数
begin
set score := score * 0.5;
end;
-- 调用
set @score = 91;
call p5(@score);
select @score;4.2 case
- 使用规则
-- case开始
case
when 条件1 then 满足条件1执行的语句；
when 条件2 then 满足条件2执行的语句；
when 条件3 then 满足条件3执行的语句；
else 不满足以上条件执行的语句；
-- case结束
end case;
-- 输入季度，并进行判断
create procedure p6(in month int)
begin
declare result varchar(10);
case
when 1 <= month and month <= 3 then set result = '第一季度';
when 4 <= month and month <= 6 then set result = '第二季度';
when 7 <= month and month <= 9 then set result = '第三季度';
when 10 <= month and month <= 12 then set result = '第四季度';
else set result = '非法参数';
end case;
select concat('您输入的月份为：', month, '，属于', result);
end;
call p6(19);4.3 while
-- 使用规则
while 条件 do
满足条件执行的SQL语句
不满足条件退出循环
end while；
-- 练习：累加n的值
create procedure p7(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;
-- 调用
call p7(10);4.4 repeat
-- 使用规则
repeat
要执行的sql语句
until 满足条件就退出循环
end repeat;
end repeat;
-- 练习：计算前n个数的值
create procedure p8(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;
-- 调用
call p8(10);4.5 loop
-- 使用规则 -- leaver 跳出循环 -- iterate 中断本次循环，进行下一次循环
create procedure p10(in n int)
begin
-- 定义变量统计总和
declare sum int default 0;
-- 循环
fun: loop
-- 满足以下条件跳出循环
if n <= 0 then
leave fun;
end if;
-- 满足以下条件不进行累加
if n % 2 = 0 then
set n = n - 1;
iterate fun;
end if;
-- 赋值
set sum = sum + n;
set n = n - 1;
end loop fun;
-- 查看最终结果
select sum;
end;
call p10(5);5. 游标（cursor）
-- 用来存储查询结果集的数据类型，在存储过程和函数中可以用游标对结果集进行数据处理 -- 使用规则：声明，open、fetch、close -- 声明游标 declare 游标名 cursor for 查询语句（查询到的内容将会存储到游标内）; -- open 游标名; -- fetch 游标名 into 变量1，变量2，...;（游标内的每一条记录都会存储到对应的变量，变量接收的类型要和游标记录的数据类型保持一致） -- close 游标名;
-- 条件处理程序（handler）：处理程序出现问题时该如何执行（如当游标内的记录为空时如何退出循环） -- 使用规则：declare handler_action handler for condition_value, condition_value,... statement; -- 声明一个处理异常程序的语句即：遇到上面的状态码（如：02000）时进行什么程序操作（如：exit），中止程序后该游标什么状态（如：关闭游标） -- handler_action -- continue：继续执行当前程序; -- exit：退出程序; -- condition_value -- sqlstate sqlstate_value：状态码（如：02000） -- sqlwarning：所有以01开头的状态码的简写 -- not found：所有以02开头的状态码的简写 -- sqlexception：所有没有被sqlwarning、not found捕抓到的状态码的简写
create procedure p11(in source varchar(10))
begin
-- 声明游标内数据的类型
declare uname varchar(10) default null;
declare uid int;
declare uage int;
declare udept_id int;
declare uaddress varchar(10);
-- 声明游标
declare emp_cursor cursor for select * from emp where address = source;
-- 声明条件处理程序，当游标满足什么条件自动退出程序（当游标记录的数据为空时退出程序，并关闭游标）
declare exit handler for not found close emp_cursor;
-- 或者使用状态码退出
-- declare exit handler for SQ
-- declare exit handler for sqlstate '02000' close emp_cursor;
-- 创建新表存储游表的记录
drop table if exists tb_emp;
create table tb_emp(
id int primary key auto_increment,
name varchar(10) not null,
address varchar(10)
);
-- 打开游标
open emp_cursor;
-- 循环遍历存储游标内的记录
while true do
-- 获取游标内的数据存储到定义的临时变量中
fetch emp_cursor into uid, uname, uage, udept_id, uaddress;
-- 将数据存入新表
insert into tb_emp value (null, uname, uaddress);
end while;
-- 关闭游标
close emp_cursor;
end;
-- 调用存储过程
call p11('广州');6. 存储函数
-- 使用规则：函数必须要有返回值
create function 函数名称(参数列表)
returns type（函数返回值类型） character（函数特征）
begin
函数内部要执行的语句
return 返回值
end;
-- 使用规则：函数必须要有返回值
create function 函数名称(参数列表)
returns type（函数返回值类型） character（函数特征）
begin
函数内部要执行的语句
return 返回值
end;7. 触发器
触发器的主要特点和功能包括：
- 自动化业务逻辑和操作：触发器能够自动执行存储过程，响应特定事件，如insert、update和delete语句，从而实现约束、默认值或处理业务逻辑的功能。
- 响应特定事件：每个触发器都有一个触发事件和响应事件。触发事件通常是数据表上的INSERT、UPDATE或DELETE语句，而响应事件则是在触发事件后MySQL服务器执行的操作。
- 处理多种操作：触发器可以处理多种操作，比如设置默认值、展示错误信息、实现复杂的业务逻辑等。
需要注意的是，在MySQL中，只有执行INSERT、UPDATE和DELETE操作时才能激活触发器，其他SQL语句则不会激活触发器。
通过创建和使用触发器，开发人员可以更有效地管理数据库中的数据，确保数据的完整性和一致性，同时减少手动干预的需要，提高应用程序的自动化程度。
创建触发器语法
create trigger 触发器名称
after/before insert on 触发器关联的表名 for each row
begin
insert into 触发器的记录插入的哪张表的名称 (插入内容);
end;
-- 创建触发器
-- 插入数据触发器
-- 创建触发器（trigger） 触发器名字
create trigger tb_emp_insert_trigger
-- 触发器在emp表操作之后触发
after insert on emp for each row
-- 触发器内部结构
begin
insert into emp_logs (id, operation, operation_time, operation_id, operation_prams) values
(null, 'insert', now(), new.id,
concat('插入数据内容为：id=',new.id,'name=',new.name,'age=',new.age,'address=',new.address )
);
end;create trigger 触发器名称
after/before update on 触发器关联的表名 for each row
begin
insert into 触发器的记录插入的哪张表的名称 (插入内容);
end;
-- 创建修改数据触发器
create trigger tb_emp_update_trigger
-- 触发器在emp表操作之后触发
after update on emp for each row
-- 触发器内部结构
begin
insert into emp_logs (id, operation, operation_time, operation_id, operation_prams) values
(null,'update',now(),new.id,
concat('更新之前数据：id=', old.id, 'name=', old.name, 'age=', old.age, 'address=', old.address,
'更新之后数据：id=', new.id, 'name=', new.name, 'age=', new.age, 'address=', new.address )
);
end;删除同理
本文含有隐藏内容，请 开通VIP 后查看