数据库常见面试题 —— 8. 游标 (CURSOR) 的定义、分类和使用

@北海怪兽

已于 2024-01-03 14:07:59 修改

阅读量9.6k

点赞数 4

分类专栏：数据库SQL常见面试题文章标签： sql 数据库

于 2022-03-24 15:10:25 首次发布

本文链接：https://blog.youkuaiyun.com/gly1653810310/article/details/123681605

版权

数据库SQL常见面试题专栏收录该内容

16 篇文章

订阅专栏

文章目录

1. 游标的定义
2. 游标的特性
3. 游标的分类
4. 使用游标
5. 创建游标
6. 游标的使用模式

1. 游标的定义

游标（Cursor）是一个存储在DBMS服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

2. 游标的特性

① 能够标记游标为只读，使数据能读取，但不能更新和删除
② 能控制可以执行的定向操作（向前、向后、第一、最后、绝对位置、相对位置等）
③能标记某些列是可编辑的，某些列为不可编辑的
④ 规定范围，使游标对创建它的特定请求（如存储过程）或对所有请求可访问
⑤ 指示DBMS对检索出的数据（而不是指表中活动数据）进行复制，使数据在游标打开和访问期间不变化

3. 游标的分类

（1）显式游标
显示游标一次从数据库中提取多行数据
（2）隐式游标
隐式游标一般只从数据库中提取一行数据

4. 使用游标

① 在使用游标前，必须声明（定义）它。这个过程实际上没有检索数据它只是定义要使用的SELECT 语句和游标选项
② 一旦声明就必须打开游标以供使用
③ 对于填写数据的游标，根据需要取出各行
④ 在结束游标使用时，必须关闭游标，可能的话，释放游标

5. 创建游标

MySQL 和 SQL Server

declare CustCusor CURSOR
FOR 
SELECT * FROM Customers
where cust_email is null

Oracle

declare cursor custcursor
is 
select * from customers 
where cust_email is null

6. 游标的使用模式

6.1 声明游标

首先，需要使用 DECLARE 语句声明游标，并定义游标的名称、数据类型和查询语句等属性。
例如：

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] （说明游标的“作用域”）
     [ FORWARD_ONLY | SCROLL ] （说明游标的“方向”）
     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] （“说明游标的“类型”）
     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] 
     [ TYPE_WARNING ] 
     FOR select_statement 
     [ FOR UPDATE [ OF column_name [ ,...n ] ] ]
[;] 

DECLARE my_cursor CURSOR FOR SELECT col1, col2 FROM my_table;

① 游标的作用域
LOCAL
说明所声明的游标为局部的，其作用域为创建它的批处理、存储过程或触发器，即在批处理、调用它的存储过程或触发器执行完成后，该游标被系统隐式释放。但，若游标作为存储过程OUTPUT 的输出参数，在存储过程终止后给游标变量分配参数可以继续引用游标，如果 OUTPUT 参数将游标传递回来，则游标在最后引用它的变量释放或离开作用域时释放。

GLOBAL
指定该游标的作用域对来说连接是全局的。在由连接执行的任何存储过程或批处理中，都可以引用该游标名称。该游标仅在断开连接时隐式释放。

注意：如果 GLOBAL 和 LOCAL 参数都未指定，则默认值由 default to local cursor 数据库选项的设置控制。在 SQL Server 7.0 版中，该选项默认为 FALSE，以便与 SQL Server 的早期版本匹配，在早期版本中，所有游标都是全局的。

② 游标方向
FORWARD_ONLY
指定游标只能从第一行滚动到最后一行。FETCH NEXT 是唯一支持的提取选项。如果在指定 FORWARD_ONLY 时不指定 STATIC、KEYSET 和 DYNAMIC 关键字，则游标作为 DYNAMIC 游标进行操作。如果 FORWARD_ONLY 和 SCROLL 均未指定，则除非指定 STATIC、KEYSET 或 DYNAMIC 关键字，否则默认为 FORWARD_ONLY。STATIC、KEYSET 和 DYNAMIC 游标默认为 SCROLL。与 ODBC 和 ADO 这类数据库 API 不同，STATIC、KEYSET 和 DYNAMIC Transact-SQL 游标支持 FORWARD_ONLY。

