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

適用於: 是SQL Server 是Azure SQL Database 是Azure Synapse Analytics (SQL DW) 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) 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
這是任何系統提供的 Common Language Runtime (CLR) 使用者定義資料表類型或別名資料類型。Is any system-supplied, common language runtime (CLR) user-defined table type, or alias data type. 變數的資料類型不可以是 textntextimageA 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
這是資料指標變數的名稱。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
這是 table 類型的變數名稱。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>
定義 table 資料類型。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. 當宣告 table 變數時,table 變數必須是 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 定序名稱,而且只適用於 charvarchartextncharnvarcharntext 等資料類型的資料行。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. 除了定義為 timestamp 或含有 IDENTITY 屬性的資料行之外,任何資料行都可以套用 DEFAULT 定義。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 屬性指派給 tinyintsmallintintdecimal(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). 如需詳細資訊,請參閱使用資料表值參數 (Database Engine)For more information about table-valued parameters, see Use Table-Valued Parameters (Database Engine).

DECLARE @LocationTVP   
AS LocationTableType;  

範例: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

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)
table (Transact-SQL) table (Transact-SQL)
比較具類型的 XML 與不具類型的 XMLCompare Typed XML to Untyped XML