數(shù)據(jù)庫(kù)SQL ServeSQLite教程 課件 第7章 Transact-SQL應(yīng)用

數(shù)據(jù)庫(kù)SQLServer/SQlite教程授課教師：第7章Transact-SQL應(yīng)用Transact-SQL簡(jiǎn)稱(chēng)T-SQL，是標(biāo)準(zhǔn)SQL語(yǔ)言的增強(qiáng)版。它對(duì)SQL-92標(biāo)準(zhǔn)進(jìn)行了幾種擴(kuò)展以增強(qiáng)其性能，為處理大量數(shù)據(jù)提供必要的結(jié)構(gòu)化處理能力，并作為應(yīng)用程序與SQL

Server溝通的主要語(yǔ)言。T-SQL提供標(biāo)準(zhǔn)SQL的數(shù)據(jù)定義、操作和控制的功能，加上延伸的函數(shù)、系統(tǒng)預(yù)存程序以及程序設(shè)計(jì)結(jié)構(gòu)，讓程序設(shè)計(jì)更有彈性。第7章

Transact-SQL應(yīng)用7.1T-SQL運(yùn)算符與表達(dá)式目錄7.2T-SQL的變量7.3程序控制語(yǔ)句7.4自定義函數(shù)7.1T-SQL運(yùn)算符與表達(dá)式運(yùn)算符是一種符號(hào)，用來(lái)指定在一個(gè)或多個(gè)表達(dá)式中執(zhí)行的操作。SQLServer2008R2的運(yùn)算符有算術(shù)運(yùn)算符、位運(yùn)算符、比較運(yùn)算符、邏輯運(yùn)算符、字符串連接運(yùn)算符、賦值運(yùn)算符等。7.1T-SQL運(yùn)算符與表達(dá)式1.算術(shù)運(yùn)算符2.位運(yùn)算符3.比較運(yùn)算符4.邏輯運(yùn)算符5.字符串連接運(yùn)算符6.賦值運(yùn)算符算術(shù)運(yùn)算符在兩個(gè)表達(dá)式間執(zhí)行數(shù)學(xué)運(yùn)算，這兩個(gè)表達(dá)式可以是任何數(shù)字?jǐn)?shù)據(jù)類(lèi)型。算術(shù)運(yùn)算符有+(加)、-(減)、*(乘)、/(除)和%(求模)5種運(yùn)算。+(加)和-(減)運(yùn)算符也可用于對(duì)datetime及smalldatetime值進(jìn)行算術(shù)運(yùn)算。7.1T-SQL運(yùn)算符與表達(dá)式1.算術(shù)運(yùn)算符2.位運(yùn)算符位運(yùn)算符用于對(duì)兩個(gè)表達(dá)式進(jìn)行的位操作，這兩個(gè)表達(dá)式可為整型或與整型兼容的數(shù)據(jù)類(lèi)型。位運(yùn)算符及其規(guī)則如表7-1所示。SQL中的位運(yùn)算不但可以取出各種值,而且還可以對(duì)數(shù)據(jù)進(jìn)行排序。7.1T-SQL運(yùn)算符與表達(dá)式運(yùn)算符運(yùn)算符名稱(chēng)運(yùn)算規(guī)則&按位與兩個(gè)位均為1時(shí)，結(jié)果為1，否則為0丨按位或只要一個(gè)位為1，結(jié)果為1，否則為0^按位異或兩個(gè)位值不同時(shí)，結(jié)果為1，否則為0表7-1位運(yùn)算符及規(guī)則2.位運(yùn)算符以&為例，&是二進(jìn)制“與”運(yùn)算，參加運(yùn)算的兩個(gè)數(shù)的二進(jìn)制按位進(jìn)行運(yùn)算，運(yùn)算的規(guī)律是：0&0=0，0&1=0，1&0=0，1&1=1。對(duì)于參加運(yùn)算的數(shù)要換算為二進(jìn)制進(jìn)行運(yùn)算，例如，3&2的結(jié)果是2，運(yùn)算過(guò)程是：3&2=0111&0010=0010=2。例7-1

