數(shù)據(jù)庫原理與應(yīng)用(10)第10章 SQL高級應(yīng)用

第10章SQL高級應(yīng)用10.1SELECT高級查詢10.1.1數(shù)據(jù)匯總為決策支持系統(tǒng)生成聚合事務(wù)的匯總報(bào)表是一項(xiàng)復(fù)雜并且相當(dāng)消耗資源的工作。SQLServer2005提供兩個(gè)靈活且強(qiáng)大的工具，即SQLServer2005分析服務(wù)和報(bào)表服務(wù)。但是對于生成簡單匯總報(bào)表的應(yīng)用程序，可使用以下運(yùn)算符：CUBE或ROLLUP運(yùn)算符。均為GROUPBY子句的一部分。COMPUTE或COMPUTEBY運(yùn)算符。均與GROUPBY子句相關(guān)聯(lián)。1．聚合函數(shù)聚合函數(shù)用來完成一定的統(tǒng)計(jì)功能，對一組值執(zhí)行計(jì)算并返回單一的值。除COUNT函數(shù)之外，聚合函數(shù)忽略空值。聚合函數(shù)經(jīng)常與SELECT語句的GROUPBY子句一同使用。所有聚合函數(shù)都具有確定性。任何時(shí)候用一組給定的輸入值調(diào)用它們時(shí)，都返回相同的值。聚合函數(shù)僅用于以下子句中：SELECT子句（子查詢或外部查詢）。COMPUTE或COMPUTEBY子句。HAVING子句。

COMPUTEBY子句可以用同一SELECT語句既查看明細(xì)行，又查看匯總行?？梢杂?jì)算子組的匯總值，也可以計(jì)算整個(gè)結(jié)果集的匯總值。COMPUTE子句需要以下信息：可選的BY關(guān)鍵字。該關(guān)鍵字可按對一列計(jì)算指定的行聚合。行聚合函數(shù)名稱。例如，SUM、AVG、MIN、MAX或COUNT。要對其執(zhí)行行聚合函數(shù)的列。（1）COMPUTE生成的結(jié)果集

COMPUTE所生成的匯總值在查詢結(jié)果中顯示為分離的結(jié)果集。包括COMPUTE子句的查詢的結(jié)果類似于控制中斷報(bào)表，即匯總值由指定的組來控制的報(bào)表?？梢詾楦鹘M生成匯總值，也可以對同一組計(jì)算多個(gè)聚合函數(shù)。當(dāng)COMPUTE帶有可選的BY子句時(shí)，符合SELECT條件的每個(gè)組都有兩個(gè)結(jié)果集：每個(gè)組的第一個(gè)結(jié)果集是明細(xì)行集，其中包含該組的選擇列表信息。每個(gè)組的第二個(gè)結(jié)果集有一行，其中包含該組的COMPUTE子句中所指定的聚合函數(shù)的小計(jì)。當(dāng)COMPUTE不帶可選的BY子句時(shí)，SELECT語句有兩個(gè)結(jié)果集：每個(gè)組的第一個(gè)結(jié)果集是包含選擇列表信息的所有明細(xì)行。第二個(gè)結(jié)果集有一行，其中包含COMPUTE子句中所指定的聚合函數(shù)的合計(jì)?！纠?0.1】

給出以下程序的執(zhí)行結(jié)果。USEschoolSELECT學(xué)號,課程號,分?jǐn)?shù)FROMscoreWHERE學(xué)號IN(103,105)ORDERBY學(xué)號COMPUTESUM(分?jǐn)?shù))

解：該程序中SELECT語句使用簡單COMPUTE子句生成score表中分?jǐn)?shù)列的求和總計(jì)。執(zhí)行結(jié)果如下圖所示?！纠?0.2】

給出以下程序的執(zhí)行結(jié)果。USEschoolSELECT學(xué)號,課程號,分?jǐn)?shù)FROMscoreWHERE學(xué)號IN(103,105)ORDERBY學(xué)號COMPUTESUM(分?jǐn)?shù))BY學(xué)號解：該程序中的查詢在COMPUTE子句中加入可選的BY關(guān)鍵字，以生成每個(gè)組的小計(jì)。執(zhí)行結(jié)果如下圖所示。（2）比較COMPUTE和GROUPBY的功能COMPUTE和GROUPBY之間的區(qū)別匯總?cè)缦拢篏ROUPBY生成單個(gè)結(jié)果集。每個(gè)組都有一個(gè)只包含分組依據(jù)列和顯示該組子聚合的聚合函數(shù)的行。選擇列表只能包含分組依據(jù)列和聚合函數(shù)。COMPUTE生成多個(gè)結(jié)果集。一類結(jié)果集包含每個(gè)組的明細(xì)行，其中包含選擇列表中的表達(dá)式。另一類結(jié)果集包含組的子聚合，或SELECT語句的總聚合。選擇列表可包含除分組依據(jù)列或聚合函數(shù)之外的其他表達(dá)式。聚合函數(shù)在COMPUTE子句中指定，而不是在選擇列表中。2．GROUPBY子句GROUPBY子句的語法格式為：

