課程名稱：資料庫系統 授課老師：李春雄 博士 第 九 章 T-SQL程式設計 課程名稱：資料庫系統 授課老師：李春雄 博士

本章學習目標 1.讓讀者瞭解結構化查詢語言(SQL)與Transact- SQL(T-SQL)兩種語言之間的差異。

本章內容 9-1 何謂Transact-SQL？ 9-2 變數的宣告與使用 9-3 註解(Comment) 9-4 資料的運算 9-5 函數 9-6 流程控制 9-7 Try/Catch例外處理

9-1 何謂Transact-SQL？　 所謂Transact-SQL (T-SQL)是標準 SQL 語言的增強版，主要是用來控制Microsoft SQL Server資料庫的一種主要語言。由於目前的標準 SQL 語言(亦即SQL-92語法)是屬於非程序性語言，使得每一條SQL指令都是單獨的被執行，以導致指令與指令之間是無法傳遞參數，所以，在使用上往往不如傳統高階程式語言來的方便。 有鑑於此，MS SQL Server提供的T-SQL語言，除了符合SQL-92的規則(DDL,DML,DCL)之外，另外增加了變數、程式區塊、流程控制及迴圈控制…等第三代「程式語言」的功能，使其應用彈性大大的提昇。

9-2 變數的宣告與使用 在一般的程式語言中每一個變數都必須要宣告才能使用，而在T-SQL語言中也不例外。 【變數的分類】 9-2 變數的宣告與使用　 在一般的程式語言中每一個變數都必須要宣告才能使用，而在T-SQL語言中也不例外。 【變數的分類】 1.區域性變數：是由使用者自行定義，因此，必須要事先作宣告。 2.全域性變數：由系統提供，不需要宣告。

9-2.1 區域性變數(Local Variable) 【定義】是指用來儲存暫時性的資料。 【表示方式】以@ 為開頭 【宣告方式】使用DECLARE關鍵字作為開頭，其所宣告的變數之預設 值為NULL，我們可以利用SET或SELECT來設定初值。 【宣告語法】 說明1：變數的初始化的值都是NULL，而並非是0或空白字元。 說明2：當同時宣告多個變數時，必須要利用逗號隔開(，)。 【舉例】 DECLARE @變數名稱 資料型態 DECLARE @X INT, @Y INT -- 區域變數以@ 為開頭

【初值設定之語法】 第一種方法：利用SET設定初值 第二種方法：利用SELECT設定初值 第三種方法：從資料表中取出欄位值 【初值設定之語法】　 第一種方法：利用SET設定初值 第二種方法：利用SELECT設定初值 第三種方法：從資料表中取出欄位值 SET @變數名稱=設定值 SELECT @變數名稱=設定值 SELECT @變數名稱=欄位名稱 From 資料表名稱

【顯示方式】　 使用SELECT或PRINT敘述： 1. SELECT敘述：是以「結果視窗」呈現 2. PRINT敘述：是以「訊息視窗」呈現。

【舉例】 【解答】 【執行結果】 1. 結果視窗 2. 訊息視窗 【舉例】 　 請利用SET與SELECT來設定初值，並且利用SELECT與PRINT來顯示結果。 【解答】 【執行結果】 1. 結果視窗 2. 訊息視窗 DECLARE @Cus_Id nchar(10) -- 區域變數以@ 為開頭 DECLARE @Cus_Name nchar(10) SET @Cus_Id = 'C06' -- 設定區域變數初值 SELECT @Cus_Name = '王安' -- 用SELECT 也可拿來設定變數初值 SELECT @Cus_Id -- 顯示區域變數(Cus_Id)的內容 PRINT @Cus_Name -- 顯示區域變數(Cus_Name)的內容

【實作1】 請利用初值設定的第三種方法，來查詢「客戶代號」為C05的「客戶姓名」資料。 【解答】 執行結果： USE ch9_hwDB1 Go DECLARE @Cus_Id nchar(10) -- 區域變數以@ 為開頭 DECLARE @Cus_Name nchar(10) SET @Cus_Id = 'C05' -- 設定區域變數初值 Select @Cus_Name=客戶姓名 From 客戶資料表 Where 客戶代號=@Cus_Id Print '客戶代號=' + @Cus_Id + '客戶姓名=' + @Cus_Name

【實作2】 請利用變數來查詢所有學生中各科目成績在70分(含)以上的名單 【解答】 【 執行結果】 USE ch9_DB Go 【實作2】　 請利用變數來查詢所有學生中各科目成績在70分(含)以上的名單 【解答】 【 執行結果】 USE ch9_DB Go DECLARE @score int SET @score =70 -- 設定區域變數初值 Select 學號,課號,成績 From 選課資料表 Where 成績>=@score

9-2.2 全域性變數(Global Variable) 【定義】指用來取得系統資訊或狀態的資料。 【表示方式】@@全域變數 【說明】 在全域性變數前面加入「兩個(@@)符號」，後面不需要「小括號」。 【注意】它不需要經過宣告，即可使用。

