需求：针对Users表，新增一列fullname，并取值firstname+lastname
ALTER TABLE Users ADD fullname NVARCHAR(39) NULL;
GO
实现：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;
二、使用表变量
因为使用游标存在性能和违背面向集合思想的问题，所以我们有必要用面向集合的思想去找到一种更好的解决方案，下面这种方法是使用表变量的方式实现
-- 方法2：使用表变量
-- 声明表变量
DECLARE@tempTABLE
(
uid INT,
firstname NVARCHAR(10),
lastname NVARCHAR(20)
);
-- 将源表中的数据插入到表变量中
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
三、使用临时表
临时表也可以实现表变量的功能
-- 创建临时表
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
