(新手友好)MySQL学习笔记(8):存储过程，自定义函数，游标

存储过程

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

使用存储过程

#创建存储过程
create procedure 存储过程名()
begin——存储过程的逻辑代码——可以包含SQL语句，控制结构和变量操作
end;
#执行存储过程
call 存储过程名();
#删除存储过程
drop procedure [if exists] 存储过程名();

下面给大家看一下存储过程的运行，查一下员工表和部门表

create procedure mypro()
begin select * from emp;select * from dept;
end;
call mypro();
drop procedure if exists mypro;

使用参数

#创建存储过程
create procedure 存储过程名(
[in|out|inout] 参数名1 参数类型,
[in|out|inout] 参数名2 参数类型,
.....
)
begin——存储过程的逻辑代码——可以包含SQL语句，控制结构和变量操作
end;
#存储过程外定义全局变量 set @varName
set @var = 1;

参数类型：

in（默认）：输入参数，存储过程的输入值，从外部传递给存储过程，在存储过程内部只读，不修改值
out：输出参数，存储过程的返回值，存储过程可以修改它的值并返回
inout：输入和输出参数既可以作为输入值传递给存储过程，也可以在存储过程中修改值并返回

#输入参数特性
drop procedure if exists mypro;
create procedure mypro(in i int)
beginselect i;set i = 2;select i;
end;set @var = 1;
call mypro(@var);
select @var;

最后我们得到的三个结果为

i	i	@var
1	2	1

可以看出@var虽然输入到存储过程内部并进行运算操作，但外部的@var本身并未受到修改。

#输出参数特性
drop procedure if exists mypro;
create procedure mypro(out i int)
beginselect i;set i = 2;select i;
end;set @var = 1;
call mypro(@var);
select @var;

i	i	@var
NULL	2	2

我们可以看到，输出参数在存储过程内部，没有读权限，所以第一次读出NULL因为读不出来，然后赋值后可以读出来2，存储过程结束后，外部的@var本身受到了修改，从原本的1赋值成了2，说明输出参数是可以返回出来作用到外部的变量的。

#输入输出参数特性
drop procedure if exists mypro;
create procedure mypro(inout i int)
beginselect i;set i = 2;select i;
end;set @var = 1;
call mypro(@var);
select @var;

i	i	@var
1	2	2

可以看出，输入输出参数在存储过程内部既有读的权限，也有返回的权限，等同于出入参数与输出参数的结合。

使用变量

在存储过程内部定义局部变量，定义变量的声明语句碧玺放在最上面

#创建存储过程
create procedure 存储过程名(
[in|out|inout] 参数名1 参数类型,
[in|out|inout] 参数名2 参数类型,
.....)
begin#定义函数declare 变量名 变量的数据类型 [default 默认值]——存储过程的逻辑代码——可以包含SQL语句，控制结构和变量操作
end;#变量赋值（放在逻辑代码部分）
set 变量名 = 赋的值
#通过查询将结果赋值给变量
select 字段名 into 变量名 from 表名 [where 限制条件].....

变量赋值

drop procedure if exists mypro;
create procedure mypro()
begindeclare a int default 0;set a = 3;
end;call mypro();

通过查询将结果赋值给变量

#不报错，因为通过限制条件将查询结果筛成1行，所以赋值成功
drop procedure if exists mypro;
create procedure mypro()
begindeclare a int default 0;select empno into a from emp where empno = 7396;select a;
end;call mypro();#报错，因为没有限制条件，导致查询结果有多行，所以复制失败报错
drop procedure if exists mypro;
create procedure mypro()
begindeclare a int default 0;select empno into a from emp;select a;
end;call mypro();

逻辑语句

条件语句（if，case）与常见语言中的条件语句用法相同，需要注意标准格式

if 条件 then逻辑代码;
[elseif 条件 then逻辑代码;]
[else 条件 then逻辑代码;]
end if;casewhen 条件1 then逻辑代码;when 条件1 then逻辑代码;else 逻辑代码;
end case;

