DECLARE (Transact-SQL)DECLARE (Transact-SQL)

适用于: 是SQL Server(从 2008 开始)是Azure SQL 数据库是Azure SQL 数据仓库是并行数据仓库APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

变量是在批处理或过程的主体中用 DECLARE 语句声明的,并用 SET 或 SELECT 语句赋值。Variables are declared in the body of a batch or procedure with the DECLARE statement and are assigned values by using either a SET or SELECT statement. 游标变量可使用此语句声明,并可用于其他与游标相关的语句。Cursor variables can be declared with this statement and used with other cursor-related statements. 除非在声明中提供值,否则声明之后所有变量将初始化为 NULL。After declaration, all variables are initialized as NULL, unless a value is provided as part of the declaration.

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

语法Syntax

-- Syntax for SQL Server and Azure SQL Database  
  
DECLARE   
{   
    { @local_variable [AS] data_type  [ = value ] }  
  | { @cursor_variable_name CURSOR }  
} [,...n]   
| { @table_variable_name [AS] <table_type_definition> }   
  
<table_type_definition> ::=   
     TABLE ( { <column_definition> | <table_constraint> } [ ,...n] )   
  
<column_definition> ::=   
     column_name { scalar_data_type | AS computed_column_expression }  
     [ COLLATE collation_name ]   
     [ [ DEFAULT constant_expression ] | IDENTITY [ (seed ,increment ) ] ]   
     [ ROWGUIDCOL ]   
     [ <column_constraint> ]   
  
<column_constraint> ::=   
     { [ NULL | NOT NULL ]   
     | [ PRIMARY KEY | UNIQUE ]   
     | CHECK ( logical_expression )   
     | WITH ( <index_option > )  
     }   
  
<table_constraint> ::=   
     { { PRIMARY KEY | UNIQUE } ( column_name [ ,...n] )   
     | CHECK ( search_condition )   
     }   
  
<index_option> ::=  
See CREATE TABLE for index option syntax.  
  
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
  
DECLARE   
{{ @local_variable [AS] data_type } [ =value [ COLLATE <collation_name> ] ] } [,...n]  
  

参数Arguments

@local_variable@local_variable
变量的名称。Is the name of a variable. 变量名必须以 at 符 (@) 开头。Variable names must begin with an at (@) sign. 局部变量名称必须符合标识符规则。Local variable names must comply with the rules for identifiers.

data_typedata_type
任何系统提供的公共语言运行时 (CLR) 用户定义表类型或别名数据类型。Is any system-supplied, common language runtime (CLR) user-defined table type, or alias data type. 变量的数据类型不能为 text、ntext 或 image。A variable cannot be of text, ntext, or image data type.

有关系统数据类型的详细信息,请参阅数据类型 (Transact-SQL)For more information about system data types, see Data Types (Transact-SQL). 有关 CLR 用户定义类型或别名数据类型的详细信息,请参阅 CREATE TYPE (Transact-SQL)For more information about CLR user-defined types or alias data types, see CREATE TYPE (Transact-SQL).

=value=value
以内联方式为变量赋值。Assigns a value to the variable in-line. 值可以是常量或表达式,但它必须与变量声明类型匹配,或者可隐式转换为该类型。The value can be a constant or an expression, but it must either match the variable declaration type or be implicitly convertible to that type. 有关详细信息,请参阅表达式 (Transact-SQL)For more information, see Expressions (Transact-SQL).

@cursor_variable_name@cursor_variable_name
cursor 变量的名称。Is the name of a cursor variable. 游标变量名称必须以 at 符 (@) 开头,并符合有关标识符的规则。Cursor variable names must begin with an at (@) sign and conform to the rules for identifiers.

CURSORCURSOR
指定变量是局部游标变量。Specifies that the variable is a local cursor variable.

@table_variable_name@table_variable_name
表类型的变量的名称。Is the name of a variable of type table. 变量名称必须以 at 符 (@) 开头,并符合有关标识符的规则。Variable names must begin with an at (@) sign and conform to the rules for identifiers.

