共用方式為


SET @local_variable (Transact-SQL)

將先前利用 DECLARE @local\_variable 陳述式來建立的指定本機變數設為指定的值。

主題連結圖示Transact-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 [ 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 ] ] } ] 
      } 
    }
} 

引數

  • @local_variable
    這是 cursor、text、ntext、image 或 table 以外任何類型的變數名稱。變數名稱的開頭必須是 @ 記號 (
    @
    )。變數名稱必須符合識別碼的規則。

  • 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、NEXT、PRIOR、RELATIVE 和 ABSOLUTE。當您也指定了 FAST_FORWARD 時,便不能指定 SCROLL。

  • FORWARD_ONLY
    指定資料指標只支援 FETCH NEXT 選項。您只能依單一方向,從第一個到最後一個資料列擷取資料指標。當指定不含 STATIC、KEYSET 或 DYNAMIC 等關鍵字的 FORWARD_ONLY 時,會將資料指標實作成 DYNAMIC。當 FORWARD_ONLY 和 SCROLL 兩者都沒有指定時,除非指定了 STATIC、KEYSET 或 DYNAMIC 等關鍵字,否則,預設值是 FORWARD_ONLY。如果是 STATIC、KEYSET 和 DYNAMIC 資料指標,預設值便是 SCROLL。

    [!附註]

    在 SQL Server 2000 中,FAST_FORWARD 和 FORWARD_ONLY 資料指標選項互斥。如果指定了其中一個選項,便不能指定另一個選項,否則,會引發錯誤。可以在相同的 DECLARE CURSOR 陳述式中使用這兩個關鍵字。

  • STATIC
    定義一個建立資料暫存副本供資料指標本身使用的資料指標。對於這個資料指標的所有要求都是從 tempdb 的這份暫存資料表回答的;因此,修改基底資料表不會反映在提取這個資料指標所傳回的資料中,而且這個資料指標不允許進行修改。

  • KEYSET
    指定在開啟資料指標時,修正資料指標中之資料列的成員資格和順序。用來唯一識別資料列的索引鍵組會內建在 tempdb 的 keyset 資料表中。當資料指標擁有者捲動資料指標時,基底資料表中非索引鍵的變更,不論是資料指標擁有者所進行的變更,或其他使用者所認可的變更,都是可見的。其他使用者進行的插入便看不到,且無法利用 Transact-SQL 伺服器資料指標來插入。

    如果刪除某個資料列,嘗試提取此資料列的動作會傳回 @@FETCH_STATUS 的值 -2。從資料指標之外更新索引鍵值,類似於先刪除舊資料列,再插入新資料列。含有新值的資料列不可見,試圖提取有舊值的資料列會傳回 @@FETCH_STATUS 的值 -2。如果更新是藉由指定 WHERE CURRENT OF 子句透過資料指標執行,便可看到新值。

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

  • FAST_FORWARD
    指定啟用了最佳化的 FORWARD_ONLY、READ_ONLY 資料指標。當您也指定了 SCROLL 時,便不能指定 FAST_FORWARD。

    [!附註]

    在 SQL Server 2000 中,FAST_FORWARD 和 FORWARD_ONLY 資料指標選項互斥。如果指定了其中一個選項,便不能指定另一個選項,否則,會引發錯誤。可以在相同的 DECLARE CURSOR 陳述式中使用這兩個關鍵字。

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

  • SCROLL LOCKS
    指定保證利用資料指標來進行的定位更新或刪除會成功。將資料列讀入資料指標時,SQL Server 會鎖定這些資料列,以保證後來的修改動作能夠使用它們。當您也指定了 FAST_FORWARD 時,便不能指定 SCROLL_LOCKS。

  • OPTIMISTIC
    指定如果將資料列讀入資料指標之後,又更新了這些資料列,則利用資料指標來進行的定位更新或刪除不會成功。將資料列讀入資料指標時,SQL Server 不會鎖定這些資料列。但是,它會利用 timestamp 資料行值的比較 (如果資料表沒有 timestamp 資料行,便會利用總和檢查碼值) 來判斷資料列讀入資料指標之後,是否修改資料列。如果修改了資料列,嘗試執行的定位更新或刪除便會失敗。當您也指定了 FAST_FORWARD 時,便不能指定 OPTIMISTIC。

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

  • FOR select_statement
    這是定義資料指標結果集的標準 SELECT 陳述式。在資料指標宣告的 select_statement 內,不允許使用 COMPUTE、COMPUTE BY、FOR BROWSE 和 INTO 等關鍵字。

    如果使用 DISTINCT、UNION、GROUP BY 或 HAVING,或 select_list 包括彙總運算式,就會將資料指標建立成 STATIC。

    如果每個基礎資料表都沒有唯一索引,且要求 ISO SCROLL 資料指標或 Transact-SQL KEYSET 資料指標,它會自動成為 STATIC 資料指標。

    如果 select_statement 包含 ORDER BY 子句,且該子句中的資料行不是唯一資料列識別碼,就會將 DYNAMIC 資料指標轉換成 KEYSET 資料指標,如果無法開啟 KEYSET 資料指標,便會轉換成 STATIC 資料指標。使用 ISO 語法所定義的資料指標也是如此,但不含 STATIC 關鍵字。

  • READ ONLY
    防止利用這個資料指標來更新。UPDATE 或 DELETE 陳述式中的 WHERE CURRENT OF 子句無法參考這個資料指標。這個選項會覆寫要更新之資料指標的預設功能。這個關鍵字有別於先前的 READ_ONLY,READ 和 ONLY 之間是空格,而不是底線。

  • 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,這會隨著 default to local cursor 資料庫選項的設定而不同。

資料指標變數一律是區域變數,即使它們參考了全域資料指標也是如此。當資料指標變數參考全域資料指標時,資料指標會同時有全域和本機資料指標參考。如需詳細資訊,請參閱「範例 C」一節。

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

複合指派運算子可用於您在運算子右手邊有指派運算式的任何地方,其中包括變數以及 UPDATE、SELECT 和 RECEIVE 陳述式中的 SET。

權限

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

範例

A. 列印利用 SET 來初始化的變數值

下列範例會建立 @myvar 變數,將字串值放入這個變數中,再列印 @myvar 變數的值。

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

B. 使用以 SELECT 陳述式中的 SET 指派值的本機變數

下列範例會建立一個名稱為 @state 的本機變數,且會在 SELECT 陳述式中利用這個本機變數來尋找在 Oregon 州的所有員工的姓名。

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

C. 針對本機變數使用複合指派

下列兩個範例會產生相同的結果。它們會建立一個名為 @NewBalance 的區域變數,並將它乘以 10,然後將區域變數的新值顯示在 SELECT 陳述式中。第二個範例會使用複合指派運算子。

/* 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. 搭配全域資料指標來使用 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; 
--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 AdventureWorks.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. 從查詢中指派值

下列範例會利用查詢來指派變數值。

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

G. 修改此類型的屬性來指派使用者定義類型的變數值

下列範例會修改類型之 X 屬性的值來設定使用者定義型別 Point 的值。

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

H. 叫用此類型的方法來指派使用者定義類型的變數值

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

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);

變更記錄

更新的內容

@SQLCLR_local_variable.mutator_method 新增至<語法>一節,並將變數的說明新增至<引數>一節。

已新增範例 I。