1、Oracle SQL開發(fā)基礎(chǔ)課程結(jié)構(gòu)2內(nèi)容課時(shí)（H）第一章 Oracle數(shù)據(jù)庫基礎(chǔ)2.5第二章 編寫簡單的SELECT語句1.5第三章 限制數(shù)據(jù)和對(duì)數(shù)據(jù)排序2第四章 單行函數(shù)4第五章 多表4第六章 分組函數(shù)3第七章 子4第八章 數(shù)據(jù)操作與事務(wù)4第九章 表和約束4第十章 其他數(shù)據(jù)庫對(duì)象4第八章 數(shù)據(jù)操作與事務(wù)目標(biāo):本章旨在向?qū)W員介紹：1) 數(shù)據(jù)操作語句時(shí)間： 4學(xué)時(shí)教學(xué)方法：講授ppt上2）事務(wù)語句3本章要點(diǎn) INSERT語句 UPDATE語句 DELETE語句 9i新增MERGE語句 COMMIT命令 ROLLBACK命令 管理鎖4第八章 數(shù)據(jù)操作與事務(wù)數(shù)據(jù)操作語言（DML： Data Man

2、ipulation Language） 主要包括以下語句： INSERT UPDATE DELETE MERGE事務(wù)是一組相關(guān)的DML語句的邏輯組合。事務(wù)列命令： COMMIT ROLLBACK SAVEPOINT主要包括下5第八章 數(shù)據(jù)操作與事務(wù)數(shù)據(jù)操作與事務(wù):數(shù)據(jù)8.18.2 修改數(shù)據(jù)8.3 刪除數(shù)據(jù)8.4 合并數(shù)據(jù)8.5 使用ROWID操作數(shù)據(jù)8.6 事務(wù)處理8.7 鎖68.1.1INSERT語法結(jié)構(gòu) 語法如下：一次只一行NULL的使用，連續(xù)的單引號(hào)（）也可以表示空值。日期型數(shù)據(jù)特殊字符 多行數(shù)據(jù)按列的默認(rèn)順序列出各個(gè)列的值在Insert子句中可以隨意列出列名和他們的值字符和日期型數(shù)據(jù)應(yīng)

3、該包含在單引號(hào)中7INSERT INTO 表名(列名1,列名2，列名n)VALUES (值1,值2，值n);8.1.2INSERT語句單行數(shù)據(jù) 1/2 例8-1 將一個(gè)新成立部門的信息寫入departments表8INSERT INTO departmentsVALUES(300,'Operations',110,1500);8.1.2INSERT語句 顯示默認(rèn)值概述 使用Default表示默認(rèn)值 符合SQL：1999標(biāo)準(zhǔn) 可以使用顯示默認(rèn)值單行數(shù)據(jù) 2/2默認(rèn)值的使用 顯示默認(rèn)值可以在insert和update語句中使用98.1.3INSERT語句空值（NULL） 例8-2

4、將一個(gè)新成立部門的信息寫入departments中 ，其中管理者未知。或或10INSERT INTO departments (department_id,department_name,location_id)VALUES(310,'Operations',1500);INSERT INTO departments VALUES(310,'Operations', '',1500);INSERT INTO departments VALUES(310,'Operations', NULL,1500);8.1.4INSERT語句日

5、期型數(shù)據(jù) 例8-5 將一新入職員工信息寫入employees表或11INSERT INTOemployees(employee_id,last_name,hire_date,job_id)VALUES(210,Wang,SWANG,TO_DATE(2006-9-10,YYYY-MM-DD),IT_PROG);INSERT INTOemployees(employee_id,last_name,hire_date,job_id)VALUES(210,Wang,SWANG, 10-9月- 06,IT_PROG);8.1.5INSERT語句特殊字符12INSERT INTO testVALUES（&