[GROUPBY[ALL]分組表達(dá)式[,…n]

[WITH{CUBE|ROLLUP}]

]ALL。包含所有組和結(jié)果集，甚至包含那些任何行都不滿足WHERE子句指定的搜索條件的組和結(jié)果集。如果指定了ALL，將對組中不滿足搜索條件的匯總列返回空值。CUBE。指定在結(jié)果集內(nèi)，不僅包含由GROUPBY提供的正常行，還包含匯總行。在結(jié)果集內(nèi)返回每個(gè)可能的組和子組組合的GROUPBY匯總行。GROUPBY匯總行在結(jié)果中顯示為NULL，但可用來表示所有值。ROLLUP。指定在結(jié)果集內(nèi)不僅包含由GROUPBY提供的正常行，還包含匯總行。按層次結(jié)構(gòu)順序，從組內(nèi)的最低級別到最高級別匯總組。【例10.3】

給出以下程序的執(zhí)行結(jié)果。USEschoolSELECTstudent.班號,course.課程名,AVG(score.分?jǐn)?shù))AS'平均分'FROMstudent,course,score圖10.3程序執(zhí)行結(jié)果WHEREstudent.學(xué)號=score.學(xué)號ANDcourse.課程號=score.課程號GROUPBYstudent.班號,course.課程名WITHCUBE帶ROLLUP參數(shù)會依據(jù)GROUPBY后面所列第一個(gè)字段做匯總運(yùn)算。

【例10.4】

給出以下程序的執(zhí)行結(jié)果。圖10.4程序執(zhí)行結(jié)果

USEschool

SELECTstudent.班號,AVG(score.分?jǐn)?shù))AS'平均分'

FROMstudent,course,score

WHEREstudent.學(xué)號=score.學(xué)號

GROUPBYstudent.班號WITHROLLUP10.1.2復(fù)雜連接查詢通過連接，可以根據(jù)各個(gè)表之間的邏輯關(guān)系從兩個(gè)或多個(gè)表中檢索數(shù)據(jù)。連接表示SQLServer2005應(yīng)如何使用一個(gè)表中的數(shù)據(jù)來選擇另一個(gè)表中的行。可在FROM或WHERE子句中指定連接。連接條件與WHERE和HAVING搜索條件組合，用于控制FROM子句引用的基表中所選定的行。在FROM子句中指定連接條件，有助于將這些連接條件與WHERE子句中可能指定的其他搜索條件分開，指定連接時(shí)建議使用這種方法。簡單的子句連接語法如下：

FROM第一個(gè)表名連接類型第二個(gè)表名[ON(連接條件)]其中，連接類型有內(nèi)連接、外連接或交叉連接?！纠?0.5】

給出以下程序的執(zhí)行結(jié)果。USEschoolSELECTallocate.班號,allocate.課程號,teacher.姓名FROMallocateJOINteacherON(allocate.教師編號=teacher.編號)ORDERBYallocate.班號1．內(nèi)連接內(nèi)連接是用比較運(yùn)算符比較要連接列的值的連接。在SQL-92標(biāo)準(zhǔn)中，內(nèi)連接可在FROM或WHERE子句中指定。這是WHERE子句中唯一一種SQL-92支持的連接類型。WHERE子句中指定的內(nèi)連接稱為舊式內(nèi)連接。內(nèi)連接使用INNERJOIN關(guān)鍵詞，上面查詢各課程的任課教師姓名的例子就是一個(gè)內(nèi)連接的例子，也可以按下面方式查詢：

USEschool

SELECTallocate.班號,allocate.課程號,teacher.姓名

FROMallocateINNERJOINteacherON(allocate.教師編號=teacher.編號)

ORDERBYallocate.班號2．外連接僅當(dāng)至少有一個(gè)同屬于兩表的行符合連接條件時(shí)，內(nèi)連接才返回行。內(nèi)連接消除與另一個(gè)表中的任何行不匹配的行。而外連接會返回FROM子句中提到的至少一個(gè)表或視圖的所有行，只要這些行符合任何WHERE或HAVING搜索條件。將檢索通過左外連接引用的左表的所有行，以及通過右外連接引用的右表的所有行。全外連接中兩個(gè)表的所有行都將返回。

SQLServer2005對在FROM子句中指定的外連接使用以下關(guān)鍵字：LEFTOUTERJOIN或LEFTJOIN（左外連接）RIGHTOUTERJOIN或RIGHTJOIN（右外連接）FULLOUTERJOIN或FULLJOIN（全外連接）（1）左外連接左外連接簡稱為左連接，其結(jié)果包括第一個(gè)命名表（“左”表，出現(xiàn)在JOIN子句的最左邊）中的所有行，不包括右表中的不匹配行?！纠?0.6】

給出以下程序的執(zhí)行結(jié)果。USEschoolINSERTINTOallocate(班號,課程號)VALUES('1031','9-888')SELECTallocate.班號,allocate.課程號,teacher.姓名FROMallocateLEFTJOINteacherON(allocate.教師編號=teacher.編號)ORDERBYallocate.班號DELETEallocateWHERE班號='1031'AND課程號='9-888'（2）右外連接右外連接簡稱為右連接，其結(jié)果中包括第二個(gè)命名表（“右”表，出現(xiàn)在JOIN子句的最右邊）中的所有行，不包括左表中的不匹配行。【例10.7】

給出以下程序的執(zhí)行結(jié)果。USEschoolINSERTINTOallocate(班號,課程號)VALUES('1031','9-888')SELECTallocate.班號,allocate.課程號,teacher.姓名FROMallocateRIGHTJOINteacherON(allocate.教師編號=teacher.編號)

ORDERBYallocate.班號DELETEallocateWHERE班號='1031'AND課程號='9-888'（3）全外連接若要通過在連接結(jié)果中包括不匹配的行保留不匹配信息，可以使用全外連接。SQLServer2005提供全外連接運(yùn)算符FULLOUTERJOIN，不管另一個(gè)表是否有匹配的值，此運(yùn)算符都包括兩個(gè)表中的所有行?！纠?0.8】

