SET (Transact-SQL)SET (Transact-SQL)

适用对象:是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

将先前使用 DECLARE @local_variable 语句创建的指定局部变量设置为指定值 。Sets the specified local variable, previously created by using the DECLARE @local_variable statement, to the specified value.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

SQL Server 和 Azure SQL 数据库的语法:Syntax for SQL Server and Azure SQL Database:

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 [ 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 SQL 数据仓库和并行数据仓库语法:Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse:

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

参数Arguments

@ local_variable @ local_variable
cursortextntextimagetable 之外的任何类型的变量的名称。The name of a variable of any type except cursor, text, ntext, image, or table. 变量名称必须以 at 符号 (@) 开头 。Variable names must start with one at sign (@). 变量名称必须遵循有关标识符的规则。Variable names must follow the rules for identifiers.

property_name property_name
用户定义类型的属性。A property of a user-defined type.

field_name field_name
用户定义类型的公共字段。A public field of a user-defined type.

udt_name udt_name
公共语言运行时 (CLR) 用户定义类型的名称。The name of a common language runtime (CLR) user-defined type.

{ . | :: }
指定 CLR 用户定义类型的方法。Specifies a method of a CLR user-define type. 对于实例(非静态)方法,请使用句点 ( . )。For an instance (non-static) method, use a period (.). 对于静态方法,请使用两个冒号 (::) 。For a static method, use two colons (::). 若要调用 CLR 用户定义类型的方法、属性或字段,必须对类型具有 EXECUTE 权限。To invoke a method, property, or field of a CLR user-defined type, you must have EXECUTE permission on the type.

method_name ( argument [ ,... n ] ) method_name ( argument [ ,... n ] )
用户定义类型的方法,它使用一个或多个参数来修改类型实例的状态。A method of a user-defined type that takes one or more arguments to modify the state of an instance of a type. 静态方法必须是公共的。Static methods must be public.

@ SQLCLR_local_variable @ SQLCLR_local_variable
其类型位于程序集内的变量。A variable whose type is located in an assembly. 有关详细信息,请参阅公共语言运行时 (CLR) 集成编程概念For more information, see Common Language Runtime (CLR) Integration Programming Concepts.

mutator_method mutator_method
程序集中可更改对象状态的方法。A method in the assembly that can change the state of the object. SQLMethodAttribute.IsMutator 会应用于此方法。SQLMethodAttribute.IsMutator is applied to this method.

{ += | -= | *= | /= | %= | &= | ^= | |= }
复合赋值运算符:Compound assignment operator:

+= 相加并赋值+= Add and assign

-= 相减并赋值-= Subtract and assign

*= 相乘并赋值*= Multiply and assign

/= 相除并赋值/= Divide and assign

%= 取模并赋值%= Modulo and assign

&=“位与”并赋值&= Bitwise AND and assign

^=“位异或”并赋值^= Bitwise XOR and assign

|= “位或”并赋值|= Bitwise OR and assign

expressionexpression
任何有效的表达式Any valid expression.

cursor_variable cursor_variable
游标变量的名称。The name of a cursor variable. 如果目标游标变量先前引用了不同游标,则删除先前的引用。If the target cursor variable previously referenced a different cursor, that previous reference is removed.

cursor_name cursor_name
使用 DECLARE CURSOR 语句声明的游标名称。The name of a cursor declared by using the DECLARE CURSOR statement.

CURSORCURSOR
指定 SET 语句包含游标的声明。Specifies that the SET statement contains a declaration of a cursor.

SCROLLSCROLL
指定游标支持所有提取选项:FIRST、LAST、NEXT、PRIOR、RELATIVE 以及 ABSOLUTE。Specifies that the cursor supports all fetch options: FIRST, LAST, NEXT, PRIOR, RELATIVE, and ABSOLUTE. 如果还指定了 FAST_FORWARD,则不能指定 SCROLL。You can't specify SCROLL when you've also specified FAST_FORWARD.

FORWARD_ONLYFORWARD_ONLY
指定游标仅支持 FETCH NEXT 选项。Specifies that the cursor supports only the FETCH NEXT option. 仅以一个方向、从第一行到最后一行检索游标。The cursor is retrieved only in one direction, from the first to the last row. 如果没有使用 STATIC、KEYSET 或 DYNAMIC 关键字指定 FORWARD_ONLY,游标将作为 DYNAMIC 实现。When you specify FORWARD_ONLY without the STATIC, KEYSET, or DYNAMIC keywords, the cursor is implemented as DYNAMIC. 如果 FORWARD_ONLY 和 SCROLL 均未指定,那么除非指定了 STATIC、KEYSET 或 DYNAMIC 关键字,否则默认值为 FORWARD_ONLY。If you don't specify either FORWARD_ONLY or SCROLL, FORWARD_ONLY is the default, unless you specify the keywords STATIC, KEYSET, or DYNAMIC. 对于 STATIC、KEYSET 和 DYNAMIC 游标,SCROLL 为默认值。For STATIC, KEYSET, and DYNAMIC cursors, SCROLL is the default.

STATICSTATIC
定义一个游标,以创建将由该游标使用的数据的临时副本。Defines a cursor that makes a temporary copy of the data to be used by the cursor. 对游标的所有请求都通过 tempdb 中的这个临时表进行答复。All requests to the cursor are answered from this temporary table in tempdb. 因此,打开游标后对基表所做的修改不会在对游标进行提取操作返回的数据中反映。As a result, modifications made to the base tables after the cursor is opened aren't reflected in the data returned by fetches made to the cursor. 而且,此游标不支持修改。And, this cursor doesn't support modifications.

KEYSETKEYSET
指定当游标打开时,游标中行的成员身份和顺序已经固定。Specifies that the membership and order of rows in the cursor are fixed when the cursor is opened. 对行进行唯一标识的键集内置在 tempdb 内的 keysettable 中。The set of keys that uniquely identify the rows is built into the keysettable in tempdb. 对基表中的非键值所做的更改(由游标所有者更改或其他用户提交)在游标所有者滚动游标时可见。Changes to nonkey values in the base tables, either made by the cursor owner or committed by other users, are visible as the cursor owner scrolls around the cursor. 其他用户进行的插入不可见,且不能通过 Transact-SQLTransact-SQL 服务器游标进行插入。Inserts made by other users aren't visible, and inserts can't be made through a Transact-SQLTransact-SQL server cursor.

如果删除某一行,则在尝试提取该行时返回的 @@FETCH_STATUS 为 -2。If a row is deleted, an attempt to fetch the row returns an @@FETCH_STATUS of -2. 从游标外部更新键值类似于删除旧行后再插入新行。Updates of key values from outside the cursor are similar to a delete of the old row followed by an insert of the new row. 具有新值的行不可见,且尝试提取具有旧值的行时返回的 @@FETCH_STATUS 为 -2。The row with the new values isn't visible, and tries to fetch the row with the old values return an @@FETCH_STATUS of -2. 如果通过指定 WHERE CURRENT OF 子句来通过游标执行更新,则新值可见。The new values are visible if the update happens through the cursor by specifying the WHERE CURRENT OF clause.

DYNAMICDYNAMIC
定义一个游标,以反映游标所有者滚动游标时对结果集内的行所做的所有数据更改。Defines a cursor that reflects all data changes made to the rows in its result set as the cursor owner scrolls around the cursor. 行的数据值、顺序和成员身份在每次提取时都会更改。The data values, order, and membership of the rows can change on each fetch. 动态游标不支持绝对和相对提取选项。The absolute and relative fetch options aren't supported with dynamic cursors.

FAST_FORWARDFAST_FORWARD
指定启用了优化的 FORWARD_ONLY 和 READ_ONLY 游标。Specifies a FORWARD_ONLY, READ_ONLY cursor with optimizations enabled. 如果还指定了 SCROLL,则不能指定 FAST_FORWARD。FAST_FORWARD can't be specified when SCROLL is also specified.

READ_ONLYREAD_ONLY
禁止通过该游标进行更新。Prevents updates from being made through this cursor. 在 UPDATE 或 DELETE 语句的 WHERE CURRENT OF 子句中不能引用游标。The cursor can't be referenced in a WHERE CURRENT OF clause in an UPDATE or DELETE statement. 该选项优先于要更新的游标的默认功能。This option overrides the default capability of a cursor to be updated.

SCROLL LOCKSSCROLL LOCKS
指定通过游标进行的定位更新或删除一定会成功。Specifies that positioned updates or deletes made through the cursor are guaranteed to succeed. 将行读入游标时 SQL ServerSQL Server 将锁定这些行,以确保随后可对它们进行修改。SQL ServerSQL Server locks the rows as they're read into the cursor to guarantee their availability for later modifications. 如果还指定了 FAST_FORWARD,则不能指定 SCROLL_LOCKS。You can't specify SCROLL_LOCKS when FAST_FORWARD is also specified.

OPTIMISTICOPTIMISTIC
指定如果行自读入游标以来已得到更新,则通过游标进行的定位更新或定位删除不成功。Specifies that positioned updates or deletes made through the cursor don't succeed if the row was updated since being read into the cursor. 将行读入游标时,SQL ServerSQL Server 不锁定这些行。SQL ServerSQL Server doesn't lock rows as they're read into the cursor. 相反,它使用 timestamp 列值的比较,或者如果表没有 timestamp 列,则使用校验和值,以确定将行读入游标后是否已修改该行。Instead, it uses comparisons of timestamp column values, or a checksum value, if the table has no timestamp column, to determine if the row was modified after being read into the cursor. 如果已修改该行,尝试进行的定位更新或定位删除将失败。If the row was modified, the attempted positioned update or delete fails. 如果还指定了 FAST_FORWARD,则不能指定 OPTIMISTIC。You can't specify OPTIMISTIC when FAST_FORWARD is also specified.

TYPE_WARNINGTYPE_WARNING
指定如果游标从所请求的类型隐式转换为另一种类型,则向客户端发送警告消息。Specifies that a warning message is sent to the client when the cursor is implicitly converted from the requested type to another.

FOR select_statement FOR select_statement
定义游标结果集的标准 SELECT 语句。Is a standard SELECT statement that defines the result set of the cursor. 在游标声明的 select_statement 中不允许使用关键字 FOR BROWSE 和 INTO。The keywords FOR BROWSE, and INTO aren't allowed within the select_statement of a cursor declaration.

如果使用 DISTINCT、UNION、GROUP BY 或 HAVING,或者 select_list 中包含聚合表达式,游标将创建为 STATIC。If you use DISTINCT, UNION, GROUP BY, or HAVING, or you include an aggregate expression in the select_list, the cursor is created as STATIC.

如果每个基础表都没有唯一索引和 ISO SCROLL 游标,或如果请求了 Transact-SQLTransact-SQL KEYSET 游标,则游标将自动成为 STATIC 游标。If each underlying table doesn't have a unique index and an ISO SCROLL cursor or if a Transact-SQLTransact-SQL KEYSET cursor is requested, the cursor is automatically a STATIC cursor.

如果 select_statement 包含 ORDER BY 子句,且其中的列不是唯一的行标识符,则 DYNAMIC 游标将转换为 KEYSET 游标,或如果 KEYSET 游标不能打开,则该游标转换为 STATIC 游标。If select_statement contains an ORDER BY clause in which the columns aren't unique row identifiers, a DYNAMIC cursor is converted to a KEYSET cursor, or to a STATIC cursor if a KEYSET cursor can't be opened. 此进程同样适用于使用 ISO 语法定义但不带 STATIC 关键字的游标。This process also occurs for a cursor defined by using ISO syntax but without the STATIC keyword.

READ ONLYREAD ONLY
禁止通过该游标进行更新。Prevents updates from being made through this cursor. 在 UPDATE 或 DELETE 语句的 WHERE CURRENT OF 子句中不能引用游标。The cursor can't be referenced in a WHERE CURRENT OF clause in an UPDATE or DELETE statement. 该选项优先于要更新的游标的默认功能。This option overrides the default capability of a cursor to be updated. 该关键字与早期的 READ_ONLY 关键字的不同之处是 READ 和 ONLY 之间是一个空格,而不是下划线。This keyword varies from the earlier READ_ONLY by having a space instead of an underscore between READ and ONLY.

UPDATE [OF column_name[ ,... n ] ]
定义游标中可更新的列。Defines updatable columns within the cursor. 如果提供了 OF column_name [,...n],则只允许修改列出的列 。If OF column_name [,...n] is supplied, only the columns listed allow modifications. 如果没有提供列表,则可更新所有列,除非已将游标定义为 READ_ONLY。When no list is supplied, all columns can be updated, unless the cursor has been defined as READ_ONLY.

RemarksRemarks

声明一个变量后,该变量将初始化为 NULL。After a variable is declared, it's initialized to NULL. 使用 SET 语句将一个不是 NULL 的值赋予声明的变量。Use the SET statement to assign a value that isn't NULL to a declared variable. 给变量赋值的 SET 语句返回单值。The SET statement that assigns a value to the variable returns a single value. 在初始化多个变量时,为每个局部变量使用单独的 SET 语句。When you initialize multiple variables, use a separate SET statement for each local variable.

只能在表达式中使用变量,而不能代替对象名或关键字。You can use variables only in expressions, not instead of object names or keywords. 若要构造动态 Transact-SQLTransact-SQL 语句,请使用 EXECUTE。To construct dynamic Transact-SQLTransact-SQL statements, use EXECUTE.

SET @ cursor_variable 的语法规则不包含 LOCAL 和 GLOBAL 关键字。The syntax rules for SET @cursor_variable don't include the LOCAL and GLOBAL keywords. 使用 SET @ cursor_variable = CURSOR... 语法时,根据“默认使用本地游标数据库”选项的设置,游标将创建为 GLOBAL 或 LOCAL。When you use the SET @cursor_variable = CURSOR... syntax, the cursor is created as GLOBAL or LOCAL, depending on the setting of the default to local cursor database option.

即使游标变量引用全局游标,它们也始终是局部变量。Cursor variables are always local, even if they reference a global cursor. 如果游标变量引用全局游标,则该游标既有全局游标引用,也有局部游标引用。When a cursor variable references a global cursor, the cursor has both a global and a local cursor reference. 有关详细信息,请参阅示例 C。For more information, see Example C.

有关详细信息,请参阅 DECLARE CURSOR (Transact-SQL)For more information, see DECLARE CURSOR (Transact-SQL).

可将复合赋值运算符用于存在赋值(在运算符的右侧有一个表达式,包括变量)的任何地方以及 UPDATE、SELECT 和 RECEIVE 语句的 SET 中。You can use the compound assignment operator anywhere you have an assignment with an expression on the right-hand side of the operator, including variables, and a SET in an UPDATE, SELECT, and RECEIVE statement.

不要在 SELECT 语句中使用变量来连接值(即,计算聚合值)。Don't use a variable in a SELECT statement to concatenate values (that is, to compute aggregate values). 可能发生了意外查询结果。Unexpected query results may occur. 因为,SELECT 列表中的所有表达式(包括赋值)不一定对于每个输出行仅执行一次。Because, all expressions in the SELECT list (including assignments) aren't necessarily run exactly once for each output row. 有关详细信息,请参阅此知识库文章For more information, see this KB article.

权限Permissions

要求具有 public 角色的成员身份。Requires membership in the public role. 所有用户都可以使用 SET @local_variable 。All users can use SET @local_variable.

示例Examples

A.A. 输出使用 SET 初始化的变量值Printing the value of a variable initialized by using SET

以下示例创建 @myvar 变量,将字符串值放入该变量,然后输出 @myvar 变量的值。The following example creates the @myvar variable, puts a string value into the variable, and prints the value of the @myvar variable.

DECLARE @myvar char(20);  
SET @myvar = 'This is a test';  
SELECT @myvar;  
GO  

B.B. 在 SELECT 语句中使用由 SET 赋值的局部变量Using a local variable assigned a value by using SET in a SELECT statement

以下示例创建一个名为 @state 的局部变量,并在 SELECT 语句中使用该局部变量来查找位于 Oregon 州的所有雇员的姓氏与名字。The following example creates a local variable named @state and uses the local variable in a SELECT statement to find the first and last names of all employees who live in the state of Oregon.

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

C.C. 为局部变量使用复合赋值Using a compound assignment for a local variable

以下两个示例将产生相同的结果。The following two examples produce the same result. 它们创建一个名为 @NewBalance 的局部变量,将其乘以 10 并在一个 SELECT 语句中显示该局部变量的新值。They create a local variable named @NewBalance, multiplies it by 10 and displays the new value of the local variable in a SELECT statement. 第二个示例使用一个复合赋值运算符。The second example uses a compound assignment operator.

/* Example one */  
DECLARE  @NewBalance  int ;  
SET  @NewBalance  =  10;  
SET  @NewBalance  =  @NewBalance  *  10;  
SELECT  @NewBalance;  
  
/* Example Two */  
DECLARE @NewBalance int = 10;  
SET @NewBalance *= 10;  
SELECT @NewBalance;  

D.D. 对全局游标使用 SETUsing SET with a global cursor

以下示例创建一个局部变量,然后将游标变量设置为全局游标名。The following example creates a local variable and then sets the cursor variable to the global cursor name.

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;   
--There is now only a LOCAL variable reference  
--(@my_variable) to the my_cursor cursor.  

E.E. 使用 SET 定义游标Defining a cursor by using SET

以下示例使用 SET 语句定义游标。The following example uses the SET statement to define a cursor.

DECLARE @CursorVar CURSOR;  
  
SET @CursorVar = CURSOR SCROLL DYNAMIC  
FOR  
SELECT LastName, FirstName  
FROM AdventureWorks2012.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;  

F.F. 通过查询赋值Assigning a value from a query

以下示例使用查询为变量赋值。The following example uses a query to assign a value to a variable.

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

G.G. 通过修改类型属性为用户定义类型的变量赋值Assigning a value to a user-defined type variable by modifying a property of the type

以下示例通过修改类型的 Point 属性的值来设置用户定义类型 X 的值。The following example sets a value for user-defined type Point by modifying the value of the property X of the type.

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

H.H. 通过调用类型的方法为用户定义类型的变量赋值Assigning a value to a user-defined type variable by invoking a method of the type

以下示例通过调用类型的 SetXY 方法设置用户定义类型 point 的值 。The following example sets a value for user-defined type point by invoking method SetXY of the type.

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

I.I. 为 CLR 类型创建变量并调用赋值函数方法Creating a variable for a CLR type and calling a mutator method

以下示例为 Point 类型创建变量,然后在 Point 中执行赋值函数方法。The following example creates a variable for the type Point, and then executes a mutator method in 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 (SQL DW)Azure Synapse Analytics (SQL DW)并行数据仓库Parallel Data WarehouseExamples: Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW) and 并行数据仓库Parallel Data Warehouse

