(Les14 Manipulating Large Data Sets)[20171222]

學習目標： -使用子查詢操縱數據 -展示多表插入特性 -使用多表插入 -無條件INSERT -旋轉INSERT -有條件 ALL INSERT -有條件 FIRST INSERT -合併表行 -跟蹤數據生命周期內的改變使用子查詢操縱數據 -從另外一個表複製數據 -從內聯視圖取回數據 -基於另外一個 ...

學習目標： -使用子查詢操縱數據 -展示多表插入特性 -使用多表插入 -無條件INSERT -旋轉INSERT -有條件 ALL INSERT -有條件 FIRST INSERT -合併表行 -跟蹤數據生命周期內的改變使用子查詢操縱數據 -從另外一個表複製數據 -從內聯視圖取回數據 -基於另外一個表的值更新一個表數據 -基於另外一個表的值刪除一個表數據子查詢可用於從表中檢索數據，您可以將其用作INSERT的輸入到不同的表中。這樣，您可以使用一個SELECT語句輕鬆地將大量數據從一個表複製到另一個表。同樣，可以使用子查詢在UPDATE和DELETE語句的WHERE子句中使用子查詢進行批量更新和刪除。您也可以在SELECT語句的FROM子句中使用子查詢。這被稱為內聯視圖。從子查詢複製數據

INSERT INTO <table_name> [ column (, column ) ] subquery;

　向子查詢插入數據

 INSERT INTO ( subquery ) VALUES(col_value,....);

註意：VALUES輸入值欄位必須同子查詢相匹配，表中NOT NULL欄位必須包含在子查詢中，遵循基表任何規則。　內聯視圖作為源取回數據

SELECT    a.column [ ( , a.column ) , b.column ( , b.column ) ] FROM  <table_name > a JOIN  ( subquery ) b         ON a.column= b.column;

基於子查詢更新數據

 UPDATE <object_table> 
　　　　SET COLUMN = (subquery1),
       SET COLUMN = (subquery2)
            WHERE <conditional>;

註意：子查詢中只能返回一個值不能是一個集合。基於另一個表刪除數據

DELETE FROM <object_table> WHERE COLUMN = (SELECT COLUMN FROM <source_table> WHERE <conditional>);

子查詢DML數據使用WITH CHECK OPTION

 INSERT INTO ( subquery <conditional> WITH CHECK OPTION ) VALUES(col_value,....);

