第8章SQLServer中的程序設計8.1程序中的批處理、腳本和注釋8.2SQLServer變量8.3運算符與表達式8.4SQLServer函數(shù)8.5程序流程控制8.6程序中的事務8.7游標8.8實戰(zhàn)訓練小結思考題

程序有很多種，有客戶端的處理程序，也有服務器端的程序。但是，必須在服務器端運行的程序千萬不要用客戶端程序來代替。數(shù)據(jù)庫中的程序就是如此。本章任務：了解SQLServer中程序設計的基本知識，掌握局部變量的使用、常用函數(shù)的應用和流程控制的編程，學會事務的應用和游標的簡單應用。

SQLServer中的編程語言就是Transact-SQL語言，這是一種非過程化的語言。不論是普通的客戶/服務器應用程序，還是Web應用程序，都必須通過向服務器發(fā)送Transact-SQL語言才能實現(xiàn)與SQLServer的通信。用戶可以使用Transact-SQL語言定義過程，用于存儲以后經(jīng)常使用的操作。8.1程序中的批處理、腳本和注釋當要完成的任務不能由單獨的Transact-SQL語句來完成時，SQLServer使用批處理、腳本、存儲過程、觸發(fā)器等來組織多條Transact-SQL語句。本節(jié)主要介紹批處理、腳本等的基本概念。8.1.1批處理批處理就是一個或多個Transact-SQL語句的集合，從應用程序一次性發(fā)送到SQLServer并由SQLServer編譯成一個可執(zhí)行單元，此單元稱為執(zhí)行計劃。執(zhí)行計劃中的語句每次執(zhí)行一條。建立批處理時，使用GO語句作為批處理的結束標記。在一個GO語句行中不能包括其他Transact-SQL語句，但可以使用注釋文字。當編譯器讀取到GO語句時，它會把GO語句前面的所有語句當作一個批處理，并將這些語句打包發(fā)送給服務器。GO語句本身并不是Transact-SQL語句的組成部分，它只是一個用于表示批處理結束的前端指令。如果在一個批處理中包含任何語法錯誤，如引用了一個并不存在的對象，則整個批處理就不能被成功地編譯和執(zhí)行。如果一個批處理中某句有執(zhí)行錯誤，如違反了約束，則它僅影響該語句的執(zhí)行，并不影響批處理中其他語句的執(zhí)行。在SQLServer2000中，可以利用isql實用程序、osql實用程序及isqlw實用程序執(zhí)行批處理。isql實用程序和osql實用程序需要在DOS命令提示符下運行，這里不做介紹。isqlw實用程序在查詢分析器中執(zhí)行。

建立批處理時，應當注意以下幾點：

(1)?CREATEDEFAULT、CREATEPROCEDURE、CREATERULE、CREATETRIGGER及CREATEVIEW語句不能與其他語句放在一個批處理中。

(2)不能在刪除一個對象之后在同一批處理中再次引用這個對象。

(3)不能在一個批處理中引用其他批處理中所定義的變量。

(4)不能把規(guī)則和默認值綁定到表字段或用戶自定義數(shù)據(jù)類型之后，立即在同一個批處理中使用它們。

(5)不能在定義一個CHECK約束之后，立即在同一個批處理中使用該約束。(6)不能在修改表中的一個字段名之后，立即在同一個批處理中引用新字段名。

(7)如果一個批處理中的第一個語句是執(zhí)行某個存儲過程的EXECUTE語句，則EXECUTE關鍵字可以省略；如果該語句不是第一個語句，則必須使用EXECUTE關鍵字，或者縮寫為“EXEC”。

【例8-1】利用查詢分析器執(zhí)行兩個批處理，以顯示學生表中的信息及記錄個數(shù)。代碼如下：

USEcollegeGOPRINT'學生表包含信息如下：'SELECT*FROM學生

PRINT'學生表記錄個數(shù)為：'SELECTCOUNT(*)FROM學生

GO

該例子中包含兩個批處理：前者僅包含一個語句，后者包含四個語句。其中，PRINT語句用于顯示char類型、varchar類型或可自動轉換為字符串類型的數(shù)據(jù)。運行結果如圖8-1所示。圖8-1在查詢分析器中執(zhí)行批處理的運行結果8.1.2腳本腳本是存儲在文件中的一系列SQL語句，即許多按順序提交的批處理。

Transact-SQL腳本中可以包含一個或多個批處理。如果沒有GO語句，則將它作為單個批處理執(zhí)行。使用腳本可以將創(chuàng)建和維護數(shù)據(jù)庫時的操作步驟保存為一個磁盤文件。將Transact-SQL語句保存為腳本文件，不僅可以建立起可再用的模塊化代碼，還可以在不同計算機之間傳送Transact-SQL語句，使兩臺計算機執(zhí)行同樣的操作。腳本可以在查詢分析器中執(zhí)行，也可以在isql或osql實用程序中執(zhí)行。查詢分析器是建立、編輯和使用腳本的一個最好的環(huán)境。在查詢分析器中，不僅可以新建、保存、打開腳本文件，而且可以輸入和修改Transact-SQL語句，還可以通過執(zhí)行Transact-SQL語句來查看腳本的運行結果，從而檢驗腳本內容的正確性。8.1.3注釋注釋是指程序中用來說明程序內容的語句，它不能執(zhí)行且不參與程序的編譯。注釋用于語句代碼的說明，或暫時禁用的部分語句。為程序加上注釋不僅能增強程序的可讀性，而且有助于日后的管理和維護。在程序中使用注釋是一個程序員良好的編程習慣。SQLServer支持兩種形式的注釋語句。1.行注釋如果整行都是注釋而并非所要執(zhí)行的程序行，則該行可用行注釋，語法格式如下：

