如何在使用游标时避免在T-SQL中重复获取?

In T-SQL, when iterating results from a cursor, it seems to be common practice to repeat the FETCH statement before the WHILE loop. The below example from Microsoft:

在T-SQL中，当迭代游标的结果时，通常的做法是在WHILE循环之前重复FETCH语句。以下来自微软的例子:

DECLARE Employee_Cursor CURSOR FOR
SELECT EmployeeID, Title FROM AdventureWorks2012.HumanResources.Employee
    WHERE JobTitle = 'Marketing Specialist';
OPEN Employee_Cursor;

FETCH NEXT FROM Employee_Cursor;
WHILE @@FETCH_STATUS = 0
    BEGIN
        FETCH NEXT FROM Employee_Cursor;
    END;
CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor;
GO

(Notice how FETCH NEXT FROM Employee_Cursor; appears twice.)

(注意如何从Employee_Cursor获取NEXT;出现两次)。

If the FETCH selects into a long list of variables, then we have a large duplicated statement which is both ugly and of course, "non-DRY" code.

如果FETCH选择了一个长长的变量列表，那么我们就有了一个很大的重复语句，它既丑陋又当然，“非dry”代码。

I'm not aware of a post-condition control-of-flow T-SQL statement so it seems I'd have to resort to a WHILE(TRUE) and then BREAK when @@FETCH_STATUS is not zero. This feels clunky to me.

我不知道后条件控制流的T-SQL语句，所以我似乎必须求助于一段时间(TRUE)，然后在@ fetch_status不为零时中断。这让我觉得很笨拙。

What other options do I have?

我还有别的选择吗?

5 个解决方案

#1

-3

Simply said you can't... that's just how most where statements in SQL work. You need to get the first line before the loop and then do it again in the while statement.

只是说你不能…这就是SQL语句中大多数where语句的工作方式。您需要在循环之前获得第一行，然后在while语句中再做一次。

The better question how to get rid of the cursor and try to solve your query without it.

更好的问题是如何删除游标并尝试在不使用游标的情况下解决查询。

#2

There's a good structure posted online by Chris Oldwood which does it quite elegantly:

克里斯·奥尔德伍德(Chris Oldwood)在网上发布了一个很好的结构，它做得很优雅:

DECLARE @done bit = 0 

WHILE (@done = 0)  
BEGIN 
  -- Get the next author.  
  FETCH NEXT FROM authors_cursor  
  INTO @au_id, @au_fname, @au_lname  

  IF (@@FETCH_STATUS <> 0) 
  BEGIN 
    SET @done = 1 
    CONTINUE 
  END 

  -- 
  -- stuff done here with inner cursor elided 
  -- 
END

#3

This is what I've resorted to (oh the shame of it):

这就是我所诉诸的(哦，这是多么可耻):

WHILE (1=1)
BEGIN
    FETCH NEXT FROM C1 INTO
   @foo,
   @bar,
   @bufar,
   @fubar,
   @bah,
   @fu,
   @foobar,
   @another,
   @column,
   @in,
   @the,
   @long,
   @list,
   @of,
   @variables,
   @used,
   @to,
   @retrieve,
   @all,
   @values,
   @for,
   @conversion

    IF (@@FETCH_STATUS <> 0)
    BEGIN
        BREAK
    END

     -- Use the variables here
END

CLOSE C1
DEALLOCATE C1

You can see why I posted a question. I don't like how the control of flow is hidden in an if statement when it should be in the while.

你能明白我为什么贴出一个问题。我不喜欢在if语句中隐藏流的控制，当它应该在while时。

#4

The first Fetch shouldn't be a Fetch next, just a fetch.

第一个取回不应该是下一个取回，而应该是一个取回。

Then you're not repeating yourself.

这样你就不会重复你自己了。

I'd spend more effort getting rid of the cursor, and less on DRY dogma, (but if it really matters, you could use a GOTO :) - Sorry, M. Dijkstra)

我要花更多的精力去除光标，少花点精力在干的教条上(但如果真的重要，你可以用GOTO:) -对不起，M. Dijkstra算法)

GOTO Dry
WHILE @@FETCH_STATUS = 0 
BEGIN 
    --- stuff here

Dry:
    FETCH NEXT FROM Employee_Cursor; 
END;

#5

-2

It is obvious that a cursor is the pointer to the current row in the recordset. But mere pointing isn't gonna make sense unless it can be used. Here comes the Fetch statement into the scene. This takes data from the recordset, stores it in the variable(s) provided. so if you remove the first fetch statement the while loop won't work as there is not "FETCHED" record for manipulation, if you remove the last fetch statement, the "while" will not loop-through.

很明显，游标是记录集中当前行的指针。但是仅仅指出是没有意义的，除非它可以被使用。Fetch语句进入了场景。它从记录集中获取数据，并将其存储在所提供的变量中。因此，如果您删除了第一个fetch语句，while循环将无法工作，因为操作没有“获取”记录，如果您删除了最后一个fetch语句，那么“while”将不会循环。

So it is necessary to have both the fetch statement to loop-through the complete recordset.

因此，有必要让fetch语句同时循环遍历完整的记录集。

#1

-3