tsql - T-SQL游标中的无限循环

Question

下面是 T-SQL 游标的代码。它在第一次迭代中工作正常，但是之后陷入 FETCH NEXT 语句和 IF NOT EXISTS 语句之间的无限循环(基本上它将插入第一条记录，但是之后游标将不会移动到下一条记录所以 IF NOT EXISTS 永远是错误的)。这是我第一次使用游标，所以希望有人能解释发生了什么/如何使它工作!

    DECLARE prod_cursor CURSOR FOR
    SELECT ProductCode 
    FROM CourseToProduct 
    WHERE CourseCode = @courseCode and (TerminationDate >= @expDate OR TerminationDate IS NULL)

    OPEN prod_cursor

    FETCH NEXT FROM prod_cursor
    INTO @productCode

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF NOT EXISTS
        (
        SELECT sNumber
        FROM AgentProductTraining
        WHERE @sNumber = sNumber and 
              @courseCode = CourseCode and 
              @productCode = ProductCode and 
              @dateTaken = DateTaken
        )
        BEGIN
            IF @sNumber IS NOT NULL
            BEGIN
                INSERT INTO AgentProductTraining
                            (
                             sNumber,
                             CourseCode,
                             ProductCode,
                             DateTaken,
                             DateExpired,
                             LastChangeOperator,
                             LastChangeDate
                            ) 
                VALUES      (
                             @sNumber,
                             @courseCode,
                             @productCode,
                             @dateTaken,
                             COALESCE(@expDate, 'NULL'),
                             @lastChangeOperator,
                             @lastChangeDate
                           )    
            END
        END
    END
    CLOSE prod_cursor;
    DEALLOCATE prod_cursor;

Answer 1

您必须在 while..end 中获取下一行，否则它将永远不会移动到下一条记录。像这样:

DECLARE prod_cursor CURSOR FOR
SELECT ProductCode 
FROM CourseToProduct 
WHERE CourseCode = @courseCode and (TerminationDate >= @expDate OR TerminationDate IS NULL)

OPEN prod_cursor

FETCH NEXT FROM prod_cursor
INTO @productCode

WHILE @@FETCH_STATUS = 0
BEGIN
    IF NOT EXISTS
    (
    SELECT SymetraNumber
    FROM AgentProductTraining
    WHERE @symetraNumber = SymetraNumber and 
          @courseCode = CourseCode and 
          @productCode = ProductCode and 
          @dateTaken = DateTaken
    )
    BEGIN
        IF @symetraNumber IS NOT NULL
        BEGIN
            INSERT INTO AgentProductTraining
                        (
                         sNumber,
                         CourseCode,
                         ProductCode,
                         DateTaken,
                         DateExpired,
                         LastChangeOperator,
                         LastChangeDate
                        ) 
            VALUES      (
                         @sNumber,
                         @courseCode,
                         @productCode,
                         @dateTaken,
                         COALESCE(@expDate, 'NULL'),
                         @lastChangeOperator,
                         @lastChangeDate
                       )    
        END
    END

    FETCH NEXT FROM prod_cursor
    INTO @productCode
END
CLOSE prod_cursor;
DEALLOCATE prod_cursor;