--注釋語句這種注釋形式用來標志一行注釋，可以與要執(zhí)行的代碼處在同一行，也可以另起一行。從雙連字符(--)開始到行尾均為注釋。

2.塊注釋如果給程序所加的注釋內容較長，則可使用塊注釋，語法格式如下：／*注釋語句*／這種注釋形式用來標志多行注釋，可以與要執(zhí)行的代碼處在同一行，也可以另起一行，甚至可以放在可執(zhí)行代碼內。對于多行注釋，必須使用開始注釋字符對(／*)開始注釋，使用結束注釋字符對(*／)結束注釋，“／*”和“*／”之間的全部內容都是注釋部分。

注意：多行注釋不能跨越批處理，整個注釋必須包含在一個批處理中。塊注釋不能嵌套使用，但塊注釋中可以包含行注釋。

【例8-2】注釋語句的使用方法舉例。／*下面這個例子用來演示注釋語句的使用方法*／

USEcollegeGOSELECT*FROM教師表

--檢索所有教師的情況

GO8.2SQLServer變量變量是SQLServer用來在語句之間傳遞數(shù)據(jù)的方式之一。SQLServer中的變量分為兩種，即全局變量和局部變量。全局變量的名稱以兩個@@字符開始，由系統(tǒng)定義和維護；局部變量的名稱以一個@字符開始，由用戶自己定義和賦值。8.2.1全局變量全局變量是SQLServer系統(tǒng)提供并賦值的變量。用戶不能建立全局變量，也不能修改全局變量。在SQLServer2000中，全局變量是一組特殊的函數(shù)，它們的名稱以@@開始，而且不需要任何參數(shù)，在調用時無需在函數(shù)名后面加上一對圓括號，這些函數(shù)也稱為無參函數(shù)。SQLServer提供的全局變量共有33個，如表8-1所示。下面介紹全局變量的用途。表8-1SQLServer中的全局變量續(xù)表續(xù)表續(xù)表續(xù)表

【例8-3】利用全局變量查看SQLServer的版本和當前所使用的SQLServer服務器名稱。在查詢分析器中的運行結果如圖8-2所示。代碼如下：

PRINT'當前所用SQLServer版本信息如下：'PRINT@@VERSION--顯示版本信息

PRINT''--換行

PRINT'目前所用SQLServer服務器名稱為：'+@@SERVERNAME--

顯示服務器名稱圖8-2全局變量的使用8.2.2局部變量局部變量是指在批處理或腳本中用來保存數(shù)據(jù)值的對象。局部變量常用作計數(shù)器來計算循環(huán)執(zhí)行的次數(shù)或控制循環(huán)執(zhí)行的次數(shù)，也可以用于保存由存儲過程代碼返回的數(shù)據(jù)值。此外，還可以使用TABLE數(shù)據(jù)類型的局部變量來代替臨時表。

1.聲明局部變量使用一個局部變量之前，必須使用DECLARE語句來聲明這個局部變量，給它指定一個變量名和數(shù)據(jù)類型。對于數(shù)值變量，還需要指定其精度和小數(shù)位數(shù)。DECLARE語句的語法格式如下：

DECLARE@局部變量數(shù)據(jù)類型[,...n]

局部變量名總是以@符號開始，變量名最多可以包含128個字符，局部變量名必須符合標識符命名規(guī)則。局部變量的數(shù)據(jù)類型可以是系統(tǒng)數(shù)據(jù)類型，也可以是用戶自定義數(shù)據(jù)類型，但不能把局部變量指定為text、ntext或image數(shù)據(jù)類型。在一個DECLARE語句中可以定義多個局部變量，只需用逗號(，)分隔即可。

【例8-4】聲明SNO、SNAME、SBIRTH、SCORE等局部變量。代碼如下：

DECLARE@SNOCHAR(6)DECLARE@SNAMECHAR(10)DECLARE@SBIRTHDATETIMEDECLARE@SCROEDECIMAL(5,1)

某些數(shù)據(jù)類型需要指定長度，如char類型；某些數(shù)據(jù)類型不需要指定長度，如datetime類型；而某些數(shù)據(jù)類型還需要指定精度和小數(shù)位數(shù)，如decimal類型。

2.給局部變量賦值使用DECLARE語句聲明一個局部變量之后，該變量的值將被初始化為NULL。如果需要賦其他值，則可以使用SET語句，語法格式如下：

SET@局部變量=表達式

SET語句的功能是將表達式的值賦給局部變量。其中，表達式是SQLServer的任何有效的表達式。

使用SELECT語句也可對局部變量賦值，即通過在SELECT子句的選擇列表中引用一個局部變量來使它獲得一個值。語法格式如下：

SELECT@局部變量=表達式[，…n]

如果使用一個SELECT語句對一個局部變量賦值時，這個語句返回了多個值，則這個局部變量將取得該SELECT語句所返回的最后一個值。此外，SELECT直接與局部變量聯(lián)用，可以顯示局部變量的值。

【例8-5】聲明四個局部變量SNO、SNAME、SBIRTH、SCORE，并對它們賦值，然后將變量的值顯示出來。在查詢分析器里其運行結果如圖8-3所示。圖8-3局部變量的使用

代碼如下：

DECLARE@SNOCHAR(6),@SNAMECHAR(10),@SBIRTHDATETIME,@SCOREDECIMAL(5,1)/*聲明四個局部變量*/SET@SNO='001'SET@SNAME='JOHN'SET@SBIRTH=GETDATE()SET@SCORE=98.5PRINT@SNOPRINT@SNAMEPRINT@SBIRTHPRINT@SCORE3.局部變量的作用域局部變量的作用域指可以引用該變量的范圍。局部變量的作用域從聲明它們的地方開始到聲明它們的批處理或存儲過程結尾。也就是說，局部變量只能在聲明它的批處理、存儲過程或觸發(fā)器中使用，一旦這些批處理或存儲過程結束，局部變量將自動消除。

