《《數(shù)據(jù)庫(kù)原理與應(yīng)用》11.事務(wù)和游標(biāo)的創(chuàng)建和使用》由會(huì)員分享，可在線閱讀，更多相關(guān)《《數(shù)據(jù)庫(kù)原理與應(yīng)用》11.事務(wù)和游標(biāo)的創(chuàng)建和使用（59頁(yè)珍藏版）》請(qǐng)?jiān)谘b配圖網(wǎng)上搜索。
1、 孫 發(fā) 勤 揚(yáng)州大學(xué)新聞與傳媒學(xué)院 YZU.SUN163.COM 數(shù)據(jù)庫(kù)原理與應(yī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è)員工代碼為 000002的內(nèi)部候選人已經(jīng)被選定 為 Sales Manager（ 崗位代碼為 0001）崗位。這 必須在 Employee表中被更新，當(dāng)前已在該崗位的 人數(shù)也需要在 Position表中更新。 上面要做的 UPDATE語(yǔ)句如下： UPDATE Employee SET cCurrentPosition = 0001 WHERE
2、cEmployeeCode= 000002 UPDATE Position SET iCurrentStrength=iCurrentStrength + 1 WHERE cPositionCode=0001 系統(tǒng)崩潰是由于兩個(gè)更新之間導(dǎo)致數(shù)據(jù)不一 致性而引起的。需要防止這種情況，要確保兩個(gè) 更新或者都發(fā)生或者都不發(fā)生。 問(wèn)題： 怎樣防止數(shù)據(jù)的不一致性？ 執(zhí)行事務(wù) 驗(yàn)證數(shù)據(jù)在兩個(gè)表中都已更新 怎樣防止數(shù)據(jù)的不一致性？ 事務(wù) 一個(gè)事務(wù)可以被定義為作為工作的單個(gè)的邏輯單元被一起執(zhí)行的 一串的操作。 單個(gè)的工作單元必須具有稱為 ACID（原子性，一致性，獨(dú)立性， 和持久性）的 四個(gè)性質(zhì) 原子性 一致
3、性 獨(dú)立性 持久性 怎樣防止數(shù)據(jù)的不一致性 (續(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ǔ)法 BEGIN TRANSACTION transaction_name | tran_name_variable COMMIT TRANSACTION或 COMMIT WORK:語(yǔ)句標(biāo)志顯 式事務(wù)的結(jié)束點(diǎn) 語(yǔ)法 COMMI
4、T TRANSACTIONtransaction_name |tran_name_variable 怎樣防止數(shù)據(jù)的不一致性 (續(xù) ) Autocommit事務(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í)，這些 操作能夠被撤銷?？缭綆装賯€(gè)數(shù)據(jù)改變的事務(wù)將會(huì)花費(fèi)很多的內(nèi) 存知道下一個(gè) COMMIT或 ROLLBACK清除了操作列表。 結(jié)果： 事務(wù)的使用可以避免數(shù)據(jù)的不一致性 UPDATE語(yǔ)句可通過(guò)使用
5、BEGIN TRANSACTION和 COMMIT TRANSACTION語(yǔ)句來(lái)維持其原子性 執(zhí)行事務(wù) 動(dòng)作： 在 Query Analyzer窗口中，鍵入： BEGIN TRANSACTION trnUpdatePosition UPDATE Employee SET cCurrentPosition = 0001 WHERE cEmployeeCode= 000002 UPDATE Position SET iCurrentStrength = iCurrentStrength + 1 WHERE cPositionCode = 0001 COMMIT TRANSACTION trnUpd
6、atePosition 驗(yàn)證兩個(gè)表中的數(shù)據(jù)都已被更新 動(dòng)作： 下面的 SELECT語(yǔ)句來(lái)驗(yàn)證那些行已被更新： SELECT * FROM Position WHERE cPositionCode = 0001 SELECT * FROM Employee WHERE cEmployeeCode = 000002 識(shí)別下面單個(gè)工作單元的性質(zhì)： 由并發(fā)事務(wù)引起的任何數(shù)據(jù)修改必須與其他并發(fā)事務(wù)所作的 修改隔離開(kāi)來(lái) 所有的數(shù)據(jù)修改都被執(zhí)行或者都沒(méi)有被執(zhí)行 已完成事務(wù)的任何數(shù)據(jù)改變?cè)谙到y(tǒng)中永久起作用 事務(wù)成功地完成后所有的數(shù)據(jù)必須處于一致的狀態(tài) 2.回復(fù)改變 職位 0015已經(jīng)招募了 10個(gè)候選人。為了
7、反映這一變 化，對(duì)于 RequisitionCode 0015 ， Requisition表的 NuOfRequir屬性將會(huì)減少 10。還有，對(duì)于 cPositionCode 0015， Position表的 iCurrentStrength屬 性要加 10，使用下面命令： UPDATE Requisition set NuOfRequir = NuOfRequir - 10 WHERE RequisitionCode=0015 2.回復(fù)改變 (續(xù) ) UPDATE Position set iCurrentStrength=iCurrentStrength + 10 WHERE cPosit
8、ionCode=0015 這兩個(gè)語(yǔ)句都應(yīng)該是原子的，如果 iCurrentStrength屬 性變得不只是 iCurrentStrength屬性，那么，由 UPDATE語(yǔ)句所作的改變必須回復(fù)。 識(shí)別怎樣恢復(fù)所作的改變 執(zhí)行事務(wù) 驗(yàn)證事務(wù)是否被執(zhí)行 如何回復(fù)所作的改變 ? ROLLBACK TRANSACTION或 ROLLBACK WORK:這些 語(yǔ)句把顯式的或隱式的事務(wù)回滾到 事務(wù)的開(kāi)始，或者回滾到事務(wù)內(nèi)的 保存點(diǎn) 語(yǔ)法 ROLLBACK TRANSACTION transaction_name |tran_name_variable |savepoint_name | savepoint_
9、variable 結(jié)果 事務(wù)可用 ROLLBACK TRANSACTION語(yǔ)句回復(fù) BEGIN TRANSACTION UPDATE Requisition SET NuOfRequir = NuOfRequir - 10 WHERE cRequisitionCode=0015 UPDATE Position SET iCurrentStrength=iCurrentStrength + 10 WHERE cPositionCode=0015 IF (SELECT iMaxStrength-iCurrentStrength FROM Position WHERE cPositionCode =
10、 0015) 0 BEGIN PRINT Current strength cannot be more than Max strength. Transaction has not been committed. ROLLBACK TRANSACTION END ELSE BEGIN PRINT The transaction has been committed. COMMIT TRANSACTION END 驗(yàn)證事務(wù)是否被執(zhí)行 動(dòng)作： 查看結(jié)果窗口中所顯示的輸出。 3.回復(fù)事務(wù)的一部分 Employee和 Position表需要用下面的事務(wù)來(lái)更新： 事務(wù) 1: UPDATE Employ
11、ee SET cCurrentPosition = 0015 WHERE cEmployeeCode = 000002 UPDATE Position SET iCurrentStrength = iCurrentStrength + 1 WHERE cPositionCode = 0015 Requisition 和 Position表需要使用下面的事務(wù)來(lái)更新： 事務(wù) 2: UPDATE Requisition SET NuOfRequir = NuOfRequir - 10 WHERE cRequisitionCode=0015 UPDATE Position SET iCurrentSt
12、rength=iCurrentStrength + 10 WHERE cPositionCode=0015 3回復(fù)事務(wù)的一部分 (續(xù) ) 所有更新應(yīng)一起來(lái)做。對(duì)于 cPositionCode 0015，如果 iCurrentStrength值大于 iMaxStrength值，由第二個(gè)事務(wù)所產(chǎn) 生的改變必須被回復(fù)，而由第一個(gè)事務(wù)產(chǎn)生的改變是允許 的。 識(shí)別怎樣把事務(wù)分解成部分 執(zhí)行事務(wù) 驗(yàn)證事務(wù)的執(zhí)行 怎樣把事務(wù)分解成部分 ? 保存事務(wù) 它在事務(wù)內(nèi)設(shè)置保存點(diǎn) 。保存點(diǎn)把事務(wù)分成幾個(gè)邏輯單元 ， 這樣事務(wù)可以返回到保存點(diǎn)，如果事務(wù)的一部分是有條件地被 取消。 語(yǔ)法 SAVE TRANSACTION
13、 savepoint_name | savepoint_variable 結(jié)果 事務(wù)可以用 SAVE TRANSACTION語(yǔ)句 分解成幾個(gè)邏輯單元 保存點(diǎn)提供了一種機(jī)制，用于回滾部分事務(wù)?？梢允褂?SAVE TRANSACTION savepoint_name 語(yǔ)句創(chuàng)建一個(gè)保存點(diǎn)，然后再執(zhí) 行 ROLLBACK TRANSACTION savepoint_name 語(yǔ)句回滾到該 保存點(diǎn)，從而無(wú)須回滾到事務(wù)的開(kāi)始。 在不可能發(fā)生錯(cuò)誤的情況下，保存點(diǎn)很有用。在很少出現(xiàn)錯(cuò)誤 的情況下使用保存點(diǎn)回滾部分事務(wù)，比讓每個(gè)事務(wù)在更新之前測(cè)試 更新的有效性更為有效。更新和回滾操作代價(jià)很大，因此只有在遇 到錯(cuò)
14、誤的可能性很小，而且預(yù)先檢查更新的有效性的代價(jià)相對(duì)很高 的情況下，使用保存點(diǎn)才會(huì)非常有效。 BEGIN TRANSACTION UPDATE Employee SET cCurrentPosition = 0015 WHERE cEmployeeCode = 000002 UPDATE Position SET iCurrentStrength = iCurrentStrength + 1 WHERE cPositionCode = 0015 SAVE TRANSACTION trnTransaction1 UPDATE Requisition SET NuOfRequir=NuOfRequi
15、r - 10 WHERE cRequisitionCode=0015 UPDATE Position SET iCurrentStrength=iCurrentStrength+10 WHERE cPositionCode=0015 IF (SELECT iMaxStrength-iCurrentStrength FROM Position WHERE cPositionCode = 0015) 80 UPDATE ExternalCandidate SET dInterviewDate = getdate()+ 2 WHERE siTestScore 80 COMMIT TRANSACTIO
16、N User2為什么不能執(zhí)行此事務(wù) ? 為什么 User2不能執(zhí)行此事務(wù) ? 上鎖 確保事務(wù)的完整性和數(shù)據(jù)庫(kù)的一致性 是自動(dòng)實(shí)施的 不上鎖，查看事務(wù)處理是不可能的。 為什么 User2不能執(zhí)行此事務(wù) (續(xù) ) * 事務(wù)的并發(fā)性 3 SQL Server提供了樂(lè)觀的和悲觀的并發(fā)性控件 3 樂(lè)觀并發(fā)性控件 建立在多用戶間資源沖突大概是不可能的假設(shè)的基礎(chǔ) 上 允許事務(wù)執(zhí)行不用鎖定任何資源 只有在提交事務(wù)時(shí)才進(jìn)行資源檢查 為什么 User2不能執(zhí)行此事務(wù) (續(xù) ) 3 悲觀并發(fā)性 控件 在事務(wù)處理期間的鎖定資源 并發(fā)性問(wèn)題 丟失更新 丟失更新問(wèn)題發(fā)生在當(dāng)兩個(gè)或多個(gè)事務(wù)基于原先所選值試圖修改同一 行的時(shí)
17、候 自由依賴性 自由依賴性 問(wèn)題 （ uncommitted dependency ）也稱為 無(wú)效讀入（ dirty read） 問(wèn)題 為什么 User2不能執(zhí)行此事務(wù) (續(xù) ) 不一致性分析 不一致性分析 問(wèn)題又稱為 不可重復(fù) 問(wèn)題 幻象讀取 幻象讀取 又稱為作幻象問(wèn)題 為什么 User2不能執(zhí)行此事務(wù) (續(xù) ) SQL Server鎖模型 共享鎖 允許并發(fā)事務(wù)來(lái)讀取資源 更新鎖 避免了常見(jiàn)形式的死瑣發(fā)生 互斥型鎖 唯一地限制并發(fā)事務(wù)訪問(wèn)一個(gè)資源 為什么 User2不能執(zhí)行此事務(wù) (續(xù) ) 意向鎖 指示 SQL Server要在層次結(jié)構(gòu)較低的某個(gè)資源上獲得一個(gè)共享或排它鎖 模式鎖 當(dāng)任何數(shù)據(jù)
18、定義（ DDL）操作在表上執(zhí)行時(shí)， SQL Server考察模式修改 (Sch-M) 鎖 為什么 User2不能執(zhí)行此事務(wù) (續(xù) ) 結(jié)果： User2不能執(zhí)行他的事務(wù)，因?yàn)楫?dāng)它正在被 User1使用時(shí) SQL Server已經(jīng)鎖定了 ExternalCandidate表 死鎖 死鎖是這樣一種情形：兩個(gè)用戶（或事務(wù)）在個(gè)別的對(duì)象的上 鎖，并且每個(gè)用戶正在等待另一個(gè)對(duì)象的鎖 D I S T R I B U T O R P R O D U C T S T R A N S A C T I O N A T R A N S A C T I O N B 死鎖 (續(xù) ) 設(shè)置死鎖優(yōu)選級(jí) 為探測(cè)死鎖的情況，
19、SQL Server掃描在等待鎖請(qǐng)求的會(huì)話 SQL Server提供 SET DEADLOCK_PRIORITY命令來(lái)定制死鎖 語(yǔ)法 SET DEADLOCK_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_TIME
20、OUT 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ù)是作為一個(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)前位置，也可
21、以指向結(jié)果集中的任意位置， 它允許用戶對(duì)指定位置的數(shù)據(jù)進(jìn)行處理，可以把結(jié)果集中的數(shù)據(jù)放在數(shù)組、 應(yīng)用程序中或其它地方。 游標(biāo) 游標(biāo) 是一個(gè)在給定 結(jié)果集 中幫助訪問(wèn)和操縱數(shù)據(jù) 的數(shù)據(jù)庫(kù)對(duì)象 游標(biāo)能以下列方式處理結(jié)果集中的行： 允許從結(jié)果集中檢索指定的行 允許結(jié)果集中當(dāng)前行被修改 幫助從結(jié)果集中當(dāng)前行導(dǎo)航到不同的行 允許被其它用戶修改的數(shù)據(jù)在結(jié)果集中是可見(jiàn)的 使用游標(biāo)的步驟 有如下幾個(gè)步驟： 創(chuàng)建游標(biāo)。使用 T-SQL語(yǔ)句生成一個(gè)結(jié)果集，并且定義游標(biāo)的特征，如游標(biāo) 中的記錄是否可以修改。 打開(kāi)游標(biāo) 從游標(biāo)的結(jié)果集中讀取數(shù)據(jù)。從游標(biāo)中檢索一行或多行數(shù)據(jù)稱為取數(shù)據(jù)。 對(duì)游標(biāo)中的數(shù)據(jù)逐行操作。 關(guān)閉和
22、釋放游標(biāo)。 5.把指定屬性作為變量顯示 你需要召集所有部門頭目開(kāi)會(huì)。為此你需要部門 和相應(yīng)部門頭目的列表如下，其格式如下： Department Name = Production Department Head = Samuel Moore Department Name = Sales Department Head = Donald Fleming . . 創(chuàng)建報(bào)告所需的步驟 執(zhí)行創(chuàng)建報(bào)告所需的語(yǔ)句 按所需的結(jié)果驗(yàn)證其輸出 游標(biāo)的定義及使用過(guò)程 1. 聲明游標(biāo) 聲明游標(biāo)是指用 DECLARE語(yǔ)句聲明或創(chuàng)建一個(gè)游標(biāo) 。 聲明游標(biāo)的語(yǔ)法如下： DECLARE cursor_name SCRO
23、LL CURSOR FOR select_statement FOR READ ONLY|UPDATEOF column_name_list 其中： cursor_name:是游標(biāo)的名字，為一個(gè)合法的 SQL Server標(biāo)識(shí)符，游標(biāo)的名字必須遵循 SQL Server命名規(guī)范。 SCROLL： 表示取游標(biāo)時(shí)可以使用關(guān)鍵字 NEXT、 PRIOR、 FIRST、 LAST、 ABSOLUTE、 RELATIVE。每 個(gè)關(guān)鍵字的含義將在介紹 FETCH子句時(shí)講解。 select_statement： 是定義游標(biāo)結(jié)果集的標(biāo)準(zhǔn) SELECT語(yǔ)句，它可以是一個(gè)完整語(yǔ)法和語(yǔ)義的 Transact-SQL
24、的 SELECT語(yǔ)句。 但是這個(gè) SELECT語(yǔ)句必須有 FROM子句，不允許使用關(guān)鍵字 COMPUTE、 COMPUTE BY、 FOR BROWSE 和 INTO。 FOR READ ONLY：指出該游標(biāo)結(jié)果集只能讀，不能修改。 FOR UPDATE：指出該游標(biāo)結(jié)果集可以被修改。 OF column_name_list：列出可以被修改的列的名單。 應(yīng)該注意： 游標(biāo)有且只有兩種方式： FOR READ ONLY或 FOR UPDATE。 當(dāng)游標(biāo)方式指定為 FOR READ ONLY時(shí)，游標(biāo)涉及的表不能被修改。 當(dāng)游標(biāo)方式指定為 FOR UPDATE時(shí)，可以刪除或更新游標(biāo)涉及的表中的行。 通常
25、， 這也是缺省方式 ，即不指定游標(biāo)方式時(shí)為 FOR UPDATE方式。 聲明游標(biāo)的 DECLARE CURSOR 語(yǔ)句必須是在該游標(biāo)的任何 OPEN語(yǔ)句之前。 2. 打開(kāi)游標(biāo) 打開(kāi)游標(biāo)是指打開(kāi)已被聲明但尚未被打開(kāi)的游標(biāo)，打開(kāi)游標(biāo)使用 OPEN語(yǔ)句。 打開(kāi)游標(biāo)的語(yǔ)法如下： OPEN cursor_name 其中： cursor_name是一個(gè)已聲明的尚未打開(kāi)的游標(biāo)名。 注意： 當(dāng)游標(biāo)打開(kāi)成功時(shí)，游標(biāo)位置指向結(jié)果集的第一行之前。 只能打開(kāi)已經(jīng)聲明但尚未打開(kāi)的游標(biāo)。 3. 從打開(kāi)的游標(biāo)中提取行 游標(biāo)被打開(kāi)后，游標(biāo)位置位于結(jié)果集的第一行前，此時(shí)可以從結(jié)果集中提取（ FETCH）行。 SQL Serve
26、r將沿著游標(biāo)結(jié)果集一行或多行向下移動(dòng)游標(biāo)位置，不斷提取結(jié)果集中的數(shù)據(jù)，并修改和保存游 標(biāo)當(dāng)前的位置，直到結(jié)果集中的行全部被提取。 從打開(kāi)的游標(biāo)中提取行的語(yǔ)法如下： FETCH NEXT|PRIOR|FIRST|LAST|ABSOLUTE|RELATIVE FROM cursor_name INTO fetch_target_list 其中： cursor_name：為一已聲明并已打開(kāi)的游標(biāo)名字。 NEXT|PRIOR|FIRST|LAST|ABSOLUTE|RELATIVE：游標(biāo)移動(dòng)方向，缺省情況下是 NEXT，即向 下移動(dòng)。 NEXT：取下一行數(shù)據(jù)。 PRIOR：取前一行數(shù)據(jù)。 FIRST：
27、取第一行數(shù)據(jù)。 LAST：取最后一行數(shù)據(jù)。 ABSOLUTE：按絕對(duì)位置取數(shù)據(jù)。 RELATIVE：按相對(duì)位置取數(shù)據(jù)。 游標(biāo)位置確定了結(jié)果集中哪一行可以被提取，如果游標(biāo)方式為 FOR UPDATE的 話，也就確定該位置一行數(shù)據(jù)可以被更新或刪除。 INTO fetch_target_list：指定存放被提取的列數(shù)據(jù)的目的變量清單。這個(gè)清單 中變量的個(gè)數(shù)、數(shù)據(jù)類型、順序必須與定義該游標(biāo)的 select_statement的 SELECT_list中列出的列清單相匹配。為了更靈活地操縱數(shù)據(jù)，可以把從已聲明并 已打開(kāi)的游標(biāo)結(jié)果集中提取的列數(shù)據(jù)，分別存放在目的變量中。 INTO fetch_target
28、_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)前游標(biāo)位置已在結(jié)果集中的最后一行，結(jié)果集中 不再有數(shù)據(jù)。 -2：提取的行不存在。 rowcount保存著自游標(biāo)打開(kāi)后的第一個(gè) FETCH語(yǔ)句，直到最近一次的 FETCH語(yǔ)句為止，已從游標(biāo)結(jié)果集中提取的行數(shù)。 也就是說(shuō)它保存著任何時(shí)間點(diǎn)客戶 機(jī)程序看到的已提取的總行數(shù)。一旦結(jié)果集中所有行都被提取，那么 rowcount的
29、值就是該結(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)閉 (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)你退出
30、這個(gè) SQL Server會(huì)話時(shí) 從聲明游標(biāo)的存儲(chǔ)過(guò)程中返回時(shí) 創(chuàng)建報(bào)告所需的步驟 (續(xù) ) 5.解除分配游標(biāo) 你可以抹去由 DECLARE游標(biāo)語(yǔ)句定義的游標(biāo)的定義 語(yǔ)法 DEALLOCATE cursor_name 報(bào)告所需的步驟 (續(xù) )-7號(hào) 你需要用下面的語(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
31、 the cursor that can be used to - access the records of the table,row by row. DECLARE curDepartment cursor for SELECT vDepartmentName,vDepartmentHead FROM Department - Open the cursor OPEN curDepartment - Fetch the rows into variables FETCH curDepartment into DepartmentName, DepartmentHead - Start a
32、 loop to display all the rows of - the cursor. While (fetch_status = 0) BEGIN Print Department Name = + DepartmentName Print Department Head = + DepartmentHead - Fetch the next row from the cursor. FETCH curDepartment into DepartmentName, DepartmentHead END - Close the cursor CLOSE curDepartment - D
33、eallocate the cursor. DEALLOCATE curDepartment 定義一個(gè)游標(biāo)，將學(xué)生表 student中所有學(xué)生的姓名、性別顯示出來(lái)。 DECLARE student_name VARCHAR(8),student_sex VARCHAR(16) DECLARE student_coursor SCROLL CURSOR FOR SELECT name， sex FROM student FOR READ ONLY OPEN student_coursor FETCH student_coursor INTO student_name,student_sex WH
34、ILE FETCH_STATUS=0 BEGIN PRINT 學(xué)生姓名 :+student_name+ +性別 : + student_sex FETCH FROM student_coursor INTO student_name , student_sex END CLOSE student_coursor DEALLOCATE student_coursor 用戶可以在 UPDATE或 DELETE語(yǔ)句中使用游標(biāo)來(lái)更新、刪除表或視圖中的行，但不 能用來(lái)插入新行。 更新數(shù)據(jù) 通過(guò)在 UPDATE語(yǔ)句中使用游標(biāo)可以更新表或視圖中的行。被更新的行依賴于游標(biāo)位 置的當(dāng)前值。 更新數(shù)據(jù)語(yǔ)法形式如下
35、： 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_name| view_name：要更新的表名或視圖名，可 以加或不加限定。但它必須是聲明該游標(biāo)的 SELECT語(yǔ)句中的表名或視圖名。 column_name： 是要更新的列的列名，可以加或不加限定。但它們必須是聲 明游標(biāo)的 SELECT語(yǔ)句中 UPDATE OF column_name_list的子集。 ne
36、w_value：為被更新列的新值，它可以是一個(gè)表達(dá)式、空值或子查詢。 WHERE CURRENT OF：使 SQL Server只更新由指定游標(biāo)的游標(biāo)位置當(dāng)前值 確定的行。 cursor_name： 是已聲明為 FOR UPDATE方式并已打開(kāi)的游標(biāo)名。 注意： 使用 UPDATE.CURRENT OF語(yǔ)句 一次只能更新當(dāng)前游標(biāo)位置確定的那一行 ， OPEN語(yǔ)句將游標(biāo)位置定位在結(jié)果集第一行前，可以使用 FETCH語(yǔ)句把游標(biāo)位置定位在 要被更新的數(shù)據(jù)行處。 用 UPDATE.WHERE CURRENT OF語(yǔ)句更新表中的行時(shí)， 不會(huì)移動(dòng)游標(biāo)位置， 被更新的行可以再次被修改 ，直到下一個(gè) FETC
37、H語(yǔ)句的執(zhí)行。 UPDATE.WHERE CURRENT OF語(yǔ)句可以更新多表視圖或被連接的多表，但只 能更新其中 一個(gè)表 的行，即所有被更新的列都來(lái)自同一個(gè)表。 DECLARE student_coursor SCROLL CURSOR FOR SELECT name,sex FROM student FOR UPDATE OF name OPEN student_coursor FETCH ABSOLUTE 3 FROM student_coursor UPDATE student SET name = ABC WHERE CURRENT OF student_coursor CLOSE
38、student_coursor DEALLOCATE student_coursor 刪除數(shù)據(jù) 通過(guò)在 DELETE語(yǔ)句中使用游標(biāo)來(lái)刪除表或視圖中的行。被刪除的行依賴于游標(biāo) 位置的當(dāng)前值。 刪除數(shù)據(jù)語(yǔ)法形式如下： DELETE FROM database.owner.table_name|view_name WHERE CURRENT OF cursor_name 其中： table_name|view_name：為要從其中刪除行的表名或視圖名，可以加或不加限 定。但它必須是定義該游標(biāo)的 SELECT語(yǔ)句中的表名或視圖名。 cursor_name：為已聲明并已打開(kāi)的游標(biāo)名。 WHERE CURRENT OF：它使 SQL Server只刪除由指定游標(biāo)的游標(biāo)位置當(dāng)前值 確定的行。 注意： 使用游標(biāo)的 DELETE語(yǔ)句，一次只能刪除當(dāng)前游標(biāo)位置確定的那一行。 OPEN語(yǔ)句 將游標(biāo)位置定位在結(jié)果集第一行之前，可以用 FETCH語(yǔ)句把游標(biāo)位置定位在要被刪除 的行處。 在 DELETE語(yǔ)句中使用的游標(biāo)必須聲明為 FOR UPDATE方式 。而且聲明游標(biāo)的 SELECT語(yǔ)句中不能含有連接操作或涉及多表視圖，否則即使聲明中指明了 FOR UPDATE方式，也不能刪除其中的行。 對(duì)使用游標(biāo)刪除行的表，要求有一個(gè)唯一索引。 使用游標(biāo)的 DELETE語(yǔ)句，刪除一行后將游標(biāo)位置向前移動(dòng)一行。
《數(shù)據(jù)庫(kù)原理與應(yīng)用》11.事務(wù)和游標(biāo)的創(chuàng)建和使用