6、amp;TEST&）;SHOW ESCAPE； 查看ESCAPE狀態(tài)escape OFF返回ESCAPE狀態(tài)為OFF SET ESCAPE ON； 設(shè)定ESCAPE狀態(tài)為ON SHOW ESCAPE； 查看ESCAPE狀態(tài)escape "" (hex 5c)返回ESCAPE符號(hào)為“”INSERT INTO test VALUES（&TEST&）;8.1.6INSERT語句 語法多行數(shù)據(jù) 例8-7 將受雇日期在“1995-1-1”之前的員工信息到hemployees表中。不必書寫values子句INSERT子句中列的數(shù)量和類型必須和子和類型相匹配中列的

7、數(shù)量13INSERT INTO hemployees SELECT * FROM employeesWHERE hire_date<TO_DATE(1995-1-1,YYYY-MM-DD);INSERT INTO表名(列名1,列名2，列名n) 子；第八章 數(shù)據(jù)操作與事務(wù)數(shù)據(jù)操作與事務(wù):數(shù)據(jù)8.18.2 修改數(shù)據(jù)8.3 刪除數(shù)據(jù)8.4 合并數(shù)據(jù)8.5 使用ROWID操作數(shù)據(jù)8.6 事務(wù)處理8.7 鎖148.2.1UPDATE語法結(jié)構(gòu) UPDATE語法結(jié)構(gòu) UPDATE簡單修改15UPDATE employeesSET salary=salary+200,manager_id=103 WHE

8、RE department_id=60;UPDATE employees SET salary=salary*(1+0.2);UPDATE 表名 SET 列名=表達(dá)式，列名=表達(dá)式，··· WHERE 條件表達(dá)式；8.2.2UPDATE嵌入子修改 1/2嵌入子修改除基于表自身實(shí)現(xiàn)嵌入子的方式實(shí)現(xiàn)修改操作外，也可以在子中基于其他表實(shí)現(xiàn)修改操作。16UPDATE employeesSET department_id=10,salary=500+(SELECT AVG(salary)FROM employees)WHERE job_id=(SELECT job_id F

9、ROM employeesWHERE employee_id=110)AND employee_id<>110;8.2.2UPDATE嵌入子修改 2/2相關(guān)子修改 update employees a set salary = salary+(select avg(salary) from employees bwhere b.department_id = a.department_id)178.2.3修改數(shù)據(jù)(完整性錯(cuò)誤)update employees set department_id = 55 where department_id = 100;ORA-02291:到父項(xiàng)

10、關(guān)鍵字完整約束條件 (NEU.EMP_DEPT_FK) - 未找18第八章 數(shù)據(jù)操作與事務(wù)數(shù)據(jù)操作與事務(wù):數(shù)據(jù)8.18.2 修改數(shù)據(jù)8.3 刪除數(shù)據(jù)8.4 合并數(shù)據(jù)8.5 使用ROWID操作數(shù)據(jù)8.6 事務(wù)處理8.7 鎖198.3.1DELETE語法結(jié)構(gòu)DELETE語法結(jié)構(gòu) DELETE刪除數(shù)據(jù)20DELETE FROM departments WHERE department_id=210;DELETE FROM 表名WHERE 條件表達(dá)式；8.3.2DELETE刪除數(shù)據(jù) 1/3 例8-12 刪除管理者編號(hào)（manager_id）為205的部門，相應(yīng)部門的員工予以解聘，不包括205號(hào)員工。

11、DELETE語句不能刪除被其他表了的值。21DELETE FROM employeesWHERE department_id IN (SELECT department_idFROM departmentsWHERE manager_id =205)AND employee_id<>205;8.3.2DELETE刪除數(shù)據(jù) 2/3 刪除數(shù)據(jù) （完整性錯(cuò)誤）delete from departments where department_id = 100; ORA-02292:完整約束條件 (NEU.EMP_DEPT_FK) - 已找到子日志228.3.2DELETE刪除數(shù)據(jù) 3/3