【例8-6】聲明一個局部變量，把學生表中學號為“05209001”的學生姓名賦給局部變量并輸出。在查詢分析器中其運行結果如圖8-4所示。代碼如下：

USEcollegeGODECLARE@NAMECHAR(12)--聲明局部變量SELECT@NAME=姓名FROM學生表WHERE學號='05209001' --將結果存儲PRINT'學生表中學號為"05209001"的學生姓名為：'+@NAME /*--輸出字符串*/GO--該批處理結束，局部變量@NAME自動清除PRINT'如果繼續(xù)引用該變量，將會出現(xiàn)聲明局部變量的錯誤提示'PRINT'學生表中學號為05209001的學生姓名為：'+@NAME--輸出字符串GO圖8-4局部變量作用域的應用8.3運算符與表達式

8.3.1運算符

1.算術運算符算術運算符有：+(加)、-(減)、*(乘)、/(除)、%(模)

算術運算符在兩個表達式上執(zhí)行數(shù)學運算，這兩個表達式可以是數(shù)字數(shù)據(jù)類型中的任何數(shù)據(jù)類型。

2.賦值運算符賦值運算符有：=(等號)。

3.按位運算符按位運算符有：&(按位與)、|(按位或)、^(按位異或)。

位運算符可以在兩個表達式之間執(zhí)行位操作，這兩個表達式可以為整型數(shù)據(jù)類型中的任何數(shù)據(jù)類型。位運算符的操作數(shù)可以是整型或二進制字符串數(shù)據(jù)類型中的任何數(shù)據(jù)類型(但image數(shù)據(jù)類型除外)，此外，兩個操作數(shù)不能同時是二進制字符串數(shù)據(jù)類型中的某種數(shù)據(jù)類型。

4.比較運算符比較運算符有：=(等于)、>(大于)、<(小于)、>=(大于或等于)、<=(小于或等于)、<>(不等于)、!=(不等于)、!<(不小于)、!>(不大于)。比較運算符可以測試兩個表達式的大小關系。除了text、ntext或image數(shù)據(jù)類型的表達式外，比較運算符可以用于所有的表達式。5.邏輯運算符邏輯運算符有：ALL、AND、ANY、BETWEEN、EXISTS、IN、LIKE、NOT、OR、SOME。邏輯運算符對某個條件進行測試，以獲得其真實情況。邏輯運算符和比較運算符一樣，返回帶有TRUE或FALSE值的布爾數(shù)據(jù)類型。8.3.2表達式表達式是符號與運算符的組合，SQLServer對其求值可獲得單個數(shù)據(jù)值。簡單的表達式可以是一個常量、變量、列或標量函數(shù)?？梢杂眠\算符將兩個或更多的簡單表達式聯(lián)接起來組成復雜的表達式。(1)對于由單個常量、變量、標量函數(shù)或列名組成的簡單表達式，該表達式的數(shù)據(jù)類型、排序規(guī)則、精度、小數(shù)位數(shù)和值就是它所引用的元素的數(shù)據(jù)類型、排序規(guī)則、精度、小數(shù)位數(shù)和值。

(2)如果兩個表達式是用比較或者邏輯運算符組合的，則結果的數(shù)據(jù)類型是布爾型，值有三種：TRUE、FALSE或UNKNOWN。

(3)如果兩個表達式是用算術運算符、位運算符或字符串運算符組合的，則結果的數(shù)據(jù)類型由運算符確定。

(4)復雜的表達式由很多符號與運算符構成，得出一個單值結果。通過對組件表達式進行組合可確定結果表達式的數(shù)據(jù)類型、排序規(guī)則、精度和值，每次組合兩個表達式，直到得到最后結果。表達式中元素組合的順序由表達式中運算符的優(yōu)先級決定。8.4SQLServer函數(shù)

SQLServer函數(shù)與其他程序設計語言中的函數(shù)相似，其形式包含函數(shù)名稱，名稱后邊跟一對小括號，大部分函數(shù)的小括號里有參數(shù)，其目的是為用戶操作提供方便。一般情況下，在允許使用變量、字段和表達式的地方都可以使用函數(shù)。在使用函數(shù)時，只要提供正確的參數(shù)，就可以得到我們需要的結果。函數(shù)可以由系統(tǒng)提供，也可以由用戶創(chuàng)建。系統(tǒng)提供的函數(shù)稱為內置函數(shù)，它為用戶方便、快捷地執(zhí)行某些操作提供幫助；用戶創(chuàng)建的函數(shù)稱為用戶自定義函數(shù)，它是用戶根據(jù)自己的特殊需求而創(chuàng)建的，用來補充和擴展內置函數(shù)。SQLServer2000系統(tǒng)提供了上百個內置函數(shù)，用來幫助用戶獲得系統(tǒng)信息，進行數(shù)學計算、字符處理和轉換數(shù)據(jù)類型等。通常將這些函數(shù)分為三類：標量函數(shù)、聚合函數(shù)和行集函數(shù)。下面我們將介紹常用的標量函數(shù)和聚合函數(shù)。8.4.1標量函數(shù)標量函數(shù)對單一值進行操作，返回單一值。只要在能夠使用表達式的地方，就可以使用標量函數(shù)。標量函數(shù)有很多，下面介紹一些常用的標量函數(shù)，如數(shù)學函數(shù)、日期和時間函數(shù)、字符串函數(shù)和數(shù)據(jù)類型轉換函數(shù)，其他標量函數(shù)的使用請參考SQLServer幫助文件。1.數(shù)學函數(shù)數(shù)學函數(shù)對數(shù)字表達式進行數(shù)學運算并返回運算結果。組成數(shù)字表達式的數(shù)據(jù)類型有decimal、integer、float、real、money、smallmoney、smallint和tinyint。SQLServer提供了20多個數(shù)學函數(shù)，下面對其常用函數(shù)進行簡單介紹。