聲明兩個(gè)局部變量并賦值，求它們的位運(yùn)算。declare@aint,@bintselect@a=7,@b=4select@a&@bas'a&b',@a|@bas'a|b',@a^@bas'a^b'顯示結(jié)果為：4、7、3。7.1T-SQL運(yùn)算符與表達(dá)式比較運(yùn)算符用于測(cè)試兩個(gè)表達(dá)式的值是否相同，運(yùn)算結(jié)果為“true”或“false”。比較運(yùn)算符及名稱(chēng)如表7-2所示。7.1T-SQL運(yùn)算符與表達(dá)式3.比較運(yùn)算符運(yùn)算符運(yùn)算名稱(chēng)運(yùn)算符運(yùn)算名稱(chēng)＝相等＜＝小于或等于＞大于＜＞、?。讲坏扔冢夹∮冢。疾恍∮冢荆酱笥诨虻扔冢。静淮笥诒?-2比較運(yùn)算符及名稱(chēng)邏輯運(yùn)算符用于對(duì)某個(gè)條件進(jìn)行測(cè)試，運(yùn)算結(jié)果為“true”或“false”，邏輯運(yùn)算符及規(guī)則如表7-3所示。7.1T-SQL運(yùn)算符與表達(dá)式運(yùn)算符運(yùn)算名稱(chēng)and如果兩個(gè)操作數(shù)都為“true”，則運(yùn)算結(jié)果為“true”or如果兩個(gè)操作數(shù)中有一個(gè)為“true”，則運(yùn)算結(jié)果為“true”not若一個(gè)操作值為“true”，則運(yùn)算結(jié)果為“false”，否則為“true”all如果兩個(gè)操作數(shù)值都為“true”，則運(yùn)算結(jié)果為“true”any如果在一系列操作數(shù)中只要有一個(gè)為“true”，則運(yùn)算結(jié)果為“true”between如果兩個(gè)操作數(shù)在指定的范圍內(nèi)，則運(yùn)算結(jié)果為“true”exists如果子查詢(xún)包含一些行，則運(yùn)算結(jié)果為“true”in如果操作數(shù)值等于表達(dá)式列表中的一個(gè)，則運(yùn)算結(jié)果為“true”like如果操作數(shù)與一種模式相匹配，則運(yùn)算結(jié)果為“true”some如果在一系列操作數(shù)中有些值為“true”，則運(yùn)算結(jié)果為“true”表7-3邏輯運(yùn)算符及規(guī)則4.邏輯運(yùn)算符字符串連接運(yùn)算符通過(guò)運(yùn)算符“+”實(shí)現(xiàn)兩個(gè)或多個(gè)字符串的連接運(yùn)算。例7-2

執(zhí)行下面的語(yǔ)句連接多個(gè)字符串。運(yùn)算結(jié)果為“abcdefghijk”。select('ab'+'cdefg'+'hijk')as字符串連接7.1T-SQL運(yùn)算符與表達(dá)式5.字符串連接運(yùn)算符6.賦值運(yùn)算符在給局部變量賦值的set和select語(yǔ)句中使用的“=”運(yùn)算符稱(chēng)為賦值運(yùn)算符。賦值運(yùn)算符用于將表達(dá)式的值賦予另外一個(gè)變量，也可以使用賦值運(yùn)算符在列標(biāo)題和為列定義值的表達(dá)式之間建立關(guān)系。當(dāng)一個(gè)復(fù)雜的表達(dá)式有多個(gè)運(yùn)算符時(shí)，運(yùn)算符優(yōu)先級(jí)決定執(zhí)行運(yùn)算的先后次序，執(zhí)行的順序會(huì)影響所得到的運(yùn)算結(jié)果。在一個(gè)表達(dá)式中，括號(hào)最優(yōu)先，其次按先高(優(yōu)先級(jí)數(shù)字小)后低(優(yōu)先級(jí)數(shù)字大)的順序進(jìn)行運(yùn)算。運(yùn)算符的優(yōu)先級(jí)如表7-4所示。7.1T-SQL運(yùn)算符與表達(dá)式6.賦值運(yùn)算符7.1T-SQL運(yùn)算符與表達(dá)式運(yùn)算符優(yōu)先級(jí)+(正)，-(負(fù))1*(乘)，/(除)，%(模)2+(加)，+(串聯(lián))，-(減)3=,>,<,>=,<=,<>,!=,!>,!<(比較運(yùn)算符)4^(位異或)，&(位與)，|(位或)5not6and7all,any,between,in,like,or,some8=(賦值)9表7-4運(yùn)算符的優(yōu)先級(jí)7.2T-SQL的變量變量是執(zhí)行程序中必不可少的部分，主要用來(lái)在程序運(yùn)行過(guò)程中存儲(chǔ)和傳遞數(shù)據(jù)。變量其實(shí)就是內(nèi)存中的一個(gè)存儲(chǔ)區(qū)域，存儲(chǔ)在這個(gè)區(qū)域中的數(shù)據(jù)就是變量的值，由系統(tǒng)或用戶(hù)定義并賦值。T-SQL語(yǔ)句中的變量有兩種：局部變量和全局變量。這兩種變量在使用方法和具體意義上均不相同。7.2