J.J. 输出使用 SET 初始化的变量值Printing the value of a variable initialized by using SET

以下示例创建 @myvar 变量,将字符串值放入该变量,然后输出 @myvar 变量的值。The following example creates the @myvar variable, puts a string value into the variable, and prints the value of the @myvar variable.

DECLARE @myvar char(20);  
SET @myvar = 'This is a test';  
SELECT top 1 @myvar FROM sys.databases;  
  

K.K. 在 SELECT 语句中使用由 SET 赋值的局部变量Using a local variable assigned a value by using SET in a SELECT statement

以下示例创建一个名为 @dept 的局部变量,并在 SELECT 语句中使用该局部变量来查找 Marketing 部门的所有雇员的姓氏与名字。The following example creates a local variable named @dept and uses this local variable in a SELECT statement to find the first and last names of all employees who work in the Marketing department.

-- Uses AdventureWorks  
  
DECLARE @dept char(25);  
SET @dept = N'Marketing';  
SELECT RTRIM(FirstName) + ' ' + RTRIM(LastName) AS Name  
FROM DimEmployee   
WHERE DepartmentName = @dept;  

L.L. 为局部变量使用复合赋值Using a compound assignment for a local variable

以下两个示例将产生相同的结果。The following two examples produce the same result. 它们创建一个名为 @NewBalance 的局部变量,将其乘以 10 并在一个 SELECT 语句中显示该局部变量的新值。They create a local variable named @NewBalance, multiplies it by 10 and displays the new value of the local variable in a SELECT statement. 第二个示例使用一个复合赋值运算符。The second example uses a compound assignment operator.

/* Example one */  
DECLARE  @NewBalance  int ;  
SET  @NewBalance  =  10;  
SET  @NewBalance  =  @NewBalance  *  10;  
SELECT  TOP 1 @NewBalance FROM sys.tables;  
  
/* Example Two */  
DECLARE @NewBalance int = 10;  
SET @NewBalance *= 10;  
SELECT TOP 1 @NewBalance FROM sys.tables;  

M.M. 通过查询赋值Assigning a value from a query

以下示例使用查询为变量赋值。The following example uses a query to assign a value to a variable.

-- Uses AdventureWorks  
  
DECLARE @rows int;  
SET @rows = (SELECT COUNT(*) FROM dbo.DimCustomer);  
SELECT TOP 1 @rows FROM sys.tables;  

另请参阅See Also

复合运算符 (Transact-SQL) Compound Operators (Transact-SQL)
DECLARE @local_variable (Transact-SQL) DECLARE @local_variable (Transact-SQL)
EXECUTE (Transact-SQL) EXECUTE (Transact-SQL)
SELECT (Transact-SQL) SELECT (Transact-SQL)
SET 语句 (Transact-SQL)SET Statements (Transact-SQL)