在编写SQL Server或MySQL存储过程时，经常会遇到需要逐条处理记录的情形。传统做法习惯声明游标，通过FETCH NEXT方式循环，但这种写法在大数据量下容易引发性能瓶颈。使用WHILE循环配合临时表或变量偏移，可以在多数场景下替代游标，既保持逻辑清晰，又减少系统开销。理解两者执行模型的差异，是写出高效存储过程的基础。
游标与WHILE循环的执行原理对比
游标在数据库内部会开辟一块内存区域保存结果集元数据，并在每一轮FETCH时通过网络协议或内部通道取回一行。对于默认不滚动的游标，数据库通常会在基表上施加共享锁或更新锁，直到游标关闭才释放。如果游标声明时未指定LOCAL和FAST_FORWARD，还可能在临时库生成完整副本，导致tempdb压力陡增。这种机制在并发环境中极易造成锁等待，甚至触发死锁监控。
WHILE循环本身不是数据库特有的数据访问结构，而是T-SQL或过程化SQL提供的控制流语句。典型的替代方案是先把需要处理的主键或关键列装入临时表，然后用变量记录当前处理位置，每次循环读取并删除或更新一小批数据。由于循环体内可以显式控制事务边界，锁的持有时间被压缩到单批执行窗口，数据库引擎也能更好地利用索引查找而非全表扫描。
从查询计划角度观察，游标往往生成复杂的游标算子，而WHILE循环拆分成多个独立的SELECT和UPDATE语句后，优化器能为每条语句选择最优索引。尤其在SQL Server中，使用TOP结合ORDER BY的抽取方式，可以让每次迭代都走索引查找，逻辑读次数明显低于游标逐行提取。下面用一个简单示例展示两种写法的结构差异。
-- 游标写法示例
DECLARE cur CURSOR FOR SELECT id, name FROM users WHERE status = 0;
OPEN cur;
FETCH NEXT FROM cur INTO @id, @name;
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE logs SET processed = 1 WHERE user_id = @id;
FETCH NEXT FROM cur INTO @id, @name;
END
CLOSE cur; DEALLOCATE cur;
-- WHILE替代写法示例
SELECT id INTO #tmp FROM users WHERE status = 0;
WHILE EXISTS (SELECT 1 FROM #tmp)
BEGIN
SELECT TOP 100 @id = id FROM #tmp ORDER BY id;
UPDATE logs SET processed = 1 WHERE user_id = @id;
DELETE FROM #tmp WHERE id = @id;
END
使用WHILE循环重写游标的具体步骤
第一步是明确循环边界条件。原先游标依赖@@FETCH_STATUS判断，而WHILE方案通常用EXISTS检测临时表或变量表是否还有剩余记录。如果源数据量极大，建议将主键或唯一索引列放入临时表，并为其建立聚集索引，这样DELETE和TOP抽取都能保持高效。切忌直接对业务表使用WHILE (SELECT COUNT(*) FROM 大表) 这类写法，因为每次判断都会触发全表聚合。
第二步是设计批次大小。单条处理失去了批处理优势，一次处理几万条又可能撑长事务。经验值是每批500到2000行，具体需结合行宽度和日志容量。在循环体开头用SELECT TOP (@batch) id FROM #tmp ORDER BY id取出本批主键，处理完后再统一从临时表删除。若业务允许，可每批提交一次事务，将恢复模型压力分摊到多个短事务中。
第三步是错误处理与断点续跑。游标中途失败往往难以定位已处理行，而WHILE循环因有临时表记录剩余任务，重启时只需重新执行过程即可接着跑。建议在临时表增加process_flag列，处理成功置1，异常时回滚本批并更新错误日志。如下代码展示带批次变量的完整骨架，其中包含事务控制与计数输出。
CREATE PROCEDURE dbo.proc_batch_fix
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #job (id INT PRIMARY KEY, done BIT DEFAULT 0);
INSERT INTO #job (id) SELECT id FROM orders WHERE amount < 0;
DECLARE @batch INT = 1000, @cur INT;
WHILE EXISTS (SELECT 1 FROM #job WHERE done = 0)
BEGIN
BEGIN TRY
BEGIN TRAN;
SELECT TOP (@batch) @cur = id FROM #job WHERE done = 0 ORDER BY id;
UPDATE o SET amount = 0 FROM orders o JOIN #job j ON o.id = j.id
WHERE j.done = 0 AND j.id <= @cur;
UPDATE #job SET done = 1 WHERE id <= @cur;
COMMIT TRAN;
END TRY
BEGIN CATCH
ROLLBACK TRAN;
BREAK;
END CATCH
END
END
替代方案的性能权衡与注意事项
WHILE循环并非万能银弹。当处理逻辑高度依赖前一行的计算结果且无法用集合运算表达时，游标可读性反而更高。另外，如果临时表写入和删除频率过高，页分裂可能造成碎片，此时可改用表变量并限制行数，或定期重建临时对象的索引。在MySQL中，由于存储过程对临时表支持较弱，常借助用户变量与LIMIT偏移实现类似效果，但要小心深分页导致的性能衰减。
事务粒度是另一个关键点。若把整个WHILE包在单个事务里，日志文件会随循环增长而膨胀，一旦超出阈值便报错中断。推荐每批独立提交，或在SQL Server中用SET XACT_ABORT ON配合内部保存点。同时，循环内部应避免调用标量函数嵌套，因为逐行执行会放大函数开销，应尽量改写为基于集合的UPDATE或JOIN。
最后需要关注并发影响。WHILE循环更新业务表时，若WHERE条件未命中索引，会升级为表锁。应确保过滤列有合适索引，并使用ROWLOCK提示减少锁定范围。通过动态管理视图观察sys.dm_db_index_usage_stats，可验证每次迭代是否走了预期索引。只有在充分测试执行时间与锁等待后，才能放心将线上游标存储过程改为WHILE结构。