T-SQL的變量局部變量是作用域局限在一定范圍內(nèi)的變量，是用戶(hù)自定義的變量。一般來(lái)說(shuō)，局部變量的使用范圍局限于定義它的批處理內(nèi)。定義它的批處理中的SQL語(yǔ)句可以引用這個(gè)局部變量，直到批處理結(jié)束，這個(gè)局部變量的生命周期也就結(jié)束了。局部變量在程序中通常用來(lái)存儲(chǔ)從表中查詢(xún)到的數(shù)據(jù)或在程序執(zhí)行過(guò)程中用于暫存變量。通常將其用于下面3種情況：(1)作為計(jì)數(shù)器，計(jì)算循環(huán)執(zhí)行的次數(shù)或控制循環(huán)執(zhí)行的次數(shù)。(2)保存數(shù)據(jù)值以供控制流語(yǔ)句測(cè)試。(3)保存由存儲(chǔ)過(guò)程返回代碼返回的數(shù)據(jù)值。7.2.1

局部變量1.概念及其用途在使用一個(gè)局部變量之前，必須先聲明該變量。其語(yǔ)法格式如下：declare@變量名變量類(lèi)型[,...n]參數(shù)說(shuō)明：(1)局部變量名的命名必須遵循SQLServer的標(biāo)識(shí)符命名規(guī)則，并且必須以字符“@”開(kāi)頭。(2)局部變量的類(lèi)型可以是系統(tǒng)數(shù)據(jù)類(lèi)型，也可以是用戶(hù)自定義的數(shù)據(jù)類(lèi)型。(3)declare語(yǔ)句可以聲明一個(gè)或多個(gè)局部變量，變量被聲明以后初值都是“null”。7.2.1

局部變量2.聲明及其賦值1)聲明局部變量局部變量被創(chuàng)建之后，系統(tǒng)將其初始值設(shè)為“null”。若要改變局部變量的值，可以使用set語(yǔ)句或select語(yǔ)句給局部變量重新賦值。select語(yǔ)句的語(yǔ)法格式如下：select@變量名=表達(dá)式[,...]set語(yǔ)句的語(yǔ)法格式如下：set@變量名=表達(dá)式7.2.1

局部變量2.聲明及其賦值2)局部變量賦值參數(shù)說(shuō)明：(1)@變量名是準(zhǔn)備為其賦值的局部變量。表達(dá)式是有效的SQLServer表達(dá)式，且其類(lèi)型應(yīng)與局部變量的數(shù)據(jù)類(lèi)型相匹配。(2)從語(yǔ)法格式中可以看出，select語(yǔ)句和set語(yǔ)句的區(qū)別在于使用set語(yǔ)句一次只能給一個(gè)變量賦值，而select語(yǔ)句可以一次給多個(gè)變量賦值。7.2.1

局部變量2.聲明及其賦值2)局部變量賦值可以使用select或print語(yǔ)句顯示局部變量的值。其語(yǔ)法格式如下：select@變量名[,...n]print@變量名兩者的區(qū)別在于，select語(yǔ)句以表格方式顯示一個(gè)或多個(gè)變量的值，而print語(yǔ)句在消息框中顯示一個(gè)變量的值。7.2.1

局部變量3.顯示局部變量的值例7-3

聲明一個(gè)長(zhǎng)度為12個(gè)字符的局部變量s#，并為其賦值。代碼如下：declare@s#varchar(12)select@s#='2018010101'例7-4聲明一個(gè)局部變量maxscore，將sc表中學(xué)號(hào)(s#)為2018010101的最高分賦值給這個(gè)變量。代碼如下：declare@maxscorenumeric(6,1)select@maxscore=max(score)fromscwheres#='2018010101'7.2.1

局部變量3.顯示局部變量的值例7-5

顯示例1和例2中定義的局部變量@s#和@maxscore的值。代碼如下：declare@s#varchar(12)select@s#='2018010101'declare@maxscorenumeric(6,1)select@maxscore=max(score)fromscwheres#='2018010101'print@s#print@maxscore7.2.1

局部變量3.顯示局部變量的值選定上述語(yǔ)句，單擊“執(zhí)行”按鈕，print一次只能顯示一個(gè)變量的值。顯示結(jié)果如圖7-1所示。7.2.1

局部變量3.顯示局部變量的值圖7-1局部變量的聲明、賦值和顯示全局變量是以“@@”開(kāi)頭，由系統(tǒng)預(yù)先定義并負(fù)責(zé)維護(hù)的變量。也可以把全局變量看成是一種特殊形式的函數(shù)。全局變量不可以由用戶(hù)隨意建立和修改，其作用范圍也并不局限于某個(gè)程序，而是任何程序均可調(diào)用。常用的全局變量有30多個(gè)，通常用來(lái)存儲(chǔ)一些SQLServer的配置值和效能統(tǒng)計(jì)數(shù)字，用戶(hù)可以通過(guò)查詢(xún)?nèi)肿兞縼?lái)檢測(cè)系統(tǒng)的參數(shù)值或執(zhí)行查詢(xún)命令后的狀態(tài)值。在全局變量的使用過(guò)程中要注意，全局變量是由SQLServer服務(wù)器定義的，用戶(hù)只能使用全局變量，且在引用全局變量時(shí)，必須以“@@”開(kāi)頭。另外，局部變量的名稱(chēng)不能與全局變量的名稱(chēng)相同，否則就會(huì)在應(yīng)用程序中出錯(cuò)。7.2.2

