第十一章SQLServer中的程序設(shè)計(jì)1主要內(nèi)容批處理的基本概念事務(wù)處理SQLServer的流程控制語(yǔ)句211.1批處理的基本概念系統(tǒng)將批處理語(yǔ)句作為整體編譯成一個(gè)可執(zhí)行單元，即執(zhí)行計(jì)劃包含一個(gè)或多個(gè)T-SQL語(yǔ)句的組批處理這些語(yǔ)句被應(yīng)用程序一次性地發(fā)送到SQLServer執(zhí)行3（1）應(yīng)用程序作為一個(gè)執(zhí)行單元發(fā)出的所有SQL語(yǔ)句構(gòu)成一個(gè)批處理，并生成單個(gè)執(zhí)行計(jì)劃（2）存儲(chǔ)過(guò)程或觸發(fā)器內(nèi)的所有語(yǔ)句構(gòu)成一個(gè)批處理。每個(gè)存儲(chǔ)過(guò)程或觸發(fā)器都編譯為一個(gè)執(zhí)行計(jì)劃（3）由EXECUTE語(yǔ)句執(zhí)行的字符串指定批處理的方法4例：在Northwind庫(kù)中創(chuàng)建testview視圖，并查詢視圖中存在的數(shù)據(jù)，該視圖從表Products中提取各產(chǎn)品的名稱和價(jià)格數(shù)據(jù)5USENorthwindgoCREATEVIEWtestview

ASSELECT

ProductName,UnitPriceFROM[Products]goSELECT*FROMtestviewgo6批處理結(jié)束的符號(hào)或標(biāo)志是字符串’go’。批處理可以交互地運(yùn)行或從一個(gè)文件中運(yùn)行。提交給T-SQL的文件可以包含多個(gè)SQL批處理，每個(gè)批處理之間以批處理分隔符go命令中止7例：在pubs庫(kù)中列出authors表中au_id字段和au_fname字段，其中要求au_id字段以7開(kāi)頭

USEpubsSELECT

au_id,au_fname

FROMauthorsWHEREau_idLIKE'7%'GO8USE

NorthwindgoCREATE

VIEWmy_viewASSELECT

LastName,FirstName

FROMEmployeesINSERT

INTOmy_view

VALUES('John','David')go無(wú)效的批處理，insert語(yǔ)言前加go就可以了9UseNorthwindGoCREATETABLEmy_table(c1smallint,C2varchar(5))goALTERTABLEADDc3intINSERTINTOmy_table(3,’love’,255)go10問(wèn)題什么時(shí)候用批處理？1111.2事務(wù)處理SQLServer把單個(gè)工作單元定義為事務(wù)任何單個(gè)SQL語(yǔ)句都可看成一個(gè)單個(gè)的工作單元，在SQLServer中，事務(wù)處理可以通過(guò)一段SQL程序來(lái)完成12事務(wù)的由來(lái)DELETEFROMdepartmentWHEREdepartment_id=‘D004’DELETEFROMemployeeWHEREdepartment_id=‘D004’13事務(wù)處理語(yǔ)句包含在如下兩行語(yǔ)句當(dāng)中BEGINTRANSACTION[transaction_name]……COMMITTRANSACTION[transaction_name]

14三種事務(wù)運(yùn)行模式自動(dòng)提交事務(wù)：每條單獨(dú)的語(yǔ)句都是一個(gè)事務(wù)顯式事務(wù)：以BEGIN

TRANSACTION

語(yǔ)句開(kāi)始，以COMMIT或ROLLBACK語(yǔ)句結(jié)束的事務(wù);用戶定義的事務(wù)也稱為顯式事務(wù)15隱性事務(wù)：在前一事務(wù)完成時(shí)新事務(wù)隱式啟動(dòng)，但每個(gè)事務(wù)仍以COMMIT或ROLLBACK語(yǔ)句完成16例：通過(guò)事務(wù)來(lái)保證Northwind庫(kù)中表Products和表Order

Details上的所有ProdcutID為40的產(chǎn)品其單價(jià)翻番BEGIN

TRANSACTIONmy_transaction --開(kāi)始一個(gè)事務(wù)UPDATE[Products] --更新Products表SET

UnitPrice=2*UnitPrice

WHERE

ProductID=40

17SAVE

TRANSACTION

my_savepoint --保存能回滾的點(diǎn)UPDATE[Order

Details] --更新OrderDetails表SETUnitPrice=2*UnitPrice WHEREProductID=40

