ORACLE學習之外部表

################    ORACLE學習之外部表     ################

    外部表只能在Oracle 9i之後來使用。外部表是指不在資料庫內的表，如作業系統內有一個以逗號為分隔符的儲存資料的檔案，可以透過外部表將該檔案的內容顯示在資料庫內，外部表的功能類似於檢視，只能讀，不能修改。
    建立外部表時首先要建立目錄指定外部表的資料檔案的位置，然後編輯外部表的建立語法：
    這裡利用sqlldr生成一個外部表的標準，例如：
[oracle@orcl2 sqlldr2]$ pwd
/u01/myscript/sqlldr2
[oracle@orcl2 sqlldr2]$ ls
sqlldr2.ctl  sqlldr2.dat
[oracle@orcl2 sqlldr2]$ 
在/u01/myscript/sqlldr2目錄下有sqlldr2.ctl和sqlldr2.dat兩個檔案，其中sqlldr2.ctl是sqlldr的控制檔案，用來描述要匯入資料的詳細規則，sqlldr2.dat是資料檔案，內部存有要載入的資料，並且以逗號為分隔符。具體內容如下，此處稍加解釋，有關sqlldr的內容請看上篇詳解。
[oracle@orcl2 sqlldr2]$ cat sqlldr2.ctl 
LOAD DATA //指定載入資料
INFILE sqlldr2.dat //指定載入的資料檔案位置
APPEND INTO TABLE BONUS //指定要載入的表
FIELDS TERMINATED BY "," //指定資料檔案以逗號為分隔符
(ENAME,JOB,SAL) //指定載入資料的列明


[oracle@orcl2 sqlldr2]$ cat sqlldr2.dat //此處為資料檔案，以逗號為分隔符，不在贅述 
USER1,EMP,100
USER2,EMP,101
USER3,EMP,102
USER4,EMP,103
USER5,EMP,104
USER6,EMP,105
USER7,MGR,106
USER8,MGR,107
USER9,HR,108


先在資料內建立目錄，並授權給scott（以scott使用者為例）：


SQL> create directory sqlldr2 as '/u01/myscript/sqlldr2';
Directory created.
SQL> grant read,write on directory sqlldr2 to scott;
Grant succeeded.


利用sqlldr生成建立外部表的語句：
[oracle@orcl2 sqlldr2]$ sqlldr scott/oracle control=sqlldr2.ctl external_table=generate_only


SQL*Loader: Release 10.2.0.1.0 - Production on Wed Nov 20 10:21:27 2013
Copyright (c) 1982, 2005, Oracle.  All rights reserved.


同目錄下回生成一個.log結尾的日誌檔案，檢視日誌檔案並獲得sql語句
[oracle@orcl2 sqlldr2]$ cp sqlldr2.log sqlldr2.sql
[oracle@orcl2 sqlldr2]$ vi sqlldr2.sql 
刪除無用的行，只保留一下語句即可：
CREATE TABLE "SYS_SQLLDR_X_EXT_BONUS"   //建立的外部表名，可自行修改
(
  "ENAME" VARCHAR2(20), //要建立外部表的列的屬性
  "JOB" VARCHAR2(15),
  "SAL" NUMBER
)
ORGANIZATION external //指定該表為外部表，以下為外部表的屬性
(
  TYPE oracle_loader    
/****載入資料的方式，ORACLE_LOADER是傳統方式，還有皮ORACLE_DATAPUMP是資料泵的方式****/


  DEFAULT DIRECTORY SQLLDR2 //預設的載入路徑，看，指定的是剛建立的目錄
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII  //記錄結束標記，此處為換行結束
    BADFILE 'SQLLDR2':'sqlldr2.bad' //生成.bad檔案，匯入過程中記錄壞的資料
    LOGFILE 'sqlldr2.log_xt' //生成日誌檔案
    READSIZE 1048576 //讀取日誌檔案快取區的大小，預設1M
    FIELDS TERMINATED BY "," LDRTRIM //指定分隔符
    REJECT ROWS WITH ALL NULL FIELDS //指定若為空值，則載入是為NUll(若整列均為空，則不予載入)
    (
      "ENAME" CHAR(255) //以下為列的屬性
        TERMINATED BY ",",
      "JOB" CHAR(255)
        TERMINATED BY ",",
      "SAL" CHAR(255)
        TERMINATED BY ","
    )
  )
  location
  (
    'sqlldr2.dat' //要載入的資料檔名
  )
)REJECT LIMIT UNLIMITED //指定查詢資料能結束的錯誤次數，此處不限制