SCROLL
可以随意滚动游标。指定所有的提取选项（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）均可用。如果未在 ISO DECLARE CURSOR 中指定 SCROLL，则 NEXT 是唯一支持的提取选项。如果也指定了 FAST_FORWARD，则不能指定 SCROLL。

③ 游标类型
STATIC
定义一个游标，以创建将由该游标使用的数据的临时复本。对游标的所有请求都从 tempdb 中的这一临时表中得到应答；因此，在对该游标进行提取操作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改。及该游标是只读的。

DYNAMIC
定义一个游标，以反映在滚动游标时对结果集内的各行所做的所有数据更改。行的数据值、顺序和成员身份在每次提取时都会更改。

KEYSET
指定当游标打开时，游标中行的成员身份和顺序已经固定。对行进行唯一标识的键集内置在 tempdb 内一个称为 keyset 的表中。

FAST_FORWARD
指定启用了性能优化的 FORWARD_ONLY、READ_ONLY 游标。如果指定了 SCROLL 或 FOR_UPDATE，则不能也指定 FAST_FORWARD。

6.2 打开游标

使用 OPEN 语句打开游标，准备开始处理数据。
例如：

OPEN my_cursor;

在打开游标之后，可以使用全局变量@@CURSOR_ROWS来显示游标内的记录条数，使用全局变量@@FETCH_STATUS来返回上一条游标FETCH语句的状态。

@@FETCH_STATUS 是一个系统变量，返回一个整数，用于指示最后一次 FETCH 语句的执行结果，并根据该结果执行某些操作。

如果 FETCH 语句成功检索到了一行或多行数据，则 @@FETCH_STATUS 的值为 0。
如果 FETCH 语句未检索到任何数据，则 @@FETCH_STATUS 的值为 -2。
如果 FETCH 语句发生错误，则 @@FETCH_STATUS 的值为 -1。

用户可以利用 @@FETCH_STATUS 的返回结果，来判断是否还有更多的数据需要获取（@@FETCH_STATUS 的值为 0 意味着还有更多数据，否则表示获取完毕），并决定是否继续执行 FETCH 语句或退出循环。

6.3 检索数据

使用 FETCH 语句从游标中检索数据，可以一次检索一行或多行数据，并将数据存储在变量中进行处理。
例如：

FETCH NEXT FROM my_cursor INTO @col1, @col2;

--读取当前行的下一行，并使其置为当前行(刚开始时游标置于表头的前一行，即若表是从0开始的，游标最初置于-1处，所以第一次读取的是头一行)
fetch next from my_cursor 
 --读取当前行的前一行，并使其置为当前行
fetch prior from my_cursor
--读取游标的第一行，并使其置为当前行（不能用于只进游标）
fetch first from my_cursor 
--读取游标的最后一行，并使其置为当前行（不能用于只进游标）
fetch last from my_cursor 
--读取从游标头开始向后的第2行，并将读取的行作为新的行
fetch absolute 2 from my_cursor 
--读取从当前行开始向后的第3行，并将读取的行作为新的行
fetch relative 3 from my_cursor 
--读取当前行的上两行，并将读取的行作为新的行
fetch relative-2 from my_cursor

◆ NEXT：当前记录的下一条记录。
◆ PRIOR：当前记录的上一条记录。
◆ FIRST：游标中第一条记录。
◆ LAST：游标中最后一条记录。
◆ ABSOLUTE：游标中指定位置的记录，即绝对位置。
◆ RELATIVE：相对于当前位置的记录，即相对位置。

6.4 处理数据

在每次检索数据之后，使用变量中的数据执行所需的操作，例如，对数据进行计算、逻辑判断、修改等。

6.5 循环处理

通常使用循环结构（例如 WHILE、LOOP）来遍历游标中的所有数据，并在每次循环迭代中执行相应的操作。

6.6 关闭和释放游标

在处理完所有数据之后，使用 CLOSE 和 DEALLOCATE 语句关闭和释放游标。
例如：

CLOSE my_cursor;

DEALLOCATE my_cursor;

7. 实际应用

示例1 （使用游标进行数据的更新或插入）

