數(shù)據(jù)操作與事務(wù)控制

數(shù)據(jù)操作與事務(wù)控制課程結(jié)構(gòu)內(nèi)容課時（H）第一章Oracle數(shù)據(jù)庫基礎(chǔ)1.8第二章編寫簡單的查詢語句1.2第三章限制數(shù)據(jù)和對數(shù)據(jù)排序1.5第四章單行函數(shù)2.5第五章多表查詢2第六章分組函數(shù)2第七章子查詢2第八章數(shù)據(jù)操作與事務(wù)控制3.5第九章表和約束4第十章其他數(shù)據(jù)庫對象2.5第2頁,共59頁，星期六，2024年，5月第八章數(shù)據(jù)操作與事務(wù)控制目標:本章旨在向?qū)W員介紹：1)數(shù)據(jù)操作語句2）事務(wù)控制語句時間：3.5學(xué)時教學(xué)方法：講授ppt＋上機練習(xí)第3頁,共59頁，星期六，2024年，5月本章要點INSERT語句UPDATE語句DELETE語句9i新增MERGE語句COMMIT命令ROLLBACK命令管理鎖第4頁,共59頁，星期六，2024年，5月第八章數(shù)據(jù)操作與事務(wù)控制數(shù)據(jù)操作語言（DML：DataManipulationLanguage）主要包括以下語句：INSERTUPDATEDELETEMERGE事務(wù)是一組相關(guān)的DML語句的邏輯組合。事務(wù)控制主要包括下列命令：COMMITROLLBACKSAVEPOINT第5頁,共59頁，星期六，2024年，5月第八章數(shù)據(jù)操作與事務(wù)控制數(shù)據(jù)操作與事務(wù)控制:8.1插入數(shù)據(jù)8.2修改數(shù)據(jù)8.3刪除數(shù)據(jù)8.4合并數(shù)據(jù)8.5使用ROWID操作數(shù)據(jù)8.6事務(wù)處理8.7鎖第6頁,共59頁，星期六，2024年，5月8.1.1INSERT語法結(jié)構(gòu)語法如下：一次只插入一行NULL的使用，連續(xù)的單引號（‘’）也可以表示空值。插入日期型數(shù)據(jù)插入特殊字符插入多行數(shù)據(jù)按列的默認順序列出各個列的值在Insert子句中可以隨意列出列名和他們的值字符和日期型數(shù)據(jù)應(yīng)該包含在單引號中INSERTINTO表名[(列名1[,列名2，…，列名n])]VALUES(值1[,值2，…，值n]);第7頁,共59頁，星期六，2024年，5月8.1.2INSERT語句插入單行數(shù)據(jù)1/2例8-1將一個新成立部門的信息寫入departments表INSERTINTOdepartmentsVALUES(300,'Operations',110,1500);第8頁,共59頁，星期六，2024年，5月8.1.2INSERT語句插入單行數(shù)據(jù)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語句插入日期型數(shù)據(jù)例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轉(zhuǎn)義符用哪個符號表示。INSERT語句中使用“\”符對特殊符號轉(zhuǎn)義。INSERTINTOtestVALUES（‘\&TEST\&’）;SHOWESCAPE；——查看ESCAPE狀態(tài)escapeOFF——返回ESCAPE狀態(tài)為OFFSETESCAPEON；——設(shè)定ESCAPE狀態(tài)為ONSHOWESCAPE；——查看ESCAPE狀態(tài)escape"\"(hex5c)——返回ESCAPE符號為“\”

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

INSERT子句中列的數(shù)量和類型必須和子查詢中列的數(shù)量和類型相匹配第13頁,共59頁，星期六，2024年，5月第八章數(shù)據(jù)操作與事務(wù)控制數(shù)據(jù)操作與事務(wù)控制:8.1插入數(shù)據(jù)8.2修改數(shù)據(jù)8.3刪除數(shù)據(jù)8.4合并數(shù)據(jù)8.5使用ROWID操作數(shù)據(jù)8.6事務(wù)處理8.7鎖第14頁,共59頁，星期六，2024年，5月8.2.1UPDATE語法結(jié)構(gòu)UPDATE語法結(jié)構(gòu)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嵌入子查詢修改除基于表自身實現(xiàn)嵌入子查詢的方式實現(xiàn)修改操作外，也可以在子查詢中基于其他表實現(xiàn)修改操作。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相關(guān)子查詢修改updateemployeesasetsalary=salary+(select

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

where

exists(select'1'fromemployeesbwhereb.employee_id=a.employee_id)第23頁,共59頁，星期六，2024年，5月第八章數(shù)據(jù)操作與事務(wù)控制數(shù)據(jù)操作與事務(wù)控制:8.1插入數(shù)據(jù)8.2修改數(shù)據(jù)8.3刪除數(shù)據(jù)8.4合并數(shù)據(jù)8.5使用ROWID操作數(shù)據(jù)8.6事務(wù)處理8.7鎖第24頁,共59頁，星期六，2024年，5月8.4合并數(shù)據(jù)根據(jù)指定的條件執(zhí)行插入或者更新操作如果滿足條件的行存在，執(zhí)行更新操作，否則執(zhí)行插入操作優(yōu)點： 避免獨立的數(shù)據(jù)更新 提高效率而且使用方便 在數(shù)據(jù)倉庫應(yīng)用中經(jīng)常使用第25頁,共59頁，星期六，2024年，5月8.4.1MERGE語法結(jié)構(gòu)1/2MERGE語法結(jié)構(gòu)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語法結(jié)構(gòu)2/2INTO子句：指定更新或插入的目標表USING子句：指定更新或插入的數(shù)據(jù)源，它可以是表、視圖、子查詢ON子句：合并操作的條件判斷語句 第27頁,共59頁，星期六，2024年，5月8.4.2MERGE語句合并數(shù)據(jù)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月第八章數(shù)據(jù)操作與事務(wù)控制數(shù)據(jù)操作與事務(wù)控制:8.1插入數(shù)據(jù)8.2修改數(shù)據(jù)8.3刪除數(shù)據(jù)8.4合并數(shù)據(jù)8.5使用ROWID操作數(shù)據(jù)8.6事務(wù)處理8.7鎖第29頁,共59頁，星期六，2024年，5月8.5.1ROWID介紹1/2ROWID：偽列，是表中虛擬的列，是系統(tǒng)自動產(chǎn)生的。每一行記錄中都包含ROWID，表示這一行的唯一地址。ROWID標識了Oracle如何定位行，通過ROWID能快速定位一行記錄。ROWIDCNAMEAAAHjXAAOAAAADaAAA姚明AAAHjXAAOAAAADaAAC成龍SELECTrowid,cnameFROMdossier;第30頁,共59頁，星期六，2024年，5月8.5.1ROWID介紹2/2ROWID的格式：ROWID中包含該行數(shù)據(jù)的物理位置信息，所以能快速的定位記錄ROWIDCNAMEAAAHjXAAOAAAADaAAA姚明AAAHjXAAOAAAADaAAC成龍例：AAAHjXAAOAAAADaAAA數(shù)據(jù)對象編號文件編號塊編號行編號

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

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

SETsal=2000WHEREename=

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

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

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