循环语句（where，repeat）与常见语言中的循环语句用法相同，需要注意标准格式

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

值得注意的是repeat属于直到型循环，与while这种当型不同，需要确保一定能达到循环条件否则会陷入死循环。

存储过程练习

1.计算前n项和

#计算前n项和
drop procedure if exists mypro;
create procedure mypro(in n int)
begindeclare a int default 1;declare sum1 int default 0;repeat set sum1 = sum1+a;set a = a+1;until a>n end repeat;select sum1;
end;set @var = 5;
call mypro(@var);

存储过程特点

优点：
- 代码复用：存储过程可以被多个应用程序或脚本调用，实现了代码的复用。
- 提高性能：MySQL会将编译后的存储过程放入缓存。如果应用程序在单个连接中多次使用储存过程，直接使用编译版本。
- 减少网络流量：存储过程可以一次执行多条SQL语句，减少了与数据库的交互次数。
- 安全控制：存储过程可以对数据库中的数据进行严格的访问控制和权限管理。
- 数据一致性：存储过程可以实现复杂的数据操作和事务处理，确保数据的一致性和完整性。
缺点：
- 创建和维护成本高：SQL是一种结构化查询语言，难以处理复杂的逻辑业务。
- 开发调试复杂：需要通过特定的工具和技术进行，不方便调试。
- 可移植性差：存储过程通常依赖于特定的数据库平台和版本，不同的数据库系列之间存储过程的语法和特性可能有差异，导致存储过程的可移植性差。

自定义函数

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

#创建函数
create function 函数名([参数1 数据类型[,参数2 数据类型,.....]])
return 返回值类型
begin 函数逻辑代码
end;
#调用函数
select 函数名([参数1,参数2,.....])
#删除函数
drop function [if exists] 函数名;

下面是例子

#求员工最高工资
drop function if exists maxSal
create function maxSal()
return int
begindeclare max_sal int;select max(sal) into max_sal from emp;return max_sal;
end;select maxSal();

自定义函数练习

创建函数：实现检验登录的账号密码是否正常
- 创建账号信息表：（自增id，name，password）并存入数据
- 创建一个函数：传入两个参数
- 函数返回结果：（1）用户不存在（2）密码错误（3）登陆成功

drop table if exists users;
create table users(id int primary key auto_increment,uname varchar(12),upassword varchar(12)
);insert into users(uname,upassword  
)
values
('jack','123456'),
('hanle','123456');drop function if exists login;
create function login(uname1 varchar(12) ,upassword1 varchar(12))
returns varchar(5)
beginif (select uname from users where uname = uname1 ) is null thenreturn '用户不存在';elseif (select upassword from users where uname = uname1) not like upassword1 thenreturn '密码错误';elsereturn '登陆成功';end if;
end;

注意：在if语句中不能直接判断select语句，需要判断select出的字段是否符合条件

游标

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

创建游标后，可以使用open 语句打开游标，开始执行游标指定的查询语句并生成结果集。在游标打开得到结果集后，可以使用fetch语句访问它的每一行。

游标处理完成后，应关闭游标，释放游标使用的内存与资源。

下边是在存储过程或者函数中创建使用游标的语句

#创建游标
declare 游标名 cursor for 查询语句;
#打开游标
open 游标名;
#读取游标数据到变量中
fetch 游标名 into 变量名1[,变量名2,.....];
#关闭游标
close 游标名;

下边给大家使用游标的两种情况

检索单行数据：

#创建存储过程
drop procedure if exists testCursor;
create procedure testCursor()
begin#声明变量declare emp_name varchar(20);#声明游标，查询emp表中的enamedeclare mycursor cursorfor select ename from emp;#打开游标open mycursor;#多次读取游标数据fetch mycursor into emp_name;select emp_name;fetch mycursor into emp_name;select emp_name;#关闭游标close mycursor;
end;call testCursor;

首先这是我们直接查询ename的结果

这是我们使用游标查出来的两次结果

