數據操作與事務控制課程結構內容課時（H）第一章Oracle數據庫基礎1.8第二章編寫簡單的查詢語句1.2第三章限制數據和對數據排序1.5第四章單行函數2.5第五章多表查詢2第六章分組函數2第七章子查詢2第八章數據操作與事務控制3.5第九章表和約束4第十章其他數據庫對象2.5第2頁,共59頁，星期六，2024年，5月第八章數據操作與事務控制目標:本章旨在向學員介紹：1)數據操作語句2）事務控制語句時間：3.5學時教學方法：講授ppt＋上機練習第3頁,共59頁，星期六，2024年，5月本章要點INSERT語句UPDATE語句DELETE語句9i新增MERGE語句COMMIT命令ROLLBACK命令管理鎖第4頁,共59頁，星期六，2024年，5月第八章數據操作與事務控制數據操作語言（DML：DataManipulationLanguage）主要包括以下語句：INSERTUPDATEDELETEMERGE事務是一組相關的DML語句的邏輯組合。事務控制主要包括下列命令：COMMITROLLBACKSAVEPOINT第5頁,共59頁，星期六，2024年，5月第八章數據操作與事務控制數據操作與事務控制:8.1插入數據8.2修改數據8.3刪除數據8.4合并數據8.5使用ROWID操作數據8.6事務處理8.7鎖第6頁,共59頁，星期六，2024年，5月8.1.1INSERT語法結構語法如下：一次只插入一行NULL的使用，連續的單引號（‘’）也可以表示空值。插入日期型數據插入特殊字符插入多行數據按列的默認順序列出各個列的值在Insert子句中可以隨意列出列名和他們的值字符和日期型數據應該包含在單引號中INSERTINTO表名[(列名1[,列名2，…，列名n])]VALUES(值1[,值2，…，值n]);第7頁,共59頁，星期六，2024年，5月8.1.2INSERT語句插入單行數據1/2例8-1將一個新成立部門的信息寫入departments表INSERTINTOdepartmentsVALUES(300,'Operations',110,1500);第8頁,共59頁，星期六，2024年，5月8.1.2INSERT語句插入單行數據2/2顯示默認值概述

使用Default表示默認值符合SQL：1999標準可以使用顯示默認值控制默認值的使用顯示默認值可以在insert和update語句中使用第9頁,共59頁，星期六，2024年，5月8.1.3INSERT語句插入空值（NULL）例8-2將一個新成立部門的信息寫入departments中

，其中管理者未知?；蚧騃NSERTINTOdepartmentsVALUES(310,'Operations',NULL,1500);INSERTINTOdepartments(department_id,department_name,location_id)VALUES(310,'Operations',1500);INSERTINTOdepartmentsVALUES(310,'Operations','',1500);第10頁,共59頁，星期六，2024年，5月8.1.4INSERT語句插入日期型數據例8-5將一新入職員工信息寫入employees表或INSERTINTOemployees(employee_id,last_name,email,hire_date,job_id)VALUES(210,’Wang’,’SWANG’,’10-9月-06’,’IT_PROG’);INSERTINTOemployees(employee_id,last_name,email,hire_date,job_id)VALUES(210,’Wang’,’SWANG’,TO_DATE(’2006-9-10’,’YYYY-MM-DD’),’IT_PROG’);第11頁,共59頁，星期六，2024年，5月8.1.5INSERT語句插入特殊字符查看ESCAPE轉義符用哪個符號表示。INSERT語句中使用“\”符對特殊符號轉義。INSERTINTOtestVALUES（‘\&TEST\&’）;SHOWESCAPE；——查看ESCAPE狀態escapeOFF——返回ESCAPE狀態為OFFSETESCAPEON；——設定ESCAPE狀態為ONSHOWESCAPE；——查看ESCAPE狀態escape"\"(hex5c)——返回ESCAPE符號為“\”

