1、第六講第六講 T-SQL程序設計程序設計教學內容教學內容要求學完本章，掌握以下內容：要求學完本章，掌握以下內容：nT-SQL的變量的變量n程序流程控制程序流程控制lIF.ELSElWHILElCASE 學習目標及重點學習目標及重點學習目標學習目標n掌握在掌握在T-SQL程序設計中使用變量程序設計中使用變量n掌握流程控制語句的使用掌握流程控制語句的使用重點與難點重點與難點n如何在如何在T-SQL程序設計中使用變量程序設計中使用變量n如何使用語句控制程序的執行順序如何使用語句控制程序的執行順序6.1 變量變量 n變量是變量是SQL Server用來在其語句間傳遞數據的用來在其語句間傳遞數據的方式之

2、一，它由系統或用戶定義并賦值。方式之一，它由系統或用戶定義并賦值。nSQL Server中的變量分為局部變量和全局變量中的變量分為局部變量和全局變量兩種兩種n全局變量的名稱以兩個全局變量的名稱以兩個字符開始，由系統字符開始，由系統定義和維護；定義和維護；n局部變量名稱以一個局部變量名稱以一個字符開始，由用戶自字符開始，由用戶自己定義和賦值。己定義和賦值。6.1.1 全局變量全局變量nSQL Server使用全局變量來記錄使用全局變量來記錄SQL Server服務器的活動狀態。服務器的活動狀態。n全局變量是一組由全局變量是一組由SQL Server事先定義事先定義好的變量，這些變量不能由用戶參與

3、定好的變量，這些變量不能由用戶參與定義，是只讀的。義，是只讀的。幾個常用的全局變量的使用方法幾個常用的全局變量的使用方法n（1）ERRORl該全局變量將返回最后執行的Transact-SQL語句的錯誤代碼，數據類型為integer。l在SQL Server執行一個Transact-SQL語句之后，如果語句成功執行，則ERROR將返回0；l如果出現一個錯誤，則返回一條錯誤信息。lERROR變量將返回這條錯誤信息的代碼，可以從sysmessages系統表中查看有關錯誤信息代碼的含義n例：使用例：使用ERROR變量在一個變量在一個UPDATE語句中檢測限制檢語句中檢測限制檢查沖突（錯誤代碼為查沖突（

4、錯誤代碼為#547）nUPDATE StudInfo Set StudSex=ll Where StudNo=99070470 nIF ERROR=547 PRINT 出現限制檢查沖突，請檢查需要更新的數據限制出現限制檢查沖突，請檢查需要更新的數據限制(2)ROWCOUNTn該變量將返回受上一條該變量將返回受上一條SQL語句影響的記錄語句影響的記錄數，返回的數據類型為數，返回的數據類型為integer。n任何不返回記錄的語句都將這一變量設置任何不返回記錄的語句都將這一變量設置為為0。n例：在例：在UPDATE語句中使用語句中使用ROWCOUNT變變量來檢測是否存在發生更改的記錄。量來檢測是否存

5、在發生更改的記錄。nUPDATE StudInfo Set StudSex=ll Where StudNo=99070470 nIF ROWCOUNT=0nPrint 警告：沒有發生記錄更新警告：沒有發生記錄更新6.1.2 局部變量局部變量n局部變量是指在局部變量是指在Transact-SQL批處理和批處理和腳本中用來保存數據值的對象。腳本中用來保存數據值的對象。n通常用于下面三種情況：通常用于下面三種情況：l作為計數器，計算循環執行的次數或控制循環執行的次數；l保存數據值以供控制流語句測試；l保存由存儲過程返回代碼返回的數據值。（1）聲明局部變量）聲明局部變量n格式：格式：DECLARE 變

