數(shù)據(jù)庫(kù)SQL ServerSQLite教程課件：Transact-SQL 應(yīng)用

Transact-SQL應(yīng)用7.1

T-SQL運(yùn)算符與表達(dá)式7.2

T-SQL的變量7.3程序控制語(yǔ)句7.4自定義函數(shù)

7.1T-SQL運(yùn)算符與表達(dá)式

1.算術(shù)運(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)算。

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-1聲明兩個(gè)局部變量并賦值，求它們的位運(yùn)算。

declare@aint,@bint

select@a=7,@b=4

select@a&@bas'a&b',@a|@bas'a|b',@a^@bas'a^b'

顯示結(jié)果為：4、7、3。

3.比較運(yùn)算符

比較運(yùn)算符用于測(cè)試兩個(gè)表達(dá)式的值是否相同，運(yùn)算結(jié)果為“true”或“false”。比較運(yùn)算符及名稱(chēng)如表7-2所示。

4.邏輯運(yùn)算符

邏輯運(yùn)算符用于對(duì)某個(gè)條件進(jìn)行測(cè)試，運(yùn)算結(jié)果為“true”或“false”，邏輯運(yùn)算符及規(guī)則如表7-3所示。

5.字符串連接運(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字符串連接

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.2T-SQL的變量7.2.1局部變量1.概念及其用途局部變量是作用域局限在一定范圍內(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ù)值。

2.聲明及其賦值

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”。

2)局部變量賦值

局部變量被創(chuàng)建之后，系統(tǒng)將其初始值設(shè)為“null”。若要改變局部變量的值，可以使用set語(yǔ)句或select語(yǔ)句給局部變量重新賦值。

select語(yǔ)句的語(yǔ)法格式如下：

select@變量名=表達(dá)式[,...]

set語(yǔ)句的語(yǔ)法格式如下：

set@變量名=表達(dá)式

參數(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è)變量賦值。

3.顯示局部變量的值

可以使用select或print語(yǔ)句顯示局部變量的值。其語(yǔ)法格式如下：

select@變量名[,...n]

print@變量名

兩者的區(qū)別在于，select語(yǔ)句以表格方式顯示一個(gè)或多個(gè)變量的值，而print語(yǔ)句在消息框中顯示一個(gè)變量的值。圖7-1局部變量的聲明、賦值和顯示

7.2.2全局變量

全局變量是以“@@”開(kāi)頭，由系統(tǒng)預(yù)先定義并負(fù)責(zé)維護(hù)的變量。也可以把全局變量看成是一種特殊形式的函數(shù)。

在全局變量的使用過(guò)程中要注意，全局變量是由SQLServer服務(wù)器定義的，用戶(hù)只能使用全局變量，且在引用全局變量時(shí)，必須以“@@”開(kāi)頭。另外，局部變量的名稱(chēng)不

能與全局變量的名稱(chēng)相同，否則就會(huì)在應(yīng)用程序中出錯(cuò)。表7-5列出了SQLServer的幾個(gè)常用全局變量及其含義，其他全局變量可查閱SQLServer聯(lián)機(jī)叢書(shū)。

7-6執(zhí)行select@@servername，返回服務(wù)器名稱(chēng)，顯示結(jié)果如圖7-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-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.1begin…end語(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ǔ)句塊允許嵌套。

其語(yǔ)法格式如下：

begin

{

SQL語(yǔ)句塊|程序塊

}

end

該語(yǔ)句適用于以下情況：while循環(huán)需要包含多條語(yǔ)句，case函數(shù)的元素需要包含多條語(yǔ)句，if或else子句中需要包含多條語(yǔ)句。

例7-8在數(shù)據(jù)庫(kù)studscore_ds1的職工信息表employees中，查詢(xún)001號(hào)員工是否存在，如果有則刪除該員工，并顯示提示信息。代碼如下：

7.3.2單條件分支語(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ǔ)句塊|程序塊}

]

語(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-9在數(shù)據(jù)庫(kù)studscore_ds1的學(xué)生成績(jī)sc表中，查詢(xún)是否存在考90分及以上的學(xué)生信息；有則顯示結(jié)果，無(wú)則顯示“沒(méi)有考90分及以上的學(xué)生”。

7.3.3多條件分支語(yǔ)句

⒈if多條件分支

if…elseif…else語(yǔ)句用于多條件分支執(zhí)行。

其語(yǔ)法格式如下：

if<條件表達(dá)式>

{SQL語(yǔ)句塊|程序塊}

elseif<條件表達(dá)式>

{SQL語(yǔ)句塊|程序塊}

…

else

{SQL語(yǔ)句塊|程序塊}

例7-10在數(shù)據(jù)庫(kù)studscore_ds1的sc表中，使用if多條件分支語(yǔ)句判斷學(xué)生的成績(jī)等級(jí)，并顯示該學(xué)生的等級(jí)。代碼如下：

2.case多條件分支

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ǔ)句

其語(yǔ)法格式如下：

case<運(yùn)算式>

when<運(yùn)算式>then<運(yùn)算式>

...

when<運(yùn)算式>then<運(yùn)算式>

