<rp id="wnpn7"><ruby id="wnpn7"></ruby></rp>
<progress id="wnpn7"><track id="wnpn7"><rt id="wnpn7"></rt></track></progress>
<ruby id="wnpn7"></ruby>
<ruby id="wnpn7"><blockquote id="wnpn7"><div id="wnpn7"></div></blockquote></ruby>

    1. <em id="wnpn7"><ruby id="wnpn7"><input id="wnpn7"></input></ruby></em>
      1. <button id="wnpn7"><acronym id="wnpn7"></acronym></button><button id="wnpn7"><acronym id="wnpn7"></acronym></button>

        <rp id="wnpn7"><acronym id="wnpn7"></acronym></rp>

        <li id="wnpn7"><object id="wnpn7"><u id="wnpn7"></u></object></li>
        VB.net 2010 視頻教程 VB.net 2010 視頻教程 python基礎視頻教程
        SQL Server 2008 視頻教程 c#入門經典教程 Visual Basic從門到精通視頻教程
        當前位置:
        首頁 > 數據庫 > SQL教程 >
        • sql教程之SqlServer 利用游標批量更新數據

        本站最新發布    SQL Server 2016數據庫視頻教程

        試聽地址   http://www.squ68.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


        相關教程
                
        免费看成年人视频大全_免费看成年人视频在线观看