數(shù)據(jù)庫(kù)原理與應(yīng)用：第11章 事務(wù)和游標(biāo)的創(chuàng)建和使用

1、數(shù)據(jù)庫(kù)原理與應(yīng)用數(shù)據(jù)庫(kù)原理與應(yīng)用第十一講第十一講 事務(wù)和游標(biāo)的創(chuàng)建和使用事務(wù)和游標(biāo)的創(chuàng)建和使用 創(chuàng)建事務(wù)提交事務(wù)回滾事務(wù)回滾事務(wù)的一部分用鎖理解死鎖聲明游標(biāo)打開(kāi)游標(biāo)從游標(biāo)取數(shù)據(jù)關(guān)閉游標(biāo)本講主要內(nèi)容例：一個(gè)員工代碼為一個(gè)員工代碼為000002的內(nèi)部候選人已經(jīng)被選定的內(nèi)部候選人已經(jīng)被選定為為Sales Manager（ 崗位代碼為崗位代碼為0001）崗位。這）崗位。這必須在必須在Employee表中被更新，當(dāng)前已在該崗位的人表中被更新，當(dāng)前已在該崗位的人數(shù)也需要在數(shù)也需要在Position表中更新。表中更新。上面要做的上面要做的UPDATE語(yǔ)句如下：語(yǔ)句如下：UPDATE EmployeeSET

2、cCurrentPosition = 0001WHERE cEmployeeCode= 000002UPDATE PositionSET iCurrentStrength=iCurrentStrength + 1WHERE cPositionCode=0001 系統(tǒng)崩潰是由于兩個(gè)更新之間導(dǎo)致數(shù)據(jù)不一系統(tǒng)崩潰是由于兩個(gè)更新之間導(dǎo)致數(shù)據(jù)不一致性而引起的。需要防止這種情況，要確保兩個(gè)致性而引起的。需要防止這種情況，要確保兩個(gè)更新或者都發(fā)生或者都不發(fā)生。更新或者都發(fā)生或者都不發(fā)生。問(wèn)題：?jiǎn)栴}： 怎樣防止數(shù)據(jù)的不一致性？ 執(zhí)行事務(wù) 驗(yàn)證數(shù)據(jù)在兩個(gè)表中都已更新怎樣防止數(shù)據(jù)的不一致性？事務(wù)事務(wù)一個(gè)事務(wù)可以被

3、定義為作為工作的單個(gè)的邏輯單元被一起執(zhí)行的一串的操作。單個(gè)的工作單元必須具有稱為ACID（原子性，一致性，獨(dú)立性，和持久性）的四個(gè)性質(zhì)原子性原子性一致性一致性獨(dú)立性獨(dú)立性持久性持久性怎樣防止數(shù)據(jù)的不一致性怎樣防止數(shù)據(jù)的不一致性(續(xù)續(xù)) 為實(shí)現(xiàn)ACID性質(zhì)的需求，SQL Server提供了下面的特性:3事務(wù)管理3上鎖3日志 事務(wù)日志-是SQL Server管理所有它的的事務(wù)而維護(hù)的日志 顯式事務(wù)-是事務(wù)的開(kāi)始和結(jié)束都被顯式地定義的事務(wù)。怎樣防止數(shù)據(jù)的不一致性(續(xù))BEGIN TRANSACTION:該該語(yǔ)句標(biāo)志顯示事務(wù)的開(kāi)始語(yǔ)句標(biāo)志顯示事務(wù)的開(kāi)始語(yǔ)法BEGIN TRANSACTION trans

4、action_name | tran_name_variableCOMMIT TRANSACTION或或 COMMIT WORK:語(yǔ)句標(biāo)志顯語(yǔ)句標(biāo)志顯式事務(wù)的結(jié)束點(diǎn)式事務(wù)的結(jié)束點(diǎn)語(yǔ)法 COMMIT TRANSACTIONtransaction_name |tran_name_variable怎樣防止數(shù)據(jù)的不一致性(續(xù))Autocommit事務(wù)事務(wù)autocommit模式是SQL Server的缺省事務(wù)管理模式，當(dāng)使用SET AUTOCOMMIT OFF開(kāi)啟事務(wù)支持時(shí)，所有insert，delete或update操作列表存儲(chǔ)在內(nèi)存中，因此，當(dāng)進(jìn)行ROLLBACK事務(wù)時(shí)，這些操作能夠被撤銷(xiāo)?？缭綆?/p>

