SQL Server數(shù)據(jù)庫(kù)及應(yīng)用（第二版）課件第10章 游標(biāo)和事務(wù)

第10章游標(biāo)和事務(wù)本章學(xué)習(xí)目標(biāo)了解游標(biāo)、事務(wù)的概念和作用掌握游標(biāo)的操作和使用方法掌握事務(wù)的操作和使用方法10.1游標(biāo)10.1.1

游標(biāo)概述1.游標(biāo)的概念在SQLServer中，使用SELECT語(yǔ)句生成的記錄集合被作為一個(gè)整體單元來(lái)處理，無(wú)法對(duì)其中的一條或一部分記錄單獨(dú)處理。在數(shù)據(jù)庫(kù)應(yīng)用程序中，常常需要對(duì)這些記錄集合進(jìn)行逐行操作。游標(biāo)就是提供這種機(jī)制的結(jié)果集擴(kuò)展，它使我們可以逐行處理結(jié)果集。10.1.1游標(biāo)概述2．游標(biāo)的優(yōu)點(diǎn)（1）游標(biāo)允許應(yīng)用程序?qū)ELECT查詢語(yǔ)句返回的結(jié)果集中的每一行進(jìn)行相同或不同的操作。（2）允許從結(jié)果集中檢索指定的行。（3）允許結(jié)果集中的當(dāng)前行被修改。（4）允許被其它用戶修改的數(shù)據(jù)在結(jié)果集中是可見(jiàn)的。10.1.1

游標(biāo)概述3.游標(biāo)的分類SQLServer支持三種游標(biāo)實(shí)現(xiàn)方式：T-SQL游標(biāo)API服務(wù)器游標(biāo)客戶游標(biāo)10.1.1

游標(biāo)概述3.游標(biāo)的分類（1）T-SQL游標(biāo)

T-SQL游標(biāo)是由DECLARECURSOR語(yǔ)句定義，主要用于T-SQL腳本、存儲(chǔ)過(guò)程和觸發(fā)器。T-SQL游標(biāo)在服務(wù)器上實(shí)現(xiàn)，并由從客戶端發(fā)送給服務(wù)器的T-SQL語(yǔ)句進(jìn)行管理。T-SQL游標(biāo)不支持提取數(shù)據(jù)塊或多行數(shù)據(jù)。10.1.1

游標(biāo)概述3.游標(biāo)的分類

（2）API游標(biāo)

API游標(biāo)支持OLEDB和ODBC中的游標(biāo)函數(shù)，在服務(wù)器上實(shí)現(xiàn)。每一次客戶端應(yīng)用程序調(diào)用API游標(biāo)函數(shù)時(shí)，SQLServer的OLEDB提供者、ODBC驅(qū)動(dòng)器都會(huì)將這些客戶請(qǐng)求傳送給服務(wù)器以對(duì)API服務(wù)器游標(biāo)進(jìn)行處理。10.1.1

游標(biāo)概述3.游標(biāo)的分類（3）客戶游標(biāo)客戶游標(biāo)主要是當(dāng)在客戶機(jī)上緩存結(jié)果集時(shí)才使用。在客戶游標(biāo)中，有一個(gè)缺省的結(jié)果集被用來(lái)在客戶機(jī)上緩存整個(gè)結(jié)果集?？蛻粲螛?biāo)僅支持靜態(tài)游標(biāo)而非動(dòng)態(tài)游標(biāo)。10.1.1

