sql 存储过程里面如何 对数据集循环
答案:2 悬赏:60
解决时间 2021-03-06 22:47
- 提问者网友:但未伤到心
- 2021-03-06 01:49
sql 存储过程里面如何 对数据集循环
最佳答案
- 二级知识专家网友:木子香沫兮
- 2021-03-06 03:20
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
以下示例为 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
全部回答
- 1楼网友:输掉的尊严
- 2021-03-06 03:38
你就不能转换成list再用foreach吗?listlist=n.tolist()
我要举报
如以上问答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息,可以点下面链接进行举报!
大家都在看
推荐资讯