需求：針對Users表，新增一列fullname黔酥，并取值firstname+lastname
ALTER TABLE Users ADD fullname NVARCHAR(39) NULL;
GO
實現(xiàn)：UPDATE Users SET fullname=firstname+''+lastname;
一锭弊、使用游標
使用游標的代碼比較繁瑣，概括起來主要有以下幾個步驟错森，聲明游標确垫，打開游標弓颈，使用游標帽芽，關閉游標和釋放游標
-- 方法1：游標-- 聲明變量DECLARE@uid AS INT,
? ? @firstname AS NVARCHAR(10),
? ? @lastname AS NVARCHAR(20);
? ? -- 聲明游標DECLARE U_Users CURSOR FAST_FORWARD FOR SELECT uid,firstname,lastname
? ? FROM Users
? ? ORDER BY uid;
? ? OPEN U_Users;-- 取第一條記錄
FETCH NEXT FROM U_Users
INTO@uid,@firstname,@lastname;WHILE@@FETCH_STATUS=0 BEGIN
-- 操作
UPDATE Users SET fullname=@firstname+''+@lastname WHERE uid=@uid;
-- 取下一條記錄
FETCH NEXT FROM U_Users INTO@uid,@firstname,@lastname;
END
-- 關閉游標
CLOSE U_Users;
-- 釋放游標
DEALLOCATE U_Users;
二、使用表變量
因為使用游標存在性能和違背面向集合思想的問題翔冀，所以我們有必要用面向集合的思想去找到一種更好的解決方案导街，下面這種方法是使用表變量的方式實現(xiàn)
-- 方法2：使用表變量
-- 聲明表變量
DECLARE@tempTABLE
(
uid INT,
firstname NVARCHAR(10),
lastname NVARCHAR(20)
);
-- 將源表中的數(shù)據(jù)插入到表變量中
INSERT INTO@temp(uid, firstname, lastname )
SELECT uid,firstname,lastname FROM Users
ORDER BY empid;
-- 聲明變量
DECLARE
@empid AS INT,
@firstname AS NVARCHAR(10),
@lastname AS NVARCHAR(20);
WHILEEXISTS(SELECTempidFROM@temp)
BEGIN
-- 也可以使用top 1
SET ROWCOUNT 1
SELECT @uid=uid,@firstname=firstname,@lastname=lastname FROM @temp;
UPDATE Users SET fullname=@firstname+''+@lastname WHERE uid=@uid;
SET ROWCOUNT 0
DELETE FROM@temp WHERE uid=@uid;
END
三、使用臨時表
臨時表也可以實現(xiàn)表變量的功能
-- 創(chuàng)建臨時表
IF OBJECT_ID('tempdb.dbo.#users','U') IS NOT NULL DROP TABLE dbo.#users;
GO
SELECT uid,firstname,lastname
INTO dbo.#users
FROM Users
ORDER BY uid;
-- 聲明變量
DECLARE
? ? @uid AS INT,
? ? @firstname AS NVARCHAR(10),
? ? @lastname AS NVARCHAR(20);
WHILE EXISTS(SELECT uid FROM dbo.#users)
BEGIN
? ? -- 也可以使用top 1
? ? SET ROWCOUNT 1
? ? SELECT @uid= uid, @firstname= firstname,@lastname= lastname FROM dbo.#users;
? ? UPDATE Users SET fullname= @firstname+' '+@lastname WHERE uid=@uid;
? ? SET ROWCOUNT 0
? ? DELETE FROM dbo.#users WHERE uid=@uid;
END