COMMIT

TRANSACTION18例：創(chuàng)建一個(gè)在COMMIT

TRANSACTION語(yǔ)句之前帶有ROLLBACK

TRANSACTION子句的事務(wù)my_transaction。該子句將撤銷從SAVE

TRANSACTION

my_savepoint到ROLLBACK

TRANSACTION

my_savepoint之間的語(yǔ)句對(duì)數(shù)據(jù)庫(kù)所作的任何修改19BEGINTRANSACTIONmy_transaction

UPDATE[Products]

SETUnitPrice=2*UnitPrice

WHEREProductID=40SAVETRANSACTIONmy_savepoint

UPDATE[OrderDetails]SETUnitPrice=2*UnitPrice

WHEREProductID=40ROLLBACKTRANSACTIONmy_savepointCOMMITTRANSACTION由于執(zhí)行了回滾操作，表OrderDetails中的修改沒(méi)發(fā)生20當(dāng)多個(gè)用戶同時(shí)訪問(wèn)數(shù)據(jù)庫(kù)時(shí)，系統(tǒng)通過(guò)鎖來(lái)防止它們的活動(dòng)不互相干擾鎖可以防止用戶訪問(wèn)其他用戶正在修改的數(shù)據(jù)在一個(gè)事務(wù)中，鎖保存在被讀取/修改的表上，從而防止多個(gè)事務(wù)并發(fā)使用資源造成問(wèn)題鎖21將鎖最小化(如完成事務(wù)后，立即提交)可以提高并發(fā)性和提高數(shù)據(jù)庫(kù)的性能22基本鎖專用鎖鎖讀操作需要的共享鎖寫操作需要的獨(dú)占鎖更新鎖意向鎖模式鎖批量更新鎖23共享鎖：常用于只讀操作，如SELECT語(yǔ)句專用鎖：確保不會(huì)同時(shí)對(duì)同一資源進(jìn)行多重更新，常用于對(duì)數(shù)據(jù)修改操作如INSERT、UPDATE或DELETE

24更新鎖：用于可更新的資源中，以防當(dāng)多個(gè)會(huì)話在讀取、鎖定及資源更新時(shí)可能發(fā)生的死鎖意向鎖：常用于建立鎖的層次結(jié)構(gòu)，主要類型有意向共享、意向排它和與意向排它共享25模式鎖：常在執(zhí)行依賴于表架構(gòu)的操作時(shí)使用批量更新鎖：向表中大容量復(fù)制數(shù)據(jù)并指定了TABLOCK提示時(shí)使用26例：使SQLServer為事務(wù)SELECTcount(*)fromProducts保持共享鎖直到該事務(wù)完成，該選項(xiàng)不確定共享鎖是表鎖或頁(yè)鎖

SELECTcount(*)fromProducts(tablock

holdlock)27關(guān)于表的鎖定選項(xiàng)：HOLDLOCK：將共享鎖保留到事務(wù)完成，而不是在相應(yīng)的表、行或數(shù)據(jù)頁(yè)不再需要時(shí)就立即釋放鎖NOLOCK：不發(fā)出共享鎖，不提供排它鎖。利用此選項(xiàng)可能會(huì)讀取未提交的事務(wù)或一組在讀取中回滾的頁(yè)面,它僅應(yīng)用于SELECT語(yǔ)句28PAGLOCK：在通常使用單個(gè)表鎖的地方采用頁(yè)鎖TABLOCK：使用表鎖代替行級(jí)鎖或頁(yè)級(jí)鎖，若同時(shí)指定HOLDLOCK，則在事務(wù)結(jié)束之前,鎖將一直保持TABLOCKX：使用表的排它鎖，該鎖可防止其它事務(wù)讀取或更新表，并在語(yǔ)句或事務(wù)結(jié)束前一直保持29UPDLOCK:讀取表時(shí)使用更新鎖，而不使用共享鎖，并將鎖一直保留到語(yǔ)句或事務(wù)的結(jié)束。UPDLOCK

的優(yōu)點(diǎn)是允許用戶讀取數(shù)據(jù)并在以后更新數(shù)據(jù)，同時(shí)確保自從上次讀取數(shù)據(jù)后數(shù)據(jù)沒(méi)有被更改XLOCK:使用排它鎖并一直保持到由語(yǔ)句處理的所有數(shù)據(jù)上的事務(wù)結(jié)束30事務(wù)隔離級(jí)別允許用戶為整個(gè)SQLServer設(shè)置會(huì)話級(jí)獨(dú)立級(jí)別，可用SETTRANSACTION

