SQL Server 數(shù)據(jù)庫(kù)開(kāi)發(fā)經(jīng)典案例教程第10章T-SQL編程

第10章T-SQL編程本章目標(biāo)掌握如何定義變量并賦值掌握如何輸出顯示數(shù)據(jù)掌握IF、WHILE、CASE邏輯控制語(yǔ)句理解SQL中批處理的概念使用變量變量分為局部變量和全局變量局部變量使用DECLARE定義,聲明時(shí)必須在變量名前加@局部變量必須先聲明后賦值如:變量名vname定義成@vname全局變量標(biāo)識(shí)為兩個(gè)@標(biāo)記(@@)由SQLServer提供的，不能由用戶創(chuàng)建。全局變量提供關(guān)于SQLServer的當(dāng)前狀態(tài)信息如:SQLServer當(dāng)前版本號(hào):@@version局部變量的聲明和賦值局部變量的聲明語(yǔ)法:例如:局部變量的賦值有兩種方法語(yǔ)法:例如DECLARE@variable_nameDataTypeDECLARE@countint--聲明一個(gè)局部變量@countDECLARE@enamevarchar(8)--聲明一個(gè)局部變量ename,最多可存儲(chǔ)８?jìng)€(gè)字符SET@variable_name=value或SELECT@variable_name=valueSET@count=10或SELECT@count=10SET@ename=’SMITH’或SELECT@ename=’SMITH’局部變量的使用SET賦值語(yǔ)句一般用于賦給變量指定的數(shù)據(jù)常量SELECT賦值語(yǔ)句一般用于從表中查詢數(shù)據(jù)，然后再賦給變量。

需要注意的是:SELECT語(yǔ)句需要確保篩選的記錄不多于一條。如果查詢的記錄多于一條，將把最后一條記錄的值賦給變量。根據(jù)用戶名查找和“張敏“的地址相同的用戶信息DECLARE@namevarchar(20)

――定義用戶名DECLARE@addrvarchar(20)

――定義地址SET@name='張敏'

――使用set給用戶名賦值SELECT@addr=addrFROMUserInfo

――使用select存儲(chǔ)張敏的地址WHEREUserName=@nameSELECT*FROMUserInfoWHEREAddr=@addr

――篩選數(shù)據(jù)局部變量的特點(diǎn)一個(gè)SELECT語(yǔ)句可以初始化多個(gè)局部變量。DECLARE@bookNamenvarchar(100),@authornvarchar(100)--聲明多個(gè)變量SET@bookName='java',@author='孫天琴'--使用SET給多個(gè)變量賦值SElECT@bookName='c#',@author='明日'--使用SELECT給多個(gè)變量賦值SET@bookName=(SELECTBookNameFROMBookInfo)--SET將多個(gè)查詢的結(jié)果賦值給變量出錯(cuò)SELECT@author=AuthorfromBookInfo--使用SELECT將查詢的最有一個(gè)值賦給變量SET@author=(SELECTAuthorFROMBookInfoWHEREPrice<0)

--查詢結(jié)果不存在時(shí)將NULL賦給變量SELECT@author='林林'SELECT@author=AuthorFROM

BookInfoWHEREPrice<0

--查詢結(jié)果不存在時(shí)@author保持原值全局變量全局變量標(biāo)識(shí)為兩個(gè)@標(biāo)記(@@),它是由SQLServer提供的變量含義@@ERROR最后一個(gè)T-SQL錯(cuò)誤的錯(cuò)誤號(hào)@@IDENTITY最后一次插入的標(biāo)識(shí)值@@LANGUAGE當(dāng)前使用的語(yǔ)言的名稱@@MAX_CONNECTIONS可以創(chuàng)建的同時(shí)連接的最大數(shù)目@@ROWCOUNT 受上一個(gè)SQL語(yǔ)句影響的行數(shù)@@SERVERNAME本地服務(wù)器的名稱@@TRANSCOUNT 當(dāng)前連接打開(kāi)的事務(wù)數(shù)@@VERSION

SQLServer的版本信息全局變量的使用print'SQLServer的版本'+@@VERSIONprint'服務(wù)器的名稱:'+@@SERVERNAMEprint'最后學(xué)號(hào)為:'+convert(varchar(5),@@IDENTITY)輸出語(yǔ)句T-SQL支持輸出語(yǔ)句,用于輸出顯示處理的數(shù)據(jù)結(jié)果常用的輸出語(yǔ)句有兩種:Print局部變量或字符串SELECT局部變量as自定義列名示例:使用print語(yǔ)句要求單個(gè)局部變量或字符串表達(dá)式作為參數(shù)

print‘當(dāng)前錯(cuò)誤號(hào)’+@@ERROR

