Oracle 巧用外部表

转载：https://blog.youkuaiyun.com/iteye_11961/article/details/81933761

转载一：https://www.cnblogs.com/ivictor/p/4648023.html

转载二：来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28602568/viewspace-1797410/，如需转载，请注明出处，否则将追究法律责任。

注释：

    之前有写过 如何将.dbf/.xls/.csv文件导入oracle数据库  适用量较小的文件；

    当excel/txt/csv/dbf等文件数据大于10w，直接导入可能比较慢，如下简单使用了下oracle的外部表功能使数据库直接加载/读取系统文件(而非insert表)。

1、将excel另存为.txt格式，再传到数据库directory 路径（目录列表：select * from dba_directories）

2、执行创建外部表语句：

   create table test

   (name1 varchar(4000 )/*等其他字段*/)

    organization external

   (type oracle_loader

    default directory bak

    access parameters

    ( records delimited by newline characterset UTF16

    fields terminated by "  "

    )location ( '文件名称.txt' ))

    reject limit unlimited; 

参数注释：

    ( records delimited by newline characterset UTF16 /*字符集要和“文件名称.txt”字符集相同,而非数据和系统文件（查看T.txt字符集命令：file T.txt）*/

    fields terminated by "  " /*描述字段的终止符*/

3、查询表即可 select * from test; 

参数介绍：

a）ORGANIZATION EXTERNAL 关键字，必须要有。以表明定义的表为外部表。
b）外部表的类型
      ORACLE_LOADER：定义外部表的缺省方式，只能只读方式实现文本数据的装载。
      ORACLE_DATAPUMP：支持对数据的装载与卸载，数据文件必须为二进制dump文件。可以从外部表提取数据装载到内部表，也可以从内部表卸载数据作为二进制文件填充到外部表。
c）DEFAULT DIRECTORY：缺省的目录指明了外部文件所在的路径
d）LOCATION：定义了外部表的位置
e）ACCESS PARAMETERS：描述如何对外部表进行访问
     RECORDS关键字后定义如何识别数据行  
     DELIMITED BY 'XXX'——换行符，常用newline定义换行，并指明字符集。对于特殊的字符则需要单独定义，如特殊符号，

     可以使用OX'十六位值'，例如tab(/t)的十六位是9，则DELIMITEDBY0X'09'；
     cr(/r)的十六位是d，那么就是DELIMITEDBY0X'0D'。
     SKIP X ——跳过X行数据，有些文件中第一行是列名，需要跳过第一行，则使用SKIP 1。
     FIELDS关键字后定义如何识别字段，常用的如下：
     FIELDS：TERMINATED BY 'x'——字段分割符。
     ENCLOSED BY 'x'——字段引用符，包含在此符号内的数据都当成一个字段。
      例如一行数据格式如："abc","a""b,""c,"。使用参数TERMINATED BY ',' ENCLOSED BY '"'后，系统会读到两个字段，第一个字段的值是abc，第二个字段值是a"b,"c,。
     LRTRIM ——删除首尾空白字符。
     MISSING FIELD VALUES ARE NULL——某些字段空缺值都设为NULL。
     对于字段长度和分割符不确定且准备用作外部表文件，可以使用UltraEdit、Editplus等来进行分析测试，如果文件较大，则需要考虑将文件分割成小文件并从中提取数据进行测试。

f）FIELDS TERMINATED BY ","  --描述字段的终止符 

g）REJECT LIMIT UNLIMITED    --描述允许的错误数，此处为无限制