指定WITH CHECK OPTION，如果在INSERT，UPDATE或DELETE語句中使用子查詢來替代表中的某個表，那麼不會操作未包含在子查詢中的行的更改。簡單的講必須滿足條件的行才能INSERT、UPDATE和DELETE。

        10:19:14 SQL> insert into (select employee_id,last_name,email,hire_date,job_id from emp
        10:19:14   2  where employee_id=102 with check option)
        10:19:14   3  values(101,'Kochhar','NKOCHHAR',to_date('1989-09-21','YYYY-MM-DD'), 'HD_VP');
        insert into (select employee_id,last_name,email,hire_date,job_id from emp   
                                                                 
        ERROR 在行 1:
        ORA-01402: 檢視 WITH CHECK OPTION where- 子句違反

        10:18:38 SQL> insert into (select employee_id,last_name,email,hire_date,job_id from emp
        10:18:38   2  where employee_id=101 with check option)
        10:18:38   3  values(101,'Kochhar','NKOCHHAR',to_date('1989-09-21','YYYY-MM-DD'), 'HD_VP');
        已建立 1 個資料列.

顯示預設值 -在INSERT和UPDATE語句中使用DEFAULT關鍵字來識別預設值，如果不存在預設值則使用NULL空值。 -使用DEFAULT選項可以避免在程式中硬編碼預設值或查詢字典以查找它。

   　　　10:24:56 SQL> update emp set job_id=default where employee_id=101;
        update emp set job_id=default where employee_id=101
                       *
        ERROR 在行 1:
        ORA-01407: 無法將 ("HR"."EMP"."JOB_ID") 更新為 NULL
 
        10:26:25 SQL> select salary from emp where employee_id=101;
        SALARY
        ----------
         16990
        10:26:48 SQL> update emp set  salary=default where employee_id=101;
        已更新 1 個資料列.
        10:27:11 SQL> select salary from emp where employee_id=101;
        SALARY
        ----------

多表插入多表插入，從子查詢返回的行通過計算插入到一個或多個表中。多表INSERT語句在數據倉庫非常有用，定期載入數據到數據倉庫，以便於業務分析。從源庫提取數據並將其引入數據倉庫的過程通常稱為ETL，它表示提取、轉換和載入。 -提取，必須從許多不同的來源(如資料庫、應用程式)中識別和提取所屬的數據。 -轉換，數據必須物理運輸到目標系統或中間系統進行進一步處理，在此過程中可以進行一些轉換。 -載入，將數據存放於資料庫。多表INSERT語句是實現SQL數據轉換的技術之一。語法：

　　　　　　　　INSERT ALL
                    INTO <table_a> VALUES (...,...,...)
                    INTO <table_b> VALUES (...,...,...)
                    INTO <table_c> VALUES (...,...,...)
                    SELECT ...
                    FROM <source_table>
                    WHERE <conditional>;

好處： -減少INSERT ... SELECT語句 -減少轉換工作量 -新語句並行化提高性能多表INSERT類型 -無條件INSERT -有條件ALL INSERT -有條件 FIRST INSERT -旋轉INSERT 語法

　　　　　　 INSERT [ALL] [conditional_insert_clause] [insert_into_clause values_clause] (subquery)
            -conditional_insert_clause
                [ALL] [FIRST]
                [WHEN condition THEN] [insert_into_clause values_clause] [ELSE] [insert_into_clause values_clause]

無條件INSERT ALL後面跟多個insert_into_clause來執行無條件的多項INSERT。子查詢每返回一行均執行一次insert_into_clause。

  　　　　INSERT ALL
                INTO <table_name> VALUES(...,...,...)
                INTO <table_name> VALUES(...,...,...)
                (subquery WHERE <conditional>);

有條件INSERT condition_into_clause按條件執行多INSERT。Oracle通過相應的WHEN條件過濾每個insert_into_clause。一個多INSERT語句最多可以包含127個WHEN子句。有條件INSERT ALL Oracle評估每個WHEN子句，而不考慮其他WHEN子句的結果，對條件為真的每個WEHN子句，執行相應的INTO子句。

 　　　　INSERT ALL
                WHEN <conditional> THEN
                    INTO <table_name> VALUES(...,...,...)
                WHEN <conditional> THEN
                    INTO <table_name> VALUES(...,...,...)
                (subquery);

有條件INSERT FIRST Oracle按順序評估每個WHEN子句。如果第一個WHEN子句的計算結果為true，則Oracle執行相應的INTO子句並跳出後續WHEN子句。有條件INSERT ELSE Oracle按順序評估每個WHEN子句。如果WHEN沒有滿足的則執行ELSE中的INTO，再則ELSE也不滿足，則Oracle不會對該行採取任何操作。

　　　　 INSERT FIRST
                WHEN <conditional> THEN
                    INTO <table_name> VALUES(...,...,...)
                WHEN <conditional> THEN
                    INTO <table_name> VALUES(...,...,...)
                ELSE
                    INTO <table_name> VALUES(...,...,...)
                (subquery);

多表INSERT限制操作 -不能在視圖或物化視圖上執行 -不能遠程表(dblink)執行多INSERT -多表INSERT時不能使用表集合表達式，如TABLE1，TABLE2.. -多表INSERT中，所有insert_into_clause組合不能超過999個目標列。旋轉INSERT(行轉列)

　　　　　　 INSERT ALL
                   INTO <table_name> VALUES(col1,col2,col3)
                   INTO <table_name> VALUES(col1,col2,col4)
                   INTO <table_name> VALUES(col1,col2,col5)
            SELECT col1,col2,col3,col4,col5 FROM <table_name> WHERE <conditional>;

MERGE語句

- Oracle支持INSERT、UPDATE和DELETE操作的合併語句。避免了多個DML語句。是否INSERT、UPDATE或DELELE到目標表取決於子句中的條件。 - MERGE語句適用於許多數據倉庫應用程式。例如，在數據倉庫應用程式中，可以需要處理多個源的數據，其中一些數據可能是重覆的，通過MERGE語句，可以有條件地添加或修改行。 -滿足條件存在就UPDATE、不存在就INSERT

　　MERGE INTO <object_table> USING <source_table> ON (join column)
            WHEN MATCHED THEN
                UPDATE SET ...
            WHEN NOT MATCHED THEN
                INSRT (column_list) VALUES...;->註意這裡沒有INTO 關鍵字。

跟蹤數據的改變可以使用閃回版本查詢在一段時間內查看行的所有更改。此功能可以將一個版本子句附加到SELECT 語句，該語句指定查看行值更改的SCN或時間戳範圍。 -undo_retention初始化參數，保留版本的時間值。 -SCN（System Change Number）系統更改號，Oracle分配一個系統更改號(SCN)，以標識每個提交事務的重做記錄。

        21:56:28 SQL> update emp set salary=1000 where employee_id=101;
        已更新 1 個資料列.
        21:57:01 SQL> commit;
        確認完成.
        21:57:04 SQL> update emp set salary=salary+1000 where employee_id=101;
        已更新 1 個資料列.
        21:57:11 SQL> commit;
        確認完成.
        21:57:13 SQL> select salary from emp
        21:57:20   2  versions between scn minvalue and maxvalue
        21:57:21   3  where employee_id=101;
        SALARY
        ----------
         2000
         1000
 
    21:57:22 SQL> select versions_starttime,versions_endtime ,salary from emp
    21:59:06   2  versions between scn minvalue and maxvalue
    21:59:09   3  where employee_id=101;
 
 
VERSIONS_STARTTIME                  VERSIONS_ENDTIME                        SALARY
----------------------------------- ----------------------------------- ----------
22-12月-17 09.57.10 下午                                                     2000
22-12月-17 09.57.01 下午            22-12月-17 09.57.10 下午                  1000
22-12月-17 09.56.16 下午            22-12月-17 09.57.01 下午                  2000
22-12月-17 09.55.58 下午            22-12月-17 09.56.16 下午                  1000
                                   22-12月-17 09.55.58 下午

　　　　versions_starttime和versions_endtime偽列