荊州職業(yè)技術(shù)學(xué)院《網(wǎng)絡(luò)數(shù)據(jù)庫(kù)技術(shù)》課程教案專業(yè)班級(jí)教師授課時(shí)間教學(xué)課題第十二講游標(biāo)及事務(wù)學(xué)時(shí)2教學(xué)目的1、學(xué)習(xí)SQLServer2000數(shù)據(jù)庫(kù)的事務(wù)處理2、掌握游標(biāo)的使用方法3、配合其它T-SQL語(yǔ)句靈活使用游標(biāo)

4、了解SQLServer鎖機(jī)制教材分析重點(diǎn)1、掌握事務(wù)的使用方法2、游標(biāo)的靈活應(yīng)用難點(diǎn)Inserted、Deleted表的使用教學(xué)方法采用“項(xiàng)目教學(xué)法”教學(xué)手段多媒體教學(xué)教學(xué)過程及時(shí)間分配★復(fù)習(xí)鞏固，問題牽引、導(dǎo)入新課。提問：5min★小結(jié)及作業(yè)布置5min【教學(xué)內(nèi)容】第十三章游標(biāo)及事務(wù)1３.1游標(biāo)的定義及其優(yōu)點(diǎn)1.游標(biāo)的概念v游標(biāo)(Cursor)是一種處理數(shù)據(jù)的方法，為了查看或者處理結(jié)果集中的數(shù)據(jù)，游標(biāo)提供了在結(jié)果集中向前或者向后瀏覽數(shù)據(jù)的能力。可以把游標(biāo)看成一種指針，它既可以指向當(dāng)前位置，也可以指向結(jié)果集中的任意位置，它允許用戶對(duì)指定位置的數(shù)據(jù)進(jìn)行處理，可以把結(jié)果集中的數(shù)據(jù)放在數(shù)組、應(yīng)用程序中或其它地方。Transact-SQL游標(biāo)遵循ANSI-92標(biāo)準(zhǔn)。2.使用游標(biāo)的優(yōu)點(diǎn) v允許程序?qū)τ刹樵冋Z(yǔ)句SELECT返回的行集合中的每一行數(shù)據(jù)執(zhí)行相同或不同的操作，而不是對(duì)整個(gè)行集合執(zhí)行同一個(gè)操作 v提供對(duì)基于游標(biāo)位置的表中的行進(jìn)行刪除和更新的能力 v游標(biāo)實(shí)際上作為面向集合的數(shù)據(jù)庫(kù)管理系統(tǒng)（RDBMS）和面向行的程序設(shè)計(jì)之間的橋梁，使這兩種處理方式通過游標(biāo)溝通起來1３.２游標(biāo)的使用1.使用游標(biāo)的步驟使用游標(biāo)具體地說，有如下幾個(gè)步驟：①創(chuàng)建游標(biāo)。使用T-SQL語(yǔ)句生成一個(gè)結(jié)果集，并且定義游標(biāo)的特征，如游標(biāo)中的記錄是否可以修改。②打開游標(biāo)。③從游標(biāo)的結(jié)果集中讀取數(shù)據(jù)。從游標(biāo)中檢索一行或多行數(shù)據(jù)稱為取數(shù)據(jù)。④對(duì)游標(biāo)中的數(shù)據(jù)逐行操作。⑤關(guān)閉和釋放游標(biāo)。1３.２游標(biāo)的使用２．游標(biāo)的定義及使用過程1）.聲明游標(biāo)聲明游標(biāo)是指用DECLARE語(yǔ)句聲明或創(chuàng)建一個(gè)游標(biāo)。聲明游標(biāo)的語(yǔ)法如下：DECLAREcursor_name[SCROLL]CURSORFORselect_statement[FOR{READONLY|UPDATE[OFcolumn_name_list]}]其中：vcursor_name:是游標(biāo)的名字，為一個(gè)合法的SQLServer標(biāo)識(shí)符，游標(biāo)的名字必須遵循SQLServer命名規(guī)范。vSCROLL：表示取游標(biāo)時(shí)可以使用關(guān)鍵字NEXT、PRIOR、vvFIRST、LAST、ABSOLUTE、RELATIVE。每個(gè)關(guān)鍵字的含義將在介紹FETCH子句時(shí)講解。vselect_statement：是定義游標(biāo)結(jié)果集的標(biāo)準(zhǔn)SELECT語(yǔ)句，它可以是一個(gè)完整語(yǔ)法和語(yǔ)義的Transact-SQL的SELECT語(yǔ)句。13.2游標(biāo)的使用但是這個(gè)SELECT語(yǔ)句必須有FROM子句，不允許使用關(guān)鍵字COMPUTE、COMPUTEBY、FORBROWSE和INTO。vFORREADONLY：指出該游標(biāo)結(jié)果集只能讀，不能修改。vFORUPDATE：指出該游標(biāo)結(jié)果集可以被修改。vOFcolumn_name_list：列出可以被修改的列的名單。應(yīng)該注意:v游標(biāo)有且只有兩種方式：FORREADONLY或FORUPDATE。v當(dāng)游標(biāo)方式指定為FORREADONLY時(shí)，游標(biāo)涉及的表不能被修改。v當(dāng)游標(biāo)方式指定為FORUPDATE時(shí)，可以刪除或更新游標(biāo)涉及的表中的行。通常，這也是缺省方式，即不指定游標(biāo)方式時(shí)為FORUPDATE方式。v聲明游標(biāo)的DECLARECURSOR語(yǔ)句必須是在該游標(biāo)的任何OPEN語(yǔ)句之前。。2）.打開游標(biāo)打開游標(biāo)是指打開已被聲明但尚未被打開的游標(biāo)，打開游標(biāo)使用OPEN語(yǔ)句。打開游標(biāo)的語(yǔ)法如下：OPENcursor_name其中：vcursor_name是一個(gè)已聲明的尚未打開的游標(biāo)名。注意：v當(dāng)游標(biāo)打開成功時(shí)，游標(biāo)位置指向結(jié)果集的第一行之前。v只能打開已經(jīng)聲明但尚未打開的游標(biāo)。3）.從打開的游標(biāo)中提取行游標(biāo)被打開后，游標(biāo)位置位于結(jié)果集的第一行前，此時(shí)可以從結(jié)果集中提取（FETCH）行。SQLServer將沿著游標(biāo)結(jié)果集一行或多行向下移動(dòng)游標(biāo)位置，不斷提取結(jié)果集中的數(shù)據(jù)，并修改和保存游標(biāo)當(dāng)前的位置，直到結(jié)果集中的行全部被提取。從打開的游標(biāo)中提取行的語(yǔ)法如下：FETCH[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE|RELATIVE]FROM]cursor_name[INTOfetch_target_list]其中：vcursor_name：為一已聲明并已打開的游標(biāo)名字。NEXT|PRIOR|FIRST|LAST|ABSOLUTE|RELATIVE：游標(biāo)移動(dòng)方向，缺省情況下是NEXT，即向下移動(dòng)。vNEXT：取下一行數(shù)據(jù)。vPRIOR：取前一行數(shù)據(jù)。vFIRST：取第一行數(shù)據(jù)。LAST：取最后一行數(shù)據(jù)。vABSOLUTE：按絕對(duì)位置取數(shù)據(jù)。vRELATIVE：按相對(duì)位置取數(shù)據(jù)。另外，有２個(gè)全局變量@@FETCH_STATUS和@@rowcount提供關(guān)于游標(biāo)活動(dòng)的信息4）.關(guān)閉游標(biāo)關(guān)閉(Close)游標(biāo)是停止處理定義游標(biāo)的那個(gè)查詢。關(guān)閉游標(biāo)并不改變它的定義，可以再次用open語(yǔ)句打開它，SQLServer會(huì)用該游標(biāo)的定義重新創(chuàng)建這個(gè)游標(biāo)的一個(gè)結(jié)果集。關(guān)閉游標(biāo)的語(yǔ)法如下：CLOSEcursor_name其中：cursor_name：是已被打開并將要被關(guān)閉的游標(biāo)名字。v在如下情況下，SQLServer會(huì)自動(dòng)地關(guān)閉已打開的游標(biāo)：當(dāng)你退出這個(gè)SQLServer會(huì)話時(shí)從聲明游標(biāo)的存儲(chǔ)過程中返回時(shí)5).釋放游標(biāo)釋放(Deallocate)游標(biāo)是指釋放所有分配給此游標(biāo)的資源，包括該游標(biāo)的名字。釋放游標(biāo)的語(yǔ)法是：DEALLOCATECURSORcursor_name其中：vcursor_name：將要被DEALLOCATE釋放的游標(biāo)名字。如果釋放一個(gè)已打開但未被關(guān)閉的游標(biāo)，SQLServer會(huì)自動(dòng)先關(guān)閉這個(gè)游標(biāo)，然后再釋放它。v關(guān)閉游標(biāo)與釋放游標(biāo)的區(qū)別：關(guān)閉游標(biāo)并不改變游標(biāo)的定義，一個(gè)游標(biāo)關(guān)閉后，不需要再次聲明，就可以重新打開并使用它。但一個(gè)游標(biāo)釋放后，就釋放了與該游標(biāo)有關(guān)的一切資源，也包括游標(biāo)的聲明，游標(biāo)釋放后就不能再使用該游標(biāo)了，如需再次使用游標(biāo)，就必須重新定義。13.3使用游標(biāo)修改數(shù)據(jù)13.3.1更新數(shù)據(jù)通過在UPDATE語(yǔ)句中使用游標(biāo)可以更新表或視圖中的行。被更新的行依賴于游標(biāo)位置的當(dāng)前值。更新數(shù)據(jù)語(yǔ)法形式如下：UPDATE{table_name|view_name}SET[[{table_name.|view_name.}]column_name={new_value}[...n]WHERECURRENTOFcursor_name其中：v緊跟UPDATE之后的table_name|view_name：要更新的表名或視圖名，可以加或不加限定。但它必須是聲明該游標(biāo)的SELECT語(yǔ)句中的表名或視圖名。vcolumn_name：是要更新的列的列名，可以加或不加限定。但它們必須是聲明游標(biāo)的SELECT語(yǔ)句中UPDATEOFcolumn_name_list的子集vnew_value：為被更新列的新值，它可以是一個(gè)表達(dá)式、空值或子查詢。vWHERECURRENTOF：使SQLServer只更新由指定游標(biāo)的游標(biāo)位置當(dāng)前值確定的行。vcursor_name：是已聲明為FORUPDATE方式并已打開的游標(biāo)名。注意：①使用UPDATE...CURRENTOF語(yǔ)句一次只能更新當(dāng)前游標(biāo)位置確定的那一行，OPEN語(yǔ)句將游標(biāo)位置定位在結(jié)果集第一行前，可以使用FETCH語(yǔ)句把游標(biāo)位置定位在要被更新的數(shù)據(jù)行處。②用UPDATE...WHERECURRENTOF語(yǔ)句更新表中的行時(shí)，不會(huì)移動(dòng)游標(biāo)位置，被更新的行可以再次被修改，直到下一個(gè)FETCH語(yǔ)句的執(zhí)行。③UPDATE...WHERECURRENTOF語(yǔ)句可以更新多表視圖或被連接的多表，但只能更新其中一個(gè)表的行，即所有被更新的列都來自同一個(gè)表。注意：①使用游標(biāo)的DELETE語(yǔ)句，一次只能刪除當(dāng)前游標(biāo)位置確定的那一行。OPEN語(yǔ)句將游標(biāo)位置定位在結(jié)果集第一行之前，可以用FETCH語(yǔ)句把游標(biāo)位置定位在要被刪除的行處。②在DELETE語(yǔ)句中使用的游標(biāo)必須聲明為FORUPDATE方式。而且聲明游標(biāo)的SELECT語(yǔ)句中不能含有連接操作或涉及多表視圖，否則即使聲明中指明了FORUPDATE方式，也不能刪除其中的行。③對(duì)使用游標(biāo)刪除行的表，要求有一個(gè)唯一索引。④使用游標(biāo)的DELETE語(yǔ)句，刪除一行后將游標(biāo)位置向前移動(dòng)一行。13.4事務(wù)的使用1.事務(wù)的概念所謂事務(wù)(Transaction),是指一個(gè)操作序列，這些操作序列要么都被執(zhí)行，要么都不被執(zhí)行，它是一個(gè)不可分割的工作單元。2.事務(wù)的特點(diǎn) 在多用戶DBMS中，“事務(wù)”是個(gè)十分重要的概念。它是保持?jǐn)?shù)據(jù)一致性及可恢復(fù)性的基本工作單位。事務(wù)的主要特點(diǎn)是：v一致性：它保證并發(fā)用戶讀取數(shù)據(jù)的一致性。v隔離性：事務(wù)內(nèi)部的操作及使用的數(shù)據(jù)對(duì)并發(fā)的其他事務(wù)是隔離的。v可恢復(fù)性：DBMS利用事務(wù)日志能對(duì)事務(wù)進(jìn)行自動(dòng)恢復(fù)。3.事務(wù)的管理v在SQLServer中，對(duì)事務(wù)的管理包含三個(gè)方面：事務(wù)控制語(yǔ)句：控制事務(wù)執(zhí)行的語(yǔ)句。包括將一系列操作定義為一個(gè)工作單元來處理。鎖機(jī)制：封鎖正被一個(gè)事務(wù)修改的數(shù)據(jù)，防止其他用戶訪問到“不一致”的數(shù)據(jù)。事務(wù)日志：使事務(wù)具有可恢復(fù)性。v為了盡可能避免死鎖的出現(xiàn)，應(yīng)注意：在所有的事務(wù)中都按同一順序來訪問各個(gè)表。盡可能利用存儲(chǔ)過程來完成一個(gè)事務(wù)，以保證對(duì)各表的訪問次序都是一致的。事務(wù)應(yīng)該盡量小且應(yīng)盡快提交。避免人工輸入操作出現(xiàn)在事務(wù)中。避免并發(fā)地執(zhí)行許多像INSERT、UPDATE、DELETE這類數(shù)據(jù)修改語(yǔ)句13.4.3事務(wù)控制語(yǔ)句在SQLServer中，對(duì)事務(wù)的管理是通過事務(wù)控制語(yǔ)句和幾個(gè)全局變量結(jié)合起來實(shí)現(xiàn)的。1.事務(wù)控制語(yǔ)句SAVETRAN：后跟保存點(diǎn)名，表示設(shè)置保存點(diǎn)vBEGINTRAN[tran_name]：標(biāo)識(shí)一個(gè)用戶定義的事務(wù)的開始。tran_name為事務(wù)的名字，標(biāo)識(shí)一個(gè)事務(wù)開始。vCOMMITTRAN[tran_name]：表示提交事務(wù)中的一切操作，結(jié)束一個(gè)用戶定義的事務(wù)。使得對(duì)數(shù)據(jù)庫(kù)的改變生效。vROLLBACKTRAN[tran_name|save_name]：回退一個(gè)事務(wù)到事務(wù)的開頭或一個(gè)保存點(diǎn)。表示要撤消該事務(wù)已做的操作，回滾到事務(wù)開始前或保存點(diǎn)前的狀態(tài)。vSAVETRANsave_name：在事務(wù)中設(shè)置一個(gè)保存點(diǎn)。它可以使一個(gè)事務(wù)內(nèi)的部分操作回退。COMMITTRAN/*提交A組語(yǔ)句，且若未回退B組語(yǔ)句則提交B組語(yǔ)句*/4.在事務(wù)中不能包含的語(yǔ)句注意，事務(wù)中不能包含如下語(yǔ)句：CREATEDATABASEALTERDATABASEBACKUPLOGDROPDATABASERECONFIGURERESTOREDATABASERESTORELOGUPDATESTATISTICS5.事務(wù)使用示例【例】使用事務(wù)向表book中插入數(shù)據(jù)。USEjwglGOBEGINTRANtran_exampINSERTINTObook(book_id,book_name,publish_company)VALUES('dep04_s006_01‘,'VFP程序設(shè)計(jì)‘,'南京大學(xué)出版社‘)SAVETRANint_pointINSERTINTOboo