全局變量表7-5列出了SQLServer的幾個(gè)常用全局變量及其含義，其他全局變量可查閱SQLServer聯(lián)機(jī)叢書(shū)。7.2.2

全局變量全局變量名稱(chēng)全局變量的意義@@version返回當(dāng)前安裝的日期、版本和處理器類(lèi)型@@servername返回運(yùn)行MicrosoftSQLServer的本地服務(wù)器名稱(chēng)@@spid返回當(dāng)前用戶(hù)進(jìn)程的進(jìn)程ID@@identity返回最后插入的標(biāo)識(shí)列的列值@@error返回執(zhí)行Transact-SQL語(yǔ)句的錯(cuò)誤代碼@@procid返回當(dāng)前存儲(chǔ)過(guò)程的ID值@@language返回當(dāng)前使用的語(yǔ)言名表7-5幾個(gè)常見(jiàn)的全局變量例7-6執(zhí)行select@@servername，返回服務(wù)器名稱(chēng)，顯示結(jié)果如圖7-2所示。7.2.2

全局變量圖7-2服務(wù)器名稱(chēng)執(zhí)行select*FromSys.SysServers，返回服務(wù)器名稱(chēng)等信息，顯示結(jié)果如圖7-3所示。圖7-3服務(wù)器名稱(chēng)信息例7-7執(zhí)行Select@@spidas'ID',system_useras'LoginName',useras'UserName',返回用戶(hù)ID、登錄名及用戶(hù)名信息，如圖7-4所示。7.2.2

全局變量圖7-4用戶(hù)ID、登錄名及用戶(hù)名信息7.3程序控制語(yǔ)句SQLServer支持結(jié)構(gòu)化的編程方法，結(jié)構(gòu)化編程中程序流程控制的三大結(jié)構(gòu)是順序結(jié)構(gòu)、選擇結(jié)構(gòu)和循環(huán)結(jié)構(gòu)。T-SQL提供了實(shí)現(xiàn)這3種結(jié)構(gòu)的流程控制語(yǔ)句，使用這些流程控制語(yǔ)句可以控制命令的執(zhí)行順序，以便更好地組織程序。7.3

程序控制語(yǔ)句begin…end語(yǔ)句相當(dāng)于其他語(yǔ)言中的復(fù)合語(yǔ)句，如C語(yǔ)言中的{}。它用于將多條T-SQL語(yǔ)句封裝為一個(gè)整體的語(yǔ)句塊，即將begin…end內(nèi)的所有T-SQL語(yǔ)句視為一個(gè)單元執(zhí)行。在實(shí)際應(yīng)用中，begin…end語(yǔ)句一般與if…else、while等語(yǔ)句聯(lián)用，當(dāng)判斷條件符合需要執(zhí)行兩個(gè)或多個(gè)語(yǔ)句時(shí)，就需要使用begin…end語(yǔ)句將這些語(yǔ)句封裝為一個(gè)語(yǔ)句塊。begin…end語(yǔ)句塊允許嵌套。7.3.1

begin…end語(yǔ)句其語(yǔ)法格式如下：begin{SQL語(yǔ)句塊|程序塊}end該語(yǔ)句適用于以下情況：while循環(huán)需要包含多條語(yǔ)句，case函數(shù)的元素需要包含多條語(yǔ)句，if或else子句中需要包含多條語(yǔ)句。7.3.1

begin…end語(yǔ)句例7-8在數(shù)據(jù)庫(kù)studscore_ds1的職工信息表employees中，查詢(xún)001號(hào)員工是否存在，如果有則刪除該員工，并顯示提示信息。代碼如下：usestudscore_ds1ifexists(select*fromemployeeswhereempid='001')begindeletefromemployeeswhereempid='001'print'001employeeisdeleted!'endelseprint'001employeenotfound!'7.3.1

begin…end語(yǔ)句if…else語(yǔ)句是條件判斷語(yǔ)句，用以實(shí)現(xiàn)選擇結(jié)構(gòu)。當(dāng)if后的條件成立時(shí)就執(zhí)行其后的T-SQL語(yǔ)句，條件不成立時(shí)執(zhí)行else語(yǔ)句后的T-SQL語(yǔ)句。其中，else子句是可選項(xiàng)，如果沒(méi)有else子句，當(dāng)條件不成立時(shí)則執(zhí)行if語(yǔ)句后的其他語(yǔ)句。其語(yǔ)法格式如下：if<條件表達(dá)式>{SQL語(yǔ)句塊|程序塊}[else{SQL語(yǔ)句塊|程序塊}]7.3.2

