oracle insert的擴充

⑴ insert into table_name [(column[,column...])] select_statement
             每次只能插入一個表，但效能上比寫多條insert語句要高。另外，oracle還提供create table table_name as select ...,as不可少！實現直接建立並且插入表，依賴於後面的select語句的結果，如：create table new_table as select * from source_table where 1=0
             案例：
             表mxt新增一列time，那麼這個列會被置在最後，現想把此列放於第2
             create table copy_mxt as select id,time,name from mxt;
             drop table mxt;
             rname copy_mxt to mxt;

    向多個表發散彈槍：

          類型：
            ① 無條件的insert
            ② 帶條件的insert all
            ③ 帶條件的insert first
            ④ pivoting insert

          多表insert的使用限制：
            ① 只能對table使用insert，不能對視圖或物化視圖使用
            ② 不能對遠端資料表使用這個插入操作
            ③ 多表插入時，不能指定一個表的集操作
            ④ 多表insert的into的目標表加在一起的列數不能超過999

          ㈠ 無條件的insert
             文法：
             insert all
             into dest_table1 [column(,column...)] [values (...)]
             into dest_table2 [column(,column...)] [values (...)]
             ...
             select_statement  --values中指定是select結果列，如果沒有values指定列，那麼表示目標表的列類型和順序與select語句查詢的結果完全一致。建議不要省略目標表中的列和values，增強可讀性。
             例子：

[sql]
view plaincopyprint?

1. create table emp_1
   as select employee_id,last_name
   from employees where 1=0; 
   
2. create table emp_2
   as select *
   from employees where 1=0; 
   
3. create table emp_3
   as select *
   from employees where 1=0; 
   
4. insert all 
   
5. --不指定emp_1後面的列，也沒有values指定列，那麼emp_1所有列類型和順序與查詢的列的類型和順序一致 
   
6. --也就是emp_1中只有查詢結果中的那幾列，而且類型和順序一致 
   
7.   into emp_1 
8. --指定了emp_2後面的列，但沒有values指定列，表示emp_2中要插入的列被選擇出來，與查詢結果列的類型和順序一致 
   
9. --emp_2也有很多列，不止這兩列 
   
10.   into emp_2(employee_id,last_name) 
    
11. --指定emp_3後面的列和values指定列，那麼values後面的列名必須與查詢結果一致，如果查詢有別名，必須在values中使用別名 
    
12. --emp_3指定的列的類型和順序必須與values保持一致 
    
13. --emp_3也可能列數大於指定列數 
    
14.   into emp_3(employee_id,last_name)
    values(e_id,e_last_name) 
15.   select employee_id e_id,last_name e_last_name  
    
16.    from employees 

create table emp_1 as select employee_id,last_name from employees where 1=0;create table emp_2 as select * from employees where 1=0;create table emp_3 as select * from employees where 1=0;insert all--不指定emp_1後面的列，也沒有values指定列，那麼emp_1所有列類型和順序與查詢的列的類型和順序一致--也就是emp_1中只有查詢結果中的那幾列，而且類型和順序一致  into emp_1--指定了emp_2後面的列，但沒有values指定列，表示emp_2中要插入的列被選擇出來，與查詢結果列的類型和順序一致--emp_2也有很多列，不止這兩列  into emp_2(employee_id,last_name)--指定emp_3後面的列和values指定列，那麼values後面的列名必須與查詢結果一致，如果查詢有別名，必須在values中使用別名--emp_3指定的列的類型和順序必須與values保持一致--emp_3也可能列數大於指定列數  into emp_3(employee_id,last_name) values(e_id,e_last_name)  select employee_id e_id,last_name e_last_name    from employees

          ㈡ 帶條件的insert
             包括：insert all和insert first
             文法：
             insert all
             when condition_statement
             then into dest_table1[指定列] values[查詢中的列]
             when condition_statement
             then into dest_table2[指定列] values[查詢中的列]
             select_statement
              注釋：帶條件的insert all與insert first的區別在於只按條件對查詢的結果進行刷選，不會考慮前面已經匹配過的記錄，每次都是全量匹配，而insert first對已經匹配過的記錄不予考慮，下一個when會自動過濾掉上一個匹配過的行記錄
             例子：

[sql]
view plaincopyprint?

1. insert all 
   
2.   --將查詢結果s_id>20的插入，條件中指定的列必須與查詢結果名字一致，如果有別名，請用別名 
   
3.   when e_id>20 
4.     then into emp_1 
   
5.   --s_last_name為M開頭的插入，可能插入的行與s_id有重複 
   
6.   when e_last_name like
   'M%' 
7.     then into emp_2(employee_id,last_name) 
   
8.   --如果指定else，則不滿足上麵條件的插入到emp_3,插入的行不會與上面兩個重複 
   
9.   else 
10.     into emp_3(employee_id,last_name)
    values(e_id,e_last_name) 
11.   select employee_id e_id,last_name e_last_name 
    
12.     from employees 

insert all  --將查詢結果s_id>20的插入，條件中指定的列必須與查詢結果名字一致，如果有別名，請用別名  when e_id>20    then into emp_1  --s_last_name為M開頭的插入，可能插入的行與s_id有重複  when e_last_name like 'M%'    then into emp_2(employee_id,last_name)  --如果指定else，則不滿足上麵條件的插入到emp_3,插入的行不會與上面兩個重複  else    into emp_3(employee_id,last_name) values(e_id,e_last_name)  select employee_id e_id,last_name e_last_name    from employees

          ㈢ pivoting insert（旋轉插入）
             關係型資料庫三範式，歸納起來三句話：
             ① 行不可再分
             ② 列不可再分
             ③ 列不可重複
             但很多情況下，範式都會被破壞。使用旋轉插入可以實現將非關係表記錄轉換為關係表在資料庫儲存。和insert all類似，不過這裡是向一個表發散彈槍，將列轉變成行。
             例子：

[sql]
view plaincopyprint?

1. create table sales (year_2008 number,year_2009 number,year_2010 number,year_2011 number,year_2012 number) 
   
2. insert into sales
   values(191,922,392,942,592) 
3. select * from sales 
   
4.  
5. create table cow_sales(year_sales number) 
   
6.  
7. insert all 
   
8.   into cow_sales values(year_2008) 
   
9.   into cow_sales
   values(year_2009) 
10.   into cow_sales values(year_2010) 
    
11.   into cow_sales
    values(year_2011) 
12.   into cow_sales values(year_2012) 
    
13.   select * from sales 
    
14.  
15. select * from cow_sales 
    

create table sales (year_2008 number,year_2009 number,year_2010 number,year_2011 number,year_2012 number)insert into sales values(191,922,392,942,592)select * from salescreate table cow_sales(year_sales number)insert all  into cow_sales values(year_2008)  into cow_sales values(year_2009)  into cow_sales values(year_2010)  into cow_sales values(year_2011)  into cow_sales values(year_2012)  select * from salesselect * from cow_sales

原文地址：http://blog.csdn.net/linwaterbin/article/details/7994147