Chapter20 增強(qiáng)的DML語句

1、OCP007Chapter20 增強(qiáng)的DML語句                                       目的完成本課后，可以做下列事情：描述多表插入的特性使用下列類型的多表插入：無條件插入，回環(huán)插入

2、，條件的ALL INSERT，條件的First insert創(chuàng)建和使用外部表在創(chuàng)建主鍵約束時命名所產(chǎn)生的索引INSERT語句：以這種方式一次只能插入一行UPDATE語句：可以一次更新多行符合條件的行多表插入INSERT.SELECT語句可以在一條DML語句中向多個表插入多行，可以用于數(shù)據(jù)倉庫系統(tǒng)，以從多個數(shù)據(jù)源提取數(shù)據(jù)到目的表中。并且可以顯著的提高性能。ETL，代表數(shù)據(jù)倉庫中常用的抽?。╡xtraction)，傳輸（transformation)，加載（loading)。使用新的多表插入，可以并行的直接加載，取得較高的性能。多表插入的類型：無條件插入；有條件的ALL INSERT；有條件的FI

3、RST INSERT；循環(huán)插入多表插入的語法：INSERT ALL conditional_insert_clauseinsert_into_clause values_clause (subquery)其中的條件插入子句：ALL FIRSTWHEN condition THEN insert_into_clause values_clauseELSE insert_into_clause values_clause無條件插入：ALL into_clause在ALL的后面指定多表插入插入子句，執(zhí)行無條件的多表插入。Oracle服務(wù)器對子查詢返回的每一行都執(zhí)行一次insert_into_clau

4、se子句。條件插入：conditional_insert_clause指定conditional_insert_clause子句可以執(zhí)行條件多表插入。每一個when條件用來判斷是否執(zhí)行插入子句。一個單獨的多表插入語句最多可以有127個when子句。條件插入：ALL如果指定了ALL，Oracle服務(wù)器計算每一個when子句，不考慮計算的結(jié)果。對每一個計算為真的when子句，Oralce服務(wù)器執(zhí)行對應(yīng)的INTO子句。有條件的首次插入（FIRST:INSERT）如果指定FIRST，Oracle服務(wù)器按順序計算每一個when子句。Oracle會執(zhí)行第一個計算為真的when子句對應(yīng)的INTO子句，忽略后

5、面的when子句。條件插入：ELSE子句對于給定的行，如果沒有when子句為真：如果指定了ELSE子句，Oracle服務(wù)器執(zhí)行對應(yīng)的INTO子句如果沒有指定ELSE子句，Oracle不采取任何動作多表插入語句的限制：多表插入只能作用于表不能對遠(yuǎn)程表進(jìn)行多表插入執(zhí)行多表插入時，不能指定表的集合表達(dá)式insert_into_clauses子句不能指定超過999個目標(biāo)行無條件插入：ALLINSERT ALLINTO sal_history VALUES(EMPID,HIREDATE,SAL)INTO mgr_history VALUES(EMPID,MGR,SAL)SELECT employee_i

6、d EMPID,hire_date HIREDATE,salary SAL, manager_id MGRFROM employeesWHERE employee_id > 200;條件插入：ALLINSERT ALLWHEN SAL > 10000 THENINTO sal_history VALUES(EMPID,HIREDATE,SAL)WHEN MGR > 200 THENINTO mgr_history VALUES(EMPID,MGR,SAL)SELECT employee_id EMPID,hire_date HIREDATE,salary SAL, manag

7、er_id MGRFROM employeesWHERE employee_id > 200;條件插入：FIRSTINSERT FIRSTWHEN SAL > 25000 THENINTO special_sal VALUES(DEPTID, SAL)WHEN HIREDATE like ('%00%') THENINTO hiredate_history_00 VALUES(DEPTID,HIREDATE)WHEN HIREDATE like ('%99%') THENINTO hiredate_history_99 VALUES(DEPTID,

8、HIREDATE)ELSEINTO hiredate_history VALUES(DEPTID, HIREDATE)SELECT department_id DEPTID, SUM(salary) SAL,MAX(hire_date) HIREDATEFROM employeesGROUP BY department_id;旋轉(zhuǎn)插入：INSERT ALLINTO sales_info VALUES (employee_id,week_id,sales_MON)INTO sales_info VALUES (employee_id,week_id,sales_TUE)INTO sales_in

9、fo VALUES (employee_id,week_id,sales_WED)INTO sales_info VALUES (employee_id,week_id,sales_THUR)INTO sales_info VALUES (employee_id,week_id, sales_FRI)SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE,sales_WED, sales_THUR,sales_FRIFROM sales_source_data;外部表：外部表是一些只讀表，數(shù)據(jù)存放在數(shù)據(jù)庫之外，metadata存放在數(shù)據(jù)庫內(nèi)。使用外部

10、表特性，可以把外部數(shù)據(jù)作為一個虛擬表使用?？梢灾苯訉ν獠勘磉M(jìn)行查詢和連接的操作，不需要加載到數(shù)據(jù)庫。外部表和常規(guī)表的最大區(qū)別在于外部表是只讀的，而且也不可以創(chuàng)建索引。使用CREATE TABLE . ORGANIZATION EXTERNAL來創(chuàng)建外部表的metadata，這些外部表可以被認(rèn)為是一個視圖。Oracle服務(wù)器提供了兩種主要的訪問驅(qū)動以使用外部表。一種是ORACLE_LOADER，采用Oracle Loader技術(shù)來讀取外部表里面的數(shù)據(jù)。另一種是import/export訪問驅(qū)動，也稱為ORACLE_INTERNAL，可以導(dǎo)入導(dǎo)出與平臺無關(guān)的格式。外部表子句包含訪問驅(qū)動的類型（缺省

11、是ORACLE_LOADER）、external_data_properties, and the REJECT LIMIT.external_data_properties包含： DEFAULT DIRECTORY ACCESS PARAMETERS LOCATIONREJECT LIMIT指定發(fā)生多少個轉(zhuǎn)換錯誤時，Oracle返回錯誤放棄查詢數(shù)據(jù)。DEFAULT DIRECTORY指定外部表的位置，可以指定多個位置，增加并行性和負(fù)載均衡。后面兩個參數(shù)都是訪問驅(qū)動程序使用的，Oracle服務(wù)器并不解釋它們。創(chuàng)建外部表：首先創(chuàng)建目錄對象，以對應(yīng)外部表在系統(tǒng)上面的存放位置CREATE OR RE

12、PLACE DIRECTORY AS 'path_name'然后再創(chuàng)建外部表，如：CREATE TABLE oldemp (empno NUMBER, empname CHAR(20), birthdate DATE)ORGANIZATION EXTERNAL(TYPE ORACLE_LOADERDEFAULT DIRECTORY emp_dirACCESS PARAMETERS(RECORDS DELIMITED BY NEWLINEBADFILE 'bad_emp'LOGFILE 'log_emp'FIELDS TERMINATED BY ','(empno CHAR,empname CHAR,birthdate CHAR date_format date mask "dd-mon-yyyy")LOCATION ('emp1.txt')PARALLEL 5   ->外部表可能很大，建議使用這個