5、百個(gè)數(shù)據(jù)改變的事務(wù)將會(huì)花費(fèi)很多的內(nèi)存知道下一個(gè)COMMIT或ROLLBACK清除了操作列表。結(jié)果：結(jié)果：事務(wù)的使用可以避免數(shù)據(jù)的不一致性UPDATE語(yǔ)句可通過(guò)使用BEGIN TRANSACTION和COMMIT TRANSACTION語(yǔ)句來(lái)維持其原子性執(zhí)行事務(wù)動(dòng)作：動(dòng)作：在 Query Analyzer窗口中，鍵入： BEGIN TRANSACTION trnUpdatePositionUPDATE Employee SET cCurrentPosition = 0001 WHERE cEmployeeCode= 000002 UPDATE Position SET iCurrentStren

6、gth = iCurrentStrength + 1 WHERE cPositionCode = 0001 COMMIT TRANSACTION trnUpdatePosition驗(yàn)證兩個(gè)表中的數(shù)據(jù)都已被更新動(dòng)作：動(dòng)作：下面的SELECT語(yǔ)句來(lái)驗(yàn)證那些行已被更新：SELECT * FROM PositionWHERE cPositionCode = 0001 SELECT * FROM Employee WHERE cEmployeeCode = 000002 識(shí)別下面單個(gè)工作單元的性質(zhì)：識(shí)別下面單個(gè)工作單元的性質(zhì)：由并發(fā)事務(wù)引起的任何數(shù)據(jù)修改必須與其他并發(fā)事務(wù)所作的修改隔離開(kāi)來(lái)所有的數(shù)據(jù)修改

7、都被執(zhí)行或者都沒(méi)有被執(zhí)行已完成事務(wù)的任何數(shù)據(jù)改變?cè)谙到y(tǒng)中永久起作用事務(wù)成功地完成后所有的數(shù)據(jù)必須處于一致的狀態(tài)2.回復(fù)改變職位職位0015已經(jīng)招募了已經(jīng)招募了10個(gè)候選人。為了反映這一變個(gè)候選人。為了反映這一變化，對(duì)于化，對(duì)于RequisitionCode 0015 ，Requisition表的表的NuOfRequir屬性將會(huì)減少屬性將會(huì)減少10。還有，對(duì)于。還有，對(duì)于cPositionCode 0015，Position表的表的iCurrentStrength屬屬性要加性要加10，使用下面命令：，使用下面命令：UPDATE Requisitionset NuOfRequir = NuOfRe

8、quir - 10WHERE RequisitionCode=0015 2.回復(fù)改變(續(xù))UPDATE Positionset iCurrentStrength=iCurrentStrength + 10WHERE cPositionCode=0015這兩個(gè)語(yǔ)句都應(yīng)該是原子的，如果這兩個(gè)語(yǔ)句都應(yīng)該是原子的，如果iCurrentStrength屬屬性變得不只是性變得不只是iCurrentStrength屬性，那么，由屬性，那么，由UPDATE語(yǔ)句所作的改變必須回復(fù)。語(yǔ)句所作的改變必須回復(fù)。 識(shí)別怎樣恢復(fù)所作的改變 執(zhí)行事務(wù) 驗(yàn)證事務(wù)是否被執(zhí)行如何回復(fù)所作的改變?ROLLBACK TRANSACT

9、ION或或 ROLLBACK WORK:這些這些語(yǔ)句把顯式的或隱式的事務(wù)回滾到語(yǔ)句把顯式的或隱式的事務(wù)回滾到事務(wù)的開(kāi)始，或者回滾到事務(wù)內(nèi)的事務(wù)的開(kāi)始，或者回滾到事務(wù)內(nèi)的保存點(diǎn)保存點(diǎn)語(yǔ)法ROLLBACK TRANSACTION transaction_name |tran_name_variable |savepoint_name |savepoint_variable結(jié)果結(jié)果事務(wù)可用ROLLBACK TRANSACTION語(yǔ)句回復(fù)BEGIN TRANSACTIONUPDATE RequisitionSET NuOfRequir = NuOfRequir - 10WHERE cRequisiti

10、onCode=0015UPDATE PositionSET iCurrentStrength=iCurrentStrength + 10WHERE cPositionCode=0015IF (SELECT iMaxStrength-iCurrentStrength FROM Position WHERE cPositionCode = 0015) 0 BEGINPRINT Current strength cannot be more than Max strength. Transaction has not been committed. ROLLBACK TRANSACTION ENDE

