遊標和遞迴sql 的一些程式碼

蘭博丶專屬發表於2018-08-20
遞迴SQL
DECLARE @UserID INT; --推廣員帳號
DECLARE @ProxyID INT; --代理帳號
DECLARE @Score INT=1000; --分數
SELECT
    @UserID = [SpreaderID]
FROM
    [QPAccountsDB].[dbo].[AccountsInfo]
WHERE
    UserID = 5055;
SELECT --查出推廣員的代理帳號
        @ProxyID = ProxyID
FROM
        [QPAccountsDB].[dbo].[AccountsInfo]
    LEFT JOIN
        [QPProxyDB].[dbo].[BS_ProxyInfo]
            ON BS_ProxyInfo.account = AccountsInfo.Accounts
WHERE
        UserID = @UserID;
PRINT @ProxyID;
CREATE TABLE #ProxyInfo
    (
        belongsAgent     INT,
        assignProportion TINYINT
    );
WITH cte
AS (   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 cursor01
INTO
    @belongsAgent,
    @assignProportion; --into的變數數量必須與遊標查詢結果集的列數相同
WHILE @@fetch_status = 0 --提取成功，進行下一條資料的提取操作 
    BEGIN

      SET   @AllTax=@assignProportion*@AllTax/100
UPDATE [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;

 

相關文章