ORACLE insert命令解析

ORACLE insert  

   多表 INSERT 語句概述

:: INSERT...SELECT 語句能夠作為單個的 DML 語句的一部分用於插入行到多表中 :: 多表
INSERT  語句能夠被用在資料倉儲系統中從一個或多個操作源轉移資料到一組目的表中  ::
下面的方法提供重要的效能改進： - 單個 DML  與多 INSERT...SELECT 語句相對 -  單個
DML 與一個用 IF...THEN 語法做多插入的過程相對

Overview of Multitable INSERT Statements

 In a  multitable INSERT  statement, you  insert computed  rows derived from the
 rows returned from the evaluation of a subquery into one or more tables.

 Multitable INSERT statements can  play a very useful  role in a data  warehouse
 scenario. You need to load your  data warehouse regularly so that it  can serve
 its purpose of  facilitating business analysis.  To do this,  data from one  or
 more operational systems needs to  be extracted and copied into  the warehouse.
 The process of extracting data from the source system and bringing it into  the
 data  warehouse  is   commonly  called  ETL,   which  stands  for   extraction,
 transformation, and loading.

  During extraction, the  desired data has  to be identified  and extracted from
  many  different  sources, such  as  database systems  and  applications. After
  extraction, the data has to be physically transported to the target system  or
  an intermediate system for further  processing. Depending on the chosen  means
  of transportation, some transformations can  be done during this process.  For
  example, a  SQL statement  that directly  accesses a  remote target  through a
  gateway can concatenate two columns as part of the SELECT statement.

  Once data is  loaded into an  Oracle9i, database, data  transformations can be
  executed using SQL operations.  With Oracle9i multitable INSERT  statements is
  one of the techniques for implementing SQL data transformations.

Overview of Multitable Insert Statements (continued)

  Multitable  INSERTS statement  offer the  benefits of  the INSERT  ... SELECT
  statement when multiple  tables are involved  as targets. Using  functionality
  prior  to Oracle9i,  you had  to deal  with n  independent INSERT  ... SELECT
  statements, thus processing  the same source  data n times  and increasing the
  transformation workload n times.

  As with the  existing INSERT ...  SELECT statement, the  new statement can  be
  parallelized and used with  the direct-load mechanism for  faster performance.
  (直接裝載機制)

  Each record from any input stream, such as a nonrelational database table, can
  now  be  converted into  multiple records  for more  relational database table
  environment. To implement this functionality before Oracle9i, you had to write
  multiple INSERT statements.

   多表 INSERT 語句的型別

  Oracle9i 引入下面的多表插入語句的型別：  - 無條件  INSERT -  條件 ALL  INSERT
  - 條件 FIRST INSERT - 重點(Pivoting) INSERT

Types of Multitable INSERT Statements

   Oracle 9i introduces  the following types  of multitable INSERT  statements:
   - Unconditional INSERT - Conditional  ALL INSERT - Conditional FIRST  INSERT
   - Pivoting INSERT

   You use different clauses to indicate the type of INSERT to be executed.

 

   多表 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]

 

Multitable INSERT Statements

  The slide displays the generic format for multitable INSERT statements.  There
  are four types of multitable insert statements.

 Unconditional INSERT

 Conditional ALL INSERT

 Conditional FIRST INSERT

 Pivoting INSERT

  Unconditional INSERT: ALL into_clause

  Specify  ALL   followed  by   multiple  insert_into_clauses   to  perform   an
  unconditional   multitable   insert.   The   Oracle   Server   executes   each
  insert_into_clause once for each row returned by the subquery.

 Conditional INSERT: conditional_insert_clause

 Specify  the  conditional_insert_clause  to  perform  a  conditional multitable
 insert.  The  Oracle  Server   filters  each  insert_into_clause  through   the
 corresponding WHEN condition, which determines whether that  insert_into_clause
 is executed. A single  multitable insert statement can  contain up to 127  WHEN
 clauses.

 Conditional INSERT: ALL

  If you specify ALL, the Oracle Server evaluates each WHEN clause regardless of
  the results of the evaluation of  any other WHEN clause. For each  WHEN clause
  whose  condition   evaluates  to   true,  the   Oracle  Server   executes  the
  corresponding INTO clause list.

Multitable INSERT Statements  (continued)

  Conditional FIRST: INSERT

  If you  specify FIRST,  the Oracle  Server evaluates  each WHEN  clause in the
  order in which it appears in the statement. If the first WHEN clause evaluates
  to true, the  Oracle Server executes  the corresponding INTO  clause and skips
  subsequent WHEN clauses for the given row.

 Conditional INSERT: ELSE Clause

  For a given row, if no WHEN clause evaluates to true:

 If you  have specified  an ELSE,  clause the  Oracle Server  executes the  INTO
 clause list associated with the ELSE clause.

 If you did not  specify an ELSE clause,  the Oracle Server takes  no action for
 that row.

 Restrictions on Multitable INSERT Statements

 You can perform. multitable inserts only on tables, not on views or materialized
 views.

 You cannot perform. a multitable insert into a remote table.

 You cannot specify a table  collection expression when performing a  multitable
 insert.

 In  a  multitable insert,  all  of the  insert_into_clauses  cannot combine  to
 specify more than 999 target columns.

   無條件 INSERT ALL

