SQL Server存储过程中Cursor的使用

发表于 更新于 分类于

前言

今天接手了一个 SQL Server 存储过程的活，需要修改原来的存储过程，添加一部分业务逻辑，简单查了下存储过程的使用方法。

游标

数据库查询语句的查询结果一般都是一个结果集，没办法根据查询结果针对性的对某一条数据进行处理，可以通过 where 语句限定查询结果，但是每次只能处理一条数据，需要不停的执行 select 语句。

这种需要处理非常大量的数据的场合，就可以通过游标来读取结果集依次进行处理。游标可以理解为一个指针，游标每次只指向某一行数据，通过变量赋值的方法来将指向的行的数据赋值给某个指定的变量。

使用游标

游标的使用分为几步：

  • 声明游标
  • 打开游标
  • 读取游标中的数据
  • 关闭游标
  • 释放游标

新建一个测试数据库，演示游标的使用方法。

数据库中一共有三个字段 ，写入了三行整形的测试数据，接下来使用游标来操作数据。

1
2
3
4
5
ALTER PROCEDURE [dbo].[cursor_test]
AS
BEGIN
SELECT "data"=2,"data2"=3
END

使用 select 语句可以输出数据。

下面是使用游标的完整流程。

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
ALTER PROCEDURE [dbo].[cursor_test]
AS
BEGIN
	DECLARE @data1 int --声明变量
	DECLARE @data2 int --声明变量
	DECLARE @data3 int --声明变量

	DECLARE cursor_test CURSOR FOR --创建游标

	SELECT test1,test2,test3 FROM test --查询语句

	OPEN cursor_test --打开游标

	FETCH NEXT FROM cursor_test into @data1,@data2,@data3 --从游标变量中读取值

	WHILE @@FETCH_STATUS=0 --判断FETCH语句是否执行成功

		BEGIN
		
			select "data1" = @data1,"data2" = @data2,"data3" = @data3 --输出数据
			FETCH NEXT FROM cursor_test into @data1,@data2,@data3  -- 从游标中重新读取值

		END

	CLOSE cursor_test --关闭游标

	DEALLOCATE cursor_test; --释放游标
END

执行结果为：

完整的打印了表中所有的数据。

接下使用存储过程将所有数据都加一，代码为：

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
ALTER PROCEDURE [dbo].[cursor_test]
AS
BEGIN
	DECLARE @data1 int --声明变量
	DECLARE @data2 int --声明变量
	DECLARE @data3 int --声明变量
	DECLARE @id int --声明变量

	DECLARE cursor_test CURSOR FOR --创建游标

	SELECT id,test1,test2,test3 FROM test --查询语句

	OPEN cursor_test --打开游标

	FETCH NEXT FROM cursor_test into @id,@data1,@data2,@data3 --从游标变量中读取值

	WHILE @@FETCH_STATUS=0 --判断FETCH语句是否执行成功

		BEGIN
		
			--select "data1" = @data1,"data2" = @data2,"data3" = @data3 --输出数据
			update test set test1=@data1+1,test2=@data2+1,test3=@data3+1 where id=@id
			FETCH NEXT FROM cursor_test into @id,@data1,@data2,@data3  -- 从游标中重新读取值

		END

	CLOSE cursor_test --关闭游标

	DEALLOCATE cursor_test; --释放游标
END

执行两次，可以看到数据变化了，每个数据的值都被 +1 了。

再执行三次，可以看到数据又变化了。

总结

通过游标搭配一些字符串处理函数对需要处理的数据进行一些想要的处理，例如生成序列号，生成流水号，搭配定时器自动化处理数据入库出库等等，方便了数据库开发，但是不利于版本管理，有好有坏，使用时要注意。

参考链接

https://docs.microsoft.com/zh-cn/sql/t-sql/language-elements/declare-cursor-transact-sql?view=sql-server-ver15

本文章首发于个人博客 LLLibra146’s blog
本文作者：LLLibra146
版权声明：本博客所有文章除特别声明外，均采用 © BY-NC-ND 许可协议。非商用转载请注明出处！严禁商业转载！
本文链接：https://blog.d77.xyz/archives/783796fc.html