11、LSE BEGINPRINT The transaction has been committed.COMMIT TRANSACTION END驗(yàn)證事務(wù)是否被執(zhí)行動(dòng)作：動(dòng)作：查看結(jié)果窗口中所顯示的輸出。3.回復(fù)事務(wù)的一部分Employee和和Position表需要用下面的事務(wù)來(lái)更新：表需要用下面的事務(wù)來(lái)更新：事務(wù)事務(wù)1:UPDATE EmployeeSET cCurrentPosition = 0015WHERE cEmployeeCode = 000002UPDATE PositionSET iCurrentStrength = iCurrentStrength + 1WHERE cPosi

12、tionCode = 0015Requisition 和和Position表需要使用下面的事務(wù)來(lái)更新：表需要使用下面的事務(wù)來(lái)更新： 事務(wù)事務(wù) 2: UPDATE Requisition SET NuOfRequir = NuOfRequir - 10 WHERE cRequisitionCode=0015UPDATE Position SET iCurrentStrength=iCurrentStrength + 10 WHERE cPositionCode=00153回復(fù)事務(wù)的一部分(續(xù))所有更新應(yīng)一起來(lái)做。對(duì)于所有更新應(yīng)一起來(lái)做。對(duì)于cPositionCode 0015，如果，如果iCur

13、rentStrength值大于值大于iMaxStrength值，由第二個(gè)事務(wù)所產(chǎn)值，由第二個(gè)事務(wù)所產(chǎn)生的改變必須被回復(fù)，而由第一個(gè)事務(wù)產(chǎn)生的改變是允許生的改變必須被回復(fù)，而由第一個(gè)事務(wù)產(chǎn)生的改變是允許的。的。 識(shí)別怎樣把事務(wù)分解成部分 執(zhí)行事務(wù) 驗(yàn)證事務(wù)的執(zhí)行怎樣把事務(wù)分解成部分?保存事務(wù)保存事務(wù)它在事務(wù)內(nèi)設(shè)置保存點(diǎn) 。保存點(diǎn)把事務(wù)分成幾個(gè)邏輯單元，這樣事務(wù)可以返回到保存點(diǎn)，如果事務(wù)的一部分是有條件地被取消。語(yǔ)法SAVE TRANSACTION savepoint_name | savepoint_variable結(jié)果結(jié)果事務(wù)可以用SAVE TRANSACTION語(yǔ)句分解成幾個(gè)邏輯單元保存點(diǎn)提

14、供了一種機(jī)制，用于回滾部分事務(wù)?？梢允褂帽４纥c(diǎn)提供了一種機(jī)制，用于回滾部分事務(wù)?？梢允褂?SAVE TRANSACTION savepoint_name 語(yǔ)句創(chuàng)建一個(gè)保存點(diǎn)，然后再執(zhí)語(yǔ)句創(chuàng)建一個(gè)保存點(diǎn)，然后再執(zhí)行行 ROLLBACK TRANSACTION savepoint_name 語(yǔ)句回滾到該語(yǔ)句回滾到該保存點(diǎn)，從而無(wú)須回滾到事務(wù)的開(kāi)始。保存點(diǎn)，從而無(wú)須回滾到事務(wù)的開(kāi)始。在不可能發(fā)生錯(cuò)誤的情況下，保存點(diǎn)很有用。在很少出現(xiàn)錯(cuò)誤在不可能發(fā)生錯(cuò)誤的情況下，保存點(diǎn)很有用。在很少出現(xiàn)錯(cuò)誤的情況下使用保存點(diǎn)回滾部分事務(wù)，比讓每個(gè)事務(wù)在更新之前測(cè)試的情況下使用保存點(diǎn)回滾部分事務(wù)，比讓每個(gè)事務(wù)在更新之前

15、測(cè)試更新的有效性更為有效。更新和回滾操作代價(jià)很大，因此只有在遇更新的有效性更為有效。更新和回滾操作代價(jià)很大，因此只有在遇到錯(cuò)誤的可能性很小，而且預(yù)先檢查更新的有效性的代價(jià)相對(duì)很高到錯(cuò)誤的可能性很小，而且預(yù)先檢查更新的有效性的代價(jià)相對(duì)很高的情況下，使用保存點(diǎn)才會(huì)非常有效。的情況下，使用保存點(diǎn)才會(huì)非常有效。BEGIN TRANSACTION UPDATE Employee SET cCurrentPosition = 0015 WHERE cEmployeeCode = 000002UPDATE Position SET iCurrentStrength = iCurrentStrength +