游標(biāo)概述3.游標(biāo)的分類由于API游標(biāo)和T-SQL游標(biāo)使用在服務(wù)器端，所以被稱為服務(wù)器游標(biāo)，也被稱為后后臺(tái)游標(biāo)，而客戶端游標(biāo)被稱為前臺(tái)游標(biāo)。10.1.2游標(biāo)的使用游標(biāo)主要用在存儲(chǔ)過(guò)程、觸發(fā)器和T-SQL語(yǔ)言腳本中。用戶可以把它理解為一種特殊變量，也必須先聲明后使用。游標(biāo)的使用可以總結(jié)為5個(gè)步驟：聲明游標(biāo)、打開(kāi)游標(biāo)、提取數(shù)據(jù)、關(guān)閉游標(biāo)和釋放游標(biāo)。10.1.2游標(biāo)的使用1．聲明游標(biāo)通常使用DECLARE語(yǔ)句來(lái)聲明一個(gè)游標(biāo)。聲明的游標(biāo)主要包括以下主要內(nèi)容：游標(biāo)名字?jǐn)?shù)據(jù)來(lái)源（表和列）選取條件屬性（僅讀或可修改）10.1.2游標(biāo)的使用1．聲明游標(biāo)其語(yǔ)法格式如下：DECLAREcursor_nameCURSOR[LOCAL|GLOBAL][FORWARD_ONLY|SCROLL][STATIC|KEYSET|DYNAMIC|FAST_FORWARD][READ_ONLY|SCROLL_LOCKS|OPTIMISTIC][TYPE_WARNING]FORselect_statement[FORUPDATE[OFcolumn_name[,...n]]][;]10.1.2游標(biāo)的使用其中，各參數(shù)的說(shuō)明如下：cursor_name：指定游標(biāo)的名稱。必須符合標(biāo)識(shí)符規(guī)則。LOCAL：指定對(duì)于在其中創(chuàng)建的批處理、存儲(chǔ)過(guò)程或觸發(fā)器來(lái)說(shuō)，該游標(biāo)的作用域是局部的。GLOBAL：指定該游標(biāo)的作用域是全局的。FORWARD_ONLY：指定游標(biāo)只能從第一行滾動(dòng)到最后一行。SCROLL：指定所有的提取選項(xiàng)均可用。10.1.2游標(biāo)的使用STATIC：定義一個(gè)游標(biāo)，在對(duì)該游標(biāo)進(jìn)行提取操作時(shí)返回的數(shù)據(jù)中不反映對(duì)基本表所做的修改，并且該游標(biāo)不允許修改。KEYSET：指定當(dāng)游標(biāo)打開(kāi)時(shí)，游標(biāo)中行的成員身份和順序已經(jīng)固定。DYNAMIC：定義一個(gè)游標(biāo)，以反映在滾動(dòng)游標(biāo)時(shí)對(duì)結(jié)果集內(nèi)的各行所做的所有數(shù)據(jù)更改。FAST_FORWARD：指定啟用了性能優(yōu)化的FORWARD_ONLY、READ_ONLY游標(biāo)。如果指定了SCROLL或FOR_UPDATE，則不能指定FAST_FORWARD。10.1.2游標(biāo)的使用READ_ONLY：設(shè)置只讀游標(biāo)。SCROLL_LOCKS：指定通過(guò)游標(biāo)進(jìn)行的定位更新或刪除一定會(huì)成功。OPTIMISTIC：指定如果行自讀入游標(biāo)以來(lái)已得到更新，則通過(guò)游標(biāo)進(jìn)行的定位更新或定位刪除不成功。TYPE_WARNING：指定將游標(biāo)從所請(qǐng)求的類型隱式轉(zhuǎn)換為另一種類型時(shí)向客戶端發(fā)送警告消息。10.1.2游標(biāo)的使用select_statement：定義游標(biāo)結(jié)果集的標(biāo)準(zhǔn)SELECT語(yǔ)句。在游標(biāo)聲明的select_statement中不允許使用關(guān)鍵字COMPUTE、COMPUTEBY、FORBROWSE和INTO子句。FORUPDATE[OFcolumn_name[,...n]]：定義游標(biāo)中可更新的列。如果提供了OFcolumn_name[,...n]，則只允許修改所列出的列。如果指定了UPDATE，但未指定列的列表，則除非指定了READ_ONLY并發(fā)選項(xiàng)，否則可以更新所有的列。10.1.2游標(biāo)的使用2．打開(kāi)游標(biāo)聲明了游標(biāo)后在做其它操作之前必須打開(kāi)它。打開(kāi)游標(biāo)是執(zhí)行與其相關(guān)的一段SQL語(yǔ)句。其語(yǔ)法格式為：OPEN{[GLOBAL]cursor_name}10.1.2游標(biāo)的使用2．打開(kāi)游標(biāo)游標(biāo)處于打開(kāi)狀態(tài)下，不能再次被打開(kāi)，也就是OPEN語(yǔ)句只能打開(kāi)已聲明但未打開(kāi)的游標(biāo)。打開(kāi)一個(gè)游標(biāo)后，可以使用系統(tǒng)函數(shù)@@ERROR判斷打開(kāi)操作是否成功。當(dāng)游標(biāo)被成功打開(kāi)時(shí)，游標(biāo)位置指向記錄集的第一行之前。游標(biāo)打開(kāi)成功后，可以使用系統(tǒng)函數(shù)@@CURSOR_ROWS返回游標(biāo)中的記錄數(shù)。10.1.2游標(biāo)的使用3．提取游標(biāo)語(yǔ)法格式如下：FETCH[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n|@nvar}|RELATIVE{n|@nvar}]FROM]{{[GLOBAL]cursor_name}|@cursor_variable_name}[INTO@variable_name[,...n]]10.1.2游標(biāo)的使用其中，各參數(shù)的說(shuō)明如下：NEXT：指定緊跟當(dāng)前行返回結(jié)果行，并且當(dāng)前行遞增為返回行。如果FETCHNEXT為對(duì)游標(biāo)的第一次提取操作，則返回結(jié)果集中的第一行。PRIOR：指定返回緊鄰當(dāng)前行前面的結(jié)果行，并且當(dāng)前行遞減為返回行。FIRST：返回游標(biāo)中的第一行并將其作為當(dāng)前行。LAST：返回游標(biāo)中的最后一行并將其作為當(dāng)前行。10.1.2游標(biāo)的使用ABSOLUTE{n|@nvar}：指定按絕對(duì)位置提取數(shù)據(jù)。RELATIVE{n|@nvar}：指定按相對(duì)位置提取數(shù)據(jù)。GLOBAL：指定cursor_name為全局游標(biāo)。cursor_name：要從中進(jìn)行提取的打開(kāi)的游標(biāo)名稱。10.1.2游標(biāo)的使用@cursor_variable_name：游標(biāo)變量名，引用要從中進(jìn)行提取操作的打開(kāi)的游標(biāo)。INTO@variable_name[,...n]：允許將提取操作的列數(shù)據(jù)放到局部變量中。列表中的各個(gè)變量從左到右與游標(biāo)結(jié)果集中的相應(yīng)列相關(guān)聯(lián)。各變量的數(shù)據(jù)類型必須與相應(yīng)的結(jié)果集列的數(shù)據(jù)類型匹配，或是結(jié)果集列數(shù)據(jù)類型所支持的隱式轉(zhuǎn)換。變量的數(shù)目必須與游標(biāo)選擇列表中的列數(shù)一致。10.1.2游標(biāo)的使用4．關(guān)閉游標(biāo)在游標(biāo)操作的最后不要忘記關(guān)閉游標(biāo)，以使系統(tǒng)釋放游標(biāo)占用的資源。關(guān)閉游標(biāo)的語(yǔ)法格式為：CLOSE{{[GLOBAL]cursor_name}|cursor_variable_name}關(guān)閉游標(biāo)后，系統(tǒng)刪除了游標(biāo)中的所有數(shù)據(jù)，所以不能再?gòu)挠螛?biāo)中提取數(shù)據(jù)。但是，可以再使用OPEN語(yǔ)句重新打開(kāi)游標(biāo)使用。10.1.2游標(biāo)的使用4．釋放游標(biāo)如果一個(gè)游標(biāo)確定不再使用時(shí)，可以將其刪除，徹底釋放游標(biāo)所占系統(tǒng)資源。釋放游標(biāo)即將其刪除，如果想重新使用游標(biāo)就必須重新聲明一個(gè)新的游標(biāo)。其語(yǔ)法形格式為：DEALLOCATE{{[GLOBAL]cursor_name}|@cursor_variable_name}10.1.2游標(biāo)的使用例10-1：聲明一個(gè)游標(biāo)Customer_Cursor，用于查看AWLT數(shù)據(jù)庫(kù)中Customer表中姓名中包含字母Ab的記錄，并使用FETCHNEXT逐個(gè)提取這些行。例10-2：聲明一個(gè)游標(biāo)Name_Cursor，并使用SELECT語(yǔ)句顯示@@CURSOR_ROWS的值。例10-3：該例與例10-1類似，使用FETCH語(yǔ)句將值存入變量，但FETCH語(yǔ)句的輸出存儲(chǔ)于局部變量而不是直接返回到客戶端。PRINT語(yǔ)句將變量組合成單一字符串并將其返回到客戶端。10.1.3使用游標(biāo)修改數(shù)據(jù)使用T-SQL服務(wù)器游標(biāo)時(shí)，可以使用包含WHERECURRENTOF子句的UPDATE和DELETE語(yǔ)句更新或刪除當(dāng)前游標(biāo)中的當(dāng)前行。使用此子句所做的更改或刪除只影響游標(biāo)所在行。10.1.3使用游標(biāo)修改數(shù)據(jù)（1）用于游標(biāo)操作時(shí)，UPDATE語(yǔ)句的語(yǔ)法格式如下：UPDATEtable_nameSETcolumn_name=expressionWHERECURRENTOFcursor_name其中，CURRENTOFcursor_name表示當(dāng)前游標(biāo)的當(dāng)前數(shù)據(jù)行。CURRENTOF子句只能用在UPDATE和DELETE操作的語(yǔ)句中。10.1.3使用游標(biāo)修改數(shù)據(jù)（2）用于游標(biāo)操作時(shí)，DELETE語(yǔ)句的語(yǔ)法格式如下：DELETEFROMtable_nameWHERECURRENTOFcursor_name例10-4：使用游標(biāo)更新表中的數(shù)據(jù)。例10-5：使用游標(biāo)刪除表中記錄。當(dāng)游標(biāo)基于多個(gè)數(shù)據(jù)表時(shí)，UPDATE語(yǔ)句和DELETE語(yǔ)句一次只能更新或刪除一個(gè)表中的數(shù)據(jù)，而其他表中的數(shù)據(jù)不受影響。10.2事務(wù)10.2.1事務(wù)概述1.事務(wù)的概念事務(wù)是一種機(jī)制、是一種操作序列，它包含了一組數(shù)據(jù)庫(kù)操作命令，這組命令要么全部執(zhí)行，要么全部不執(zhí)行。事務(wù)是一個(gè)不可分割的邏輯工作單元。遇到錯(cuò)誤時(shí)，回滾事務(wù)，取消事務(wù)內(nèi)所做的所有改變，從而保證數(shù)據(jù)庫(kù)中數(shù)據(jù)的一致性和可恢復(fù)性。。10.2.1事務(wù)概述2．事務(wù)的特性原子性：事務(wù)是數(shù)據(jù)庫(kù)的邏輯工作單元，事務(wù)中的操作要么全部執(zhí)行，要么全部不執(zhí)行。一致性：事務(wù)執(zhí)行的結(jié)果必須是使數(shù)據(jù)庫(kù)從一個(gè)一致性狀態(tài)變到另一個(gè)一致性狀態(tài)。隔離性：事務(wù)的執(zhí)行不能被其它事務(wù)干擾。持久性（永久性）：事務(wù)一旦提交，則其對(duì)數(shù)據(jù)庫(kù)中數(shù)據(jù)的改變就應(yīng)該是永久的。10.2.1事務(wù)概述3．與批處理的區(qū)別批處理是一組整體編譯的SQL語(yǔ)句，事務(wù)是一組作為單個(gè)邏輯工作單元執(zhí)行的SQL語(yǔ)句。批處理中語(yǔ)句的組合發(fā)生在編譯時(shí)刻，事務(wù)中語(yǔ)句的組合發(fā)生在執(zhí)行時(shí)刻。10.2.1事務(wù)概述3．與批處理的區(qū)別編譯時(shí)批處理中某個(gè)語(yǔ)句存在語(yǔ)法錯(cuò)誤，則系統(tǒng)將取消整個(gè)批處理中所有語(yǔ)句的執(zhí)行；而在運(yùn)行時(shí)刻，如果事務(wù)中某個(gè)數(shù)據(jù)修改違反約束、規(guī)則等，系統(tǒng)默認(rèn)只回退到產(chǎn)生該錯(cuò)誤的語(yǔ)句。一個(gè)事務(wù)中可以擁有多個(gè)批處理，一個(gè)批處理中可以有多個(gè)SQL語(yǔ)句組成的事務(wù)，事務(wù)內(nèi)批處理的多少不影響事務(wù)的提交或回滾操作。10.2.1事務(wù)概述4．事務(wù)的分類（1）顯式事務(wù)顯示事務(wù)是指顯示定義了其啟動(dòng)和結(jié)束的事務(wù)，即每個(gè)事務(wù)均以BEGINTRAN語(yǔ)句顯示開(kāi)始，以COMMITTRAN或ROLLBACKTRAN語(yǔ)句顯示結(jié)束。（2）隱式事務(wù)有時(shí)候看起來(lái)沒(méi)有使用事務(wù)的明顯標(biāo)志，但它們可能隱藏在幕后，這種事務(wù)叫做隱式事務(wù)。10.2.1事務(wù)概述4．事務(wù)的分類（2）隱式事務(wù)有時(shí)候看起來(lái)沒(méi)有使用事務(wù)的明顯標(biāo)志，但它們可能隱藏在幕后，這種事務(wù)叫做隱式事務(wù)。（3）自動(dòng)提交事務(wù)自動(dòng)提交事務(wù)是SQLServer默認(rèn)的事務(wù)管理模式。每條單獨(dú)的T-SQL語(yǔ)句都是一個(gè)事務(wù)，每條語(yǔ)句在完成時(shí)，都會(huì)提交或回滾。10.2.2管理事務(wù)1．

