三步教會你掌握oracle外表(external table)

外表(external

table)就像普通的表對像一樣，可以select等，只是它是只讀的，數據庫中只保存了表結構的描述，表數據卻沒有存放在數據庫內，而是存放在了文件

系統上。當用戶想偶爾使用數據庫外的結構化數據時，用起外表來就非常方便，甚至比sqlldr都要方便的多。在這篇文章里，我們為大家演示了

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

第一步：創建目錄并授權。目錄是數據文件的存放目標，數據文件通常要求是文本文件。這個過程在9i以前是需要配置utl_file_dir參數的。

sys@TEST>!ls /home/oracle/temp

user.ctl userlist.txt user.log

rudolf@TEST>

sys@TEST>conn system/alibaba

Connected.

sys@TEST>

sys@TEST>CREATE DIRECTORY TEMP AS '/home/oracle/temp/';

Directory created.

sys@TEST>grant read,write on directory TEMP to rudolf;

Grant succeeded.

第二步：創建外表與測試

rudolf@TEST>CREATE TABLE "USERLIST"

2 (

3 ID NUMBER,

4 USERNAME VARCHAR2(30),

5 EMAIL VARCHAR2(128)

6 )

7 ORGANIZATION external

8 (

9 TYPE oracle_loader

10 DEFAULT DIRECTORY TEMP

11 ACCESS PARAMETERS

12 (

13 RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII

14 BADFILE 'TEMP':'userlist.bad'

15 DISCARDFILE 'TEMP':'userlist.dis'

16 LOGFILE 'TEMP':'user.log'

17 READSIZE 1048576

18 FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM

19 MISSING FIELD VALUES ARE NULL

20 REJECT ROWS WITH ALL NULL FIELDS

21 (

22 ID CHAR(30)

23 TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',

24 USERNAME CHAR(30)

25 TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',

26 EMAIL CHAR(128)

27 TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'

28 )

29 )

30 location

31 (

32 'userlist.txt'

33 )

34 )REJECT LIMIT UNLIMITED

35

rudolf@TEST>/

Table created.

rudolf@TEST>l

1 select id,username from userlist where rownum < 10

2*

rudolf@TEST>/

ID USERNAME

---------- ------------------------------

1 RudolfLu

3 tomgu

6 coug

7 chao_ping

8 parrotao

9 cnoug

10 FilsDeDragon

11 Dragon

9 rows selected.

瞧，成功了。外表就這么簡單?？墒侵挥卸桨?#xff0c;第三步在哪里呢？你也許會問。還有啊，userlist.txt要固定的格式嗎？create table...的語法這樣的狂復雜，每一項都是什么含義呢？

第三步：理解外表數據結構與create table ... organization

external語法。大家都用過sqlldr吧？外表的數據文件的結構呢就同sqlldr能讀的數據文件結構一樣了。那么語法呢？嘿嘿，別急，讓我們先

來做個sqlldr的練習吧：

[oracle@rac1 temp]$ head -10 userlist.txt

1,"RudolfLu"

3,"tomgu"

6,"coug"

7,"chao_ping"

8,"parrotao"

9,"cnoug"

10,"FilsDeDragon"

11,"Dragon"

15,"Xavier"

[oracle@rac1 temp]$ cat user.ctl

LOAD

INFILE '/home/oracle/temp/userlist.txt'

badfile '/home/oracle/temp/userlist.bad'

discardfile '/home/oracle/temp/userlist.dis'

APPEND

INTO TABLE userlist

fields terminated by ',' optionally enclosed by '"'

trailing nullcols

( id char(30),

username char(30)

)

rudolf@TEST>create table userlist

2 (id number,

3 username varchar2(30)

4 );

Table created.

rudolf@TEST>!

[oracle@rac1 temp]$ sqlldr rudolf/nix@test2.world control=./user.ctl external_table=GENERATE_ONLY

注意，我們加了一個external_table的參數。它的作用是告訴sqlldr不用真實load數據，而是生成包含external table 創建腳本的log文件。

[oracle@rac1 temp]$ ls

user.ctl userlist.txt user.log

[oracle@rac1 temp]$ cat user.log

SQL*Loader: Release 9.2.0.4.0 - Production on Wed Dec 10 20:50:19 2003

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Control File: ./user.ctl

Data File: /home/oracle/temp/userlist.txt

Bad File: /home/oracle/temp/userlist.bad

Discard File: /home/oracle/temp/userlist.dis

...

CREATE DIRECTORY statements needed for files

------------------------------------

CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/temp/'

CREATE TABLE statement for external table:

------------------------------------

CREATE TABLE "SYS_SQLLDR_X_EXT_USERLIST"

(

ID NUMBER,

USERNAME VARCHAR2(30)

)

ORGANIZATION external

(

TYPE oracle_loader

DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000

ACCESS PARAMETERS

(

RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII

BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'userlist.bad'

DISCARDFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'userlist.dis'

LOGFILE 'user.log_xt'

READSIZE 1048576

FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM

MISSING FIELD VALUES ARE NULL

REJECT ROWS WITH ALL NULL FIELDS

(

ID CHAR(30)

TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',

USERNAME CHAR(30)

TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' )

)

location

(

'userlist.txt'

)

)REJECT LIMIT UNLIMITED

...

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

您是不是已經學會了？

總結

以上是生活随笔為你收集整理的oracle外表日期,三步教会您掌握oracle外表(external table)的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。