6、量名稱變量名稱 數據類型數據類型n這個語句將會對變量執行下列操作：這個語句將會對變量執行下列操作：l為變量指派名稱，名稱的第一個字符必須為;l為變量指派數據類型和長度，如果是數字局部變量，那么還需要指定精度和小數位數；l將變量值設置為NULLn如：聲明一個數據類型為如：聲明一個數據類型為int的的temp_counter局部變局部變量量DECLARE temp_counter INTn注：使用一個注：使用一個DECLARE語句聲明多個局部變量時，在語句聲明多個局部變量時，在每個局部變量定義之間使用逗號分隔。每個局部變量定義之間使用逗號分隔。 Declare StudNo Varchar(15)

7、,StudScore Numeric(5,1)（2）為局部變量賦值）為局部變量賦值n通常使用通常使用SET語句為局部變量賦值，格式為：語句為局部變量賦值，格式為：SET 變量名稱變量名稱=變量值變量值n如：如：SET temp_counter=0n也可以使用也可以使用SELECT語句為局部變量賦值語句為局部變量賦值Select temp_counter=0n如：定義一個變量如：定義一個變量MaxScoren-聲明局部變量聲明局部變量nDECLARE MaxScore numeric(5,1)n-將其賦值為學號為將其賦值為學號為99070470的最高分的最高分nSELECT MaxScore=M

8、AX(StudScore) StudScoreInfo Where StudNo=20071002局部變量和全局變量示例局部變量和全局變量示例nDeclare StudName varchar(20)nDeclare Salary int,Today datetimenSet StudName=李明李明nSet Salary=50nSelect Today=Getdate()nSelect StudName,SalarynPrint TodaynSelect Rowcount,Error6.2 程序流程控制程序流程控制nTransact-SQL語言不但允許用戶使用變語言不但允許用戶使用變量和函

9、數，同時還提供了控制流語言的量和函數，同時還提供了控制流語言的特殊關鍵字來控制特殊關鍵字來控制Transact-SQL語句、語句、語句塊和存儲過程的執行過程。語句塊和存儲過程的執行過程。6.2.1 BEGING.END語句塊語句塊n語法語法BEGIN sql_statement| statement_block ENDn參數參數 sql_statement | statement_block是任何有效的是任何有效的 Transact-SQL 語句或以語句塊定義的語句分語句或以語句塊定義的語句分組。組。 n通常用于下列情況：通常用于下列情況：lWHILE循環需要包含多條語句lCASE函數的元素需

10、要包含多條語句lIF或ELSE子句中需要包含多條語句6.2.2 IFELSE語句語句n語法語法IF Boolean_expression sql_statement | statement_block ELSE sql_statement | statement_block n參數參數lBoolean_expression1、是返回 TRUE 或 FALSE 的表達式2、如果布爾表達式中含有 SELECT 語句，必須用圓括號將 SELECT 語句括起來。lsql_statement | statement_blockTransact-SQL 語句或用語句塊定義的語句分組。IF語句示例語句示例D

11、eclare i IntegerSet i=3IF I5 PRINT I的值大于為的值大于為5-Declare i IntegerSet i=3IF I5 PRINT I的值大于的值大于5Else PRINT I的值不大于的值不大于5IF語句示例語句示例IF Exists(Select * From StudScoreInfo Where StudScore=100) Print 有考有考100分的學生分的學生Else Print 沒有考沒有考100分的學生分的學生IF Exists(Select * From StudScoreInfo Where StudScore=100) begin

12、Print 有考有考100分的學生分的學生 Select * From StudScoreInfo Where StudScore=100 endElse Print 沒有考沒有考100分的學生分的學生IF多條件分支示例多條件分支示例Declare AvgScore numeric(5,1)Declare ScoreLevel Varchar(10)Select AvgScore=Avg(StudScore) From StudScoreInfo Where StudNo=99070470if AvgScore=90 Set ScoreLevel=優秀優秀else if AvgScore=80

13、 Set ScoreLevel=良好良好else if AvgScore=70 Set ScoreLevel=中等中等else if AvgScore=60 Set ScoreLevel=及格及格else Set ScoreLevel=不及格不及格print ScoreLevelCASE多條件分支多條件分支簡單簡單 CASE 函數：函數：nCASE input_expression WHEN when_expressionTHEN result_expression .n ELSE else_result_expression ENDCASE 搜索函數：搜索函數：nCASE WHEN Bool