好了，外部表語法解釋完畢，熟悉的情況下可以自己手動建立，接下來去資料庫執行建立該外部表：
SQL> get /u01/myscript/sqlldr2/sqlldr2.sql
  1  CREATE TABLE BONUS_TEST
  2  (
  3    "ENAME" VARCHAR2(20),
  4    "JOB" VARCHAR2(15),
  5    "SAL" NUMBER
  6  )
  7  ORGANIZATION external
  8  (
  9    TYPE oracle_loader
 10    DEFAULT DIRECTORY SQLLDR2
 11    ACCESS PARAMETERS
 12    (
 13      RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
 14      BADFILE 'SQLLDR2':'sqlldr2.bad'
 15      LOGFILE 'sqlldr2.log_xt'
 16      READSIZE 1048576
 17      FIELDS TERMINATED BY "," LDRTRIM
 18      REJECT ROWS WITH ALL NULL FIELDS
 19      (
 20        "ENAME" CHAR(255)
 21          TERMINATED BY ",",
 22        "JOB" CHAR(255)
 23          TERMINATED BY ",",
 24        "SAL" CHAR(255)
 25          TERMINATED BY ","
 26      )
 27    )
 28    location
 29    (
 30      'sqlldr2.dat'
 31    )
 32* )REJECT LIMIT UNLIMITED
SQL> /
Table created.
SQL> select * from bonus_test;
ENAME                JOB                    SAL
-------------------- --------------- ----------
USER1                EMP                    100
USER2                EMP                    101
USER3                EMP                    102
USER4                EMP                    103
USER5                EMP                    104
USER6                EMP                    105
USER7                MGR                    106
USER8                MGR                    107
USER9                HR                     108
9 rows selected.


資料檔案中的9條資料完全加入到了資料庫中
[oracle@orcl2 sqlldr2]$ ls
sqlldr2.ctl  sqlldr2.dat  sqlldr2.log  sqlldr2.log_xt  sqlldr2.sql
同目錄下生成了一個.log_xt的日誌檔案
[oracle@orcl2 sqlldr2]$ cat sqlldr2.log_xt 




 LOG file opened at 11/20/13 10:36:42


Field Definitions for table BONUS_TEST
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Reject rows with all null fields


  Fields in Data Source: 


    ENAME                           CHAR (255)
      Terminated by ","
      Trim whitespace same as SQL Loader
    JOB                             CHAR (255)
      Terminated by ","
      Trim whitespace same as SQL Loader
    SAL                             CHAR (255)
      Terminated by ","
      Trim whitespace same as SQL Loader
該檔案記錄外部表的操作資訊
若向sqlldr2.dat檔案中加入一行資料USER10,HR,108，再次進入資料庫查詢該外部表會發現多出了一條，好神奇~
SQL> select * from bonus_test;
ENAME                JOB                    SAL
-------------------- --------------- ----------
USER1                EMP                    100
USER2                EMP                    101
USER3                EMP                    102
USER4                EMP                    103
USER5                EMP                    104
USER6                EMP                    105
USER7                MGR                    106
USER8                MGR                    107
USER9                HR                     108
USER10               HR                     108
10 rows selected.




再次檢視sqlldr2.log_xt日誌會發現多出幾條日誌資訊


未完待續...