游标 简单示例

CREATE PROCEDURE [dbo].[Test_TableCursor]

AS

DECLARE @Id int
DECLARE @ProductName nvarchar(50)
DECLARE @Message nvarchar(100)
DECLARE @Cmd nvarchar(400)

DECLARE TableCursor CURSOR FOR
SELECT [Id],[Name] FROM Data_Product

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @Id,@ProductName

WHILE @@FETCH_STATUS = 0
BEGIN
IF @@FETCH_STATUS = -2
CONTINUE

SELECT @Message = 'Get ' + Convert(nvarchar(50),@Id) + ' ' + @ProductName + '…'
PRINT @Message

SELECT @cmd = 'UPDATE Data_Product SET [Name] = '''+@ProductName +'1'' where [Id]= ' + Convert(nvarchar(20),@Id)
EXEC (@cmd)
PRINT ' '

FETCH NEXT FROM TableCursor INTO @Id,@ProductName
END

DEALLOCATE TableCursor

==================================================================================

@@FETCH_STATUS

返回值 说明

0 FETCH 语句成功。

-1 FETCH 语句失败或行不在结果集中。

-2 提取的行不存在。
==================================================================================

Oracle 版本

DECLARE CURSOR c IS SELECT id,name FROM data_news;

p_id number;
p_Name VARCHAR2(40);

BEGIN

OPEN c;

FETCH c INTO p_id,p_NAME;
WHILE c%FOUND LOOP
DBMS_OUTPUT.put_line(to_char(p_id)||p_NAME);
FETCH c INTO p_id,p_NAME;
END LOOP;

CLOSE c;

END;

==================================================================================

或者

LOOP
FETCH c INTO p_id,p_Name;
EXIT WHEN c%NotFOUND ;
DBMS_OUTPUT.put_line(to_char(p_id)||p_Name);
END LOOP;

CLOSE c;

==================================================================================