(1)?ABS(數(shù)值型表達式)函數(shù)。此函數(shù)用于返回給定數(shù)值表達式的絕對值。例如，在查詢分析器中輸入如下代碼：

SELECTABS(-2),ABS(0),ABS(3)

執(zhí)行代碼得到如下結果：

---------------------------------------203(所影響的行數(shù)為1行)

結果表明，ABS函數(shù)將負值變?yōu)檎?，正值和零保持不變?2)?CEILING(數(shù)值型表達式)函數(shù)。此函數(shù)用于返回大于或等于所給數(shù)字表達式的最小整數(shù)。

(3)?FLOOR(數(shù)值型表達式)函數(shù)。此函數(shù)用于返回小于或等于所給數(shù)字表達式的最大整數(shù)。例如，在查詢分析器中輸入如下代碼：

SELECTFLOOR(123.45),CEILING(123.45),FLOOR(-123.45),CEILING(-123.45)

執(zhí)行代碼得到如下結果：

-------------------------------123124-124-123(所影響的行數(shù)為1行)(4)?EXP(float表達式)函數(shù)。此函數(shù)用于返回給定float表達式的以e為底的指數(shù)值。(5)?LOG(float表達式)函數(shù)。此函數(shù)返回給定float表達式的自然對數(shù)。

(6)?LOG10(float表達式)函數(shù)。此函數(shù)用于返回給定float表達式的以10為底的對數(shù)。

(7)?SQRT(float表達式)函數(shù)。此函數(shù)用于返回給定表達式的平方根。例如，在查詢分析器中輸入如下代碼：

SELECTEXP(1),LOG(2.7183),LOGl0(10),SQRT(4)

執(zhí)行代碼得到如下結果：

-----------------------------------------------------------------------------2.71828182845904511.00000668491398771.02.0(所影響的行數(shù)為1行)(8)?SQUARE(float表達式)函數(shù)。此函數(shù)用于返回給定表達式的平方值。例如，SQUARE(3)的結果為9.0。

(9)?POWER(數(shù)值型表達式1,數(shù)值型表達式2)函數(shù)。此函數(shù)用于返回數(shù)值型表達式1的數(shù)值型表達式2次方的值。例如，POWER(2,3)表示2的3次方。乘方運算函數(shù)返回值的數(shù)據(jù)類型與第一個參數(shù)的數(shù)據(jù)類型和精度相同。例如，在查詢分析器中輸入如下代碼：

SELECTPOWER(2,-3),POWER(2.0,-3),POWER(2.000,-3)

執(zhí)行代碼得到如下結果：

------------------0.1.125(所影響的行數(shù)為1行)(10)?SIGN(數(shù)值型表達式)函數(shù)。SIGN函數(shù)用于返回給定表達式的正(+1)、零(0)或負(-1)。例如，在查詢分析器中輸入如下代碼：

SELECTSIGN(34),SIGN(0),SIGN(-123)

執(zhí)行代碼得到如下結果：

------------10-1(所影響的行數(shù)為1行)(11)?RAND(整型表達式)函數(shù)。RAND函數(shù)用于返回0～1之間的隨機float值。整型表達式在這里是給出的種子值或起始值。例如，下列代碼將通過RAND函數(shù)產生4個不同的隨機數(shù)。DECLARE@countersmallintSET@counter=1WHILE@counter<5BEGINSELECTRAND(@counter)Random_NumberSETNOCOUNT0NSET@counter=@counter+1SETNOCOUNTOFFENDGO(12)?ROUND(數(shù)值表達式,長度)函數(shù)。ROUND函數(shù)用于返回數(shù)字表達式并四舍五入為指定的長度或精度的值。當長度為正數(shù)時，數(shù)值表達式四舍五入為長度所指定的小數(shù)位數(shù)。當長度為負數(shù)時，數(shù)值表達式則按長度的指定在小數(shù)點的左邊四舍五入。例如，在查詢分析器中輸入如下代碼：

SELECTROUND(789.34,1),ROUND(789.34,0),ROUND(789.34,-1),ROUND(789.34,-2)

執(zhí)行代碼得到如下結果：

---------------------------------------789.30789.00790.00800.00(所影響的行數(shù)為1行)(13)三角函數(shù)。●?SIN(float表達式)正弦函數(shù)：返回輸入表達式的三角正弦值。●?COS(float表達式)余弦函數(shù)：返回輸入表達式的三角余弦值?！?TAN(float表達式)正切函數(shù)：返回輸入表達式的三角正切值?！?COT(float表達式)余切函數(shù)：返回輸入表達式的三角余切值?！?ACOS(float表達式)反余弦函數(shù)：返回以弧度表示的角度值?！?ASIN(float表達式)反正弦函數(shù)：返回以弧度表示的角度值?！?ATAN(float表達式)反正切函數(shù)：返回以弧度表示的角度值。

(14)?DEGREES(數(shù)值表達式)函數(shù)。此函數(shù)將弧度轉換為角度。

(15)?RADIANS(數(shù)值表達式)函數(shù)。此函數(shù)將角度轉換為弧度。