在这里插入图片描述
公交车和乘客到达 LeetCode 站。如果一辆公交车在 tbus 时间点到达车站，乘客在 tpassenger 到达车站，其中 tpassenger <= tbus，而该乘客没有赶上任何公交车，则该乘客将搭乘该公交车。此外，每辆公交车都有一个容量。如果在公交车到站的那一刻，等待的乘客超过了它的载客量 capacity，只有 capacity 个乘客才会搭乘该公交车。
编写解决方案，报告使用每条总线的用户数量。
返回按 bus_id 升序排序的结果表。
结果格式如下所示。
在这里插入图片描述

--建表
if object_id('Buses','u') is not null drop table Buses
go
create table Buses(
  bus_id       int
, arrival_time  int
, capacity      int
)
go
insert into  Buses
values
( 1   ,2    ,1    )
,( 2   ,4    ,10   )
,( 3   ,7    ,2    )
go
if object_id('Passengers','u') is not null drop table Passengers
go
create table Passengers(
  passenger_id  int
, arrival_time  int
)
go
insert into Passengers
values
( 11  ,1    )
,( 12  ,1    )
,( 13  ,5    )
,( 14  ,6    )
,( 15  ,7    )
go
--查询
alter table Passengers add Flag int not null default 0
,bus_id int
--select * from Passengers

declare @bus_id int,@arrival_time int ,@capacity  int,@i int
--声明游标
declare C cursor for
select  bus_id, arrival_time,capacity from Buses order by arrival_time
--打开游标
open C
--从游标中检索数据，可以一次检索一行或多行数据，并将数据存储在变量中进行处理。
fetch next from C into @bus_id,@arrival_time,@capacity
--如果 FETCH 语句成功检索到了一行或多行数据，则 @@FETCH_STATUS 的值为 0
while @@FETCH_STATUS=0
begin

	update a
	set bus_id = @bus_id,flag = 1
	from Passengers a
	inner join (select * from (select passenger_id,arrival_time,ROW_NUMBER()over(order by arrival_time)Rnk
	                            from Passengers
	                            where arrival_time <= @arrival_time and flag = 0)a
	            where Rnk<=@capacity)b
	on a.passenger_id = b.passenger_id

fetch next from C into @bus_id,@arrival_time,@capacity

end
close C
deallocate C

select a.bus_id,count(b.passenger_id) as passengers_cnt from Buses a
left join  Passengers b
on a.bus_id = b.bus_id
group by a.bus_id

示例2 （使用游标实现分页）

--建表
if object_id ('Student','U') is not null drop table Student
GO
create table Student (
    Student_id int identity (1,1)
    ,Student_Name varchar(20)
)
go
insert into Student (Student_Name)
values
 ('Lily'          )
,('Andrew'        )
,('Sophia'        )
,('Benjamin'      )
,('Ava'           )
,('Richard'       )
,('Abigail'       )
,('Charles'       )
,('Emma'          )
,('Alexander'     )
,('Isabella'      )
,('Daniel'        )
,('Madison'       )
,('Joseph'        )
,('Mia'           )
,('Samuel'        )
,('Charlotte'     )
,('George'        )
,('Ella'          )
,('Edward'        )
,('Amelia'        )
,('Henry'         )
,('Aria'          )
,('Thomas'        )
,('Layla'         )
,('Katherine'     )
,('Scarlett'      )
,('Christopher'   )
,('Riley'         )
,('Timothy'       )
,('Zoey'          )
,('Steven'        )
,('Penelope'      )
,('Marcus'        )
,('Avery'         )
go
select * from Student

--Output

alter proc splitPage
 @pageIndex int    --分页后的第几页
,@pageSize int     --分页后每页有多少条数据
as
begin

declare @table table (
     Student_ID INT
    ,Student_Name varchar(20) )

declare cur cursor scroll for select Student_Id,Student_Name from Student

declare @count int , @name varchar(20) , @no int
set @count=(@pageIndex-1) * @pageSize + 1

open cur
fetch absolute @count from cur into @no,@name

while @count <= @pageSize * @pageIndex and @@FETCH_STATUS=0
begin
insert into @table
values
(@no ,@name)
set @count = @count+1
fetch absolute @count from cur into  @no,@name end
end

close cur
deallocate cur

select * from @table
end

--Run
exec splitPage 2,10