存储过程
存储过程类似函数
每次单个SQL语句对数据库进行操作都需要访问网络协议等，有一定的开销。通过存储过程减少数据库和服务器之间的传输，一次性访问
- 封装，复用：将SQL语句封装，每次需要即调用
- 接收参数，也可以返回数据
- 减少网络交互，效率提升
基本语法
# 创建
create procedure p1([参数列表])
begin
select count(*) from emp;
end;
# 调用
call p1([参数列表]);
# 查看
select * from information_schema,routines where rouitne_schema = 'p1'; #查询指定数据库的存储过程状态信息
show create procedure p1; # 查询某个存储过程定义
# 删除
drop procedure [if exists] p1;
变量
系统变量
mysql服务器提供定义，分为全局变量和会话变量
注意会话变量中datagrip和命令行的区别
# 查看所有变量
show [session|global] variables [like '……']; # 查看
# 查看变量
select @@[session|global].系统变量名;
# 设置变量
set @@[session|gloabl].autocommit = 0;
set [session|global] autocommit = 0;
# 查看
show session variables like 'con%';
# 查看变量
select @@session.autocommit;
# 设置变量
set @@session.autocommit = 0;
set global autocommit = 0;
- 设置全局变量重启mysql后，会恢复到默认值
服务关闭重启后，再次查询，恢复默认值
- 会话变量是指在仅在当前窗口系统变量的值改变，另一个窗口系统变量依然是默认值
重开一个窗口后，再次查询会话变量，值为1
用户定义变量
# 赋值
#方式一 set = :=
set @myname = 'abcd';
set @myage := 20;
set @mygender := '男' ,@myhobby := 'java'; # 多次赋值
#方式二 select
select @mycolor := 'red';
select 20 into @myn;
select count(*) into @mycount from tb_user; #可直接将表字段存入变量
#查询变量
select @myn;
- 创建的变量如果没有赋值，仅声明，则显示NULL
- 任何没有被创建的变量，结果都是NULL
局部变量
一般放在存储过程的内部，周期为begin -> end
create procedure p1()
begin
declare user2_count int default 0; #default默认，可省略
select count(*) into user2_count from tb_user2;
select user2_count; # 局部变量的查询不带@
end;
call p1;
if
# 语法
if …… then
……
elseif then
……
else
……
end if;
根据定义的分数score变量，判定当前分数对应的分数等级。
score >= 85分，等级为优秀
score >= 60分 且 score < 85分，等级为及格
score < 60分，等级为不及格
create procedure p2()
begin
declare score int default 30;
declare result varchar(10);
if score >= 85 then
set result := '优秀';
elseif score >= 60 then
set result := '及格';
else
set result := '不及格';
end if;
select result;
end;
call p2;
参数
参数类型分为： IN、OUT、INOUT
IN 输入参数
OUT 输出参数
INOUT 既是输入也是输出参数
根据输入的分数score变量，判定当前分数对应的分数等级。
score >= 85分，等级为优秀
score >= 60分 且 score < 85分，等级为及格
score < 60分，等级为不及格
#参数无需在外定义
create procedure p3(IN score int,OUT result varchar(10))
begin
if score >= 85 then
set result := '优秀';
elseif score >= 60 then
set result := '及格';
else
set result := '不及格';
end if;
end;
call p3(80,@result);
select @result;
case
# 与流程函数相似
-- 方式一
case v
when …… then ……;
when …… then ……;
else ……;
end case;
-- 方式二
case
when …… then ……：
when …… then ……;
else ……;
end case;
根据传入的月份，判定月份所属的季节
1-3月份，为第一季度
4-6月份，为第二季度
7-9月份，为第三季度
10-12月份，为第四季度
create procedure p4(in month int,out result varchar(10))
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;
call p4(5,@result);
select @result;
while
计算从1累加到n的值，n为传入的参数值
create procedure sum1(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 sum1(10);
repeat
类似do …… while……
先执行语句然后再进行判断
计算从1累加到n的值，n为传入的参数值
create procedure sum2(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 sum2(10);
loop
loop实现死循环
leave ：配合循环，跳出循环
iterate：跳出当前循环的剩余语句，执行下一个循环
# 语法
[begin_label:] LOOP
SQL逻辑...
END LOOP [end_label];
# begin_label 、 end_label 自定义标记
LEAVE label; -- 退出指定标记的循环体
ITERATE label; -- 直接进入下一次循环
计算从1累加到n的值，n为传入的参数值
create procedure sum3(in n int)
begin
declare sum int default 0;
sum:loop
if n <= 0 then leave sum;
end if;
set sum := sum + n;
set n := n - 1;
end loop sum;
select sum;
end;
call sum3(10);
计算从1到n之间的偶数累加的值，n为传入的参数值
create procedure sum4(in n int)
begin
declare sum 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 sum := sum + n;
set n := n - 1;
end loop sum;
select sum;
end;
call sum4(10);
游标
游标是存储结果集的 “变量”，相当于一个小型的表（临时的），存储不同字段和数据，如果想对表的多个字段数据进行处理，可以通过游标将表的数据转移到另一个表
类比视图，视图展示表的一部分数据，如果表数据更改，视图数据也会跟着改变
基本用法
# 声明游标
declare 游标名 cursor for 查询语句;
# 打开游标
open 游标名;
# 获取游标记录
fetch 游标名 into 变量[,变量];
# 关闭游标
close 游标名;
# 使用流程：声明，打开，获取，关闭
根据传入的参数uage，来查询用户表tb_user中，所有的用户年龄小于等于uage的用户姓名 （name）和专业（profession），并将用户的姓名和专业插入到所创建的一张新表 (id,name,profession)中
# 声明变量，声明游标
# 创建新表
# 打开游标
# 获取游标，将数据放入新表
# 游标声明必须在普通变量声明之后
create procedure p10(in uage int)
begin
declare uname varchar(10);
declare upro varchar(10);
declare u_cursor cursor for select name,profession from tb_user where age <= uage;
create tb_user_pro(
id int primary key auto_increment,
name varchar(10),
profession varchar(10)
);
open u_cursor;
while true do
fetch u_cursor into uname,upro;
insert into tb_user_pro values(null,uname,upro);
end while;
close u_cursor;
end;
call p10(30);
运行存储过程会报错，因为 while没有终止条件，fetch游标的数据，如果游标的数据已经被全部采取后，再获取数据，就会报错
但是表依然可以被创建，如果要消去报错异常，需要添加条件处理
条件处理程序
对程序给出的异常进行相关处理
# 基本语法
DECLARE handler_action HANDLER FOR condition_value [, condition_value] ... statement ;
handler_action 的取值：
CONTINUE: 继续执行当前程序
EXIT: 终止执行当前程序
condition_value 的取值：
SQLSTATE sqlstate_value: 状态码，如 02000
SQLWARNING: 所有以01开头的SQLSTATE代码的简写
NOT FOUND: 所有以02开头的SQLSTATE代码的简写
SQLEXCEPTION: 所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的简写
与上结合
create procedure p10(in uage int)
begin
declare uname varchar(10);
declare upro varchar(10);
declare u_cursor cursor for select name,profession from tb_user where age <= uage;
declare exit handler for SQLSTATE '02000' close u_cursor;
# declare exit handler for not found close u_cursor;
# 可以用具体的状态码，也可以用大概的范围性报错
create tb_user_pro(
id int primary key auto_increment,
name varchar(10),
profession varchar(10)
);
open u_cursor;
while true do
fetch u_cursor into uname,upro;
insert into tb_user_pro values(null,uname,upro);
end while;
close u_cursor;
end;
call p10(30);
3125
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