<table_type_definition><table_type_definition>
定义表数据类型。Defines the table data type. 表声明包括列定义、名称、数据类型和约束。The table declaration includes column definitions, names, data types, and constraints. 允许的约束类型只包括 PRIMARY KEY、UNIQUE、NULL 和 CHECK。The only constraint types allowed are PRIMARY KEY, UNIQUE, NULL, and CHECK. 如果类型绑定了规则或默认定义,则不能将别名数据类型用作列标量数据类型。An alias data type cannot be used as a column scalar data type if a rule or default definition is bound to the type.

<table_type_definiton> 是在 CREATE TABLE 中用于定义表的信息子集。<table_type_definiton> Is a subset of information used to define a table in CREATE TABLE. 其中包含了元素和主要定义。Elements and essential definitions are included here. 有关详细信息,请参阅 CREATE TABLE (Transact-SQL)For more information, see CREATE TABLE (Transact-SQL).

nn
指示可以指定多个变量并对变量赋值的占位符。Is a placeholder indicating that multiple variables can be specified and assigned values. 声明表变量时,表变量必须是 DECLARE 语句中声明的唯一变量。When declaring table variables, the table variable must be the only variable being declared in the DECLARE statement.

column_namecolumn_name
表中的列的名称。Is the name of the column in the table.

scalar_data_typescalar_data_type
指定列是标量数据类型。Specifies that the column is a scalar data type.

computed_column_expressioncomputed_column_expression
定义计算列值的表达式。Is an expression defining the value of a computed column. 计算列由同一表中的其他列通过表达式计算而得。It is computed from an expression using other columns in the same table. 例如,计算列可以定义为 cost、AS、price * qty。表达式可以是非计算列名称、常量、内置函数、变量,也可以是用一个或多个运算符连接的上述元素的任意组合。For example, a computed column can have the definition cost AS price * qty. The expression can be a noncomputed column name, constant, built-in function, variable, or any combination of these connected by one or more operators. 表达式不能为子查询或用户定义函数。The expression cannot be a subquery or a user-defined function. 表达式不能引用 CLR 用户定义类型。The expression cannot reference a CLR user-defined type.

[ COLLATE collation_name][ COLLATE collation_name]
指定列的排序规则。Specifies the collation for the column. collation_name 可以是 Windows 排序规则名称或 SQL 排序规则名称。只适用于 char、varchar、text、nchar、nvarchar 和 ntext 等数据类型列。collation_name can be either a Windows collation name or an SQL collation name, and is applicable only for columns of the char, varchar, text, nchar, nvarchar, and ntext data types. 如果未指定,则该列的排序规则是用户定义数据类型的排序规则(如果列为用户定义数据类型)或当前数据库的排序规则。If not specified, the column is assigned either the collation of the user-defined data type (if the column is of a user-defined data type) or the collation of the current database.

有关 Windows 和 SQL 排序规则名称的详细信息,请参阅 COLLATE (Transact-SQL)For more information about the Windows and SQL collation names, see COLLATE (Transact-SQL).

DEFAULTDEFAULT
如果在插入过程中未显式提供值,则指定为列提供的值。Specifies the value provided for the column when a value is not explicitly supplied during an insert. DEFAULT 定义可适用于除定义为 timestamp 或带 IDENTITY 属性的列以外的任何列。DEFAULT definitions can be applied to any columns except those defined as timestamp or those with the IDENTITY property. 删除表时,将删除 DEFAULT 定义。DEFAULT definitions are removed when the table is dropped. 只有常量值(如字符串)、系统函数(如 SYSTEM_USER())或 NULL 可用作默认参数。Only a constant value, such as a character string; a system function, such as a SYSTEM_USER(); or NULL can be used as a default. 为了与 SQL ServerSQL Server 的早期版本兼容,可以为 DEFAULT 分配约束名称。To maintain compatibility with earlier versions of SQL ServerSQL Server, a constraint name can be assigned to a DEFAULT.