給出以下程序的執(zhí)行結(jié)果。USEschoolINSERTINTOallocate(班號,課程號)VALUES('1031','9-888')SELECTallocate.班號,allocate.課程號,teacher.姓名FROMallocateFULLOUTERJOINteacherON(allocate.教師編號=teacher.編號)ORDERBYallocate.班號DELETEallocateWHERE班號='1031'AND課程號='9-888'3．交叉連接在這類連接的結(jié)果集內(nèi)，兩個(gè)表中每兩個(gè)可能成對的行占一行。交叉連接不使用WHERE子句。在數(shù)學(xué)上，就是表的笛卡爾積。第一個(gè)表的行數(shù)乘以第二個(gè)表的行數(shù)等于笛卡爾積結(jié)果集的大小?！纠?0.9】

給出以下程序的執(zhí)行結(jié)果。USEschoolSELECTcourse.課程名,teacher.姓名FROMcourseCROSSJOINteacher結(jié)果沒有實(shí)際意義。10.1.3復(fù)雜子查詢子查詢能夠?qū)⒈容^復(fù)雜的查詢分解為幾個(gè)簡單的查詢，而且子查詢可以嵌套。1．子查詢規(guī)則嵌套在外部SELECT語句中的子查詢可以包括以下子句：包含標(biāo)準(zhǔn)選擇列表組件的標(biāo)準(zhǔn)SELECT查詢。包含一個(gè)或多個(gè)表或者視圖名的標(biāo)準(zhǔn)FROM子句?？蛇x的WHERE子句?？蛇x的GROUPBY子句?？蛇x的HAVING子句。在SQLServer2005中，子查詢還要受下面的條件限制：通過比較運(yùn)算符引入的子查詢的選擇列表只能包括一個(gè)表達(dá)式或列名稱（分別對SELECT*或列表進(jìn)行EXISTS和IN操作除外）。如果外部查詢的WHERE子句包括某個(gè)列名，則該子句必須與子查詢選擇列表中的該列在連接上兼容。子查詢的選擇列表中不允許出現(xiàn)ntext、text和image數(shù)據(jù)類型。由于不修改數(shù)據(jù)的比較運(yùn)算符（指其后未接關(guān)鍵字IN、ANY或ALL等）的引入，這類子查詢必須返回單個(gè)值，而且子查詢中不能包括GROUPBY和HAVING子句。包括GROUPBY的子查詢不能使用DISTINCT關(guān)鍵字。不能指定COMPUTE和INTO子句。TOP子句用于指定要返回的記錄個(gè)數(shù)，如SELECTTOP3或30PERCENTFROMstudent表示顯示前3個(gè)記錄或者滿足條件的30％的記錄。只有同時(shí)指定了TOP，才可以指定ORDERBY。由子查詢創(chuàng)建的視圖不能更新。按約定，通過EXISTS引入的子查詢的選擇列表由星號（*）組成，而不使用單個(gè)列名。由于通過EXISTS引入的子查詢進(jìn)行了存在測試，并返回TRUE或FALSE而非數(shù)據(jù)，所以這些子查詢的規(guī)則與標(biāo)準(zhǔn)選擇列表的規(guī)則完全相同。2．子查詢類型有如下3種常用的子查詢類型：在通過IN引入的列表或者由ANY或ALL修改的比較運(yùn)算符的列表上進(jìn)行操作。通過不修改數(shù)據(jù)的比較運(yùn)算符（指其后未接關(guān)鍵字IN、ANY或ALL等）引入，并且必須返回單個(gè)值。通過EXISTS引入的存在測試。上述3種子查詢通常采用的格式有下面幾種：WHERE表達(dá)式[NOT]IN(子查詢)WHERE表達(dá)式比較運(yùn)算符[ANY|ALL](子查詢)WHERE[NOT]EXISTS(子查詢)（1）使用IN或NOTIN通過IN（或NOTIN）引入的子查詢結(jié)果是沒有值或多個(gè)值。子查詢返回結(jié)果之后，外部查詢將利用這些結(jié)果?！纠?0.10】

給出以下程序的執(zhí)行結(jié)果。USEschoolSELECTstudent.學(xué)號,student.姓名FROMstudentWHEREstudent.學(xué)號IN (SELECTscore.學(xué)號FROMscore WHEREscore.課程號='6-166')執(zhí)行結(jié)果USEschoolSELECTstudent.學(xué)號,student.姓名FROMstudentWHEREstudent.學(xué)號NOTIN (SELECTscore.學(xué)號FROMscore WHEREscore.課程號='6-166')執(zhí)行結(jié)果（2）使用ANY或ALL

ANY或ALL通常與關(guān)系運(yùn)算符連用，如>ANY(子查詢)表示大于任意子查詢的結(jié)果。

【例10.12】

給出功能為“查詢其平均分高于所有課程平均分的學(xué)生學(xué)號和它們的平均分”的程序及其執(zhí)行結(jié)果。圖10.13程序執(zhí)行結(jié)果

解：對應(yīng)的程序如下：USEschoolSELECT學(xué)號,AVG(分?jǐn)?shù))AS'平均分'FROMscoreGROUPBY學(xué)號HAVINGAVG(分?jǐn)?shù))>ALL (SELECTAVG(分?jǐn)?shù)) FROMscore WHERE分?jǐn)?shù)ISNOTNULL )（3）使用EXISTS在子查詢中，還可以使用EXISTS，它一般用在WHERE子句中，其后緊跟一個(gè)子查詢，從而構(gòu)成一個(gè)條件，當(dāng)該子查詢至少存在一個(gè)返回值時(shí)，這個(gè)條件為真，否則為假。