全局變量@@ERROR返回的是整數(shù)值。所以必須使用轉(zhuǎn)換函數(shù)，把數(shù)據(jù)轉(zhuǎn)換為字符串，如下所示：Print‘當(dāng)前錯(cuò)誤號(hào)’+convert(varchar(5),@@ERROR)print‘服務(wù)器的名稱：’+@@SERVERNAMESELECT@@SERVERNAMEAS‘服務(wù)器名稱’

演示select和print語(yǔ)句的輸出出錯(cuò)輸出語(yǔ)句示例使用print語(yǔ)句打印2013年所銷(xiāo)售的書(shū)籍總量和總金額-聲明變量

declare@moneydecimaldeclare@countint--賦值

select@count=sum(Quantity),@money=SUM(Price*Discount*Quantity)fromSalesMastera,SalesDetailsbwherea.SalesMasterID=b.SalesMasterIDanddatepart(yy,a.SalesDate)=2013--輸出

print'2013年的銷(xiāo)售總額為:'+@money+'元'print'2013年的銷(xiāo)售總量為:'+@count+'本'轉(zhuǎn)換函數(shù)convert和castCAST語(yǔ)法：

CAST(表達(dá)式AS數(shù)據(jù)類(lèi)型[(長(zhǎng)度)])CONVERT語(yǔ)法：CONVERT(數(shù)據(jù)類(lèi)型[(長(zhǎng)度)],表達(dá)式[,樣式])-print'2013年的銷(xiāo)售總額為:'+convert(varchar(10),@money)+'元'

print'2013年的銷(xiāo)售總量為:'+cast(@countasvarchar(10))+'本'

邏輯控制語(yǔ)句

Transact_SQL提供了一組流程控制語(yǔ)句，包括條件邏輯(IF..ELSE和CASE)循環(huán)（WHILE，經(jīng)常與CONTINUE、BREAK連用）多分支選擇CASE..END等

順序結(jié)構(gòu)BEGIN

語(yǔ)句塊

END注意：如果語(yǔ)句塊只包含單條語(yǔ)句，則BEGIN…END可以省略不寫(xiě)IF-ELSE條件語(yǔ)句當(dāng)條件為真時(shí)，執(zhí)行一條或一組語(yǔ)句語(yǔ)法：IF（條件）

{語(yǔ)句|語(yǔ)句塊}ELSE

{語(yǔ)句|語(yǔ)句塊}IF-ELSE條件語(yǔ)句示例

查詢圖書(shū)銷(xiāo)售信息，如果存在沒(méi)有銷(xiāo)售過(guò)的書(shū)則打印“沒(méi)有銷(xiāo)售過(guò)的書(shū)籍：”并顯示這些書(shū)籍信息，否則打印“所有的書(shū)籍都銷(xiāo)售過(guò)“。ifEXISTS(select*fromBookInfowhereBookIDnotin(selectdistinctBookIDfromSalesDetails))beginprint'沒(méi)有銷(xiāo)售過(guò)的書(shū)籍是:'selectBookName,Author,PublishDate,PricefromBookInfoawhereBookIDnotin(selectdistinctBookIDfromSalesDetails)endelseprint'所有的書(shū)籍都銷(xiāo)售過(guò)'IF-ELSE條件語(yǔ)句示例declare@salesIdintdeclare@quantityintdeclare@saleDatedatetimeselecttop1@salesId=SalesMasterID,@saleDate=SalesDatefromSalesMasterorderbySalesDatedesc

select@quantity=SUM(Quantity)fromSalesDetailswhereSalesMasterID=@salesId

if(@quantity>10)printconvert(varchar(10),@saleDate,112)+'銷(xiāo)售良好,銷(xiāo)售的數(shù)量是'+cast(@quantityasvarchar(10))+'本'elseif(@quantity<5)printcast(@saleDateasvarchar(20))+'銷(xiāo)售一般,銷(xiāo)售的數(shù)量是'+cast(@quantityasvarchar(10))+'本'elseprintcast(@saleDateasvarchar(20))+'銷(xiāo)售差,銷(xiāo)售的數(shù)量是'+cast(@quantityasvarchar(10))+'本'查詢最近一天銷(xiāo)售的最多書(shū)籍信息，如果數(shù)量大于則顯示銷(xiāo)售良好，如果大于顯示銷(xiāo)售一般，否則顯示銷(xiāo)售差。多分支CASE….END1。CASE輸入表達(dá)式

WHENwhen表達(dá)式THEN結(jié)果