16、1 WHERE cPositionCode = 0015 SAVE TRANSACTION trnTransaction1 UPDATE Requisition SET NuOfRequir=NuOfRequir - 10 WHERE cRequisitionCode=0015 UPDATE Position SET iCurrentStrength=iCurrentStrength+10 WHERE cPositionCode=0015IF (SELECT iMaxStrength-iCurrentStrength FROM Position WHERE cPositionCode = 00

17、15) 80 UPDATE ExternalCandidate SET dInterviewDate = getdate()+ 2 WHERE siTestScore 80 COMMIT TRANSACTIONUser2為什么不能執(zhí)行此事務(wù)?為什么User2不能執(zhí)行此事務(wù)?上鎖上鎖確保事務(wù)的完整性和數(shù)據(jù)庫(kù)的一致性是自動(dòng)實(shí)施的不上鎖，查看事務(wù)處理是不可能的。為什么為什么User2不能執(zhí)行此事務(wù)不能執(zhí)行此事務(wù)(續(xù)續(xù))*事務(wù)的并發(fā)性3 SQL Server提供了樂(lè)觀的和悲觀的并發(fā)性控件3 樂(lè)觀并發(fā)性控件建立在多用戶間資源沖突大概是不可能的假設(shè)的基礎(chǔ)上允許事務(wù)執(zhí)行不用鎖定任何資源只有在提交事務(wù)時(shí)才進(jìn)行

18、資源檢查為什么User2不能執(zhí)行此事務(wù)(續(xù))3 悲觀并發(fā)性悲觀并發(fā)性控件控件 在事務(wù)處理期間的鎖定資源并發(fā)性問(wèn)題并發(fā)性問(wèn)題丟失更新 丟失更新問(wèn)題發(fā)生在當(dāng)兩個(gè)或多個(gè)事務(wù)基于原先所選值試圖修改同一丟失更新問(wèn)題發(fā)生在當(dāng)兩個(gè)或多個(gè)事務(wù)基于原先所選值試圖修改同一行的時(shí)候行的時(shí)候自由依賴性自由依賴性自由依賴性問(wèn)題問(wèn)題（uncommitted dependency ）也稱為）也稱為無(wú)效讀入（無(wú)效讀入（dirty read）問(wèn)題問(wèn)題為什么User2不能執(zhí)行此事務(wù)(續(xù))不一致性分析不一致性分析不一致性分析問(wèn)題又稱為問(wèn)題又稱為 不可重復(fù)不可重復(fù)問(wèn)題問(wèn)題幻象讀取幻象讀取幻象讀取又稱為作幻象問(wèn)題又稱為作幻象問(wèn)題為什么

19、User2不能執(zhí)行此事務(wù)(續(xù))SQL Server鎖模型鎖模型共享鎖允許并發(fā)事務(wù)來(lái)讀取資源允許并發(fā)事務(wù)來(lái)讀取資源更新鎖避免了常見(jiàn)形式的死瑣發(fā)生避免了常見(jiàn)形式的死瑣發(fā)生互斥型鎖唯一地限制并發(fā)事務(wù)訪問(wèn)一個(gè)資源唯一地限制并發(fā)事務(wù)訪問(wèn)一個(gè)資源為什么User2不能執(zhí)行此事務(wù)(續(xù))意向鎖指示指示SQL Server要在層次結(jié)構(gòu)較低的某個(gè)資源上獲得一個(gè)共享或排它鎖要在層次結(jié)構(gòu)較低的某個(gè)資源上獲得一個(gè)共享或排它鎖模式鎖當(dāng)任何數(shù)據(jù)定義（當(dāng)任何數(shù)據(jù)定義（DDL）操作在表上執(zhí)行時(shí)，）操作在表上執(zhí)行時(shí)，SQL Server考察模式修改考察模式修改 (Sch-M) 鎖鎖為什么User2不能執(zhí)行此事務(wù)(續(xù))結(jié)果：結(jié)果：U

20、ser2不能執(zhí)行他的事務(wù)，因?yàn)楫?dāng)它正在被User1使用時(shí)SQL Server已經(jīng)鎖定了ExternalCandidate表死鎖死鎖是這樣一種情形：兩個(gè)用戶（或事務(wù)）在個(gè)別的對(duì)象的上死鎖是這樣一種情形：兩個(gè)用戶（或事務(wù)）在個(gè)別的對(duì)象的上鎖，并且每個(gè)用戶正在等待另一個(gè)對(duì)象的鎖鎖，并且每個(gè)用戶正在等待另一個(gè)對(duì)象的鎖DISTRIBUTORPRODUCTSTRANSACTION ATRANSACTION B死鎖(續(xù))設(shè)置死鎖優(yōu)選級(jí)設(shè)置死鎖優(yōu)選級(jí)為探測(cè)死鎖的情況，SQL Server掃描在等待鎖請(qǐng)求的會(huì)話SQL Server提供SET DEADLOCK_PRIORITY命令來(lái)定制死鎖語(yǔ)法SET DEADL