【例10.13】

給出功能為“查詢所有任課教師的姓名和單位”的程序及其執(zhí)行結(jié)果。

解：對應(yīng)的程序如下：USEschoolSELECT姓名,單位FROMteacheraWHEREEXISTS (SELECT* FROMallocateb WHEREa.編號=b.教師編號)

【例10.14】

給出功能為“查詢所有未講課的教師的姓名和單位”的程序及其執(zhí)行結(jié)果。

解：對應(yīng)的程序如下：

USEschool

SELECT姓名,單位

FROMteachera

WHERENOTEXISTS

(SELECT*

FROMallocateb

WHEREa.編號=b.教師編號)3．多層嵌套子查詢可以嵌套在外部SELECT、INSERT、UPDATE或DELETE語句的WHERE或HAVING子句內(nèi)，或者其他子查詢中。盡管根據(jù)可用內(nèi)存和查詢中其他表達(dá)式的復(fù)雜程度不同，嵌套限制也有所不同，但一般均可以嵌套到32層?！纠?0.15】

給出以下程序的執(zhí)行結(jié)果。USEschoolSELECT姓名,班號FROMstudentWHERE學(xué)號= (SELECT學(xué)號

FROMscore WHERE分?jǐn)?shù)= (SELECTMAX(分?jǐn)?shù)) FROMscore) )10.1.4數(shù)據(jù)來源是一個(gè)查詢的結(jié)果在查詢語句中，F(xiàn)ROM指定數(shù)據(jù)來源，它可以是一個(gè)或多個(gè)表。實(shí)際上，由FROM指定的數(shù)據(jù)來源也可以是一個(gè)SELECT查詢的結(jié)果?！纠?0.16】

給出以下程序的執(zhí)行結(jié)果。USEschoolSELECT課程號,avgsAS'平均分'FROM(SELECT課程號,AVG(分?jǐn)?shù))avgs

FROMscore GROUPBY課程號)TORDERBYavgsDESC【例10.17】

給出以下程序的執(zhí)行結(jié)果。USEschoolSELECT班號,學(xué)號,姓名,MAX(分?jǐn)?shù))最分?jǐn)?shù)FROM(SELECTs.學(xué)號,s.姓名,s.班號,c.課程名,sc.分?jǐn)?shù)

FROMstudents,course

c,scorescWHEREs.學(xué)號=sc.學(xué)號ANDc.課程號=sc.課程號AND分?jǐn)?shù)ISNOTNULL)TGROUPBY班號,學(xué)號,姓名ORDERBY班號,學(xué)號10.2事務(wù)處理事務(wù)是SQLServer中的單個(gè)邏輯單元，一個(gè)事務(wù)內(nèi)的所有SQL語句作為一個(gè)整體執(zhí)行，要么全部執(zhí)行，要么都不執(zhí)行。一個(gè)邏輯工作單元必須有四個(gè)特性，稱為ACID（原子性、一致性、隔離性和持久性）屬性，只有這樣才能成為一個(gè)事務(wù)。10.2.1事務(wù)分類按事務(wù)的啟動和執(zhí)行方式，可以將事務(wù)分為三類：顯式事務(wù)。也稱為用戶定義或用戶指定的事務(wù)，即可以顯式地定義啟動和結(jié)束的事務(wù)。自動提交事務(wù)。自動提交模式是SQLServer的默認(rèn)事務(wù)管理模式。每個(gè)T-SQL語句在完成時(shí)，都被提交或回滾。如果一個(gè)語句成功地完成，則提交該語句；如果遇到錯誤，則回滾該語句。隱性事務(wù)。當(dāng)連接以隱性事務(wù)模式進(jìn)行操作時(shí)，SQLServer將在提交或回滾當(dāng)前事務(wù)后自動啟動新事務(wù)。無須描述事務(wù)的開始，只須提交或回滾每個(gè)事務(wù)。10.2.2顯式事務(wù)顯式事務(wù)需要顯式地定義事務(wù)的啟動和結(jié)束。它是通過BEGINTRANSACTION、COMMITTRANSACTION、COMMITWORK、ROLLBACKTRANSACTION或ROLLBACKWORK等T-SQL語句來完成的。1．啟動事務(wù)啟動事務(wù)使用BEGINTRANSACTION語句，執(zhí)行該語句會將@@TRANCOUNT加1。其語法格式如下：

BEGINTRAN[SACTION][tran_name|@tran_name_variable

[WITHMARK['desp']]]2．結(jié)束事務(wù)如果沒有遇到錯誤，可使用COMMITTRANSACTION語句成功地結(jié)束事務(wù)。該事務(wù)中的所有數(shù)據(jù)修改在數(shù)據(jù)庫中都將永久有效。事務(wù)占用的資源將被釋放。

COMMITTRANSACTION語句的語法格式如下：

COMMIT[TRAN[SACTION][tran_name|@tran_name_variable]]3．回滾事務(wù)如果事務(wù)中出現(xiàn)錯誤，或者用戶決定取消事務(wù)，可回滾該事務(wù)?；貪L事務(wù)是通過ROLLBACK語句來完成的。其語法格式如下：