2.日期和時間函數(shù)日期和時間函數(shù)用于處理datetime和smalldatetime類型的數(shù)據(jù)。常用的日期和時間函數(shù)有如下幾種：●?GETDATE()：返回當前系統(tǒng)日期和時間。●?DATEADD(datepart,number,date)：在date值上加上datepart和number參數(shù)指定的時間間隔，返回新的datetime值。●?DATEDIFF(datepart,startdate,enddate)：返回跨兩個指定日期的日期和時間間隔數(shù)。●?DATENAME(datepart,date)：返回代表指定日期的指定日期部分的字符串。●?DATEPART(datepart,date)：返回代表指定日期的指定日期部分的整數(shù)。●?YEAR(date)：返回表示指定日期中年份的整數(shù)。●?MONTH(date)：返回代表指定日期月份的整數(shù)?！?DAY(date)：返回代表指定日期的天的日期部分中日的整數(shù)。下面舉例說明日期和時間函數(shù)的使用?！纠?-7】查看今天的年月日，并以格式化的形式顯示。在查詢分析器中輸入以下代碼：

SELECT'今天是'+DATENAME(YY,GETDATE())+'年'+DATENAME(MM,GETDATE())+‘月'+DATENAME(DD,GETDATE())+'日'執(zhí)行該代碼，得到如下結果：今天是2007年03月20日

(所影響的行數(shù)為1行)【例8-8】用日期函數(shù)計算學生表中學生的年齡。其程序代碼如下：

USEcollegeGOSELECT姓名,DATEDIFF(YY,出生日期,GETDATE())AS年齡FROM

學生

GO

執(zhí)行該代碼，返回如下結果：姓名年齡

------------

林東21

王斌19(所影響的行數(shù)為2行)【例8-9】建表時使用當前系統(tǒng)日期和時間作為默認值，在添加記錄時不用指定時間，該字段會將這時的系統(tǒng)日期時間(服務器時間)作為內容記錄到相關字段。其程序代碼如下：

USESalesCreatetable銷售表(

銷售記錄號intidentity(1,1)primarykey,

銷售金額smallmoney,

交易時間smalldatetimedefaultGETDATE())

這樣，在記錄銷售時只要用下列語句即可。

insert銷售表(銷售金額)values(35.80)insert銷售表(銷售金額)values(185.20)

用select*from銷售表語句顯示結果：銷售記錄號銷售金額交易時間

-----------------------------------------------135.802007-5-812：27：002185.202007-5-812：28：00

3.字符串函數(shù)字符串函數(shù)方便用戶對字符數(shù)據(jù)進行處理，它可以實現(xiàn)字符串的查找、轉換等操作。常用的字符串函數(shù)有以下幾種：●?ASCII(字符型表達式)：返回字符表達式最左端字符的ASCII代碼值。●?CHAR(整型表達式)：將整型的ASCII代碼轉換為字符?！?CHANDEX(字符型表達式1,字符型表達式2[,開始位置])：返回字符串中指定表達式的起始位置?！?LOWER(字符型表達式)：將大寫字符數(shù)據(jù)轉換為小寫字符?！?UPPER(字符型表達式)：將小寫字符數(shù)據(jù)轉換為大寫字符。●?STR(float型表達式[,長度[,小數(shù)點后長度]])：將數(shù)字數(shù)據(jù)轉換為字符數(shù)據(jù)?！?LEFT(字符型表達式,整型表達式)：返回字符串中從左邊開始指定個數(shù)的字符。●?RIGHT(字符型表達式,整型表達式)：返回字符串中從右邊開始指定個數(shù)的字符。●?LTRIM(字符型表達式)：刪除起始空格后返回字符表達式?！?RTRIM(字符型表達式)：截斷所有尾隨空格后返回一個字符串?！?SUBSTRING(expression,start,length)：從expression的第start個字符處返回length個字符?！?LEN(字符串表達式)：返回給定字符串表達式的字符(而不是字節(jié))個數(shù)，其中不包含尾隨空格。下面舉例來了解字符串函數(shù)的用法。

【例8-10】將學生表中的某個學生的出生日期的月份轉化為字符串，并測試其長度。其代碼如下：

SELECTLEN(STR(MONTH(出生日期)))FROM學生表WHERE

學號='05209001'

執(zhí)行結果為10。【例8-11】使用字符串函數(shù)查找姓林的同學，并格式化顯示其出生年月。其代碼如下：

USEcollegeGO SELECT姓名,STR(YEAR(生日期))+‘年’+LTRIM(STR(MONTH(生日期)))+'月'AS出生年月

FROM學生表

WHERELEFT(姓名,1)='林'GO

執(zhí)行該代碼，返回如下結果：姓名出生年月林東1986年3月

(所影響的行數(shù)為1行)

在例8-11中，由于兩個日期函數(shù)返回的結果均為數(shù)值，因此要實現(xiàn)顯示結果，必須將其轉換為字符串，而從例8-10中知道，出生日期取出月份值轉化為字符串后的長度為10，所以需要加上去空格函數(shù)。

4.數(shù)據(jù)類型轉換函數(shù)對不同數(shù)據(jù)類型的數(shù)據(jù)進行運算時，需要將其轉換為相同的數(shù)據(jù)類型。SQLServer中有一些數(shù)據(jù)類型之間會自動進行轉換，如整數(shù)除以實數(shù)時，都將轉換為實數(shù)，而有一些數(shù)據(jù)類型必須進行強制轉換。系統(tǒng)提供了CAST和CONVERT函數(shù)來實現(xiàn)數(shù)據(jù)類型的轉換，這兩個轉換函數(shù)都可用于選擇列表、WHERE子句和允許使用表達式的任何地方?！?CAST(expressionASdata_type)：將某種數(shù)據(jù)類型的表達式顯式轉換為另一種數(shù)據(jù)類型。●?CONVERT(data_type[(length)],expression[,style])：將表達式的值從一種數(shù)據(jù)類型轉換為另一種數(shù)據(jù)類型。