21、OCK_PRIORITY LOW|NORMAL|deadlock_var控制在發(fā)生死鎖情況時(shí)會(huì)話的反應(yīng)方式。如果兩個(gè)進(jìn)程都鎖定數(shù)據(jù)，并且直到其它進(jìn)程釋放自己的鎖時(shí)，每個(gè)進(jìn)程才能釋放自己的鎖，即發(fā)生死鎖情況。LOW指定當(dāng)前會(huì)話為首選死鎖犧牲品。Microsoft SQL Server 自動(dòng)回滾死鎖犧牲品的事務(wù)，并給客戶端應(yīng)用程序返回 1205 號(hào)死鎖錯(cuò)誤信息。NORMAL指定會(huì)話返回到默認(rèn)的死鎖處理方法。定制定制LOCK_TIMEOUT SET LOCK_TIMEOUT命令可被用來(lái)設(shè)置等待被阻塞資源語(yǔ)句的最長(zhǎng)時(shí)間游標(biāo)的定義及其優(yōu)點(diǎn) 前面介紹的數(shù)據(jù)檢索方法可以得到數(shù)據(jù)庫(kù)中有關(guān)表的數(shù)據(jù)，但這些數(shù)據(jù)是

22、作為一個(gè)結(jié)果集得到的，用戶可以把這個(gè)結(jié)果集保存到一個(gè)文件里，或生成一個(gè)新表以便于以后使用。這種查詢是非常重要的。但這種查詢形式有一個(gè)很大的缺點(diǎn)，它不能對(duì)結(jié)果集中每一行的數(shù)據(jù)進(jìn)行處理。使用游標(biāo)可以實(shí)現(xiàn)對(duì)查詢結(jié)果集中的數(shù)據(jù)逐行處理。游標(biāo)的概念 游標(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)用程序中或其它地方。游標(biāo)游標(biāo)游標(biāo)是一個(gè)在給定是一個(gè)在給定結(jié)果集結(jié)果集中幫助訪問(wèn)和操縱數(shù)據(jù)中幫助

23、訪問(wèn)和操縱數(shù)據(jù)的數(shù)據(jù)庫(kù)對(duì)象的數(shù)據(jù)庫(kù)對(duì)象游標(biāo)能以下列方式處理結(jié)果集中的行：游標(biāo)能以下列方式處理結(jié)果集中的行：允許從結(jié)果集中檢索指定的行允許結(jié)果集中當(dāng)前行被修改幫助從結(jié)果集中當(dāng)前行導(dǎo)航到不同的行允許被其它用戶修改的數(shù)據(jù)在結(jié)果集中是可見(jiàn)的使用游標(biāo)的步驟使用游標(biāo)的步驟有如下幾個(gè)步驟：有如下幾個(gè)步驟： 創(chuàng)建游標(biāo)。使用創(chuàng)建游標(biāo)。使用T-SQL語(yǔ)句生成一個(gè)結(jié)果集，并且定義游標(biāo)的特征，如游標(biāo)語(yǔ)句生成一個(gè)結(jié)果集，并且定義游標(biāo)的特征，如游標(biāo)中的記錄是否可以修改。中的記錄是否可以修改。 打開(kāi)游標(biāo)打開(kāi)游標(biāo) 從游標(biāo)的結(jié)果集中讀取數(shù)據(jù)。從游標(biāo)中檢索一行或多行數(shù)據(jù)稱為取數(shù)據(jù)。從游標(biāo)的結(jié)果集中讀取數(shù)據(jù)。從游標(biāo)中檢索一行或多行

24、數(shù)據(jù)稱為取數(shù)據(jù)。 對(duì)游標(biāo)中的數(shù)據(jù)逐行操作。對(duì)游標(biāo)中的數(shù)據(jù)逐行操作。 關(guān)閉和釋放游標(biāo)。關(guān)閉和釋放游標(biāo)。5.把指定屬性作為變量顯示你需要召集所有部門(mén)頭目開(kāi)會(huì)。為此你需要部門(mén)你需要召集所有部門(mén)頭目開(kāi)會(huì)。為此你需要部門(mén)和相應(yīng)部門(mén)頭目的列表如下，其格式如下：和相應(yīng)部門(mén)頭目的列表如下，其格式如下： Department Name = Production Department Head = Samuel Moore Department Name = Sales Department Head = Donald Fleming . . 創(chuàng)建報(bào)告所需的步驟創(chuàng)建報(bào)告所需的步驟執(zhí)行創(chuàng)建報(bào)告所需的語(yǔ)句執(zhí)行創(chuàng)建報(bào)告

