sql游标循环结果集

我们知道游标是一种对结果集操作的神器，使用游标，可以很方便的循环结果集，并对结果集进行数据处理。

1、建表

CREATE TABLE [dbo].[Student](
    [Uid] [INT] IDENTITY(1,1) NOT NULL,
    [Name] [NCHAR](10) NULL,
    [BirthDay] [DATETIME] NULL,
    [Sex] [INT] NULL,
    [Age] [INT] NULL,
    [demo] [INT] NULL,
 CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED 
(
    [Uid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

2、sql代码实例：

BEGIN
    DECLARE @a INT ,
        @error INT;    
    DECLARE @temp VARCHAR(50);
    SET @a = 2;
    SET @error = 0;

   --定义一个游标
    DECLARE order_cursor CURSOR
   --申明游标为Uid
    FOR
        ( SELECT    [Uid]
          FROM      Student
        );
    --打开游标--
    OPEN order_cursor;
    --开始循环游标变量--
    FETCH NEXT FROM order_cursor INTO @temp;
    WHILE @@FETCH_STATUS = 0    --返回被 FETCH语句执行的最后游标的状态--
        BEGIN            
            UPDATE  Student
            SET     Age = 15 + @a ,
                    demo = @a
            WHERE   Uid = @temp;
            SET @a = @a + 1;
            SET @error = @error + @@ERROR;   --记录每次运行sql后是否正确，0正确
            FETCH NEXT FROM order_cursor INTO @temp;   --转到下一个游标，没有会死循环
        END;    
    CLOSE order_cursor;  --关闭游标
    DEALLOCATE order_cursor;   --释放游标
END;
GO

查看表

 SELECT  * FROM    Student;

结果如下

3、实例2

下面是循环插入数据的SQL实例。

declare @orderN varchar(50)--临时变量，用来保存游标值
declare y_curr cursor FOR --申明游标 为orderNum

SELECT  FContractNo
FROM    dbo.v_ContractInfo
WHERE   FDate >= '2019-04-01 00:00:000'
        AND PayMode = 1
        AND Fstatus = 1
        AND FranchiseeNo IN ( '60005', '60007', '60011', '60036', '60070', '60097', '60146' )
        AND (WbStatus IS NULL OR WbStatus=0 ) AND FContractNo NOT IN (SELECT OrderNO FROM CF_BookingCheck )
open y_curr --打开游标
fetch next from Y_curr into @orderN ----开始循环游标变量
while(@@fetch_status=0)---返回被 FETCH  语句执行的最后游标的状态，而不是任何当前被连接打开的游标的状态。

BEGIN 
PRINT(@orderN)
INSERT INTO CF_BookingCheck (OrderNO,OverDays,WbStatus,FinanceType,WbChecker,WbCheckDate)VALUES (@orderN,0,3,0,'系统','2019-04-30 00:00:000')
fetch next from y_curr into @orderN --开始循环游标变量
END
close y_curr--关闭游标
deallocate y_curr --释放游标