1、第第7章章 數(shù)據(jù)操縱與事務(wù)處理數(shù)據(jù)操縱與事務(wù)處理主要內(nèi)容主要內(nèi)容l數(shù)據(jù)插入l數(shù)據(jù)修改l數(shù)據(jù)合并l數(shù)據(jù)刪除l事務(wù)控制7.1 數(shù)數(shù) 據(jù)據(jù) 插插 入入l利用INSERT INTO語句插入數(shù)據(jù)l利用子查詢插入數(shù)據(jù)l向案例數(shù)據(jù)庫表插入初始數(shù)據(jù)l向多個表中插入數(shù)據(jù)7.1.1 利用利用INSERT INTO語句插入數(shù)據(jù)語句插入數(shù)據(jù)l語法INSERT INTO table_name|view_name (column1,column2) VALUES(value1,values, ) l注意如果在如果在INTO子句中沒有指明任何列名，則子句中沒有指明任何列名，則VALUES子句中列值子句中列值的個數(shù)、順序、類

2、型必須與表中列的個數(shù)、順序、類型相匹配。的個數(shù)、順序、類型必須與表中列的個數(shù)、順序、類型相匹配。如果在如果在INTO子句中指定了列名，則子句中指定了列名，則VALUES子句中提供的列值子句中提供的列值的個數(shù)、順序、類型必須與指定列的個數(shù)、順序、類型按位置對的個數(shù)、順序、類型必須與指定列的個數(shù)、順序、類型按位置對應(yīng)。應(yīng)。向表或視圖中插入的數(shù)據(jù)必須滿足表的完整性約束。向表或視圖中插入的數(shù)據(jù)必須滿足表的完整性約束。字符型和日期型數(shù)據(jù)在插入時(shí)要加單引號。日期類型數(shù)據(jù)需要按字符型和日期型數(shù)據(jù)在插入時(shí)要加單引號。日期類型數(shù)據(jù)需要按系統(tǒng)默認(rèn)格式輸入，或使用系統(tǒng)默認(rèn)格式輸入，或使用TO_DATE函數(shù)進(jìn)行日期轉(zhuǎn)

3、換。函數(shù)進(jìn)行日期轉(zhuǎn)換。 l向departments表中插入一行記錄。INSERT INTO departmentsVALUES(departments_seq.nextval, Teaching,200,1800);l向employees表中插入一行記錄。INSERT INTO employees(employee_id, last_name,email,hire_date,job_id,department_id)VALUES(employees_seq.nextval,sun,sysdate,AC_ACCOUNT,200); 7.1.2 利用子查詢插入數(shù)據(jù)利用子查詢插入數(shù)據(jù)l語法INSER

4、T INTO table_name|view_name(column1,column2,)subquery;l注意INTO子句中指定的列的個數(shù)、順序、類型必須與子子句中指定的列的個數(shù)、順序、類型必須與子查詢中列的個數(shù)、順序和類型相匹配。查詢中列的個數(shù)、順序和類型相匹配。 l統(tǒng)計(jì)各個部門的部門號、部門最高工資和最低工資，并將統(tǒng)計(jì)的結(jié)果寫入到表dept_salary_stat（假設(shè)該表已經(jīng)創(chuàng)建）中。 INSERT INTO emp_salary_stat SELECT department_id,max(salary), min(salary) FROM employees GROUP BY de

5、partment_id; l向employees表中插入一個員工信息，員工號為1234，員工姓為“Liu”，email為“”，其他信息與員工號為150的員工信息相同。INSERT INTO employees SELECT 1234, first_name,Liu,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_idFROM employees WHERE employee_id=150; l如果要將大量數(shù)據(jù)插入表中，可以利用子查詢直接裝載的方式進(jìn)行。由于直接裝載數(shù)據(jù)的操作過程不寫入日志文件，因

6、此數(shù)據(jù)插入操作的速度大大提高。l利用子查詢裝載數(shù)據(jù)語法為：INSERT /*+APPEND*/ INTO table_name|view_name(column1,column2,)subquery;l復(fù)制employees表中employee_id，salary，job_id,department_id四列的值，并插入到backup_emp表中 ，語句為：INSERT /*+APPEND*/ INTO backup_empSELECT employee_id,salary,job_id,department_idFROM employees;7.1.3 向案例數(shù)據(jù)庫表插入初始數(shù)據(jù)向案例數(shù)據(jù)庫

