SET @local_variable (Transact-SQL)

發行項
10/16/2024

適用於：SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics Analytics Platform System (PDW) Microsoft Fabric 的 SQL 端點分析 Microsoft Fabric 的倉儲

將先前使用 DECLARE @local_variable 語句建立的指定局部變數設定為指定的值。

Transact-SQL 語法慣例

Syntax

SQL Server、Azure SQL Database、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網狀架構的語法：

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

引數

@local_variable

除了 cursor、text、ntext、image 或 table 以外的任何類型變數名稱。變數名稱的開頭必須是 at 記號 ( @ )。變數名稱必須遵照識別碼的規則。

property_name

使用者定義型別的屬性。

field_name

使用者定義型別的公用欄位。

udt_name

Common Language Runtime (CLR) 使用者定義型別的名稱。

{ . |:: }

指定 CLR 使用者自訂類型的方法。針對實例（非靜態）方法，請使用句號（.）。如果是靜態方法，請使用兩個冒號（::）。若要叫用 CLR 使用者自訂類型的方法、屬性或欄位，您必須具有類型的 EXECUTE 權限。

method_name （ argument [ ,... n ]

利用一個或多個引數來修改類型執行個體狀態的使用者定義型別方法。靜態方法必須是公用的。

@SQLCLR_local_variable

類型位於組件中的變數。如需詳細資訊，請參閱 Common Language Runtime （CLR）整合程式設計概念。

mutator_method

組件中可以變更物件狀態的方法。 SQLMethodAttribute.IsMutator 會套用到這個方法。

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

複合指派運算子：

+= - 新增和指派
-= - 減去並指派
*= - 乘以並指派
/= - 分割和指派
%= - 模數並指派
&= - 位 AND 和指派
^= - 位 XOR 和指派
|= - 位 OR 和指派

expression

任何有效的運算式。

cursor_variable

資料指標變數的名稱。如果目標資料指標先前參考不同的資料指標，就會移除先前的參考。

cursor_name

使用 DECLARE CURSOR 語句宣告的數據指標名稱。

CURSOR

指定 SET 語句包含數據指標的宣告。

SCROLL

指定資料指標支援所有擷取選項：FIRST、LAST、、NEXTPRIOR、、 RELATIVE和 ABSOLUTE。當您也指定 SCROLL 時，您無法指定 FAST_FORWARD。

FORWARD_ONLY

指定數據指標只 FETCH NEXT 支援選項。您只能依單一方向，從第一個到最後一個資料列擷取資料指標。當您指定 FORWARD_ONLY 不含 STATIC、 KEYSET或 DYNAMIC 關鍵字時，資料指標會實作為 DYNAMIC。如果您未指定 FORWARD_ONLY 或 SCROLL， FORWARD_ONLY 則為預設值，除非您指定關鍵詞 STATIC、 KEYSET或 DYNAMIC。針對 STATIC、 KEYSET和 DYNAMIC 資料指標， SCROLL 是預設值。

STATIC

定義一個資料指標，它會建立資料暫存複本供資料指標本身使用。對數據指標的所有要求都會從中的 tempdb這個臨時表回答。因此，資料指標開啟後對基底資料表所做的修改，不會反映在資料指標提取所傳回的資料中。而且，這個資料指標不支援修改。

KEYSET

指定在開啟資料指標時，修正資料指標中之資料列的成員資格和順序。唯一識別數據列的索引鍵集合內建在中可 tempdb設定的索引鍵。當資料指標擁有者捲動資料指標時，基底資料表中非索引鍵值的變更 (不論是資料指標擁有者所做的變更還是其他使用者所認可的變更) 都是可見的。其他使用者的插入便不可見，且無法透過 Transact-SQL 伺服器資料指標來插入。

如果刪除資料列，嘗試擷取資料列會 @@FETCH_STATUS 傳回的 -2。從資料指標之外更新索引鍵值，類似於先刪除舊資料列，再插入新資料列。看不到具有新值的數據列，並嘗試擷取具有舊值的數據列會傳回 @@FETCH_STATUS 的 -2。如果更新透過數據指標 WHERE CURRENT OF 指定子句，就會顯示新的值。

DYNAMIC

定義一個資料指標，使資料指標擁有者捲動資料指標時，資料指標能夠反映結果集資料列的所有資料變更。每次提取時，資料列的資料值、順序和成員資格都有可能改變。動態資料指標不支援絕對和相對提取選項。

FAST_FORWARD

指定FORWARD_ONLYREAD_ONLY已啟用優化的數據指標。 FAST_FORWARD 當同時指定時 SCROLL ，無法指定。

READ_ONLY

防止利用這個資料指標來更新。數據指標無法在或 DELETE 語句的 UPDATE 子句中WHERE CURRENT OF參考。這個選項會覆寫要更新之資料指標的預設功能。

