VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > 数据库 > SQL教程 >
  • sql教程之SqlServer 利用游标批量更新数据

本站最新发布    SQL Server 2016数据库视频教程

试听地址   https://www.xin3721.com/eschool/SQLxin3721/

SqlServer 利用游标批量更新数据

Intro#

游标在有时候会很有用,在更新一部分不多的数据时,可以很方便的更新数据,不需要再写一个小工具来做了,直接写 SQL 就可以了

Sample#

下面来看一个实际示例:


	
Copy
-- 声明字段变量 DECLARE @RegionCode INT; DECLARE @RegionName NVARCHAR(64); DECLARE @ProvinceId INT; -- 声明游标 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 ); -- 打开游标 OPEN ProvinceCursor; -- 移动游标,加载数据 FETCH NEXT FROM ProvinceCursor INTO @ProvinceId,@RegionCode,@RegionName; WHILE @@FETCH_STATUS = 0 BEGIN -- 根据游标数据进行操作,这里只输出要执行的 SQL 脚本,也可以直接 UPDATE,看自己需要 PRINT 'UPDATE dbo.Provinces SET Code = ' + CONVERT(NVARCHAR(12), @RegionCode)+', Name = N'''+@RegionName +''' WHERE Id = ' + CONVERT(NVARCHAR(12), @provinceId) +';'; -- 移动游标到下一条数据 FETCH NEXT FROM ProvinceCursor INTO @ProvinceId,@RegionCode,@RegionName; END; CLOSE ProvinceCursor; DEALLOCATE ProvinceCursor;

Another Sample#


	
Copy
DECLARE @projectId nvarchar(36) -- 声明变量 DECLARE My_Cursor CURSOR --定义游标 FOR (SELECT OriginalProjectId FROM dbo.CommunityProjects WHERE CommunityId = -1) --查出需要的集合放到游标中 OPEN My_Cursor; --打开游标 FETCH NEXT FROM My_Cursor INTO @projectId; WHILE @@FETCH_STATUS = 0 BEGIN UPDATE dbo.CommunityProjects SET CommunityId = CAST(ISNULL(( SELECT ZhongyiCommunityId FROM dbo.CommunityMappings WHERE FangdiCommunityId = @projectId ),'-1') AS INT) WHERE OriginalProjectId = @projectId FETCH NEXT FROM My_Cursor INTO @projectId; END CLOSE My_Cursor; --关闭游标 DEALLOCATE My_Cursor; --释放游标

and more


	
Copy
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 BEGIN SET @provinceId =ISNULL((SELECT Id FROM dbo.Provinces WHERE Name = @RegionName), 0); IF @provinceId > 0 PRINT 'UPDATE dbo.Provinces SET Code = ' + CONVERT(NVARCHAR(12), @RegionCode)+' WHERE Id = ' + CONVERT(NVARCHAR(12), @provinceId) +';'; ELSE PRINT 'INSERT INTO dbo.Provinces(Name,Code) VALUES(N''' + @RegionName + ''',' + CONVERT(NVARCHAR(12), @RegionCode)+ ');'; FETCH NEXT FROM ProvinceCursor INTO @RegionCode, @RegionName; END; CLOSE ProvinceCursor; DEALLOCATE ProvinceCursor;

More#

在做一些小数据量的数据操作时,游标会非常方便,而且游标比较灵活,你可以只生成更新数据的SQL,也可以打印出数据更新前后的值,以便错误更新数据之后的数据恢复

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/

作者: WeihanLi


相关教程