constant_expressionconstant_expression
用作列的默认值的常量、NULL 或系统函数。Is a constant, NULL, or a system function used as the default value for the column.

IDENTITYIDENTITY
指示新列是标识列。Indicates that the new column is an identity column. 在表中添加新行时,SQL ServerSQL Server 将为列提供一个唯一的增量值。When a new row is added to the table, SQL ServerSQL Server provides a unique incremental value for the column. 标识列通常与 PRIMARY KEY 约束一起使用,作为表的唯一行标识符。Identity columns are commonly used in conjunction with PRIMARY KEY constraints to serve as the unique row identifier for the table. 可以将 IDENTITY 属性分配到 tinyint、smallint、int、decimal(p,0) 或 numeric(p,0) 列。The IDENTITY property can be assigned to tinyint, smallint, int, decimal(p,0), or numeric(p,0) columns. 每个表只能创建一个标识列。Only one identity column can be created per table. 不能对标识列使用绑定默认值和 DEFAULT 约束。Bound defaults and DEFAULT constraints cannot be used with an identity column. 必须同时指定种子和增量,或者都不指定。You must specify both the seed and increment, or neither. 如果二者都未指定,则取默认值 (1,1)。If neither is specified, the default is (1,1).

seedseed
是装入表的第一行所使用的值。Is the value used for the very first row loaded into the table.

incrementincrement
添加到以前装载的列标识值的增量值。Is the incremental value added to the identity value of the previous row that was loaded.

ROWGUIDCOLROWGUIDCOL
指示新列是行的全局唯一标识符列。Indicates that the new column is a row global unique identifier column. 对于每个表,只能将其中的一个 uniqueidentifier 列指定为 ROWGUIDCOL 列。Only one uniqueidentifier column per table can be designated as the ROWGUIDCOL column. ROWGUIDCOL 属性只能分配给 uniqueidentifier 列。The ROWGUIDCOL property can be assigned only to a uniqueidentifier column.

NULL | NOT NULLNULL | NOT NULL
指示变量中是否允许使用 Null。Indicates if null is allowed in the variable. 默认值为 NULL。The default is NULL.

PRIMARY KEYPRIMARY KEY
通过唯一索引对给定的一列或多列强制实现实体完整性的约束。Is a constraint that enforces entity integrity for a given column or columns through a unique index. 每个表只能创建一个 PRIMARY KEY 约束。Only one PRIMARY KEY constraint can be created per table.

UNIQUEUNIQUE
通过唯一索引为给定的一列或多列提供实体完整性的约束。Is a constraint that provides entity integrity for a given column or columns through a unique index. 一个表可以有多个 UNIQUE 约束。A table can have multiple UNIQUE constraints.

CHECKCHECK
一个约束,该约束通过限制可输入一列或多列中的可能值来强制实现域完整性。Is a constraint that enforces domain integrity by limiting the possible values that can be entered into a column or columns.

logical_expressionlogical_expression
返回 TRUE 或 FALSE 的逻辑表达式。Is a logical expression that returns TRUE or FALSE.

RemarksRemarks

变量常用在批处理或过程中,作为 WHILE、LOOP 或 IF...ELSE 块的计数器。Variables are often used in a batch or procedure as counters for WHILE, LOOP, or for an IF...ELSE block.

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

局部变量的作用域是其被声明时所在批处理。The scope of a local variable is the batch in which it is declared.

表变量不一定是内存驻留。A table variable is not necessarily memory resident. 在内存压力下,可以将属于表变量的页推送到 tempdb。Under memory pressure, the pages belonging to a table variable can be pushed out to tempdb.