【例8-12】使用CAST函數(shù)將數(shù)值轉換為字符。在查詢分析器中輸入以下代碼：

USEcollegeGOSELECT學號+'同學平均成績?yōu)?+CAST(AVG(成績)ASVARCHAR(3))+'分'FROM成績表

GROUPBY學號

GO

執(zhí)行代碼后返回以下結果：

05209001同學平均成績?yōu)?2分

05209032同學平均成績?yōu)?0分

(所影響的行數(shù)為2行)8.4.2聚合函數(shù)聚合函數(shù)對一組值進行計算后，向調用者返回單一的值。一般情況下，它經(jīng)常與SELECT語句的GROUPBY子句一同使用。SQLServer提供了十幾個聚合函數(shù)，下面我們將介紹幾個最常用的聚合函數(shù)。

(1)?COUNT(*)：用于計算所有行數(shù)。

(2)?MIN(數(shù)值表達式)：用于計算表達式的最小值。

(3)?MAX(數(shù)值表達式)：用于計算表達式的最大值。

(4)?SUM(數(shù)值表達式)：用于計算表達式的和。

(5)?AVG(數(shù)值表達式)：用于計算表達式的平均值。【例8-13】使用聚合函數(shù)統(tǒng)計college數(shù)據(jù)庫中學生的成績情況。其程序清單如下：

USEcollegeGOSELECTCOUNT(*)AS課程門數(shù),SUM(考試成績)AS總分,AVG(考試成績)AS平均分

FROM成績表

GROUPBY學號

GO

執(zhí)行以上程序，在查詢分析器中將得到如圖8-5所示的結果。圖8-5使用聚合函數(shù)返回數(shù)據(jù)8.4.3用戶自定義函數(shù)可使用CREATEFUNCTION語句、ALTERFUNCTION語句、DROPFUNCTION語句分別創(chuàng)建、修改和除去用戶定義函數(shù)。每個完全合法的用戶定義函數(shù)名(database_name.owner_name.function_name)必須唯一。

SQLServer2000支持以下三種用戶定義函數(shù)：

(1)標量函數(shù)：返回值為標量值的函數(shù)。

(2)內嵌表值函數(shù)：返回值為可更新表的函數(shù)。若用戶定義函數(shù)包含單個SELECT語句且該語句可更新，則該函數(shù)返回的表也可更新。

(3)多語句表值函數(shù)：返回值為不可更新數(shù)據(jù)表的函數(shù)。若用戶定義函數(shù)包含多個SELECT語句,則該函數(shù)返回的表不可更新。1.標量函數(shù)的使用用戶定義函數(shù)有零個或多個輸入?yún)?shù)并返回標量值或表。函數(shù)最多可以有1024個輸入?yún)?shù)。當函數(shù)的參數(shù)有默認值時，調用該函數(shù)時必須指定默認的DEFAULT關鍵字才能獲取默認值。該行為不同于在存儲過程中含有默認值的參數(shù)，在這些存儲過程中，省略該函數(shù)意味著省略默認值。用戶定義函數(shù)不支持輸出參數(shù)。標量函數(shù)返回在RETURNS子句中定義的類型的單個數(shù)據(jù)值。1)標量函數(shù)的定義標量函數(shù)的一般定義形式如下：

CREATEFUNCTION[所有者名.]函數(shù)名

(參數(shù)1[As]類型1[=默認值])[,...參數(shù)n[AS]類型n[=默認值]]RETURNS返回值類型

[WITHENCRYPTIONISCHEMABINDING[[,]...n]][AS]BEGIN

函數(shù)體

RETURN標量表達式

END【例8-14】計算全體學生某門功課的平均成績。

USEcollegeGOCREATEFUNCTIONaverage(@cnumchar(20))RETURNSintASBEGINDECLARE@averintSELECT@aver=(SELECTavg(考試成績)FROM成績表

WHERE課程號=@cnumGROUPBY課程號

)RETURN@averENDGO2)標量函數(shù)的調用當調用用戶定義的標量函數(shù)時，必須提供至少由兩部分組成的名稱(所有者名．函數(shù)名)。調用形式如下：所有者名.函數(shù)名(實參1，…，實參11)其中，實參為已賦值的局部變量或表達式。

【例8-15】如下程序是對例8-14中定義的函數(shù)的調用。

USEcollege--用戶函數(shù)在此數(shù)據(jù)庫中已定義

DECLARE@course1char(20)DECLARE@aver1int--通過EXEC調用用戶函數(shù)，并將返回值賦給局部變量

EXEC@aver1=dbo.average@cnum='059001'--顯示局部變量的值

SELECT@aver1AS'059001課程的平均成績'GO2.內嵌表值函數(shù)的使用

1)內嵌表值函數(shù)的定義語法格式：

CREATEFUNCTION[owner_name.]function_name/*定義函數(shù)名部分*/([{@parameter_name[AS]calar_parameter_data_type[=default]}[，…n]])/*定義參數(shù)部分*/RETURNSTABLE/*返回值為表類型

[WITH<function_option>[[，]…n]]/*定義函數(shù)的可選項*/[AS]RETURN[()select-stmt[]]/*通過SELECT語句返回內嵌表*/<function_option>::={ENCRYPTION|SCHEMABINDING}RETURNS子句僅包含關鍵字TABLE，表示此函數(shù)返回一個表。內嵌表值函數(shù)的函數(shù)體僅有一個RETURN語句，并通過參數(shù)select-stmt指定的SELECT語句返回內嵌表值。語法格式中的其他參數(shù)項同標量函數(shù)的定義。例如，為了顯示學生的各科成績，可以利用成績表和學生表來創(chuàng)建視圖(為簡單起見用成績表代替)，然后在此基礎上定義如下內嵌表值函數(shù)：

