sql游标?sql 游标 最后?sqlserver游标的用法?SQLSERVER游标及循环语句

你是否正在寻找关于sql游标的内容？让我把最直接的东西奉献给你：

A. 在简单的游标中使用 FETCH

以下示例为 Person.Contact 表中姓氏以字母 B 开头的行声明了一个简单的游标，并使用 FETCH NEXT 逐个提取这些行。FETCH 语句以单行结果集形式返回在 DECLARE CURSOR 中指定的列的值。

USE AdventureWorks GO DECLARE contact_cursor CURSOR FOR SELECT LastName FROM Person.Contact WHERE LastName LIKE 'B%' ORDER BY LastName OPEN contact_cursor -- Perform the first fetch. FETCH NEXT FROM contact_cursor -- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN -- This is executed as long as the previous fetch succeeds. FETCH NEXT FROM contact_cursor END CLOSE contact_cursor DEALLOCATE contact_cursor GO

B. 使用 FETCH 将值存入变量

以下示例与示例 A 相似，但 FETCH 语句的输出存储于局部变量而不是直接返回到客户端。PRINT 语句将变量组合成单一字符串并将其返回到客户端。

USE AdventureWorks GO -- Declare the variables to store the values returned by FETCH. DECLARE @LastName varchar(50), @FirstName varchar(50) DECLARE contact_cursor CURSOR FOR SELECT LastName, FirstName FROM Person.Contact WHERE LastName LIKE 'B%' ORDER BY LastName, FirstName OPEN contact_cursor -- Perform the first fetch and store the values in variables. -- Note: The variables are in the same order as the columns -- in the SELECT statement. FETCH NEXT FROM contact_cursor INTO @LastName, @FirstName -- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN -- Concatenate and display the current values in the variables. PRINT 'Contact Name: ' + @FirstName + ' ' + @LastName -- This is executed as long as the previous fetch succeeds. FETCH NEXT FROM contact_cursor INTO @LastName, @FirstName END CLOSE contact_cursor DEALLOCATE contact_cursor GO

C. 声明 SCROLL 游标并使用其他 FETCH 选项

以下示例创建一个 SCROLL 游标，使其通过 LAST、PRIOR、RELATIVE 和 ABSOLUTE 选项支持全部滚动功能，。

。

另外，再举一个简单的例子：

Declare @Id varchar(20)
Declare @Name varchar(20)
Declare Cur Cursor For
select substring(id,0,7) as id,name from temp1
Open Cur
Fetch next From Cur Into @Id,@Name
While @@fetch_status=0
Begin
Update temp Set [c3]=@Name where [id] like @Id+'%'
Fetch Next From Cur Into @Id,@Name
End
Close Cur
Deallocate Cur

简单的FOR循环等：

declare @i int
set @i=0
while @i<10
begin
set @i = @i+1
end

SQL SERVER不支持FOR循环

USE AdventureWorks GO -- Execute the SELECT statement alone to show the -- full result set that is used by the cursor. SELECT LastName, FirstName FROM Person.Contact ORDER BY LastName, FirstName -- Declare the cursor. DECLARE contact_cursor SCROLL CURSOR FOR SELECT LastName, FirstName FROM Person.Contact ORDER BY LastName, FirstName OPEN contact_cursor -- Fetch the last row in the cursor. FETCH LAST FROM contact_cursor -- Fetch the row immediately prior to the current row in the cursor. FETCH PRIOR FROM contact_cursor -- Fetch the second row in the cursor. FETCH ABSOLUTE 2 FROM contact_cursor -- Fetch the row that is three rows after the current row. FETCH RELATIVE 3 FROM contact_cursor -- Fetch the row that is two rows prior to the current row. FETCH RELATIVE -2 FROM contact_cursor CLOSE contact_cursor DEALLOCATE contact_cursor GO

以上就是关于sql游标的全部内容，相信你一定会非常满意。

本文来自电脑杂谈，转载请注明本文网址：
https://www.pc-fly.com/a/shenmilingyu/article-19119-1.html