Oraclei外部表(External Table)的使用

　　什么是外部表？　
　　External table和正规的表很相似，具体的定义可以参见《Oracle概念手册》，以下的几点需要注意：　
　　　
　　　　>创建的语法类似于： "CREATE TABLE ... ORGANIZATION EXTERNAL"　
　　　　>数据在数据库的外部组织，是操作系统文件。　
　　　　>操作系统文件在数据库中的标志是通过一个逻辑目录来映射的。　　　　
　　　　>数据是只读的。（外部表相当于一个只读的虚表）　
　　　　>不可以在上面运行任何DML操作，不可以创建索引。　　
　　　　>可以查询操作和连接。可以并行操作。　
　　　
　　例子：
　　　
　　假定有如下的两个数据文件：
　　　
　　1　平面文件数据的描述
　　　 假设如下的两个平面文件
　　　 1.dat：
　　
　　　Code: [Copy to clipboard]　
　　
　　　　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：
　　
　　
　　　Code: [Copy to clipboard]　
　　　　　　　　　　　　　　　　　　
　　　　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　创建一个逻辑目录并进行适当授权：
　　
　　　Code: [Copy to clipboard]　
　　
　　　　　SQL> CREATE DIRECTORY TESTDIR AS 'D:\TEMP\';
　　　　　目录已创建。
　　　
　　　　　SQL> GRANT READ ON DIRECTORY TESTDIR TO DEMO;
　　　　　授权成功。
　　　
　　　　　SQL> GRANT WRITE ON DIRECTORY TESTDIR TO DEMO;
　　　　　授权成功。
　　
　　注意：创建完毕逻辑目录之后要把平面文件拷贝到该目录下，另外还要注意文件名字不要写错。
　　　
　　3　创建外部表
　　
　　　Code: [Copy to clipboard]　
　　
　　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 [color=blue] ORGANIZATION EXTERNAL[/color]
　　　11　([color=blue]TYPE ORACLE_LOADER[/color]
　　　12　DEFAULT DIRECTORY TESTDIR
　　　13　ACCESS PARAMETERS(RECORDS DELIMITED BY NEWLINE
　　　14　FIELDS TERMINATED BY ',')
　　　15* LOCATION('1.DAT','2.DAT'))
　　SQL> /
　　　
　　表已创建。
　　
　　4　 进行Select选择操作看看是否正确：
　　
　　　Code: [Copy to clipboard]　
　　
　　　　　 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　
　　　
　　如果要得到外部表的有关信息：
　　
　　
　　　Code: [Copy to clipboard]　
　　
　　　
　　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想要知道平面文件的位置，使用如下的查询：
　　
　　　Code: [Copy to clipboard]　
　　
　　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
　　

查看本文来源