开发者

Convert PLSQL Cursor FOR Loop Syntax to TSQL

I have some PLSQL code which loops through some logic:

开发者_StackOverflow中文版
 FOR I in cur1
 LOOP

    SELECT value1, value2
    FROM db1..table1 t1

 END LOOP;

Can anyone explain to me the syntax for doing this in TSQL?


This is a generic loop in a standar TSQL Cursor. But try to avoid Cursors when possible. They Have very bad performance.

DECLARE @somevariable VARIABLE_TYPE_HERE
DECLARE @sampleCursor CURSOR

SET @sampleCursor = CURSOR FOR
SELECT somefield... from bla bla bla...

OPEN @sampleCursor 
FETCH NEXT
FROM @sampleCursor INTO @somevariable 
WHILE @@FETCH_STATUS = 0
BEGIN

PRINT @somevariable 

FETCH NEXT
FROM @sampleCursor INTO @somevariable 

END
CLOSE @sampleCursor 
DEALLOCATE @sampleCursor 


There is no FOR in T-SQL. An example with WHILE:

DECLARE Employee_Cursor CURSOR FOR
SELECT EmployeeID, Title 
FROM AdventureWorks2008R2.HumanResources.Employee

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;

For more information: http://msdn.microsoft.com/en-us/library/ms178642.aspx

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