CREATEFUNCTIONs_score(@snumchar(8))RETURNStableASreturn(SELECT*FROM成績表

WHERE學號=@snum)GO2)內嵌表值函數(shù)的調用內嵌表值函數(shù)只能通過SELECT語句調用。內嵌表值函數(shù)調用時，可以僅使用函數(shù)名。在此，以前面定義的s_score()內嵌表值函數(shù)的調用作例子，通過輸入學號調用內嵌函數(shù)來查詢其成績。例如，調用s_score()函數(shù)，查詢學號為“05209001”學生的各科成績。調用代碼為：

select*fromcollege.dbo.s_score('05209001')8.5程序流程控制流程控制語句是用來控制程序執(zhí)行和流程分支的命令，這些命令包括條件控制語句、無條件轉移語句和循環(huán)語句。使用這些命令可以使程序具有結構性和邏輯性，并可實現(xiàn)較復雜的功能。8.5.1BEGlN…END語句在條件和循環(huán)通常等流程控制語句中，要執(zhí)行兩個或兩個以上的Transact-SQL語句，就需要使用BEGIN…END語句，通常把這些Transact-SQL語句組織成一個語句塊，作為一個整體來處理。BEGIN…END語句的語法格式如下：

BEGIN

語句1

語句2ENDBEGIN…END語句通常用于下列情況：

(1)?WHILE循環(huán)需要包含語句塊。

(2)?CASE語句的元素需要包含語句塊。

(3)?IF或ELSE子句需要包含語句塊。注意：BEGIN和END語句必須成對使用，不能單獨使用。BEGIN…END語句塊可以嵌套?！纠?-16】使用BEGIN…END語句顯示班級編號為“052001”的班級編號和班級名稱。運行結果如圖8-6所示。代碼如下：

USEcollegeGOIFEXISTS(SELECT*FROM班級表WHERE班級編號='052001')BEGINPRINTN'滿足條件的班級：'SELECT班級編號，班級名稱FROM班級表WHERE班級編號

='052001'ENDGO圖8-6在查詢分析器中執(zhí)行的結果8.5.2IF…ELSE語句在程序中，經(jīng)常需要根據(jù)特定條件指示SQLServer執(zhí)行不同的操作和運算，也就是進行流程控制。SQLServer利用IF…ELSE命令使程序有不同的條件分支，從而完成各種不同條件環(huán)境下的操作。

IF…ELSE語句的語法格式如下：

IF布爾表達式語句1[ELSE

語句2]

其中，布爾表達式表示一個測試條件，其取值為TRUE或FALSE。如果布爾表達式中包含一個SELECT語句，則必須使用圓括號把這個SELECT語句括起來。語句1和語句2可以是單個的Transact-SQL語句，也可以是用語句BEGIN…END定義的語句塊。該語句的執(zhí)行過程是：如果IF后面的布爾表達式返回TRUE，則執(zhí)行語句1，否則執(zhí)行語句2。若無ELSE，且測試條件成立，則執(zhí)行語句1，否則跳過語句1。

【例8-17】使用IF…ELSE語句實現(xiàn)以下功能：如果存在職稱為副教授或教授的教師，那么輸出這些教師的姓名、學歷、職務和職稱，否則輸出沒有滿足條件的教師。

運行結果如圖8-7所示，代碼如下：

USECOLLEGEGOIFEXISTS(SELECT*FROM教師表WHERE職稱='副教授'OR

職稱='教授')BEGINSELECT姓名,學歷,職稱FROM教師表WHERE職稱='副教授'OR

職稱='教授'PRINT'以上教師具有高級職稱'ENDELSEBEGINPRINT'沒有滿足條件的教師'ENDGO圖8-7在查詢分析器中執(zhí)行的結果8.5.3CASE表達式

CASE表達式是一個特殊的Transact-SQL表達式，它能夠實現(xiàn)多重選擇的情況。CASE表達式不同于一個普通的Transact-SQL語句，它不能單獨執(zhí)行，而只能作為一個可以單獨執(zhí)行的語句的一部分來使用。CASE表達式分為簡單CASE表達式和搜索CASE表達式兩種類型。

1.簡單CASE表達式簡單CASE表達式將一個測試表達式與一組簡單表達式進行比較，如果某個簡單表達式與測試表達式的值相等，則返回相應結果表達式的值。簡單CASE表達式的語法格式如下：

CASE測試表達式

WHEN測試值1THEN結果表達式1[WHEN測試值2THEN結果表達式2[...]][ELSE結果表達式n]END

其中，測試表達式用于條件判斷，測試值用于與測試表達式做比較，測試表達式必須與測試值的數(shù)據(jù)類型相同。簡單CASE表達式必須以CASE開頭并以END結束，它能夠將一個表達式的值和一系列的測試值進行比較，并返回符合條件的結果表達式。簡單CASE表達式的執(zhí)行過程是：用測試表達式的值依次與每一個WHEN子句的測試值作比較，直至找到第一個與測試表達式的值完全相同的測試值時，便將該WHEN子句指定的結果表達式返回。如果沒有任何一個WHEN子句的測試值和測試表達式相同，則SQLServer將檢查是否有ELSE子句存在。如果存在ELSE子句，則將ELSE子句之后的結果表達式返回；如果不存在ELSE子句，則返回一個NULL值。

注意：在一個簡單CASE表達式中，一次只能有一個WHEN子句指定的結果表達式返回。若同時有多個測試值與測試表達式的值相同，則只有第一個與測試表達式的值相同的WHEN子句指定的結果表達式返回。

