4.4　變量

變量可以保存查詢之后的結(jié)果，可以在查詢語(yǔ)句中使用變量，也可以將變量中的值插入到數(shù)據(jù)表中，在T-SQL中變量的使用非常靈活方便，可以在任何T-SQL語(yǔ)句集合中聲明使用，根據(jù)其生命周期，可以分為全局變量和局部變量。

4.4.1　全局變量

全局變量是SQL Server系統(tǒng)提供的內(nèi)部使用的變量，其作用范圍并不僅僅局限于某一程序，而是任何程序均可以隨時(shí)調(diào)用。全局變量通常存儲(chǔ)一些SQL Server的配置設(shè)定值和統(tǒng)計(jì)數(shù)據(jù)。用戶可以在程序中用全局變量來(lái)測(cè)試系統(tǒng)的設(shè)定值或者是Transact-SQL命令執(zhí)行后的狀態(tài)值。在使用全局變量時(shí)應(yīng)注意以下幾點(diǎn)。

全局變量不是由用戶的程序定義的，它們是在服務(wù)器級(jí)定義的。用戶只能使用預(yù)先定義的全局變量，而不能修改全局變量。引用全局變量時(shí)，必須以標(biāo)記符“@@”開(kāi)頭。

SQL Server 2016中包含的全局變量及其含義如下：

