遊標和遞迴sql 的一些代碼

標籤：rom   etc   asc   for   order   inf   推廣   fir   tiny   

DECLARE @UserID INT; --推廣員帳號DECLARE @ProxyID INT; --代理帳號DECLARE @Score INT=1000; --分數SELECT    @UserID = [SpreaderID]FROM    [QPAccountsDB].[dbo].[AccountsInfo]WHERE    UserID = 5055;SELECT --查出推廣員的代理帳號        @ProxyID = ProxyIDFROM        [QPAccountsDB].[dbo].[AccountsInfo]    LEFT JOIN        [QPProxyDB].[dbo].[BS_ProxyInfo]            ON BS_ProxyInfo.account = AccountsInfo.AccountsWHERE        UserID = @UserID;PRINT @ProxyID;CREATE TABLE #ProxyInfo    (        belongsAgent     INT,        assignProportion TINYINT    );WITH cteAS (   SELECT           belongsAgent       FROM           [QPProxyDB].[dbo].[BS_ProxyInfo]       WHERE           ProxyID = @ProxyID           AND belongsAgent <> -1       UNION ALL       SELECT               a.belongsAgent       FROM               [QPProxyDB].[dbo].[BS_ProxyInfo] a           JOIN               cte                              b                   ON a.ProxyID = b.belongsAgent       WHERE               a.belongsAgent <> -1)INSERT #ProxyInfo    (        belongsAgent,        assignProportion    )       SELECT           BS_ProxyInfo.ProxyID,           assignProportion       FROM           cte LEFT JOIN [QPProxyDB].[dbo].[BS_ProxyInfo] ON BS_ProxyInfo.ProxyID = cte.belongsAgent       ORDER BY           BS_ProxyInfo.belongsAgent ASC;---遊標更新刪除當前資料---1.聲明遊標DECLARE cursor01 CURSOR SCROLL FOR    SELECT        *    FROM        #ProxyInfo    ORDER BY        belongsAgent ASC;        DECLARE @AllTax INT         SET @AllTax =@Score--2.開啟遊標OPEN cursor01;--3.聲明遊標提取資料所要存放的變數DECLARE    @belongsAgent     INT,    @assignProportion TINYINT;--4.定位遊標到哪一行FETCH FIRST FROM cursor01INTO    @belongsAgent,    @assignProportion; --into的變數數量必須與遊標查詢結果集的列數相同WHILE @@fetch_status = 0 --提取成功，進行下一條資料的提取操作     BEGIN      SET   @AllTax=@assignProportion*@AllTax/100UPDATE [QPProxyDB].[dbo].[BS_ProxyInfo] SET allTax+=@AllTax WHERE ProxyID=@belongsAgent        FETCH NEXT FROM cursor01        INTO            @belongsAgent,            @assignProportion; --移動遊標    END;CLOSE cursor01;DEALLOCATE cursor01;DROP TABLE #ProxyInfo;

 

遊標和遞迴sql 的一些代碼