九，存储过程与存储函数

normaling
4 阅读10分钟

一，介绍

定义： 存储过程是一组预编译的 SQL 语句的集合，存储在数据库中，可通过名称直接调用。它支持参数传递、流程控制（如条件判断、循环）、异常处理等特性，类似于编程语言中的函数。

特点：

  • 预编译：首次执行时编译，后续调用直接复用，提升效率。
  • 代码封装：将复杂逻辑封装为可重复调用的模块。
  • 减少网络开销：单次调用替代多次 SQL 传输。
  • 权限控制：可通过存储过程限制对底层表的直接操作。

二，基本语法

2.1 创建存储过程

create procedure 存储过程名([参数列表])
begin
	--SQL语句
end;

2.2 调用存储过程

call 存储过程名([参数]);

2.3 查看存储过程

查询是否存在对应存储过程

查询 information_shema系统数据库下的routines表

里面存储了对应的存储过程

当我们要查询数据库内某个表可以直接 数据库名.表名 这种方式查询，或者先use进入这个数据库在查询

select * from information_shema.routines where routine_schema = '要查询的存储过程名';

查询指定存储过程的定义方式

show create procedure 存储过程名;

2.4 删除存储过程

drop procedure [if exists] 存储过程名;

2.5 注意

如果我们是在命令行中创建存储过程的创建SQL，会报错，因为默认我们是 分号 ;结束SQL语句，但是在datagrip等软件中就不会报错！

解决办法！

  1. 修改mysql的结束符！

    delimiter 结束符

  2. 在Datagrip等软件使用

三，变量

3.1 系统变量

系统变量是MYSQL服务器提供，不是用户自定义的，属于服务器层面，分为全局变量(GLOBAL)。会话变量(SESSION)。

  1. 查看系统变量

    • 查看所有系统变量

      show [session | global ] variables;

    • 通过模糊匹配方式查询指定变量

      show [session | global ] variables like '....';

      查看指定系统变量

      select @@[session | global] 系统变量名;

  2. 设置系统变量

    set [session | global ] 系统变量名 = 值;
set @@[session | global ] 系统变量名=值;

注意：

  • 设置的系统变量值如果服务器重启后会失效，要永久修改就修改/etc/my.cnf 文件内容

  • 如果不指定是session还是global那么默认session

3.2 用户自定义变量

用户根据自己定义的变量叫用户自定义变量，用户变量不需要提前声明，用的时候直接 @变量名 使用即可，如果没有对变量进行赋值，获取到的值为NULL。

  1. 赋值

    • 用set方式赋值

      set @变量名1=值1,@变量2=值2,...;
set @变量名1:=值1,@变量2:=值2,...;		#推荐，用于区分 =

    • 用select方式赋值

      select @变量名1 := 值1.@变量名2 := 值2,...;
select 字段名 into @变量名 from 表名; #将select查询到的值作为变量的值

  2. 使用

    select @变量名1,@变量名2,...;

    用户自定义变量的作用域为当前的连接！

3.3 局部变量

  • 局部变量 是根据需要定义在局部生效的变量。

    • 访问之前需要DECLARE声明。

    • 可用作存储过程内的局部变量和输入参数，局部变量的范围是在其声明内的BEGIN...END块

    • 局部变量一般用在存储过程内部.

  1. 声明

    Declare 变量名 数据类型 [default 默认值];

  2. 赋值

    set 局部变量名1 = 值1,局部变量名2 = 值2,...;
set 局部变量名1 := 值1,局部变量名2 = 值2...;
select 字段 into 变量名 from 表名;

  3. 使用

    select 变量名;

四，存储过程的参数

4.1 参数类型

类型含义备注
IN该类作为输入，也就是需要调用时传入值默认
OUT该类作为输出，也就是该参数可以作为返回值
INOUT既可以作为传入值也可以作为返回值使用

  • in 就相当于你函数里面写的值

  • out 就相当于你函数的 return值

  • inout 就是俩个的结合体

举例

create procedure 存储过程名(in id int,out name char(5))
begin
	--SQL
end;
call 存储过程名(传入值,用于接收name这个传出参数的值的变量);