12、刪除數(shù)據(jù) （相關(guān)子 delete emp_copy a）where exists (select '1' from employees b whereb.employee_id = a.employee_id)23第八章 數(shù)據(jù)操作與事務(wù)數(shù)據(jù)操作與事務(wù):數(shù)據(jù)8.18.2 修改數(shù)據(jù)8.3 刪除數(shù)據(jù)8.4 合并數(shù)據(jù)8.5 使用ROWID操作數(shù)據(jù)8.6 事務(wù)處理8.7 鎖248.4合并數(shù)據(jù) 根據(jù)指定的條件執(zhí)行或者更新操作 如果滿足條件的行存在，執(zhí)行更新操作，否則執(zhí)行操作 優(yōu)點(diǎn)：避免的數(shù)據(jù)更新提高效率而且使用方便在數(shù)據(jù)倉庫應(yīng)用中經(jīng)常使用258.4.1MERGE語法結(jié)構(gòu) 1/2 MERGE

13、語法結(jié)構(gòu)26MERGE INTO table_name t_alias USING table | view | subquery t_alias ON ( join condition )WHEN MATCHED THENUPDATE SETcol1=col1_val,col2=col2_val··· WHEN NOT MATCHED THENINSERT（column_list）VALUES（column_values）;8.4.1MERGE語法結(jié)構(gòu) 2/2 INTO 子句：指定更新或 USING 子句：指定更新或子 ON 子句：合并操作的條件的目標(biāo)表的數(shù)據(jù)源

14、，它可以是表、視圖、語句278.4.2MERGE語句合并數(shù)據(jù)該例子顯示匹配employees表中的employee_id列與emp表中的employee_id列。如果找到了一個(gè)匹配，用employees表中匹配行的列值更新emp表中匹配的列值。如果相匹配行沒有找到， employees表中的列值入到emp表中。28MERGE INTO emp aUSING employees b ON(a.employee_id=b.employee_id) WHEN MATCHED THENUPDATE SETa.=b.,a.phone_number=b.phone_number,a.salary=b.sa

15、lary, a.manager_id=b.manager_id, a.department_id=b.department_idWHEN NOT MATCHED THEN INSERT(employee_id,phone_number,salary,manager_id,departm ent_id)VALUES(b.employee_id, b., b.phone_number, b.salary,b.manager_id, b.department_id);第八章 數(shù)據(jù)操作與事務(wù)數(shù)據(jù)操作與事務(wù):數(shù)據(jù)8.18.2 修改數(shù)據(jù)8.3 刪除數(shù)據(jù)8.4 合并數(shù)據(jù)8.5 使用ROWID操作數(shù)據(jù)8.6

16、事務(wù)處理8.7 鎖298.5.1ROWID介紹 1/2 ROWID： 偽列，是表中虛擬的列，是系統(tǒng)自動(dòng)產(chǎn)生的。中都包含ROWID，表示這一行的唯一地址。 每一行 ROWID標(biāo)識(shí)了Oracle如何行，通過 ROWID 能快速定位一行。30ROWIDCNAMEAAAHjXAAOAAAADaAAA姚明AAAHjXAAOAAAADaAAC成龍l SELECT rowid,cnamel FROM dossier;8.5.1ROWID介紹 2/2 ROWID的格式： ROWID中包含該行數(shù)據(jù)的物理位置信息，所以能快速的定位31ROWIDCNAMEAAAHjXAAOAAAADaAAA姚明AAAHjXAAOA

17、AAADaAAC成龍l 例： AAAHjXAAOAAAADaAAA數(shù)據(jù)對(duì)象編編號(hào) 塊編號(hào)行編號(hào)AAAHjXAAOAAAADa AAAl 行編號(hào)l 塊編號(hào)l 相關(guān)文件編號(hào)l 數(shù)據(jù)對(duì)象編號(hào)l RRRl BBBBBBl FFFl OOOOOO8.5.2使用ROWID進(jìn)行數(shù)據(jù)操作 ROWID的應(yīng)用：，DML語句可以使用ROWID操作數(shù)據(jù)， 快速單行效率最快 作為表行的唯一標(biāo)識(shí)32例:使用ROWNUM修改數(shù)據(jù)UPDATE employeesSETfirst_name = first_name | '*'WHERE ROWID = 'AAAMg6AAFAAAABUAAA'第