【例8-18】使用簡單CASE表達式實現(xiàn)以下功能：分別輸出課程號和課程名，并且在課程名后添加備注。運行結果如圖8-8所示。代碼如下：

USEcollegeGOSELECT課程號,課程名稱,備注=CASE課程名稱

WHEN'C語言'THEN'職業(yè)基礎'WHEN'高等數(shù)學'THEN'公共基礎'WHEN'鄧小平理論'THEN'公共基礎'ENDFROM課程表

GO圖8-8在查詢分析器中執(zhí)行的結果2.搜索CASE表達式與簡單CASE表達式相比較，在搜索CASE表達式中，CASE關鍵字后面不跟任何表達式，各個WHEN子句后都是布爾表達式。搜索CASE表達式的語法格式如下：

CASEWHEN布爾表達式1THEN結果表達式1[WHEN布爾表達式2THEN結果表達式2[，…n]][ELSE結果表達式l]END

搜索CASE表達式的執(zhí)行過程是：測試每個WHEN子句后的布爾表達式，如果結果為TRUE，則返回相應的結果表達式，否則檢查是否有ELSE子句存在。如果存在ELSE子句，便將ELSE子句之后的結果表達式返回；如果不存在ELSE子句，便返回一個NULL值。

注意：在一個搜索CASE表達式中，一次只能有一個WHEN子句指定的結果表達式返回。如果有多個布爾表達式為TRUE，則只返回第一個為TRUE的WHEN子句指定的結果表達式。

【例8-19】使用搜索CASE表達式實現(xiàn)以下功能：分別輸出班級代碼、班級名稱，并根據(jù)班級編號判別系部名稱。運行結果如圖8-9所示。

USEcollegeGOSELECT班級編號,系部名稱=CASEWHENsubstring(班級編號,3,1)='2'THEN'計算機系'WHENsubstring(班級編號,3,1)='3'THEN'人文系'ENDFROM班級表

GO圖8-9在查詢分析器中執(zhí)行CASE的結果8.5.4WAITFOR語句

WAITFOR語句可以暫停執(zhí)行程序一段時間之后再繼續(xù)執(zhí)行，也可以暫停執(zhí)行程序到所指定的時間后再繼續(xù)執(zhí)行。WAITFOR語句的語法格式如下：

WAITFORDELAY'時間'|TIME'時間‘其中，DELAY指定一段時間間隔過去之后執(zhí)行一個操作；TIME表示從某個時刻開始執(zhí)行一個操作。時間參數(shù)必須為可接受的DATETIME數(shù)據(jù)格式。在DATETIME數(shù)據(jù)中，不允許有日期部分，即采用HH:MM:SS的格式。

【例8-20】使用WAITFOR實現(xiàn)以下功能：輸出班級編號為“052010”的班級編號、班級名稱、系部代碼之前等待2秒。運行結果見圖8-10所示。代碼如下：

USEcollegeGOWAITFORDELAY'00:00:02'SELECT班級編號,班級名稱FROM班級WHERE班級編號

='052010'GO圖8-10在查詢分析器中執(zhí)行WAITFOR的結果8.5.5WHILE語句在程序中，當需要重復處理某項工作時，就需使用WHILE循環(huán)語句。WHILE語句通過布爾表達式來設置一個循環(huán)條件，如果判斷循環(huán)條件為真，則執(zhí)行一個SQL語句或語句塊，否則退出循環(huán)，繼續(xù)執(zhí)行后面的語句，重復判斷和執(zhí)行形成循環(huán)。WHILE語句的語法格式如下：

WHILE布爾表達式

BEGIN

語句序列1[BREAK]

語句序列2[CONTINUE]

語句序列3END其中，布爾表達式用來設置循環(huán)執(zhí)行的條件。當表達式取值連續(xù)多次為TRUE時，循環(huán)將重復執(zhí)行多次；當取值為FALSE時，循環(huán)將停止執(zhí)行。如果布爾表達式中包含一個SELECT語句，則必須將該SELECT語句包含在一對小括號中。可選命令BREAK提前跳出循環(huán)，并運行循環(huán)之后的語句?？蛇x命令CONTINUE使程序忽略CONTINUE之后的語句，直接跳回到WHILE命令行，重新執(zhí)行下一輪循環(huán)。循環(huán)體中的各語句序列可以是單個的Transact-SQL語句，也可以是用BEGIN和END定義的語句塊。循環(huán)允許嵌套，在嵌套循環(huán)中，內層循環(huán)的BREAK命令將使控制權轉移到外一層循環(huán)并繼續(xù)執(zhí)行。

【例8-21】使用WHILE語句實現(xiàn)以下功能：求2～300之間的所有素數(shù)。運行結果如圖8-11所示。代碼如下：

DECLARE@IINT,@JINTSET@I=2WHILE@I<=300BEGINSET@J=2WHILE@J<=@I-1BEGINIF@I％@J=0BREAKELSESET@J=@J+lENDIF@I=@JPRINTCONVERT(VARCHAR,@I)+'是素數(shù)!'SET@I=@I+1END圖8-11在查詢分析器中查找素數(shù)循環(huán)執(zhí)行的結果8.5.6PRINT語句

SQLServer向客戶程序返回信息的方法除了使用SELECT語句外，還可以使用PRINT語句。它的語法格式如下：PRINT'字符串'|局部變量|全局變量

【例8-22】PRINT語句舉例。程序代碼如下：

USEcollegeGODECLARE@STRCHAR(20)SET@STR='歡迎使用PRINT語句'PRINT@STRGO

在查詢分析器中執(zhí)行的結果如圖8-12所示。圖8-12PRINT語句舉例