7、表插入初始數(shù)據(jù)lINSERT INTO ehr.regions SELECT * FROM hr.regions;lINSERT INTO ehr.countries SELECT * FROM hr. countries;lINSERT INTO ehr.locations SELECT * FROM hr. locations;lINSERT INTO ehr.departments SELECT * FROM hr.departments;lINSERT INTO SELECT * FROM ;lINSERT INTO ehr.employees SELE

8、CT * FROM hr.employees;lINSERT INTO ehr.sal_grades SELECT * FROM hr.sal_grades;lINSERT INTO ehr.job_history SELECT * FROM hr.job_history;7.1.4 向多個表中插入數(shù)據(jù)向多個表中插入數(shù)據(jù)l在Oracle 11g中，可以使用INSERT語句同時(shí)向多個表中插入數(shù)據(jù)。l根據(jù)數(shù)據(jù)插入的條件不同，分為：無條件插入：將數(shù)據(jù)插入所有指定的表中無條件插入：將數(shù)據(jù)插入所有指定的表中有條件插入：將數(shù)據(jù)插入符合條件的表中。有條件插入：將數(shù)據(jù)插入符合條件的表中。l無條件多表插入的基本

9、語法為INSERT ALL INTO table1 VALUES(column1,column2,)INTO table2 VALUES(column1,column2,)subquery;l利用無條件多表插入，查詢employees表中工資高于8000元的員工信息并分別插入emp_sal和emp_mgr表。INSERT ALLINTO emp_sal VALUES(employee_id,hire_date,salary)INTO emp_mgr VALUES(employee_id,manager_id,salary)SELECT employee_id,hire_date,manager

10、_id,salaryFROM employees WHERE salary8000; l有條件多表插入語法為INSERT ALL|FIRST WHEN condition1 THEN INTO table1(column1,) WHEN condition2 THEN INTO table2(column1,)ELSE INTO tablen(column1,)subquery;l參數(shù)說明ALL：表示一條記錄可以同時(shí)插入多個滿足條件的表中；：表示一條記錄可以同時(shí)插入多個滿足條件的表中；FIRST：表示一條記錄只插入第一個滿足條件的表中。：表示一條記錄只插入第一個滿足條件的表中。l 將emplo

11、yees表中員工信息按照不同部門號分別復(fù)制到emp10，emp20，emp30和emp_other表中。同時(shí)，將工資低于5000元的員工信息復(fù)制到lowsal表中，將工資高于10000元的員工信息復(fù)制到highsal表中，將工資在500010000元之間的員工信息復(fù)制到middlesal表中。 INSERT ALLWHEN department_id=10 THEN INTO emp10WHEN department_id =20 THEN INTO emp20WHEN department_id =30 THEN INTO emp30WHEN department_id =40 THEN I

