MySQL
存储过程
存储过程、变量、流程控制与游标
存储过程
信息
经过编译,并存储在数据库中的一段 SQL 集合;
就是 SQL 的封装和重用,调用存储过程可以简化业务端的工作,减少数据在客户端和服务端的传输从而提高效率。
-- 创建存储过程
create procedure 存储过程名称([参数...])
begin
sql 语句
end;
-- 调用存储过程
call 存储过程名称()
-- 查看存储所有过程 infomation_schema.ROUTEINGS 查询可以带任何查询条件
select * from infomation_schema.ROUTEINES;
-- 查看存储过程
show create procedure 存储过程名称;
-- 删除存储过程
drop procedure if exists 存储过程名称;命令行创建存储过程
由于存储过程中也包含一些 sql 语句导致直接出现多个分号会创建失败;
-- 使用 delimiter 修改结束符
delimiter $$
create procedure p1()
begin
select * from emp;
end;
delimiter $$变量
- 系统变量
global | session
-- 查看系统变量
select @@[global | session] 系统变量名;
show [global | session] variables;
show [global | session] variables like '';
-- 设置系统变量
set @@[global. | session.]系统变量名 = 值;
set [global. | session.]系统变量名 = 值;- 用户自定义变量
-- 定义变量
set @变量名 = 值[,...];
set @变量名 := 值[,...];
-- select 只能使用 :=
select @变量 := 值[,...];
-- info 则是将查询的数据赋值给这个变量
select 字段名 into @变量名 from 表名 where 条件...;
-- 查询变量
select @变量名;- 局部变量
在存储过程中定义的变量,必须在 begin 后面 sql 语句前面。存储过程中的变量不需要增加 @
delcare 变量名 变量类型 [default 默认值];
create procedure p1()
begin
declare num int default 0;
select count(*) into num from emp;
select num;
end;
call p1();条件判断
单分支 if 判断
create procedure p1()
begin
declare gender varchar(16) default 'male';
set gender = if (gender = 'male', '男', '女');
select gender;
end;
call p1();多分支 if 判断
delimiter $$
create procedure p1()
begin
declare score int default 80;
declare notice varchar(10) default '';
if (score = 100) then
set notice = '优秀';
elseif (score = 80) then
set notice = '不错';
else
set notice = '垃圾';
end if;
select notice;
end $$
delimiter;
call p1();多分支 case 表达式
delimiter $$
create procedure p1()
begin
declare score int default 80;
declare notice varchar(10) default '';
set notice = case score
when 100 then '优秀'
when 80 then '不错'
else '垃圾'
end;
select notice;
end $$
delimiter;
call p1();多分支 case 语句
delimiter $$
create procedure p1()
begin
declare score int default 80;
declare notice varchar(10) default '';
set notice = case
when score >= 90 then '牛逼'
when score >= 80 then '还行'
else '垃圾'
end;
select notice;
end $$
delimiter;
call p1();参数
-- in 输入参数, 默认
-- out 输出参数
-- inout 输入和输出参数
create procedure 存储过程名称([in/out/inout 参数名 参数类型, ....])
begin
sql 语句
end;比如将上面的存储过程改为参数传递
create procedure p1(in score int, out res varchar(16))
begin
set res = case
when score >= 90 then '优秀'
when score >= 70 then '一般'
else '垃圾'
end;
end;
-- @res 不赋值的变量就是 null
call p1(90, @res);
-- 打印变量
select @res;如果这个变量期望在外面也生效,就需要使用 inout
-- 如果使用 in 外部的 @score 则不会改变
create procedure p1(inout score double)
begin
set score = score / 10;
end;
set @score = 88;
call p1(@score);
select @score;循环语句
while 循环,满足条件退出循环
create procedure p1(in num int)
begin
declare res int default 0;
while num > 0 do
set res = num + res;
set num = num - 1; -- 终止循环条件
end while;
select res;
end;repeat 循环,满足条件退出循环,至少执行一遍逻辑
create procedure p1(in num int)
begin
declare res int default 0;
repeat
set res = res + num;
set num = num - 1;
until num <= 0; -- 满足条件终止循环
end repeat;
select res;
end;loop 循环,可以通过 leave 跳出循环 iterate 进入下一次循环
create procedure p1(in num int)
begin
declare res int default 0;
-- sum: 是标签,用来终止或者跳出循环时使用
sum:loop
if num = 5 then
set num = num - 1;
iterate sum; -- 等于 5 的时候跳出本次循环进入下一次
end if;
set res = res + num;
set num = num - 1;
if num <= 0 then
leave sum; -- 跳出循环
end if;
end loop sum; -- 尾部的标签可写可不写
select res;
end;游标
用来存储查询结果集的数据类型
-- 声明游标, select语句的结果会赋值给这个游标
declare 游标名称 cursor for select语句;
-- 打开游标
open 游标名称;
-- 读取游标记录, 到对应的变量名
fetch 游标名称 into 变量名,...;
-- 关闭游标
close 游标名称示例:接收 u_salary 参数,查询 emp 中所有薪资小于该参数的员工姓名、年龄、薪资以及岗位,存储到一个新的表中
create procedure p1(in u_salary int)
begin
declare my_name varchar(16);
declare my_age int;
declare my_salary float(10, 2);
declare my_post varchar(16);
-- 定义游标内容
declare my_cursor cursor for select name, age, salary, post from emp where salary < u_salary;
-- 使用 handler 捕获异常关闭游标
declare exit handler for SQLSTATE '02000' close my_cursor;
drop table if exists tb_cursor;
create table tb_cursor(
id int primary key auto_increment,
name varchar(16),
age int,
salary float(10, 2),
post varchar(16)
);
open my_cursor;
-- 死循环会导致出现异常
while true do
-- 将游标中的每行数据放在对应的变量中
fetch my_cursor into my_name, my_age, my_salary, my_post;
insert into tb_cursor(name, age, salary, post) values (my_name, my_age, my_salary, my_post);
end while;
close my_cursor;
end;条件处理程序 handler
-- continue 跳过异常代码继续执行
-- exit 退出整个存储过程
-- 条件,包含以下四种
-- 满足条件后执行 continue/exit 动作,然后执行 SQL逻辑
declare continue/exit handler for 条件 SQL逻辑;SQLSTATE 状态码捕获某一个状态码SQLWARNING警告信息,所有 01 开头的状态码NOT FOUND没有找到数据,所有 02 开头的状态码SQLEXCEPTION上面错误数据的兜底
存储函数
有返回值的存储过程,存储函数的参数只能是 in 类型;
准确来说前面的存储过程都是传入参数,然后修改参数的值,而不是真正的返回值。
create function 存储函数名([参数,...])
returns 返回值类型 [特性]
begin
sql 语句
end;特性 8.0 之后必须
DETERMINTSTIC: 确定性,输入和输出总是相同的,这个参数可以帮助优化器进行性能优化,允许函数缓存结果NOT DETERMINTSTIC:非确定性,输入和返回值可能会发生变化,适用于可能会因为外部因素变化的函数比如时间READS SQL DATA:指定函数会读取数据,包括表,视图等,就是说函数在执行的过程中会对数据库进行读取操作MODIFIES SQL DATA:指定函数会修改数据,比如插入,更新删除表数据,函数在执行过程中会对数据库进行写入操作NO SQL:不会对数据进行读写操作
create function f1(num int)
returns int READS SQL DATA
begin
declare res int;
select count(*) into res from emp where salary < num;
return res;
end;
set @num = f1(5000);
select @num;触发器
是一个和表有关的数据库对象,在插入、更新、删除数据之前或者之后触发,触发器被触发后会执行内部的 sql 逻辑
create trigger 触发器名字
-- before/after 之前或者之后
-- insert/update/delete 指定语句触发
-- for each row 行触发器,涉及多少行就会触发多少次
before/after insert/update/delete on 表名 for each row
begin
sql;
end;
show triggers; -- 查看触发器
drop trigger 触发器名字; -- 删除触发器比如记录 emp 表的数据变更日志,并把变更日志插入到日志表 emp_logs 中。
-- 创建表
create table emp_logs(
id int primary key auto_increment,
event_type varchar(16) not null,
event_time datetime not null ,
event_id int not null ,
event_content varchar(800)
);
-- 在触发器的内部,提供了 old 和 new 可以访问操作前和操作的数据
create trigger emp_insert_trigger
before insert on emp for each row
begin
insert into emp_logs values (null, 'insert', now(), new.id, concat(
'插入的数据:', 'id=', new.id, 'name=',new.name, 'salary=',new.salary)
);
end;
create trigger emp_update_trigger
before update on emp for each row
begin
insert into emp_logs values(null, 'update', now(), old.id, concat(
'更新后的数据:', 'id=', new.id, 'name=',new.name, 'salary=',new.salary,
'更新前的数据:', 'id=', old.id, 'name=',old.name, 'salary=',old.salary)
);
end;
create trigger emp_delete_trigger
before delete on emp for each row
begin
insert into emp_logs values(null, 'update', now(), old.id, concat(
'删除的数据:', 'id=', old.id, 'name=',old.name, 'salary=',old.salary)
);
end;