:: 從 EMPLOYEES 中選擇 EMPLOYEE_ID 大於200的僱員 EMPLOYEE_ID, HIRE_DATE, SALARY,
和 MANAGER_ID 值 :: 用多表 INSERT 插入這些值到 SAL_HISTORY 和 MGR_HISTORY 表中

要先建立表

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;  8  rows created.
//兩個表一共插了8行

Unconditional INSERT ALL

 The  example  in the  slide  inserts rows  into  both the  SAL_HISTORY  and the
 MGR_HISTORY tables.//插入到兩個表中.

  The SELECT statement retrieves the details of employee ID, hire date,  salary,
  and manager ID of those employees  whose employee ID is greater than  200 from
  the EMPLOYEES table. The details of the employee ID, hire date, and salary are
  inserted into the  SAL_HISTORY table. The  details of employee  ID, manager ID
  and salary are inserted into the MGR_HISTORY table.

  This INSERT statement is referred to as an unconditional INSERT, as no further
  restriction is applied to the rows that are retrieved by the SELECT statement.
  All the  rows retrieved  by the  SELECT statement  are inserted  into the  two
  tables,  SAL_HISTORY  and  MGR_HISTORY.  The  VALUES  clause  in  the   INSERT
  statements specifies  the columns  from the  SELECT statement  that have to be
  inserted into each of  the tables. Each row  returned by the SELECT  statement
  results in  two insertions,  one for  the SAL_HISTORY  table and  one for  the
  MGR_HISTORY table.

  The feedback 8 rows created can be  interpreted to mean that a total of  eight
  insertions were performed on the base tables SAL_HISTORY and MGR_HISTORY.

   條件INSERT ALL

:: 從 EMPLOYEES 表中選擇 EMPLOYEE_ID 大於200的那些僱員的 EMPLOYEE_ID, HIRE_DATE,
SALARY   和  MANAGER_ID   值  ::   如果  SALARY   大於  $10,000，用一個條件多表
INSERT語句插入這些值到 SAL_HISTORY 表中 :: 如果 MANAGER_ID 大於  200，用一個多表
INSERT 語句插入這些值到 MGR_HISTORY 表中

Conditional INSERT ALL

  The problem statement for a  conditional INSERT ALL statement is  specified in
  the slide. The solution to the preceding problem is shown in the next page.

 

   條件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; 4 rows created. 

Conditional INSERT ALL (continued)

  The example in the slide is similar to the example on the previous slide as it
  inserts rows into both the SAL_HISTORY and the MGR_HISTORY tables. The  SELECT
  statement retrieves the details of employee ID, hire date, salary, and manager
  ID of those employees whose employee ID is greater than 200 from the EMPLOYEES
  table. The details of employee ID, hire date, and salary are inserted into the
  SAL_HISTORY table.  The details  of employee  ID, manager  ID, and  salary are
  inserted into the MGR_HISTORY table.

  This INSERT statement is referred to as a conditional ALL INSERT, as a further
  restriction is applied to the rows that are retrieved by the SELECT statement.
  From the rows that are retrieved  by the SELECT statement, only those  rows in
  which the  value of  the SAL  column is  more than  10000 are  inserted in the
  SAL_HISTORY table, and similarly  only those rows where  the value of the  MGR
  column is more than 200 are inserted in the MGR_HISTORY table.

  Observe that unlike the previous example, where eight rows were inserted  into
  the tables, in this example only four rows are inserted.

  The feedback 4 rows  created can be interpreted  to mean that a  total of four
  inserts were performed on the base tables, SAL_HISTORY and MGR_HISTORY.

   條件FIRST INSERT

:: 從 EMPLOYEES 表中選擇 DEPARTMENT_ID  , SUM(SALARY) 和 MAX(HIRE_DATE) ::  如果
SUM(SALARY) 大於 $25,000 則用一個條件 FIRST 多表 INSERT 插入這些值到 SPECIAL_SAL
表中  ::  如果第一個  WHEN 子句的值為  true，則該行的後面的  WHEN  子句被跳過 ::
對於那些不滿足第一個   WHEN   條件的行，用一個條件多表   INSERT    基於HIRE_DATE
列的值插入  HIREDATE_HISTORY_00,  或  HIREDATE_HISTORY_99,  或  HIREDATE_HISTORY
表,根據HIREDATE列的值.

