SET @local_variable (Transact-SQL)

项目
06/12/2024

适用于： SQL Server Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics 分析平台系统 (PDW) Microsoft Fabric 中的 SQL 分析端点 Microsoft Fabric 中的仓库

将以前使用 DECLARE @local_variable 语句创建的指定局部变量设置为指定的值。

Transact-SQL 语法约定

语法

适用于 SQL Server、Azure SQL 数据库和 Azure SQL 托管实例的语法：

SET
{ @local_variable
    [ . { property_name | field_name } ] = { expression | udt_name { . | :: } method_name }
}
| { @SQLCLR_local_variable.mutator_method }
| { @local_variable
    { += | -= | *= | /= | %= | &= | ^= | |= } expression
}
| { @cursor_variable =
    { @cursor_variable | cursor_name
    | { CURSOR [ [ LOCAL | GLOBAL ] ]
        [ FORWARD_ONLY | SCROLL ]
        [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
        [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
        [ TYPE_WARNING ]
    FOR select_statement
        [ FOR { READ ONLY | UPDATE [ OF column_name [ , ...n ] ] } ]
      }
    }
}

Azure Synapse Analytics、并行数据仓库和 Microsoft Fabric 的语法：

SET @local_variable { = | += | -= | *= | /= | %= | &= | ^= | |= } expression

参数

@local_variable

除 cursor、text、ntext、image 或 table 之外的任何类型的变量的名称。变量名称必须以 at 符号 (@) 开头。变量名称必须遵循有关标识符的规则。

property_name

用户定义类型的属性。

field_name

用户定义类型的公共字段。

udt_name

公共语言运行时 (CLR) 用户定义类型的名称。

{ . |:: }

指定 CLR 用户定义类型的方法。对于实例（非静态）方法，请使用句点（.）。对于静态方法，请使用两个冒号（::）。若要调用 CLR 用户定义类型的方法、属性或字段，必须对类型具有 EXECUTE 权限。

method_name （参数 [ ,... n ]

用户定义类型的方法，它使用一个或多个参数来修改类型实例的状态。静态方法必须是公共的。

@SQLCLR_local_variable

其类型位于程序集内的变量。有关详细信息，请参阅公共语言运行时（CLR）集成编程概念。

mutator_method

程序集中可更改对象状态的方法。 SQLMethodAttribute.IsMutator 会应用于此方法。

{ += | -= | *= | /= | %= | &= | ^= | |= }

复合赋值运算符：

+= - 添加和分配
-= - 减去和分配
*= - 相乘和赋值
/= - 划分和分配
%= - 模式和分配
&= - 按 AND 位和分配
^= - 按 XOR 位和分配
|= - 按 OR 位和分配

expression

任何有效的表达式。

cursor_variable

游标变量的名称。如果目标游标变量先前引用了不同游标，则删除先前的引用。

cursor_name

使用 DECLARE CURSOR 语句声明的游标的名称。

CURSOR

指定 SET 该语句包含游标的声明。

SCROLL

指定游标支持所有提取选项：FIRST、、LAST、NEXT、PRIOR和RELATIVEABSOLUTE。不能在同时指定 SCROLL 时指定 FAST_FORWARD。

FORWARD_ONLY

指定游标仅 FETCH NEXT 支持该选项。仅以一个方向、从第一行到最后一行检索游标。如果未指定FORWARD_ONLY游标或DYNAMIC关键字，则游标将实现为 DYNAMIC。 KEYSETSTATIC 如果未指定或指定，FORWARD_ONLYSCROLLFORWARD_ONLY则为默认值，除非指定关键字STATIC或。 KEYSETDYNAMIC 对于 STATIC， KEYSET和 DYNAMIC 游标 SCROLL 是默认值。

STATIC

定义一个游标，以创建将由该游标使用的数据的临时副本。对游标的所有请求都从此临时表中 tempdb得到应答。因此，打开游标后对基表所做的修改不会在对游标进行提取操作返回的数据中反映。而且，此游标不支持修改。

KEYSET

指定当游标打开时，游标中行的成员身份和顺序已经固定。唯一标识行的键集内置于可设置的键中 tempdb。对基表中的非键值所做的更改（由游标所有者更改或其他用户提交）在游标所有者滚动游标时可见。其他用户进行的插入不可见，且不能通过 Transact-SQL 服务器游标进行插入。

如果删除行，则尝试提取该行将返回一-2个 @@FETCH_STATUS 。从游标外部更新键值类似于删除旧行后再插入新行。具有新值的行不可见，并尝试提取包含旧值的行返回一个@@FETCH_STATUS-2值。如果通过指定子句通过游标 WHERE CURRENT OF 进行更新，则新值可见。

DYNAMIC

定义一个游标，以反映游标所有者滚动游标时对结果集内的行所做的所有数据更改。行的数据值、顺序和成员身份在每次提取时都会更改。动态游标不支持绝对和相对提取选项。

FAST_FORWARD

指定FORWARD_ONLYREAD_ONLY启用了优化的游标。 FAST_FORWARD 指定时 SCROLL 不能指定。

READ_ONLY

禁止通过该游标进行更新。游标不能在或语句的子句中 WHERE CURRENT OF UPDATE DELETE 引用。该选项优先于要更新的游标的默认功能。

SCROLL LOCKS

指定通过游标进行的定位更新或删除一定会成功。将行读入游标时 SQL Server 将锁定这些行，以确保随后可对它们进行修改。不能指定 SCROLL_LOCKS 何时 FAST_FORWARD 也指定。

OPTIMISTIC

指定如果行自读入游标以来已得到更新，则通过游标进行的定位更新或定位删除不成功。将行读入游标时，SQL Server 不锁定这些行。相反，它使用 timestamp 列值的比较，或者如果表没有 timestamp 列，则使用校验和值，以确定将行读入游标后是否已修改该行。如果已修改该行，尝试进行的定位更新或定位删除将失败。不能指定 OPTIMISTIC 何时 FAST_FORWARD 也指定。

TYPE_WARNING

指定如果游标从所请求的类型隐式转换为另一种类型，则向客户端发送警告消息。

FOR select_statement

定义游标结果集的标准 SELECT 语句。关键字FOR BROWSE，INTO不允许在游标声明的select_statement内。

如果使用、、或，或者将聚合表达式包含在select_list中，则游标将创建为 STATIC。HAVINGGROUP BYUNIONDISTINCT

如果每个基础表没有唯一索引和 ISO SCROLL 游标，或者请求 Transact-SQL KEYSET 游标，则游标将自动是 STATIC 游标。

如果select_statement包含列不是唯一ORDER BY行标识符的子句，DYNAMIC则游标将KEYSET转换为游标;如果KEYSET无法打开游标，则游标将转换为STATIC游标。此过程也适用于使用 ISO 语法定义的游标，但没有 STATIC 关键字。

READ ONLY

禁止通过该游标进行更新。游标不能在或语句的子句中 WHERE CURRENT OF UPDATE DELETE 引用。该选项优先于要更新的游标的默认功能。此关键字因空格而不是下划线而READONLY有所不同READ_ONLY。

UPDATE [ OF column_name [ ,...n ] ]

定义游标中可更新的列。如果提供了 OF <column_name> [ , ...n ]，则只允许修改所列出的列。如果未提供列表，则所有列都可以更新，除非游标被 READ_ONLY定义为。

注解

声明变量后，将初始化为 NULL. 使用 SET 语句为不是 NULL 声明变量的值赋值。为 SET 变量赋值的语句返回单个值。初始化多个变量时，请为每个局部变量使用单独的 SET 语句。

只能在表达式中使用变量，而不能代替对象名或关键字。若要构造动态 Transact-SQL 语句，请使用 EXECUTE。

尽管包含和关键字的SET @cursor_variable = CURSOR...语法规则SET @cursor_variable，但使用语法时，游标将创建为GLOBAL或LOCAL创建，具体取决于默认设置为本地游标数据库选项。GLOBAL LOCAL

即使游标变量引用全局游标，它们也始终是局部变量。如果游标变量引用全局游标，则该游标既有全局游标引用，也有局部游标引用。有关详细信息，请参阅示例 D，将 SET 与全局游标配合使用。

有关详细信息，请参阅声明游标 (Transact-SQL)。

可以在任意位置使用复合赋值运算符，该赋值具有运算符右侧的表达式，包括变量和SET语句中的UPDATESELECTRECEIVE变量。

不要在语句中使用 SELECT 变量来连接值（即计算聚合值）。可能会出现意外的查询结果，因为列表中的所有表达式 SELECT （包括赋值）不一定为每个输出行运行一次。有关详细信息，请参阅 KB 287515。

权限

要求公共角色具有成员身份。所有用户都可以使用 SET @local_variable。

示例

本文中的 Transact-SQL 代码示例使用 AdventureWorks2022 或 AdventureWorksDW2022 示例数据库，可从 Microsoft SQL Server 示例和社区项目主页下载它。

A. 使用 SET 打印初始化的变量的值

以下示例创建 @myVar 变量，将字符串值放入该变量，然后输出 @myVar 变量的值。

DECLARE @myVar CHAR(20);
SET @myVar = 'This is a test';
SELECT @myVar;
GO

B. 在 SELECT 语句中使用 SET 分配值的局部变量

以下示例创建一个命名@state的局部变量，并在语句中使用SELECT局部变量来查找所有处于状态Oregon的员工的名字（FirstName）和姓氏（LastName）。

USE AdventureWorks2022;
GO
DECLARE @state CHAR(25);
SET @state = N'Oregon';
SELECT RTRIM(FirstName) + ' ' + RTRIM(LastName) AS Name, City
FROM HumanResources.vEmployee
WHERE StateProvinceName = @state;
GO

°C 对局部变量使用复合赋值

以下两个示例将产生相同的结果。每个示例创建一个名为 @NewBalance，乘以 10它的局部变量，然后在语句中 SELECT 显示局部变量的新值。第二个示例使用一个复合赋值运算符。

/* Example one */
DECLARE @NewBalance INT;
SET @NewBalance = 10;
SET @NewBalance = @NewBalance * 10;
SELECT @NewBalance;
GO

/* Example Two */
DECLARE @NewBalance INT = 10;
SET @NewBalance *= 10;
SELECT @NewBalance;
GO

D. 将 SET 与全局游标配合使用

以下示例创建一个局部变量，然后将游标变量设置为全局游标名。

DECLARE my_cursor CURSOR GLOBAL
FOR SELECT * FROM Purchasing.ShipMethod
DECLARE @my_variable CURSOR ;
SET @my_variable = my_cursor ;
--There is a GLOBAL cursor declared(my_cursor) and a LOCAL variable
--(@my_variable) set to the my_cursor cursor.

DEALLOCATE my_cursor;
GO
--There is now only a LOCAL variable reference
--(@my_variable) to the my_cursor cursor.

E. 使用 SET 定义游标

以下示例使用 SET 语句定义游标。

DECLARE @CursorVar CURSOR;

SET @CursorVar = CURSOR SCROLL DYNAMIC
FOR
SELECT LastName, FirstName
FROM AdventureWorks2022.HumanResources.vEmployee
WHERE LastName like 'B%';

OPEN @CursorVar;

FETCH NEXT FROM @CursorVar;
WHILE @@FETCH_STATUS = 0
BEGIN
    FETCH NEXT FROM @CursorVar
END;

CLOSE @CursorVar;
DEALLOCATE @CursorVar;
GO

F. 从查询中分配值

以下示例使用查询为变量赋值。

USE AdventureWorks2022;
GO
DECLARE @rows INT;
SET @rows = (SELECT COUNT(*) FROM Sales.Customer);
SELECT @rows;
GO

G. 通过修改类型的属性，为用户定义的类型变量赋值

以下示例通过修改类型的 X 属性的值来设置用户定义类型 (UDT) Point 的值。

DECLARE @p Point;
SET @p.X = @p.X + 1.1;
SELECT @p;
GO

详细了解如何创建本示例中引用的 Point UDT，并查看创建用户定义类型一文中的以下示例。

H. 通过调用类型的方法，为用户定义的类型变量赋值

以下示例通过调用类型的 SetXY 方法设置用户定义类型 point 的值。

DECLARE @p Point;
SET @p=point.SetXY(23.5, 23.5);

I. 为 CLR 类型创建变量并调用 mutator 方法

以下示例为 Point 类型创建变量，然后在 Point 中执行赋值函数方法。

CREATE ASSEMBLY mytest FROM 'c:\test.dll' WITH PERMISSION_SET = SAFE
CREATE TYPE Point EXTERNAL NAME mytest.Point
GO
DECLARE @p Point = CONVERT(Point, '')
SET @p.SetXY(22, 23);

示例：Azure Synapse Analytics 和 Analytics Platform System (PDW)