• @@CONNECTIONS：返回SQL Server自上次啟動(dòng)以來(lái)嘗試的連接數(shù)，無(wú)論連接是成功還是失敗。
• @@CPU_BUSY：返回SQL Server自上次啟動(dòng)后的工作時(shí)間。其結(jié)果以CPU時(shí)間增量或“滴答數(shù)”表示，此值為所有CPU時(shí)間的累積，因此，可能會(huì)超出實(shí)際占用的時(shí)間，乘以@@TIMETICKS即可轉(zhuǎn)換為微秒。
• @@CURSOR_ROWS：返回連接上打開(kāi)的上一個(gè)游標(biāo)中的當(dāng)前限定行的數(shù)目。為了提高性能，SQL Server可異步填充大型鍵集和靜態(tài)游標(biāo)，可調(diào)用@@CURSOR_ROWS以確定當(dāng)其被調(diào)用時(shí)檢索游標(biāo)符合條件的行數(shù)。
• @@DATEFIRST：針對(duì)會(huì)話返回SET DATEFIRST的當(dāng)前值。
• @@DBTS：返回當(dāng)前數(shù)據(jù)庫(kù)的當(dāng)前timestamp數(shù)據(jù)類型的值。這一時(shí)間戳值在數(shù)據(jù)庫(kù)中必須是唯一的。
• @@ERROR：返回執(zhí)行的上一個(gè)Transact-SQL語(yǔ)句的錯(cuò)誤號(hào)。
• @@FETCH_STATUS：返回針對(duì)連接當(dāng)前打開(kāi)的任何游標(biāo)，發(fā)出的上一條游標(biāo)FETCH語(yǔ)句的狀態(tài)。
• @@IDENTITY：返回插入到表的IDENTITY列的最后一個(gè)值。
• @@IDLE：返回SQL Server自上次啟動(dòng)后的空閑時(shí)間。結(jié)果以CPU時(shí)間增量或“時(shí)鐘周期”表示，并且是所有CPU的累積，因此該值可能超過(guò)實(shí)際經(jīng)過(guò)的時(shí)間，乘以@@TIMETICKS即可轉(zhuǎn)換為微秒。
• @@IO_BUSY：返回自從SQL Server最近一次啟動(dòng)以來(lái)，SQL Server已經(jīng)用于執(zhí)行輸入和輸出操作的時(shí)間。其結(jié)果是CPU時(shí)間增量（時(shí)鐘周期），并且是所有CPU的累積值，所以，它可能超過(guò)實(shí)際消逝的時(shí)間，乘以@@TIMETICKS即可轉(zhuǎn)換為微秒。
• @@LANGID：返回當(dāng)前使用的語(yǔ)言的本地語(yǔ)言標(biāo)識(shí)符（ID）。
• @@LANGUAGE：返回當(dāng)前所用語(yǔ)言的名稱。
• @@LOCK_TIMEOUT：返回當(dāng)前會(huì)話的當(dāng)前鎖定超時(shí)設(shè)置（毫秒）。
• @@MAX_CONNECTIONS：返回SQL Server實(shí)例允許同時(shí)進(jìn)行的最大用戶連接數(shù)。返回的數(shù)值不一定是當(dāng)前配置的數(shù)值。
• @@MAX_PRECISION：按照服務(wù)器中的當(dāng)前設(shè)置，返回decimal和numeric數(shù)據(jù)類型所用的精度級(jí)別。默認(rèn)情況下，最大精度返回38。
• @@NESTLEVEL：返回對(duì)本地服務(wù)器上執(zhí)行的當(dāng)前存儲(chǔ)過(guò)程的嵌套級(jí)別（初始值為0）。
• @@OPTIONS：返回有關(guān)當(dāng)前SET選項(xiàng)的信息。
• @@PACK_RECEIVED：返回SQL Server自上次啟動(dòng)后從網(wǎng)絡(luò)讀取的輸入數(shù)據(jù)包數(shù)。
• @@PACK_SENT：返回SQL Server自上次啟動(dòng)后寫(xiě)入網(wǎng)絡(luò)的輸出數(shù)據(jù)包個(gè)數(shù)。
• @@PACKET_ERRORS：返回自上次啟動(dòng)SQL Server后，在SQL Server連接上發(fā)生的網(wǎng)絡(luò)數(shù)據(jù)包錯(cuò)誤數(shù)。
• @@ROWCOUNT：返回上一次語(yǔ)句影響的數(shù)據(jù)行的行數(shù)。
• @@PROCID：返回Transact-SQL當(dāng)前模塊的對(duì)象標(biāo)識(shí)符（ID）。Transact-SQL模塊可以是存儲(chǔ)過(guò)程、用戶定義函數(shù)或觸發(fā)器。不能在CLR模塊或進(jìn)程內(nèi)數(shù)據(jù)訪問(wèn)接口中指定@@PROCID 。
• @@SERVERNAME：返回運(yùn)行SQL Server的本地服務(wù)器的名稱。
• @@SERVICENAME：返回SQL Server正在其下運(yùn)行的注冊(cè)表項(xiàng)的名稱。若當(dāng)前實(shí)例為默認(rèn)實(shí)例，則@@SERVICENAME返回MSSQLSERVER；若當(dāng)前實(shí)例是命名實(shí)例，則該函數(shù)返回該實(shí)例名。
• @@SPID：返回當(dāng)前用戶進(jìn)程的會(huì)話ID。
• @@TEXTSIZE：返回SET語(yǔ)句的TEXTSIZE選項(xiàng)的當(dāng)前值，它指定SELECT語(yǔ)句返回的text或image數(shù)據(jù)類型的最大長(zhǎng)度，其單位為字節(jié)。
• @@TIMETICKS：返回每個(gè)時(shí)鐘周期的微秒數(shù)。
• @@TOTAL_ERRORS：返回自上次啟動(dòng)SQL Server之后，SQL Server所遇到的磁盤(pán)寫(xiě)入錯(cuò)誤數(shù)。
• @@TOTAL_READ：返回SQL Server自上次啟動(dòng)后，由SQL Server讀?。ǚ蔷彺孀x?。┑拇疟P(pán)的數(shù)目。
• @@TOTAL_WRITE：返回自上次啟動(dòng)SQL Server以來(lái)，SQL Server所執(zhí)行的磁盤(pán)寫(xiě)入數(shù)。
• @@TRANCOUNT：返回當(dāng)前連接的活動(dòng)事務(wù)數(shù)。
• @@VERSION：返回當(dāng)前安裝的日期、版本和處理器類型。