18、八章 數(shù)據(jù)操作與事務(wù)數(shù)據(jù)操作與事務(wù):數(shù)據(jù)8.18.2 修改數(shù)據(jù)8.3 刪除數(shù)據(jù)8.4 合并數(shù)據(jù)8.5 使用ROWID操作數(shù)據(jù)8.6 事務(wù)處理8.7 鎖338.6.1事務(wù)概念及特征 1/3 概念：事務(wù)（Transaction）也稱工作單元，是一個(gè)或多個(gè)SQL 語句所組成的序列，這些SQL操作作為一個(gè)完整的工作單元， 要么全部執(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ù)在該組語句中保持一致 一個(gè) DDL語句或者一個(gè) DCL語句或者一個(gè)TCL語句348.6.1事

19、務(wù)概念及特征 2/3 可用四個(gè)字母的縮寫表示：即ACID 原子性（Atomicity） 事務(wù)就像一個(gè)的工作單元。原子性保證要么所有的操作都，要不全都失敗。如果所有的動(dòng)作都了，我們就說這個(gè)事務(wù)了，不然就是失敗的，然后回滾。 一致性（Consistency） 一旦事務(wù)完成了（不管是的，還是失敗的），整個(gè)系統(tǒng)處于操作規(guī)則的統(tǒng)一狀態(tài)，也就是說，數(shù)據(jù)損壞。358.6.1事務(wù)概念及特征 3/3性（Isolation） 事務(wù)的性是指數(shù)據(jù)庫中一個(gè)事務(wù)的執(zhí)行不能被其它事務(wù)干擾。所以，事務(wù)應(yīng)該起來，目的為了防止同時(shí)的讀和寫操作。這就需要事務(wù)與鎖同時(shí)使用。 持久性（Durability）性（Permanence），

20、指事務(wù)一旦 事務(wù)的持久性也稱為提交，則其對(duì)數(shù)據(jù)庫中數(shù)據(jù)的改變就是的。368.6.2事務(wù)1/8事務(wù)的顯示分為：顯式：及隱式。 顯示提交：Commit 顯示回滾：Rollback隱式：隱式提交：當(dāng)下列任意一種情況發(fā)生時(shí)，會(huì)發(fā)生隱式提交a. 執(zhí)行一個(gè)DDL 語句b. 執(zhí)行一個(gè)DCL 語句c.從 SQL*Plus正常（即使用EXIT或QUIT命令）隱式回滾：當(dāng)下列任意一種情況發(fā)生時(shí)，會(huì)發(fā)生隱式回滾a. 從SQL*Plus中強(qiáng)行b. 客戶端連接到服務(wù)器端異常中斷c.系統(tǒng)注意：PL/Sql developer工具和Sqlplus略有不同。378.6.2事務(wù) 事務(wù)2/8令主要有以下三個(gè)： 事務(wù)提交：COMM

21、IT 事務(wù)回滾：ROLLBACK 設(shè)立保存點(diǎn)：SAVEPOINT（作為輔助命令使用） 設(shè)置保存點(diǎn)語法： SAVEPOINT 保存點(diǎn)名稱； ROLLBACK TO保存點(diǎn)名稱;定義保存點(diǎn)回滾到已定義保存點(diǎn)388.6.2事務(wù)3/8 開始：事務(wù)開始于上一個(gè)事務(wù)結(jié)束后執(zhí)行的第一個(gè)DML語句 結(jié)束：事務(wù)結(jié)束于下面的任一種情況的發(fā)生： 執(zhí)行了COMMIT 或者ROLLBACK命令 隱式提交（單個(gè)的DDL或DCL語句）或自動(dòng)提交 用戶 系統(tǒng)398.6.2事務(wù)l 事務(wù)自動(dòng)提交 設(shè)置格式：4/8 例8-14 SQL*Plus自動(dòng)提交的應(yīng)用示例40SHOW AUTOCOMMIT;查看AUTOCOMMIT變量狀態(tài)au