ROLLBACK[TRAN[SACTION]

[tran_name|@tran_name_variable

|savepoint_name|@savepoint_variable]]

【例10.18】

給出以下程序的執(zhí)行結(jié)果。

USEschool

GO

BEGINTRANSACTION --啟動事務(wù)

INSERTINTOstudentVALUES('100','陳浩','男','1992/03/05','1033') --插入一個(gè)學(xué)生記錄

ROLLBACK --回滾事務(wù)

GO

SELECT*FROMstudent --查詢student表的記錄

GO解：該程序啟動一個(gè)事務(wù)向student表中插入一個(gè)記錄，然后回滾該事務(wù)。正是由于回滾了事務(wù)，所以student表中沒有真正插入該記錄。4．在事務(wù)內(nèi)設(shè)置保存點(diǎn)設(shè)置保存點(diǎn)使用SAVETRANSACTION語句，其語法格式為：

SAVETRAN[SACTION]{savepoint_name|@savepoint_variable}用戶可以在事務(wù)內(nèi)設(shè)置保存點(diǎn)或標(biāo)記。保存點(diǎn)是如果有條件地取消事務(wù)的一部分，事務(wù)可以返回的位置?！纠?0.19】

給出以下程序的執(zhí)行結(jié)果。USEschoolGOBEGINTRANSACTIONMytran --啟動事務(wù)

INSERTINTOstudentVALUES('100','陳浩','男','1992/03/05','1033') --插入一個(gè)學(xué)生記錄SAVETRANSACTIONMytran --保存點(diǎn)

INSERTINTOstudentVALUES('200','王浩','男','1992/10/05','1031') --插入一個(gè)學(xué)生記錄ROLLBACKTRANSACTIONMytranCOMMITTRANSACTIONGOSELECT*FROMstudent --查詢student表的記錄GODELETEstudentWHERE學(xué)號='100' --刪除插入的記錄GO

解：該程序設(shè)置了在事務(wù)內(nèi)設(shè)置保存點(diǎn)。執(zhí)行結(jié)果如圖10.19所示。從結(jié)果看到，由于在事務(wù)內(nèi)設(shè)置保存點(diǎn)Mytran，ROLLBACK只回滾到該保存點(diǎn)為止，所以只插入保存點(diǎn)前的一個(gè)記錄。5．標(biāo)記事務(wù)WITHMARK選項(xiàng)使事務(wù)名置于事務(wù)日志中。將數(shù)據(jù)庫還原到早期狀態(tài)時(shí)，可使用標(biāo)記事務(wù)替代日期和時(shí)間。6．不能用于事務(wù)的操作在事務(wù)處理中，并不是所有的T-SQL語句都可以取消執(zhí)行，一些不能撤消的操作（如創(chuàng)建、刪除和修改數(shù)據(jù)庫的操作），即使SQLServer取消了事務(wù)執(zhí)行或者對事務(wù)進(jìn)行了回滾，這些操作對數(shù)據(jù)庫造成的影響也是不能恢復(fù)的。10.2.3自動提交事務(wù)

SQLServer使用BEGINTRANSACTION語句啟動顯式事務(wù)，或隱性事務(wù)模式設(shè)置為打開之前，將以自動提交模式進(jìn)行操作。當(dāng)提交或回滾顯式事務(wù)或者關(guān)閉隱性事務(wù)模式時(shí)，SQLServer將返回到自動提交模式。在自動提交模式下，有時(shí)看起來SQLServer好像回滾了整個(gè)批處理，而不是僅僅一個(gè)SQL語句。這種情況只有在遇到的錯誤是編譯錯誤而不是運(yùn)行時(shí)錯誤時(shí)才會發(fā)生。編譯錯誤將阻止SQLServer建立執(zhí)行計(jì)劃，這樣批處理中的任何語句都不會執(zhí)行。盡管看起來好像是產(chǎn)生錯誤之前的所有語句都被回滾了，但實(shí)際情況是該錯誤使批處理中的任何語句都沒有執(zhí)行。10.2.4隱式事務(wù)在為連接將隱性事務(wù)模式設(shè)置為打開之后，當(dāng)SQLServer首次執(zhí)行某些T-SQL語句時(shí)，都會自動啟動一個(gè)事務(wù)，而不需要使用BEGINTRANSACTION語句。這些T-SQL語句包括：

ALTERTABLE INSERT OPEN

CREATE DELETE REVOKE

DROP SELECT FETCH

TRUNCATETABLE GRANT UPDATE10.3數(shù)據(jù)的鎖定10.3.1SQLServer中的鎖定

SQLServer2005具有多粒度鎖定，允許一個(gè)事務(wù)鎖定不同類型的資源。SQLServer可以鎖定的資源如下表所示（表中按粒度增加的順序列出）。資源描述RID行標(biāo)識符。用于單獨(dú)鎖定表中的一行鍵（KEY）索引中的行鎖。用于保護(hù)可串行事務(wù)中的鍵范圍頁（PAG）8KB數(shù)據(jù)頁或索引頁擴(kuò)展盤區(qū)（EXT）相鄰的8個(gè)數(shù)據(jù)頁或索引頁構(gòu)成的一組表（TAB）包括所有數(shù)據(jù)和索引在內(nèi)的整個(gè)表DB數(shù)據(jù)庫

