一、游标的使用
有为博主链接中写的很详细,通俗易懂;
SQL – 游标(详细)
–下面整理了使用游标进行一个while循环,用于遍历数据
方便复制:
USE [数据库名]
GO
DECLARE @serviceName NVARCHAR(256) --定义变量
DECLARE @traceKey BIGINT --定义变量
– 1.定义游标cur,并准备从表T_TRACE中读取TraceKey, ServiceName字段
DECLARE cur CURSOR FOR SELECT TraceKey,ServiceName FROM T_TRACE WHERE UptrillionApiResourceKey = -1;
OPEN cur --2.打开游标
FETCH NEXT FROM cur INTO @traceKey,@serviceName --3.开始(1)中的读取(单行)
WHILE @@FETCH_STATUS = 0 --边界条件 = 0 表示读取成功
BEGIN
–添加要执行的操作内容
FETCH NEXT FROM cur INTO @traceKey, @serviceName --读取下一条数据 --> 数据库会自动更新While循环条件 @@FETCH_STATUS
END
CLOSE cur --4.关闭游标
DEALLOCATE cur --5.销毁游标
(代码同上)
USE [数据库名]
GO
DECLARE @serviceName NVARCHAR(256) --定义变量,用于数据更新的字段1
DECLARE @traceKey BIGINT --定义变量,用于数据更新的字段2
-- 1.定义游标cur,并准备从表T_TRACE中读取TraceKey, ServiceName字段
DECLARE cur CURSOR FOR SELECT TraceKey,ServiceName FROM T_TRACE WHERE UptrillionApiResourceKey = -1;
OPEN cur --2.打开游标
FETCH NEXT FROM cur INTO @traceKey,@serviceName --3.开始(1)中的读取(单行)
WHILE @@FETCH_STATUS = 0 --边界条件 = 0 表示读取成功
BEGIN
--添加要执行的操作内容
FETCH NEXT FROM cur INTO @traceKey, @serviceName --读取下一条数据 --> 数据库会自动更新While循环条件 @@FETCH_STATUS
END
CLOSE cur --4.关闭游标
DEALLOCATE cur --5.销毁游标
二、临时表的使用(本地临时表)
下面整理了使用临时表的进行While循环,用于遍历数据
方便复制:
Use [数据库]
go
---- 1.创建临时表 [#T_TRACE_TEST_cursor] //临时存储在 SQLServer/Databases/System Databases/tempdb/Temporary Tables中
— 此处定义的临时表有三个字段:[行号 i], [TraceKey], [ServiceName]
SELECT ROW_NUMBER() OVER (ORDER BY [TraceKey]) as i ,[TraceKey], [ServiceName] INTO #T_TRACE_TEST_cursor FROM T_TRACE_TEST order by [TraceKey]
---- 2.创建临时表索引(必须)
CREATE CLUSTERED INDEX IDX_i ON #T_TRACE_TEST_cursor (i)
---- 3.定义循环临时表的字段
DECLARE @i INT=1;
---- 4.开始循环 并撰写相关T-SQL语句
DECLARE @count INT=0;
SELECT @count=COUNT(1) FROM #T_TRACE_TEST_cursor --临时表总行数(用于While循环);
WHILE(@i<=@count)
BEGIN
— 4.1 要执行的 T-SQL语句
— 4.2 建议使用exec执行SQL语句(此方法执行速度要比直接执行SQL要快)
declare @sqlStr nvarchar(max) = CONCAT(‘拼接 操作其他内容的SQL执行语句’)
–print @sqlStr
exec(@sqlStr)
— 4.3控制循环边界
SET @i=@i+1;
END
---- 5.删除临时表 [#T_TRACE_TEST_cursor](必须)
DROP TABLE #T_TRACE_TEST_cursor
(代码同上)
Use [数据库]
go
---- 1.创建临时表 [#T_TRACE_TEST_cursor] //临时存储在 SQLServer/Databases/System Databases/tempdb/Temporary Tables中
--- 此处定义的临时表有三个字段:[行号 i], [TraceKey], [ServiceName]
SELECT ROW_NUMBER() OVER (ORDER BY [TraceKey]) as i ,[TraceKey], [ServiceName] INTO #T_TRACE_TEST_cursor FROM T_TRACE_TEST order by [TraceKey]
---- 2.创建临时表索引(必须)
CREATE CLUSTERED INDEX IDX_i ON #T_TRACE_TEST_cursor (i)
---- 3.定义循环临时表的字段
DECLARE @i INT=1;
---- 4.开始循环 并撰写相关T-SQL语句
DECLARE @count INT=0;
SELECT @count=COUNT(1) FROM #T_TRACE_TEST_cursor --临时表总行数(用于While循环);
WHILE(@i<=@count)
BEGIN
--- 4.1 要执行的 T-SQL语句
--- 4.2 建议使用exec执行SQL语句(此方法执行速度要比直接执行SQL要快)
declare @sqlStr nvarchar(max) = CONCAT('拼接 操作其他内容的SQL执行语句')
--print @sqlStr
exec(@sqlStr)
--- 4.3控制循环边界
SET @i=@i+1;
END
---- 5.删除临时表 [#T_TRACE_TEST_cursor](必须)
DROP TABLE #T_TRACE_TEST_cursor
三、游标和临时表执行速度比较
经过测试在37523行数据中,二者的执行速度时间相等(你可以在更高的数据量中进行测试)