对于处理数据库海量数据的存储，也可以采用以下的方法处理；
主要是针对CSV等格式的外部文件。
1. csv的结构，每个字段以什么为分割
2. external table的创建
3. 如何实现外部表访问外部文件，并显示在数据库里面
例如 有一个外部文件名字为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')
)
reject limit unlimited;
表创建完成;
可以查看数据 select * from temptes;
如果不知道怎么写external table的创建语法，可以利用利用sqlldr生成external_table表的语法；
例如在F盘有个SQLLDR文件的DETAIL.ctl文件：
load data
infile 'DETAIL.csv'
into table global_region_bns_detail
(SBH char terminated by '|',
DBH char terminated by '|',
Y_GJ char terminated by '|',
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 '|',
LD_CS char terminated by '|',
DS char terminated by '|',
BNS_CS char terminated by '|',
BNS_ID char terminated by '|'
)；
利用命令 ：CMD>sqlldr scott/123@user control=DETAIL.ctl 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.
控制文件: DETAIL.ctl
数据文件: 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 * | CHARACTER
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 "|",
"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 "|",
"ZC_CS" CHAR(255)
TERMINATED BY "|",
"ZJ" CHAR(255)
TERMINATED BY "|",
"JJ" CHAR(255)
TERMINATED BY "|",
"LDDOWN" CHAR(255)
TERMINATED BY "|",
"PW" CHAR(255)
TERMINATED BY "|",
"LD" CHAR(255)
TERMINATED BY "|",
"LD_CS" CHAR(255)
TERMINATED BY "|",
"DS" CHAR(255)
TERMINATED BY "|",
"BNS_CS" CHAR(255)
TERMINATED BY "|",
"BNS_ID" CHAR(255)
TERMINATED BY "|"
)
)
location
(
'DETAIL.csv'
)
)REJECT LIMIT UNLIMITED
用于加载内部表的 INSERT 语句:
------------------------------------------------------------------------
INSERT /*+ append */ INTO GLOBAL_REGION_BNS_DETAIL
(
SBH,
DBH,
Y_GJ,
D_XJ,
GRXFBV,
TERMLYBV,
GYBV_300I,
GYBV_300O,
GYBV_ZC,
ZC,
ZC_CS,
ZJ,
JJ,
LDDOWN,
PW,
LD,
LD_CS,
DS,
BNS_CS,
BNS_ID
)
SELECT
"SBH",
"DBH",
"Y_GJ",
"D_XJ",
"GRXFBV",
"TERMLYBV",
"GYBV_300I",
"GYBV_300O",
"GYBV_ZC",
"ZC",
"ZC_CS",
"ZJ",
"JJ",
"LDDOWN",
"PW",
"LD",
"LD_CS",
"DS",
"BNS_CS",
"BNS_ID"
FROM "SYS_SQLLDR_X_EXT_GLOBAL_REGION"
用于清除由以前的语句创建的对象的语句:
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_GLOBAL_REGION"
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
从 星期三 4月 10 12:45:27 2013 开始运行
在 星期三 4月 10 12:45:28 2013 处运行结束
经过时间为: 00: 00: 00.13
CPU 时间为: 00: 00: 00.08；
这里面就有你要的语法，同时还创建了CREATE DIRECTORY 语句。
可以说SQLLDR是external_table的一个补充。
