介绍
存储过程是事先经过编译并且存储在数据库中的一段SQL语句的集合。
调用存储过程可以简化应用开发人员的很多工作,减少数据在数据库和应用服务器之间的传输,有助于提高数据处理的效率。
存储过程思想上就是SQL语言层面的代码封装和重用。
特点
封装,复用
可以接收参数,可以返回数据
减少网络交互,提高了效率
基本语法
创建存储过程
create procedure 存储过程名([参数列表])
begin
--SQL语句
end;
调用存储过程
call 存储过程名([参数]);
查看存储过程
查询是否存在对应存储过程
查询 information_shema系统数据库下的routines表
里面存储了对应的存储过程
当我们要查询数据库内某个表可以直接 数据库名.表名 这种方式查询,或者先use进入这个数据库在查询
select * from information_shema.routines where routine_schema = '要查询的存储过程名';
查询指定存储过程的定义方式
show create procedure 存储过程名;
删除存储过程
drop procedure [if exists] 存储过程名;
注意!
如果我们是在命令行中创建存储过程的创建SQL,会报错!
因为默认我们是 分号 结束SQL语句
但是在datagrip等软件中就不会报错!
解决办法!
修改mysql的结束符!
delimiter 结束符
变量
系统变量
系统变量是MYSQL服务器提供,不是用户自定义的,属于服务器层面,
分为全局变量(GLOBAL)。会话变量(SESSION)。
一个查询控制台就是一个会话
查看系统变量
如果不指定是session还是global那么默认session
查看所有系统变量
show [session | global ] variables;
通过模糊匹配方式查询指定变量
show [session | global ] variables like '....';
查看指定系统变量
select @@[session | global]系统变量名;
如果要加global级别那么要
select @@global.系统变量名;
用 点 作为分隔符!
设置系统变量
set [session | global ] 系统变量名 = 值;
set @@[session | global ]系统变量名=值;
设置的系统变量值如果服务器重启后会失效!
要永久修改就修改/etc/my.cnf 文件内容
用户自定义变量
用户根据自己定义的变量叫用户自定义变量
用户变量不需要提前声明,用的时候直接 @变量名 使用即可
如果没有对变量进行复制,获取到的值为NULL。
作用域为当前的连接!
赋值
用set方式赋值
set @变量名1=值1,@变量2=值2,...;
set @变量名1:=值1,@变量2:=值2,...; ->推荐,用于区分 =
用select方式赋值
select @变量名1 := 值1.@变量名2 := 值2,...;
select 字段名 into @变量名 from 表名; ->将select查询到的值作为变量的值
使用
select @变量名1,@变量名2,...;
局部变量
局部变量 是根据需要定义在局部生效的变量。
访问之前需要DECLARE声明。
可用作存储过程内的局部变量和输入参数,局部变量的范围是在其声明内的BEGIN...END块
局部变量一般用在存储过程内部.
声明
Declare 变量名 数据类型 [default 默认值];
赋值
set 局部变量名1 = 值1,局部变量名2 = 值2,...;
set 局部变量名1 := 值1,局部变量名2 = 值2...;
select 字段 into 变量名 from 表名;
使用
select 变量名;
存储过程的参数
参数类型
类型 | 含义 | 备注 |
---|---|---|
IN | 该类作为输入,也就是需要调用时传入值 | 默认 |
OUT | 该类作为输出,也就是该参数可以作为返回值 | |
INOUT | 既可以作为传入值也可以作为返回值使用 |
in 就相当于你函数里面写的值
out 就相当于你函数的 return值
inout 就是俩个的结合体
举例
create procedure 存储过程名(in id int,out name char(5))
begin
--SQL
end;
call 存储过程名(传入值,用于接收name这个传出参数的值的变量);
用法
create procedure 存储过程名([in | out | inout 参数名 参数的数据类型])
begin
--SQL语句
end;
条件判断
if条件判断的语法
if 条件1 then
执行语句1;
else 条件2 then
执行语句2;
else
执行语句n;
end if;
case流程控制的语法
语法1 (表达式=值1 or 值2 or 值3 or.....or 值n)
如果表达式等于对应的值就执行对应语句。
Case 表达式
when 值1 then 执行语句1;
when 值2 then 执行语句2;
....
else 执行语句n;
end case;
语法2(条件表达式1 = 值1 or 条件表达式2 = 值2 or 条件表达式3 = 值3 or ... or 条件表达式n = 值 n)
如果条件表达式成立就执行对应语句。
case
when 条件表达式1 then 执行语句1;
when 条件表达式2 then 执行语句2;
...
else 执行语句n;
end case;
循环
while循环
while循环是有条件的循环控制语句。
先判断条件,如果为true则执行逻辑,否则就不执行。
while 条件 do
SQL逻辑
end while;
repeat循环
repeat循环是有条件的循环控制语句。
当满足条件的时候!退出循环。
先执行一次逻辑,然后判断逻辑是否满足,相当于 do while
repeat
SQL逻辑;
until 循环停止条件 ->这里没有分号!
end repeat;
loop循环
Loop实现简单的循环,如果不在SQL逻辑中增加退出循环的条件,可以用来实现简单的死循环。
LOOP可以配合:
- leave 配合循环使用,退出循环,相当于break
- iterate 必须在循环中使用,作用是跳过当前循环剩下的语句,直接进入下一次循环,相当于 continue
[开始标记:] Loop
SQL逻辑;
end Loop[结尾标记:];
leave 标记名; ->退出指定标记的循环体
iterate 标记名; -->直接进入下一次循环
开始标记和结尾标记要一模一样,开始标记多个 :
用法举例
从1累加到n
create procedure p(in n int)
begin
declare total int default 0;
sum:loop
if n<=0 then
leave sum;
end if;
set total := total + n;
set n := n-1;
end loop sum;
select total;
end;
从1到n之间偶数累加
create procedure p0(in n int)
begin
declare total int default 0;
sum:loop
if n<=0 then
leave sum;
elseif 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;
游标
我们知道变量可以通过select查询表数据并将数据设置为变量数据
但是!变量只能存储单行/单列的数据!
游标却可以存储一张表!
介绍
游标是用来存储查询结果集的数据类型,在存储过程和函数中可以使用游标对结果集进行循环处理。
游标的使用包括了
- 游标的声明
- open
- fetch
- close
变量的声明必须在游标之前!!
语法
声明游标
Declare 游标名 cursor for 查询语句;
打开游标
在使用游标之前必须打开游标
open 游标名;
获取游标记录
将游标的记录遍历赋值给变量
Fetch 游标名 into 变量1,变量2,....;
关闭游标
close 游标名;
使用方法举例
根据传入的参数 uage
查询 tb_user表的姓名和专业,条件是年龄小于 uage的记录
并将查询返回的结果插入到新创建的表中。
这个新创建的表包含了(id ,name,profession)
逻辑实现:
- 声明游标,存储查询的结果集
- 准备工作:创建表结构
- 开启游标
- 获取游标中的记录
- 插入数据到新表
- 关闭游标
create procedure p2(in uage int)
begin
declare u_name varchar(10);
declare u_pro varchar(10);
declare u_cursor cursor for select name,profession from tb_user where age <= uage;
drop table if exists tb_user_profession;
create table if not exists tb_user_profession(
id int primary key auto_increment,
name char(10),
profession varchar(100)
);
open u_cursor;
#这里写的是死循环!只靠游标和循环无法得知如何停止循环!
while true do
fetch u_cursor into u_name,u_pro;
insert into tb_user_profession value (null,u_name,u_pro);
end while;
close u_cursor;
end;
只靠游标和循环无法得知如何停止循环!
数据库会报错 [02000] [1329]No data - zero rows fetched,selected,or processed
即循环结束后我们如果只靠游标和循环无法得到什么时候游标已经空了!
解决办法!条件处理程序!
条件处理程序
用来定义在流程控制结果执行过程遇到问题时相应的处理步骤
比如说上面的游标什么时候为空问题!
使用条件处理程序只需要声明他即可!
语法
声明条件处理程序
Declare 条件处理程序的类型 handler for 执行条件1,... [SQL逻辑];
条件处理程序的类型
- Continue 继续执行当前程序
- Exit 终止执行当前程序
执行条件类型
SQLSTATE 'SQL状态码' | 状态码,如02000 |
---|---|
SQLWARNING | 所有以01开头的SQLSTATE代码简写 |
NOT FOUND | 所有以02开头的SQLSTATE代码简写 |
SQLEXCEPTION | 除了SQLWARNING和NOT FOUND以外的SQLSTATE状态码简写 |
这个状态码就是程序报错显示的第一个中括号的数字
02开头叫 代表找不到数据
具体状态码可以去MYSQL官方文档查看!
用法举例
条件处理程序可用来完善游标终止条件。
因为游标死循环后最终会爆错并返回02000状态码表示游标无数据
然后出发条件处理程序的退出类型!
create procedure p2(in uage int)
begin
declare u_name varchar(10);
declare u_pro 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;
drop table if exists tb_user_profession;
create table if not exists tb_user_profession(
id int primary key auto_increment,
name char(10),
profession varchar(100)
);
open u_cursor;
while true do
fetch u_cursor into u_name,u_pro;
insert into tb_user_profession value (null,u_name,u_pro);
end while;
end;
存储函数
介绍
存储函数是有返回值的存储过程,且存储函数必须有返回值。
存储函数的参数只能是in类型的。
语法
创建存储函数
create function 存储函数名([参数列表])
returns 返回值类型 [存储函数的特性列表]
begin
SQL语句;
return...;
end;
存储函数的调用
select 存储函数名(参数);
存储参数的特性
DETERMINITIC | 相同的输入参数总是产生相同的结果 |
---|---|
NO SQL | 在当前存储函数不包含SQL语句 |
READS SQL DATA | 存储函数只包含读取数据的SQL语句,不包含写入数据的SQL语句 |
注意事项
-
对应MYSQL 8.x版本来说
-
MYSQL的二进制日志(binary logging)是默认开启的,他要求我们必须指定存储函数的具体特性!是DETERMINITIC 还是NO SQL 或READS SQL DATA
-
对于存储函数的参数列表不需要指定参数的类型是in out 还是 inout因为他强制为in
-
对于存储函数的调用,把他当成一个值来看待!!,这个值就是他的返回值