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
精彩评论