SQL Server中遍历表中记录的方法

rgr3255

遍历表有下面几种方法

1、使用游标
2、使用表变量
3、使用临时表


下面通过一个实例分别介绍三中方法的实现：

1、需求

给HR.Employees表，fullname 列赋值，其值为 firstname+lastname
为了演示表的遍历，忽略 UPDATE HR.Employees SET fullname= firstname+' '+lastname; 的实现方式


2、使用游标
使用游标的代码主要有以下几个步骤，声明游标，打开游标，使用游标，关闭游标和释放游标。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

-- 方法1：游标 -- 声明变量 DECLARE     @empid AS INT,     @firstname AS NVARCHAR(10),     @lastname AS NVARCHAR(20);       -- 声明游标 DECLARE C_Employees CURSOR FAST_FORWARD FOR     SELECT empid,firstname,lastname     FROM HR.Employees     ORDER BY empid;       OPEN C_Employees; -- 取第一条记录 FETCH NEXT FROM C_Employees INTO @empid,@firstname,@lastname; WHILE @@FETCH_STATUS=0 BEGIN     -- 操作     UPDATE HR.Employees SET fullname= @firstname+' '+@lastname WHERE empid=@empid;           -- 取下一条记录     FETCH NEXT FROM C_Employees INTO @empid,@firstname,@lastname; END -- 关闭游标 CLOSE C_Employees; -- 释放游标 DEALLOCATE C_Employees;

3、使用表变量

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

-- 方法2：使用表变量 -- 声明表变量 DECLARE @temp TABLE (     empid INT,     firstname NVARCHAR(10),     lastname NVARCHAR(20) ); -- 将源表中的数据插入到表变量中 INSERT INTO @temp(empid, firstname, lastname ) SELECT empid,firstname,lastname FROM HR.Employees ORDER BY empid; -- 声明变量 DECLARE     @empid AS INT,     @firstname AS NVARCHAR(10),     @lastname AS NVARCHAR(20);       WHILE EXISTS(SELECT empid FROM @temp) BEGIN     -- 也可以使用top 1     SET ROWCOUNT 1     SELECT @empid= empid, @firstname= firstname,@lastname= lastname FROM @temp;     UPDATE HR.Employees SET fullname= @firstname+' '+@lastname WHERE empid=@empid;     SET ROWCOUNT 0           DELETE FROM @temp WHERE empid=@empid; END

4、使用临时表

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

-- 方法3：使用临时表 -- 创建临时表 IF OBJECT_ID('tempdb.dbo.#tempemployees','U') IS NOT NULL DROP TABLE dbo.#tempemployees; GO SELECT empid,firstname,lastname INTO dbo.#tempemployees FROM HR.Employees ORDER BY empid; --SELECT * FROM dbo.#tempemployees; -- 声明变量 DECLARE     @empid AS INT,     @firstname AS NVARCHAR(10),     @lastname AS NVARCHAR(20);       WHILE EXISTS(SELECT empid FROM dbo.#tempemployees) BEGIN     -- 也可以使用top 1     SET ROWCOUNT 1     SELECT @empid= empid, @firstname= firstname,@lastname= lastname FROM dbo.#tempemployees;     UPDATE HR.Employees SET fullname= @firstname+' '+@lastname WHERE empid=@empid;     SET ROWCOUNT 0           DELETE FROM dbo.#tempemployees WHERE empid=@empid; END