当前位置：首页 > 数据库 > MsSql > 详细页面

SQL中的游标、异常处理、存储函数及总结(最新推荐)

时间：2023-10-26来源：系统城装机大师作者：佚名

一.游标

游标(cursor)是用来存储查询结果集的数据类型,在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH和 CLOSE.

格式

操作

演示

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23 use test_procedure ;
-- 需求:输入一个部门名，查询该部门员工的编号、名字、薪资，将查询的结果集添加游标
delimiter $$
create procedure proc21(in in_name varchar(50))
begin
-- 定义局部变量
declare var_empno int;
declare var_ename varchar(50);
declare var_sal decimal(7,2);
-- 声明游标
declare my_cursor cursor for
select empno ,ename ,sal from emp e,dept d where d.dname =in_name ;
-- 打开游标
open my_cursor;
-- 通过游标获得值
fetch my_cursor into var_empno,var_ename,var_sal;
select var_empno,var_ename,var_sal;
-- 关闭游标
close my_cursor;
end $$
delimiter ;

call proc21('销售部');

运行输出的结果是

但是

我们发现符合要求的有多个值，这是因为游标是一条条往下执行的所以我们可以用一个循环

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25 use test_procedure ;
-- 需求:输入一个部门名，查询该部门员工的编号、名字、薪资，将查询的结果集添加游标
delimiter $$
create procedure proc21(in in_name varchar(50))
begin
-- 定义局部变量
declare var_empno int;
declare var_ename varchar(50);
declare var_sal decimal(7,2);
-- 声明游标
declare my_cursor cursor for
select empno ,ename ,sal from emp e,dept d where d.dname =in_name ;
-- 打开游标
open my_cursor;
-- 通过游标获得值
label:loop
fetch my_cursor into var_empno,var_ename,var_sal;
select var_empno,var_ename,var_sal;
end loop label;
-- 关闭游标
close my_cursor;
end $$
delimiter ;

call proc21('销售部');

结果会报错

No data - zero rows fetched, selected, or processed 这句话意思是没有数据零行读取选择或处理，就是说循环到最后没值了，所以我们需要找到一个条件并及时退出循环，这就需要异常处理—handler句柄。

二.异常处理—handler句柄

格式

注意

在语法中，变量声明、游标声明、handler声明是必须按照先后顺序书写的，否则创建存储过程出错。

--定义句柄:定义异常的处理方式

1:异常处理完之后程序该怎么执行
continue :继续执行剩余代码

exit :直接终止程序
undo:不支持
2:触发条件

条件码:1329

条件名:
SQLWARNING

NOT FOUND

SQLEXCEPTION

演示

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75 -- 用条件名
use test_procedure ;
-- 需求:输入一个部门名，查询该部门员工的编号、名字、薪资，将查询的结果集添加游标
delimiter $$
create procedure proc22(in in_name varchar(50))
begin
-- 定义局部变量
declare var_empno int;
declare var_ename varchar(50);
declare var_sal decimal(7,2);

-- 定义标记值
declare flag int default 1;
-- 声明游标
declare my_cursor cursor for
select empno ,ename ,sal from emp e,dept d where d.dname =in_name ;
-- 定义句柄，当数据未发现时将标记位设置为0
declare continue handler for not found set flag=0;

-- 打开游标
open my_cursor;
-- 通过游标获得值
label:loop
fetch my_cursor into var_empno,var_ename,var_sal;
if flag=1 then
select var_empno,var_ename,var_sal;
else
leave label;
end if;
end loop label;
-- 关闭游标
close my_cursor;
end $$
delimiter ;

call proc22('销售部');

-- 用条件码
use test_procedure ;
-- 需求:输入一个部门名，查询该部门员工的编号、名字、薪资，将查询的结果集添加游标
delimiter $$
create procedure proc23(in in_name varchar(50))
begin
-- 定义局部变量
declare var_empno int;
declare var_ename varchar(50);
declare var_sal decimal(7,2);

-- 定义标记值
declare flag int default 1;
-- 声明游标
declare my_cursor cursor for
select empno ,ename ,sal from emp e,dept d where d.dname =in_name ;
-- 定义句柄，当数据未发现时将标记位设置为0
declare continue handler for 1329 set flag=0;

-- 打开游标
open my_cursor;
-- 通过游标获得值
label:loop
fetch my_cursor into var_empno,var_ename,var_sal;
if flag=1 then
select var_empno,var_ename,var_sal;
else
leave label;
end if;
end loop label;
-- 关闭游标
close my_cursor;
end $$
delimiter ;

call proc23('销售部');

两个的运行结果是一样的

三.存储函数

格式

在MySQL中，创建存储函数使用create function关键字，其基本形式如下:

参数说明

(1) func_name :存储函数的名称。
(2) param_name type:可选项，指定存储函数的参数。type参数用于指定存储函数的参数类型，该类型可以是MySQL数据库中所有支持的类型。
(3)returns type:指定返回值的类型。
(4)characteristic:可选项，指定存储函数的特性。
(5)routine_body: SQL代码内容。

演示

1
2
3
4
5
6
7
8
9 drop function if exists myfunc1_emp;
delimiter $$
create function myfunc1_emp() returns int
begin
declare cnt int default 0;
select count(*) into cnt from emp;
return cnt;
end $$
delimiter ;

运行结果可以会报错，像这样

意思是说“此函数在其声明中没有确定性、无 SQL 或读取 SQL 数据，并且启用了二进制日志记录（您*可能*希望使用不太安全的 log_bin_trust_function_creators 变量）”

所以我们设置允许创建函数权限信任即可

-- 允许创建函数权限信任
set global log_bin_trust_function_creators=true ;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15 -- 允许创建函数权限信任
set global log_bin_trust_function_creators=true ;

drop function if exists myfunc1_emp;
delimiter $$
create function myfunc1_emp() returns int
begin
declare cnt int default 0;
select count(*) into cnt from emp;
return cnt;
end $$
delimiter ;

-- 调用存储函数
select myfunc1_emp();