ORACLE9I中外部表格的使用

ORACLE9I中外部表格的使用

說明: 這個文章講述Oracle9I外部表格(External Table)的初級使用。

什麼是外部表格？

External table和正規的表很相似，具體的定義可以參見《Oracle概念手冊》，以下的幾點需要注意：

   >建立的文法類似於： "CREATE TABLE ... ORGANIZATION EXTERNAL"
   >資料在資料庫的外部組織，是作業系統檔案。
   >作業系統檔案在資料庫中的標誌是通過一個邏輯目錄來映射的。       
   >資料是唯讀。（外部表格相當於一個唯讀虛表）
   >不可以在上面運行任何DML操作，不可以建立索引。   
   >可以查詢操作和串連。可以並行操作。

例子：

假定有如下的兩個資料檔案：

1 一般檔案資料的描述
  假設如下的兩個一般檔案
  1.dat：

 
    7369,SMITH,CLERK,7902,17-DEC-80,100,0,20                                          
    7499,ALLEN,SALESMAN,7698,20-FEB-81,250,0,30                                       
    7521,WARD,SALESMAN,7698,22-FEB-81,450,0,30                                        
    7566,JONES,MANAGER,7839,02-APR-81,1150,0,20    

  2.dat：

                                 
    7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,0,30                                      
    7698,BLAKE,MANAGER,7839,01-MAY-81,1550,0,30                                        
    7934,MILLER,CLERK,7782,23-JAN-82,3500,0,10                     

   （要有對作業系統中該目錄的讀寫權限。）
    
2 建立一個邏輯目錄並進行適當授權：
  

 
      SQL> CREATE DIRECTORY TESTDIR AS 'D:/TEMP/'; 
      目錄已建立。 

      SQL> GRANT READ ON DIRECTORY TESTDIR TO DEMO; 
      授權成功。 

      SQL> GRANT WRITE ON DIRECTORY TESTDIR TO DEMO; 
      授權成功。

注意：建立完畢邏輯目錄之後要把一般檔案拷貝到該目錄下，另外還要注意檔案名稱字不要寫錯。

3 建立外部表格

 
 SQL> ED 
 已寫入檔案 afiedt.buf 

   1  CREATE TABLE DEMO.EXT 
   2  (emp_id number(4), 
   3   ename varchar2(12), 
   4   job varchar2(12) , 
   5   mgr_id  number(4) , 
   6   hiredate date, 
   7   salary number(8), 
   8   comm number(8), 
   9   dept_id number(2)) 
  10  ORGANIZATION EXTERNAL 
  11  (TYPE ORACLE_LOADER 
  12  DEFAULT DIRECTORY TESTDIR 
  13  ACCESS PARAMETERS(RECORDS DELIMITED BY NEWLINE 
  14  FIELDS TERMINATED BY ',') 
  15* LOCATION('1.DAT','2.DAT')) 
 SQL> / 

 表已建立。

4 進行Select選擇操作看看是否正確：
      

 
       SQL> select * from DEMO.EXT； 

       EMP_ID ENAME   JOB         MGR_ID HIREDATE    SALARY       COMM DEPT_ID  
       ------ ------- ---------- ------- --------- -------- ---------- -------  
         7369 SMITH   CLERK         7902 17-DEC-80      100          0      20  
         7499 ALLEN   SALESMAN      7698 20-FEB-81      250          0      30  
         7521 WARD    SALESMAN      7698 22-FEB-81      450          0      30  
         7566 JONES   MANAGER       7839 02-APR-81     1150          0      20  
         7654 MARTIN  SALESMAN      7698 28-SEP-81     1250          0      30  
         7698 BLAKE   MANAGER       7839 01-MAY-81     1550          0      30  
         7934 MILLER  CLERK         7782 23-JAN-82     3500          0      10  

   如果要得到外部表格的有關資訊：

 

 SQL> DESC DBA_EXTERNAL_TABLES; 

  名稱                                                                                                    
  ----------------------------------------------------------------- 
  OWNER                                                                                                   
  TABLE_NAME                                                                                              
  TYPE_OWNER                                                                                              
  TYPE_NAME                                                                                               
  DEFAULT_DIRECTORY_OWNER                                                                                 
  DEFAULT_DIRECTORY_NAME                                                                                  
  REJECT_LIMIT                                                                                            
  ACCESS_TYPE                                                                                             
  ACCESS_PARAMETERS                                                                                       

 SQL> SELECT OWNER,TABLE_NAME,DEFAULT_DIRECTORY_NAME,ACCESS_PARAMETERS 
   2  FROM  
   3  DBA_EXTERNAL_TABLES; 

 OWNER                TABLE_NAME        DEFAULT_DIRECTORY_NAME   ACCESS_PARAMETERS 
 ------------------ ------------------------------ ------------------------------ 
 DEMO                  EXT              TESTDIR                   RECORDS DELIMITED BY NEWLINE 
                                                                  FIELDS TERMINATED BY ',' 

如果DBA想要知道一般檔案的位置，使用如下的查詢：

 

 SQL> desc DBA_EXTERNAL_LOCATIONS; 
  名稱                                            
  ----------------------------------------- 
  OWNER                                           
  TABLE_NAME                                      
  LOCATION                                        
  DIRECTORY_OWNER                                 
  DIRECTORY_NAME                                  

 SQL>  select * from DBA_EXTERNAL_LOCATIONS;  

 OWNER         TABLE_NAME     LOCATION        DIR  DIRECTORY_NAME 
 ------------------------------------------------------------------------ 
 DEMO           EXT             1.DAT         SYS  TESTDIR 
 DEMO           EXT             2.DAT         SYS TESTDIR

參考文檔：Oracle9i Database Administration Guide