第8章 游標、事務(wù)和鎖

第8章游標、事務(wù)和鎖主編：劉志成本章學(xué)習導(dǎo)航本章學(xué)習導(dǎo)航

本章學(xué)習要點（1）聲明游標、打開游標、提取游標數(shù)據(jù)和關(guān)閉游標。（2）循環(huán)處理游標。（3）游標的更新。（4）提交事務(wù)、回滾事務(wù)和設(shè)置保存點。（5）鎖的功能及其類型。

建議課時：4課時8.1游標游標的概念游標（Cursor）是Oracle系統(tǒng)在內(nèi)存中開辟的一塊工作區(qū)，在該工作區(qū)中存放查詢語句返回的結(jié)果集。結(jié)果集可以包含零條數(shù)據(jù)記錄、一條數(shù)據(jù)記錄，也可以是多條數(shù)據(jù)記錄。在定義游標所在的工作區(qū)中，存在一個指針，在初始狀態(tài)下，游標指針指向查詢結(jié)果集的第一條數(shù)據(jù)記錄的位置。當執(zhí)行FETCH語句提取數(shù)據(jù)記錄后，游標指針將向下移動一個數(shù)據(jù)記錄的位置。Oracle中的游標分為顯示游標和隱式游標。當查詢返回的結(jié)果集超過一條數(shù)據(jù)記錄時，就需要一個顯式游標，此時用戶不能使用SELECTINTO語句。顯式游標在PL/SQL塊的聲明部分聲明，在執(zhí)行部分或異常處理部分打開、提取和關(guān)閉PL/SQL管理隱式游標，當查詢開始時隱式游標打開，查詢結(jié)束時隱式游標自動關(guān)閉。8.1游標游標的概念游標通過以下方式擴展結(jié)果處理：從結(jié)果集的當前位置檢索一行數(shù)據(jù)記錄；支持對結(jié)果集的當前數(shù)據(jù)記錄進行數(shù)據(jù)更新。PL/SQL游標一般按以下步驟來使用：（1）聲明游標；（2）打開游標；（3）提取游標數(shù)據(jù)；（4）對當前數(shù)據(jù)記錄執(zhí)行更新操作（可選）；（5）關(guān)閉游標。

課堂案例1—游標操作學(xué)習使用Oracle的PL/SQL語句聲明游標、打開游標、提取游標數(shù)據(jù)和關(guān)閉游標的操作方法

。案例學(xué)習目標使用DECLARECURSOR聲明游標、使用OPENCURSOR打開游標、使用FETCH提取游標數(shù)據(jù)、使用CLOSECURSOR關(guān)閉游標

。案例知識要點課堂案例1—游標操作案例完成步驟-基本步驟添加標題文字1．聲明游標聲明游標，就是使一個游標與一條查詢語句建立聯(lián)系。

DECLARE CURSOR<游標名>[（參數(shù)1數(shù)據(jù)類型[，…n]）]IS查詢語句

[FORUPDATE[OF[用戶方案.]<表名>.<列名>[，…n]]];2．打開游標就是執(zhí)行游標定義時所對應(yīng)的查詢語句，并把查詢返回的結(jié)果集存儲在游標對應(yīng)的工作區(qū)中。

OPEN<游標名>[（參數(shù)1[，…n]）];3．提取游標數(shù)據(jù)就是從定義游標的工作區(qū)中檢索一條數(shù)據(jù)記錄作為當前數(shù)據(jù)記錄。

FETCH<游標名>INTO變量1[，…n]4．關(guān)閉游標

CLOSE<游標名>;教師演示講解課堂案例1—游標操作案例完成步驟-實例添加標題文字【例1-1】使用游標查詢商品表GOODS中第一款商品的信息。教師演示講解8.1.3游標的屬性

游標的屬性添加標題文字（1）%ISOPEN描述游標是否已經(jīng)打開，返回布爾型值。如果游標沒有打開就直接使用FETCH語句提取游標數(shù)據(jù)，Oracle系統(tǒng)就會報告錯誤。（2）%FOUND描述最近一次FETCH操作的執(zhí)行情況，返回布爾型值。如果最近一次使用FETCH語句提取游標數(shù)據(jù)得到結(jié)果則返回TRUE，否則返回FALSE。（3）%NOTFOUND用于描述最近一次FETCH操作的執(zhí)行情況，返回布爾型值。但與%FOUND屬性不同的是，如果最近一次使用FETCH語句提取游標數(shù)據(jù)沒有得到結(jié)果則返回TRUE，否則返回FALSE。（4）%ROWCOUNT用于描述截至目前從游標工作區(qū)提取的實現(xiàn)記錄數(shù)。

教師演示講解8.1.3游標的屬性

游標的屬性【例1-3】使用游標查詢商品表GOODS中所有商品的信息。教師演示講解8.1.4游標中的循環(huán)