ISOLATION

LEVEL語(yǔ)句來(lái)設(shè)置該級(jí)別，當(dāng)用戶設(shè)置獨(dú)立級(jí)別時(shí)，用戶是為SQL

Server會(huì)話中所有的SELECT語(yǔ)句設(shè)置了缺省鎖行為。在指定了獨(dú)立級(jí)別后，可以在單個(gè)的SELECT語(yǔ)句中與它一起使用鎖指定而重載它。另外，可以用DBCC

USEROPTIONS來(lái)查看了什么級(jí)別設(shè)置3111.3SQL

Server的流程控制語(yǔ)句通過(guò)流程控制語(yǔ)句以控制程序的流程，允許語(yǔ)句彼此相關(guān)及相互依賴可用于單個(gè)的SQL語(yǔ)句、語(yǔ)句塊和存儲(chǔ)過(guò)程的執(zhí)行32常用的流程控制語(yǔ)言DECLARE RETURNRAISERROR PRINTCASE WHILE BEGIN…END IF…ELSEBREAK CONTINUE3311.3.1聲明變量用DECLARE可以聲明變量用SELECT語(yǔ)句可以給變量賦值

DECLARE@variable_namedatatype[,@variable_namedatatype…]

SELECT@variable_namevalue[,@variable_namevalue…]34例：用一個(gè)變量存放表名，并將該變量傳遞給sp_spaceused系統(tǒng)存儲(chǔ)過(guò)程，以查詢表空間的使用情況。DECLARE@nextchar(30)SELECT@next=''

WHILE@nextisnotnullBEGINSELECT@next=min(name)FROM

sysobjects

WHEREtype='U'ANDname>@nextEXECsp_spaceused@nextENDGo從sysobjects中提取表名，按升序?qū)⒏鞅砻x給變量@next

3511.3.2預(yù)聲明的全局變量許多預(yù)聲明的全局變量用來(lái)報(bào)告最近一次SQL

Server啟動(dòng)以來(lái)的系統(tǒng)活動(dòng)情況以及連接信息預(yù)聲明的全局變量的特征是有兩個(gè)@@放在變量名前36例：執(zhí)行sp_monitor系統(tǒng)存儲(chǔ)過(guò)程來(lái)獲預(yù)聲明的全局變量的信息,以顯示有關(guān)SQLServer

繁忙程度

EXECsp_monitorgo373811.3.3RETURN

語(yǔ)句

RETURN語(yǔ)句實(shí)現(xiàn)從一個(gè)查詢或過(guò)程中無(wú)條件退出向調(diào)用它的過(guò)程或應(yīng)用程序返回一個(gè)整數(shù)-1~-99：代表不同的出錯(cuò)原因0：成功返回；-1：對(duì)象丟失-2：發(fā)生數(shù)據(jù)類型錯(cuò)誤39例：創(chuàng)建了一個(gè)用戶存儲(chǔ)過(guò)程，根據(jù)輸入的參數(shù)值返回不同的用戶自定義的返回值CREATE

procmy_proc@input_yearintASIF(@input_year<=2000)RETURN1ELSERETURN240若要取得存儲(chǔ)過(guò)程的返回值，可以通過(guò)EXEC@return_status=過(guò)程名，如獲得存儲(chǔ)過(guò)程my_proc的返回值，命令如下：DECLARE@return_statusint

EXEC@return_status=my_proc1996If@return_status=1Print‘Theinputyearisbefore2000’If@return_status=2Print‘Theinputyearisafter2000’4111.3.4CASE表達(dá)式CASE用于計(jì)算多個(gè)條件并為每個(gè)條件返回單個(gè)值CASE函數(shù)的用法：

CASEexpression

WHENexpression1THENexpression1

[[WHENexpression2THENexpression2][…]][ELSEexpressionN]END42例：CASE在SELECT中的應(yīng)用。從數(shù)據(jù)庫(kù)Northwind中的表Products上檢索其單價(jià)和產(chǎn)品名稱，并按單價(jià)的大小依次返回不同的字符串值43SELECT

ProductName,CASEWHEN

UnitPriceISNULLTHEN'notpriced'WHEN