INSERTINTOtestVALUES（‘&TEST&’）;第12頁,共59頁，星期六，2024年，5月8.1.6INSERT語句插入多行數據語法例8-7將受雇日期在“1995-1-1”之前的員工信息復制到hemployees表中。INSERTINTO表名[(列名1[,列名2，…，列名n])]子查詢；INSERTINTOhemployeesSELECT*FROMemployeesWHEREhire_date<TO_DATE(‘1995-1-1’,’YYYY-MM-DD’);不必書寫values子句

INSERT子句中列的數量和類型必須和子查詢中列的數量和類型相匹配第13頁,共59頁，星期六，2024年，5月第八章數據操作與事務控制數據操作與事務控制:8.1插入數據8.2修改數據8.3刪除數據8.4合并數據8.5使用ROWID操作數據8.6事務處理8.7鎖第14頁,共59頁，星期六，2024年，5月8.2.1UPDATE語法結構UPDATE語法結構UPDATE簡單修改UPDATEemployeesSETsalary=salary+200,manager_id=103WHEREdepartment_id=60;UPDATE表名

SET列名=表達式[，列名=表達式，···][WHERE條件表達式]；UPDATEemployeesSETsalary=salary*(1+0.2);第15頁,共59頁，星期六，2024年，5月8.2.2UPDATE嵌入子查詢修改1/2嵌入子查詢修改除基于表自身實現嵌入子查詢的方式實現修改操作外，也可以在子查詢中基于其他表實現修改操作。UPDATE

employeesSETdepartment_id=10,salary=500+(SELECTAVG(salary) FROMemployees)WHEREjob_id=(SELECTjob_idFROMemployees WHEREemployee_id=110)ANDemployee_id<>110;第16頁,共59頁，星期六，2024年，5月8.2.2UPDATE嵌入子查詢修改2/2相關子查詢修改updateemployeesasetsalary=salary+(select

avg(salary)fromemployeesbwhereb.department_id=a.department_id)第17頁,共59頁，星期六，2024年，5月8.2.3修改數據(完整性錯誤)updateemployeessetdepartment_id=55wheredepartment_id=100;ORA-02291:違反完整約束條件(NEU.EMP_DEPT_FK)-未找到父項關鍵字第18頁,共59頁，星期六，2024年，5月第八章數據操作與事務控制數據操作與事務控制:8.1插入數據8.2修改數據8.3刪除數據8.4合并數據8.5使用ROWID操作數據8.6事務處理8.7鎖第19頁,共59頁，星期六，2024年，5月8.3.1DELETE語法結構DELETE語法結構DELETE刪除數據DELETE[FROM]表名[WHERE條件表達式]；DELETEFROMdepartmentsWHEREdepartment_id=210;第20頁,共59頁，星期六，2024年，5月8.3.2DELETE刪除數據1/3例8-12刪除管理者編號（manager_id）為205的部門，相應部門的員工予以解聘，不包括205號員工。DELETE語句不能刪除被其他表引用了的記錄值。DELETEFROMemployeesWHEREdepartment_idIN(SELECTdepartment_id FROMdepartments WHEREmanager_id=205)ANDemployee_id<>205;第21頁,共59頁，星期六，2024年，5月8.3.2DELETE刪除數據2/3刪除數據（完整性錯誤）deletefromdepartmentswheredepartment_id=100;ORA-02292:違反完整約束條件(NEU.EMP_DEPT_FK)-已找到子記錄日志第22頁,共59頁，星期六，2024年，5月8.3.2DELETE刪除數據3/3刪除數據（相關子查詢）deleteemp_copya

where