25、所需的語(yǔ)句按所需的結(jié)果驗(yàn)證其輸出按所需的結(jié)果驗(yàn)證其輸出游標(biāo)的定義及使用過(guò)程游標(biāo)的定義及使用過(guò)程1.聲明游標(biāo)聲明游標(biāo)聲明游標(biāo)是指用聲明游標(biāo)是指用DECLARE語(yǔ)句聲明或創(chuàng)建一個(gè)游標(biāo)語(yǔ)句聲明或創(chuàng)建一個(gè)游標(biāo)。聲明游標(biāo)的語(yǔ)法如下：聲明游標(biāo)的語(yǔ)法如下：DECLARE cursor_name SCROLL CURSORFOR select_statementFOR READ ONLY|UPDATEOF column_name_list其中：其中： cursor_name:cursor_name:是游標(biāo)的名字，為一個(gè)合法的是游標(biāo)的名字，為一個(gè)合法的SQL ServerSQL Server標(biāo)識(shí)符，游標(biāo)的名字必

26、須遵循標(biāo)識(shí)符，游標(biāo)的名字必須遵循SQL SQL ServerServer命名規(guī)范。命名規(guī)范。SCROLLSCROLL：表示取游標(biāo)時(shí)可以使用關(guān)鍵字表示取游標(biāo)時(shí)可以使用關(guān)鍵字NEXTNEXT、PRIORPRIOR、FIRSTFIRST、LASTLAST、ABSOLUTEABSOLUTE、RELATIVERELATIVE。每個(gè)關(guān)鍵字的含義將在介紹每個(gè)關(guān)鍵字的含義將在介紹FETCHFETCH子句時(shí)講解。子句時(shí)講解。select_statementselect_statement：是定義游標(biāo)結(jié)果集的標(biāo)準(zhǔn)是定義游標(biāo)結(jié)果集的標(biāo)準(zhǔn) SELECTSELECT語(yǔ)句，它可以是一個(gè)完整語(yǔ)法和語(yǔ)義的語(yǔ)句，它可以是一個(gè)完

27、整語(yǔ)法和語(yǔ)義的Transact-SQLTransact-SQL的的SELECTSELECT語(yǔ)句。語(yǔ)句。 但是這個(gè)但是這個(gè)SELECT語(yǔ)句必須有語(yǔ)句必須有FROM子句，不允許使用關(guān)鍵字子句，不允許使用關(guān)鍵字 COMPUTE、COMPUTE BY、FOR BROWSE 和和 INTO。FOR READ ONLY：指出該游標(biāo)結(jié)果集只能讀，不能修改。：指出該游標(biāo)結(jié)果集只能讀，不能修改。FOR UPDATE：指出該游標(biāo)結(jié)果集可以被修改。：指出該游標(biāo)結(jié)果集可以被修改。OF column_name_list：列出可以被修改的列的名單。：列出可以被修改的列的名單。應(yīng)該注意：應(yīng)該注意： 游標(biāo)有且只有兩種方式：游

28、標(biāo)有且只有兩種方式：FOR READ ONLY或或FOR UPDATE。 當(dāng)游標(biāo)方式指定為當(dāng)游標(biāo)方式指定為FOR READ ONLY時(shí)，游標(biāo)涉及的表不能被修改。時(shí)，游標(biāo)涉及的表不能被修改。 當(dāng)游標(biāo)方式指定為當(dāng)游標(biāo)方式指定為FOR UPDATE時(shí)，可以刪除或更新游標(biāo)涉及的表中的行。時(shí)，可以刪除或更新游標(biāo)涉及的表中的行。通常，通常，這也是缺省方式這也是缺省方式，即不指定游標(biāo)方式時(shí)為，即不指定游標(biāo)方式時(shí)為FOR UPDATE方式。方式。 聲明游標(biāo)的聲明游標(biāo)的DECLARE CURSOR 語(yǔ)句必須是在該游標(biāo)的任何語(yǔ)句必須是在該游標(biāo)的任何OPEN語(yǔ)句之前。語(yǔ)句之前。2. 打開(kāi)游標(biāo)打開(kāi)游標(biāo)打開(kāi)游標(biāo)是指打開(kāi)