当前分配有游标的游标变量可在下列语句中作为源引用:A cursor variable that currently has a cursor assigned to it can be referenced as a source in a:

  • CLOSE 语句。CLOSE statement.

  • DEALLOCATE 语句。DEALLOCATE statement.

  • FETCH 语句。FETCH statement.

  • OPEN 语句。OPEN statement.

  • 定位的 DELETE 或 UPDATE 语句。Positioned DELETE or UPDATE statement.

  • SET CURSOR 变量语句(在右侧)。SET CURSOR variable statement (on the right side).

在所有上述语句中,如果存在被引用的游标变量,但是不具有当前分配给它的游标,那么 SQL ServerSQL Server 将引发错误。In all of these statements, SQL ServerSQL Server raises an error if a referenced cursor variable exists but does not have a cursor currently allocated to it. 如果不存在被引用的游标变量,SQL ServerSQL Server 将引发与其他类型的未声明变量引发的错误相同的错误。If a referenced cursor variable does not exist, SQL ServerSQL Server raises the same error raised for an undeclared variable of another type.

游标变量:A cursor variable:

  • 可以是游标类型或其他游标变量的目标。Can be the target of either a cursor type or another cursor variable. 有关详细信息,请参阅 SET @local_variable (Transact-SQL)For more information, see SET @local_variable (Transact-SQL).

  • 如果当前没有给游标变量分配游标,则可在 EXECUTE 语句中作为输出游标参数的目标引用。Can be referenced as the target of an output cursor parameter in an EXECUTE statement if the cursor variable does not have a cursor currently assigned to it.

  • 应被看作是指向游标的指针。Should be regarded as a pointer to the cursor.

示例Examples

A.A. 使用 DECLAREUsing DECLARE

下例将使用名为 @find 的局部变量检索所有姓氏以 Man 开头的联系人信息。The following example uses a local variable named @find to retrieve contact information for all last names beginning with Man.

USE AdventureWorks2012;  
GO  
DECLARE @find varchar(30);   
/* Also allowed:   
DECLARE @find varchar(30) = 'Man%';   
*/  
SET @find = 'Man%';   
SELECT p.LastName, p.FirstName, ph.PhoneNumber  
FROM Person.Person AS p   
JOIN Person.PersonPhone AS ph ON p.BusinessEntityID = ph.BusinessEntityID  
WHERE LastName LIKE @find;  
  

下面是结果集:Here is the result set.

LastName            FirstName               Phone
------------------- ----------------------- -------------------------
Manchepalli         Ajay                    1 (11) 500 555-0174
Manek               Parul                   1 (11) 500 555-0146
Manzanares          Tomas                   1 (11) 500 555-0178
 
(3 row(s) affected)

B.B. 在 DECLARE 中使用两个变量Using DECLARE with two variables

下例将检索北美销售区中年销售额至少为 $2,000,000 的 Adventure Works CyclesAdventure Works Cycles 销售代表的名字。The following example retrieves the names of Adventure Works CyclesAdventure Works Cycles sales representatives who are located in the North American sales territory and have at least $2,000,000 in sales for the year.

USE AdventureWorks2012;  
GO  
SET NOCOUNT ON;  
GO  
DECLARE @Group nvarchar(50), @Sales money;  
SET @Group = N'North America';  
SET @Sales = 2000000;  
SET NOCOUNT OFF;  
SELECT FirstName, LastName, SalesYTD  
FROM Sales.vSalesPerson  
WHERE TerritoryGroup = @Group and SalesYTD >= @Sales;  

C.C. 声明一个表类型的变量Declaring a variable of type table

下例将创建一个 table 变量,用于储存 UPDATE 语句的 OUTPUT 子句中指定的值。The following example creates a table variable that stores the values specified in the OUTPUT clause of the UPDATE statement. 在它后面的两个 SELECT 语句返回 @MyTableVar 中的值以及 Employee 表中更新操作的结果。Two SELECT statements follow that return the values in @MyTableVar and the results of the update operation in the Employee table. 请注意,INSERTED.ModifiedDate 列中的结果与 Employee 表的 ModifiedDate 列中的值不同。Note that the results in the INSERTED.ModifiedDate column differ from the values in the ModifiedDate column in the Employee table. 这是因为对 AFTER UPDATE 表定义了 ModifiedDate 触发器,该触发器可以将 Employee 的值更新为当前日期。This is because the AFTER UPDATE trigger, which updates the value of ModifiedDate to the current date, is defined on the Employee table. 不过,从 OUTPUT 返回的列可反映触发器激发之前的数据。However, the columns returned from OUTPUT reflect the data before triggers are fired. 有关详细信息,请参阅 OUTPUT 子句 (Transact-SQL)For more information, see OUTPUT Clause (Transact-SQL).

