|
|
存储过程
事先经过编译并存储在数据库中的一段SQL语句的结合（类似面向对象）
特点
封装、复用
可以接收参数返回结果
减少网络交互，效率提升
基本语法
定义：
create procedure 存储过程名称([参数列表])
begin
SQL语句
end;
调用：
call 名称([参数])
查看：
select * from information_schema.routins where routin_schema='数据库名称'；
show create procedure 存储过程名称；查看创建语句
删除：
drop procedure [if exists] 存储过程名称；
tips：在命令行中执行存储过程创建的SQL，需要使用关键字delimiter指定SQL语句的结束符。
否则在读到存储过程内的SQL语句后；时会结束执行
delimiter 结束符 （$$） 在语句执行前输入，创建之后改回来
变量
系统变量，MySQL服务器提供，不是由用户定义，分为全局变量（global）和会话变量（session）
全局变量对所有会话有效，会话变量仅在当前会话有效（全局变量修改之后对所有会话有效，但重启MySQL会恢复默认值）
如果想永久修改需要去修改配置文件/etc/my.cnf
查看系统变量：（不指定类型默认session）
show [session|global] variables;
show [session|global] variables like '.....';模糊匹配
select @@[session|global].系统变量名;准确查看
设置系统变量：
set [session|global] 系统变量名=值;
用户自定义变量
用户根据需要自己定义的变量，用户变量不需要声明，在使用时直接”@变量名“即可使用
作用域时当前会话
赋值：
set @变量名=值，[...];
set @变量名:=值，[...]; 推荐使用:=，可以与判断相等的=区分
select @变量名:=值，[...];
select 字段名 into @变量名 from 表名;
使用：
select @变量名；
局部变量
在局部生效的变量，访问之前通过declare声明。可以在存储过程内部使用，也可以作为输入参数。
作用范围在begin与end之间
声明：
declare 变量名 变量类型 [default...];
赋值：
set 变量名=值;
set 变量名:=值;
select 字段名 into 变量名 from 表名;
if条件判断
if 条件1 then
...
elseif 条件2 then
...
else
...
end if；
参数
in类型，传入的参数（默认）
out类型，输出的参数
inout类型，既可以作为输入，也可以作为输出
create procedure 存储过程名称([in/out/inout 参数名 参数类型])
begin
SQL语句
end;
调用存储过程时需要用一个用户自定义变量接收out的结果
使用inout类型的时候也需要自定义变量，用自定义变量输入执行前的数据并接受执行后的数据
case
case 表达式
when 值1 then ...
[when 值2 then ...]
[else ...]
end case;
case
when 表达式1 then ...
[when 表达式2 then ...]
[else ...]
end case;
contat（）函数，字符串拼接
循环
while
while 条件 do
...
end while;
repeat满足条件退出循环
repeat
...
until 条件
end repeat；
loop如果不在SQL逻辑中增加循环退出的条件，则为死循环。
leave：配合循环使用，退出循环——break功能
iterate：跳过当前循环剩余语句，直接进入下一次循环——continue功能
[标记:]loop
...
end loop [标记];
leave 标记; 退出标记循环
iterate 标记; 直接进入下一次循环
游标（光标）
用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果进行循环的处理。
声明游标：(要先声明普通变量，再声明游标)
declare 游标名称 cursor for 查询语句;
打开游标：
open 游标名称;
获取游标记录：
fetch 游标名称 into 变量[,变量]；
关闭游标：
close 游标名称;
条件处理程序handler
可以用来定义在流程控制结构执行过程中遇到问题时的处理步骤。
declare 行为 handler for 条件值,... SQL语句；
行为：continue（继续）、exit（终止）
条件值：
SQLSTATE 状态码 报错时显示状态码
SQLWARNING 所有以01开头的状态码
NOT FOUND 所有以02开头的状态码（02代表找不到数据）
SQLEXCEPTION 所有没有被SQLWARNING和NOT FOUND捕获的状态码
参考MySQL官方文档：
http://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html
存储函数
（使用相对于存储过程比较少，因为存储函数能做的存储过程也能做，并且存储函数要求必须要有返回值）
有返回值的存储过程，参数只能是in类型
create function 存储函数名称([参数列表])
returns 类型 [可选参数characterristic]
begin
...
return...;
end;
参数characterristic说明：（MySQL8.0一般要求加上该参数）
deterministic：相同输入参数有相同结果
no sql：不包含sql语句
reads sql data：包含读取数据的语句，但不包含写入数据的语句
触发器（多用于记录日志）
是与表有关的数据库对象，指在insert/update/delete之前或者之后，触发并执行触发器中定义的SQL语句集合。
new与old两个别名用来引用新的和旧的数据。只支持行级（影响几行触发几次）触发器，不支持语句级（几句语句触发几次）触发器
对于insert触发器，new表示将要或者已经新增的数据
对于update触发器，old表示改之前的数据，new表示将要或者已经修改后的数据
对于delete触发器，old表示将要或者已经删除的数据
触发器创建：
create trigger 触发器名称
before/after insert/update/delete
on 表名 for each row 代表行级触发器
begin
...
end;
触发器查看：
show triggers；
触发器删除：
drop trigger [数据库名] 触发器名称; 没有指定数据库默认为当前数据库
now()函数获取当前时间 |
|
发表于 2023-6-18 11:37:50