SQL存储过程
|
269
|
0
|
MySQL,数据库

936 字
|
10 分钟

简介：

在一个业务逻辑中，可能需要查询多次数据库（多次网络请求） 可以将多条SQL语句封装在一个集合当中，调用业务对应的SQL集合。

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化开发人员的重复工作，减少网络请求的数量，提升安全（更细粒度的权限控制），从而提高开发和数据处理的效率。

存储过程思想上，就是数据库SQL语言层面的代码封装与重用。

创建存储过程：

CREATE PROCEDURE [name]([参数列表])
begin
    --SQL语句;
end;

在以终端方式创建存储过程

-- 先将结束符修改成其他符号，以避免误解析SQL语句
DELIMITER //
CREATE PROCEDURE [name]([参数列表])
BEGIN
    --SQL语句;
END //
-- 定义结束，将结束符恢复成分号 ;
DELIMITER ;

调用存储过程：

CALL [name];

查看存储过程：

-- 查询指定数据库的存储过程以及状态信息
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = '[database]'
SHOW CREATE PROCEDURE [name]; --查询某个存储过程的定义sql
image-20240907195550643

删除存储过程：

DROP PROCEDURE [if exists][name]; --删除存储过程

变量：

系统变量：

非用户定义，属于服务器层面。分为global（全局变量）和session（会话/局部变量）

查看设置系统变量：

SHOW [SESSION|GLOBAL] VARIABLES; -- 查询所有系统变量
SHOW [SESSION|GLOBAL] VARIABLES LIKE '...'; -- 查看指定系统变量
SELECT @@[SESSION|GLOBAL] [系统变量名]; -- @@代表的是系统变量
-- 设置系统变量 --
SET [SESSION|GLOBAL] [系统变量名] = ...;
set @@[SESSION.|GLOBAL.][系统变量名] = ...;

在重启后，全局变量会恢复成my.cnf中的配置。

用户自定义变量：

格式：@变量名 作用域为当前session

赋值和查看：

set @[var_name] = ...;
set @[var_name] := ...; -- 为了区分，建议使用此方式
select @[var_name] := ...;
SELECT ... into @[var_name] from [table_name];
-- 查看 --
select @[var_name] := 表达式;

局部变量：

用DECLARE声明，局部变量的范围是在其声明的BEGIN … END块。

声明赋值

-- INT/BIGINT/CHAR/VARCHAR/DATE/TIME等 default是默认值
DECLARE [变量名] [变量类型] [default ...]
-- 赋值
set [变量名] = ...;
set [变量名] := ...;
select ... into [变量名] from [table_name];

语法

参数：

参数分为三种类型

IN : 作为输入，需要调用时传入值（默认） OUT：作为输出，参数可以作为返回值 INOUT：既可以作为输入参数，也可以作为输出参数

create procedure [存储过程名称](IN/OUT/INOUT [变量名] [变量类型])

条件控制语句：

if判断：

语法：

IF 条件1 THEN ...
ELSEIF 条件2 THEN ...
...
ELSE ...
END IF;

例：

create procedure judging(in score int, out result varchar(4))
begin
    if score >= 85 then
        set result := 'A';
    elseif score>=60 then
        set result := 'B';
    else
        set result := 'C';
    END IF;
end;

使用：

call judging(59 , @result);

case：

语法

-- value == vx时 执行对应代码
CASE [value]
    WHEN [v1] THEN ...
    WHEN [v2] THEN ...
    ELSE ...
END CASE;
-- 表达式成立时
CASE
    WHEN [表达式1] THEN ...
    WHEN [表达式2] THEN ...
    ELSE ...
END CASE;

循环语句：

while：

在一开始就进行判断。

WHILE [表达式] DO
    ...
END WHILE;

repeat：

repeat在执行完逻辑，判断满足条件时，才退出循环（至少执行一次）

REPEAT
    ...
    UNTIL [表达式]
END REPEAT;

loop：

实现简单的循环，退出循环需要自行加入 LEAVE 或者 ITERATE 需要在首位指定loop的label。

LEAVE ：退出循环（break） ITERATE ：开始下一次循环（continue）

这两条语句需要加入循环的label来控制

语法：

[begin_label:] LOOP
    ...
    LEAVE [label]
    ITERATE [label]
END LOOP [end_label];

游标：

游标（CURSOR) 是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、open、fetch和close

声明游标要在声明普通变量之后。否则会报错

语法：

-- 声明游标 --
DECLARE [cursor_name] CURSOR FOR [查询语句];
-- 打开游标 --
OPEN [cursor_name];
-- 获取游标记录 --
FETCH [cursor_name] INTO [变量1],[变量2];
-- 关闭游标 --
CLOSE [cursor_name];

条件处理程序：

条件处理程序（Handler）可以用来定义并自动执行在流程控制结构执行过程中遇到定义的问题时的相应处理步骤。

在出现对应SQL状态时，会自动执行。

语法：

DECLARE [handler_action] HANDLER FOR [condition_value] [sqlstate_value];

-- handler_action --
    CONTINUE: 继续执行当前程序
    EXIT: 终止执行当前程序
-- condition_value --
    SQLSTATE ：状态码（sqlstate_value）
    SQLWARNING：所有以01开头的sqlstate代码简写
    NOT FOUND：所有以02开头的sqlstate代码简写
    SQLEXCEPTION：其他sqlstate代码简写

用来处理死循环：

declare exit handler for sqlstate '02000' close u_cursor;
while true do
    fetch u_cursor into uname,ujob;
end while;

可以在官方文档查找所有 sql 状态码的具体描述。

存储函数：

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的

语法：

CREATE FUNCTION [存储函数名称]([参数列表])
RETURNS [数据类型] [characteristic]
BEGIN
    ...SQL
    RETURN ...;
END;
-- 必须指定的函数特性 --
characteristic:
    DETERMINISTIC 相同的输入参数总是产生相同的结果
    NO SQL 不包含SQL语句
    READS SQL DATA 包含数据数据的语句，但不包含写入数据的语句
上一篇
下一篇