SqlServer 游标选项说明及测试

原创

JeesonHuang 2023-01-10 11:14:28 博主文章分类：SQLServer ©著作权

©著作权归作者所有：来自51CTO博客作者JeesonHuang的原创作品，请联系作者获取转载授权，否则将追究法律责任

USE [Temp]
GO
--  创建测试表
CREATE TABLE [dbo].[CURTAB](
    [Guid]  [uniqueidentifier] NOT NULL,
    [Name]  [varchar](50) NULL,
    [Value] [numeric](18, 4) NULL,
    [InsertDate] [datetime] NOT NULL,
    [Mark]  [bit] NULL,
    CONSTRAINT [PK_CURTAB] PRIMARY KEY CLUSTERED ([Guid] ASC)
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CURTAB] ADD  CONSTRAINT [DF_CURTAB_Guid]  DEFAULT (newsequentialid()) FOR [Guid]
GO
ALTER TABLE [dbo].[CURTAB] ADD  CONSTRAINT [DF_CURTAB_InsertDate]  DEFAULT (getdate()) FOR [InsertDate]
GO
ALTER TABLE [dbo].[CURTAB] ADD  CONSTRAINT [DF_CURTAB_Mark]  DEFAULT (0) FOR [Mark]
GO


--插入30万行记录
SET NOCOUNT ON
INSERT INTO [dbo].[CURTAB](Name,value)
SELECT 'KK',0
GO 300000
SET NOCOUNT OFF


--全表查询4秒
SELECT [Guid],[Name],[Value],[InsertDate],[Mark] FROM [dbo].[CURTAB]

/*
游标使用步骤:
DECLARE CURSOR 定义 Transact-SQL 服务器游标的属性，例如游标的滚动行为和用于生成游标所操作的结果集的查询。
OPEN 语句填充结果集，
FETCH 从结果集返回行。
CLOSE 语句释放与游标关联的当前结果集。
DEALLOCATE 语句释放游标所使用的资源。


声明游标语法:
DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] 
     [ FORWARD_ONLY | SCROLL ] 
     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] 
     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] 
     [ TYPE_WARNING ] 
     FOR select_statement 
     [ FOR UPDATE [ OF column_name [ ,...n ] ] ]
*/

--测试
SET NOCOUNT ON
DECLARE @GUID UNIQUEIDENTIFIER
DECLARE @NAME VARCHAR(50)
DECLARE @VALUE DECIMAL(18,4)
DECLARE @InsertDate DATETIME
DECLARE @Mark bit
DECLARE MYCUR CURSOR TYPE_WARNING 
    FOR SELECT [Guid],[Name],[Value],[InsertDate],[Mark] FROM [dbo].[CURTAB]
    -- FOR UPDATE OF [Value]
OPEN MYCUR
--FETCH FIRST FROM MYCUR INTO @GUID,@NAME,@VALUE,@InsertDate,@Mark
FETCH NEXT FROM MYCUR INTO @GUID,@NAME,@VALUE,@InsertDate,@Mark
--WAITFOR DELAY '01:00:00' --用于测试 SCROLL_LOCKS 和 OPTIMISTIC
WHILE @@FETCH_STATUS = 0
BEGIN
    --UPDATE [dbo].[CURTAB] SET [Value]=0 WHERE CURRENT OF MYCUR
    FETCH NEXT FROM MYCUR INTO @GUID,@NAME,@VALUE,@InsertDate,@Mark
END
CLOSE MYCUR
DEALLOCATE MYCUR
SET NOCOUNT OFF

官方说明和测试总结:
静态游标较快,滚动游标较慢(测试中，静态游标查询5秒,滚动游标查询9~13秒)

LOCAL ：游标的范围对在其中创建它的批处理、存储过程或触发器有效;
GLOBAL：同一个连接会话中，任何存储过程或批处理中，都可以引用该游标名称;

FORWARD_ONLY：静态或动态游标(依情况而定)，只进游标，只允许 FETCH NEXT;
SCROLL：滚动游标，允许 FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE 定位游标;

STATIC：静态游标，游标数据从 tempdb 取出(不变);
KEYSET：静态或动态游标(依情况而定)，在 tempdb 中生成 keyset 表固定游标行记录和顺序;
DYNAMIC：滚动游标，游标数据从当前表中取出(可变);
FAST_FORWARD：静态游标，只进游标，只允许 FETCH NEXT;

READ_ONLY：静态游标，只读游标，可定义进退;
SCROLL_LOCKS：滚动游标，读入游标时锁定实际表的行记录;
OPTIMISTIC：滚动游标，读入游标时不锁定实际表的行记录;

TYPE_WARNING：游标类型隐式转换为另一种类型时向客户端发出警告(参考:使用隐式游标转换);
FOR UPDATE：定义游标中可更新的列;

DECLARE CURSOR 不指定 READ_ONLY、OPTIMISTIC 或 SCROLL_LOCKS，则默认值如下：