有没有办法在不使用游标的情况下遍历 TSQL 中的表变量? [英] Is there a way to loop through a table variable in TSQL without using a cursor?

问题描述

假设我有以下简单的表变量:

Let's say I have the following simple table variable:

declare @databases table
(
    DatabaseID    int,
    Name        varchar(15),   
    Server      varchar(15)
)
-- insert a bunch rows into @databases

如果我想遍历行，声明和使用游标是我唯一的选择吗?还有别的办法吗?

Is declaring and using a cursor my only option if I wanted to iterate through the rows? Is there another way?

推荐答案

首先你应该绝对确定你需要遍历每一行——基于集合的操作在我能想到的任何情况下都会执行得更快，并且通常会使用更简单的代码.

First of all you should be absolutely sure you need to iterate through each row — set based operations will perform faster in every case I can think of and will normally use simpler code.

根据您的数据，可能只使用 SELECT 语句进行循环，如下所示:

Depending on your data it may be possible to loop using just SELECT statements as shown below:

Declare @Id int

While (Select Count(*) From ATable Where Processed = 0) > 0
Begin
    Select Top 1 @Id = Id From ATable Where Processed = 0

    --Do some processing here

    Update ATable Set Processed = 1 Where Id = @Id 

End

另一种选择是使用临时表:

Another alternative is to use a temporary table:

Select *
Into   #Temp
From   ATable

Declare @Id int

While (Select Count(*) From #Temp) > 0
Begin

    Select Top 1 @Id = Id From #Temp

    --Do some processing here

    Delete #Temp Where Id = @Id

End

您应该选择的选项实际上取决于您的数据的结构和数量.

The option you should choose really depends on the structure and volume of your data.

注意:如果您使用的是 SQL Server，则最好使用:

Note: If you are using SQL Server you would be better served using:

WHILE EXISTS(SELECT * FROM #Temp)

使用 COUNT 将不得不接触表中的每一行，EXISTS 只需要接触第一行(见 约瑟夫的回答如下).

Using COUNT will have to touch every single row in the table, the EXISTS only needs to touch the first one (see Josef's answer below).

这篇关于有没有办法在不使用游标的情况下遍历 TSQL 中的表变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！