Oracle OCP(11)：操作大数据集

一、子查询插入数据

• 语法

  INSERT INTO table [ column (, column) ] subquery;
  

• 说明：
  您可以使用INSERT语句向一个表中添加行，其中的值来自于查询结果集。
  插入子句的列列表中列及其数据类型的数量必须与子查询中的值和数据类型相匹配。
• 例句：

  INSERT INTO EMPL3
  SELECT *
    FROM employees;
  

二、WITH CHECK OPTION

• 语法：

  insert into (<select clause> WITH CHECK OPTION) values (...);
  

• 说明：
  insert进subquery里的这张表里
  如果不满足subquery里的where条件的话，就不允许插入。
  如果插入的列有不在subquery作为检查的where条件里，那么也会不允许插入。
  如果不加WITH CHECK OPTION则在插入时不会检查。
  这里注意，subquery其实是不会实际执行的。
• 例句

  INSERT INTO
    (SELECT EMPLOYEE_ID, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY
       FROM EMPLOYEES
      WHERE DEPARTMENT_ID = 50 WITH CHECK OPTION)
  VALUES
    (99998,
     'Smith',
     'JSMITH',
     TO_DATE('1999-06-07', 'yyyy-mm-dd'),
     'ST_CLERK',
     5000);
  

三、INSERT ALL

• 语法：
  • 无条件

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

  • 有条件

    INSERT [ALL]
        [WHEN condition THEN] [insert_into_clause values_clause]
        [ELSE] [insert_into_clause values_clause];
    

• 说明
  • 如果没有when条件，则会对所有表进行插入操作
  • 如果有when条件，则对每一个when条件都进行检查，如果满足条件就执行插入操作。
• 例句：

  --无条件
  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 EMPLOYEES
   WHERE EMPLOYEE_ID > 200;
  
  
  --有条件
  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 FIRST

• 语法：

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

• 说明：
  对于每一行数据，只插入到第一个when条件成立的表，不继续检查其他条件。
• 例句：

  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;
  

五、MERGE

• 语法：

  MERGE INTO table_name table_alias
  USING (table|view|sub_query) alias
     ON (join condition)
   WHEN MATCHED
   THEN UPDATE SET
        col1 = col_val1,
        col2 = col2_val
   WHEN NOT MATCHED
   THEN INSERT (column_list)
        VALUES (column_values);
  

• 说明：
  Merge用来从一个表中选择一些数据更新或者插入到另一个表中。而最终是用更新还是用插入的方式取决于该语句中的条件。
• 例句：

  MERGE INTO empl3 c
  USING employees e
     ON (c.employee_id = e.employee_id)
   WHEN MATCHED
   THEN UPDATE SET
        c.first_name = e.first_name,
        c.last_name = e.last_name,
        ...
        c.department_id = e.department_id
   WHEN NOT MATCHED
   THEN INSERT VALUES
        (e.employee_id, e.first_name, e.last_name,
        e.email, e.phone_number, e.hire_date, e.job_id,
        e.salary, e.commission_pct, e.manager_id,
        e.department_id);
  

六、Flashback Version Query

• 语法：

  VERSIONS BETWEEN TIMESTAMP [lower bound] AND [upper bound];
  VERSIONS BETWEEN SCN [lower bound] AND [upper bound];
  

• 说明：
  通过versions between能够查看指定时间段内undo表空间中记录的不同版本
• 例句：

  SELECT SALARY
    FROM EMPLOYEES3 VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
   WHERE EMPLOYEE_ID = 107;