Ch11.T-SQL 設計批次處理概念 T-SQL 的程式結構錯誤與例外處理機制 Ch12. 預存程序與觸發程序建立預存程序、預存程序的應用建立觸發程序、觸發程序的應用

第四篇 Transact –SQL的設計 • Ch11.T-SQL設計 • 批次處理概念 • T-SQL的程式結構 • 錯誤與例外處理機制 • Ch12.預存程序與觸發程序 • 建立預存程序、預存程序的應用 • 建立觸發程序、觸發程序的應用 • Ch13.檢視與自訂函數 • 檢視的各種類型、檢視的建立 • 建立自訂函數、資料欄位中如何使用自訂函數

第十一章 T-SQL 設計(Transact-SQL Programming)

Chapter 11 Outline • 11-1 在SQL敘述中使用註解 • 11-2 批次處理觀念 • 11-3 區域與全域變數 • 11-4 T-SQL的程式結構 • 11-5 錯誤處理 • 11-6 本章總結

11-1 在SQL敘述中使用註解 • 為了要加強資料存取的安全與效率，因此很多事情可以在伺服器端解決，因此衍生出許多的應用，如預存程序、觸發程序、檢視、自訂函數等。 • 為了要設計出強大或是有效率的功能，因此必需要了解T-SQL的設計。 • 由於程式可能很長，因此必須加上註解才有助於閱讀與維護 • 單行註解：-- • 多行註解：夾在 /* 與 */ 之間的文字皆是

11-2 批次處理觀念 • 批次（Batch） • 由一群SQL敘述所組成 • 批次結束地方用"GO"敘述來標示 • 指令碼（Script） • 由一個以上的批次所組成 • 由於SQL Server一次會執行批次內所有的SQL敘述，所以SQL敘述是否有錯誤就會影響整個結果 • 編譯錯誤(例如語法錯誤)：就會妨礙執行計劃的編譯，因此該批次的任何SQL敘述都不會被執行 • 執行階段的錯誤：通常(例如算術溢位)會停止目前的敘述句和批次中隨後進行的敘述句；但有某些錯誤(例如強制違規)只會停止目前違反條件的敘述句，而批次中所有剩餘的敘述句會繼續執行

11-2 批次處理觀念 • 錯誤批次處理實例 • 一個指令碼包含三個批次（每一個批次用“Go”標示）第一個批次執行的結果第二個批次因打錯資料表名稱無法執行第三個批次執行的結果

11-2 批次處理觀念 • 錯誤批次處理實例第一行SQL敘述執行的結果因為第二行SQL敘述錯誤就停止往下執行了

11-2 批次處理觀念 • 錯誤批次處理實例 • 同一批次內有錯誤仍會繼續進行的例子第一行SQL敘述因違反參考整合，因此執行失敗執行第二行SQL敘述所加入的資料錄第二行SQL敘述成功執行的訊息

11-2 批次處理觀念 • 批次處理之限制條件 • create default，create view ， create rule ，create procedure， create trigger等敘述在處理時不能與其他敘述存在。 • 如果使用ALTER變更資料表結構，則同一批次中不能參考新資料行。 • 在一個批次中，如果預存程序（Stored procedure）不是第一個被執行的敘述，則在預存程序名稱前必須加上"EXEC"指令。

11-3 區域與全域變數 • 在T-SQL中配合一般SQL敘述或流程控制敘述，我們還可以使用「變數」（Variables）來做程式設計。T-SQL的變數有兩種，分別為區域變數（Local Variables）與全域變數（Global Variables） • 區域變數是由‘@’符號開始的識別字 • 全域變數是由‘@@’符號開始的識別字

11-3 區域與全域變數 • T-SQL中區域變數是用DECLARE敘述來宣告，敘述的語法如下： • declare @Variable_name datatype [，@Variable_name datatype]…. • @Variable_name：自行命名之區域變數 • datatype可以是系統內建或使用者自訂的資料型態 • 區域變數的有效範圍是限制在宣告它的預存程序、觸發程序或批次中 • 當宣告區域變數後，它的初值（initial value）被設定為空值（Null value）

11-3 區域與全域變數 • 宣告區域變數範例一第一個批次執行的結果第二個批次不認識@a，所以發生錯誤

11-3 區域與全域變數 • 指定區域變數的值可使用set或select • SET的語法結構： • SET @local_variable = expression • expression為符合T-SQL語法的運算式 • 使用SELECT也有兩種方式，分別為指派選擇(Assignment Select)與擷取指派(Retrieval Select) • 指派選擇的語法結構： • SELECT @variable_name = expression(只是將SET改成使用SELECT而已)

11-3 區域與全域變數 • 擷取指派(Retrieval Select)選擇的語法結構與指派選擇相同，但是expression可以是SQL敘述，也就是可以將資料庫中的某一個特定值指派給區域變數。

