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

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

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

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

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

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

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

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

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

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

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

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

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

教師演示講解8.1.3游標(biāo)的屬性

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

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

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

游標(biāo)中的循環(huán)【例1-4】使用游標(biāo)查詢商品表GOODS中“02”類型的所有商品的信息?？梢酝ㄟ^(guò)使用FOR循環(huán)提取游標(biāo)的數(shù)據(jù)，并向游標(biāo)內(nèi)部傳入商品類別編號(hào)作為游標(biāo)參數(shù)

教師演示講解8.1.4游標(biāo)的更新

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

WHERECURRENTOF<游標(biāo)名|條件表達(dá)式>教師演示講解8.1.4游標(biāo)的更新

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

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

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

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

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

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

。案例學(xué)習(xí)目標(biāo)使用COMMIT事務(wù)提交、使用ROLLBACK進(jìn)行事務(wù)回滾、事務(wù)撤銷(xiāo)和使用SAVEPOINT設(shè)置事務(wù)保存點(diǎn)

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

添加標(biāo)題文字教師演示講解課堂案例2—事務(wù)處理案例完成步驟-事務(wù)提交添加標(biāo)題文字【例1-6】提交更新商品表GOODS的事務(wù)。分析：在執(zhí)行更新商品表GOODS的UPDATE語(yǔ)句后，只有當(dāng)前用戶可以看到數(shù)據(jù)庫(kù)更新后的結(jié)果，此時(shí)還需要顯式使用COMMIT命令（除非設(shè)置自動(dòng)提交開(kāi)關(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;添加標(biāo)題文字教師演示講解課堂案例2—事務(wù)處理案例完成步驟-事務(wù)回滾添加標(biāo)題文字事務(wù)回滾（Rollback）是指當(dāng)事務(wù)中的某一條SQL語(yǔ)句執(zhí)行失敗時(shí)，將對(duì)數(shù)據(jù)庫(kù)的操作恢復(fù)到事務(wù)執(zhí)行前或者某個(gè)指定位置。

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

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

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

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

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

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

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