SqlServer 利用游標(biāo)批量更新數(shù)據(jù)

Intro

游標(biāo)在有時(shí)候會(huì)很有用，在更新一部分不多的數(shù)據(jù)時(shí)，可以很方便的更新數(shù)據(jù)，不需要再寫(xiě)一個(gè)小工具來(lái)做了，直接寫(xiě) SQL 就可以了

Sample

下面來(lái)看一個(gè)實(shí)際示例：

-- 聲明字段變量 DECLARE @RegionCode INT; DECLARE @RegionName NVARCHAR(64); DECLARE @ProvinceId INT; -- 聲明游標(biāo) DECLARE ProvinceCursor CURSOR FOR( SELECT Id AS ProvinceId, region.RegionCode,region.RegionName FROM dbo.Provinces AS province JOIN dbo.Regions AS region ON province.Name=SUBSTRING(region.RegionName,1, LEN(province.Name)) AND region.RegionType=1 ); -- 打開(kāi)游標(biāo) OPEN ProvinceCursor; -- 移動(dòng)游標(biāo)，加載數(shù)據(jù) FETCH NEXT FROM ProvinceCursor INTO @ProvinceId,@RegionCode,@RegionName; -- 游標(biāo)加載數(shù)據(jù)成功WHILE @@FETCH_STATUS = 0 BEGIN--根據(jù)游標(biāo)數(shù)據(jù)進(jìn)行操作，這里只輸出要執(zhí)行的 SQL 腳本，也可以直接 UPDATE，看自己需要PRINT 'UPDATE dbo.Provinces SET Code = ' + CONVERT(NVARCHAR(12), @RegionCode)+', Name = N'''+@RegionName +''' WHERE Id = ' + CONVERT(NVARCHAR(12), @provinceId) +';';-- 移動(dòng)游標(biāo)到下一條數(shù)據(jù)FETCH NEXT FROM ProvinceCursorINTO @ProvinceId,@RegionCode,@RegionName; END; CLOSE ProvinceCursor; DEALLOCATE ProvinceCursor;

Another Sample

DECLARE @projectId nvarchar(36)-- 聲明變量 DECLARE My_Cursor CURSOR --定義游標(biāo) FOR (SELECT OriginalProjectId FROM dbo.CommunityProjects WHERE CommunityId = -1)--查出需要的集合放到游標(biāo)中 OPEN My_Cursor; --打開(kāi)游標(biāo) FETCH NEXT FROM My_Cursor INTO @projectId; WHILE @@FETCH_STATUS = 0BEGINUPDATE dbo.CommunityProjectsSET CommunityId = CAST(ISNULL((SELECT ZhongyiCommunityId FROM dbo.CommunityMappingsWHERE FangdiCommunityId = @projectId),'-1') AS INT)WHERE OriginalProjectId = @projectIdFETCH NEXT FROM My_Cursor INTO @projectId;END CLOSE My_Cursor; --關(guān)閉游標(biāo) DEALLOCATE My_Cursor; --釋放游標(biāo)

and more

DECLARE @RegionCode INT; DECLARE @RegionName NVARCHAR(64); DECLARE @provinceId INT; DECLARE ProvinceCursor CURSOR FOR( SELECT RegionCode,RegionName FROM dbo.Regions WHERE RegionType = 1); OPEN ProvinceCursor; FETCH NEXT FROM ProvinceCursor INTO @RegionCode,@RegionName; WHILE @@FETCH_STATUS = 0 BEGINSET @provinceId =ISNULL((SELECT Id FROM dbo.Provinces WHERE Name = @RegionName), 0);IF @provinceId > 0PRINT 'UPDATE dbo.Provinces SET Code = ' + CONVERT(NVARCHAR(12), @RegionCode)+' WHERE Id = ' + CONVERT(NVARCHAR(12), @provinceId) +';';ELSEPRINT 'INSERT INTO dbo.Provinces(Name,Code) VALUES(N''' + @RegionName + ''',' + CONVERT(NVARCHAR(12), @RegionCode)+ ');';FETCH NEXT FROM ProvinceCursorINTO @RegionCode,@RegionName; END; CLOSE ProvinceCursor; DEALLOCATE ProvinceCursor;

More

在做一些小數(shù)據(jù)量的數(shù)據(jù)操作時(shí)，游標(biāo)會(huì)非常方便，而且游標(biāo)比較靈活，你可以只生成更新數(shù)據(jù)的SQL，也可以打印出數(shù)據(jù)更新前后的值，以便錯(cuò)誤更新數(shù)據(jù)之后的數(shù)據(jù)恢復(fù)

Reference

• https://www.cnblogs.com/xielong/p/5941595.html

• https://www.cnblogs.com/mrma/p/3794520.html

• https://www.sqlservertutorial.net/sql-server-stored-procedures/sql-server-cursor/

• https://www.mssqltips.com/sqlservertip/1599/sql-server-cursor-example/

總結(jié)

以上是生活随笔為你收集整理的SqlServer 利用游标批量更新数据的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。