【例4.1】查看當(dāng)前SQL Server的版本信息和服務(wù)器名稱，輸入語(yǔ)句如下。

使用Windows身份驗(yàn)證登錄到SQL Server服務(wù)器之后，新建立一個(gè)使用當(dāng)前連接的查詢，輸入上面的語(yǔ)句，單擊【執(zhí)行】按鈕，執(zhí)行結(jié)果如圖4-1所示。

4.4.2　局部變量

局部變量是一個(gè)能夠擁有特定數(shù)據(jù)類型的對(duì)象，它的作用范圍僅限制在程序內(nèi)部。在批處理和腳本中變量可以有如下用途：作為計(jì)數(shù)器計(jì)算循環(huán)執(zhí)行的次數(shù)或控制循環(huán)執(zhí)行的次數(shù)，保存數(shù)據(jù)值供控制流語(yǔ)句測(cè)試以及保存由存儲(chǔ)過(guò)程代碼返回的數(shù)據(jù)值或者函數(shù)返回值。局部變量被引用時(shí)要在其名稱前加上標(biāo)志“@”，而且必須先用DECLARE命令聲明后才可以使用。定義局部變量的語(yǔ)法形式如下：

參數(shù)@local-variable用于指定局部變量的名稱，變量名必須以符號(hào)“@”開(kāi)頭，且必須符合SQL Server的命名規(guī)則。

參數(shù)data-type用于設(shè)置局部變量的數(shù)據(jù)類型及其大小。data-type可以是任何由系統(tǒng)提供的或用戶定義的數(shù)據(jù)類型。但是，局部變量不能是text、ntext或image數(shù)據(jù)類型。

【例4.2】使用DECLARE語(yǔ)句創(chuàng)建int數(shù)據(jù)類型的名為@mycounter的局部變量，輸入語(yǔ)句如下。

若要聲明多個(gè)局部變量，在定義的第一個(gè)局部變量后使用一個(gè)逗號(hào)，然后指定下一個(gè)局部變量名稱和數(shù)據(jù)類型。

【例4.3】創(chuàng)建3個(gè)名為@Name、@Phone和@Address的局部變量，并將每個(gè)變量都初始化為NULL，輸入語(yǔ)句如下。

使用DECLARE命令聲明并創(chuàng)建局部變量之后，會(huì)將其初始值設(shè)為NULL；如果想要設(shè)置局部變量的值，必須使用SELECT命令或者SET命令。其語(yǔ)法形式為：

其中，@local-variable是給其賦值并聲明的局部變量。expression是任何有效的SQL Server表達(dá)式。

【例4.4】使用SELECT語(yǔ)句為@MyCount變量賦值，最后輸出@MyCount變量的值，輸入語(yǔ)句如下。

執(zhí)行結(jié)果如圖4-2所示。

【例4.5】通過(guò)查詢語(yǔ)句給變量賦值，輸入語(yǔ)句如下。

該語(yǔ)句查詢出member表中總的記錄數(shù)，并將其保存在rows局部變量中。

【例4.6】在SELECT查詢語(yǔ)句中，使用由SET賦值的局部變量，輸入語(yǔ)句如下。

4.4.3　批處理和腳本

批處理是同時(shí)從應(yīng)用程序發(fā)送到SQL Server并得以執(zhí)行的一組單條或多條Transact-SQL語(yǔ)句，這些語(yǔ)句為了達(dá)到一個(gè)整體的目標(biāo)而同時(shí)執(zhí)行。GO命令表示批處理的結(jié)束。如果Transact-SQL腳本中沒(méi)有GO命令，那么它將被作為單個(gè)批處理來(lái)執(zhí)行。

SQL Server將批處理中的語(yǔ)句作為一個(gè)整體，編譯為一個(gè)執(zhí)行計(jì)劃，因此批處理中的語(yǔ)句是一起提交給服務(wù)器的，所以可以節(jié)省系統(tǒng)開(kāi)銷(xiāo)。