exists(select'1'fromemployeesbwhereb.employee_id=a.employee_id)第23頁,共59頁，星期六，2024年，5月第八章數據操作與事務控制數據操作與事務控制:8.1插入數據8.2修改數據8.3刪除數據8.4合并數據8.5使用ROWID操作數據8.6事務處理8.7鎖第24頁,共59頁，星期六，2024年，5月8.4合并數據根據指定的條件執行插入或者更新操作如果滿足條件的行存在，執行更新操作，否則執行插入操作優點： 避免獨立的數據更新 提高效率而且使用方便 在數據倉庫應用中經常使用第25頁,共59頁，星期六，2024年，5月8.4.1MERGE語法結構1/2MERGE語法結構MERGEINTOtable_name[t_alias]USING{table|view|subquery}[t_alias]ON(joincondition)WHENMATCHEDTHENUPDATESETcol1=col1_val[,col2=col2_val···]WHENNOTMATCHEDTHENINSERT（column_list）VALUES（column_values）;第26頁,共59頁，星期六，2024年，5月8.4.1MERGE語法結構2/2INTO子句：指定更新或插入的目標表USING子句：指定更新或插入的數據源，它可以是表、視圖、子查詢ON子句：合并操作的條件判斷語句 第27頁,共59頁，星期六，2024年，5月8.4.2MERGE語句合并數據MERGEINTOempaUSINGemployeesbON(a.employee_id=b.employee_id)WHENMATCHEDTHENUPDATESETa.email=b.email,a.phone_number=b.phone_number,a.salary=b.salary,a.manager_id=b.manager_id,a.department_id=b.department_idWHENNOTMATCHEDTHENINSERT(employee_id,email,phone_number,salary,manager_id,department_id)VALUES(b.employee_id,b.email,b.phone_number,b.salary,b.manager_id,b.department_id);該例子顯示匹配employees表中的employee_id列與emp表中的employee_id列。如果找到了一個匹配，用employees表中匹配行的列值更新emp表中匹配的列值。如果相匹配行沒有找到，employees表中的列值被插入到emp表中。

第28頁,共59頁，星期六，2024年，5月第八章數據操作與事務控制數據操作與事務控制:8.1插入數據8.2修改數據8.3刪除數據8.4合并數據8.5使用ROWID操作數據8.6事務處理8.7鎖第29頁,共59頁，星期六，2024年，5月8.5.1ROWID介紹1/2ROWID：偽列，是表中虛擬的列，是系統自動產生的。每一行記錄中都包含ROWID，表示這一行的唯一地址。ROWID標識了Oracle如何定位行，通過ROWID能快速定位一行記錄。ROWIDCNAMEAAAHjXAAOAAAADaAAA姚明AAAHjXAAOAAAADaAAC成龍SELECTrowid,cnameFROMdossier;第30頁,共59頁，星期六，2024年，5月8.5.1ROWID介紹2/2ROWID的格式：ROWID中包含該行數據的物理位置信息，所以能快速的定位記錄ROWIDCNAMEAAAHjXAAOAAAADaAAA姚明AAAHjXAAOAAAADaAAC成龍例：AAAHjXAAOAAAADaAAA數據對象編號文件編號塊編號行編號

