13 Transact-SQL程式設計 13-1 Transact-SQL語言的基礎 13-2 批次的使用 13-3 註解與自訂訊息 13-4 變數的宣告與使用 13-5 運算式與運算子 13-6 流程控制結構 13-7 錯誤處理 13-8 產生SQL Server指令碼

13-1 Transact-SQL語言的基礎 13-1-1 Transact-SQL資料庫語言

13-1-1 Transact-SQL資料庫語言 Transact-SQL（簡稱T-SQL）是Microsoft SQL Server資料庫系統支援的資料庫查詢語言，為了方便說明，在本書是使用T-SQL取代全名Transact-SQL。 T-SQL的語法是遵循ANSI-SQL 92標準所制定，它最早是由Sybase公司所開發，只是擴充語法增加程式設計功能。簡單的說，T-SQL就是ANSI-SQL結構化查詢語言可程式化的擴充版本。 T-SQL擴充ANSI-SQL增加功能強大的程式設計相關指令，包含：變數宣告、條件處理、錯誤處理、指標和眾多函數等，可以讓我們撰寫SQL程式碼檔案的批次、預存程序、自訂函數和觸發程序。

13-1-2 Transact-SQL指令碼檔案-說明 SQL Server指令碼（Scripts）是儲存在檔案中的一系列T-SQL指令敘述，其副檔名是.sql，因為內容是一般文字檔案，所以，我們可以使用Windows記事本、WordPad或其他程式碼編輯工具來建立和編輯T-SQL指令碼檔案。 在SQL Server的Management Studio和SQLCMD等工具程式都可以載入和執行儲存在檔案中的T-SQL指令敘述，例如：書附光碟各章節的SQL範例的指令碼檔案。

13-1-2 Transact-SQL指令碼檔案- 使用方式 將建立資料庫或操作所執行的T-SQL指令碼永久保存至檔案，其功能如同是一種資料庫備份機制。事實上，Management Studio就提供產生SQL Server指令碼精靈，可以幫助我們自動產生資料庫的T-SQL指令碼。 因為儲存成檔案，換句話說，我們可以在不同電腦或伺服器間交換、傳送和執行T-SQL指令碼檔案。 為了方便訓練員工、除錯或升級所需，我們可以將T-SQL指令敘述儲存成檔案，以方便指令碼除錯、瞭解指令碼或修改指令碼內容。

13-2 批次的使用 13-2-1 批次的基礎 13-2-2 使用GO指令定義批次

13-2-1 批次的基礎-說明 批次（Batch）是從應用程式將T-SQL指令敘述送至SQL Server資料庫引擎執行的基本單位。因為批次允許同時包裹一至多個T-SQL指令敘述，換句話說，我們能夠一次包裹執行多個T-SQL指令敘述。 批次是一組T-SQL指令敘述的集合，我們可以在應用程式使用批次將多個T-SQL指令敘述包裹起來，一起送至SQL Server資料庫引擎來執行， 對於SQL Server資料庫引擎來說，在剖析、編譯和執行批次時，批次的多個T-SQL指令敘述會編譯成單一的執行單位，稱為「執行計劃」（Execution Plan），然後一次就執行完執行計劃中的所有指令敘述。

13-2-1 批次的基礎-錯誤處理方式 編譯階段錯誤：如果批次中的T-SQL指令語法有錯誤，就會中止編譯，因為尚未編譯成執行計劃，所以批次中的任何指令敘述都不會執行。例如：批次共有5個T-SQL指令敘述，若第3個指令敘述編譯錯誤，在批次中的所有指令敘述都不會執行。 執行階段錯誤：如果批次完成編譯，在執行時發生錯誤，大部分情況是中止目前指令敘述和之後指令敘述的執行（例如：找不到資料表），不過，少數情況（例如：違反限制性條件時）就只會中止目前的指令敘述，仍然會執行剩下的指令敘述。

13-2-2 使用GO指令定義批次-說明 在SQL Server的Management Studio和SQLCMD工具程式是使用GO指令來定義批次的結束，GO指令並不是T-SQL指令，它只是一個代表結束點的符號，以便在T-SQL指令碼檔案分隔出一至多個批次。 換句話說，如果T-SQL指令碼沒有使用GO指令，隱含表示這個T-SQL指令碼就是一個批次，因為SQL Server會自動替我們加上GO指令。

13-2-2 使用GO指令定義批次-範例 SQL指令碼檔：Ch13_2_2.sql 使用GO指令建立擁有多個批次的T-SQL指令碼，如下所示： USE 教務系統 GO CREATE VIEW 課程_高學分 AS SELECT * FROM 課程 WHERE 學分 >= 4 SELECT * FROM 課程_高學分