游標中的循環(huán)循環(huán)提取游標工作區(qū)內(nèi)結(jié)果集的數(shù)據(jù)記錄時，既可以通過LOOP循環(huán)來簡單實現(xiàn)，也可以通過FOR循環(huán)來實現(xiàn)復(fù)雜功能。而且，使用FOR循環(huán)提取游標數(shù)據(jù)時，與其他方法有些差異，主要表現(xiàn)在：使用FOR循環(huán)提取游標數(shù)據(jù)時，Oracle系統(tǒng)自動打開游標，而不必顯式地使用OPEN語句打開游標；Oracle系統(tǒng)隱含地定義了一個數(shù)據(jù)類型為%ROWTYPE的變量，并以此作為循環(huán)的計數(shù)器；Oracle系統(tǒng)自動重復(fù)從游標工作區(qū)內(nèi)提取數(shù)據(jù)并放入計數(shù)器變量中；當游標工作區(qū)內(nèi)所有數(shù)據(jù)記錄都被提取完成或者循環(huán)中止時，Oracle系統(tǒng)會自動關(guān)閉游標

教師演示講解8.1.4游標中的循環(huán)

游標中的循環(huán)【例1-4】使用游標查詢商品表GOODS中“02”類型的所有商品的信息。可以通過使用FOR循環(huán)提取游標的數(shù)據(jù)，并向游標內(nèi)部傳入商品類別編號作為游標參數(shù)

教師演示講解8.1.4游標的更新

游標的更新UPDATE或DELETE語句中的WHERECURRENTOF子句專門處理要執(zhí)行UPDATE或DELETE操作的表中取出的最近的數(shù)據(jù)。要使用這個方法，在聲明游標時必須使用FORUPDATE子句，當對話使用FORUPDATE子句打開一個游標時，所有返回集中的數(shù)據(jù)行都將處于行級（ROW-LEVEL）獨占式鎖定，其他對象只能查詢這些數(shù)據(jù)行，不能進行UPDATE、DELETE或SELECT...FORUPDATE操作。在多表查詢中，使用OF子句來鎖定特定的表，如果忽略了OF子句，那么所有表中選擇的數(shù)據(jù)行都將被鎖定。如果這些數(shù)據(jù)行已經(jīng)被其他會話鎖定，那么在正常情況下Oracle將等待，直到數(shù)據(jù)行解鎖。在UPDATE和DELETE中使用WHERECURRENTOF子句的語法格式如下：

WHERECURRENTOF<游標名|條件表達式>教師演示講解8.1.4游標的更新

游標的更新【例1-5】使用帶FORUPDATE子句的游標更新商品表GOODS中商品詳細描述信息，并顯示更新前后的信息

教師演示講解8.2事務(wù)

事務(wù)概述事務(wù)（Transaction）是Oracle系統(tǒng)中進行數(shù)據(jù)庫操作的基本單位。事務(wù)是一個操作序列，它包含了一組SQL語句，所有的SQL語句作為一個邏輯整體一起向Oracle系統(tǒng)提交或者撤銷操作請求，即事務(wù)中的SQL語句要么都被執(zhí)行，要么都不被執(zhí)行。事務(wù)是Oracle系統(tǒng)中一個不可分割的邏輯工作單元。應(yīng)用事務(wù)可以保證Oracle數(shù)據(jù)庫的一致性和可恢復(fù)性

教師演示講解8.2事務(wù)

事務(wù)概述一個事務(wù)的邏輯工作單元必須具有以下屬性。（1）原子性（Atomicity）一個事務(wù)必須作為Oracle系統(tǒng)工作的原子單位（在化學(xué)中，原子稱為“不可再分的微?！保?，事務(wù)要么全部執(zhí)行，要么全部不執(zhí)行。（2）一致性（Consistency）當事務(wù)完成之后，所有數(shù)據(jù)必須處于一致性狀態(tài)，事務(wù)所修改的數(shù)據(jù)必須遵循Oracle數(shù)據(jù)庫的各種完整性約束。（3）隔離性（Isolation）一個事務(wù)所做的更新操作必須與其他事務(wù)所做的更新操作保持完全隔離，在并發(fā)處理過程中，一個事務(wù)所開始處理的數(shù)據(jù)必須為另一個事務(wù)處理前或者處理后的數(shù)據(jù)，而不能為另一個事務(wù)正在處理的數(shù)據(jù)。這種隔離性是通過Oracle的鎖機制來實現(xiàn)的。（4）永久性（Durability）事務(wù)完成后，事務(wù)對數(shù)據(jù)庫所做的更新被永久保持。事務(wù)的上述4種屬性也稱為事務(wù)的ACID（取每種屬性的英文名稱的首字母組成）屬性

教師演示講解課堂案例2—事務(wù)處理學(xué)習使用Oracle的PL/SQL語句進行事務(wù)提交、事務(wù)回滾、事務(wù)撤銷和設(shè)置保存點等操作

