SQLserver中的游标的分类
在 SQL Server 中,游标(Cursor)是一种数据库对象,用于逐行处理结果集中的数据。游标可以用于复杂的数据处理任务,尤其是那些不能通过简单的 SELECT
语句和 JOIN
操作完成的任务。SQL Server 提供了几种类型的游标,每种游标都有其特定的用途和性能特点。
只进游标
DECLARE STOCK_CURSOR CURSOR LOCAL
FOR
SELECT * FROM STOCK
OPEN STOCK_CURSOR
DECLARE @STOCKID INT,@STONAME NVARCHAR(50),@STOCKNO NVARCHAR(50),@STOCK_CURSOR CURSOR
SET @STOCK_CURSOR =STOCK_CURSOR
FETCH NEXT FROM @STOCK_CURSOR INTO @STOCKID,@STONAME,@STOCKNO
WHILE @@FETCH_STATUS=0
BEGIN
PRINT CAST(@STOCKID AS VARCHAR)+','+@STONAME+','+CAST(@STOCKNO AS VARCHAR);
FETCH NEXT FROM @STOCK_CURSOR INTO @STOCKID,@STONAME,@STOCKNO
END
CLOSE STOCK_CURSOR
DEALLOCATE STOCK_CURSOR
1. 静态游标(Static Cursor)
静态游标是一个快照,它在游标打开时创建,并且不会随着底层数据的变化而更新。即使基础数据发生变化,静态游标中的数据也不会改变。
2. 动态游标(Dynamic Cursor)
动态游标会反映基础数据的任何变化。如果基础数据在游标打开后被修改,这些变化会反映在游标中。
3. 快照游标(Snapshot Cursor)
快照游标类似于静态游标,它在游标打开时创建一个数据的快照。但是,与静态游标不同的是,快照游标通常使用锁定读取(shared locks),这意味着它们不会阻止其他用户更新数据。
4. 键集驱动游标(Keyset-Driven Cursor)
键集驱动游标在打开时获取结果集中的键集(通常是主键),并且只反映这些键对应的数据行的更新。如果数据行被删除,游标中将不再包含这些行;如果数据行被添加,游标中也不会包含这些新行。但是,如果游标中的行被更新,游标会反映这些更新。
5. 快速向前游标(Fast Forward Cursor)
快速向前游标是一种只读游标,它提供了快速的向前滚动能力。这种游标不支持滚动到任意位置,只能从第一行开始,逐行向前移动。
游标的声明和使用
在 SQL Server 中,你可以使用 DECLARE
语句来声明一个游标,并使用 OPEN
、FETCH
、CLOSE
和 DEALLOCATE
语句来操作游标。
-- 声明游标
DECLARE @cursor CURSOR;
DECLARE @var1 INT, @var2 INT;
-- 定义游标
SET @cursor = CURSOR FOR
SELECT Column1, Column2
FROM TableName;
-- 打开游标
OPEN @cursor;
-- 从游标中提取数据
FETCH NEXT FROM @cursor INTO @var1, @var2;
-- 循环处理游标中的数据
WHILE @@FETCH_STATUS = 0
BEGIN
-- 处理数据
PRINT @var1, @var2;
-- 获取下一行数据
FETCH NEXT FROM @cursor INTO @var1, @var2;
END
-- 关闭游标
CLOSE @cursor;
-- 释放游标资源
DEALLOCATE @cursor;
注意事项
-
游标通常比集合操作(如
JOIN
或子查询)效率低,因为它们逐行处理数据。 -
在可能的情况下,尽量使用集合操作来替代游标。
-
确保在游标使用完毕后关闭并释放游标,以避免资源泄露。
游标的生命周期
游标的生命周期指的是游标从创建到销毁的整个过程。在 SQL Server 中,游标的生命周期包括以下几个阶段:
-
声明(Declaration):在这个阶段,你定义游标的属性,包括它的名称、返回的数据类型、以及它将遍历的查询。这个阶段不实际检索数据,只是定义了游标的结构。
DECLARE cursor_name CURSOR FOR select_statement;
-
打开(Opening):在声明游标之后,你需要打开游标以开始检索数据。打开游标后,你可以开始从结果集中提取数据。
OPEN cursor_name;
-
提取(Fetching):打开游标后,你可以使用
FETCH
语句从游标中提取数据。你可以一次提取一行(单行提取),也可以一次提取多行(批量提取)。FETCH NEXT FROM cursor_name INTO variable_list;
-
滚动(Scrolling):在提取数据后,你可能需要在结果集中向前或向后滚动以访问不同的数据行。这可以通过
FETCH
语句与不同的选项来实现,如FETCH PRIOR
、FETCH ABSOLUTE
等。 -
关闭(Closing):一旦完成数据提取,你应该关闭游标。关闭游标会释放与游标相关联的资源,但不释放游标本身。
CLOSE cursor_name;
-
释放(Deallocating):关闭游标后,你需要释放游标以彻底清除游标的定义和资源。这一步是游标生命周期的最后阶段。
DEALLOCATE cursor_name;
-
销毁(Destruction):在游标被释放后,它就不再存在了。如果在游标声明的会话结束后游标还未被释放,SQL Server 会自动释放它。
游标生命周期的示例
-- 声明游标
DECLARE employee_cursor CURSOR FOR
SELECT EmployeeID, LastName
FROM Employees;
-- 打开游标
OPEN employee_cursor;
-- 提取数据
FETCH NEXT FROM employee_cursor INTO @EmployeeID, @LastName;
-- 循环处理数据
WHILE @@FETCH_STATUS = 0
BEGIN
-- 处理提取的数据
PRINT @EmployeeID + ' : ' + @LastName;
-- 继续提取下一行数据
FETCH NEXT FROM employee_cursor INTO @EmployeeID, @LastName;
END
-- 关闭游标
CLOSE employee_cursor;
-- 释放游标
DEALLOCATE employee_cursor;
注意事项
-
确保在游标使用完毕后及时关闭和释放游标,以避免不必要的资源占用。
-
在可能的情况下,考虑使用集合操作来替代游标,因为游标通常比集合操作效率低。
-
在编写包含游标的代码时,注意异常处理,确保即使在发生错误的情况下也能正确关闭和释放游标。
游标的作用:定位到结果集中的某一行,对当前位置的数据进行读写。
缺点: 数据读取出来放到内存,--内存空间有限,如果数据量太大,内存空间不足
适用于:数据量小的情况。
静态游标:结果集,对数据库如何操作,结果集都不会变;支持滚动,
结果集与操作之后的数据一致---关闭游标,再重新打开
动态游标:与静态相对,前后滚动,结果集获取所有的改变,
提取时,行数据、顺序、成员都会发生变化,对数据库的操作都通过游标可见。
游标外部所做的更新直到提交时才可见。
只进游标:不支持滚动,从头读到尾,对数据库所做更改在提取时是可见的。
只进不退。提取后所做的更改是不可见的。