4.2 用法

create procedure 存储过程名([in | out | inout 参数名 参数的数据类型])
begin
	--SQL语句
end;

五，条件判断

5.1 if条件判断的语法

if 条件1 then
	执行语句1;
else 条件2 then
	执行语句2;
else
	执行语句n;
end if;

5.2 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;

六，循环

6.1 while循环

while循环是有条件的循环控制语句。

先判断条件，如果为true则执行逻辑，否则就不执行。

while 条件 do
	SQL逻辑
end while;

6.2 repeat循环

repeat循环是有条件的循环控制语句。

当满足条件的时候！退出循环。

先执行一次逻辑，然后判断逻辑是否满足，相当于 do while

repeat
	SQL逻辑;
	until 循环停止条件		->这里没有分号！
end repeat;

6.3 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查询表数据并将数据设置为变量数据，但是！变量只能存储单行/单列的数据！，我们查到一张表就不能用变量存储了。

这个时候就需要用到游标。

7.1 介绍

游标是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环处理。

游标的使用包括了

  • 游标的声明
  • open
  • fetch
  • close

变量的声明必须在游标之前！！

7.2 语法

  1. 声明游标

    Declare 游标名 cursor for 查询语句;

  2. 打开游标

    open 游标名;

  3. 获取游标记录

    Fetch 游标名 into 变量1,变量2,....;

  4. 关闭游标

    close 游标名;

7.3 举例

需求：根据传入的参数 uage，查询 tb_user表的姓名和专业，条件是年龄小于uage的记录，并将查询返回的结果插入到新创建的表中。

  • 这个新创建的表包含了(id ，name，profession)

逻辑实现：

  1. 声明游标，存储查询的结果集
  2. 准备工作：创建表结构
  3. 开启游标
  4. 获取游标中的记录
  5. 插入数据到新表
  6. 关闭游标
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

  • 即循环结束后我们如果只靠游标和循环无法得到什么时候游标已经空了！

解决办法：条件处理程序！

条件处理程序：用来定义在流程控制结果执行过程遇到问题时相应的处理步骤

  • 比如说上面的游标什么时候为空问题
  • 使用条件处理程序只需要声明他即可

8.1 语法

声明条件处理程序

Declare 条件处理程序的类型 handler for 执行条件1,... [SQL逻辑];

8.2 条件处理程序的类型

  • Continue 继续执行当前程序
  • Exit 终止执行当前程序

8.3 执行条件类型

SQLSTATE 'SQL状态码'状态码，如02000
SQLWARNING所有以01开头的SQLSTATE代码简写
NOT FOUND所有以02开头的SQLSTATE代码简写
SQLEXCEPTION除了SQLWARNING和NOT FOUND以外的SQLSTATE状态码简写

这个状态码就是程序报错显示的第一个中括号的数字，02开头 代表找不到数据，具体状态码可以去MYSQL官方文档查看！

8.4 用法举例

条件处理程序可用来完善游标终止条件，因为游标死循环后最终会爆错并返回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;

九，存储函数

9.1 介绍

存储函数是有返回值的存储过程，且存储函数必须有返回值，存储函数的参数只能是in类型的。

9.2 语法

创建存储函数

create function 存储函数名([参数列表])
returns 返回值类型 [存储函数的特性列表]
begin
	SQL语句;
	return...;
end;

存储函数的调用

select 存储函数名(参数);

9.3 存储参数的特性

DETERMINITIC相同的输入参数总是产生相同的结果
NO SQL在当前存储函数不包含SQL语句
READS SQL DATA存储函数只包含读取数据的SQL语句，不包含写入数据的SQL语句

9.4 注意事项

  1. 对于MYSQL 8.x版本来说，MYSQL的二进制日志(binary logging)是默认开启的，他要求我们必须指定存储函数的具体特性！是DETERMINITIC 还是NO SQL 或READS SQL DATA
  2. 对于存储函数的参数列表不需要指定参数的类型是in out 还是 inout因为他强制为in
  3. 对于存储函数的调用，把他当成一个值来看待！！，这个值就是他的返回值
avatar
文章
阅读
粉丝