。案例學(xué)習目標使用COMMIT事務(wù)提交、使用ROLLBACK進行事務(wù)回滾、事務(wù)撤銷和使用SAVEPOINT設(shè)置事務(wù)保存點

。案例知識要點課堂案例2—事務(wù)處理案例完成步驟-事務(wù)提交添加標題文字1．事務(wù)提交事務(wù)提交（Commit）用于提交自上次提交以后對數(shù)據(jù)庫中數(shù)據(jù)所做的改動。在Oracle數(shù)據(jù)庫中，為了維護數(shù)據(jù)的一致性，系統(tǒng)為每個用戶分別設(shè)置了一個工作區(qū)，對數(shù)據(jù)表中數(shù)據(jù)所做的添加、修改和刪除操作都在工作區(qū)內(nèi)完成。在Oracle系統(tǒng)中，提交事務(wù)的命令是COMMIT

添加標題文字教師演示講解課堂案例2—事務(wù)處理案例完成步驟-事務(wù)提交添加標題文字【例1-6】提交更新商品表GOODS的事務(wù)。分析：在執(zhí)行更新商品表GOODS的UPDATE語句后，只有當前用戶可以看到數(shù)據(jù)庫更新后的結(jié)果，此時還需要顯式使用COMMIT命令（除非設(shè)置自動提交開關(guān)的狀態(tài)為ON）提交事務(wù)，以使數(shù)據(jù)更新生效。

--執(zhí)行數(shù)據(jù)更新操作

UPDATE SCOTT.GOODS SET g_DESCRIPTION='男人的衣柜,展現(xiàn)男人的魅力' WHERE g_ID='040001'; --提交事務(wù)

COMMIT;添加標題文字教師演示講解課堂案例2—事務(wù)處理案例完成步驟-事務(wù)回滾添加標題文字事務(wù)回滾（Rollback）是指當事務(wù)中的某一條SQL語句執(zhí)行失敗時，將對數(shù)據(jù)庫的操作恢復(fù)到事務(wù)執(zhí)行前或者某個指定位置。

ROLLBACK[TO<保存點>];【例1-7】更新商品表GOODS的信息，并回滾該事務(wù)。

--執(zhí)行數(shù)據(jù)更新操作

UPDATE SCOTT.GOODS SET g_DESCRIPTION=NULL WHERE g_ID='040001'; --回滾事務(wù)

ROLLBACK;添加標題文字教師演示講解課堂案例2—事務(wù)處理案例完成步驟-設(shè)置保存點添加標題文字如果讓事務(wù)回滾到指定位置，需要在事務(wù)中預(yù)先設(shè)置事務(wù)保存點（SavePoint）。所謂保存點，是指在其所在位置之前的事務(wù)語句不能回滾的位置，回滾事務(wù)后，保存點之后的事務(wù)語句被回滾，但保存點之前的事務(wù)語句依然被有效執(zhí)行，即不能回滾。

SAVEPOINT<保存點名>;添加標題文字教師演示講解課堂案例2—事務(wù)處理案例完成步驟-設(shè)置保存點添加標題文字添加標題文字教師演示講解8.3鎖

鎖的概述Oracle通過使用鎖（Lock）機制維護數(shù)據(jù)的完整性、并發(fā)性和一致性。Oracle在兩個不同級別上提供讀取一致性：語句級讀取一致性和事務(wù)級讀取一致性。（1）語句級讀取一致性O(shè)racle總是實施語句級讀取一致性，保證單個查詢所返回的數(shù)據(jù)與該查詢開始時刻保持一致。（2）事務(wù)級讀取一致性事務(wù)級讀取一致性是指在同一個事務(wù)中的所有數(shù)據(jù)對時間點是一致的。教師演示講解8.3鎖

鎖的類型根據(jù)保護的對象不同，Oracle數(shù)據(jù)庫鎖可以分為以下幾大類：DML鎖（DataLocks，數(shù)據(jù)鎖），用于保護數(shù)據(jù)的完整性；DDL鎖（DictionaryLocks，字典鎖），用于保護數(shù)據(jù)庫對象的結(jié)構(gòu)，如表、索引等的結(jié)構(gòu)定義；內(nèi)部鎖和閂（InternalLocksandLatches），保護數(shù)據(jù)庫的內(nèi)部結(jié)構(gòu)。DML鎖的目的在于保證并發(fā)情況下的數(shù)據(jù)完整性，在Oracle數(shù)據(jù)庫中，DML鎖主要包括TM鎖和TX鎖，其中TM鎖稱為表級鎖，TX鎖稱為事務(wù)鎖或行級鎖。當Oracle執(zhí)行DML語句時，系統(tǒng)自動在所要操作的表上申請TM類型的鎖。當TM鎖獲得后，系統(tǒng)再自動申請