表9-1 常用全域性變數一覽表 系統參數 說明 @@CONNECTIONS 表9-1 常用全域性變數一覽表 系統參數 說明 @@CONNECTIONS 傳回 SQL Server 上次啟動之後所嘗試的連接次數，成功和失敗都包括在內。 @@CPU_BUSY 傳回 SQL Server 上次啟動之後所花的工作時間。 @@CURSOR_ROWS 傳回在連接所開啟的最後一個資料指標中，目前符合的資料列數。 @@DATEFIRST 傳回 SET DATEFIRST 之工作階段的目前值。 SET DATEFIRST 會指定每週第一天。U.S. English 預設值是 7，也就是星期日。 @@ERROR 傳回最後執行的 Transact-SQL 陳述式的錯誤號碼。 @@IDENTITY 這是傳回最後插入的識別值之系統函數。 @@LANGUAGE 傳回目前所用的語言名稱。 @@LOCK_TIMEOUT 傳回目前工作階段的目前鎖定逾時設定 (以毫秒為單位)。 @@MAX_CONNECTIONS 傳回 SQL Server 執行個體所能接受的最大同時使用者連接數目。傳回的數目不一定是目前所設定的數目。 @@NESTLEVEL 傳回本機伺服器中執行目前預存程序的巢狀層級 (最初是 0)。 @@OPTIONS 傳回目前 SET 選項的相關資訊。 @@REMSERVER 傳回符合登入記錄所顯示的遠端 SQL Server 資料庫伺服器的名稱。 @@ROWCOUNT 傳回受到前一個陳述式所影響的資料列數。 @@SERVERNAME 傳回執行 SQL Server 的本機伺服器名稱。 @@SPID 傳回目前使用者處理序的工作階段識別碼。 @@TRANCOUNT 傳回目前連接的使用中交易數目。 @@VERSION 傳回目前安裝之 SQL Server 的版本、處理器架構、建置日期和作業系統。 資料來源：SQL Server 20008線上叢書 (http://msdn.microsoft.com/zh-tw/library/ms187766.aspx)

【舉例1】 查詢目前SQL Server伺服器的名稱 【解答】 【執行結果】 DECLARE @MyServerName nchar(20) 【舉例1】 　 查詢目前SQL Server伺服器的名稱 【解答】 【執行結果】 DECLARE @MyServerName nchar(20) SET @MyServerName=@@SERVERNAME SELECT @MyServerName AS 我的DB主機名稱

【舉例2】 查詢所有學生中各科目成績在70分(含)以上的筆數 【解答】 【執行結果】 USE ch9_DB 【舉例2】 　 查詢所有學生中各科目成績在70分(含)以上的筆數 【解答】 【執行結果】 USE ch9_DB DECLARE @score int SET @score =70 -- 設定區域變數初值 Select 學號,課號,成績 From dbo.選課資料表 Where 成績>=@score SELECT @@ROWCOUNT AS [70分(含)以上的筆數]

9-3 註解(Comment) 【定義】 在程式中加入註解說明，可以使得程式更容易閱讀與了解，也有助於後續的管理與維護工作。註解內的文字是提供設計者使用，系統不會執行它。 【兩種撰寫格式】 1.單行註解 2.區塊註解

一、單行註解(Comment) 【表示方式】以「--」作為開頭字元 【使用時機】可以寫在程式碼的後面或單獨一行註解。 【舉例】 【實例】 Declare @R int, @A int , @L int --宣告三個變數R,A,L --計算圓的面積與周長 Declare @R int, @A float , @L float --宣告三個變數R,A,L Declare @PI float=3.14 SET @R=3 --設定半徑 SET @A=@PI*SQUARE(@R) --計算圓的面積 SET @L=2*@PI*@R --計算圓的周長 PRINT '面積A=' + CONVERT(CHAR,@A) PRINT '周長L=' + CONVERT(CHAR,@L)

二、區塊註解 【表示方式】 「/*」與「*/」之間的所有內容 【使用時機】註解的內容超過一行時。 【舉例】 例如1：/* 註解內容 */ 例如2：/* 註解 可以包括多行內容 */ /*題目：計算圓的面積與周長 圓面積公式：PI*R^2 圓周長公式：*PI*R */

【實例】 /*題目：計算圓的面積與周長 圓面積公式：PI*R^2 圓周長公式：*PI*R */ Declare @R int, @A float , @L float --宣告三個變數R,A,L Declare @PI float=3.14 SET @R=3 --設定半徑 SET @A=@PI*SQUARE(@R) --計算圓的面積 SET @L=2*@PI*@R --計算圓的周長 PRINT '面積A=' + CONVERT(CHAR,@A) PRINT '周長L=' + CONVERT(CHAR,@L)

9-4 資料的運算　 我們都知道電腦處理資料的過程為：輸入---處理---輸出，其中「處理」程序通常是藉由運算式(Expression)來完成。每一行運算式都是由運算元(Operand)與運算子(Operator)所組合而成的。 例如：A=B+1，其中「A,B,1」稱為運算元，「=,+」則稱為運算子。 一般而言，「運算元」都是變數或常數，而運算子則可分為四種： (一) 指定運算子 (二) 算術運算子 (三) 關係運算子 (四) 邏輯運算子

9-4.1 指定運算子　 一般初學者，在撰寫程式中遇到數學上的等號「＝」時，都會有一些疑問，那就是何時才是真正的「等號」，何時才能當作「指定運算子」來使用。 基本上，在T-SQL中的等號「=」大部份都是當作「指定運算子」來使用，也就是在某一行運算式中，從「=」指定運算子的右邊開始看，亦即將右邊的運算式的結果指定給左邊的運算元。

【舉例】 請宣告A,B兩個變數為整數型態，並分別指定初值為1與2 【解答】 【注意】 我們在撰寫運算式時，特別小心的就是不能將常數或二個及二個變數以上放在「=」指定運算子的左邊。 Declare @A int, @B int SET @A=1 SET @B=2

【實作】 請在ch9_DB資料庫中，取出「學生資料表」的學生總筆數之後，再指定給Total變數，並利用PRINT列印出來。 【解答】 【執行結果】 USE ch9_DB Go DECLARE @Total int Select @Total=count(*) From dbo.學生資料表 PRINT '學生總筆數=' + CONVERT(CHAR,@Total)

9-4.2 算術運算子　 在程式語言有四則運算，而在T-SQL程式語言中也不例外，其主要的目的就是用來處理使用者輸入的數值資料。而在程式語言的算術運算式中，也是由數學運算式所構成的計算式，因此，在運算時也要注意到運算子的優先順序。如下表所示： 表9-2 算術運算子的種類 【說明】程式語言中的乘法是以星號「*」代替，數學中則以「×」代替。 運算子 功能 例子 執行結果 ┼ (加) A與B兩數相加 14+28 42 ─ (減) A與B兩數相減 29-14 14 ＊ (乘) A與B兩數相乘 5*8 40 ／ (除) A與B兩數相除 10/3 3.33333333…. % (餘除) A與B兩數相除後，取餘數 10 % 3 1

【實例】 請宣告A,B兩個變數為整數型態，並分別指定初值為1與2，再將變數A與B的值相加以後，指定給Sum變數 【解答】 【執行結果】 【實例】　 請宣告A,B兩個變數為整數型態，並分別指定初值為1與2，再將變數A與B的值相加以後，指定給Sum變數 【解答】 【執行結果】 Declare @A int, @B int , @SUM int SET @A=1 SET @B=2 SET @SUM =@A+@B SELECT @SUM AS 'A+B之和'

9-4. 3 關係運算子　 關係運算子是一種比較大小的運算式 , 因此又稱「比較運算式」。如果我們所想要的資料是要符合某些條件，而不是全部的資料時，那就必須要在Select子句中再使用Where條件式即可。並且也可以配合使用「比較運算子條件」來搜尋資料。若條件式成立的話則會傳回「True(真)」，若不成立的話則會傳回「False(假)」。如下表所示： 表9-3比較運算子表 註：設A代表「成績欄位名稱」,B代表「字串或數值資料」 運算子 功能 例子 條件式說明 ＝ (等於) 判斷A與B是否相等 A=B 成績=60 != (不等於) 判斷A是否不等於B A<>B 成績!=60 < (小於) 判斷A是否小於B A<B 成績<60 <= (小於等於) 判斷A是否小於等於B A<=B 成績<=60 > (大於) 判斷A是否大於B A>B 成績>60 >= (大於等於) 判斷A是否大於等於B A>=B 成績>=60

【實例】 請利用變數方式，在「選課資料表」中查詢任何課程成績「不及格60」的學生的「學號、課程代號及成績」 【解答】 【執行結果】 【實例】 　 請利用變數方式，在「選課資料表」中查詢任何課程成績「不及格60」的學生的「學號、課程代號及成績」 【解答】 【執行結果】 Use ch9_DB Go Declare @Score int Set @Score=60 SELECT 學號, 課號, 成績 FROM 選課資料表 WHERE 成績<@Score

9-4. 4 邏輯運算子 在Where條件式中除了可以設定「比較運算子」之外，還可以設定「邏輯運算子」來將數個比較運算子條件組合起來，成為較複雜的條件式。其常用的邏輯運算子如表9-4所示： 表9-4 邏輯運算子表 註：設A代表「左邊條件式」,B代表「右邊條件式」 運算子 功能 And(且) 判斷A且B兩個條件式是否皆成立 Or(或) 判斷A或B兩個條件式是否有一個成立 Not(反) 非 A的條件式 Exists(存在) 判斷某一子查詢是否存在

【實例】 請利用變數方式，在「選課資料表」中查詢修課號為「C005」且成績是「及格60分」的學生的「學號及成績」 【解答】 【執行結果】 【實例】 　 請利用變數方式，在「選課資料表」中查詢修課號為「C005」且成績是「及格60分」的學生的「學號及成績」 【解答】 【執行結果】 Use ch9_DB Go Declare @Score int Declare @CNo nchar(10) Set @Score=60 Set @CNo='C005' SELECT 學號, 課號, 成績 FROM 選課資料表 WHERE 成績>=@Score And 課號=@CNo

9-5 函數 在SQL Server中的函數種類非常的多，除了第七章介紹的函數之外，在本章節中，再進一步介紹以下五種函數。 一、轉換函數 9-5 函數 在SQL Server中的函數種類非常的多，除了第七章介紹的函數之外，在本章節中，再進一步介紹以下五種函數。 一、轉換函數 二、時間函數 三、聚合函數 四、排序函數 五、常用系統函數

9-5.1 使用「轉換函數」 基本上，在SQL Server中資料要進行運算時，必須要有相同的資料型態，但是，如果遇到不同運算元要運算時，在SQL Server中有兩種轉換方法： 一、隱含轉換(Implicit Conversion)：小轉大 二、強制轉換(Explicit Conversion)：大轉小

一、隱含轉換(Implicit Conversion) 隱含轉換又稱為自動轉換，也就是將表示範圍較小的資料型態轉換成表示範圍較大的資料型態，由於此種轉換方式是由系統自動處理，所以不會出現錯誤訊息。 【優點】小轉大時，原始資料不會「失真」現象。 【實例】小轉大(例如smallint int)

二、強制轉換　 「強制轉換」顧名思義就是將表示範圍較大的資料型態強制轉換成表示範圍較小的資料型態(例如：intsmallint)或不同資料型態的轉換(例如intnchar)。 【缺點】大轉小或不同型態轉換時，原始資料會可能會有「失真」現象。 【使用方法】指定轉換。 (一)CAST( ) (二)CONVERT( )

(一)CAST( ) 【語法】CAST(運算式 AS資料型態 ) 【說明】指將運算式的結果轉換成指定的資料型態。 【例如】CAST(GetDate() As nchar(11)) SELECT CAST(GetDate() As nchar(11))

(二)CONVERT( ) 【語法】CONVERT(資料型態，運算式) 【說明】指將運算式的結果轉換成指定的資料型態。 【例如】CONVERT(int,rand()*10) 【例如】CONVERT(nchar, @Score)

9-5.2 使用「時間函數」　 【定義】T-SQL除了具有SQL語言基本功能之外，還可以提供「時間函數」來搜尋資料。其常用的時間函數如表9-5所示： 表9-5 時間函數表 運算子 功能 GetDate( ) 取得目前系統的時間 Year( ) 取得目前指定日期的西元年 Month( ) 取得目前指定日期的月份 Day( ) 取得目前指定日期的日期 DatePart( ) 取得目前指定日期的各份格式值 DateName( ) 取得目前指定日期的各份格式之文字名稱 DateAdd( ) 取得目前指定日期再加上指定的間隔值 DateDiff( ) 取得兩個日期之間的時間間隔單位數目

一、GetDate( ) 【定義】取得目前系統的時間，它可運用於交易資料的追蹤與查詢。 【語法】 GetDate( ) SQL指令 【舉例】建立「學生基本資料表」，並記錄學生的註冊時間。 SQL指令 use ch9_hwDB1 Go Create Table 學生基本資料表 ( 學號 nchar(8) not null, 姓名 nchar(10) not null, 註冊時間 datetime default GetDate() ) Insert Into 學生基本資料表(學號,姓名) Values('S1001','一心')

二、Year( ) 【定義】取得目前指定日期的西元年。 SQL指令 【語法】 Year(date ) 【舉例】請查詢出「一心」同學今年註冊的年份 【執行結果】 SQL指令 use ch9_hwDB1 Go Select 姓名,YEAR(註冊時間) As 註冊年份 From 學生基本資料表 Where 姓名='一心'

三、Month( ) 【定義】取得目前指定日期的月份。 【語法】Month(date ) 【舉例】請在ch9_hwDB1資料庫中，查詢在8,9,10三個月份生日的 員工姓名及生日。 SQL指令 use ch9_hwDB1 Go --第一個寫法： Select 員工姓名,生日 From dbo.員工資料表 Where month(生日)>=8 And month(生日)<=10   --第二個寫法： Where month(生日) between 8 and 10 --第三個寫法： Where month(生日) In(8,9,10)

四、Day( ) 【定義】取得目前指定日期的日期。 【語法】Day(date ) SQL指令 【舉例】判斷目前的日期是否為月初(1號)，如果是，則印出「月初領 薪水」，如果不是，則印出「不是月初」 SQL指令 use ch9_hwDB1 Go if (Day(GetDate())=1) Print '月初領薪水' else Print '不是月初'

五、DatePart( ) 【定義】取得目前指定日期的各份格式值。 【語法】 DatePart (detepart,date ) SQL指令 【舉例】查詢「學生基本資料表」中學生姓名「一心」的註冊時間之詳細資料。 【執行結果】 SQL指令 use ch9_hwDB1 Go Declare @MyDate datetime Set @MyDate=(Select 註冊時間 From 學生基本資料表 Where 姓名='一心') Select DATEPART(YY,@MyDate) As 年份, DATEPART(qq,@MyDate) As 季節, DATEPART(mm,@MyDate) As 月份, DATEPART(dd,@MyDate) As 日期

六、DateName( ) 【定義】取得目前指定日期的各份格式之文字名稱。 SQL指令 【語法】 DateName (detepart,date ) 【舉例】查詢「學生基本資料表」中學生姓名「一心」的註冊時間之詳細資料。 【執行結果】 SQL指令 use ch9_hwDB1 Go Declare @MyDate datetime Set @MyDate=(Select 註冊時間 From 學生基本資料表 Where 姓名='一心') Select DateName(YY,@MyDate) As 年份, DateName(qq,@MyDate) As 季節, DateName(mm,@MyDate) As 月份, DateName(dd,@MyDate) As 日期

七、DateAdd( ) 【定義】取得目前指定日期再加上指定的間隔值。 【語法】 DateAdd (detepart, 間隔值, date ) 【舉例】請查詢「訂單資料表」中客戶的訂單日期與10天必須要送達的日期。 【執行結果】 SQL指令 use ch9_hwDB1 Go Select 訂單編號,訂單日期,DATEADD(DD,10,訂單日期) As 最晚送達日期 From dbo.訂單資料表 Order by 訂單編號

八、DateDiff( ) 【定義】取得兩個日期之間的時間間隔單位數目。 【語法】 DateDiff(detepart, 開始日期, 結束日期 ) 【舉例】請查詢「訂單資料表」中客戶的訂單日期與交貨日期的各天數為何。 【執行結果】 SQL指令 use ch9_hwDB1 Go Select 訂單編號,訂單日期,DateDiff(DD,訂單日期,交貨日期) As 準備天數 From 訂單資料表 Order by 訂單編號

9-5.3 使用「聚合函數」　 【定義】 T-SQL除了具有SQL語言基本功能之外，還可以提供「聚合函數」來搜尋資料。其常用的聚合函數如表9-6所示： 表9-6 聚合函數 聚合函數 說明 Count(*) 計算個數函數 Count(欄位名稱) 計算該欄位名稱之不具NULL值列的總數 Avg 計算平均函數 Sum 計算總合函數 Max 計算最大值函數 Min 計算最小值函數

一、記錄筆數(Count) 【語法】Count(*) 【說明】傳回記錄筆數 【範例】請在「ch9_DB」資料庫中，利用變數來取得「學生資 料表」中全班人數之後，再列印出來。 【執行結果】 use ch9_DB Declare @Total Int Select @Total = Count(*) FROM 學生資料表 Print '全班學生人數為: ' +Convert(Char(3),@Total)+'人'

二、平均數(AVG) 【語法】Avg(數值型態的欄位名稱) 【說明】傳回平均數 【範例】請在「ch9_DB」資料庫中，利用變數來取得「資料庫成績單」學生「成績」平均成績之後，再列印出來。 【執行結果】 use ch9_DB Declare @Average Int -- 列出學生「資料庫」平均成績 Select @average = Avg(成績) From dbo.資料庫成績單 Print '學生「資料庫」平均成績:' +Convert(Char(6),@average)+'分'

三、總合(Sum) 【定義】SUM函數是用來傳回一組記錄在某欄位內容值的總和。 【語法】SUM(數值型態的欄位名稱) 【範例】請在「ch9_DB」資料庫中，利用變數來取得「選課成績表」 中全班「總分」之後，再列印出來。 【執行結果】 use ch9_DB Declare @SUM Int Select @SUM = SUM(成績) FROM 選課資料表 Print '全班總分: ' +Convert(Char(3),@SUM)

四、最大值(Max) 【定義】MAX函數用來傳回一組記錄在某欄位內容值中的最大值。 【語法】MAX (欄位名稱) 【範例】請在「ch9_DB」資料庫中，利用變數來取得「選課成績表」 中全班「最高分」之後，再列印出來。 use ch9_DB Declare @MAX Int Select @MAX = MAX(成績) FROM 選課資料表 Print '全班最高分: ' +Convert(Char(3),@MAX)

五、最小值(Min) 【定義】MIN函數用來傳回一組記錄在某欄位內容值中的最小值。 【語法】MIN (欄位名稱) 【範例】請在「ch9_DB」資料庫中，利用變數來取得「選課資料表」中全班「最低分」之後，再列印出來。 use ch9_DB Declare @MIN Int Select @MIN = MIN(成績) FROM 選課資料表 Print '全班最低分: ' +Convert(Char(3),@MIN)

9-5.4 使用「排序函數」 基本上，在SQL語法中，我們可以利用Order by來排序資料表中的記錄順序，但是，如果欲顯示排序後的「排名」結果，那就必須要透過「排序函數」。 【排序分類】 (一)ROW_NUMBER( )：當有相同的值時，仍有不同的編號。 (二)RANK( )：當有相同的值時，則會有相同的編號，並且在下一筆 記錄的編號「會」自動跳號。 (三)DENSE_RANK()：當有相同的值時，則會有相同的編號，並且在 下一筆記錄的編號「不會」自動跳號。

一、ROW_NUMBER( ) 【定義】依照資料的筆數進行排序，當有相同的值時，仍有不同的編號。 【 例如】有六位學生的成績如下： 【範例】請在「ch9_DB」資料庫中，利用ROW_NUMBER( )函數來進行成績的排序，當有同分時，仍依不同的名次排序。 【 解答】 【執行結果 】 原始成績 90 80 70 60 排名 1 2 3 4 5 6 use ch9_DB Select 學號,姓名,成績,ROW_NUMBER ()Over(Order by 成績 DESC) As 排名次 From dbo.資料庫成績單

二、 RANK( ) 【 解答】 【執行結果 】 【定義】 依照資料的筆數進行排序，當有相同的值時，則會有相同的編號。 並且在下一筆記錄的編號「會」自動跳號。 【 例如】有六位學生的成績如下： 【範例】請在「ch9_DB」資料庫中，利用RANK( )函數來進行成績的排序，當有同分時，則會有相同的名次。 【 解答】 【執行結果 】 原始成績 90 80 70 60 排名 1 2 4 6 use ch9_DB Select 學號,姓名,成績,RANK()Over(Order by 成績 DESC) As 排名次 From dbo.資料庫成績單

三、 DENSE_RANK() 【定義】依照資料的筆數進行排序，當有相同的值時，則會有相同的編號。並且在下一筆記錄的編號「不會」自動跳號。 【 例如】有六位學生的成績如下： 【 實例】請在「ch9_DB」資料庫中，利用DENSE_RANK( )函數來進行成績的排序，當有同分時，則會有相同的名次。並且在下一筆記錄的名次「不會」自動跳號。 【解答 】 【執行結果 】 原始成績 90 80 70 60 排名 1 2 3 4 use ch9_DB Select 學號,姓名,成績,DENSE_RANK()Over(Order by 成績 DESC) As 排名次 From dbo.資料庫成績單

9-5.5 常用系統函數 基本上，在SQL語法中，我們常用的系統函數有以下五種： 1.CASE函數 2.CURRENT_USER函數 9-5.5 常用系統函數 基本上，在SQL語法中，我們常用的系統函數有以下五種： 1.CASE函數 2.CURRENT_USER函數 3.SYSTEM_USER函數 4.IDENTITY函數 5.ISNULL函數

一、CASE函數 在日常生活中，我們所面臨的決策可能不只一種情況，也有可能兩種情況，甚至兩種以上的不同情況。在前面已經介紹過兩種情況的結構，但是，如果我們所面對的情況有兩種以上時，則必須要使用多重選擇結構。常見的有兩種結構： 1.巢狀IF結構(下一單元會有詳細說明) 2.逐一比對結構Case 【語法】 CASE WHEN 條件式1 THEN 敘述1 WHEN 條件式2 THEN 敘述2 …… WHEN 條件式N THEN 敘述N ELSE 敘述N END

【範例】 題目：請利用Case 來設計單選題 【執行結果】 DECLARE @i int , @answer nvarchar(20) SET @i=2 Print '下列何者是DBMS？(1)Excel(2)SQL Server (3)Word' SET @answer = CASE @i WHEN 1 THEN '(1)Excel' WHEN 2 THEN '(2)SQL Server' WHEN 3 THEN '(3)Word' END PRINT '[答案：]' + @answer

【實作】 請利用CASE WHEN列出客戶指定排序 (1.台北市2.台中市3.台南市4.高雄市5.其他用升冪) (1)排序前 (2)排序後 (1)排序前 (2)排序後 【解答】 use ch9_hwDB1 Select * from dbo.客戶資料表 ORDER BY Case when 城市='台北市' then 1 when 城市='台中市' then 2 when 城市='台南市' then 3 when 城市='高雄市' then 4 else 5 End ,城市 asc

二、CURRENT_USER函數 【語法】CURRENT_USER 【說明】傳回目前使用者的名稱。這個函數相當於 USER_NAME()。 【實例】顯示目前使用者的名稱 【執行結果】 DECLARE @MyUserName nchar(20) SET @MyUserName=CURRENT_USER SELECT @MyUserName AS 目前使用者的名稱

三、SYSTEM_USER函數 【語法】SYSTEM_USER 【說明】如果未指定預設值，則可將系統提供的目前登入值插入資料表中。 【實例】顯示目前使用者的名稱 【執行結果】 DECLARE @MySYSTEM_USER nchar(20) SET @MySYSTEM_USER=SYSTEM_USER SELECT @MySYSTEM_USER AS 目前系統使用者的名稱

四、IDENTITY函數 【實例】請利用IDENTITY函數對「選課資料表」中的選課記錄來 產生自動編號 【執行結果】 【語法】IDENTITY(資料型態 [,自動編號起始值,每次遞增值]) AS 流水號欄位 【說明】在資料表中增加一個流水號欄位名稱。 【實例】請利用IDENTITY函數對「選課資料表」中的選課記錄來 產生自動編號 use ch9_hwDB1 go Select IDENTITY(int,1,1) as 序號,客戶姓名,電話 Into 客戶備份表 From dbo.客戶資料表   Select * From 客戶備份表 【執行結果】

五、ISNULL函數 【執行前】 【執行後】 【語法】ISNULL (檢查欄位 , 指定的取代值) 【說明】以指定的取代值來取代 NULL。 【舉例】請在ch9_DB資料庫中，將「選課資料表」中缺考(Null)的成績設定為50分。 【執行前】 【執行後】 use ch9_DB go SELECT 學號,AVG(成績)AS 平均成績 --執行前 FROM dbo.選課資料表 Group by 學號   SELECT 學號,AVG(ISNULL(成績, 50))AS 平均成績 --執行後

9-6 流程控制 傳統的結構化程式設計中有三種結構，而在T-SQL中也不例外。 9-6 流程控制 傳統的結構化程式設計中有三種結構，而在T-SQL中也不例外。 (1)循序(Sequential)：簡單命令式的指令，如X=Y+Z。 (2)選擇(Selection)：需做決策時，用 IF-ELSE 指令。 (3)迴圈(Repetition)：當需反覆時，用WHILE 指令。 一、循序結構(Sequential) 二、選擇結構(Selection) 三、迴圈結構(Loop)

除此之外，下表中為T-SQL常用來控制流程的關鍵字： 說明 Begin/End 定義程式區塊 If-Then 條件判斷式 Case When 搭配Order by之條件判斷式 While 重複結構 Break 中止最內層的While迴圈 Continue 啟動While迴圈 Goto label 跳到指定的label之後的程式 Waitfor 設定程式延遲執行 Return 結束並傳回值 Execute 執行程式

9-6.1 Begin/End 【定義】指用來定義程式區塊。 基本上，Begin/End都會與If/Else搭配使用。 【語法】 【範例】顯示全班同學資料 Begin 敘述區塊 End Use ch9_DB Begin Select * From 學生資料表 End

9-6.2 IF-ELSE 【定義】if的中文意思就是「如果…就…」。 在單一選擇結構中，只會執行條件成立時的敘述。 【語法】 其中 (條件式)  是一關係運算式 或 邏輯運算式 【說明】1.以if為首的條件式必須放在 ( )之內，之後的敘述放在它後面。 2.如果「條件式」成立(True)，就執行後面的「敘述區塊1」。 如果「條件式」不成立(False)，就執行後面的「敘述區塊2」。 If (條件式) Begin 敘述區塊1 End Else 敘述區塊2

【範例】 判斷目前的時間是早上或中午的程式 declare @time datetime=getdate()--取得系統目前的時間 declare @Welcome Nvarchar(13) if (datepart(hh,@time)<12) Begin set @welcome=N'早安' End else set @welcome=N'午安' Select @welcome

【實作】 請利用變數及if/else來查詢學號S1001同學所修課程代號為C001的 成績及判斷是否及格。 【解答】 Use ch9_DB 【實作】　 請利用變數及if/else來查詢學號S1001同學所修課程代號為C001的 成績及判斷是否及格。 【解答】 Use ch9_DB Declare @score int -- 印出學號S1001同學所修課程代號為C001的成績 SELECT @score=成績 FROM dbo.選課資料表 WHERE 學號='S1001' And 課號='C001' -- 判斷是否及格 BEGIN IF @score >= 60 PRINT '學號S1001同學所修課程代號為C001的成績是：及格' ELSE PRINT '學號S1001同學所修課程代號為C001的成績是：不及格' END

9-6.3 多重選擇結構　 在日常生活中，我們所面臨的決策可能不只一種情況，也有可能兩種情況，甚至兩種以上的不同情況。在前面已經介紹過兩種情況的結構，但是，如果我們所面對的情況有兩種以上時，則必須要使用多重選擇結構。常見的有兩種結構： 1. IF/ELSE IF/ELSE條件式判斷 2. CASE...WHEN條件式判斷

一、逐一比對結構IF/ELSE IF/ELSE 【定義】此種結構是雙重結構的改良版，它可以使用於多種選擇情況。 【語法】 If (條件式1) Begin 敘述區塊1 End Else if(條件式2) 敘述區塊2 Else 敘述區塊3 【說明】 如果「條件式1」不成立，就繼續往下判斷「條件式2」，依樣畫葫蘆的判斷下去，直到所有的條件式判斷完為止，否則就執行「敘述區塊3」。 【使用時機】當條件式有兩種以上時。

【實作範例】 請在ch9_DB資料庫中，利用if/else if /else函數在「選課資料表」中找出全班最高「成績」的等級，其等級的分類規則如下： 條件： (1) 90(含)分以上為：優等 (2)80~89分為：甲等 (3)70~79分為：乙等 (4) 60~69分為：丙等 (5) 60分以下為：丁等

use ch9_DB declare @Level char(13) Declare @Max_Score int Select @Max_Score=Max(成績) From dbo.選課資料表 if (@Max_Score>=90) print '全班最高分=' + CONVERT(CHAR,@Max_Score)+ '【優等】' else if (@Max_Score>=80 AND @Max_Score<90) print '全班最高分=' + CONVERT(CHAR,@Max_Score)+ '【甲等】' else if (@Max_Score>=70 AND @Max_Score<80) print '全班最高分=' + CONVERT(CHAR,@Max_Score)+ '【乙等】' else if (@Max_Score>=60 AND @Max_Score<70) print '全班最高分=' + CONVERT(CHAR,@Max_Score)+ '【丙等】' else print '全班最高分=' + CONVERT(CHAR,@Max_Score)+ '【丁等】‘

二、逐一比對結構 CASE...WHEN 【定義】 if/else if/else與CASE...WHEN結構具有相同的功能，但如果條件很多時，使用if/else if/else結構就很容易混亂了。因此，當程式中的條件式(Condition)超過兩個以上時，最好使用CASE...WHEN結構，它可以使程式較為精簡且可讀性較高。

當條件式1成立時，則執行敘述1，條件式2成立時，則執行敘述2，如果所有的條件式都不成立時，則執行敘述N+1。 【語法】 【說明1】 當條件式1成立時，則執行敘述1，條件式2成立時，則執行敘述2，如果所有的條件式都不成立時，則執行敘述N+1。 【使用時機】當條件式有兩種以上時。 CASE WHEN 條件式1 THEN 敘述1 WHEN 條件式2 THEN 敘述2 ………………. WHEN 條件式N THEN 敘述N ELSE 敘述N+1 END

【實作範例】 請在ch9_DB資料庫中，利用CASE WHEN函數將「選課資料表」中「成績」依照分數來分等級，其規則如下： 條件：(1) 90(含)分以上為：優等 (2)80~89分為：甲等 (3)70~79分為：乙等 (4) 60~69分為：丙等 (5) 60分以下為：丁等 請在查詢之後，顯示：「學號，課號，成績，等級」四個欄位資料。

【解答】 【執行結果】 use ch9_DB Select 學號,課號,成績, Case 【解答】 【執行結果】 use ch9_DB Select 學號,課號,成績, Case when (成績>=90) then '優等' when (成績>=80 AND 成績<90) then '甲等' when (成績>=70 AND 成績<80) then '乙等' when (成績>=60 AND 成績<70) then '丙等' else '丁等' End AS 等級 from dbo.選課資料表

9-6.4 WHILE 迴圈結構 　 【定義】在一般的程式語言中，如果預先已知道了迴圈要執行的次數，使用for計數迴圈是一個很好的選擇，但T-SQL語言中並沒有for計數迴圈可以使用。其主要的原因就是資料庫中的資料表之記錄筆數，我們無法預先知道，所以使用 while迴圈會是一個很好的選擇。while迴圈是屬於前測試迴圈，當條件式「成立(True)」時，則執行迴圈敘述。 【語法】 While(條件式) Begin {SQL語法 | 敘述區塊} [BREAK] [CONTINUE] End

【語法】 【說明】 (1)while指當條件式成立時，才會反覆執行迴圈內的敘述區塊。 Begin {SQL語法 | 敘述區塊} [BREAK] [CONTINUE] End 【說明】 (1)while指當條件式成立時，才會反覆執行迴圈內的敘述區塊。 (2)先判斷while指令後的條件式是否成立，若是，則執行迴圈內的敘述區塊之後，再一次判斷該條件是否成立，若是則繼續，否則跳到while迴圈。

【範例1】計算1+2+..+10的程式 declare @i int=1,@sum int=0 while (@i<=10) 【範例1】計算1+2+..+10的程式　 declare @i int=1,@sum int=0 while (@i<=10) begin set @sum+=@i set @i+=1 End Select @sum

【實作】列印出1~10中的偶數值 DECLARE @i int SET @i=0 WHILE @i<10 BEGIN 【實作】列印出1~10中的偶數值　 DECLARE @i int SET @i=0 WHILE @i<10 BEGIN SET @i = @i + 1 IF @i % 2 = 0 PRINT CONVERT(char, @i) + '是偶數' END

(3) break敘述會使程式強迫跳離迴圈，繼續執行迴圈外下一個敘述，若其出現在巢狀迴圈內，則跳離該層迴圈，而break敘述在while迴圈中的比較如下： Begin 程式區塊1; break; 程式區塊2; End 程式區塊3;

【範例2】利用break來設計1+2+..+10的程式 declare @i int=1,@sum int=0 while (1=1) begin set @sum+=@i set @i+=1 if @i>10 break End Select @sum

(4) continue則是強迫程式跳到迴圈的起頭，當遇到其敘述時，停止執行迴圈主體，而到迴圈的最前面開始處繼續執行，而continue敘述在while迴圈中的比較如下： Begin 程式區塊1; continue; 程式區塊2; End 程式區塊3;

【範例3】利用continue來設計1+3+..+9的程式 declare @i int=0,@sum int=0 WHILE @i<10 begin set @sum+=@i set @i+=1 if (@i% 2=0) continue Print 'i=' + CONVERT(char, @i) + ' sum=' + CONVERT(char, @sum) End

9-6.5 WaitFor 一、WaitFor Delay ‘time’ 【語法】 【說明】time的格式為：hh:mm:ss，最多可以暫停24小時 【範例】延遲10秒後，公佈及格名單 WaitFor Delay ‘time’ WaitFor Delay '00:00:10' Use ch9_DB GO SELECT 學號,課號,成績 FROM dbo.選課資料表 WHERE 成績>=60

【定義】指系統先等待一段時間之後，再在使用者指定某一個時間點繼 續執行。其中‘time’可以使用datetime格式，但無法使用 日期部份。 二、WaitFor Time ‘time’ 【定義】指系統先等待一段時間之後，再在使用者指定某一個時間點繼 續執行。其中‘time’可以使用datetime格式，但無法使用 日期部份。 【語法】 【說明】time的格式為：hh:mm:ss 【範例】每天早上10整，公佈訂單資料。 WaitFor Time ‘time’ WaitFor Time '10:00:00' Use ch9_DB GO SELECT 訂單編號,訂單日期 FROM 訂單資料表

9-6.6 Return 【定義】指強迫結束目前正在執行中的程序。 【語法】Return(傳回值) 主程式。 【範例】計算圓的面積與周長 Create Procedure Circle_Area (@R int, @pi decimal(3,2) ) AS Return(@pi*@R*@R) Go ---計算圓的面積與周長 Declare @CArea decimal Exec @CArea =Circle_Area 3,3.14 Select '半徑為3的圓面積=' + CONVERT(char,@CArea)

9-6.7 Execute批次執行命令 【定義】指用來批次執行T-SQL的批次命令。 【語法】EXEC(sql指令) 【解答】 Use ch9_DB Go -- 印出成績是70到80分之間的學生 DECLARE @sql VARCHAR(256) SET @sql='SELECT * FROM dbo.選課資料表WHERE 成績 BETWEEN 70 AND 80 ORDER BY 成績' EXEC(@sql) --執行sql指令

9-7 Try/Catch例外處理　 【定義】 當我們撰寫完成的程式，在執行階段時產生錯誤或不正常狀況，稱之為例外。在Transact-SQL 中提供Try/Catch語法來專門處理例外狀況。其目的就是對於可能出現的錯誤，可以利用Try/Catch結構來捕捉可能的錯誤，並且我們也可以針對可能的錯誤，自行撰寫所需的錯誤處理程序。 【語法】 01 02 03 04 05 06 07 08 BEGIN TRY --可能會產生錯誤的程式區段       {SQL語法 | 敘述區塊} END TRY BEGIN CATCH --定義產生錯誤時的例外處理程式碼       [{SQL語法 | 敘述區塊} ] END CATCH

【實例】 執行結果： BEGIN TRY --可能會產生錯誤的程式區段 Declare @x int ,@y int ,@z int 【實例】　 01 02 03 04 05 06 07 08 09 10 11 12 13 14 BEGIN TRY --可能會產生錯誤的程式區段 Declare @x int ,@y int ,@z int Set @x = 10 Set @y = 0; Set @z = @x / @y END TRY BEGIN CATCH --定義產生錯誤時的例外處理程式碼 print '兩個數相除，分母不能為!' END CATCH print 'x/y=' + CONVERT(char, @z) 執行結果：