13-2-2 使用GO指令定義批次-範例說明 GO指令是SQL Server工具程式Management Studio和SQLCMD定義批次的方式，其他工具或使用ADO、ADO.NET、OLE DB或ODBC撰寫的資料庫應用程式時，每一個T-SQL指令字串就是一個批次。 一些T-SQL指令一定需要獨立成一個批次，並不能和其他T-SQL指令一起執行，這些指令有：CREATE DEFAULT、CREATE FUNCTION、CREATE PROCEDURE、CREATE RULE、CREATE TRIGGER或CREATE VIEW指令。

13-3 註解與自訂訊息 13-3-1 註解 13-3-2 PRINT指令輸出自訂訊息 13-3-3 USE指令轉換資料庫

13-3-1 註解 T-SQL語言的註解有兩種方式，第一種是使用「/*」和「*/」符號來標示註解文字，如下所示： /* 使用教務系統資料庫 */ USE 教務系統 GO T-SQL的註解還可以跨過很多列，如下所示： /* --------------------------- 使用教務系統資料庫 ------------------------------ */

13-3-2 PRINT指令輸出自訂訊息 PRINT輸出指令可以傳回使用者自訂訊息至用戶端程式，它能夠輸出字串資料類型的字串、Unicode字串常數、字串運算式或T-SQL變數值的訊息文字，其基本語法如下所示： PRINT 字串運算式 例如：一些PRINT指令的範例，如下所示： PRINT 'This is a test.' PRINT N'This is a book.' PRINT @msg

13-3-3 USE指令轉換資料庫 USE指令可以轉換指令碼使用的資料庫，其基本語法如下所示： USE 資料庫名稱 上述語法可以轉換至指定【資料庫名稱】的資料庫，在同一個SQL指令碼檔案，我們可以重複使用USE指令來轉換成不同資料庫來執行T-SQL指令敘述， 例如：轉換成【教務系統】資料庫，如下所示： USE 教務系統

13-4 變數的宣告與使用 13-4-1 宣告變數與變數初值 13-4-2 指定變數值 13-4-3 變數的範圍 13-4-4 SQL Server的系統函數

13-4 變數的宣告與使用 T-SQL的變數（Variables）是一種批次中的物件，可以用來儲存指定資料類型在批次執行期間的暫存資料，其主要用途如下所示： 在不同T-SQL指令敘述間傳遞資料。 流程控制迴圈結構的計數器（Counter）或測試條件。 預存程序或自訂函數的傳入參數或儲存傳回值。 作為WHERE子句的條件。 在T-SQL宣告的變數分為使用「＠」開頭的區域變數（Local Variables）和「@@」符號開始的系統函數（它也是一種全域變數）。

13-4-1 宣告變數與變數初值- 宣告變數(語法) 使用者在批次宣告的變數是一種T-SQL區域變數，當宣告變數後，在同一個批次的指令敘述就可以指定變數值，並且在批次之後的程式敘述存取此變數值。 T-SQL語言是使用DECLARE指令來宣告變數，其基本語法如下所示： DECLARE @變數名稱1 資料類型 [, @變數名稱2 資料類型] …

13-4-1 宣告變數與變數初值- 宣告變數(範例) 例如：宣告名為@balance的int整數類型的變數，如下所示： DECLARE @balance int DECLARE指令宣告一個名為@balance的整數變數，可以用來儲存整數沒有小數點的變數值。

13-4-1 宣告變數與變數初值-變數初值 SQL Server支援變數初值，可以在宣告時，使用「=」等號來指定變數的初值。例如：宣告名為@total的int整數類型的變數，並且指定初值為100，如下所示： DECLARE @total int = 100

13-4-1 宣告變數與變數初值- 宣告多個變數 不只如此，我們也可以在同一列T-SQL指令同時宣告多個變數且指定初值，而且可以是不同的資料類型，如下所示： DECLARE @myName varchar(12), @amount int = 123, @mycounter int = 5

13-4-1 宣告變數與變數初值-範例 SQL指令碼檔：Ch13_4_1.sql 在批次宣告名為@balance和@total變數，同時指定變數@total的初值後，使用PRINT指令顯示變數值，如下所示： DECLARE @balance int DECLARE @total int = 100 PRINT @balance PRINT @total

13-4-2 指定變數值- 使用SET指令(語法) 在批次宣告T-SQL變數後，我們就可以使用SET指令來指定變數值，其語法如下所示： 上述語法指定T-SQL變數是使用「=」指定運算子來指定成之後的運算式值，它可以是常數值、第13-5節的運算式或SELECT指令敘述的查詢結果。

13-4-2 指定變數值- 使用SET指令(範例1) SQL指令碼檔：Ch13_4_2_01.sql 在批次宣告T-SQL變數@balance且指定帳戶本金1000後，計算一年期本金加利息的帳戶餘額，如下所示： DECLARE @balance int SET @balance = 1000 SET @balance = @balance * 1.02 PRINT ‘總額:' + CAST(@balance AS char)

