SQL Server循环——游标、表变量、临时表

时间:2022-10-18 00:09:27

游标

在游标逐行处理过程中,当需要处理的记录数较大,而且游标处理位于数据库事务内时,速度非常慢。

-- 声明变量
DECLARE @Id AS Int
-- 声明游标
DECLARE C_Id CURSOR FAST_FORWARD READ_ONLY FOR
SELECT b.Id FROM dbo.Books b;
-- 打开游标
OPEN C_Id; -- 取第一条记录
FETCH NEXT FROM C_Id INTO @Id;
WHILE @@FETCH_STATUS = 0
BEGIN
--逻辑处理
SELECT * FROM dbo.Books WHERE Id = @Id;
-- 取下一条记录
FETCH NEXT FROM C_Id INTO @Id;
END;
-- 关闭游标
CLOSE C_Id;
-- 释放游标
DEALLOCATE C_Id;

临时表

-- 创建临时表
IF OBJECT_ID('tempdb.dbo.#tempBooks','U') IS NOT NULL DROP TABLE dbo.#tempBooks;
GO SELECT Id
INTO dbo.#tempBooks
FROM dbo.Books b DECLARE
@Id Int WHILE EXISTS(SELECT Id FROM dbo.#tempBooks)
BEGIN
-- 也可以使用top 1
SET ROWCOUNT 1 --逻辑处理
SELECT @Id=Id FROM dbo.#tempBooks;
SELECT * FROM dbo.Books WHERE Id = @Id; SET ROWCOUNT 0
-- 删除临时表
DELETE FROM dbo.#tempBooks WHERE Id=@Id;
END
DECLARE @intMinId INT ,
@intMaxId INT; SELECT RowID = IDENTITY( INT,1,1)--使用identity(int,1,1)来产生行号,必须把数据插入到一张表中。
INTO #templist
FROM dbo.Books; SELECT @intMinId = MIN(RowID) ,
@intMaxId = MAX(RowID)
FROM dbo.#templist; WHILE @intMinId <= @intMaxId
BEGIN
PRINT @intMinId;
SET @intMinId = @intMinId + 1;
END;
DROP TABLE dbo.#templist;

表变量

-- 声明表变量
DECLARE @tempBooks TABLE
(
Id Int
); -- 将源表中的数据插入到表变量中
INSERT INTO @tempBooks(Id)
SELECT Id FROM dbo.Books
-- 声明变量
DECLARE
@Id Int WHILE EXISTS(SELECT Id FROM @tempBooks)
BEGIN
-- 也可以使用top 1
SET ROWCOUNT 1 --逻辑处理
SELECT @Id = Id FROM @tempBooks;
SELECT * FROM dbo.Books WHERE Id = @Id; SET ROWCOUNT 0 DELETE FROM @tempBooks WHERE Id = @Id;
END