29、已被聲明但尚未被打開(kāi)的游標(biāo)，打開(kāi)游標(biāo)使用打開(kāi)游標(biāo)是指打開(kāi)已被聲明但尚未被打開(kāi)的游標(biāo)，打開(kāi)游標(biāo)使用OPEN語(yǔ)句。語(yǔ)句。打開(kāi)游標(biāo)的語(yǔ)法如下：打開(kāi)游標(biāo)的語(yǔ)法如下： OPEN cursor_name其中：其中：cursor_name是一個(gè)已聲明的尚未打開(kāi)的游標(biāo)名。是一個(gè)已聲明的尚未打開(kāi)的游標(biāo)名。注意：注意： 當(dāng)游標(biāo)打開(kāi)成功時(shí)，游標(biāo)位置指向結(jié)果集的第一行之前。當(dāng)游標(biāo)打開(kāi)成功時(shí)，游標(biāo)位置指向結(jié)果集的第一行之前。 只能打開(kāi)已經(jīng)聲明但尚未打開(kāi)的游標(biāo)。只能打開(kāi)已經(jīng)聲明但尚未打開(kāi)的游標(biāo)。3. 從打開(kāi)的游標(biāo)中提取行從打開(kāi)的游標(biāo)中提取行游標(biāo)被打開(kāi)后，游標(biāo)位置位于結(jié)果集的第一行前，此時(shí)可以從結(jié)果集中提取（游標(biāo)被打開(kāi)后，

30、游標(biāo)位置位于結(jié)果集的第一行前，此時(shí)可以從結(jié)果集中提取（FETCH）行。）行。SQL Server將沿著游標(biāo)結(jié)果集一行或多行向下移動(dòng)游標(biāo)位置，不斷提取結(jié)果集中的數(shù)據(jù)，并修改和保存游將沿著游標(biāo)結(jié)果集一行或多行向下移動(dòng)游標(biāo)位置，不斷提取結(jié)果集中的數(shù)據(jù)，并修改和保存游標(biāo)當(dāng)前的位置，直到結(jié)果集中的行全部被提取。標(biāo)當(dāng)前的位置，直到結(jié)果集中的行全部被提取。從打開(kāi)的游標(biāo)中提取行的語(yǔ)法如下：從打開(kāi)的游標(biāo)中提取行的語(yǔ)法如下：FETCH NEXT|PRIOR|FIRST|LAST|ABSOLUTE|RELATIVE FROM cursor_name INTO fetch_target_list其中：其中： curs

31、or_name：為一已聲明并已打開(kāi)的游標(biāo)名字。：為一已聲明并已打開(kāi)的游標(biāo)名字。NEXT|PRIOR|FIRST|LAST|ABSOLUTE|RELATIVE：游標(biāo)移動(dòng)方向，缺省情況下是：游標(biāo)移動(dòng)方向，缺省情況下是NEXT，即向，即向下移動(dòng)。下移動(dòng)。NEXT：取下一行數(shù)據(jù)。：取下一行數(shù)據(jù)。PRIOR：取前一行數(shù)據(jù)。：取前一行數(shù)據(jù)。FIRST：取第一行數(shù)據(jù)。：取第一行數(shù)據(jù)。 LAST：取最后一行數(shù)據(jù)。ABSOLUTE：按絕對(duì)位置取數(shù)據(jù)。RELATIVE：按相對(duì)位置取數(shù)據(jù)。游標(biāo)位置確定了結(jié)果集中哪一行可以被提取，如果游標(biāo)方式為FOR UPDATE的話，也就確定該位置一行數(shù)據(jù)可以被更新或刪除。 INT

32、O fetch_target_list：指定存放被提取的列數(shù)據(jù)的目的變量清單。這個(gè)清單中變量的個(gè)數(shù)、數(shù)據(jù)類(lèi)型、順序必須與定義該游標(biāo)的select_statement的SELECT_list中列出的列清單相匹配。為了更靈活地操縱數(shù)據(jù)，可以把從已聲明并已打開(kāi)的游標(biāo)結(jié)果集中提取的列數(shù)據(jù)，分別存放在目的變量中。INTO fetch_target_list是T-SQL對(duì)ANSI-92 SQL標(biāo)準(zhǔn)的擴(kuò)充。 有兩個(gè)全局變量提供關(guān)于游標(biāo)活動(dòng)的信息： FETCH_STATUS 保存著最后FETCH語(yǔ)句執(zhí)行后的狀態(tài)信息，其值和含義如下：0 ：表示成功完成FETCH 語(yǔ)句。-1：表示FETCH語(yǔ)句執(zhí)行有錯(cuò)誤，或者當(dāng)