SQLServer使用不同的鎖定模式鎖定資源，這些鎖定模式確定了并發(fā)事務(wù)訪問資源的方式，如下表所示。鎖模式描述共享（S）用于不更改或不更新數(shù)據(jù)的操作（只讀操作），如SELECT語句更新（U）用于可更新的資源中。防止當(dāng)多個(gè)會話在讀取、鎖定以及隨后可能進(jìn)行的資源更新時(shí)發(fā)生常見形式的死鎖排它（X）用于數(shù)據(jù)修改操作，例如INSERT、UPDATE或DELETE。確保不會同時(shí)對同一資源進(jìn)行多重更新意向用于建立鎖的層次結(jié)構(gòu)。意向鎖的類型為：意向共享（IS）、意向排它（IX）以及與意向排它共享（SIX）架構(gòu)在執(zhí)行依賴于表架構(gòu)的操作時(shí)使用。架構(gòu)鎖的類型為：架構(gòu)修改（Sch-M）和架構(gòu)穩(wěn)定性（Sch-S）大容量更新（BU）向表中大容量復(fù)制數(shù)據(jù)并指定了TABLOCK提示時(shí)使用1．共享鎖共享鎖允許并發(fā)事務(wù)讀?。⊿ELECT）一個(gè)資源。資源上存在共享鎖時(shí)，任何其他事務(wù)都不能修改數(shù)據(jù)。一旦已經(jīng)讀取數(shù)據(jù)，便立即釋放資源上的共享鎖，除非將事務(wù)隔離級別設(shè)置為可重復(fù)讀或更高級別，或者在事務(wù)生存周期內(nèi)用鎖定提示保留共享鎖。2．更新鎖更新鎖可以防止通常形式的死鎖。一般更新模式由一個(gè)事務(wù)組成，此事務(wù)讀取記錄，獲取資源（頁或行）的共享鎖，然后修改行，此操作要求鎖轉(zhuǎn)換為排它鎖。如果兩個(gè)事務(wù)獲得了資源上的共享模式鎖，然后試圖同時(shí)更新數(shù)據(jù)，則一個(gè)事務(wù)嘗試將鎖轉(zhuǎn)換為排它鎖。共享模式到排它鎖的轉(zhuǎn)換必須等待一段時(shí)間，因?yàn)橐粋€(gè)事務(wù)的排它鎖與其他事務(wù)的共享模式鎖不兼容，發(fā)生鎖等待。第二個(gè)事務(wù)試圖獲取排它鎖以進(jìn)行更新。由于兩個(gè)事務(wù)都要轉(zhuǎn)換為排它鎖，并且每個(gè)事務(wù)都等待另一個(gè)事務(wù)釋放共享模式鎖，因此發(fā)生死鎖。3．排它鎖排它鎖可以防止并發(fā)事務(wù)對資源進(jìn)行訪問。其他事務(wù)不能讀取或修改排它鎖鎖定的數(shù)據(jù)。4．意向鎖意向鎖表示SQLServer需要在層次結(jié)構(gòu)中的某些底層資源上獲取共享鎖或排它鎖。例如，放置在表級的共享意向鎖表示事務(wù)打算在表中的頁或行上放置共享鎖。在表級設(shè)置意向鎖可防止另一個(gè)事務(wù)隨后在包含那一頁的表上獲取排它鎖。意向鎖可以提高性能，因?yàn)镾QLServer僅在表級檢查意向鎖來確定事務(wù)是否可以安全地獲取該表上的鎖。而無須檢查表中的每行或每頁上的鎖以確定事務(wù)是否可以鎖定整個(gè)表。5．架構(gòu)鎖執(zhí)行表的數(shù)據(jù)定義語言（DDL）操作（如添加列或刪除表）時(shí)使用架構(gòu)修改鎖。當(dāng)編譯查詢時(shí)，使用架構(gòu)穩(wěn)定性鎖。架構(gòu)穩(wěn)定性鎖不阻塞任何事務(wù)鎖，包括排它鎖。因此在編譯查詢時(shí)，其他事務(wù)（包括在表上有排它鎖的事務(wù)）都能繼續(xù)運(yùn)行。但不能在表上執(zhí)行DDL操作。6．大容量更新鎖當(dāng)將數(shù)據(jù)大容量復(fù)制到表，且指定了TABLOCK提示或者使用sp_tableoption設(shè)置了tablelockonbulk表選項(xiàng)時(shí)，將使用大容量更新鎖。大容量更新鎖允許進(jìn)程將數(shù)據(jù)并發(fā)地大容量復(fù)制到同一表，同時(shí)防止其他不進(jìn)行大容量復(fù)制數(shù)據(jù)的進(jìn)程訪問該表。7．鎖兼容性只有兼容的鎖類型才可以放置在已鎖定的資源上。例如，當(dāng)控制排它鎖時(shí)，在第一個(gè)事務(wù)結(jié)束并釋放排它鎖之前，其他事務(wù)不能在該資源上獲取任何類型的（共享、更新或排它）鎖。資源鎖模式有一個(gè)兼容性矩陣，顯示了與在同一資源上可獲取的其他鎖相兼容的鎖，如下表所示。請求模式現(xiàn)有的授權(quán)模式ISSUIXSIXX意向共享（IS）是是是是是否共享（S）是是是否否否更新（U）是是否否否否意向排它（IX）是否否是否否與意向排它共享（SIX）是否否否否否排它（X）否否否否否否10.3.2自定義鎖1．死鎖封鎖機(jī)制的引入能解決并發(fā)用戶訪問數(shù)據(jù)的不一致性問題，但是，卻會引起死鎖。引起死鎖的主要原因是兩個(gè)進(jìn)程已經(jīng)各自鎖定一個(gè)頁，但是又要訪問被對方鎖定的頁。因而會形成等待圈，導(dǎo)致死鎖。圖10.22死鎖2．自定義鎖超時(shí)在默認(rèn)情況下，沒有強(qiáng)制的超時(shí)期限，并且除了試圖訪問數(shù)據(jù)外（有可能被無限期阻塞），沒有其他方法可以測試某個(gè)資源在鎖定之前是否已經(jīng)、被鎖定。