AAAHjXAAOAAAADaAAAOOOOOOFFFBBBBBBRRR數據對象編號相關文件編號塊編號行編號第31頁,共59頁，星期六，2024年，5月8.5.2使用ROWID進行數據操作ROWID的應用：快速定位單行記錄，DML語句可以使用ROWID操作數據，效率最快作為表行的唯一標識例:使用ROWNUM修改數據UPDATEemployeesSETfirst_name=first_name||'*'WHEREROWID='AAAMg6AAFAAAABUAAA';第32頁,共59頁，星期六，2024年，5月第八章數據操作與事務控制數據操作與事務控制:8.1插入數據8.2修改數據8.3刪除數據8.4合并數據8.5使用ROWID操作數據8.6事務處理8.7鎖第33頁,共59頁，星期六，2024年，5月8.6.1事務概念及特征1/3概念：事務（Transaction）也稱工作單元，是一個或多個SQL語句所組成的序列，這些SQL操作作為一個完整的工作單元，要么全部執行，要么全部不執行。通過事務的使用，能夠使一系列相關操作關聯起來，防止出現數據不一致現象。事務的組成：在ORACLE數據庫中，事務由以下語句組成：一組相關的DML語句，修改的數據在該組語句中保持一致一個DDL語句或者一個DCL語句或者一個TCL語句第34頁,共59頁，星期六，2024年，5月8.6.1事務概念及特征2/3可用四個字母的縮寫表示：即ACID原子性（Atomicity）事務就像一個獨立的工作單元。原子性保證要么所有的操作都成功，要不全都失敗。如果所有的動作都成功了，我們就說這個事務成功了，不然就是失敗的，然后回滾。一致性（Consistency）一旦事務完成了（不管是成功的，還是失敗的），整個系統處于操作規則的統一狀態，也就是說，數據不會損壞。第35頁,共59頁，星期六，2024年，5月8.6.1事務概念及特征3/3隔離性（Isolation）事務的隔離性是指數據庫中一個事務的執行不能被其它事務干擾。所以，事務應該隔離起來，目的為了防止同時的讀和寫操作。這就需要事務與鎖同時使用。持久性（Durability）事務的持久性也稱為永久性（Permanence），指事務一旦提交，則其對數據庫中數據的改變就是永久的。第36頁,共59頁，星期六，2024年，5月8.6.2事務控制1/8事務的控制分為：顯式控制及隱式控制。顯示控制：顯示提交：Commit顯示回滾：Rollback隱式控制：隱式提交：當下列任意一種情況發生時，會發生隱式提交執行一個DDL語句執行一個DCL語句從SQL*Plus正常退出（即使用EXIT或QUIT命令退出）隱式回滾：當下列任意一種情況發生時，會發生隱式回滾從SQL*Plus中強行退出客戶端連接到服務器端異常中斷系統崩潰注意：PL/Sqldeveloper工具和Sqlplus略有不同。第37頁,共59頁，星期六，2024年，5月8.6.2事務控制2/8事務控制的命令主要有以下三個：事務提交：COMMIT事務回滾：ROLLBACK設立保存點：SAVEPOINT（作為輔助命令使用）設置保存點語法：SAVEPOINT保存點名稱；——定義保存點ROLLBACKTO保存點名稱;——回滾到已定義保存點第38頁,共59頁，星期六，2024年，5月8.6.2事務控制3/8開始：事務開始于上一個事務結束后執行的第一個DML語句結束：事務結束于下面的任一種情況的發生：執行了COMMIT或者ROLLBACK命令隱式提交（單個的DDL或DCL語句）或自動提交用戶退出系統崩潰第39頁,共59頁，星期六，2024年，5月8.6.2事務控制4/8設置格式：例8-14

SQL*Plus自動提交的應用示例SETAUTOCOMMIT[ON|OFF];SHOWAUTOCOMMIT;——查看AUTOCOMMIT變量狀態autocommitOFFSETAUTOCOMMITON；——把變量狀態設置為ONINSERTINTOtestVALUES(‘TEST’);已創建1行提交完成——已經自動提交事務自動提交第40頁,共59頁，星期六，2024年，5月8.6.2事務控制5/8DELETEFROMtest;ROLLBACK;——撤消DELETE操作INSERTINTOtestVALUES(’A’);SAVEPOINTinsert_a;——定義insert_a保存點INSERTINTOtestVALUES(’B’);SAVEPOINTinsert_b;——定義insert_b保存點INSERTINTOtestVALUES(’C’);ROLLBACKTOinsert_b;——撤消操作到insert_b保存點DELETEFROMtestWHEREtest_str=‘A’;COMMIT;——將所有修改寫入數據庫ROLLBACK;——所有操作已經COMMIT提交，不能 回滾事務控制示例第41頁,共59頁，星期六，2024年，5月執行Commit或Rollback前的數據狀態數據變化前的狀態可以被恢復當前會話可以使用SELECT語句來驗證DML操作后的結果其它會話不能查看由當前用戶的DML操作結果受影響記錄被鎖定，也就是其它用戶不能改變受影響記錄中的數據8.6.2事務控制6/8第42頁,共59頁，星期六，2024年，5月8.6.2事務控制7/8Commit后的狀態在數據庫中數據變化成為永久性的先前的數據狀態永久性的消失所有用戶/會話都可以查詢提交后的結果鎖定的記錄被釋放，可以有效地被其他用戶操作所有的存儲節點被清除第43頁,共59頁，星期六，2024年，5月8.6.2事務控制8/8Rollback后的數據狀態使用ROLLBACK可以放棄所有懸而未決的變化。數據變化是可以撤銷的先前的數據狀態被恢復鎖定的記錄被釋放所有的存儲節點被清除第44頁,共59頁，星期六，2024年，5月8.6.3讀一致性1/2讀一致性保證了不同會話在同一時間查看數據時，數據一致。ORACLE在兩個不同級別上提供讀一致性：語句級讀一致性和事務級一致性。事務級一致性，當一個會話正在修改數據時，其它的會話將看不到該會話未提交的修改。語句級讀一致性，保證單個查詢所返回的數據與該查詢開始時刻相一致。所以一個查詢從不會看到在查詢執行過程中提交的其它事務所作的任何修改。第45頁,共59頁，星期六，2024年，5月8.6.3讀一致性2/2UPDATEemp

