SQL Server遍曆表的幾種方法

標籤：

　　在資料庫開發過程中，我們經常會碰到要遍曆資料表的情形，一提到遍曆表，我們第一印象可能就想到使用遊標，使用遊標雖然直觀易懂，但是它不符合面向集合操作的原則，而且效能也比面向集合低。當然，從面向集合操作的角度出發，也有兩種方法可以進行遍曆表的操作，總結起來，遍曆表有下面幾種方法。

1. 使用遊標
2. 使用表變數
3. 使用暫存資料表

我的需求是：針對HR.Employees表，新增一列fullname，並取值firstname+lastname。

這個需求本來可以一條sql語句搞定，如下代碼所示。但是為了示範表的遍曆，我還是使用了這三種方式來實現一下。

USE TSQLFundamentals2008;GOUPDATE HR.Employees SET fullname= firstname+‘ ‘+lastname;

使用遊標

　　使用遊標的代碼比較繁瑣，概括起來主要有以下幾個步驟，聲明遊標，開啟遊標，使用遊標，關閉遊標和釋放遊標。範例程式碼如下。

 

USE TSQLFundamentals2008;GOSELECT * FROM HR.Employees;-- 需求是，新增一列fullname，取值firstname+lastnameALTER TABLE HR.Employees ADD fullname NVARCHAR(30) NULL;GO-- 方法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=0BEGIN    -- 操作    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;-- 方法2：使用表變數-- 聲明表變數DECLARE @temp TABLE(    empid INT,    firstname NVARCHAR(10),    lastname NVARCHAR(20));-- 將源表中的資料插入到表變數中INSERT INTO @temp(empid, firstname, lastname )SELECT empid,firstname,lastname FROM HR.EmployeesORDER 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-- 方法3：使用暫存資料表-- 建立暫存資料表IF OBJECT_ID(‘tempdb.dbo.#tempemployees‘,‘U‘) IS NOT NULL DROP TABLE dbo.#tempemployees;GOSELECT empid,firstname,lastname INTO dbo.#tempemployeesFROM HR.EmployeesORDER 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--UPDATE HR.Employees SET fullname=NULL;--SELECT * FROM HR.Employees;--USE TSQLFundamentals2008;--GO--UPDATE HR.Employees SET fullname= firstname+‘ ‘+lastname;

View Code

 

SQL Server遍曆表的幾種方法