mysqlexternaltable_创建Oracle外部表ExternalTable 对于处理数据库海量数据的存储，也可以采⽤以下的⽅法处理；
主要是针对CSV等格式的外部⽂件。
1. csv的结构，每个字段以什么为分割
2. external table的创建
3. 如何实现外部表访问外部⽂件，并显⽰在数据库⾥⾯
html语言的标记区分大小写吗例如 有⼀个外部⽂件名字为temptes.csv
⽂件内容如下：
sunny,85,85,FALSE,no
sunny,80,90,TRUE,no
overcast,83,86,FALSE,yes
rainy,70,96,FALSE,yes
rainy,68,80,FALSE,yes
rainy,65,70,TRUE,no
overcast,64,65,TRUE,yes
sunny,72,95,FALSE,no
sunny,69,70,FALSE,yes
rainy,75,80,FALSE,yes
sunny,75,70,TRUE,yes
overcast,72,90,TRUE,yes
overcast,81,75,FALSE,yes
rainy,71,91,TRUE,no
要导⼊到数据库⾥。
⾸先创建⼀个创建⼀个Directory：必须⽤sys⽤户创建  create directory dirtemp as 'F:\EXTERNALDIR'
;
然后授权 grant read,write on directory dirtemp to users; //⽤户名
把⽂件temptes.csv放到'F:\EXTERNALDIR'⽂件⾥。
使⽤被授权的⽤户users创建外部表，表名和你的⽂件名⼀定要⼀致。
Create table temptes
(WTMARKET varchar2(20),
fenli1 varchar2(50),
fenli2  varchar2(20),
errro varchar2(20),
flag varchar2(20)
)
organization external
(
type oracle_loader
default directory dirtemp
access parameters ( fields terminated by ',' )
location (temptes.CSV')
)django框架课
reject limit unlimited;
表创建完成;
可以查看数据 select * from  temptes;
如果不知道怎么写external table的创建语法，可以利⽤利⽤sqlldr⽣成external_table表的语法；例如在F盘有个SQLLDR⽂件的l⽂件：
load data
infile 'DETAIL.csv'
into table global_region_bns_detail
(SBH      char terminated by '|',
DBH      char terminated by '|',
Y_GJ      char terminated by '|',
alert是什么意思啊D_XJ      char terminated by '|',
GRXFBV    char terminated by '|',
TERMLYBV  char terminated by '|',
GYBV_300I char terminated by '|',
GYBV_300O char terminated by '|',
GYBV_ZC  char terminated by '|',
ZC        char terminated by '|',
ZC_CS    char terminated by '|',
ZJ        char terminated by '|',
JJ        char terminated by '|',
LDDOWN    char terminated by '|',
PW        char terminated by '|',
LD        char terminated by '|',
ospf db报文LD_CS    char terminated by '|',
DS        char terminated by '|',
BNS_CS    char terminated by '|',
BNS_ID    char terminated by '|'
)；
利⽤命令 ：CMD>sqlldr scott/123@user l external_table=generate_only;⽣成了DETAIL.log⽂件，打开如下：
SQL*Loader: Release 10.2.0.1.0 - Production on 星期三 4⽉ 10 12:45:27 2013
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
控制⽂件:      l
数据⽂件:      DETAIL.csv
错误⽂件:    DETAIL.bad
废弃⽂件:    未作指定
(可废弃所有记录)
要加载的数: ALL
要跳过的数: 0
允许的错误: 50
继续:    未作指定
所⽤路径:      外部表
表 GLOBAL_REGION_BNS_DETAIL，已加载从每个逻辑记录
插⼊选项对此表 INSERT ⽣效
列名                        位置      长度  中⽌ 包装数据类型
------------------------------ ---------- ----- ---- ---- ---------------------
SBH                                FIRST    *  |      CHARACTER
DBH                                  NEXT    *  |      CHARACTER
Y_GJ                                NEXT    *  |      CHARACTER
D_XJ                                NEXT    *  |      CHARACTERscopes
GRXFBV                              NEXT    *  |      CHARACTER
TERMLYBV                            NEXT    *  |      CHARACTER
GYBV_300I                            NEXT    *  |      CHARACTER
GYBV_300O                            NEXT    *  |      CHARACTER
GYBV_ZC                              NEXT    *  |      CHARACTER
ZC                                  NEXT    *  |      CHARACTER
ZC_CS                                NEXT    *  |      CHARACTER
ZJ                                  NEXT    *  |      CHARACTER
JJ                                  NEXT    *  |      CHARACTER
LDDOWN                              NEXT    *  |      CHARACTER
PW                                  NEXT    *  |      CHARACTER
LD                                  NEXT    *  |      CHARACTER
LD_CS                                NEXT    *  |      CHARACTER
DS                                  NEXT    *  |      CHARACTER
BNS_CS                              NEXT    *  |      CHARACTER
BNS_ID                              NEXT    *  |      CHARACTER
⽂件需要 CREATE DIRECTORY 语句
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS 'F:\'⽤于外部表的 CREATE TABLE 语句:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_GLOBAL_REGION"
(
"SBH" VARCHAR2(30),
"DBH" VARCHAR2(30),
"Y_GJ" VARCHAR2(30),
"D_XJ" VARCHAR2(30),
"GRXFBV" VARCHAR2(30),
"TERMLYBV" VARCHAR2(30),
"GYBV_300I" VARCHAR2(30),
"GYBV_300O" VARCHAR2(30),
"GYBV_ZC" VARCHAR2(30),
"ZC" VARCHAR2(30),
"ZC_CS" VARCHAR2(30),
"ZJ" VARCHAR2(30),
"JJ" VARCHAR2(30),
"LDDOWN" VARCHAR2(30),
"PW" VARCHAR2(30),
"LD" VARCHAR2(30),
"LD_CS" VARCHAR2(30),
"DS" VARCHAR2(30),
"BNS_CS" VARCHAR2(30),
"BNS_ID" VARCHAR2(30)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'DETAIL.bad' LOGFILE 'DETAIL.log_xt'
READSIZE 1048576
FIELDS LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"SBH" CHAR(255)
TERMINATED BY "|",
"DBH" CHAR(255)
TERMINATED BY "|",
mysql语句转oracle"Y_GJ" CHAR(255)
TERMINATED BY "|",
"D_XJ" CHAR(255)
TERMINATED BY "|",
"GRXFBV" CHAR(255)
TERMINATED BY "|",
"TERMLYBV" CHAR(255)
TERMINATED BY "|",
"GYBV_300I" CHAR(255)
TERMINATED BY "|",
"GYBV_300O" CHAR(255)
TERMINATED BY "|",
"GYBV_ZC" CHAR(255)
TERMINATED BY "|",
"ZC" CHAR(255)
TERMINATED BY "|",

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。