есть ли способ перебрать табличную переменную в sql без использования курсора?

<p>допустим, у меня есть следующая простая табличная переменная:</p><pre><code>declare @databases table ( databaseid int, name varchar(15), server varchar(15) ) -- insert a bunch rows into @databases </code></pre><p>является ли объявление и использование курсора моим единственным вариантом, если я хочу перебирать строки? есть ли другой способ?</p>

<p>прежде всего, вы должны быть абсолютно уверены, что вам нужно перебирать каждую строку - операции на основе набора будут выполняться быстрее в каждом случае, о котором я могу подумать, и, как правило, использовать более простой код.</p><p>в зависимости от ваших данных можно выполнить цикл, используя операторы select, как показано ниже</p><pre><code>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 </code></pre><p>другой альтернативой является использование временной таблицы:</p><pre><code>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 </code></pre><p>выбор, который вы должны выбрать, действительно зависит от структуры и объема ваших данных.</p><p><strong> примечание: </strong> если вы используете sql server, вам лучше использовать:</p><pre><code>while exists(select * from #temp) </code></pre><p>при использовании <code>count</code> нужно будет коснуться каждой строки в таблице, а <code>exists</code> - только первой (см. <a href="https://stackoverflow.com/a/65294/963542">ответ йозефа</a> ниже).</p>

<p>просто быстрое примечание, если вы используете sql server (2008 и выше), примеры, которые имеют:</p><pre><code>while (select count(*) from #temp) > 0 </code></pre><p>будет лучше обслужен с</p><pre><code>while exists(select * from #temp) </code></pre><p>счетчик должен будет касаться каждой строки в таблице, а <code>exists</code> - только первой.</p>

<p>вот как я это делаю:</p><pre><code>declare @rownum int, @custid nchar(5), @name1 nchar(25) select @custid=max(userid) from userids --start with the highest id select @rownum = count(*) from userids --get total number of records while @rownum > 0 --loop until no more records begin select @name1 = username1 from userids where userid= @custid --get other info from that row print cast(@rownum as char(12)) + ' ' + @custid + ' ' + @name1 --do whatever select top 1 @custid=userid from userids where userid < @custid order by userid desc--get the next one set @rownum = @rownum - 1 --decrease count end </code></pre><p>нет курсоров, нет временных таблиц, нет лишних столбцов. столбец userid должен быть уникальным целым числом, как и большинство первичных ключей.</p>

<p>определите свою временную таблицу следующим образом:</p><pre><code>declare @databases table ( rowid int not null identity(1,1) primary key, databaseid int, name varchar(15), server varchar(15) ) -- insert a bunch rows into @databases </code></pre><p>тогда сделай это -</p><pre><code>declare @i int select @i = min(rowid) from @databases declare @max int select @max = max(rowid) from @databases while @i <= @max begin select databaseid, name, server from @database where rowid = @i --do some stuff set @i = @i + 1 end </code></pre>

<p>вот как я бы это сделал:</p><pre><code>select identity(int, 1,1) as pk, databaseid into #t from @databases declare @maxpk int;select @maxpk = max(pk) from #t declare @pk int;set @pk = 1 while @pk <= @maxpk begin -- get one record select databaseid, name, server from @databases where databaseid = (select databaseid from #t where pk = @pk) --do some processing here -- select @pk = @pk + 1 end </code></pre><hr><p>[править] поскольку я, вероятно, пропустил слово "переменная" при первом прочтении вопроса, вот обновленный ответ ...</p><hr><pre><code>declare @databases table ( pk int identity(1,1), databaseid int, name varchar(15), server varchar(15) ) -- insert a bunch rows into @databases --/* insert into @databases (databaseid, name, server) select 1,'maindb', 'myserver' insert into @databases (databaseid, name, server) select 1,'mydb', 'myserver2' --*/ declare @maxpk int;select @maxpk = max(pk) from @databases declare @pk int;set @pk = 1 while @pk <= @maxpk begin /* get one record (you can read the values into some variables) */ select databaseid, name, server from @databases where pk = @pk /* do some processing here */ /* ... */ select @pk = @pk + 1 end </code></pre>

<p>если у вас нет выбора, кроме как идти строка за строкой, создавая курсор fast\_forward. это будет так же быстро, как создание цикла, и его будет намного легче поддерживать в течение длительного времени.</p><p>fast\_forward указывает курсор forward\_only, read\_only с включенной оптимизацией производительности. fast\_forward не может быть указан, если также указаны scroll или for\_update.</p>

<p>другой подход без необходимости изменения схемы или использования временных таблиц:</p><pre><code>declare @rowcount int = 0 ,@currentrow int = 1 ,@databaseid int ,@name varchar(15) ,@server varchar(15); select @rowcount = count(*) from @databases; while (@currentrow <= @rowcount) begin select top 1 @databaseid = rt.[databaseid] ,@name = rt.[name] ,@server = rt.[server] from ( select row\_number() over ( order by t.[databaseid], t.[name], t.[server] ) as [rownumber] ,t.[databaseid] ,t.[name] ,t.[server] from @databases t ) rt where rt.[rownumber] = @currentrow; exec [your\_stored\_procedure] @databaseid, @name, @server; set @currentrow = @currentrow + 1; end </code></pre>

<p>легкий, без необходимости создавать дополнительные таблицы, если у вас есть целое число <code>id</code> в таблице</p><pre><code>declare @id int = 0, @anything nvarchar(max) while(1=1) begin select top 1 @anything=[anything],@id=@id+1 from table where id>@id if(@@rowcount=0) break; --process @anything end </code></pre>

<p>вы можете использовать цикл while:</p><pre><code>while (select count(*) from #temptable) > 0 begin insert into @databases... delete from #temptable where x = x end </code></pre>

<pre><code>-- [po\_rollbackonreject] 'fininv10532' alter procedure po\_rollbackonreject @caseid nvarchar(100) as begin select * into #tmptable from po\_invoiceitems where caseid = @caseid declare @id int declare @po\_no int declare @current\_balance money while (select row\_number() over(order by po\_lineno desc) from #tmptable) > 0 begin select top 1 @id = po\_lineno, @current\_balance = current\_balance, @po\_no = po\_no from #temp update po\_details set current\_balance = current\_balance + @current\_balance, previous\_app\_amount= previous\_app\_amount + @current\_balance, is\_processed = 0 where po\_linenumber = @id and po\_no = @po\_no update po\_invoiceitems set isvisible = 0, is\_processed= 0 ,is\_inprogress = 0 , is\_active = 0 where po\_lineno = @id and po\_no = @po\_no end end </code></pre>

<p>я действительно не вижу смысла, почему вы должны прибегнуть к использованию страшных <code>cursor</code>. но вот еще один вариант, если вы используете sql server версии 2005/2008<br> используйте <strong>рекурсия</strong></p><pre><code>declare @databases table ( databaseid int, name varchar(15), server varchar(15) ) --; insert records into @databases... --; recurse through @databases ;with dbs as ( select * from @databases where databaseid = 1 union all select a.* from @databases a inner join dbs b on a.databaseid = b.databaseid + 1 ) select * from dbs </code></pre>

<p>это будет работать в версии sql server 2012.</p><pre><code>declare @rowcount int select @rowcount=count(*) from addresstable; while( @rowcount>0) begin select @rowcount=@rowcount-1; select * from addresstable order by addressid desc offset @rowcount rows fetch next 1 rows only; end </code></pre>

<p>я собираюсь предоставить решение на основе множеств.</p><pre><code>insert @databases (databaseid, name, server) select databaseid, name, server from ... (use whatever query you would have used in the loop or cursor) </code></pre><p>это намного быстрее, чем любой метод зацикливания, и его легче писать и поддерживать.</p>

<p>этот подход требует только одну переменную и не удаляет строки из @databases. я знаю, что здесь есть много ответов, но я не вижу такого, который бы использовал min для получения следующего идентификатора.</p><pre><code>declare @databases table ( databaseid int, name varchar(15), server varchar(15) ) -- insert a bunch rows into @databases declare @currid int select @currid = min(databaseid) from @databases while @currid is not null begin -- do stuff for @currid select @currid = min(databaseid) from @databases where databaseid > @currid end </code></pre>

<p>для этого можно использовать курсор:</p><p>функция create [dbo] .f\_teste\_loop возвращает таблицу @tabela (cod int, nome varchar (10)) как начало</p><pre><code>insert into @tabela values (1, 'verde'); insert into @tabela values (2, 'amarelo'); insert into @tabela values (3, 'azul'); insert into @tabela values (4, 'branco'); return; </code></pre><p>конец</p><p>создайте процедуру [dbo]. [sp\_teste\_loop] как начало</p><pre><code>declare @cod int, @nome varchar(10); declare curloop cursor static local for select cod ,nome from dbo.f\_teste\_loop(); open curloop; fetch next from curloop into @cod, @nome; while (@@fetch\_status = 0) begin print @nome; fetch next from curloop into @cod, @nome; end close curloop; deallocate curloop; </code></pre><p>конец</p>

<p>вот мое решение, которое использует бесконечный цикл, оператор <code>break</code> и функцию <code>@@rowcount</code>. курсоры или временная таблица не нужны, и мне нужно написать только один запрос, чтобы получить следующую строку в таблице <code>@databases</code>:</p><pre><code>declare @databases table ( databaseid int, [name] varchar(15), [server] varchar(15) ); -- populate the [@databases] table with test data. insert into @databases (databaseid, [name], [server]) select x.databaseid, x.[name], x.[server] from (values (1, 'roger', 'servera'), (5, 'suzy', 'serverb'), (8675309, 'jenny', 'tommytutone') ) x (databaseid, [name], [server]) -- create an infinite loop & ensure that a break condition is reached in the loop code. declare @databaseid int; while (1=1) begin -- get the next database id. select top(1) @databaseid = databaseid from @databases where databaseid > isnull(@databaseid, 0); -- if no rows were found by the preceding sql query, you're done; exit the while loop. if (@@rowcount = 0) break; -- otherwise, do whatever you need to do with the current [@databases] table row here. print 'processing @databaseid #' + cast(@databaseid as varchar(50)); end </code></pre>

<p>я согласен с предыдущим постом, что основанные на множестве операции, как правило, будут работать лучше, но если вам нужно перебирать строки, вот подход, который я бы выбрал:</p><ol> <li>добавить новое поле в табличную переменную (тип данных бит, по умолчанию 0)</li> <li>вставьте ваши данные</li> <li>выберите строку top 1, где fused = 0 <em>(примечание: fused - это имя поля в шаге 1)</em></li> <li>выполните любую обработку, которую вам нужно сделать</li> <li>обновите запись в вашей табличной переменной, установив fused = 1 для записи</li> <li> <p>выберите следующую неиспользуемую запись из таблицы и повторите процесс</p> <pre><code>declare @databases table ( databaseid int, name varchar(15), server varchar(15), fused bit default 0 ) -- insert a bunch rows into @databases declare @dbid int select top 1 @dbid = databaseid from @databases where fused = 0 while @@rowcount <> 0 and @dbid is not null begin -- perform your processing here --update the record to "used" update @databases set fused = 1 where databaseid = @dbid --get the next record select top 1 @dbid = databaseid from @databases where fused = 0 end </code></pre></li> </ol>

<p>я предпочитаю использовать offset fetch, если у вас есть уникальный идентификатор, который вы можете отсортировать по таблице:</p><pre><code>declare @tablevariable (id int, name varchar(50)); declare @recordcount int; select @recordcount = count(*) from @tablevariable; while @recordcount > 0 begin select id, name from @tablevariable order by id offset @recordcount - 1 fetch next 1 row; set @recordcount = @recordcount - 1; end </code></pre><p>таким образом, мне не нужно добавлять поля в таблицу или использовать оконную функцию.</p>

<p>шаг 1: оператор select ниже создает временную таблицу с уникальным номером строки для каждой записи.</p><pre><code>select eno,ename,eaddress,mobno int,row\_number() over(order by eno desc) as rno into #tmp\_sri from emp </code></pre><p>шаг 2: объявите обязательные переменные</p><pre><code>declare @rownumber int declare @ename varchar(100) </code></pre><p>шаг 3: взять общее количество строк из временной таблицы</p><pre><code>select @rownumber = count(*) from #tmp\_sri declare @rno int </code></pre><p>шаг 4: создание таблицы временных циклов на основе уникального номера строки, созданного в temp</p><pre><code>while @rownumber>0 begin set @rno=@rownumber select @ename=ename from #tmp\_sri where rno=@rno **// you can take columns data from here as many as you want** set @rownumber=@rownumber-1 print @ename **// instead of printing, you can write insert, update, delete statements** end </code></pre>

<p>это код, который я использую 2008 r2. этот код, который я использую, предназначен для построения индексов по ключевым полям (ssno & empr\_no) во всех сказках.</p><pre><code>if object\_id('tempdb..#a')is not null drop table #a select 'if exists (select name from sysindexes where name ='+char(39)+''+'idx\_'+column\_name+'\_'+substring(table\_name,5,len(table\_name)-3)+char(39)+')' +' begin drop index [idx\_'+column\_name+'\_'+substring(table\_name,5,len(table\_name)-3)+'] on '+table\_schema+'.'+table\_name+' end create index idx\_'+column\_name+'\_'+substring(table\_name,5,len(table\_name)-3)+ ' on '+ table\_schema+'.'+table\_name+' ('+column\_name+') ' 'field' ,row\_number() over (order by table\_name) as 'rownmbr' into #a from information\_schema.columns where (column\_name like '%\_ssno\_%' or column\_name like'%\_empr\_no\_') and table\_schema='dbo' declare @loopcntr int declare @row int declare @string nvarchar(1000) set @loopcntr=(select count(*) from #a) set @row=1 while (@row <= @loopcntr) begin select top 1 @string=a.field from #a a where a.rownmbr = @row execute sp\_executesql @string set @row = @row + 1 end </code></pre>

<p>лучше выберите @pk = @pk + 1: set @pk + = @pk. избегайте использования select, если вы не ссылаетесь на таблицы, которые просто присваивают значения.</p>