13 Transact-SQL程式設計 13-1 Transact-SQL語言的基礎 13-2 批次的使用 13-3 註解與自訂訊息

Presentation on theme: "13 Transact-SQL程式設計 13-1 Transact-SQL語言的基礎 13-2 批次的使用 13-3 註解與自訂訊息"— Presentation transcript:

1

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

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

4 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程式碼檔案的批次、預存程序、自訂函數和觸發程序。

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

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

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

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

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

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

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

12 使用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 13-3 註解與自訂訊息 註解 PRINT指令輸出自訂訊息 USE指令轉換資料庫

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

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

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

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

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

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

20 13-4-1 宣告變數與變數初值- 宣告變數(範例)
int

21 宣告變數與變數初值-變數初值 SQL int = 100

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

23 13-4-1 宣告變數與變數初值-範例 SQL指令碼檔：Ch13_4_1.sql
int int = 100

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

25 13-4-2 指定變數值- 使用SET指令(範例1) SQL指令碼檔：Ch13_4_2_01.sql
int = 1000 * 1.02 PRINT ‘總額:' + AS char)

26 13-4-2 指定變數值- 使用SET指令(範例2) SQL指令碼檔：Ch13_4_2_02.sql
int = (SELECT SUM(學分) FROM 課程) PRINT ‘學分數:' + AS char)

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

28 13-4-2 指定變數值- 使用SELECT指令(範例1)
SQL指令碼檔：Ch13_4_2_03.sql varchar(12) = '陳會安' AS 姓名

29 13-4-2 指定變數值- 使用SELECT指令(範例2)
SQL指令碼檔：Ch13_4_2_04.sql varchar(12) varchar(10) = = 城市 FROM 員工 WHERE 薪水 >= 60000 AS AS 城市

30 13-4-2 指定變數值- 使用SELECT指令(範例3)
SQL指令碼檔：Ch13_4_2_05.sql varchar(5) = 'CS101' SELECT 課程編號, 名稱, 學分 FROM 課程 WHERE 課程編號

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

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

33 13-4-2 指定變數值- 變數值的種類(資料表變數範例)
SQL指令碼檔：Ch13_4_2_06.sql table ( std_no char(4), name varchar(12) ) SELECT 學號, 姓名 FROM 學生 WHERE 性別 = '男' SELECT *

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

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

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

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

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

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

40 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邏輯運算子 ＝ 指定運算子

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

42 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節：計算值欄位＞。

43 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

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

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

46 再談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運算

47 13-5-3 再談T-SQL的指定運算子-範例 SQL指令碼檔：Ch13_5_3.sql
int = int = 20 @y

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

49 類型轉換運算子-CAST運算子 CAST運算子是ANSI-SQL標準的類型轉換運算子，可以將資料從一種資料類型轉換成另一種資料類型，如下所示： PRINT 'Total Credits:' + AS char) PRINT CAST(‘ ' AS datetime) - 1

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

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

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

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

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

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

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

57 13-6-2 IF/ELSE條件控制指令-範例1 SQL指令碼檔：Ch13_6_2_01.sql
int = 125 <= 120 PRINT '半票' > 120 BEGIN PRINT '全票' PRINT 'height > 120' END

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

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

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

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

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

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

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

65 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 學生

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

67 13-6-4 CASE多條件函數- 搜尋CASE函數(範例1a)
SQL指令碼檔：Ch13_6_4_02.sql int = 25 = CASE < 15 THEN '小孩'

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

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

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

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

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

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

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

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

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

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

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

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

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

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

82 13-6-6 GOTO跳躍至指定標籤 -範例1b WHILE @category_Id < 3 BEGIN
+ 1 = 1 = 3 GOTO BREAK_POINT INSERT INTO TextBooks @category_Id) END = 0 BREAK_POINT: SELECT * FROM TextBooks DROP TABLE TextBooks

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

84 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 員工

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

86 13-6-8 IIF與CHOOSE函數- IIF()函數(範例)
SQL指令碼檔：Ch13_6_8_01.sql int = 65 int = 70 varchar(10) = IIF '數學高', '英文高' )

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

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

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

90 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

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

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

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

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

95 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

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

97 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

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

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

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