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

三步教會你掌握Oracle外表(externaltable)

2022-06-13   來源: Oracle 

  外表(external table)就像普通的表對像一樣可以select等只是它是只讀的數據庫中只保存了表結構的描述表數據卻沒有存放在數據庫內而是存放在了文件系統上當用戶想偶爾使用數據庫外的結構化數據時用起外表來就非常方便甚至比sqlldr都要方便的多在這篇文章裡我們為大家演示了

  三步就掌握Oracle外表過程通過這次學習也許大家就會發展原來學習oracle也是好容易哦

  第一步創建目錄並授權

  目錄是數據文件的存放目標數據文件通常要求是文本文件這個過程在i以前是需要配置utl_file_dir參數的 復制內容到剪貼板

  代碼
 !ls>sys@TEST>!ls /home/oracle/temp
  userctl userlisttxt userlog
  rudolf@TEST>
  conn>sys@TEST>conn system/alibaba
  Connected
  sys@TEST>
  CREATE>sys@TEST>CREATE DIRECTORY TEMP AS /home/oracle/temp/;
  Directory created
  grant>sys@TEST>grant readwrite on directory TEMP to rudolf;
  Grant succeeded

  第二步創建外表與測試 復制內容到剪貼板

  代碼
cellPadding= width=% bgColor=#ffffff border=>  CREATE>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

   cellPadding= width=% bgColor=#ffffff border=>   USERNAME CHAR()
   TERMINATED BY OPTIONALLY ENCLOSED BY
   EMAIL CHAR()
   TERMINATED BY OPTIONALLY ENCLOSED BY
   )
   )
   location
   (
   userlisttxt
   )
   )REJECT LIMIT UNLIMITED
  
  />rudolf@TEST>/
  Table created
  l>rudolf@TEST>l
   select idusername from userlist where rownum <
  *
  />rudolf@TEST>/
  ID USERNAME
  
   RudolfLu
   tomgu
   coug
   chao_ping
   parrotao
   cnoug
   FilsDeDragon
   Dragon
   rows selected

  瞧成功了外表就這麼簡單可是只有二步啊第三步在哪裡呢?你也許會問還有啊userlisttxt要固定的格式嗎?create table……的語法這樣的狂復雜每一項都是什麼含義呢?

  這就是第三步要教給大家的東西了

  第三步理解外表數據結構與create table …… organization external語法

  大家都用過sqlldr吧?外表的數據文件的結構呢就同sqlldr能讀的數據文件結構一樣了那麼語法呢?嘿嘿別急讓我們先來做個sqlldr的練習吧
cellPadding= width=% bgColor=#ffffff border=>   [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

   cellPadding= width=% bgColor=#ffffff border=>  APPEND
  INTO TABLE userlist
  fields terminated by optionally enclosed by
  trailing nullcols
  ( id char()
  username char()
  )
  create>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文件
cellPadding= width=% bgColor=#ffffff border=>  [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 cellPadding= width=% bgColor=#ffffff border=>  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
  

  瞧原來我們更本不用擔心怎麼寫create external table的語句呢sqlldr就可以幫我們生成了!

  您是不是已經學會了?


From:http://tw.wingwit.com/Article/program/Oracle/201311/18052.html
    推薦文章
    Copyright © 2005-2022 電腦知識網 Computer Knowledge   All rights reserved.