SQL Server 多行数据处理(While循环和游标）
windex000 2018-09-19
1.使用while
IF OBJECT_ID('tempdb..#temptest',N'U') IS NOT NULL
DROP TABLE #temptest
GO
CREATE TABLE #temptest
(
ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL
,PatchID NUMERIC
,PatchName VARCHAR(50)
,[Status] INT DEFAULT 0
)
TRUNCATE TABLE #temptest
-- 插入临时表
INSERT INTO #temptest SELECT PatchID,PatchName,0 FROM dbo.SystemPatch
DECLARE @count INT
DECLARE @i INT
SET @count=0
SET @i=1
SELECT @count = COUNT(1) FROM #temptest
-- 循环
WHILE @i<= @count
BEGIN
UPDATE #temptest SET Status=1 WHERE ID=@i
SET @i = @i +1
END
-- SELECT * FROM #temptest
2.使用游标
IF OBJECT_ID('tempdb..#temptest2',N'U') IS NOT NULL
DROP TABLE #temptest2
CREATE TABLE #temptest2
(
ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL
,PatchID NUMERIC
,PatchName VARCHAR(50)
,[Status] INT DEFAULT 0
)
-- 插入临时表
INSERT INTO #temptest2 SELECT PatchID,PatchName,0 FROM dbo.SystemPatch
DECLARE @patchid INT
SET @patchid=0
-- 游标
DECLARE syspatch CURSOR FOR SELECT PatchID FROM #temptest2
OPEN syspatch
FETCH NEXT FROM syspatch INTO @patchid -- 读取第一行
WHILE @@FETCH_STATUS =0
BEGIN
UPDATE #temptest2 SET Status=1 WHERE PatchID = @patchid
FETCH NEXT FROM syspatch INTO @patchid -- 读取下一行
END
CLOSE syspatch
DEALLOCATE syspatch
-- SELECT * FROM #temptest2
测试： 1. 处理 13 条记录
while 处理耗时 ： 0 s
游标处理耗时： 0 s
2. 处理617条记录
while 处理耗时 ：0s
游标处理耗时：0s
3.处理169390
while 处理耗时 ：2s
游标处理耗时： 30m40s
