熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

教會你掌握oracle外表

2013-11-13 22:16:51  來源: Oracle 

  外表(external table)就像普通的表對像一樣可以select等只是它是只讀的數據庫中只保存了表結構的描述表數據卻沒有存放在數據庫內而是存放在了文件系統上當用戶想偶爾使用數據庫外的結構化數據時用起外表來就非常方便甚至比sqlldr都要方便的多在這篇文章裡我們為大家演示了三步就掌握oracle外表過程通過這次學習也許大家就會發展原來學習oracle也是好容易哦
  
  第一步創建目錄並授權
  目錄是數據文件的存放目標數據文件通常要求是文本文件這個過程在i以前是需要配置utl_file_dir參數的
  
    sys@TEST>!ls /home/oracle/temp
   userctl userlisttxt userlog
    rudolf@TEST> sys@TEST>conn system/alibaba
   Connected sys@TEST>
   sys@TEST>CREATE DIRECTORY
   TEMP AS /home/oracle/temp/;
    Directory created
    sys@TEST>grant readwrite on directory TEMP to rudolf;
    Grant succeeded 
  
  第二步創建外表與測試
   rudolf@TEST>CREATE TABLE USERLIST
     (
      ID NUMBER
      USERNAME VARCHAR()
      EMAIL VARCHAR()
     )
     ORGANIZATION external
     (
      TYPE oracle_loader
      DEFAULT DIRECTORY TEMP
      ACCESS PARAMETERS
      (
       RECORDS DELIMITED BY NEWLINE CHARACTERSET USASCII
       BADFILE TEMP:userlistbad
       DISCARDFILE TEMP:userlistdis
       LOGFILE TEMP:userlog
       READSIZE
       FIELDS TERMINATED BY OPTIONALLY ENCLOSED BY LDRTRIM
       MISSING FIELD VALUES ARE NULL
       REJECT ROWS WITH ALL NULL FIELDS
       (
        ID CHAR()
         TERMINATED BY OPTIONALLY ENCLOSED BY
        USERNAME CHAR()
         TERMINATED BY OPTIONALLY ENCLOSED BY
        EMAIL CHAR()
         TERMINATED BY OPTIONALLY ENCLOSED BY
       )
      )
      location
      (
       userlisttxt
      )
     )REJECT LIMIT UNLIMITED
      rudolf@TEST>/
    Table created
    rudolf@TEST>l
     select idusername from userlist where rownum <
    * rudolf@TEST>/
        ID USERNAME
   
        RudolfLu
        tomgu
        coug
        chao_ping
        parrotao
        cnoug
        FilsDeDragon
        Dragon
     rows selected  
  
  第三步理解外表數據結構與create table organization external語法
  大家都用過sqlldr吧?外表的數據文件的結構呢就同sqlldr能讀的數據文件結構一樣了那麼語法呢?嘿嘿別急讓我們先來做個sqlldr的練習吧
  
   [oracle@rac temp]$ head userlisttxt
   RudolfLu
   tomgu
   coug
   chao_ping
   parrotao
   cnoug
   FilsDeDragon
   Dragon
   Xavier
    [oracle@rac temp]$ cat userctl
    LOAD INFILE /home/oracle/temp/userlisttxt
   badfile /home/oracle/temp/userlistbad
   discardfile /home/oracle/temp/userlistdis
   APPEND INTO TABLE userlist fields terminated by
   optionally enclosed by
   trailing nullcols
   ( id char()
    username char() )
    rudolf@TEST>create table userlist
     (id number
      username varchar()
      );
    Table created
   rudolf@TEST>!
   [oracle@rac temp]$ sqlldr rudolf/nix@testworld control=/userctl external_table=GENERATE_ONLY   
  
  注意我們加了一個external_table的參數它的作用是告訴sqlldr不用真實load數據而是生成包含external table 創建腳本的log文件
  
   [oracle@rac temp]$ ls
   userctl
   userlisttxt
   userlog [oracle@rac temp]$ cat userlog
     SQL*Loader: Release Production on Wed Dec ::
    Copyright (c) Oracle Corporation
   All rights reserved
    Control File:
    /userctl
   Data File:
     /home/oracle/temp/userlisttxt
    Bad File:
     /home/oracle/temp/userlistbad
    Discard File: /home/oracle/temp/userlistdis
     CREATE DIRECTORY statements needed for files
   
   CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_ AS /home/oracle/temp/
    CREATE TABLE statement for external table:
   
   CREATE TABLE SYS_SQLLDR_X_EXT_USERLIST
    (
    ID NUMBER  USERNAME VARCHAR() )
   ORGANIZATION external
    (  
  TYPE oracle_loader
    DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_
    ACCESS PARAMETERS
     (
     RECORDS DELIMITED BY NEWLINE CHARACTERSET USASCII
     BADFILE SYS_SQLLDR_XT_TMPDIR_:userlistbad
     DISCARDFILE SYS_SQLLDR_XT_TMPDIR_:userlistdis
     LOGFILE userlog_xt
     READSIZE
     FIELDS TERMINATED BY OPTIONALLY ENCLOSED BY LDRTRIM
      MISSING FIELD VALUES ARE NULL
      REJECT ROWS WITH ALL NULL FIELDS
      (
      ID CHAR()
       TERMINATED BY OPTIONALLY ENCLOSED BY
      USERNAME CHAR()
       TERMINATED BY OPTIONALLY ENCLOSED BY
    )
    )
    location
     (
     userlisttxt
    )
   )REJECT LIMIT UNLIMITED  
From:http://tw.wingwit.com/Article/program/Oracle/201311/18672.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.