[else<運(yùn)算式>]

end

參數(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-11產(chǎn)生一個(gè)0～1之間的隨機(jī)數(shù)，然后使用簡(jiǎn)單case語(yǔ)句給出變量的值，并顯示結(jié)果。代碼如下：

2)搜索case語(yǔ)句

其語(yǔ)法格式如下：

case

when<條件表達(dá)式>then<運(yùn)算式>

...

when<條件表達(dá)式>then<運(yùn)算式>

[else<運(yùn)算式>]

end

參數(shù)說(shuō)明：case后面沒(méi)有表達(dá)式。when后面的條件表達(dá)式是作為執(zhí)行和判斷條件的布爾表達(dá)式

例7-12在表employees中，計(jì)算平均工資，然后分析判斷員工工資的總體情況。代碼如下：

7.3.4循環(huán)語(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ǔ)句。

參數(shù)說(shuō)明：條件表達(dá)式是作為執(zhí)行和判斷的布爾表達(dá)式，返回“true”或“false”。如果布爾表達(dá)式中含有select語(yǔ)句，則必須用圓括號(hào)將select語(yǔ)句括起來(lái)。程序塊是一條

T-SQL語(yǔ)句或一個(gè)begin…end語(yǔ)句塊。

例7-13進(jìn)行T-SQL編程，輸出1～100之間能被7整除的數(shù)，并且最后輸出總個(gè)數(shù)。

代碼如下：

7.3.5goto語(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-16結(jié)合使用T-SQL編程的goto語(yǔ)句求10！，并顯示計(jì)算結(jié)果。代碼如下：

7.3.6return語(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-17創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，通過(guò)return語(yǔ)句返回一個(gè)值，用于判斷員工表employees是否存在該員工。代碼如下：

7.3.7waitfor語(yǔ)句

waitfor語(yǔ)句用于在達(dá)到指定時(shí)間或時(shí)間間隔之前阻止執(zhí)行批處理、存儲(chǔ)過(guò)程或事務(wù)，直到所設(shè)定的時(shí)間已到或等待了指定的時(shí)間間隔之后才繼續(xù)往下運(yùn)行。

其語(yǔ)法格式如下：

waitfordelay等待時(shí)間|time完成時(shí)間

參數(shù)說(shuō)明：

(1)“delay等待時(shí)間”是指定可以繼續(xù)執(zhí)行批處理、存儲(chǔ)過(guò)程或事務(wù)之前必須經(jīng)過(guò)的指定時(shí)間段，最長(zhǎng)可為24小時(shí)?？墒褂胐atetime數(shù)據(jù)可接受的格式之一指定“等待時(shí)間”，也可以將其指定為局部時(shí)間，但不能指定日期，因此不允許指定datetime值的日期部分。

(2)“time完成時(shí)間”是指定運(yùn)行批處理、存儲(chǔ)過(guò)程或事務(wù)的具體時(shí)刻?？梢允褂胐atetime數(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í)間。代碼如下：

7.3.8注釋語(yǔ)句

在T-SQL中可以使用兩類(lèi)注釋符。ANSI標(biāo)準(zhǔn)的注釋符“--”用于單行注釋?zhuān)?**/用于多行注釋。

1.單行注釋

其語(yǔ)法格式如下：

--text_of_comment

參數(shù)說(shuō)明：兩個(gè)連字符(--)是SQL-92標(biāo)準(zhǔn)的注釋指示符。text_of_comment為包含注釋文本的字符串。

2.多行注釋

其語(yǔ)法格式如下：

/*text_of_comment*/

參數(shù)說(shuō)明：text_of_comment為包含注釋文本的字符串。

7.3.9使用腳本和批處理

1.腳本

腳本是存儲(chǔ)在文件中的一系列SQL語(yǔ)句，即一系列按順序提交的批處理。使用腳本可以將創(chuàng)建和維護(hù)數(shù)據(jù)庫(kù)時(shí)的操作步驟保存為一個(gè)磁盤(pán)文件，文件的擴(kuò)展名為.sql。

2.批處理

批處理是包含一個(gè)或多個(gè)T-SQL語(yǔ)句的組，從應(yīng)用程序一次性地發(fā)送到MicrosoftSQLServer執(zhí)行。SQLServer將批處理語(yǔ)句編譯成一個(gè)可執(zhí)行單元，此單元稱(chēng)為執(zhí)行計(jì)劃。

運(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-19go示例。代碼如下：

usestudscore_ds1

go--第一個(gè)批處理打開(kāi)數(shù)據(jù)庫(kù)的操作

select*frombooks

go--第二個(gè)批處理查詢(xún)books表中的數(shù)據(jù)

7.4自定義函數(shù)

在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.1創(chuàng)建自定義函數(shù)

標(biāo)量函數(shù)返回單一的數(shù)據(jù)值，其類(lèi)型可以是除了text、ntext、image、cursor、rowversion(timestamp)之外的所有類(lèi)型。

創(chuàng)建標(biāo)量函數(shù)的語(yǔ)法格式如下：

參數(shù)說(shuō)明：

(1)@parameter_name[as]scalar_parameter_data_