13-4-2 指定變數值- 使用SET指令(範例2) SQL指令碼檔：Ch13_4_2_02.sql 在批次宣告T-SQL變數@tatal後，使用SELECT指令的查詢結果來指定變數值，這是查詢【課程】資料表的總學分數，如下所示： DECLARE @total int SET @total = (SELECT SUM(學分) FROM 課程) PRINT ‘學分數:' + CAST(@total AS char)

13-4-2 指定變數值- 使用SELECT指令(語法) SELECT指令也可以用來指定變數值，或配合FROM子句將查詢結果的欄位值填入變數，其基本語法如下所示： SELECT @變數名稱 = 運算式或欄位名稱 上述語法指定T-SQL變數是「=」等號後的運算式值，或查詢資料表的記錄資料，直接將欄位值指定成變數值。

13-4-2 指定變數值- 使用SELECT指令(範例1) SQL指令碼檔：Ch13_4_2_03.sql 在宣告T-SQL變數@myName後，使用SELECT指令來指定和顯示變數值，如下所示： DECLARE @myName varchar(12) SELECT @myName = '陳會安' SELECT @myName AS 姓名

13-4-2 指定變數值- 使用SELECT指令(範例2) SQL指令碼檔：Ch13_4_2_04.sql 查詢【教務系統】資料庫的【員工】資料表，將姓名和城市欄位值填入T-SQL變數@myName和@myCity，如下所示： DECLARE @myName varchar(12) DECLARE @myCity varchar(10) SELECT @myName = 姓名, @myCity = 城市 FROM 員工 WHERE 薪水 >= 60000 SELECT @myName AS 姓名, @myCity AS 城市

13-4-2 指定變數值- 使用SELECT指令(範例3) SQL指令碼檔：Ch13_4_2_05.sql 我們也可以使用T-SQL變數作為WHERE子句的條件，用來查詢【教務系統】資料庫的【課程】資料表，WHERE子句的條件是變數@c_no的值，如下所示： DECLARE @c_no varchar(5) SELECT @c_no = 'CS101' SELECT 課程編號, 名稱, 學分 FROM 課程 WHERE 課程編號 = @c_no

13-4-2 指定變數值- 變數值的種類(說明) T-SQL變數的變數值有兩種，可以儲存單一值的純量變數，和整個資料表的資料表變數，如下所示： 純量變數（Scalar Variables）：這是一種儲存標準資料類型的單一值，在之前宣告的變數都是一種純量變數。 資料表變數（Table Variables）：可以儲存整個資料表內容的變數，我們可以在資料表變數使用SELECT、INSERT、UPDATE和DELETE指令，將它視為是一個標準資料表。

13-4-2 指定變數值- 變數值的種類(資料表變數語法) 在批次宣告資料表變數也是使用DECLARE指令，其基本語法和CREATE TABLE指令類似，如下所示： DECLARE @資料表變數名稱 table ( 欄位名稱1 資料類型 [欄位屬性清單] [, 欄位名稱1 資料類型 [欄位屬性清單]] … [, 資料表屬性清單] ) 上述語法是使用table資料類型來宣告資料表變數。

13-4-2 指定變數值- 變數值的種類(資料表變數範例) SQL指令碼檔：Ch13_4_2_06.sql 在宣告資料表變數@students後，使用INSERT/SELECT指令插入記錄資料後，最後再使用SELECT指令查詢資料表變數的內容，如下所示： DECLARE @students table ( std_no char(4), name varchar(12) ) INSERT @students SELECT 學號, 姓名 FROM 學生 WHERE 性別 = '男' SELECT * FROM @students

13-4-3 變數的範圍 T-SQL區域變數的範圍（Scopes）是在同一個批次或預存程序，換句話說，所有在批次中使用的區域變數，也一定需要在同一個批次中宣告。我們不能存取其他批次中宣告的變數，如下所示： DECLARE @myName varchar(12) GO SELECT @myName = '張無忌'

13-4-4 SQL Server的系統函數-說明 SQL Server的系統函數（System Functions），舊名全域變數（Global Variables）可以傳回SQL Server系統資訊的值、物件或設定值。 我們可以在T-SQL運算式或CREATE TABLE指令的DEFAULT條件約束使用這些系統函數。

13-4-4 SQL Server的系統函數- 常用函數 常用的系統函數說明，如下表所示： 系統函數 說明 @@IDENTIFY 傳回伺服器最後產生IDENTITY欄位自動編號的值，如果沒有產生傳回NULL @@ROWCOUNT 傳回最近執行T-SQL指令敘述所影響的記錄數 @@ERROR 傳回最近執行T-SQL指令敘述所產生的錯誤編號，如果沒有錯誤傳回0 @@SERVERNAME 傳回伺服器名稱