14、ean_expression THEN result_expression .n ELSEelse_result_expression ENDCASE示例示例Declare a int,Answer Char(10)set a=cast(rand()*10 as int)print aSet Answer=Case aWhen 1 Then AWhen 2 Then BWhen 3 Then CWhen 4 Then DWhen 5 Then EELSE OthersENDPRINT The answer is +AnswerCASE示例示例Declare a int,Answer Char(

15、10)set a=cast(rand()*10 as int)print aSet Answer=CaseWhen a=1 Then AWhen a3 Then CWhen a=90 Then 優秀優秀When AvgScore=80 Then 良好良好When AvgScore=70 Then 中等中等When AvgScore=60 Then 及格及格else 不及格不及格endprint ScoreLevel6.2.3 WHILE語句語句n語法語法 WHILE Boolean_expression sql_statement | statement_block BREAK sql_sta

16、tement | statement_block CONTINUE n說明說明lBREAK導致從最內層的 WHILE 循環中退出。將執行出現在 END 關鍵字后面的任何語句，END 關鍵字為循環結束標記。 lCONTINUE使 WHILE 循環重新開始執行，忽略 CONTINUE 關鍵字后的任何語句。WHILE示例一示例一Declare i intSet i=10while i0beginset i=i-1If i=8 continueif i=2 breakif i%2=0 print iendWhile示例二示例二Declare i int,Result intSet i=2SELECT

17、Result=5While i0Begin Set Result=Result+Power(i ,2) Set i=i-1EndPRINT Result While示例三示例三Declare i int,Result intSet i=10Set Result=4while i=0beginset i=i-2if i=2 breakif i%3=0 Set Result=Result+iendPrint Result While示例四示例四Declare I int,Result IntSet I=3Set Result=10While IResultBeginSet I=I+Result%3

18、Set Result=Result-I%4EndSet I=I+Resultprint I6.2.4 RETURN語句語句n功能功能l從查詢或過程中無條件退出。lRETURN 即時且完全，可在任何時候用于從過程、批處理或語句塊中退出。l不執行位于 RETURN 之后的語句。n語法語法RETURN integer_expression n參數參數linteger_expressionl是返回的整型值。存儲過程可以給調用過程或應用程序返回整型值。l返回類型l可以選擇是否返回 int。RETURN ExampleCreate Procedure CheckScore AsIf exists(Sele

19、ct * From StudScoreInfo Where StudScore=100)return (Select Count(*) From StudScoreInfo Where StudScore=100)else return 0GoDeclare Count intExec Count=CheckScoreprint Count6.2.5 WAITFOR語句語句n功能：指定觸發語句塊、存儲過程或事務執行的時功能：指定觸發語句塊、存儲過程或事務執行的時間、間、 時間間隔或事件。時間間隔或事件。n語法語法WAITFOR DELAY time | TIME time n參數參數lDELA

20、Y指示 SQL Server 一直等到指定的時間過去，最長可達 24 小時。ltime要等待的時間。可以按 datetime 數據可接受的格式指定 time，也可以用局部變量指定此參數。不能指定日期。因此，在 datetime 值中不允許有日期部分。lTIME：指示 SQL Server 等待到指定時間。 WAITFOR ExampleDeclare Count intSet Count=0While CountAvgScore查詢大于課程平均分的學生成績信息查詢大于課程平均分的學生成績信息nSelect S.* From scoreInfo S,courseInfo C Where S.courseID=C.courseID And CourseName=嵌入式系統 And score(Select Avg(score) From scoreInfo S,courseInfo C Where S.courseID=C.courseID And courseName=嵌入式系統)插入記錄插入記錄nDeclare StudNo char(10),StudName char(30)nSet StudNo=(Select Max(stuID)+1 From stuInfo)nSe