13 1 TransactSQL 13 1 1 TransactSQL 13

13 -1 Transact-SQL語言的基礎 • 13 -1 -1 Transact-SQL資料庫語言 • 13 -1 -2 Transact-SQL指令碼檔案

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

13 -4 -2 指定變數值使用SET指令(範例1) SQL指令碼檔：Ch 13_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 指定變數值使用SELECT指令(範例1) SQL指令碼檔：Ch 13_4_2_03. sql • 在宣告T-SQL變數@my. Name後，使用SELECT 指令來指定和顯示變數值，如下所示： DECLARE @my. Name varchar(12) SELECT @my. Name = '陳會安' SELECT @my. Name AS 姓名

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

13 -4 -4 SQL Server的系統函數-範例 SQL指令碼檔：Ch 13_4_4. sql • 使用系統函數取得SQL Server系統資訊，如下： DECLARE @My. Row. Count int, @My. Identity int INSERT 課程備份 2 SELECT 課程編號, 名稱, 學分 FROM 課程 WHERE 學分 >= 4 SET @My. Row. Count = @@ROWCOUNT SET @My. Identity = @@IDENTITY SELECT @My. Row. Count AS 影響的記錄數, @@SERVERNAME AS 伺服器名稱, @My. Identity AS 自動編號, @@ERROR AS 錯誤編號

13 -6 -2 IF/ELSE條件控制指令-範例1 SQL指令碼檔：Ch 13_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 -3 RETURN中斷查詢指令-範例1 b END ELSE PRINT '學生資料表沒有記錄資料!' PRINT '課程數: ' + CAST(@total AS char)

13 -6 -4 CASE多條件函數搜尋CASE函數(範例1 a) SQL指令碼檔：Ch 13_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函數(範例1 b) WHEN @age < 60 THEN '成人' WHEN @age < 100 THEN '老人' ELSE 'Free' END PRINT @type

13 -6 -5 WHILE迴圈控制指令-範例 SQL指令碼檔：Ch 13_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迴圈控制指令巢狀迴圈(範例1 a) SQL指令碼檔：Ch 13_6_5_02. sql • 在建立Text. Books資料表後，使用巢狀WHILE迴 圈來新增資料表的記錄資料，如下所示： DECLARE @book_Id int, @category_Id int CREATE TABLE Text. Books (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迴圈控制指令巢狀迴圈(範例1 b) WHILE @category_Id < 3 BEGIN SET @category_Id = @category_Id + 1 INSERT INTO Text. Books VALUES(@book_Id, @category_Id) END SET @category_Id = 0 END SELECT * FROM Text. Books DROP TABLE Text. Books

13 -6 -5 WHILE迴圈控制指令BREAK指令跳出迴圈(範例2 a) SQL指令碼檔：Ch 13_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指令跳出迴圈(範例2 b) 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指令繼續迴圈(範例3 a) SQL指令碼檔：Ch 13_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指令繼續迴圈(範例3 b) SET @counter = @counter + 1 IF @counter % 2 = 0 CONTINUE SET @total = @total + @counter END PRINT '總和: ' + CAST(@total AS char)

13 -6 -6 GOTO跳躍至指定標籤 -範例1 a SQL指令碼檔：Ch 13_6_6. sql • 在建立Text. Books資料表後，使用巢狀WHILE迴圈產生記 錄資料，批次是使用GOTO指令跳出巢狀迴圈，所以並沒 有執行完，如下所示： DECLARE @book_Id int, @category_Id int CREATE TABLE Text. Books (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跳躍至指定標籤 -範例1 b WHILE @category_Id < 3 BEGIN SET @category_Id = @category_Id + 1 IF @book_id = 1 AND @category_id = 3 GOTO BREAK_POINT INSERT INTO Text. Books VALUES(@book_Id, @category_Id) END SET @category_Id = 0 END BREAK_POINT: SELECT * FROM Text. Books DROP TABLE Text. Books

13 -6 -8 IIF與CHOOSE函數IIF()函數(範例) SQL指令碼檔：Ch 13_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 -1 錯誤處理結構-範例1 a SQL指令碼檔：Ch 13_7_1_01. sql • 使用TRY/CATCH指令建立除以零錯誤的錯誤處 理結構，如下所示： BEGIN TRY SELECT 1/0 -- 除以零的錯誤 END TRY BEGIN CATCH -- 顯示錯誤資訊 SELECT ERROR_NUMBER() AS Error. Number,

13 -7 -1 錯誤處理結構-範例1 b ERROR_SEVERITY() AS Error. Severity, ERROR_STATE() AS Error. State, ERROR_PROCEDURE() AS Error. Procedure, ERROR_LINE() AS Error. Line, ERROR_MESSAGE() AS Error. Message END CATCH

13 -7 -2 使用RAISERROR指令產生 錯誤訊息-產生錯誤訊息(範例) SQL指令碼檔：Ch 13_7_2_02. sql • 使用TRY/CATCH指令建立錯誤處理結構，其中的錯誤是 由RAISERROR指令產生錯誤編號 55555的自訂錯誤，如 下所示： BEGIN TRY RAISERROR (55555, 7, 10) END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS Error. Number, ERROR_SEVERITY() AS Error. Severity, ERROR_STATE() AS Error. State, ERROR_PROCEDURE() AS Error. Procedure, ERROR_LINE() AS Error. Line, ERROR_MESSAGE() AS Error. Message END CATCH

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