LOCK_TIMEOUT語句設(shè)置允許應(yīng)用程序設(shè)置語句等待阻塞資源的最長時(shí)間。當(dāng)語句等待的時(shí)間大于LOCK_TIMEOUT設(shè)置時(shí)，系統(tǒng)將自動取消阻塞的語句，并給應(yīng)用程序返回“已超過了鎖請求超時(shí)時(shí)段”的1222號錯誤信息。3．自定義事務(wù)隔離級別在數(shù)據(jù)庫操作過程中很可能出現(xiàn)以下幾種不確定情況：更新丟失：兩個(gè)事務(wù)都同時(shí)更新一行數(shù)據(jù)，但是第二個(gè)事務(wù)卻中途失敗退出，導(dǎo)致對數(shù)據(jù)的兩個(gè)修改都失效了。這是因?yàn)橄到y(tǒng)沒有執(zhí)行任何的鎖操作，因此并發(fā)事務(wù)并沒有被隔離開來。臟讀：一個(gè)事務(wù)開始讀取了某行數(shù)據(jù)，但是另外一個(gè)事務(wù)已經(jīng)更新了此數(shù)據(jù)但沒有能夠及時(shí)提交。這是相當(dāng)危險(xiǎn)的，因?yàn)楹芸赡芩械牟僮鞫急换貪L。不可重復(fù)讀：一個(gè)事務(wù)對同一行數(shù)據(jù)重復(fù)讀取兩次但是卻得到了不同結(jié)果。例如在兩次讀取中途有另外一個(gè)事務(wù)對該行數(shù)據(jù)進(jìn)行了修改并提交?；米x（幻像或幻影）：事務(wù)在操作過程中進(jìn)行兩次查詢，第二次查詢結(jié)果包含了第一次查詢中未出現(xiàn)的數(shù)據(jù)（這里并不要求兩次查詢SQL語句相同），這是因?yàn)樵趦纱尾樵冞^程中有另外一個(gè)事務(wù)插入數(shù)據(jù)造成的出現(xiàn)這些情況發(fā)生的根本原因都是因?yàn)樵诓l(fā)訪問的時(shí)候，沒有一個(gè)機(jī)制避免交叉存取所造成的。而隔離級別的設(shè)置，正是為了避免這些情況的發(fā)生。事務(wù)準(zhǔn)備接受不一致數(shù)據(jù)的級別稱為隔離級別，隔離級別是一個(gè)事務(wù)必須與其他事務(wù)進(jìn)行隔離的程度。較低的隔離級別可以增加并發(fā)，但代價(jià)是降低數(shù)據(jù)的正確性。相反，較高的隔離級別可以確保數(shù)據(jù)的正確性，但可能對并發(fā)產(chǎn)生負(fù)面影響。未授權(quán)讀取，也稱為讀未提交（READUNCOMMITTED）：允許臟讀取，但不允許更新丟失。如果一個(gè)事務(wù)已經(jīng)開始寫數(shù)據(jù)，則另外一個(gè)數(shù)據(jù)則不允許同時(shí)進(jìn)行寫操作，但允許其他事務(wù)讀此行數(shù)據(jù)。授權(quán)讀取，也稱為讀提交（READCOMMITTED）：允許不可重復(fù)讀取，但不允許臟讀取。可重復(fù)讀?。≧EPEATABLEREAD）：禁止不可重復(fù)讀取和臟讀取，但是有時(shí)可能出現(xiàn)幻影數(shù)據(jù)。這可以通過共享讀鎖和排他寫鎖實(shí)現(xiàn)。串行讀（SERIALIZABLE），也稱為序列化：提供嚴(yán)格的事務(wù)隔離。它要求事務(wù)序列化執(zhí)行，事務(wù)只能一個(gè)接著一個(gè)地執(zhí)行，但不能并發(fā)執(zhí)行。在標(biāo)準(zhǔn)SQL規(guī)范中，定義了如下四個(gè)事務(wù)隔離級別：在默認(rèn)情況下，SQLServer2005在授權(quán)讀取事務(wù)隔離級別上操作。但是，應(yīng)用程序可能必須運(yùn)行于不同的隔離級別。若要在應(yīng)用程序中使用更嚴(yán)格或較寬松的隔離級別，可以通過使用SETTRANSACTIONISOLATIONLEVEL語句設(shè)置會話的隔離級別，來自定義整個(gè)會話的鎖定。

SETTRANSACTIONISOLATIONLEVEL語句的語法格式如下：

SETTRANSACTIONISOLATIONLEVEL

{READCOMMITTED

|READUNCOMMITTED

|REPEATABLEREAD

|SERIALIZABLE

}其中4個(gè)選項(xiàng)分別代表了4種隔離級別：READCOMMITTED：授權(quán)讀?。J(rèn)值）。READUNCOMMITTED：未授權(quán)讀取。REPEATABLEREAD：可重復(fù)讀取。SERIALIZABLE：串行讀?！纠?0.22】

給出以下程序的功能。USEschoolGOSETTRANSACTIONISOLATIONLEVELSERIALIZABLEGOBEGINTRANSACTIONSELECT學(xué)號,姓名,班號FROMstudentGOCOMMITTRANSACTION