Conditional FIRST INSERT

  The problem statement for a conditional FIRST INSERT statement is specified in
  the slide. The solution to the preceding problem is shown on the next page.

 

   條件FIRST INSERT

或者是第一個WHEN,或者是後面的某一個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; 8 rows created.

Conditional FIRST INSERT (continued)

  The example  in the  slide inserts  rows into  more than  one table, using one
  single  INSERT  statement.  The  SELECT  statement  retrieves  the  details of
  department ID, total salary, and maximum hire date for every department in the
  EMPLOYEES table.

  This INSERT  statement is  referred to  as a  conditional FIRST  INSERT, as an
  exception is made for the departments whose total salary is more than $25,000.
  The condition WHEN ALL > 25000 is  evaluated first. If the total salary for  a
  department  is  more  than  $25,000, then  the  record  is  inserted into  the
  SPECIAL_SAL        table        irrespective        of        the         hire
  date(如果sum(salary)>25000,則將記錄插入到SPECIAL_SAL中,不管hiredate). If  this
  first  WHEN  clause  evaluates  to  true,  the  Oracle  Server  executes   the
  corresponding INTO clause and skips subsequent WHEN clauses for this row.

  For the rows that do not satisfy the first WHEN condition (WHEN SAL >  25000),
  the  rest  of  the  conditions are  evaluated  just  as  a conditional  INSERT
  statement, and the records retrieved by the SELECT statement are inserted into
  the HIREDATE_HISTORY_00, or  HIREDATE_HISTORY_99, or HIREDATE_HISTORY  tables,
  based on the value in the HIREDATE column.

  The feedback 8 rows created can be  interpreted to mean that a total of  eight
  INSERT   statements   were   performed   on   the   base   tables  SPECIAL_SAL
  ,HIREDATE_HISTORY_00, HIREDATE_HISTORY_99, and HIREDATE_HISTORY.

 

   重點INSERT

::   支援從非關聯式資料庫表中接受一組銷售記錄，   SALES_SOURCE_DATA   的格式如下：
EMPLOYEE_ID, WEEK_ID, SALES_MON, SALES_TUE, SALES_WED, SALES_THUR, SALES_FRI  ::
你可能想要以一種典型的相關格式儲存這些記錄到   SALES_INFO   表中：  EMPLOYEE_ID,
WEEK,  SALES  ::使用  pivoting  INSERT，從非關聯式資料庫錶轉換銷售記錄集到關係格式
././././

Pivoting INSERT

  Pivoting is an operation in which you need to build a transformation such that
  each record from any  input stream, such as, a  nonrelational database  table,
  must be converted into multiple  records for a more relational  database table
  environment.

  In order  to solve  the problem  mentioned in  the slide,  you need to build a
  transformation(轉換) such  that each  record from  the original  nonrelational
  database table, SALES_SOURCE_DATA, is converted into five records for the data
  warehouse's  SALES_INFO  table.  This operation  is  commonly  referred to  as
  pivoting.

  The problem  statement for  a pivoting  INSERT statement  is specified  in the
  slide. The solution to the preceding problem is shown in the next page.

 

  重點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;  5   rows
created.

Pivoting INSERT (continued)

  In the example in the slide, the sales data is received from the nonrelational
  database table SALES_SOURCE_DATA, which is the details of the sales  performed
  by a sales representative on each day of a week, for a week with a  particular
  week ID.

    DESC  SALES_SOURCE_DATA  Name  Null?  Type  EMPLOYEE_ID   NUMBER(6)  WEEK_ID
    NUMBER(2)   SALES_MON    NUMBER(8,2)   SALES_TUE    NUMBER(8,2)    SALES_WED
    NUMBER(8,2) SALES_THUR  NUMBER(8,2) SALES_FRI  NUMBER(8,2)

SELECT  *  FROM SALES_SOURCE_DATA;  EMPLOYEE_ID   WEEK_ID  SALES_MON   SALES_TUE
SALES WED  SALES THUR  SALES_FRI 176  6 2000   3000  4000 5000  6000

DESC SALES_INFO Name  Null?  Type EMPLOYEE_ID  NUMBER(6) WEEK    NUMBER(2) SALES
NUMBER(8,2)

SELECT * FROM sales_info; EMPLOYEE_ID WEEK  SALES 176   6 2000 176   6  3000 176
6 4000 176   6 5000 176   6 6000

  Observe in the preceding  example that using a  pivoting INSERT, one row  from
  the SALES_SOURCE_DATA table is converted into five records for the  relational
  table,                                                             SALES_INFO.
  將表SALES_SOURCE_DA他的一行轉換為表SALES_INFO的五條記錄(關係表)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/14661773/viewspace-703691/，如需轉載，請註明出處，否則將追究法律責任。