單條件分支語(yǔ)句語(yǔ)法說(shuō)明：①條件表達(dá)式是作為執(zhí)行和判斷條件的布爾表達(dá)式，返回“true”或“false”。如果布爾表達(dá)式中含有select語(yǔ)句，則必須用圓括號(hào)將select語(yǔ)句括起來(lái)。②程序塊是一條T-SQL語(yǔ)句或一個(gè)begin…end語(yǔ)句塊。③if…else語(yǔ)句允許嵌套使用，可以在if之后或在else的下面嵌套另一個(gè)if語(yǔ)句。嵌套級(jí)數(shù)的限制取決于可用內(nèi)存。7.3.2

單條件分支語(yǔ)句例7-9在數(shù)據(jù)庫(kù)studscore_ds1的學(xué)生成績(jī)sc表中，查詢(xún)是否存在考90分及以上的學(xué)生信息；有則顯示結(jié)果，無(wú)則顯示“沒(méi)有考90分及以上的學(xué)生”。代碼如下：usestudscore_ds1ifexists(select*fromscwherestudscore>=90)beginprint'有考90分及以上的學(xué)生'select*fromscwherestudscore>=90endelseprint'沒(méi)有考90分及以上的學(xué)生'7.3.2

單條件分支語(yǔ)句if…elseif…else語(yǔ)句用于多條件分支執(zhí)行。其語(yǔ)法格式如下：if<條件表達(dá)式>{SQL語(yǔ)句塊|程序塊}elseif<條件表達(dá)式>{SQL語(yǔ)句塊|程序塊}…else{SQL語(yǔ)句塊|程序塊}7.3.3

多條件分支語(yǔ)句1.if多條件分支elseif@avgsc>=80set@scorelevel='良好'elseif@avgsc>=70set@scorelevel='中等'elseif@avgsc>=60set@scorelevel='及格'elseset@scorelevel='不及格'print@scorelevel7.3.3

多條件分支語(yǔ)句1.if多條件分支12例7-10

在數(shù)據(jù)庫(kù)studscore_ds1的sc表中，使用if多條件分支語(yǔ)句判斷學(xué)生的成績(jī)等級(jí)，并顯示該學(xué)生的等級(jí)。代碼如下：usestudscore_ds1declare@avgscnumeric(5,1),@scorelevelvarchar(10)select@avgsc=(selectavg(score)fromscwheres#='2018010101')if@avgsc>=90set@scorelevel='優(yōu)秀'case語(yǔ)句和if…else語(yǔ)句一樣，用來(lái)實(shí)現(xiàn)選擇結(jié)構(gòu)，case語(yǔ)句避免了多重嵌套，更加簡(jiǎn)潔清晰。T-SQL中的case語(yǔ)句可分為簡(jiǎn)單case語(yǔ)句和搜索case語(yǔ)句兩種。

1)簡(jiǎn)單case語(yǔ)句2)搜索case語(yǔ)句命令方式修改數(shù)據(jù)庫(kù)7.3.3

多條件分支語(yǔ)句2.case多條件分支其語(yǔ)法格式如下：case<運(yùn)算式>when<運(yùn)算式>then<運(yùn)算式>...when<運(yùn)算式>then<運(yùn)算式>[else<運(yùn)算式>]end7.3.3

多條件分支語(yǔ)句2.case多條件分支1)簡(jiǎn)單case語(yǔ)句參數(shù)說(shuō)明：①case后的表達(dá)式用于和when后的表達(dá)式逐個(gè)進(jìn)行比較，兩者的數(shù)據(jù)類(lèi)型必須是相同的，或者是可以進(jìn)行隱式轉(zhuǎn)換的。②then后面給出當(dāng)case后的表達(dá)式與when后的表達(dá)式相等時(shí)，要返回的結(jié)果表達(dá)式。7.3.3

多條件分支語(yǔ)句2.case多條件分支1)簡(jiǎn)單case語(yǔ)句簡(jiǎn)單case語(yǔ)句的執(zhí)行過(guò)程是：首先計(jì)算case后面表達(dá)式的值，然后按指定順序?qū)γ總€(gè)when子句后的表達(dá)式進(jìn)行比較。當(dāng)遇到與case后表達(dá)式值相等的，則執(zhí)行對(duì)應(yīng)的then后的結(jié)果表達(dá)式，并退出case結(jié)構(gòu)；若case后的表達(dá)式值與所有when后的表達(dá)式均不相等，則返回else后的結(jié)果表達(dá)式；若case后的表達(dá)式值與所有when后的表達(dá)式均不相等，且“else結(jié)果表達(dá)式”部分被省略，則返回“null”值。7.3.3

多條件分支語(yǔ)句2.case多條件分支1)簡(jiǎn)單case語(yǔ)句when1then'A'when2then'B'when3then'C'when4then'D'when5then'E'else'others'endprint'theansweris'+@answer7.3.3

多條件分支語(yǔ)句12例7-11

