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

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

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

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

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

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

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

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

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

BookInfoWHEREPrice<0

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

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

print‘當(dāng)前錯誤號’+@@ERROR

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

演示select和print語句的輸出出錯輸出語句示例使用print語句打印2013年所銷售的書籍總量和總金額-聲明變量

declare@moneydecimaldeclare@countint--賦值

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

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

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

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

邏輯控制語句

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

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

語句塊

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

{語句|語句塊}ELSE

{語句|語句塊}IF-ELSE條件語句示例

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

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

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

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

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

銷售價=b.Discount*b.Price,

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

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

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

[BREAK]WHILE循環(huán)語句示例T-SQL代碼實現(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ù)器批處理語句：語句1語句2……GO批處理是包含一個或多個SQL語句的組，從應(yīng)用程序一次性地發(fā)送到SQLServer執(zhí)行SQLServer將批處理語句編譯成一個可執(zhí)行單元，此單元稱為執(zhí)行計劃。執(zhí)行計劃中的語句每次執(zhí)行一條GO命令GO是SQLServer的批處理命令，只有代碼編輯器才能識別并處理，編輯其他應(yīng)用程序就不能使用該命令。由于每個批處理之間是獨立的，因此，在一個批處理出現(xiàn)錯誤時，并不會影響其他批處理中SQL代碼的運行。一般是將一些邏輯相關(guān)的業(yè)務(wù)操作語句，放置在同一批中，這完全由業(yè)務(wù)需求和代碼編寫者決定。批處理示例創(chuàng)建一個歷史銷售紀(jì)錄表His_Sales(Id，銷售日期，圖書ID，書名，銷售數(shù)量，折扣，價格，銷售價格)。將2013年之前的銷售記錄當(dāng)成歷史記錄插入該表。--創(chuàng)建歷史銷售記錄表

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