emp_name	emp_name
ADAMS	ALLEN

我们会发现游标在取出后会自动下移，我们需要知道这点来方便我们去理解和使用游标。

如果说我们使用取出语句直接查询游标会怎样？答案是会报错。

因为游标有点像指向行的一个指针，取出游标的内容，就等同于取出行的数据，如果直接读取游标，相当于我们不查询数据而要查询一个箭头，表中有数据但是没有箭头，因此数据库会报错。

循环检索数据

#创建存储过程
drop procedure if exists testCursor;
create procedure testCursor()
begin#声明变量declare emp_name varchar(20);declare i int default 0;#声明游标，查询emp表中的enamedeclare mycursor cursorfor select ename from emp;#打开游标open mycursor;#多次读取游标数据while i<5 dofetch mycursor into emp_name;set i=i+1;select emp_name;end while;#关闭游标close mycursor;
end;
call testCursor;

当然使用游标本身也可以设置结束条件，比如我们在不知道具体员工数时，要使用游标取出所有的员工名的情况下，就可以声明一个变量并声明一个句柄。

声明一个结束标志位的变量，声明一个句柄：当not found(sqlstate '02000')出现时，修改结束标志位。

下面是具体的语句和例子，后边有练习

declare done int default 0;
#这里声明句柄：not found 就修改局部变量
declare continue handler for not found set done = 1;
#意思就是声明游标持续操作到 not found后修改局部变量的值

#创建存储过程
drop procedure if exists testCursor;
create procedure testCursor()
begin#声明变量declare emp_name varchar(20);declare done int default 0;#声明游标，查询emp表中的enamedeclare mycursor cursorfor select ename from emp;#声明句柄：not found结束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 mycursor;
end;
call testCursor;

这样就会把emp表中的所有ename取出来，而不怕陷入死循环。

注意：声明的顺序有要求，declare的顺序为：局部变量，游标，句柄

游标练习

创建一个存储过程，使用一个部门编号作为参数，使用游标遍历该部门的所有员工，并将员工的薪水增加100。

练习答案

存储过程答案

#计算前n项和
drop procedure if exists mypro;
create procedure mypro(in n int)
begindeclare a int default 1;declare sum1 int default 0;repeat set sum1 = sum1+a;set a = a+1;until a>n end repeat;select sum1;
end;set @var = 5;
call mypro(@var);

自定义函数答案

创建函数：实现检验登录的账号密码是否正常
创建账号信息表：（自增id，name，password）并存入数据
创建一个函数：传入两个参数
函数返回结果：（1）用户不存在（2）密码错误（3）登陆成功

drop table if exists users;
create table users(id int primary key auto_increment,uname varchar(12),upassword varchar(12)
);insert into users(uname,upassword  
)
values
('jack','123456'),
('hanle','123456');drop function if exists login;
create function login(uname1 varchar(12) ,upassword1 varchar(12))
returns varchar(5)
beginif (select uname from users where uname = uname1 ) is null thenreturn '用户不存在';elseif (select upassword from users where uname = uname1) not like upassword1 thenreturn '密码错误';elsereturn '登陆成功';end if;
end;

游标答案

#创建一个存储过程，使用一个部门编号作为参数，使用游标遍历该部门的所有员工，并将员工的薪水增加100。
#创建存储过程
drop procedure if exists salup;
create procedure salup(in in_deptno int(2))
begin#声明变量declare emp_no int(2);declare done int default 0;#声明游标declare  dept_empno cursor forselect empno from emp where deptno = in_deptno;#声明句柄declare continue handler for not found set done = 1;#查询修改前的薪资select sal from emp where deptno = in_deptno;#打开游标open dept_empno;#读取数据进行操作while done = 0 dofetch dept_empno into emp_no;if done = 0 thenupdate empset sal = sal+100where empno = emp_no;end if;end while;#关闭游标close dept_empno;#查询修改后的薪资select sal from emp where deptno = in_deptno;
end;
call salup(10);

这是结果