SETsal=2000WHEREename=

'SCOTT';數據塊回滾段發生變化及未發生變化的數據變化前的老數據用戶A用戶B讀取一致的鏡像SELECT*

FROM emp;第46頁,共59頁，星期六，2024年，5月第八章數據操作與事務控制數據操作與事務控制:8.1插入數據8.2修改數據8.3刪除數據8.4合并數據8.5使用ROWID操作數據8.6事務處理8.7鎖第47頁,共59頁，星期六，2024年，5月8.7.1并發事務并發事務并發事務：如果多個事務同時訪問某一個資源，比如同時修改表中的某一行。并發事務可能出現如下問題:1.臟讀取:就是讀取了未提交的數據，如B事務讀取了A事務未提交的數據2.不可重復讀取:就是一個事務在讀取一個數據時，數據不同，可能是另一個事務修改了數據。3.更新丟失:就是兩個事務同時更新了一條數據，先更新的就會丟失數據。4.幻想讀:事務A在讀取數據的時候，數據發生了變化。以上問題如何解決呢？

鎖機制第48頁,共59頁，星期六，2024年，5月8.7.2鎖的概念1/2鎖用來在多用戶并發訪問和操作數據庫時保證數據的一致性。鎖由Oracle自動管理。如一個DML操作，ORACLE默認的機制是在DML操作涉及到的行上加鎖（行級別），但不會在更高的級別（表級別）上加更嚴格的鎖，比如只改某行的數據不會鎖住整個表。這提供了很好的并發性，因為整個表沒有鎖定，只是某些行被鎖定了，其他用戶可以修改其他行數據。查詢不需要任何鎖。第49頁,共59頁，星期六，2024年，5月8.7.2鎖的概念2/2鎖的生命周期，鎖在被相關的操作申請并持有后，會一直保持到事務的結束。事務結束后，鎖才會被釋放。鎖的內部維護機制是采用排隊機制（enqueue），一個對象的排它鎖被持有后，該對象相同級別的鎖被其他事務申請時候，所有等待該鎖的事務都在一個等待隊列中排隊，其他事務處于等待狀態。直到該鎖被釋放，等待的事務才重新競爭使用該資源。鎖的模式排它鎖模式（Exclusive）排它鎖在被釋放之前，會阻止其鎖住的資源被其他任何事務共享。共享模式（Share）第50頁,共59頁，星期六，2024年，5月8.7.3鎖的分類1/2兩種類型的鎖，DML鎖和DDL鎖。DML鎖，也稱數據鎖，用于在數據被多個不同的用戶改變時，保證數據的完整性。DDL鎖，也稱為數據字典鎖，執行DDL語句時，DDL語句涉及到的對象獲得DDL鎖。由于被持有的時間很短，因此很少看到沖突的DDL鎖，并且以nowait方式被請求。第51頁,共59頁，星期六，2024年，5月8.7.3鎖的分類2/23種DDL鎖類型：排它的DDL鎖，很多對象的創建、修改和刪除定義時候都需要獲得該鎖。比如執行CreateTable、DropTable等時會獲得表上