正文
SQL Server 中用While循环替代游标Cursor的解决方案
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
在编写SQL批处理或存储过程代码的过程中，经常会碰到有些业务逻辑的处理，需要对满足条件的数据记录逐行进行处理，这个时候，大家首先想到的方案大部分是用“游标”进行处理。
举个例子，在订单管理系统中，客服需要对订单日期为2012-09-01的销售订单进行某个批量操作，比如批量发货操作，后台业务逻辑处理时，需要对满足条件的订单记录进行逐行处理。
我首先是采用“游标”编写的业务逻辑存储过程，SQL代码可以如下：
游标
DECLARE @ORDERID VARCHAR(30) -- 声明局部游标：从订单数据表获取订单日期为2012-09-01，订单类型为Sales的订单编号
DECLARE CURSOR_ORDER CURSOR LOCAL FOR
SELECT ORDERID FROM ORDERHD H WHERE ORDERDATE = '2012-09-01' AND H.ORDERTYPE = 'Sales' -- 打开游标
OPEN CURSOR_ORDER
FETCH NEXT FROM CURSOR_ORDER INTO @ORDERID
WHILE @@FETCH_STATUS = 0
BEGIN /*
此处编写对当前行数据的业务逻辑处理代码
*/ -- 得到下一条记录
FETCH NEXT FROM CURSOR_ORDER INTO @ORDERID
END -- 关闭游标
CLOSE CURSOR_ORDER
-- 释放游标
DEALLOCATE CURSOR_ORDER
功能是实现了，但是客服在实际使用过程中，经常反馈批量操作效率太慢，需要等待较长时间才能完成操作。经过测试发现，速度慢在游标逐行处理过程中，当需要处理的记录数较大，而且游标处理位于数据库事务内时，速度非常慢。
那么，有什么方法可以解决这个处理速度慢的问题吗？
经不断的尝试，终于找到一个方法，那就是用 WHILE循环 来进行逐行数据处理。首先将需要处理的数据记录获取到一个临时表(此临时表包括2个重要字段：REFID - 记录行号，DealFlg：行处理标识，用1/0标识行是否已处理)，然后WHILE循环对临时表进行逐行处理，SQL代码如下：
While 循环
DECLARE @REFID INT
, @ORDERID VARCHAR(30) -- 获取待处理的数据记录到临时表
-- 字段说明：REFID：记录行号 / DealFlg：行处理标识
SELECT REFID = IDENTITY(INT , 1, 1), DealFlg = 0, ORDERID
INTO #Temp_Lists
FROM ORDERHD
WHERE ORDERDATE = '2012-09-01' AND H.ORDERTYPE = 'Sales' -- 获取临时表数据的最小行号
SELECT @REFID = MIN(REFID) FROM #Temp_Lists WHERE DealFlg = 0 -- 若最小行号不为空（有需要处理的数据）
WHILE @REFID IS NOT NULL
BEGIN -- 获取当前处理行的信息
SELECT @ORDERID = ORDERID FROM #Temp_Lists WHERE REFID = @REFID /*
此处编写对当前行数据的业务逻辑处理代码
*/ -- 标识当前行已处理完毕
UPDATE #Temp_Lists SET DealFlg = 1 WHERE REFID = @REFID -- 选择下一行号
SELECT @REFID = MIN(REFID) FROM #Temp_Lists WHERE DealFlg = 0 AND REFID > @REFID END
经过这样对原存储过程进行修正后，批量操作速度得到显著提升。
有兴趣的朋友，可以尝试使用这个方法替代游标，对比2种方案的处理效率。
在编写SQL批处理或存储过程代码的过程中，经常会碰到有些业务逻辑的处理，需要对满足条件的数据记录逐行进行处理，这个时候，大家首先想到的方案大部分是用“游标”进行处理。
举个例子，在订单管理系统中，客服需要对订单日期为2012-09-01的销售订单进行某个批量操作，比如批量发货操作，后台业务逻辑处理时，需要对满足条件的订单记录进行逐行处理。
我首先是采用“游标”编写的业务逻辑存储过程，SQL代码可以如下：
1 DECLARE @ORDERID VARCHAR(30)
2
3 -- 声明局部游标：从订单数据表获取订单日期为2012-09-01，订单类型为Sales的订单编号
4 DECLARE CURSOR_ORDER CURSOR LOCAL FOR
5 SELECT ORDERID FROM ORDERHD H WHERE ORDERDATE = '2012-09-01' AND H.ORDERTYPE = 'Sales'
6
7 -- 打开游标
8 OPEN CURSOR_ORDER
9 FETCH NEXT FROM CURSOR_ORDER INTO @ORDERID
10 WHILE @@FETCH_STATUS = 0
11 BEGIN
12
13 /*
14 此处编写对当前行数据的业务逻辑处理代码
15 */
16
17 -- 得到下一条记录
18 FETCH NEXT FROM CURSOR_ORDER INTO @ORDERID
19 END
20
21 -- 关闭游标
22 CLOSE CURSOR_ORDER
23 -- 释放游标
24 DEALLOCATE CURSOR_ORDER
功能是实现了，但是客服在实际使用过程中，经常反馈批量操作效率太慢，需要等待较长时间才能完成操作。经过测试发现，速度慢在游标逐行处理过程中，当需要处理的记录数较大，而且游标处理位于数据库事务内时，速度非常慢。
那么，有什么方法可以解决这个处理速度慢的问题吗？
经不断的尝试，终于找到一个方法，那就是用 WHILE循环 来进行逐行数据处理。首先将需要处理的数据记录获取到一个临时表(此临时表包括2个重要字段：REFID - 记录行号，DealFlg：行处理标识，用1/0标识行是否已处理)，然后WHILE循环对临时表进行逐行处理，SQL代码如下：
1 DECLARE @REFID INT
2 , @ORDERID VARCHAR(30)
3
4 -- 获取待处理的数据记录到临时表
5 -- 字段说明：REFID：记录行号 / DealFlg：行处理标识
6 SELECT REFID = IDENTITY(INT , 1, 1), DealFlg = 0, ORDERID
7 INTO #Temp_Lists
8 FROM ORDERHD
9 WHERE ORDERDATE = '2012-09-01' AND H.ORDERTYPE = 'Sales'
10
11 -- 获取临时表数据的最小行号
12 SELECT @REFID = MIN(REFID) FROM #Temp_Lists WHERE DealFlg = 0
13
14 -- 若最小行号不为空（有需要处理的数据）
15 WHILE @REFID IS NOT NULL
16 BEGIN
17
18 -- 获取当前处理行的信息
19 SELECT @ORDERID = ORDERID FROM #Temp_Lists WHERE REFID = @REFID
20
21 /*
22 此处编写对当前行数据的业务逻辑处理代码
23 */
24
25 -- 标识当前行已处理完毕
26 UPDATE #Temp_Lists SET DealFlg = 1 WHERE REFID = @REFID
27
28 -- 选择下一行号
29 SELECT @REFID = MIN(REFID) FROM #Temp_Lists WHERE DealFlg = 0 AND REFID > @REFID
30
31 END
改写方法2
declare @msg varchar(1000)
declare @refuseno varchar(30)
while 1=1
begin
select top 1 @refuseno = refuseno from #user_data
if @@rowcount = 0 break
/*
业务处理代码
*/
if @@error <> 0
begin
set @msg = '业务单转失败' + @refuseno
rollback
raiserror(@msg,16,1)
return
end
delete from #user_data where refuseno = @refuseno
end drop table #user_data
经过这样对原存储过程进行修正后，批量操作速度得到显著提升。
有兴趣的朋友，可以尝试使用这个方法替代游标，对比2种方案的处理效率。