11-3 區域與全域變數 • SQL Server預先設定的變數 • 使用者不能自行宣告使用全域變數或更改它們的值 • 用“@@”為開頭的識別字 • 使用者可以透過全域變數的值得知目前的一些伺服器狀態。例如： • @@CONNECTIONS：自上次啟動SQL Server時算起之連線或嘗試連線的次數 • @@ERROR：傳回上次執行T-SQL 敘述時的錯誤代碼

11-4 T-SQL的程式結構 • SQL Server 2005中的T-SQL語言，對SQL-92標準作了很多彈性的延伸。在原有的SQL語法中加入了許多類似程式控制的敘述，使得T-SQL更像一般的程式語言。 • T-SQL有以下常用的程式結構： • IF…ELSE • BEGIN…END敘述區塊 • PRINT • WHILE • CASE • GOTO • RETURN • WAITFOR • RAISERROR • TRY CATCH

11-4 T-SQL的程式結構 • IF…ELSE • 其語法如下： IF Boolean_expression {sql_statement│statement_block} END {sql_statement│statement_block} • Boolean_expression：是傳回TRUE或FALSE的運算式 • {sql_statement│statement_block}：以敘述區塊定義的Transact-SQL敘述或敘述區塊

11-4 T-SQL的程式結構 範例11-4.1：宣告一並指定一個區域變數，如果該區域變數>50，則印出'>50 '，否則印出 <'50'。declare @a int --宣告整數變數 set @a=77 --指定變數的值 if (@a>50) --判斷變數大小，可以不用() print '>50' else print '<50' print 'end'

11-4 T-SQL的程式結構 • BEGIN…END敘述區塊 • BEGIN與END是流程控制語言關鍵字，其語法如下： BEGIN { sql_statement │statement_block } END • {sql_statement│statement_block}是任何有效的Transact-SQL敘述式或以敘述式區塊定義的敘述式群組

11-4 T-SQL的程式結構 • 範例11-4.2：請將範例11-4.1改成只有小於50才會印出‘end’ declare @a int --宣告整數變數 set @a=77 --指定變數的值 if (@a>50) --判斷變數大小，可以不用() print '>50' else begin print '<50' print 'end' end 沒錯，就是這麼簡單，使用BEGIN…END將else後要執行的SQL敘述變成一個區塊就可以了

11-4 T-SQL的程式結構 • PRINT • Print敘述的主要功能是將使用者的訊息傳回用戶端。其語法如下： PRINT 'any ASCII text'│@local_variable│@@FUNCTION│string_expr • ‘any ASCII text‘：是一個文字字串 • local_variable：是一個任何有效字元資料型別的變數 • @@FUNCTION：是一個傳回字串結果的函數 • string_expr：是一個可傳回字串的運算式

11-4 T-SQL的程式結構 • 範例11-4.3：各種PRINT的輸出最後一個SQL敘述執行失敗，因為getdate()傳回的結果無法隱含轉為char或是varchar

11-4 T-SQL的程式結構 • WHILE，為T-SQL中之區塊重複執行的條件敘述其語法如下： WHILE Boolean_expression {sql_statement│statement_block} [BREAK] {sql_statement│statement_block} [CONTINUE] • BREAK：退出最內層的WHILE迴圈 • CONTINUE：重新開始WHILE迴圈，忽略掉在CONTINUE後的任何敘述

11-4 T-SQL的程式結構 範例11-4.5：求1+2+3+….n>1000中最小的n為何？ declare @target int, @n int ,@sum int select @target=1000,@n=1,@sum=0 while(1=1) --(1=1)永遠為TRUE，表示該迴圈會一直執行 begin set @sum=@sum+@n if(@sum>@target) break --大於目標直就停止WHILE迴圈 set @n=@n+1 continue print '這一行永遠執行不到' end print 'n='+cast(@n as char(5)) 遇到continue就會強制回到while判斷處

11-4 T-SQL的程式結構 • CASE敘述的功能是做多元選擇，在此敘述中有一個評估條件，評估條件清單並傳回多種可能的結果運算式之一。 • CASE分為二種格式： • 1.簡單CASE函數會比較運算式與一組簡單運算式 • 2.搜尋CASE函數會評估一組布林運算式