33、前游標(biāo)位置已在結(jié)果集中的最后一行，結(jié)果集中不再有數(shù)據(jù)。-2：提取的行不存在。 rowcount保存著自游標(biāo)打開(kāi)后的第一個(gè)保存著自游標(biāo)打開(kāi)后的第一個(gè)FETCH語(yǔ)句，直到最近一次的語(yǔ)句，直到最近一次的FETCH語(yǔ)句為止，已從游標(biāo)結(jié)果集中提取的行數(shù)。語(yǔ)句為止，已從游標(biāo)結(jié)果集中提取的行數(shù)。也就是說(shuō)它保存著任何時(shí)間點(diǎn)客戶機(jī)程序看到的已提取的總行數(shù)。一旦結(jié)果集中所有行都被提取，那么rowcount的值就是該結(jié)果集的總行數(shù)。每個(gè)打開(kāi)的游標(biāo)都與一特定的rowcount有關(guān)，關(guān)閉游標(biāo)時(shí)，該rowcount變量也被刪除。在FETCH語(yǔ)句執(zhí)行后查看這個(gè)變量，可得知從游標(biāo)結(jié)果集中已提取的行數(shù)。 4. 關(guān)閉游標(biāo)關(guān)閉游標(biāo)

34、關(guān)閉(Close)游標(biāo)是停止處理定義游標(biāo)的那個(gè)查詢。關(guān)閉游標(biāo)并不改變它的定義，可以再次用open語(yǔ)句打開(kāi)它，SQL Server會(huì)用該游標(biāo)的定義重新創(chuàng)建這個(gè)游標(biāo)的一個(gè)結(jié)果集。關(guān)閉游標(biāo)的語(yǔ)法如下：CLOSE cursor_name其中：cursor_name：是已被打開(kāi)并將要被關(guān)閉的游標(biāo)名字。在如下情況下，SQL Server會(huì)自動(dòng)地關(guān)閉已打開(kāi)的游標(biāo)：當(dāng)你退出這個(gè)SQL Server會(huì)話時(shí)從聲明游標(biāo)的存儲(chǔ)過(guò)程中返回時(shí)創(chuàng)建報(bào)告所需的步驟(續(xù))5.解除分配游標(biāo)解除分配游標(biāo)你可以抹去由DECLARE游標(biāo)語(yǔ)句定義的游標(biāo)的定義語(yǔ)法 DEALLOCATE cursor_name報(bào)告所需的步驟(續(xù))-7號(hào)你需

35、要用下面的語(yǔ)句來(lái)顯示報(bào)告。.-Create two variables that would store the -values returned by the fetch statement.DECLARE DepartmentName char(25)DECLARE DepartmentHead char(25)- Defines the cursor that can be used to - access the records of the table,row by row.DECLARE curDepartment cursor for SELECT vDepartmentName

36、,vDepartmentHead FROM Department- Open the cursorOPEN curDepartment- Fetch the rows into variablesFETCH curDepartment into DepartmentName, DepartmentHead- Start a loop to display all the rows of - the cursor.While (fetch_status = 0)BEGINPrint Department Name = + DepartmentNamePrint Department Head =

37、 + DepartmentHead- Fetch the next row from the cursor. FETCH curDepartment into DepartmentName, DepartmentHeadEND- Close the cursorCLOSE curDepartment- Deallocate the cursor.DEALLOCATE curDepartment定義一個(gè)游標(biāo)，將學(xué)生表student中所有學(xué)生的姓名、性別顯示出來(lái)。DECLARE student_name VARCHAR(8),student_sex VARCHAR(16)DECLARE stude

38、nt_coursor SCROLL CURSOR FOR SELECT name，sex FROM student FOR READ ONLYOPEN student_coursorFETCH student_coursor INTO student_name,student_sexWHILE FETCH_STATUS=0 BEGIN PRINT 學(xué)生姓名:+student_name+ +性別: + student_sex FETCH FROM student_coursor INTO student_name , student_sex ENDCLOSE student_coursorDEALLOCATE student_coursor 用戶可以在UPDATE或DELETE語(yǔ)句中使用游標(biāo)來(lái)更新、刪除表或視圖中的行，但不能用來(lái)插入新行。更新數(shù)據(jù)更新數(shù)據(jù)通過(guò)在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 WHERE CURRENT OF cursor_name使用游標(biāo)修改數(shù)據(jù)其中：其中： 緊跟UPDATE之后的table_nam