UnitPrice<10THEN'pricelow'WHEN

UnitPrice>=10andUnitPrice<100

THEN'pricenormal'ELSE'priceishigh'ENDAS'PriceCategory‘FROM

Northwind.dbo.Products

ORDERBY

UnitPrice

4411.3.5BEGINEND塊

用BEGINEND

括起來(lái)的一系列的語(yǔ)句稱作語(yǔ)句塊，BEGINEND語(yǔ)句塊可以嵌套45Begin{sql_statement|statement_block}end46例：有一成績(jī)表stud_score，記錄學(xué)生的計(jì)算機(jī)課成績(jī)comp_score和學(xué)號(hào)studid。表中現(xiàn)有數(shù)據(jù)如下： stud_id comp_score ----------- --------------- 970800 90 970810 70 970820 80

47現(xiàn)要求對(duì)comp_score進(jìn)行調(diào)節(jié)。直到最高的comp_score<75為止。然后判斷最低的comp_score是否小于60，如果小于60則顯示‘theminimuisnowlessthan60’后，退出循環(huán)。否則就顯示“Nocomp_scoreislessthan60”后結(jié)束循環(huán)48usemy_DB1WHILE(SELECTavg(comp_score)FROMstud_score)>60BEGINUPDATEstud_score

SETcomp_score=(comp_score*0.95)

IF(SELECTmax(comp_score)FROMstud_score)>75

BEGINPRINT'SomeStudentsisstillmorethan75'CONTINUEEND49SELECT'themaxiumcomp_scorearenow:',max(comp_score)FROMstud_score

IF(SELECTmin(comp_score)FROMstud_score)<60

BEGINPRINT'Theminimumisnowlessthan60'50

PRINT'***Existingwithoutrecheckingaverage***'BREAKENDPRINT'***Nocomp_scoreislessthan60.****'ENDSELECT*FROMstud_score515211.3.6游標(biāo)游標(biāo)允許對(duì)給定的結(jié)果集或SELECT語(yǔ)句生成的結(jié)果集或?qū)φ麄€(gè)結(jié)果集進(jìn)行操作，也可對(duì)結(jié)果集逐行進(jìn)行多個(gè)操作。游標(biāo)通常提供滾動(dòng)的能力，允許檢索任意一行53系統(tǒng)提供兩種基于服務(wù)器的游標(biāo)實(shí)現(xiàn)只運(yùn)行單行處理的游標(biāo)，每次提取(fetch)只從結(jié)果集中返回一行基于過(guò)程的引擎游標(biāo)，由DB_Library和ODBC游標(biāo)應(yīng)用程序接口使用54

語(yǔ)句 描述DECLARE 創(chuàng)建和定義游標(biāo)OPEN 打開(kāi)一個(gè)聲明的游標(biāo)FETCH 從游標(biāo)中檢索行CLOSE 關(guān)閉游標(biāo)DEALLOCATE

刪除游標(biāo)定義ANSI游標(biāo)55允許所有提取方法(1)DECLARE語(yǔ)句的語(yǔ)法

DECLAREcursor_name[INSENSITIVE][SCROLL]CURSORFORselect_statement[FOR{READONLY|UPDATE[OFcolumn_list]}]只允許對(duì)結(jié)果集中的下一行進(jìn)行操作

56(2)OPEN語(yǔ)句的用法打開(kāi)T-SQL

服務(wù)器游標(biāo)，然后通過(guò)執(zhí)行在DECLARE

CURSOR或SET

cursor_variable

語(yǔ)句中指定的T-SQL語(yǔ)句填充游標(biāo)57(3)

FETCH語(yǔ)句的用法FETCH[[NEXT|PRIOR|FIRST|LAST|ABSOLUTEn|RELATIVEn]FROM]cursor_name[INTO@variable_name1,@variable_name2…]58(4)

CLOSE語(yǔ)句的用法

CLOSE語(yǔ)句用于關(guān)閉游標(biāo)并釋放所有用于緩沖的內(nèi)存59(5)DEALLOCATE語(yǔ)句刪除游標(biāo)定義并釋放與該游標(biāo)關(guān)聯(lián)的所有數(shù)據(jù)結(jié)構(gòu)60例：程序設(shè)計(jì)中使用游標(biāo)的例子CREATEprocmy_procASSETnocountONSELECT'***Database:'+db_name()+'****'DECLARE@tablenamechar(30)