11-4 T-SQL的程式結構 • ( a）簡單CASE的語法結構： CASE input_expression WHEN when_expression THEN result_expression […n] [ELSE else_result_expression] END

11-4 T-SQL的程式結構 • （b）搜尋CASE的語法結構： CASE WHEN Boolean_expression THEN result_expression […n] [ELSE else_result_expression END

11-4 T-SQL的程式結構 • 經過運算後，得到的結果值有兩種型式： • 1.簡單CASE函數： • 評估input_expression • 傳回評估為TRUE的第一個result_expression • 若無input_expression = when_expression評估為TRUE，則在指定ELSE子句時，SQL Server會傳回else_result_expression；若未指定ELSE子句，則傳回NULL值 • 2.搜尋CASE函數： • 評估各WHEN子句的Boolean_expression • 傳回評估為TRUE的第一個result_expression • 若無Boolean_expression評估為TRUE，則在指定ELSE子句時，SQL Server會傳回else_result_expression；若未指定ELSE子句，則傳回NULL值

11-4 T-SQL的程式結構 • GOTO • GOTO敘述可以是巢狀的，其語法為：定義標籤： label：改變執行： GOTO label • label是當GOTO指向該標籤時，由此後開始繼續處理的點 • 標籤必須遵循識別項的規則 • 不論是否使用GOTO，標籤可以用作註解方法

11-4 T-SQL的程式結構 • 範例11-4.7：使用GTOT求1+2+3+….n>1000中最小的n為何？ declare @target int, @n int ,@sum int select @target=1000,@n=0,@sum=0 sumhere: set @n=@n+1 set @sum=@sum+@n if(@sum>@target) --到達目標，跳到finish標籤 goto finish else goto sumhere --未達目標，跳到sumhere標籤 finish: print 'n='+cast(@n as char(5))

11-4 T-SQL的程式結構 • RETURN • RETURN之功能為無條件退出查詢或程序 • 附隨有RETURN的敘述不會被執行，其語法為： RETURN[integer_expression] integer_expression：指定傳回的整數值 • 與預存程序一起使用時，RETURN不會傳回null值 • 如果想要獲得執行目前程序中的傳回值，則必須以下列形式輸入： • EXECUTE @return_status ＝procedure_name

11-4 T-SQL的程式結構 • WAITFOR • WAITFOR敘述的主要功能為指定觸發敘述區塊、預存程序或交易執行的時間、時間間隔或事件，其語法如下： WAITFOR{DELAY'time'│TIME'time'} • DELAY：指示SQL Server等待到指定的時數已過 • ‘time’：等待時數 • TIME：指定SQL Server等待到所指定的時間

11-4 T-SQL的程式結構 • 範例11-4.8：等待時間與指定時間的執行範例 Begin --CASE 1 waitfor DELAY '00:00:10' --等待10秒後執行 exec sp_helpdb end go --CASE 2 begin waitfor time '13:00'--等到13:00時才能執行 exec sp_helpdb end go

11-5 錯誤處理 • 在資料處裡時沒有人能保證一定會執行正確，因此就必須要有錯誤或是例外的處理，這樣才能讓使用者或是應用程式開發人員了解到有錯誤發生。 • 錯誤在處裡時可以分成三種狀況， • 第一種狀況就是系統會產生錯誤訊息 • 第二種是由使用者自行產生錯誤訊息 • 第三種則是使用例外狀況的錯誤處理

11-5 錯誤處理 • 通常在執行SQL敘述時可以使用全域變數@@ERROR來判斷是否發生錯誤，如有發生錯誤則可做錯誤處理。

11-5 錯誤處理 • 使用@@ERROR的方式需要精確知道資料可能會發生錯誤的地點，要不然就必須在可能出錯的SQL上都設置檢查，這樣撰寫SQL有點繁雜。 • 使用 TRY...CATCH 建構來處理 T-SQL程式碼中的錯誤時，當在執行TRY區塊內的T-SQL敘述句遇到錯誤狀況時，控制權會傳遞給可處理此錯誤的CATCH區塊。這樣就不會因為錯誤而停止SQL的執行。

11-5 錯誤處理 • TRY區塊是以BEGIN TRY敘述句開始，而以END TRY敘述句結束，結構如下： • BEGIN TRY sql_statement_1 END TRY BEGIN CATCH sql_statement_2 END CATCH • 上述的意義是如果執行sql_statement1錯誤時就會轉而執行sql_statement_2的錯誤處理機制，至於錯誤機制裡面要做些什麼事情就看需求而規劃

11-5 錯誤處理 • 範例11-5.2 使用TRY CATCH例外處理機制 declare @dividend int,@divisor int,@quotient int set @dividend=15 --被除數 set @divisor =0 --除數 begin try set @quotient= @dividend/@divisor --商 print '商數為'+cast(@quotient as char(10)) end try begin catch print '執行有錯誤：'+ERROR_MESSAGE() end catch

11-6 本章總結 • SQL Server中，一個批次（Batch），是由一群SQL敘述所組成，用“GO”敘述來結束批次。 • 指令碼（Script）是由一個以上的批次所組成。 • 所謂區域變數，是用DECLARE敘述作宣告，並由“@”符號開始的識別字，它的有效範圍是限制在宣告它的預存程序、觸發器或批次中，在宣告範圍外就變成無效。 • 區域變數可使用SELECT將值指派給變數，分成兩種：指派選擇與擷取指派。

11-6 本章總結 • 所謂全域變數，是SQL預先設定的變數，儲存系統相關的資訊，使用者不能字型宣告或改變其值，它是由“@@”符號開始的識別字，可使用SELECT來取得內容。 • T-SQL常用的程式結構包括BEGIN…END敘述區塊、PRINT、IF…ELSE、WHILE、CASE、GOTO、RETURN、WAITFOR、RAISERROR以及TRY CATCH。

Ch11.T-SQL 設計批次處理概念 T-SQL 的程式結構錯誤與例外處理機制 Ch12. 預存程序與觸發程序建立預存程序、預存程序的應用建立觸發程序、觸發程序的應用