USE AdventureWorks2012;  
GO  
DECLARE @MyTableVar table(  
    EmpID int NOT NULL,  
    OldVacationHours int,  
    NewVacationHours int,  
    ModifiedDate datetime);  
UPDATE TOP (10) HumanResources.Employee  
SET VacationHours = VacationHours * 1.25   
OUTPUT INSERTED.BusinessEntityID,  
       DELETED.VacationHours,  
       INSERTED.VacationHours,  
       INSERTED.ModifiedDate  
INTO @MyTableVar;  
--Display the result set of the table variable.  
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate  
FROM @MyTableVar;  
GO  
--Display the result set of the table.  
--Note that ModifiedDate reflects the value generated by an  
--AFTER UPDATE trigger.  
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate  
FROM HumanResources.Employee;  
GO  

D.D. 声明一个用户定义表类型的变量Declaring a variable of user-defined table type

下面的示例将创建一个名为 @LocationTVP 的表值参数或表变量。The following example creates a table-valued parameter or table variable called @LocationTVP. 这需要使用一个相应的名为 LocationTableType 的用户定义表类型。This requires a corresponding user-defined table type called LocationTableType. 有关如何创建用户定义表类型的详细信息,请参阅 CREATE TYPE (Transact-SQL)For more information about how to create a user-defined table type, see CREATE TYPE (Transact-SQL). 有关表值参数的详细信息,请参阅使用表值参数(数据引擎)For more information about table-valued parameters, see Use Table-Valued Parameters (Database Engine).

DECLARE @LocationTVP   
AS LocationTableType;  

示例:Azure SQL 数据仓库Azure SQL Data Warehouse并行数据仓库Parallel Data WarehouseExamples: Azure SQL 数据仓库Azure SQL Data Warehouse and 并行数据仓库Parallel Data Warehouse

E.E. 使用 DECLAREUsing DECLARE

下例将使用名为 @find 的局部变量检索所有姓氏以 Walt 开头的联系人信息。The following example uses a local variable named @find to retrieve contact information for all last names beginning with Walt.

-- Uses AdventureWorks  
  
DECLARE @find varchar(30);  
/* Also allowed:   
DECLARE @find varchar(30) = 'Man%';  
*/  
SET @find = 'Walt%';  
  
SELECT LastName, FirstName, Phone  
FROM DimEmployee   
WHERE LastName LIKE @find;  

F.F. 在 DECLARE 中使用两个变量Using DECLARE with two variables

以下示例检索使用变量来指定 DimEmployee 表中的第一个和最后一个雇员名称。The following example retrieves uses variables to specify the first and last names of employees in the DimEmployee table.

-- Uses AdventureWorks  
  
DECLARE @lastName varchar(30), @firstName varchar(30);  
  
SET @lastName = 'Walt%';  
SET @firstName = 'Bryan';  
  
SELECT LastName, FirstName, Phone  
FROM DimEmployee   
WHERE LastName LIKE @lastName AND FirstName LIKE @firstName;  

另请参阅See Also

EXECUTE (Transact-SQL) EXECUTE (Transact-SQL)
内置函数 (Transact-SQL) Built-in Functions (Transact-SQL)
SELECT (Transact-SQL) SELECT (Transact-SQL)
表 (Transact-SQL) table (Transact-SQL)
类型化的 XML 与非类型化的 XML 的比较Compare Typed XML to Untyped XML