有办法重构这个光标吗？

Question

因此，我从另一位分析师那里得到了这个查询，我有点困惑于为什么使用游标而不是仅仅连接。能帮我把它改造一下吗？我正在使用MSSQL 2008。

SET NOCOUNT ON;

DECLARE @myCur AS CURSOR;
DECLARE @totalCdes AS INT;
DECLARE @SysOjb AS SMALLINT;
DECLARE @prinOjb AS SMALLINT;
DECLARE @JobClass AS VARCHAR (25);
DECLARE @JobNo AS INT;
DECLARE @JobType AS VARCHAR (2);
DECLARE @JobDescr AS VARCHAR (30);
DECLARE @JobComplCde AS VARCHAR (18);
DECLARE @OrderNo AS VARCHAR (16);
DECLARE @SchedDate AS DATETIME;
DECLARE @JobResolution AS VARCHAR (200);
DECLARE @startingRow AS INT;

CREATE TABLE #JobsFixCodes
(
    SYS_OJB        BIGINT       ,
    PRIN_OJB       SMALLINT     ,
    JOB_NO         INT          ,
    ORDER_NO       BIGINT       ,
    JOB_CLASS      VARCHAR (25) ,
    JOB_TYPE       VARCHAR (30) ,
    JOB_RESOLUTION VARCHAR (200),
    SCHED_DATE     DATE         
);

SET @myCur = CURSOR
    FOR SELECT [SYS_OJB],
            [PRIN_OJB],
            CASE [JOB_CLASS_OJB] 
WHEN 'C' THEN 'New Connect' 
WHEN 'D' THEN 'Disco' 
WHEN 'R' THEN 'Restart' 
WHEN 'S' THEN 'Service Change' 
WHEN 'T' THEN 'Trouble Call' 
WHEN 'Z' THEN 'Special Request' ELSE 'Unknown' 
END AS JobClass,
            [JOB_NO_OJB],
            [JOB_TYP_OJB],
            COALESCE (cagent.DESCR_CTD, cprin.DESCR_CTD, csys.DESCR_CTD, 'Unknown') AS Job_Descr,
            COMPL_CDE_OJB,
            [ORDER_NO_OJB],
            [SCHED_DTE_OJB]
        FROM   [ExternalUser].[Vantage].[OJB_JOBS] AS j
            LEFT OUTER JOIN
            [ExternalUser].[Vantage].[CTD_DISPLAY] AS cagent
            ON cagent.SYS_CTD = j.SYS_OJB
                AND cagent.PRIN_CTD = j.PRIN_OJB
                AND cagent.AGNT_CTD = j.AGNT_OJB
                AND cagent.CDE_TBL_NO_CTD = '32'
                AND cagent.CDE_VALUE_CTD = j.JOB_TYP_OJB
                AND cagent.SPA_FLG_CTD = 'A'
            LEFT OUTER JOIN
            [ExternalUser].[Vantage].[CTD_DISPLAY] AS cprin
            ON cprin.SYS_CTD = j.SYS_OJB
                AND cprin.PRIN_CTD = j.PRIN_OJB
                AND cprin.CDE_TBL_NO_CTD = '32'
                AND cprin.CDE_VALUE_CTD = j.JOB_TYP_OJB
                AND cprin.SPA_FLG_CTD = 'P'
            LEFT OUTER JOIN
            [ExternalUser].[Vantage].[CTD_DISPLAY] AS csys
            ON csys.SYS_CTD = j.SYS_OJB
                AND csys.CDE_TBL_NO_CTD = '32'
                AND csys.CDE_VALUE_CTD = j.JOB_TYP_OJB
                AND csys.SPA_FLG_CTD = 'S'
        WHERE  JOB_STAT_OJB = 'C'
            AND SYS_OJB = '8155'
            --and SCHED_DTE_OJB = @DateParm
            AND SCHED_DTE_OJB = CONVERT (VARCHAR (20), GETDATE()-3, 101); /*Use this to run 3 days in arrears*/
            --and JOB_CLASS_OJB in (@JobClassParm) **Keep this commented out if you want *all* job classes
OPEN @myCur;

FETCH NEXT FROM @myCur INTO @SysOjb, @prinOjb, @JobClass, @JobNo, @JobType, @JobDescr, @JobComplCde, @OrderNo, @SchedDate;

WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @totalCdes = len(@JobComplCde);
        --if @totalCdes is = 0 then don't loop
        IF @totalCdes > 0
            BEGIN
                IF @totalCdes = 3
                OR @totalCdes = 6
                OR @totalCdes = 9
                OR @totalCdes = 12
                OR @totalCdes = 15
                OR @totalCdes = 18
                    SET @totalCdes = @totalCdes / 3; --this is the true loop count
                ELSE
                    SET @totalCdes = @totalCdes / 2;
            END
        ELSE
            GOTO QuitMe;
        -- print 'Total job resolutions for order (: ' + convert(varchar(16), @OrderNo) + '): ' + convert(varchar(10), @totalCdes)
        IF @JobClass = 'Service Change'
        OR @JobClass = 'Special Request'
            WHILE @totalCdes > 0
                BEGIN
                    SET @startingRow = (@totalCdes * 3) - 2;
                    -- print 'Service Change in prin ' + convert(varchar(4),@prinOjb) + ' found with order: ' + convert(varchar(16), @OrderNo) + ' with ' + convert(varchar(16), @totalCdes) + ' resolutions of ' + @JobComplCde
                    -- print 'starting row is ' + convert(varchar(4),@startingRow)
                    SET @JobResolution = (SELECT descr_ctd
                                        FROM   [ExternalUser].[Vantage].[CTD_DISPLAY]
                                        WHERE  CDE_TBL_NO_CTD = '19'
                                                AND CDE_VALUE_CTD = SUBSTRING(@JobComplCde, @startingRow, 3)
                                                AND SYS_CTD = '8155');
                    INSERT  INTO #JobsFixCodes
                    VALUES (@SysOjb, @prinOjb, @JobNo, @OrderNo, @JobClass, @JobDescr, @JobResolution, @SchedDate);
                    SET @totalCdes = @totalCdes - 1;
                END
        IF @JobClass = 'Trouble Call'
            WHILE @totalCdes > 0
                BEGIN
                    SET @startingRow = (@totalCdes * 3) - 2;
                    -- print 'Service Change in prin ' + convert(varchar(4),@prinOjb) + ' found with order: ' + convert(varchar(16), @OrderNo) + ' with ' + convert(varchar(16), @totalCdes) + ' resolutions of ' + @JobComplCde
                    -- print 'starting row is ' + convert(varchar(4),@startingRow)
                    SET @JobResolution = (SELECT descr_ctd
                                        FROM   [ExternalUser].[Vantage].[CTD_DISPLAY]
                                        WHERE  CDE_TBL_NO_CTD = '08'
                                                AND CDE_VALUE_CTD = SUBSTRING(@JobComplCde, @startingRow, 3)
                                                AND SYS_CTD = '8155');
                    INSERT  INTO #JobsFixCodes
                    VALUES (@SysOjb, @prinOjb, @JobNo, @OrderNo, @JobClass, @JobDescr, @JobResolution, @SchedDate);
                    SET @totalCdes = @totalCdes - 1;
                END
        QuitMe:
        FETCH NEXT FROM @myCur INTO @SysOjb, @prinOjb, @JobClass, @JobNo, @JobType, @JobDescr, @JobComplCde, @OrderNo, @SchedDate;
    END

CLOSE @myCur;

DEALLOCATE @myCur;

SELECT   *
FROM     #JobsFixCodes AS f
ORDER BY f.JOB_CLASS, f.ORDER_NO, f.SCHED_DATE;

DROP TABLE #JobsFixCodes;

Answer 1

将复杂的游标过程重构为适当的SQL查询是很困难的。这就是为什么这里有这么多人不愿意去想这件事。看一看本系列文章(必须有15种方法使你失去游标.)，它显式地帮助开发人员将游标重构为良好的SQL查询。可悲的是，这位臭名昭著的固执己见的作者从未完成过这个系列，但这两篇文章应该是帮助您开始重构过程的完美方法。

Answer 2

回到黑暗时代，当我还是SQL 2000的新手时，我有一位导师指导我学习游标。因此，我创建了一个模板，它将为一个简单的游标尽可能快地运行。此模板旨在尽可能快地运行循环过程。这个简单的光标在速度上与新的形式相媲美。

DECLARE @Name1 VARCHAR(50), @Name2 VARCHAR(50), @Name3 VARCHAR(50)
DECLARE CurName CURSOR FAST_FORWARD READ_ONLY FOR
SELECT statement

OPEN CurName
FETCH NEXT FROM CurName INTO @Name1, @Name2, @Name3
WHILE @@FETCH_STATUS = 0 BEGIN

    -- Do Something

    FETCH NEXT FROM CurName INTO @Name1, @Name2, @Name3
END
CLOSE CurName
DEALLOCATE CurName

使它快速的部分是"FAST_FORWARD READ_ONLY“部分。它只适用于简单的风格。(我从不在光标中更新。太慢了。)