產(chǎn)生一個(gè)0～1之間的隨機(jī)數(shù)，然后使用簡(jiǎn)單case語(yǔ)句給出變量的值，并顯示結(jié)果。代碼如下：declare@aint,@answerchar(10)set@a=cast(rand()*10asint)print@aset@answer=case@a2.case多條件分支1)簡(jiǎn)單case語(yǔ)句其語(yǔ)法格式如下：casewhen<條件表達(dá)式>then<運(yùn)算式>...when<條件表達(dá)式>then<運(yùn)算式>[else<運(yùn)算式>]end參數(shù)說(shuō)明：case后面沒(méi)有表達(dá)式。when后面的條件表達(dá)式是作為執(zhí)行和判斷條件的布爾表達(dá)式。7.3.3

多條件分支語(yǔ)句2.case多條件分支2)搜索case語(yǔ)句搜索case語(yǔ)句的執(zhí)行過(guò)程是：首先測(cè)試when條件表達(dá)式，若為真，則執(zhí)行then后面的結(jié)果表達(dá)式，否則進(jìn)行下一個(gè)條件表達(dá)式的測(cè)試；若所有when后面的條件表達(dá)式都為假，則執(zhí)行else后面的結(jié)果表達(dá)式；若所有when后面的條件表達(dá)式都為假，且“else結(jié)果表達(dá)式”部分被省略，則返回“null”。7.3.3

多條件分支語(yǔ)句2.case多條件分支2)搜索case語(yǔ)句例7-12

在表employees中，計(jì)算平均工資，然后分析判斷員工工資的總體情況。代碼如下：declare@avgsalaryfloat，@salarylevelnchar(50)select@avgsalary=(selectavg(salary)fromemployees)set@salarylevel=casewhen@avgsalary>=15000then'偏高收入！'when@avgsalary>=8500then'高收入！'when@avgsalary>=3500then'中等收入！'when@avgsalary>=1500then'大于最低標(biāo)準(zhǔn)！'else'無(wú)保障收入'endselect@salarylevel7.3.3

多條件分支語(yǔ)句2.case多條件分支2)搜索case語(yǔ)句while語(yǔ)句用于實(shí)現(xiàn)循環(huán)結(jié)構(gòu)，其功能是在滿(mǎn)足循環(huán)條件的情況下，重復(fù)執(zhí)行T-SQL語(yǔ)句或語(yǔ)句塊。當(dāng)while后面的條件為真時(shí)，就重復(fù)執(zhí)行begin…end之間的語(yǔ)句塊。while語(yǔ)句塊中的continue和break是可選項(xiàng)。若有continue語(yǔ)句，則其功能是跳過(guò)continue后的語(yǔ)句，執(zhí)行下一次循環(huán)條件測(cè)試。若遇到break語(yǔ)句，則其功能是立即終止循環(huán)，結(jié)束整個(gè)while語(yǔ)句的執(zhí)行，并繼續(xù)執(zhí)行while語(yǔ)句后的其他語(yǔ)句。7.3.4

循環(huán)語(yǔ)句其語(yǔ)法格式如下：while條件表達(dá)式begin程序塊[break]程序塊[continue]程序塊end參數(shù)說(shuō)明：條件表達(dá)式是作為執(zhí)行和判斷的布爾表達(dá)式，返回“true”或“false”。如果布爾表達(dá)式中含有select語(yǔ)句，則必須用圓括號(hào)將select語(yǔ)句括T-SQL語(yǔ)句或一個(gè)begin…end語(yǔ)句塊。7.3.4

循環(huán)語(yǔ)句printconvert(char(3),@i)--convert()轉(zhuǎn)換函數(shù)，轉(zhuǎn)換為字符set@n=@n+1endset@i=@i+1endprint'1-100能被7整除的數(shù)共計(jì)'+convert(char(3),@n)+'個(gè)'12例7-13

進(jìn)行T-SQL編程，輸出1～100之間能被7整除的數(shù)，并且最后輸出總個(gè)數(shù)。代碼如下：declare@iint,@nintselect@i=1,@n=0print'1-100能被7整除的數(shù)：'while@i<=100beginif(@i%7)=0begin7.3.4

循環(huán)語(yǔ)句例7-14

使用while循環(huán)語(yǔ)句，計(jì)算s=1!+2!+3!+4!+5!。代碼如下：declare@iint,@jint,@sintset@i=1set@j=1set@s=0while@i<=5beginset@j=@j*@iset@s=@s+@jset@i=@i+1endprint's=1!+2!+3!+4!+5!='+convert(char(5),@s)7.3.4

循環(huán)語(yǔ)句例7-15

使用while循環(huán)語(yǔ)句，計(jì)算出1～10之間偶數(shù)的平方和，并輸出結(jié)果。代碼如下：declare@iint,@sumintselect@i=1,@sum=0while@i<=10beginif@i&1=0--使用&運(yùn)算符beginset@sum=@sum+@i*@iendset@i=@i+1endprint@sum7.3.4

