[Oracle数据库学习]二十、Oracle9i对DML和DDL语句的扩展

D13

多表INSERT语句

INSERT...SELECT是使用一个DML语句向多个表中插入数据的一部分

多表INSERT语句可作为数据仓库应用中向目标数据库传送数据的一种方法
它具有更高的效率:
1）避免使用多个DML语句
2）使用一个DML完成IF...THEN的逻辑处理

 

多表INSERT语句类型

Oracle9i提供以下四种多表INSERT语句类型:
1）无条件的INSERT
2）有条件的ALL INSERT
3）有条件的FIRST INSERT
4）旋转INSERT

 

语法

无条件：

INSERT [ALL] [conditional_insert_clause]
[insert_into_clause values_clause] (subquery)

有条件：

[ALL] [FIRST]
[WHEN condition THEN] [insert_into_clause values_clause]
[ELSE] [insert_into_clause values_clause]

 

无条件的INSERT ALL

示例：

INSERT ALL
    INTO sal_history VALUES(EMPID,HIREDATE,SAL)
    INTO mgr_history VALUES(EMPID,MGR,SAL)
    SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR 
    FROM hr.employees
    WHERE employee_id > 200;

-从emp表中选择employee_id大于200的员工的empid，hiredate，sal和mgr值

-用多表INSERT插入这些值到sal_history(empno,hiredate,sal)和mgr_history(empno,mgr,sal)表中 

有条件的INSERT ALL

示例：

INSERT ALL
    WHEN SAL > 10000 THEN 
        INTO sal_history VALUES(EMPID,HIREDATE,SAL)
    WHEN MGR > 200 THEN 
        INTO mgr_history VALUES(EMPID,MGR,SAL)
    SELECT employee_id EMPID,hire_date HIREDATE, salary SAL, manager_id MGR 
    FROM employees
    WHERE employee_id > 200;

用一个条件多表INSERT语句：

-如果sal大于10000，插入这些值到sal_history表中；
-如果mgr大于200，插入这些值到mgr_history表中。

 

有条件的FIRST INSERT

示例：

INSERT FIRST
    WHEN SAL > 25000 THEN
        INTO special_sal VALUES(DEPTID, SAL)
    WHEN HIREDATE like ('%00%') THEN
        INTO hiredate_history_00 VALUES(DEPTID,HIREDATE)
    WHEN HIREDATE like ('%99%') THEN
        INTO hiredate_history_99 VALUES(DEPTID, HIREDATE)
    ELSE 
        INTO hiredate_history VALUES(DEPTID, HIREDATE)
    SELECT department_id DEPTID, SUM(salary) SAL, MAX(hire_date) HIREDATE
    FROM employees
    GROUP BY department_id;

用一个条件FIRST多表INSERT语句：
下面三个条件中：如果第一个WHEN子句的值为true，则该行后面的WHEN子句被跳过
-如果sal大于25000,则插入这些值到sal_history表中
-如果hiredate是%00%，插入这些值到hiredate_history_00表中
-如果hiredate是%99%，插入这些值到hiredate_history_99表中

旋转INSERT

示例：

INSERT ALL
    INTO sales_info VALUES (employee_id,week_id,sales_MON)
    INTO sales_info VALUES (employee_id,week_id,sales_TUE)
    INTO sales_info 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_FRI 
    FROM sales_source_data;

注：

sales_source_data的格式如下：
empno,week_id,sales_MON,sales_TUE,sales_WED,sales_THUR,sales_FRI
你可能想要以一种典型的相关格式存储这些记录到sales_info(empno,week,sales)表中使用pivoting INSERT，从非关系数据库表转换销售记录集到关系格式。

https://blog.csdn.net/cuici65440/article/details/100331701

从该例子可以看出，所谓旋转Insert是无条件insert all的一种特殊应用，但这种应用被oracle官方赋予了一个pivoting insert的名称，即旋转insert。

https://blog.csdn.net/u013169075/article/details/56027320

 

外部表

外部表是只读的表，其数据存储在数据库外的平面文件中；
外部表的各种参数在CREATE TABLE语句中指定；
使用外部表, 数据可以存储到外部文件或从外部文件中上载数据到数据库；
数据可以使用SQL访问, 但不能使用DML后在外部表上创建索引。

 

创建路径

创建外部表之前应先使用CREATE DIRECTORY语句创建路径。

CREATE DIRECTORY emp_dir AS '/flat_files' ;

 

创建外部表

CREATE TABLE oldemp(empno NUMBER, empname CHAR(20), birthdate DATE
                    )
ORGANIZATION EXTERNAL(TYPE ORACLE_LOADER
                    DEFAULT DIRECTORY emp_dirACCESS 
                    PARAMETERS(RECORDS DELIMITED BY NEWLINE
                                BADFILE '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
REJECT LIMIT 200;

 

查询外部表

SELECT * 
FROM oldempemp1.txt

 

创建主键约束同时创建索引

CREATE TABLE NEW_EMP(
    employee_id NUMBER(6) PRIMARY KEY 
        USING INDEX(CREATE INDEX emp_id_idx 
                    ON NEW_EMP(employee_id)
                    ),
    first_name VARCHAR2(20),
    last_name VARCHAR2(25)
    );

查询索引：

SELECT INDEX_NAME, TABLE_NAME 
FROM USER_INDEXES
WHERE TABLE_NAME = 'NEW_EMP';

INDEX_NAME	TABLE_NAME
EMP_ID_IDX	NEW_EMP

 

总结：

本节介绍：

1）使用多表插入INSERT语句代替DML语句，四种类型：

无条件INSERT；

有条件INSERT ALL;

有条件INSERT FIRST；

旋转INSERT（无条件INSERT的特殊应用）；

2）外部表：创建、查询；

3）创建主键约束的同时创建索引（USING INDEX）。

 

全文终。

总结：

实际使用SELECT及各类子句比较多，以及一些比较操作符、函数。

另外一些存储空间的表比如索引、约束等也会用到。