批處理中的語(yǔ)句如果在編譯時(shí)出現(xiàn)錯(cuò)誤，則不能產(chǎn)生執(zhí)行計(jì)劃，批處理中的任何一個(gè)語(yǔ)句都不會(huì)執(zhí)行。批處理運(yùn)行時(shí)出現(xiàn)錯(cuò)誤將有如下影響：

• 大多數(shù)運(yùn)行時(shí)錯(cuò)誤將停止執(zhí)行批處理中當(dāng)前語(yǔ)句和它之后的語(yǔ)句。
• 某些運(yùn)行時(shí)錯(cuò)誤（如違反約束）僅停止執(zhí)行當(dāng)前語(yǔ)句，而繼續(xù)執(zhí)行批處理中其他所有語(yǔ)句。
• 在遇到運(yùn)行時(shí)錯(cuò)誤的語(yǔ)句之前執(zhí)行的語(yǔ)句不受影響。唯一例外的情況是批處理位于事務(wù)中并且錯(cuò)誤導(dǎo)致事務(wù)回滾。在這種情況下，所有在運(yùn)行時(shí)錯(cuò)誤之前執(zhí)行的未提交數(shù)據(jù)修改都將回滾。

批處理使用時(shí)有如下限制規(guī)則：

• CREATE DEFAULT、CREATE FUNCTION、CREATE PROCEDURE、CREATE RULE、CREATE SCHEMA、CREATE TRIGGER和CREATE VIEW語(yǔ)句不能在批處理中與其他語(yǔ)句組合使用。批處理必須以CREATE語(yǔ)句開(kāi)始，所有跟在該批處理后的其他語(yǔ)句將被解釋為第一個(gè)CREATE語(yǔ)句定義的一部分。
• 不能在同一個(gè)批處理中更改表，然后引用新列。
• 如果EXECUTE語(yǔ)句是批處理中的第一句，則不需要EXECUTE關(guān)鍵字；如果EXECUTE語(yǔ)句不是批處理中的第一條語(yǔ)句，則需要EXECUTE關(guān)鍵字。

腳本是存儲(chǔ)在文件中的一系列Transact-SQL語(yǔ)句。Transact-SQL腳本包含一個(gè)或多個(gè)批處理。Transact-SQL腳本主要有以下用途：

• 在服務(wù)器上保存用來(lái)創(chuàng)建和填充數(shù)據(jù)庫(kù)的步驟的永久副本，作為一種備份機(jī)制。
• 必要時(shí)將語(yǔ)句從一臺(tái)計(jì)算機(jī)傳輸?shù)搅硪慌_(tái)計(jì)算機(jī)。
• 通過(guò)讓新員工發(fā)現(xiàn)代碼中的問(wèn)題、了解代碼或更改代碼從而快速對(duì)其進(jìn)行培訓(xùn)。

腳本可以看作一個(gè)單元，以文本文件的形式存儲(chǔ)在系統(tǒng)中，在腳本中可以使用系統(tǒng)函數(shù)和局部變量，例如一個(gè)腳本中包含了如下代碼：

該腳本中使用了6條語(yǔ)句，分別包含了USE語(yǔ)句、局部變量的定義、CREATE語(yǔ)句、INSERT語(yǔ)句、SELECT語(yǔ)句以及SET賦值語(yǔ)句，所有的這些語(yǔ)句在一起完成了person數(shù)據(jù)表的創(chuàng)建、插入數(shù)據(jù)并統(tǒng)計(jì)插入的記錄總數(shù)的工作。

USE語(yǔ)句用來(lái)設(shè)置當(dāng)前使用的數(shù)據(jù)庫(kù)，可以看到，因?yàn)槭褂昧薝SE語(yǔ)句，所以在執(zhí)行INSERT和SELECT語(yǔ)句時(shí)，它們將在指定的數(shù)據(jù)庫(kù)（test_db）中進(jìn)行操作。