[...n][ELSE其他結(jié)果]END2。CASEWHEN布爾表達(dá)式THEN結(jié)果表達(dá)式[...n][ELSE其他結(jié)果]ENDCASE…END示例查詢圖書(shū)銷(xiāo)售信息，在銷(xiāo)售表中根據(jù)折扣的值進(jìn)行處理，如果為1則顯示全價(jià)，如果為0.105顯示九五折，如果為0.10顯示10折，其余都顯示特價(jià)。select書(shū)名=BookName,原價(jià)=a.Price,

銷(xiāo)售價(jià)=b.Discount*b.Price,

銷(xiāo)售數(shù)量=b.Quantity,折扣=casediscountwhen1then'全價(jià)'when0.105then'九五折'when0.10then'九折'else'特價(jià)'endfromBookInfoa,SalesDetailsbwherea.BookID=b.BookIDCASE…END示例

實(shí)現(xiàn)銷(xiāo)售排行榜統(tǒng)計(jì)所有圖書(shū)的銷(xiāo)售量，根據(jù)銷(xiāo)售量分等級(jí)，銷(xiāo)售在50本以上的顯示“優(yōu)秀”，30到50之間的顯示“良好”,10-30之間的顯示“一般”，小于10的顯示“差”，等于0的顯示“沒(méi)有銷(xiāo)售”。select書(shū)名=a.BookName,銷(xiāo)售數(shù)量=ISNULL(SUM(b.quantity),0),

銷(xiāo)售等級(jí)=CASEwhenSUM(b.quantity)>50then'優(yōu)秀'whenSUM(b.quantity)between30and50then'良好'whenSUM(b.quantity)between10and210then'一般'whenSUM(b.quantity)between1and10then'差'else'沒(méi)有銷(xiāo)售'ENDfromBookInfoaleftjoinSalesDetailsbona.BookID=b.BookIDgroupbya.BookNameorderbySUM(b.quantity)descWHILE循環(huán)語(yǔ)句設(shè)置重復(fù)執(zhí)行SQL語(yǔ)句或語(yǔ)句塊的條件。只要指定的條件為真，就重復(fù)執(zhí)行語(yǔ)句。可以使用BREAK和CONTINUE關(guān)鍵字在循環(huán)內(nèi)部控制WHILE循環(huán)中語(yǔ)句的執(zhí)行語(yǔ)法:WHILE（條件）語(yǔ)句或語(yǔ)句塊

[BREAK]WHILE循環(huán)語(yǔ)句示例T-SQL代碼實(shí)現(xiàn)九九乘法表DECLARE@iintDECLARE@jintDECLARE@strvarchar(110)SET@i=1WHILE@i<10BEGINSET@j=1SET@str=''WHILE@j<=@iBEGINSET@str=@str+CAST(@iASvarchar(1))+'x'+CAST(@jASvarchar(1))+'='+CAST(@i*@jASvarchar(2))+''SET@j=@j+1ENDprint@strSET@i=@i+1END批處理客戶端應(yīng)用程序SQLServer服務(wù)器批處理語(yǔ)句：語(yǔ)句1語(yǔ)句2……GO批處理是包含一個(gè)或多個(gè)SQL語(yǔ)句的組，從應(yīng)用程序一次性地發(fā)送到SQLServer執(zhí)行SQLServer將批處理語(yǔ)句編譯成一個(gè)可執(zhí)行單元，此單元稱為執(zhí)行計(jì)劃。執(zhí)行計(jì)劃中的語(yǔ)句每次執(zhí)行一條GO命令GO是SQLServer的批處理命令，只有代碼編輯器才能識(shí)別并處理，編輯其他應(yīng)用程序就不能使用該命令。由于每個(gè)批處理之間是獨(dú)立的，因此，在一個(gè)批處理出現(xiàn)錯(cuò)誤時(shí)，并不會(huì)影響其他批處理中SQL代碼的運(yùn)行。一般是將一些邏輯相關(guān)的業(yè)務(wù)操作語(yǔ)句，放置在同一批中，這完全由業(yè)務(wù)需求和代碼編寫(xiě)者決定。批處理示例創(chuàng)建一個(gè)歷史銷(xiāo)售紀(jì)錄表His_Sales(Id，銷(xiāo)售日期，圖書(shū)ID，書(shū)名，銷(xiāo)售數(shù)量，折扣，價(jià)格，銷(xiāo)售價(jià)格)。將2013年之前的銷(xiāo)售記錄當(dāng)成歷史記錄插入該表。--創(chuàng)建歷史銷(xiāo)售記錄表

createtableHis_Sales(Idintidentity(1,1)primarykey,BookIdchar(5)notnull,BookNamevarchar(20)notnull,SaleDatedatetime,Quantityint,Discountfloat,PriceMoney,SalePriceMoney)go批處理示例--插入歷史記錄

insertintoSalesselectb.BookID,c.BookName,a.SalesDate,b.Quantity,b.Di