事務(wù)設(shè)計(jì)的原則不要在事務(wù)處理期間要求用戶輸入。在瀏覽數(shù)據(jù)時(shí)，盡量不要打開(kāi)事務(wù)，在所有預(yù)備的數(shù)據(jù)分析完成之前，不應(yīng)啟動(dòng)事務(wù)。保持事務(wù)盡可能簡(jiǎn)短。靈活使用更低的事務(wù)隔離級(jí)別。靈活地使用更低的游標(biāo)并發(fā)選項(xiàng)。10.2.2管理事務(wù)2．

開(kāi)始事務(wù)其語(yǔ)法格式如下：BEGIN{TRAN|TRANSACTION}[{transaction_name|@tran_name_variable}

[WITHMARK['description']]][;]10.2.2管理事務(wù)其中，各參數(shù)說(shuō)明如下：transaction_name：指定分配給事務(wù)的名稱。@tran_name_variable：用戶定義的、含有有效事務(wù)名稱的變量名稱。WITHMARK['description']：指定在日志中標(biāo)記事務(wù)。description是描述該標(biāo)記的字符串。10.2.2管理事務(wù)3．提交事務(wù)COMMITTRANSACTION用來(lái)標(biāo)志一個(gè)成功事務(wù)的結(jié)束。提交事務(wù)的語(yǔ)法格式為：COMMIT{TRAN|TRANSACTION}[transaction_nam|@tran_name_variable]][;]各參數(shù)說(shuō)明同BEGINTRANSACTION。10.2.2管理事務(wù)4．回滾事務(wù)將顯式事務(wù)回滾到事務(wù)的起點(diǎn)或事務(wù)內(nèi)的某個(gè)保存點(diǎn)。其語(yǔ)法格式為：ROLLBACK{TRAN|TRANSACTION}[transaction_name|@tran_name_variable|savepoint_name|@savepoint_variable][;]其中，savepoint_name：指定SAVETRANSACTION語(yǔ)句中的保存點(diǎn)名稱，必須符合標(biāo)識(shí)符規(guī)則。@savepoint_variable：是用戶定義的、包含有效保存點(diǎn)名稱的變量名稱。10.2.3