13-4-4 SQL Server的系統函數-範例 SQL指令碼檔：Ch13_4_4.sql DECLARE @MyRowCount int, @MyIdentity int INSERT 課程備份2 SELECT 課程編號, 名稱, 學分 FROM 課程 WHERE 學分 >= 4 SET @MyRowCount = @@ROWCOUNT SET @MyIdentity = @@IDENTITY SELECT @MyRowCount AS 影響的記錄數, @@SERVERNAME AS 伺服器名稱, @MyIdentity AS 自動編號, @@ERROR AS 錯誤編號

13-5 運算式與運算子 13-5-1 運算子的優先順序 13-5-2 T-SQL的運算子 13-5-3 再談T-SQL的指定運算子 13-5-4 類型轉換運算子

13-5 運算式與運算子 T-SQL的運算式（Expressions）可以傳回單一值，它是由一或多個常數、識別名稱、函數、變數所組成，稱為運算元（Operands），在運算元之間使用運算子（Operators）來連接一或多個運算元。 在T-SQL的運算式可以分為算術、條件、邏輯或字串等多種運算式。事實上，T-SQL指令的子句或子查詢也都可以使用運算式，例如：WHERE子句的條件運算式。

13-5-1 運算子的優先順序 運算子 說明 ~ 位元運算子NOT *、/、% 算術運算子的乘、除法和餘數 +、-、^、&、| 單運算元運算子的正號、負號、算術運算子的加和減法、字串連接運算子（+）、位元運算子的XOR、AND和OR ＝、>、>=、<、<=、<>、!=、!>、!< 比較運算子的大於、大於等於、小於和小於等於、等於、不等於、不大於、不小於 NOT 邏輯運算子NOT AND 邏輯運算子AND ALL、ANY、BETWEEN、IN、LIKE、OR、SOME ALL、ANY、BETWEEN、IN、LIKE、OR和SOME邏輯運算子 ＝ 指定運算子

13-5-2 T-SQL的運算子-算術運算子 算術運算子（Arithmetic Operators）就是加法（+）、減法（-）、乘法（*）、除法（/）和餘數（%），可以用來計算數值或日期/時間的運算，如下所示： PRINT CAST(‘2016-06-30' AS datetime) - 1 PRINT CAST(‘2016-06-30' AS datetime) + 1 上述T-SQL指令敘述可以顯示前一天和後一天的日期資料。在＜第8-2-3節：計算值欄位＞已經說明過這些算術運算子。

13-5-2 T-SQL的運算子- 比較、邏輯與字串連接運算子 比較運算子（Comparison Operators）可以用來比較數值、日期/時間或字串的大小，詳細的運算子說明請參閱＜第8-4-1節：比較運算子＞。 邏輯運算子（Logical Operators）可以連接條件運算式建立複雜的搜尋條件，或在子查詢判斷TRUE或FALSE。請參閱＜第8-4-2節：邏輯運算子＞和＜第9-4-3節：邏輯運算子的子查詢＞。 字串連接運算子（String Concatenation Operators） 「+」號可以用來連接字串欄位值或字串常數，其資料類型儘限是char、varchar和text，進一步說明請參閱＜第8-2-3節：計算值欄位＞。

13-5-2 T-SQL的運算子-位元運算子 位元運算子（Bitwise Operators） AND、OR和XOR可以執行位元的邏輯運算，如下表所示： 運算子 範例 說明 & op1 & op2 位元的AND運算子，2個運算元的位元值同為1時為1，如果有一個為0，就是0 | op1 | op2 位元的OR運算子，2個運算元的位元值只需有一個是1，就是1，否則為0 ^ op1 ^ op2 位元的XOR運算子，2個運算元的位元值只需任一個為1，結果為1，如果同為0或1時結果為0 ~ ~ op 位元的NOT運算子就是1'補數運算，即位元值的相反值，1成0；0成1

13-5-2 T-SQL的運算子-單運算元運算子 單運算元運算子（Unary Operators）只能有一個運算元，而且只能使用在數值類型，T-SQL共有3種單運算元運算子，如下表所示： 運算子 範例 說明 + + op 正號，表示數值為正 - - op 負號，表示數值為負 ~ ~ op 位元的NOT運算子，在之前已經說明過

13-5-2 T-SQL的運算子-指定運算子 指定運算子（Assignment Operator）可以將數值、字串等資料指定給欄位或T-SQL變數，進一步說明請參閱＜第13-4-2節：指定變數值＞。

13-5-3 再談T-SQL的指定運算子-說明 T-SQL的指定敘述可以配合其他運算子來簡化運算式的撰寫，建立更簡潔的T-SQL運算式，如下表所示： 運算子 範例 相當的運算式 說明 = ＠x = ＠y N/A 指定敘述 += ＠x += ＠y ＠x = ＠x + ＠y 加法 -= ＠x -= ＠y ＠x = ＠x - ＠y 減法 *= ＠x *= ＠y ＠x = ＠x * ＠y 乘法 /= ＠x /= ＠y ＠x = ＠x / ＠y 除法 &= ＠x &= ＠y ＠x = ＠x & ＠y 位元的AND運算 |= ＠x |= ＠y ＠x = ＠x | ＠y 位元的OR運算 ^= ＠x ^= ＠y ＠x = ＠x ^ ＠y 位元的XOR運算