循環(huán)語(yǔ)句goto語(yǔ)句是轉(zhuǎn)向語(yǔ)句，讓程序跳轉(zhuǎn)到一個(gè)指定的標(biāo)簽處并執(zhí)行其后的代碼。goto語(yǔ)句和標(biāo)簽可以在程序、批處理和語(yǔ)句塊中的任意位置使用，也可以嵌套使用。其語(yǔ)法格式如下：定義標(biāo)簽label:改變執(zhí)行g(shù)otolabel參數(shù)說(shuō)明：若有g(shù)oto語(yǔ)句指向label標(biāo)簽，則其為處理的起點(diǎn)。標(biāo)簽必須符合標(biāo)識(shí)符規(guī)則。7.3.5

goto語(yǔ)句例7-16

結(jié)合使用T-SQL編程的goto語(yǔ)句求10！，并顯示計(jì)算結(jié)果。代碼如下：declare@sint,@timesintselect@s=1,@times=1label1:--定義語(yǔ)句標(biāo)號(hào)set@s=@s*@timesset@times=@times+1if@times<=10gotolabel1print'10!='+str(@s)7.3.5

goto語(yǔ)句return語(yǔ)句用于結(jié)束當(dāng)前程序的執(zhí)行，無(wú)條件地終止一個(gè)查詢(xún)、存儲(chǔ)過(guò)程或批處理，返回到上一個(gè)調(diào)用它的程序或其他程序；在括號(hào)內(nèi)可指定一個(gè)返回值。其語(yǔ)法格式如下：return[integer_expression]參數(shù)說(shuō)明：integer_expression為返回的整型值。存儲(chǔ)過(guò)程可以給調(diào)用過(guò)程或應(yīng)用程序返回整型值。7.3.6

eturn語(yǔ)句例7-17

創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，通過(guò)return語(yǔ)句返回一個(gè)值，用于判斷員工表employees是否存在該員工。代碼如下：createprocedurecheck_employee(@empidvarchar(50))asifexists(select*fromemployeeswhereempid=@empid)return1elsereturn-100該題目中，找到該員工則返回“1”，否則返回“-100”。7.3.6

eturn語(yǔ)句waitfor語(yǔ)句用于在達(dá)到指定時(shí)間或時(shí)間間隔之前阻止執(zhí)行批處理、存儲(chǔ)過(guò)程或事務(wù)，直到所設(shè)定的時(shí)間已到或等待了指定的時(shí)間間隔之后才繼續(xù)往下運(yùn)行。其語(yǔ)法格式如下：waitfordelay等待時(shí)間|time完成時(shí)間7.3.7waitfor語(yǔ)句參數(shù)說(shuō)明：(1)“delay等待時(shí)間”是指定可以繼續(xù)執(zhí)行批處理、存儲(chǔ)過(guò)程或事務(wù)之前必須經(jīng)過(guò)的指定時(shí)間段，最長(zhǎng)可為24小時(shí)?？墒褂胐atetime數(shù)據(jù)可接受的格式之一指定“等待時(shí)間”，也可以將其指定為局部時(shí)間，但不能指定日期，因此不允許指定datetime值的日期部分。7.3.7waitfor語(yǔ)句(2)“time完成時(shí)間”是指定運(yùn)行批處理、存儲(chǔ)過(guò)程或事務(wù)的具體時(shí)刻。可以使用datetime數(shù)據(jù)可以接受的格式之一指定“完成時(shí)間”，也可以將其指定為局部變量，但不能指定日期，因此不允許指定datetime值的日期部分。例7-18