22、tocommit OFFSET AUTOCOMMIT ON； 把變量狀態(tài)設(shè)置為ONINSERT INTO test VALUES (TEST);已創(chuàng)建 1 行提交完成已經(jīng)自動(dòng)提交SET AUTOCOMMIT ON|OFF;8.6.2事務(wù)5/8l 事務(wù)示例41DELETE FROM test ;ROLLBACK;撤消DELETE操作INSERT INTO test VALUES(A);SAVEPOINT insert_a;定義insert_a保存點(diǎn)INSERT INTO test VALUES(B);SAVEPOINT insert_b;定義insert_b保存點(diǎn)INSERT INTO test

23、 VALUES(C);ROLLBACK TO insert_b;撤消操作到insert_b保存點(diǎn)DELETE FROM test WHERE test_str = A;COMMIT;將所有修改寫入數(shù)據(jù)庫ROLLBACK;所有操作已經(jīng)COMMIT提交，不能回滾8.6.2事務(wù)6/8 執(zhí)行Commit或Rollback前的數(shù)據(jù)狀態(tài) 數(shù)據(jù)變化前的狀態(tài)可以被恢復(fù) 當(dāng)前會(huì)話可以使用SELECT語句來驗(yàn)證 DML操作后的結(jié)果 其它會(huì)話不能查看由當(dāng)前用戶的DML操作結(jié)果 受影響中的數(shù)據(jù)被鎖定，也就是其它用戶不能改變受影響428.6.2事務(wù)7/8 Commit后的狀態(tài) 在數(shù)據(jù)庫中數(shù)據(jù)變化成為性的 先前的數(shù)據(jù)狀態(tài)

24、性的消失 所有用戶/會(huì)話都可以提交后的結(jié)果 鎖定的 所有的被，可以有效地被其他用戶操作節(jié)點(diǎn)被清除438.6.2事務(wù)8/8 Rollback后的數(shù)據(jù)狀態(tài) 使用ROLLBACK 可以放棄所有懸而未決的變化。¯ 數(shù)據(jù)變化是可以撤銷的¯ 先前的數(shù)據(jù)狀態(tài)被恢復(fù)¯ 鎖定的¯ 所有的被節(jié)點(diǎn)被清除448.6.3讀一致性 1/2讀一致性保證了不同會(huì)話在同一時(shí)間查看數(shù)據(jù)時(shí)，數(shù)據(jù)一致。ORACLE在兩個(gè)不同級(jí)別上提供讀一致性：語句級(jí)讀一致性和 事務(wù)級(jí)一致性。事務(wù)級(jí)一致性，當(dāng)一個(gè)會(huì)話正在修改數(shù)據(jù)時(shí)，其它的會(huì)話將看 不到該會(huì)話未提交的修改。語句級(jí)讀一致性，保證單個(gè)所返回的數(shù)據(jù)與該

25、開始時(shí)刻相一致。所以一個(gè)從看到在執(zhí)行過程中提交的其它事務(wù)所作的任何修改。458.6.3讀一致性 2/2數(shù)據(jù)塊UPDATE empSETsal = 2000WHERE ename ='SCOTT'回滾段用戶A發(fā)生變化及未發(fā)生 變化的數(shù)據(jù)SELECT *一致的鏡FROMemp;像變化前的老數(shù)據(jù)用戶 B46第八章 數(shù)據(jù)操作與事務(wù)數(shù)據(jù)操作與事務(wù):數(shù)據(jù)8.18.2 修改數(shù)據(jù)8.3 刪除數(shù)據(jù)8.4 合并數(shù)據(jù)8.5 使用ROWID操作數(shù)據(jù)8.6 事務(wù)處理8.7 鎖478.7.1并發(fā)事務(wù) 并發(fā)事務(wù) 并發(fā)事務(wù)：如果多個(gè)事務(wù)同時(shí)修改表中的某一行。 并發(fā)事務(wù)可能出現(xiàn)如下問題: 1.臟 2.不可重復(fù)