SCROLL LOCKS

指定藉由資料指標進行的定位更新或刪除一定會成功。當資料列被讀入資料指標時，SQL Server 會鎖定這些資料列，以確保之後可予以修改。您無法指定 SCROLL_LOCKS 同時指定何時 FAST_FORWARD 。

OPTIMISTIC

指定如果將資料列讀入資料指標之後，又更新了這些資料列，則透過資料指標來進行的定位更新或刪除不會成功。當資料列被讀入資料指標時，SQL Server 不會鎖定資料列。相反地，它會利用 timestamp 資料行值的比較 (如果資料表沒有 timestamp 資料行，便會利用總和檢查碼值) 來判斷資料列讀入資料指標之後，是否會修改資料列。如果修改了資料列，試圖執行的定位更新或刪除便會失敗。您無法指定 OPTIMISTIC 同時指定何時 FAST_FORWARD 。

TYPE_WARNING

指定當資料指標從要求的類型隱含地轉換成另一個類型時，便傳送一則警告訊息給用戶端。

FOR select_statement

SELECT定義數據指標結果集的標準語句。在資料指標宣告的select_statement內不允許關鍵字FOR BROWSE和 INTO 。

如果您使用、、或，或您在select_list中包含匯總運算式，則數據指標會建立為。UNIONHAVINGGROUP BYDISTINCTSTATIC

如果每個基礎表沒有唯一索引和 ISO SCROLL 數據指標，或要求 Transact-SQL KEYSET 數據指標，則數據指標會自動成為 STATIC 數據指標。

如果 select_statement 包含數據行不是唯一 ORDER BY 數據列標識符的子句， DYNAMIC 則數據指標會 KEYSET 轉換成數據指標，或 STATIC 無法開啟數據指標時 KEYSET 轉換成數據指標。此程式也會針對使用 ISO 語法所定義的數據指標進行，但不含 STATIC 關鍵詞。

READ ONLY

防止利用這個資料指標來更新。數據指標無法在或 DELETE 語句的 UPDATE 子句中WHERE CURRENT OF參考。這個選項會覆寫要更新之資料指標的預設功能。這個關鍵詞會因READ_ONLY在和 ONLY之間READ具有空格而非底線而有所不同。

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

在資料指標內定義可更新的資料行。如果提供了 OF <column_name> [ , ...n ]，便只允許修改列出的資料行。未提供任何清單時，除非數據指標定義為 READ_ONLY，否則所有數據行都可以更新。

備註

宣告變數之後，它會初始化為 NULL。 SET使用語句將不是NULL宣告變數的值指派給。將 SET 值指派給變數的語句會傳回單一值。當您初始化多個變數時，請針對每個局部變數使用不同的 SET 語句。

您只能在運算式中使用變數，而不能使用物件名稱或關鍵字。若要建構動態 Transact-SQL 語句，請使用 EXECUTE。

雖然的語法規則包含 SET @cursor_variable LOCAL 和 GLOBAL 關鍵詞，但當您使用 SET @cursor_variable = CURSOR... 語法時，數據指標會建立為 GLOBAL 或 LOCAL，視預設為本機數據指標資料庫選項的設定而定。

資料指標變數一律是區域變數，即使它們參考了全域資料指標也是如此。當資料指標變數參考全域資料指標時，資料指標會同時有全域和本機資料指標參考。如需詳細資訊，請參閱範例 D，搭配全域數據指標使用 SET。

如需詳細資訊，請參閱 DECLARE CURSOR (Transact-SQL)。

您可以在任何位置使用複合指派運算子，在運算子右側具有表示式，包括變數，以及 SET 、 SELECT和 RECEIVE 語句中的 UPDATE。

請勿在語句中使用 SELECT 變數來串連值（也就是計算匯總值）。可能會發生非預期的查詢結果，因為清單中的所有運算式 SELECT （包括指派）不一定只針對每個輸出數據列執行一次。如需詳細資訊，請參閱 KB 287515。

權限

需要 public 角色的成員資格。所有使用者都可以使用 SET @local_variable。

範例

本文 Transact-SQL 程式碼範例使用 AdventureWorks2022 或 AdventureWorksDW2022 範例資料庫，從 Microsoft SQL Server Samples 和 Community Projects （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. 藉由叫用型別的方法，將值指派給使用者定義型別變數

下列範例會叫用使用者定義型別 point 的 SetXY 方法來設定該型別的值。

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

I. 建立 CLR 類型的變數，並呼叫 Mutator 方法

下列範例會建立 Point 類型的變數，然後在 Point 中執行 mutator 方法。

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)

J. 使用 SET 列印初始化的變數值