写在前面的一些废话
今天帮一个朋友在Windows下面弄Oracle发现我居然不会弄了…[悲催],而且运行起来也比较卡…令:本站备案刚刚通过了.
SQL*Loader创建外部表
在当前目录保存以下内容[文件名:ldr_case1.ctl]:
LOAD DATA
INFILE *
APPEND INTO TABLE BONUS -- 这个表必须是已经存在的
FIELDS TERMINATED BY ","
(ENAME,JOB,SAL)
BEGINDATA
SMITH,CLEAK,3904
ALLEN,SALESMAN,2891
WARD,SALESMAN,3128
KING,PRESIDENT,2523
可以使用下面的命令去生成相关文件:
[当前路径]: sqlldr SCOTT/TIGER control=ldr_case1.ctl external_table=generate_only
执行之后就会有一个ldr_case1.log里面会有相关脚本.
external_table有三个参数:
- NOT_USED:不使用外部表,默认值.
- GENERATE_ONLY:SQLLDR并不执行加载,而是生成创建外部表的SQL和处理数据的SQL.并保存在log文件中.[修改后可再SQL*Plus中执行]
- EXECUTE:首先创建外部表,然后通过外部表方式加载数据
执行上述命令之后,ldr_case1.log的内容如下[节选:创建DIRECTORY,创建TABLE部分]:
CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/Documents/oracle_product/external'
CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_BONUS"
(
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"SAL" NUMBER
)
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':'ldr_case1.bad'
LOGFILE 'ldr_case1.log_xt'
READSIZE 1048576
SKIP 6
FIELDS TERMINATED BY "," LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"ENAME" CHAR(255)
TERMINATED BY ",",
"JOB" CHAR(255)
TERMINATED BY ",",
"SAL" CHAR(255)
TERMINATED BY ","
)
)
location
(
'ldr_case1.ctl'
)
)REJECT LIMIT UNLIMITED
INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO BONUS
(
ENAME,
JOB,
SAL
)
SELECT
"ENAME",
"JOB",
"SAL"
FROM "SYS_SQLLDR_X_EXT_BONUS"
statements to cleanup objects created by previous statements:
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_BONUS"
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
CREATE TABLE方式创建外部表
其实我最喜欢的是这种方式,至少目前是.
//文件保存为:tes.ctl
LOAD DATA
INFILE *
TRUNCATE INTO TABLE TES
FIELDS TERMINATED BY ","
(TNAME,TAGE,TINFO char(2000))
BEGINDATA
'tes',20,'dfsafsdafjklsdafdjksalkfjaslkfjkdsalfdjksalkfjsalfkdjsalkfdlsakfjklsd''sas',22,'dfsafsaewqioeuwoiqruewoiqruiejkfsadljfldksjakfldsaljkfjklasdfjksaldfsadsalfkjslkafjkdlsakjfdjlsafkjslkfjdslkafkjdsaf'
'fdsa',22,'fdsafsdafldsjaklfjsalkfksmncklmsa;djkl;sajfoiewfjsaljfdlskafjlksajflklksjaklfjdslajflksd'
'fdsasa',25,'dsafsadlkfjlkwejewioqfwiqfl;ksa;klfdslkafdlkjsajlkfdksjlajfkl;sa;jkfdsaldkjsal;fkskj'
别激动,往下看
//文件保存为:tes.sql
CONN / AS SYSDBA; //连接到系统
STARTUP FORCE //如果数据库已经启动,此行将先关闭数据库,然后再次启动.
CREATE DIRECTORY TES AS '/home/oracle/Documents/oracle_product/external/test/'; //必须是已经存在的目录,并且有可读写权限
GRANT READ,WRITE ON DIRECTORY TES TO SCOTT; //授权
-- CREATE TEST TABLE.
CREATE TABLE TES(
TNAME VARCHAR2(10),
TAGE NUMBER,
TINFO VARCHAR2(2000)
)
-- external table.
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY TES
-- 加载数据
ACCESS PARAMETERS(
RECORDS DELIMITED BY NEWLINE
SKIP 6
FIELDS TERMINATED BY ","
(TNAME,TAGE,TINFO)
)
LOCATION('tes.ctl')
-- end 加载数据
);
-- end 声明外部表
-- output table data.
SELECT * FROM TES;
-- delete table.
DROP TABLE TES;
-- delete directory.
DROP DIRECTORY TES;
可以在进入SQL*Plus界面之后,直接执行
@tes.sql
可以完成以下操作:–>启动数据库[已经启动的将先被关闭]–>创建外部表–>授权给SCOTT用户–>创建表–>导入数据–>使用SELECT输出表数据–>删除DIRECTORY对象–>删除创建的表.