几个说明:
1,cursor的初始化,不能使用select,而是只能用set;
2,cursor不使用的使用,要close,然后deallocate;
3,n个cursor也会共享同一个@@FETCH_STATUS
下面是示例代码,相当规范:
- USE AdventureWorks
- -- 记录性能log
- DECLARE @beg DATETIME
- SET @beg = getdate()
- -- cursor定义
- DECLARE @cur CURSOR
- -- *** cursor赋值,只能使用set,不能使用select***
- set @cur = CURSOR FOR SELECT EmployeeID, Title
- FROM AdventureWorks.HumanResources.Employee
- WHERE EmployeeID <50
- -- 打开cursor,遍历
- OPEN @cur
- FETCH NEXT FROM @cur
- WHILE @@FETCH_STATUS = 0
- BEGIN
- FETCH NEXT FROM @cur
- END
- -- 关闭之后一定要释放资源
- CLOSE @cur
- DEALLOCATE @cur
- -- 输出性能log
- DECLARE @end DATETIME
- SET @end = getdate()
- print DATEDIFF(millisecond, @beg, @end)