写在前面的一些废话
今天帮一个朋友在Windows下面弄Oracle发现我居然不会弄了…[悲催],而且运行起来也比较卡…令:本站备案刚刚通过了.
SQL*Loader创建外部表
在当前目录保存以下内容[文件名:ldr_case1.ctl]:
1 2 3 4 5 6 7 8 9 10 | 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 |
可以使用下面的命令去生成相关文件:
1 | [当前路径]: 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部分]:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 | 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方式创建外部表
其实我最喜欢的是这种方式,至少目前是.
1 2 3 4 5 6 7 8 9 10 11 | //文件保存为: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' |
别激动,往下看
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | //文件保存为: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界面之后,直接执行
1 | @tes.sql |
可以完成以下操作:–>启动数据库[已经启动的将先被关闭]–>创建外部表–>授权给SCOTT用户–>创建表–>导入数据–>使用SELECT输出表数据–>删除DIRECTORY对象–>删除创建的表.