使用事務(wù)BEGINTRANSACTIONmytran1UPDATE--對(duì)數(shù)據(jù)進(jìn)行修改但不提交DELETESAVETRANSACTIONS1--設(shè)置保存點(diǎn)S1DELETE--刪除數(shù)據(jù)但不提交ROLLBACKTRANSACTIONS1--將事務(wù)回滾到保存點(diǎn)S1，這時(shí)第5行所做的修改被撤銷INSERT--添加數(shù)據(jù)COMMITTRANSACTION--提交事務(wù)10.2.3

使用事務(wù)例10-6：顯示如何命名事務(wù)。例10-7：顯示如何標(biāo)記事務(wù)。例10-8：提交嵌套事務(wù)。例10-9：顯示了回滾已命名事務(wù)的效果。本章小結(jié)在SQLServer中，使用SELECT語(yǔ)句生成的記錄集合被作為一個(gè)整體單元來(lái)處理，無(wú)法對(duì)其中的一條或一部分記錄單獨(dú)處理，而游標(biāo)使我們可以逐行處理結(jié)果集。使用游標(biāo)的主要好處是可以逐行的處理數(shù)據(jù)。本章小結(jié)SQLServer支持三種游標(biāo)實(shí)現(xiàn)方式：T-SQL游標(biāo)API服務(wù)器游標(biāo)客戶游標(biāo)游標(biāo)的使用主要分為5個(gè)步驟：聲明游標(biāo)打開(kāi)游標(biāo)提取數(shù)據(jù)關(guān)閉游標(biāo)釋放游標(biāo)本章小結(jié)（1）聲明的游標(biāo)DECLAREcursor_nameCURSOR[LOCAL|GLOBAL][FORWARD_ONLY|SCROLL][STATIC|KEYSET|DYNAMIC|FAST_FORWARD][READ_ONLY|SCROLL_LOCKS|OPTIMISTIC][TYPE_WARNING]FORselect_statement[FORUPDATE[OFcolumn_name[,...n]]][;]本章小結(jié)（2）打開(kāi)游標(biāo)OPEN{[GLOBAL]cursor_name}（3）提取數(shù)據(jù)FETCH[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n|@nvar}|RELATIVE{n|@nvar}]FROM]{{[GLOBAL]cursor_name}|@cursor_variable_name}[INTO@variable_name[,...n]]本章小結(jié)（4）關(guān)閉游標(biāo)CLOSE{{[GLOBAL]cursor_name}|cursor_variable_name}關(guān)閉游標(biāo)后可以再使用OPEN語(yǔ)句重新打開(kāi)游標(biāo)使用。（5）釋放游標(biāo)DEALLOCATE{{[GLOBAL]cursor_name}|@cursor_variable_name}本章小結(jié)使用T-SQL服務(wù)器游標(biāo)時(shí)，可以使用包含WHERECURRENTOF子句的UPDATE和DELETE語(yǔ)句更新或刪除當(dāng)前游標(biāo)中的當(dāng)前行。使用此子句所做的更改或刪除只影響游標(biāo)所在行。本章小結(jié)事務(wù)定義事務(wù)特性原子性一致性隔離性持久性本章小結(jié)開(kāi)始事務(wù)BEGIN{TRAN|TRANSACTION}[{transaction_name|@tran_name_variable}[WITHMARK['description']]][;]提交事務(wù)COMMIT{TRAN|TRANSACTION}[transaction_nam|@tran_name_variable]][;]回滾事務(wù)ROLLBACK{TRAN|TRANSACTION}[transacti