12、NTO emp_otherWHEN salary4000 THEN INTO highsalELSE INTO middlesal SELECT * FROM employees;l多表插入的應(yīng)用利用多表插入技術(shù)可以實(shí)現(xiàn)不同數(shù)據(jù)源之間的數(shù)據(jù)轉(zhuǎn)利用多表插入技術(shù)可以實(shí)現(xiàn)不同數(shù)據(jù)源之間的數(shù)據(jù)轉(zhuǎn)換，可以將非關(guān)系數(shù)據(jù)庫的一條記錄轉(zhuǎn)換為關(guān)系數(shù)據(jù)換，可以將非關(guān)系數(shù)據(jù)庫的一條記錄轉(zhuǎn)換為關(guān)系數(shù)據(jù)庫中的多條記錄。庫中的多條記錄。 l將SALES_SOURCE_DATA表中的記錄轉(zhuǎn)換為SALES_INFO表中的記錄。CREATE TABLE sale_source_data( emp_id NUMBER(6), w

13、eek_id NUMBER(2), sale_MON NUMBER(8,2), sale_TUE NUMBER(8,2), sale_WED NUMBER(8,2), sale_THUR NUMBER(8,2), sale_FRI NUMBER(8,2); INSERT INTO sale_source_data VALUES(7844,1,100,200,300,400,500); lCREATE TABLE sale_info( emp_id NUMBER(6), week NUMBER(2), sale NUMBER(8,2);lINSERT ALL INTO sale_info VAL

14、UES (emp_id,week_id,sale_MON) INTO sale_info VALUES (emp_id,week_id,sale_TUE) INTO sale_info VALUES (emp_id,week_id,sale_WED) INTO sale_info VALUES (emp_id,week_id,sale_THUR) INTO sale_info VALUES (emp_id,week_id, sale_FRI) SELECT * FROM sale_source_data; 7.2數(shù)據(jù)修改數(shù)據(jù)修改l基本語法l修改單行記錄l修改多行記錄l帶有子查詢的修改l基本語法

15、UPDATE table_name|view_nameSET column1=value1,column2=value2WHERE conditionl修改單條記錄UPDATE employees SET salary=salary+100,commission_pct=0.4 WHERE employee_id=100;l修改多條記錄UPDATE employees SET salary=salary+150 WHERE department_id=20;l利用子查詢修改記錄利用子查詢修改記錄UPDATE employees SET salary=300+(SELECT avg(salary

16、) FROM employees WHERE department_id=30)WHERE department_id=50;7.3數(shù)據(jù)合并數(shù)據(jù)合并l利用MERGE語句可以同時(shí)完成數(shù)據(jù)的插入與更新操作。l將源表的數(shù)據(jù)分別與目標(biāo)表中的數(shù)據(jù)根據(jù)特性條件進(jìn)行比較（每次只比較一條記錄），如果匹配，則利用源表中的記錄更新目標(biāo)表中的記錄，如果不匹配，則將源表中的記錄插入目標(biāo)表中。l使用MERGE語句操作時(shí)，用戶需要具有源表的SELECT對象權(quán)限以及目標(biāo)表的INSERT，UPDATE對象權(quán)限。lMERGE語句的基本語法為：MERGE INTO schema.target_table target_alias

17、 USING schema.source_table|source_view|source_subquery source_aliasON (condition)WHEN MATCHED THEN UPDATE SET column1=expression1 ,column2=expression2 where_clauseDELETE where_clauseWHEN NOT MATCHED THEN INSERT (column2,column2) VALUES (expresstion1,expression2) where_clause;l參數(shù)說明INTO：指定進(jìn)行數(shù)據(jù)更新或插入的目標(biāo)

18、表；：指定進(jìn)行數(shù)據(jù)更新或插入的目標(biāo)表；USING：指定用于目標(biāo)表數(shù)據(jù)更新或插入的源表或視圖或子查：指定用于目標(biāo)表數(shù)據(jù)更新或插入的源表或視圖或子查詢；詢；ON：決定：決定MERGE語句執(zhí)行更新操作還是插入操作的條件。對于語句執(zhí)行更新操作還是插入操作的條件。對于目標(biāo)表中滿足條件的記錄，則利用源表中的相應(yīng)記錄進(jìn)行更新；目標(biāo)表中滿足條件的記錄，則利用源表中的相應(yīng)記錄進(jìn)行更新；而源表中不滿條件的記錄將被插入目標(biāo)表中；而源表中不滿條件的記錄將被插入目標(biāo)表中；where_clause：只有當(dāng)該條件為真時(shí)才進(jìn)行數(shù)據(jù)的更新或插：只有當(dāng)該條件為真時(shí)才進(jìn)行數(shù)據(jù)的更新或插入操作；入操作；DELETE where_cl

19、ause：當(dāng)目標(biāo)表中更新后的記錄滿足該條：當(dāng)目標(biāo)表中更新后的記錄滿足該條件時(shí)，則刪除該記錄。件時(shí)，則刪除該記錄。 l使用“雇員”表（別名e）作為數(shù)據(jù)源，在該表 的副本“副本_雇員”表（別名c）中插入并更新行。lMERGE INTO 副本_雇員 c USING 雇員elON (c.雇員標(biāo)識= e.雇員標(biāo)識)lWHEN MATCHED THEN UPDATElSETlc.姓氏 = e.姓氏,lc.部門標(biāo)識 = e.部門標(biāo)識lWHEN NOT MATCHED THEN INSERT lVALUES (e.雇員標(biāo)識, e.姓氏, e.部門標(biāo)識);MERGE INTO 副本_雇員 c USING 雇員e

20、 ON (c.雇員標(biāo)識= e.雇員標(biāo)識) WHEN MATCHED THEN UPDATE SET c.姓氏 = e.姓氏, c.部門標(biāo)識 = e.部門標(biāo)識WHEN NOT MATCHED THEN INSERT VALUES (e.雇員標(biāo)識, e.姓氏, e.部門標(biāo)識); MERGE 語句執(zhí)行前的“副本_雇員”雇員標(biāo)識 姓氏部門標(biāo)識100Smith 40103Chang 30MERGE 執(zhí)行后的“副本_雇員”雇員標(biāo)識 姓氏部門標(biāo)識100King90103Hunold60152Davies 50雇員（來源表）雇員標(biāo)識 姓氏部門標(biāo)識100King90103Hunold60152Davies 50

21、l現(xiàn)有表source_emp和target_emp，表中數(shù)據(jù)如下。利用source_emp表中的數(shù)據(jù)更新target_emp表中的數(shù)據(jù)，對target_emp表中存在的員工信息進(jìn)行更新，對不存在的員工進(jìn)行信息插入。 SELECT * FROM source_emp;EMPNO ENAME DEPTNO-100 JOAN 10110 SMITH 20120 TOM 30 lSELECT * FROM target_emp;lEMPNO ENAME DEPTNOl-l100 MARRY 20l20 JACK 40 lMERGE INTO target_emp tl USING source_emp

22、 sl ON (t.empno=s.empno)l WHEN MATCHED THEN UPDATE SETl t.ename=s.ename,t.deptno=s.deptnol WHEN NOT MATCHED THEN INSERTl VALUES(s.empno,s.ename,s.deptno)；lSELECT * FROM target_emp;lEMPNO ENAME DEPTNOl-l100 JOAN 10l20 JACK 40l110 SMITH 20l120 TOM 30 7.4刪除數(shù)據(jù)刪除數(shù)據(jù)l基本語法l刪除單條記錄l刪除多條記錄l帶有子查詢的刪除操作l利用TRUNCAT

23、E刪除數(shù)據(jù)lTRUNCATE與DELETE區(qū)別l基本語法 DELETE FROM table|view WHERE condition l刪除單條記錄DELETE FROM employees WHERE employee_id=152; l刪除多條記錄DELETE FROM employees WHERE department_id=130 ;l帶有子查詢的刪除操作DELETE FROM employees WHERE salary(SELECT salary FROM employees WHERE employee_id=101);l利用TRUNCATE刪除數(shù)據(jù)TRUNCATE TABL

24、E table_namelTRUNCATE與DELETE區(qū)別釋放存儲空間釋放存儲空間不寫入日志文件，因此執(zhí)行效率較高，但該操作不可不寫入日志文件，因此執(zhí)行效率較高，但該操作不可回滾?；貪L。 7.5事務(wù)控制事務(wù)控制l事務(wù)概念lOracle事務(wù)的隔離級別 lOracle事務(wù)處理7.5.1 事務(wù)概念事務(wù)概念l原子性（Atomicity）：事務(wù)是數(shù)據(jù)庫的邏輯工作單位，事務(wù)中的所有操作要么都做，要么都不做，不存在第三種情況。l一致性（Consistency）：事務(wù)執(zhí)行的結(jié)果必須是使數(shù)據(jù)庫從一個一致性狀態(tài)轉(zhuǎn)變到另一個一致性狀態(tài)，不存在中間的狀態(tài)。l隔離性（Isolation）：數(shù)據(jù)庫中一個事務(wù)的執(zhí)行不受其

25、他事務(wù)干擾，每個事務(wù)都感覺不到還有其他事務(wù)在并發(fā)執(zhí)行。l持久性（Durability）：一個事務(wù)一旦提交，則對數(shù)據(jù)庫中數(shù)據(jù)的改變是永久性的，以后的操作或故障不會對事務(wù)的操作結(jié)果產(chǎn)生任何影響。 7.5.2 Oracle事務(wù)的隔離級別事務(wù)的隔離級別 l數(shù)據(jù)庫中事務(wù)的并發(fā)運(yùn)行，可能導(dǎo)致下列問題：丟失修改：兩個事務(wù)同時(shí)讀取數(shù)據(jù)庫中的同一數(shù)據(jù)并進(jìn)行修丟失修改：兩個事務(wù)同時(shí)讀取數(shù)據(jù)庫中的同一數(shù)據(jù)并進(jìn)行修改，一個事務(wù)提交的結(jié)果破壞了另一個事務(wù)提交的結(jié)果，導(dǎo)改，一個事務(wù)提交的結(jié)果破壞了另一個事務(wù)提交的結(jié)果，導(dǎo)致第一個事務(wù)對數(shù)據(jù)的修改丟失。致第一個事務(wù)對數(shù)據(jù)的修改丟失。讀讀“臟臟”數(shù)據(jù)：一個事務(wù)對數(shù)據(jù)的修改在提

26、交之前被其他事數(shù)據(jù)：一個事務(wù)對數(shù)據(jù)的修改在提交之前被其他事務(wù)讀取。務(wù)讀取。不可重復(fù)讀：在某個事務(wù)讀取一次數(shù)據(jù)后，其他事務(wù)修改了不可重復(fù)讀：在某個事務(wù)讀取一次數(shù)據(jù)后，其他事務(wù)修改了這些數(shù)據(jù)并進(jìn)行了提交，當(dāng)該事務(wù)重新讀取這些數(shù)據(jù)時(shí)就會這些數(shù)據(jù)并進(jìn)行了提交，當(dāng)該事務(wù)重新讀取這些數(shù)據(jù)時(shí)就會得到與前一次不一樣的結(jié)果。得到與前一次不一樣的結(jié)果。幻想讀：事務(wù)幻想讀：事務(wù)T1讀取一條指定讀取一條指定where條件的語句，返回結(jié)條件的語句，返回結(jié)果集。此時(shí)事務(wù)果集。此時(shí)事務(wù)T2插入一行新記錄，恰好滿足插入一行新記錄，恰好滿足T1的的where條件。然后條件。然后T1使用相同的條件再次查詢，結(jié)果集中使用相同的條件

27、再次查詢，結(jié)果集中可以看到可以看到T2插入的記錄，這條新紀(jì)錄就是幻想。插入的記錄，這條新紀(jì)錄就是幻想。lOracle數(shù)據(jù)庫事務(wù)隔離級：READ COMMITED（提交讀）提交讀）p這是事務(wù)的默認(rèn)隔離等級，用于設(shè)置語句級的一致性。每個事務(wù)所執(zhí)行的查詢操作只能獲取在該查詢開始之前（不是該事務(wù)開始之前）已經(jīng)提交的數(shù)據(jù)。該隔離級別可以防止丟失修改和臟讀的問題，但不能防止不可重復(fù)讀的問題。在該級別的事務(wù)中可以執(zhí)行DML操作（若數(shù)據(jù)被加鎖則等待其他事務(wù)解鎖）。SERIALIZABLE（串行化）（串行化）p用于設(shè)置事務(wù)級的一致性，每個事務(wù)只能看到在該事務(wù)開始之前已經(jīng)提交的數(shù)據(jù)。該隔離級的事務(wù)可以防止丟失修改、臟讀和不可重復(fù)讀的問題。在該級別的事務(wù)中可以執(zhí)行DML操作（若數(shù)據(jù)被加鎖則不等待，返回錯誤）。l如果數(shù)據(jù)庫中具有大量并發(fā)事務(wù)，并且應(yīng)用程序的事務(wù)處理能力和響應(yīng)速度是關(guān)鍵因素，則READ COMMITED隔離級比較合適。如果數(shù)據(jù)庫中多個