在10點(diǎn)盤(pán)點(diǎn)，從sales表中查詢(xún)當(dāng)前的銷(xiāo)售情況。這里的時(shí)間是服務(wù)器時(shí)間，而不是客戶(hù)端時(shí)間。代碼如下：beginwaitfortime'10:00:00'select*fromsalesend7.3.7waitfor語(yǔ)句在T-SQL中可以使用兩類(lèi)注釋符。ANSI標(biāo)準(zhǔn)的注釋符“--”用于單行注釋?zhuān)?**/用于多行注釋。1)2)聚集索引非聚集索引7.3.8注釋語(yǔ)句其語(yǔ)法格式如下：--text_of_comment參數(shù)說(shuō)明：兩個(gè)連字符(--)是SQL-92標(biāo)準(zhǔn)的注釋指示符。text_of_comment為包含注釋文本的字符串。7.3.8注釋語(yǔ)句1.單行注釋其語(yǔ)法格式如下：/*text_of_comment*/參數(shù)說(shuō)明：text_of_comment為包含注釋文本的字符串。2.多行注釋腳本是存儲(chǔ)在文件中的一系列SQL語(yǔ)句，即一系列按順序提交的批處理。使用腳本可以將創(chuàng)建和維護(hù)數(shù)據(jù)庫(kù)時(shí)的操作步驟保存為一個(gè)磁盤(pán)文件，文件的擴(kuò)展名為.sql。將T-SQL語(yǔ)句保存為腳本，可以建立起可再用的模塊化代碼，還可以在不同的計(jì)算機(jī)之間傳送T-SQL語(yǔ)句，使兩臺(tái)計(jì)算機(jī)執(zhí)行同樣的操作。7.3.9注釋語(yǔ)句1.腳本批處理是包含一個(gè)或多個(gè)T-SQL語(yǔ)句的組，從應(yīng)用程序一次性地發(fā)送到MicrosoftSQLServer執(zhí)行。SQLServer將批處理語(yǔ)句編譯成一個(gè)可執(zhí)行單元，此單元稱(chēng)為執(zhí)行計(jì)劃。編譯錯(cuò)誤(如語(yǔ)法錯(cuò)誤)使執(zhí)行計(jì)劃無(wú)法編譯，從而導(dǎo)致批處理中的任何語(yǔ)句均無(wú)法執(zhí)行。7.3.9注釋語(yǔ)句2.批處理運(yùn)行時(shí)錯(cuò)誤(如算術(shù)溢出或違反約束)會(huì)產(chǎn)生以下兩種影響之一：(1)大多數(shù)運(yùn)行時(shí)錯(cuò)誤將停止執(zhí)行批處理中當(dāng)前語(yǔ)句和它之后的語(yǔ)句。(2)少數(shù)運(yùn)行時(shí)錯(cuò)誤(如違反約束)僅停止執(zhí)行當(dāng)前語(yǔ)句，而繼續(xù)執(zhí)行批處理中其他所有語(yǔ)句。在書(shū)寫(xiě)批處理語(yǔ)句時(shí)，需要使用go語(yǔ)句作為批處理命令的結(jié)束標(biāo)志。例7-19

go示例。代碼如下：usestudscore_ds1go--第一個(gè)批處理打開(kāi)數(shù)據(jù)庫(kù)的操作select*frombooksgo--第二個(gè)批處理查詢(xún)books表中的數(shù)據(jù)7.3.9注釋語(yǔ)句2.批處理7.4自定義函數(shù)用戶(hù)可以根據(jù)自己的需要自定義函數(shù)。用戶(hù)自定義函數(shù)不能用于執(zhí)行一系列改變數(shù)據(jù)庫(kù)狀態(tài)的操作，但可以像系統(tǒng)函數(shù)那樣在查詢(xún)或存儲(chǔ)過(guò)程等程序段中使用，也可以像存儲(chǔ)過(guò)程一樣通過(guò)execute命令來(lái)執(zhí)行。用戶(hù)自定義函數(shù)中存儲(chǔ)了一個(gè)T-SQL例程，可以返回一定的值。在SQLServer中根據(jù)函數(shù)返回值形式的不同將用戶(hù)自定義函數(shù)分為以下類(lèi)型：(1)標(biāo)量函數(shù)(ScalarFunction)：返回單一的數(shù)據(jù)值。(2)返回?cái)?shù)據(jù)集(Rowset)的用戶(hù)自定義函數(shù)：返回一個(gè)table類(lèi)型的數(shù)據(jù)集，依定義語(yǔ)法不同分為兩類(lèi)：行數(shù)據(jù)集函數(shù)和多語(yǔ)句數(shù)據(jù)集函數(shù)。7.4

自定義函數(shù)標(biāo)量函數(shù)返回單一的數(shù)據(jù)值，其類(lèi)型可以是除了text、ntext、image、cursor、rowversion(timestamp)之外的所有類(lèi)型。創(chuàng)建標(biāo)量函數(shù)的語(yǔ)法格式如下：createfunction[owner_name.]function_name([{@parameter_name[as]scalar_parameter_data_type[=default]}[,...n]])returnsscalar_return_data_type[with<function_option>[[,]...n]][as]beginfunction_bodyreturnscalar_expressionend7.4.1

創(chuàng)建自定義函數(shù)參數(shù)說(shuō)明：(1)@parameter_name[as]scalar_parameter_data_type[=default]表示函數(shù)的參數(shù)，可有0個(gè)或多個(gè)(最多1024個(gè)參數(shù))，而參數(shù)的名稱(chēng)前要加上“@”。參數(shù)行必須用小括號(hào)括起來(lái)，即使沒(méi)有參數(shù)，小括號(hào)也不可省略。可以用“=”來(lái)為參數(shù)指定默認(rèn)值，如createfunctionmyfunct(@achar(10),@bint=200)。參數(shù)的類(lèi)型必須是標(biāo)量類(lèi)型。(2)returnsscalar_return_data_type用于聲明返回值的類(lèi)型，可以是所有標(biāo)量類(lèi)型。7.4.1

創(chuàng)建自定義函數(shù)(3)with<function_option>[[,]...n]用于設(shè)置函數(shù)的選項(xiàng)。