解：該程序設(shè)置事務(wù)隔離級別為可串行讀，以確保并發(fā)事務(wù)不能在student表中插入行。若查看當(dāng)前設(shè)置的事務(wù)隔離級別,可用DBCCUSEROPTIONS語句，例如：

USEschool

GO

SETTRANSACTIONISOLATIONLEVELSERIALIZABLE

GO

DBCCUSEROPTIONS

GO執(zhí)行結(jié)果如下圖所示,從中看到事務(wù)隔離級別為SERIALIZABLE。10.4使用游標(biāo)10.4.1游標(biāo)的概念由SELECT語句返回的行集包括所有滿足該語句WHERE子句中條件的行，由語句所返回的這一完整的行集稱為結(jié)果集。應(yīng)用程序，特別是交互式聯(lián)機(jī)應(yīng)用程序，并不總能將整個(gè)結(jié)果集作為一個(gè)單元來有效地處理。這些應(yīng)用程序需要一種機(jī)制，以便每次處理一行或一部分行。游標(biāo)就是用來提供這種機(jī)制的結(jié)果集擴(kuò)展。10.4.2游標(biāo)的基本操作游標(biāo)的基本操作包括聲明游標(biāo)、打開游標(biāo)、提取數(shù)據(jù)、關(guān)閉游標(biāo)和釋放游標(biāo)。1．聲明游標(biāo)聲明游標(biāo)使用DECLARECURSOR語句，其語法格式如下：

DECLARE游標(biāo)名稱[INSENSITIVE][SCROLL]

[STATIC|KEYSET|DYNAMIC|FAST_FORWORD]CURSOR

FORselect_statement

[FOR{READONLY|UPDATE[OF列名[,…n]]}]2．打開游標(biāo)打開游標(biāo)使用OPEN語句，其語法格式如下：

OPEN游標(biāo)名稱當(dāng)打開游標(biāo)時(shí)，服務(wù)器執(zhí)行聲明時(shí)使用的SELECT語句。3．從打開的游標(biāo)中提取行游標(biāo)聲明，而且被打開以后，游標(biāo)位置位于第一行?？梢允褂肍ETCH語句從游標(biāo)結(jié)果集中提取數(shù)據(jù)。其語法格式如下：FETCH[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n|@nvar}|RELATIVE{n|@nvar}]FROM]

游標(biāo)名稱

[INTO@variable_name[,…n]]4．關(guān)閉游標(biāo)關(guān)閉游標(biāo)使用CLOSE語句，其語法格式如下：

CLOSE游標(biāo)名稱}關(guān)閉游標(biāo)后可以再次打開。在一個(gè)批處理中，可以多次打開和關(guān)閉游標(biāo)。5．釋放游標(biāo)釋放游標(biāo)將釋放所有分配給此游標(biāo)的資源。釋放游標(biāo)使用DEALLOCATE語句，其語法格式為：

DEALLOCATE游標(biāo)名稱10.4.3使用游標(biāo)1.使用游標(biāo)的過程游標(biāo)主要用在存儲過程、觸發(fā)器和T-SQL腳本中，它們使結(jié)果集的內(nèi)容對其他T-SQL語句同樣可用。使用游標(biāo)的典型過程如下：聲明T-SQL變量包含游標(biāo)返回的數(shù)據(jù)。為每一結(jié)果集列聲明一個(gè)變量，聲明足夠大的變量，以保存由列返回的值，并聲明可從列數(shù)據(jù)類型以隱性方式轉(zhuǎn)換得到的數(shù)據(jù)類型。使用DECLARECURSOR語句把T-SQL游標(biāo)與一個(gè)SELECT語句相關(guān)聯(lián)。DECLARECURSOR語句同時(shí)定義游標(biāo)的特征，如游標(biāo)名稱以及游標(biāo)是否為只讀或只寫特性。使用OPEN語句執(zhí)行SELECT語句并生成游標(biāo)。使用FETCHINTO語句提取單個(gè)行，并把每列中的數(shù)據(jù)轉(zhuǎn)移到指定的變量中。然后，其他T-SQL語句可以引用這些變量來訪問已提取的數(shù)據(jù)值。T-SQL不支持提取行塊。結(jié)束游標(biāo)時(shí)使用CLOSE語句。關(guān)閉游標(biāo)可以釋放某些資源，如游標(biāo)結(jié)果集和對當(dāng)前行的鎖定。但是如果重新發(fā)出一個(gè)OPEN語句，則該游標(biāo)結(jié)構(gòu)仍可用于處理。由于游標(biāo)仍然存在，此時(shí)還不能重新使用游標(biāo)的名稱。DEALLOCATE語句則完全釋放分配給游標(biāo)的資源，包括游標(biāo)名稱。在游標(biāo)被釋放后，必須使用DECLARE語句來重新生成游標(biāo)。其處理過程如下：【例10.24】

給出以下程序的執(zhí)行結(jié)果。USEschoolGO--聲明游標(biāo)DECLAREst_cursorCURSORFORSELECT學(xué)號,姓名,班號FROMstudent--打開游標(biāo)OPENst_cursor--提取第一行數(shù)據(jù)FETCHNEXTFROMst_cursor--關(guān)閉游標(biāo)CLOSEst_cursor--釋放游標(biāo)DEALLOCATEst_cursorGO【例10.25】

給出以下程序的執(zhí)行結(jié)果。USEscho