MySQL-存储过程和自定义函数

存储过程

        存储过程，一组预编译的 SQL 语句和流程控制语句，被命名并存储在数据库中。存储过程可以用来封装复杂的数据库操作逻辑，并在需要时进行调用。

使用存储过程

创建存储过程

create procedure 存储过程名()
begin存储过程的逻辑代码（可以包含 SQL 语句、控制结构和变量操作等）
end;

执行存储过程

call 存储过程名();

删除存储过程

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

使用参数

create procedure 存储过程名(
[in|out|inout] 参数名1参数的数据类型,
[in|out|inout] 参数名2 参数的数据类型,
)
begin存储过程的逻辑代码（可以包含 SQL 语句、控制结构和变量操作等）
end;

参数类型:

• in(默认)：输入参数（只读），存储过程的输入值，从外部传递给存储过程，存储过程内部是只读的，不能修改它的值
• out：输出参数（只写），存储过程的返回值，存储过程可以修改它的值并将其返回
• inout：输入和输出参数（可读可写）既可以作为输入值传递给存储过程，也可以由存储过程修改并返回

使用变量

在外部定义变量

set @varName

# 传入参数（只读）
create procedure mypro1(in i int)
beginselect i;set i=2;select i;
end;# 在外部定义变量
set @var=1;call mypro1(@var);
select @var;
# 结果：1 2 1

# 传出参数（只写）
create procedure mypro2(out i int)
beginselect i;set i=2;select i;
end;# 在外部定义变量
set @var=1;call mypro2(@var);
select @var;
# 结果：null null 2

# 传入传出参数（可读可写）
create procedure mypro3(inout i int)
beginselect i;set i=2;select i;
end;# 在外部定义变量
set @var=1;call mypro3(@var);
select @var;
# 结果：1 2 2

在内部定义变量

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

create procedure mypro(in i int)
begindeclare a double(7,2) default 1;  #初始化变量set i=2;select sal into a from emp limit 1;  #将字段赋值给变量select a;
end;set @var=1;call mypro(@var);
select @var;

逻辑语句

条件语句(if、case)

if 条件 then逻辑代码;
[elseif 条件 then逻辑代码;]
[else逻辑代码;]
end if;

casewhen 条件1 then逻辑代码when 条件2 then逻辑代码else逻辑代码
end case;

循环语句(while、repeat)

while 循环条件 do逻辑代码
end while;

repeat逻辑代码
until 循环条件 end repeat;

特点

优点：

• 代码复用：存储过程可以被多个应用程序或脚本调用，实现了代码的复用
• 提高性能：MySQL 将编译后的存储过程放入缓存中。如果应用程序在单个连接中多次使用存储过程，直接使用编译版本
• 减少网络流量：存储过程可以一次执行多条 SQL 语句，减少了与数据库的交互次数
• 安全控制：存储过程可以对数据库中的数据进行严格的访问控制和权限管理
• 数据一致性：存储过程可以实现复杂的数据操作和事务处理，确保数据的一致性和完整性

缺点：

• 创建和维护成本高：SQL 是一种结构化查询语言，难以处理复杂的业务逻辑
• 开发调试复杂：需要通过特定的工具和技术进行，不方便调式
• 可移植性差：存储过程通常依赖于特定的数据库平台和版本，不同的数据库系统之间存储过程的语法和特性可能有差异，导致存储过程的可移植性较差

练习

自定义函数

        function，可以使用自定义函数来扩展数据库的功能。

创建函数

create function 函数名([参数1数据类型[,参数2数据类型, …. ]])
returns 返回值类型
begin函数逻辑代码
end;

调用函数

select 函数名([参数1,参数2 ... ]);

删除函数

drop function [if exists] 函数名;

drop function if exists maxSal;
create function maxSal ()
returns int
begindeclare max sal int;select max (sal) into max sal from emp;return max sal;
end;select maxSal () ;

练习

游标

        cursor，使用游标可以对存储过程或函数中的查询结果进行逐行处理。

• 创建游标后，可以使用 open 语句打开游标，开始执行游标指定的查询语句并生成结果集
• 在游标打开得到结果集后，可以使用 fetch 语句访问它的每一行
• 游标处理完成后，应关闭游标，释放游标使用的内存和资源

创建游标

declare 游标名 cursor for 查询语句;

打开游标

open 游标名;

读取游标数据到变量中

fetch 游标名 into 变量名1[,变量名2 ... ];

关闭游标

close 游标名;

多次读取游标中的数据

检索单行数据

create procedure test_cursor()
begin#先声明变量declare emp_name varchar(20);#其次声明游标declare mycursor cursorforselect ename from emp;#打开游标open mycursor;#依次取出游标中的数据fetch mycursor into emp_name;select emp_name;fetch mycursor into emp_name;select emp_name;fetch mycursor into emp_name;select emp_name;#关闭游标close mycuesor;
end;

指定循环次数检索数据

create procedure test_cursor()
begin#先声明变量declare emp_name varchar(20);declare a int default 0;#其次声明游标declare mycursor cursorforselect ename from emp;#最后声明句柄declare continue handler for not found set done=1;#打开游标open mycursor;#依次取出游标中的数据while a<5 dofetch mycursor into emp_name;select emp_name;set a=a+1;end while;#关闭游标close mycuesor;
end;

指定循环条件检索数据（将游标中的数据全部读取出来）

create procedure test_cursor()
begin#先声明变量declare emp_name varchar(20);declare done int default 0;#其次声明游标declare mycursor cursorforselect ename from emp;#最后声明句柄declare continue handler for not found set done=1;#打开游标open mycursor;#依次取出游标中的数据while done=0 dofetch mycursor into emp_name;select emp_name;end while;#关闭游标close mycuesor;
end;

在 emp 表中只有15条记录的情况下，上述代码会打印出16个结果， 第16次结果和第15次结果是一样的。在执行15次 while 循环之后，done 还是等于0的，所以会进入第16次循环，在执行 “fetch mycursor into emp_name;” 语句时，会出现找不到的错误，随后进行异常捕获（即句柄中的代码）将done的值改为1，确保下次不会进入循环，但是当前循环还要继续执行（即执行 “select emp_name;” ），由于该次循环没有对 emp_name 的值进行修改，所以还是上一次的值，故15和16次的结果相同。

create procedure test_cursor()
begin#先声明变量declare emp_name varchar(20);declare done int default 0;#其次声明游标declare mycursor cursorforselect ename from emp;#最后声明句柄declare continue handler for not found set done=1;#打开游标open mycursor;#依次取出游标中的数据while done=0 dofetch mycursor into emp_name;if done=0 thenselect emp_name;end if;end while;#关闭游标close mycuesor;
end;

在 emp 表中只有15条记录的情况下，上述代码会打印出15个结果。

练习