13-5-3 再談T-SQL的指定運算子-範例 SQL指令碼檔：Ch13_5_3.sql DECLARE @x int = 4, @y int = 20 SET @x *= @y SELECT @x, @y

13-5-4 類型轉換運算子-說明 「資料類型轉換」（Type Conversions）是因為運算式可能擁有多個不同資料類型的變數或常數值。例如：在運算式中擁有int和smallint類型的變數時，就需要類型轉換。 在T-SQL的類型轉換可以分為兩種，一種是SQL Server自動進行的轉換，例如：int和smallint會自動將smallint轉換成int，稱為「隱含類型轉換」（Implicit Conversion）。另一種情況是在運算式使用CAST運算子和CONVERT()函數來強迫轉換類型，稱為「強迫類型轉換」（Explicit Conversion）。

13-5-4 類型轉換運算子-CAST運算子 CAST運算子是ANSI-SQL標準的類型轉換運算子，可以將資料從一種資料類型轉換成另一種資料類型，如下所示： PRINT 'Total Credits:' + CAST(@total AS char) PRINT CAST(‘2016-06-30' AS datetime) - 1 上述T-SQL指令敘述分別將整數@tatal變數轉換成AS關鍵字後的char類型，和日期常數轉換成datetime類型。

13-5-4 類型轉換運算子- CONVERT()函數 CONVERT()函數是T-SQL函數，一樣可以將資料從一種資料類型轉換成另一種資料類型，如下所示： PRINT 'Total Credits:' + CONVERT(char, @total) PRINT CONVERT(datetime, ‘2016-06-30') - 1 上述是和前述CAST運算子相同功能的T-SQL指令敘述，在CONVERT()函數的轉換類型是第1個參數。

13-6 流程控制結構 13-6-1 BEGIN/END指令區塊 13-6-2 IF/ELSE條件控制指令 13-6-3 RETURN中斷查詢指令 13-6-4 CASE多條件函數 13-6-5 WHILE迴圈控制指令 13-6-6 GOTO跳躍至指定標籤 13-6-7 WAITFOR暫停執行指令 13-6-8 IIF與CHOOSE函數

13-6 流程控制結構-說明 T-SQL指令碼大部分是一列指令敘述接著一列指令敘述循序的執行，但是對於複雜的工作，為了達成預期的執行結果，我們需要使用「流程控制結構」（Control Structures）來控制執行的流程。

13-6 流程控制結構-種類 T-SQL流程控制指令可以配合條件判斷來執行不同的指令敘述，或重複執行指令敘述。流程控制主要分為兩類，如下所示： 條件控制：條件控制是一個選擇題，可能為單一選擇或多選一，依照條件決定執行那一個指令敘述，或整個區塊的指令敘述。 迴圈控制：迴圈控制是重複執行指令敘述或整個區塊的指令敘述，擁有結束條件來結束迴圈執行。

13-6-1 BEGIN/END指令區塊-說明 BEGIN/END指令可以將多個T-SQL指令群組成一個邏輯區塊，在T-SQL通常是搭配流程控制指令來使用。一般來說，BEGIN/END指令的使用時機，如下所示： WHILE迴圈指令需要包含一個邏輯區塊的T-SQL指令。 IF或ELSE條件指令需要包含一個邏輯區塊的T-SQL指令。 CASE函數需要包含一個邏輯區塊的T-SQL指令。

13-6-1 BEGIN/END指令區塊-範例 簡單的說，當流程控制指令需要執行兩個或以上的T-SQL指令時，我們就需要使用BEGIN/END指令將它們括起來。如下所示： IF @dbName = '教務系統' BEGIN PRINT 'Database: School' PRINT 'Tables: Professor, Courses' END

13-6-2 IF/ELSE條件控制指令-語法 IF條件控制指令可以依條件決定是否執行T-SQL指令敘述，其基本語法如下所示： { 指令敘述 | BEGIN … END } [ELSE

13-6-2 IF/ELSE條件控制指令-範例1 SQL指令碼檔：Ch13_6_2_01.sql 請宣告變數@height後，使用IF條件判斷身高是購買全票或半票，如下所示： DECLARE @height int SET @height = 125 IF @height <= 120 PRINT '半票' IF @height > 120 BEGIN PRINT '全票' PRINT 'height > 120' END

13-6-2 IF/ELSE條件控制指令-範例2 SQL指令碼檔：Ch13_6_2_02.sql IF (SELECT COUNT(*) FROM 教授) >= 1 PRINT '教授資料表有存在記錄!' ELSE PRINT '教授資料表沒有記錄!'

13-6-2 IF/ELSE條件控制指令-範例3a SQL指令碼檔：Ch13_6_2_03.sql USE master IF DB_ID('教務系統') IS NOT NULL PRINT '找到教務系統資料庫!' ELSE PRINT '教務系統資料庫找不到!'

13-6-2 IF/ELSE條件控制指令-範例3b USE 教務系統 IF OBJECT_ID('課程') IS NOT NULL PRINT '找到課程資料表!' ELSE PRINT '找不到課程資料表!'

13-6-3 RETURN中斷查詢指令-語法 RETURN指令可以中斷批次或預存程序的執行，也就是說，在此指令之後的指令敘述都不會執行，其基本語法如下所示： RETURN [整數運算式] 上述語法的RETURN指令如果使用在預存程序，可以傳回一個整數值，如果沒有指定，預設傳回0。

13-6-3 RETURN中斷查詢指令-範例1a SQL指令碼檔：Ch13_6_3.sql 宣告變數來取得課程數，並且使用IF條件判斷【學生】資料表是否有記錄，如下所示： DECLARE @total int SET @total = (SELECT COUNT(*) FROM 課程) IF (SELECT COUNT(*) FROM 學生) >= 1 BEGIN PRINT '學生資料表有記錄資料!' RETURN

13-6-3 RETURN中斷查詢指令-範例1b END ELSE PRINT '學生資料表沒有記錄資料!' PRINT '課程數:' + CAST(@total AS char)

13-6-4 CASE多條件函數- 簡單CASE函數(語法) WHEN 比對運算式 THEN 結果運算式 […n] [ ELSE 例外的結果運算式 ] END

13-6-4 CASE多條件函數- 簡單CASE函數(範例) SQL指令碼檔：Ch13_6_4_01.sql 使用CASE函數將【學生】資料表的性別欄位改為Male和Female來顯示，如下所示： SELECT 學號, 姓名, CASE 性別 WHEN '男' THEN 'Male' WHEN '女' THEN 'Female' ELSE 'N/A' END AS 學生性別 FROM 學生

13-6-4 CASE多條件函數- 搜尋CASE函數(語法) 搜尋CASE函數屬於多條件的比較，它並沒有輸入運算式，而是在每一個WHEN子句建立布林條件的運算式，其基本語法如下所示： CASE WHEN 布林運算式 THEN 結果運算式 […n] [ ELSE 例外的結果運算式] END

13-6-4 CASE多條件函數- 搜尋CASE函數(範例1a) SQL指令碼檔：Ch13_6_4_02.sql 使用CASE函數依年齡變數@age的條件來指定變數@type的值，如下所示： DECLARE @type varchar(12), @age int SET @age = 25 SET @type = CASE WHEN @age < 15 THEN '小孩'

13-6-4 CASE多條件函數- 搜尋CASE函數(範例1b) WHEN @age < 60 THEN '成人' WHEN @age < 100 THEN '老人' ELSE 'Free' END PRINT @type

13-6-5 WHILE迴圈控制指令-語法 T-SQL語言的WHILE迴圈指令可以建立迴圈的控制結構，不只如此，對於WHILE迴圈，我們還可以使用BREAK指令跳出迴圈，或CONTINUE指令繼續迴圈的執行。 WHILE迴圈控制指令是在迴圈開頭檢查條件，開頭檢查可以判斷是否允許進入迴圈，只有當測試條件成立時才允許進入迴圈，不成立就離開迴圈，其基本語法如下所示： WHILE 條件運算式 { 指令敘述 | BEGIN … END }

13-6-5 WHILE迴圈控制指令-範例 SQL指令碼檔：Ch13_6_5_01.sql 使用WHILE迴圈計算從1加至5的和，如下所示： DECLARE @counter int, @total int SET @counter = 1 SET @total = 0 WHILE @counter <= 5 BEGIN SET @total = @total + @counter PRINT '計數: ' + CAST(@counter AS char) SET @counter = @counter + 1 END PRINT '1 加到 5 = ' + CAST(@total AS char)

13-6-5 WHILE迴圈控制指令- 巢狀迴圈(說明) 巢狀迴圈是指在WHILE迴圈中擁有其他WHILE迴圈，迴圈如同巢狀般一層一層的排列。

13-6-5 WHILE迴圈控制指令- 巢狀迴圈(範例1a) SQL指令碼檔：Ch13_6_5_02.sql 在建立TextBooks資料表後，使用巢狀WHILE迴圈來新增資料表的記錄資料，如下所示： DECLARE @book_Id int, @category_Id int CREATE TABLE TextBooks (book_Id int, category_Id int) SET @book_Id = 0 SET @category_Id = 0 WHILE @book_Id < 2 BEGIN SET @book_Id = @book_Id + 1

13-6-5 WHILE迴圈控制指令- 巢狀迴圈(範例1b) WHILE @category_Id < 3 BEGIN SET @category_Id = @category_Id + 1 INSERT INTO TextBooks VALUES(@book_Id, @category_Id) END SET @category_Id = 0 SELECT * FROM TextBooks DROP TABLE TextBooks

13-6-5 WHILE迴圈控制指令- BREAK指令跳出迴圈(說明) WHILE迴圈如果尚未到達結束條件，我們可以在BEGIN/END區塊使用BREAK指令來強迫跳出迴圈，即中斷WHILE迴圈的執行。

13-6-5 WHILE迴圈控制指令- BREAK指令跳出迴圈(範例2a) SQL指令碼檔：Ch13_6_5_03.sql 使用WHILE迴圈計算1加至5的總和，WHILE迴圈是使用BREAK指令中斷迴圈執行，如下所示： DECLARE @counter int, @total int SET @total = 0 SET @counter = 1 WHILE @counter <= 15 BEGIN SET @total = @total + @counter

13-6-5 WHILE迴圈控制指令- BREAK指令跳出迴圈(範例2b) PRINT '計數: ' + CAST(@counter AS char) SET @counter = @counter + 1 IF @counter > 5 BREAK END PRINT '1 加到 5 = ' + CAST(@total AS char)

13-6-5 WHILE迴圈控制指令- CONTINUE指令繼續迴圈(說明)

13-6-5 WHILE迴圈控制指令- CONTINUE指令繼續迴圈(範例3a) SQL指令碼檔：Ch13_6_5_04.sql 使用WHILE迴圈配合CONTINUE指令來計算1至100間的奇數總和，如下所示： DECLARE @counter int, @total int SET @total = 0 SET @counter = 0 WHILE @counter <= 99 BEGIN

13-6-5 WHILE迴圈控制指令- CONTINUE指令繼續迴圈(範例3b) SET @counter = @counter + 1 IF @counter % 2 = 0 CONTINUE SET @total = @total + @counter END PRINT '總和: ' + CAST(@total AS char)

13-6-6 GOTO跳躍至指定標籤 -語法 T-SQL語言的GOTO指令可以變更執行流程至指定的標籤（Label），其基本語法如下所示： 上述語法可以跳躍至標籤名稱的下一個指令敘述來繼續的執行。標籤的基本語法如下所示： 標籤名稱: 上述語言在標籤名稱後是一個「:」符號。GOTO指令最常見的應用是跳出巢狀迴圈，因為BREAK指令只能跳出目前這一層WHILE迴圈，如果需要跳出整個巢狀迴圈，就需要使用GOTO指令。

13-6-6 GOTO跳躍至指定標籤 -範例1a SQL指令碼檔：Ch13_6_6.sql 在建立TextBooks資料表後，使用巢狀WHILE迴圈產生記錄資料，批次是使用GOTO指令跳出巢狀迴圈，所以並沒有執行完，如下所示： DECLARE @book_Id int, @category_Id int CREATE TABLE TextBooks (book_Id int, category_Id int) SET @book_Id = 0 SET @category_Id = 0 WHILE @book_Id < 2 BEGIN SET @book_Id = @book_Id + 1

13-6-6 GOTO跳躍至指定標籤 -範例1b WHILE @category_Id < 3 BEGIN SET @category_Id = @category_Id + 1 IF @book_id = 1 AND @category_id = 3 GOTO BREAK_POINT INSERT INTO TextBooks VALUES(@book_Id, @category_Id) END SET @category_Id = 0 BREAK_POINT: SELECT * FROM TextBooks DROP TABLE TextBooks

13-6-7 WAITFOR暫停執行指令-語法 WAITFOR指令可以暫停批次、預存程序或交易的執行，其基本語法如下所示： WAITFOR { DELAY | TIME } 時間 上述語法可以使用DELAY關鍵字來指定延遲一段時間，例如：2秒，或使用TIME關鍵字指定延遲至指定的時間，例如：下午10點。時間格式是hh:mm:ss。

13-6-7 WAITFOR暫停執行指令-範例 SQL指令碼檔：Ch13_6_7_01.sql 延遲3秒才執行【學生】資料表的查詢，如下所示： WAITFOR DELAY '00:00:03' SELECT * FROM 學生 SQL指令碼檔：Ch13_6_7_02.sql 延遲至下午23點才執行【員工】資料表的查詢，如下所示： WAITFOR TIME '23:00' SELECT * FROM 員工

13-6-8 IIF與CHOOSE函數- IIF()函數(語法) 上述函數有3個參數，以第1個參數的布林運算式值來決定傳回第2或第3個參數值，True傳回第2個參數；False傳回第3個參數。

13-6-8 IIF與CHOOSE函數- IIF()函數(範例) SQL指令碼檔：Ch13_6_8_01.sql 在宣告數學和英文成績的@math和@english變數後，請使用IIF()函數判斷哪一個成績比較高，可以傳回不同的訊息文字，如下所示： DECLARE @math int = 65 DECLARE @english int = 70 DECLARE @result varchar(10) SET @result = IIF ( @math > @english, '數學高', '英文高' ) PRINT @result

13-7 錯誤處理 13-7-1 錯誤處理結構 13-7-2 使用RAISERROR指令產生錯誤訊息 13-7-3 THROW指令敘述

13-7-1 錯誤處理結構-語法 TRY/CATCH錯誤處理結構分為TRY和CATCH區塊，其中CATCH區塊必須緊接在TRY區塊後，其基本語法如下所示： BEGIN TRY T-SQL指令敘述 END TRY BEGIN CATCH END CATCH

13-7-1 錯誤處理結構-範例1a SQL指令碼檔：Ch13_7_1_01.sql 使用TRY/CATCH指令建立除以零錯誤的錯誤處理結構，如下所示： BEGIN TRY SELECT 1/0 -- 除以零的錯誤 END TRY BEGIN CATCH -- 顯示錯誤資訊 SELECT ERROR_NUMBER() AS ErrorNumber,

13-7-1 錯誤處理結構-範例1b ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage END CATCH

13-7-1 錯誤處理結構-錯誤處理函數 在CATCH區塊可以使用錯誤處理函數來取得TRY區塊產生的錯誤資訊，其相關函數的說明如下表所示： ERROR_NUMBER() 傳回錯誤號碼 ERROR_MESSAGE() 傳回完整的錯誤訊息 ERROR_SEVERITY() 傳回錯誤嚴重性代碼 ERROR_STATE() 傳回錯誤的狀態碼 ERROR_LINE() 傳回造成錯誤的行列號 ERROR_PROCEDURE() 傳回發生錯誤的預存或觸發程序名稱

13-7-2 使用RAISERROR指令產生 錯誤訊息-新增錯誤訊息(語法) RAISERROR指令除了可以產生系統預設的錯誤訊息外，我們也可以自行新增所需的錯誤訊息，這是使用sp_addmessage系統預存程序所新增的錯誤訊息，其基本語法如下所示： EXEC sp_addmessage 訊息編號, 嚴重等級 ,'錯誤訊息文字', @lang = '使用的語言'

13-7-2 使用RAISERROR指令產生 錯誤訊息-新增錯誤訊息(範例) SQL指令碼檔：Ch13_7_2_01.sql 使用系統預存程序新增成績為負數的自訂錯誤訊息，如下所示： EXEC sp_addmessage 55555, 5 ,'Error! score < 0!', @lang = 'us_english' GO EXEC sp_addmessage 55555, 5 ,'成績為負數的錯誤!', @lang = '繁體中文'

13-7-2 使用RAISERROR指令產生 錯誤訊息-產生錯誤訊息(語法)

13-7-2 使用RAISERROR指令產生 錯誤訊息-產生錯誤訊息(範例) SQL指令碼檔：Ch13_7_2_02.sql 使用TRY/CATCH指令建立錯誤處理結構，其中的錯誤是由RAISERROR指令產生錯誤編號55555的自訂錯誤，如下所示： BEGIN TRY RAISERROR (55555, 7, 10) END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage END CATCH

13-7-3 THROW指令敘述-語法 SQL Server可以使用THROW指令敘述來丟出例外，用來取代RAISERROR()函數，其語法如下所示： THROW [ 錯誤編號, 錯誤訊息, 狀態值 ][;] 上述語法的第1個參數是錯誤編號，就是上一節新增錯誤訊息的編號，其值為整數且大於等於5000，第2個參數是錯誤訊息字串，最後1個是對應訊息文字的狀態值，範圍是0~255。 如果THROW指令敘述沒有任何參數，就只能出現在BEGIN CATCH/END CATCH區塊。

13-7-3 THROW指令敘述-範例 SQL指令碼檔：Ch13_7_3_01.sql 請使用TRY/CATCH指令敘述建立重複插入記錄的錯誤處理，在CATCH區塊是使用THROW指令敘述丟出例外（沒有參數），如下所示： USE tempdb GO CREATE TABLE MyTEMPDB (ID INT PRIMARY KEY ) BEGIN TRY INSERT MyTEMPDB(ID) VALUES(1) INSERT MyTEMPDB(ID) VALUES(1) -- 重複插入記錄 END TRY BEGIN CATCH THROW END CATCH

13-8 產生SQL Server指令碼 13-8-1 使用物件總管產生指令碼 13-8-2 產生SQL Server指令碼精靈

13-8-1 使用物件總管產生指令碼 在Management Studio的「物件總管」視窗可以建立整個資料庫的T-SQL指令碼，或以預設選項建立指定資料庫物件的指令碼。指令碼可以直接產生至「查詢編輯器」視窗、檔案或剪貼簿。

13-8-2 產生SQL Server指令碼精靈 在SQL Server的Management Studio提供資料庫物件的產生SQL指令碼精靈，可以自動產生指定物件的SQL指令碼檔案。精靈能夠逐步引導我們建立所需的指令碼，並且可以選取多個物件來產生指令碼。