26、3.更新丟失 4.幻想讀某一個(gè)，比如同時(shí)48以上問題如何解決呢？ 鎖機(jī)制8.7.2鎖的概念 1/2 鎖用來在多用戶并發(fā)和操作數(shù)據(jù)庫時(shí)保證數(shù)據(jù)的一致性。鎖由Oracle自動(dòng)管理。如一個(gè)DML操作，ORACLE默認(rèn)的機(jī)制是在DML操作涉及到的行上加鎖（行級(jí)別），但在更高的級(jí)別（表級(jí)別）上加更嚴(yán)格的鎖，比如只改某行的數(shù)據(jù)鎖住整個(gè)表。這提供了很好的并發(fā)性，因?yàn)檎麄€(gè)表沒有鎖定，只 是某些行被鎖定了，其他用戶可以修改其他行數(shù)據(jù)。不需要任何鎖。498.7.2鎖的概念 2/2鎖的生命周期，鎖在被相關(guān)的操作申請(qǐng)并持有后，會(huì)一直保持到事務(wù)的結(jié)束。事務(wù)結(jié)束后，鎖才會(huì)被。鎖的內(nèi)部維護(hù)機(jī)制是采用排隊(duì)機(jī)制（enqueue

27、），一個(gè)對(duì)象的排它鎖被持有后，該對(duì)象相同級(jí)別的鎖被其他事務(wù)申請(qǐng)時(shí)候， 所有等待該鎖的事務(wù)都在一個(gè)等待隊(duì)列中排隊(duì)，其他事務(wù)處于等待狀態(tài)。直到該鎖被源。鎖的模式，等待的事務(wù)才重新競爭使用該資 排它鎖模式（Exclusive）排它鎖在被之前，會(huì)其鎖住的被其他任何事務(wù)共享。 共享模式（Share）508.7.3鎖的分類 1/2 兩種類型的鎖，DML鎖和DDL鎖。 DML鎖，也稱數(shù)據(jù)鎖，用于在數(shù)據(jù)被多個(gè)不同的用戶改變時(shí)，保證數(shù)據(jù)的完整性。 DDL鎖，也稱為數(shù)據(jù)字典鎖，執(zhí)行DDL語句時(shí)，DDL語句涉及 到的對(duì)象獲得DDL鎖。由于被持有的時(shí)間很短，因此很少看到的DDL鎖，并且以nowait方式被請(qǐng)求。518

28、.7.3鎖的分類 2/2 3種DDL鎖類型： 排它的DDL鎖，很多對(duì)象的創(chuàng)建、修改和刪除定義時(shí)候都需要獲得該鎖。比如執(zhí)行Create Table、Drop Table等時(shí)會(huì)獲得表上的排它DDL鎖。 共享的DDL鎖。在執(zhí)行Grant、Create Procedure等命令時(shí)， 會(huì)獲得命令相關(guān)操作對(duì)象的共享DDL鎖。 Breakable Parse Lock，用來在共享SQL區(qū)校驗(yàn)語句。528.7.4鎖的使用 1/3 ORACLE中實(shí)現(xiàn)加鎖機(jī)制主要有兩種方式： 1.自動(dòng)加鎖 2.手動(dòng)加鎖538.7.4鎖的使用 2/3 自動(dòng)加鎖 用戶在執(zhí)行INSERT，UPDATE，DELETE，DCL，DDL語句時(shí)，ORACLE會(huì)自動(dòng)加鎖。 在一個(gè)事務(wù)中，ORACLE會(huì)自動(dòng)鎖定執(zhí)行以上語句的數(shù)據(jù)庫對(duì)象，只有事務(wù)結(jié)束（提交或者回滾），才能被。 默認(rèn)情況下，更新數(shù)據(jù)時(shí)ORACLE會(huì)自動(dòng)提供排他鎖，使不同用戶在更新表中同一數(shù)據(jù)時(shí)發(fā)生。l 例 模擬兩個(gè)會(huì)話，同時(shí)修