(Les14 Manipulating Large Data Sets)[20171222]

修目标:

    -使用子查询操纵数据

    -展示多表插入特性

    -使用多表插入

        -无条件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伪列*

 

相关文章