sqlldr的例子:
以下例子取自&ORALCE_HOME/rdbms/demo
Case Study 1:
LOAD DATA
INFILE * --指定加载文件 *表示数据就在控制文件后面
INTO TABLE DEPT --指定表名
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' --指定区域分隔符为逗号
(DEPTNO, DNAME, LOC) --指定表的列名
BEGINDATA --仅当INFILE指定*时有效
12,RESEARCH,"SARATOGA"
10,"ACCOUNTING",`CLEVELAND
11,"ART",SALEM
13,FINANCE,"BOSTON"
21,"SALES",PHILA.
22,"SALES",ROCHESTER
42,"INT'L","SAN FRAN"
sql>sqlldr u1/u1 control=ulcase1.ctl;
注意这里的sqlldr可以写成sql>sqlldr u1/u1 ulcase1.ctl
也可以写成sql>sqlldr userid=u1/u1 control=ulcase1.ctl
但是不可以写成sql>sqlldr u1/u1 control=ulcase1.ctl ulcase1.log;
Case Study 2:
LOAD DATA
(empno POSITION(01:04) INTEGER EXTERNAL, --position(01:04)指的是从第1个字符载止到第4个字符作为empno的,下同理
mgr POSITION(27:30) INTEGER EXTERNAL,
sal POSITION(32:39) DECIMAL EXTERNAL,
comm POSITION(41:48) DECIMAL EXTERNAL,
deptno POSITION(50:51) INTEGER EXTERNAL)
数据部分:ulcase2.dat
7782 CLARK MANAGER 7839 2572.50 10
7839 KING PRESIDENT 5500.00 10
7934 MILLER CLERK 7782 920.00 10
7566 JONES MANAGER 7839 3123.75 20
7499 ALLEN SALESMAN 7698 1600.00 300.00 30
空白部分自动设置为NULL
sql>sqlldr u1/u1 control=ulcase2.ctl
Case Study 3:
增加两列:
sql>ALTER TABLE EMP ADD (PROJNO NUMBER, LOADSEQ NUMBER);
控制文件ulcase3.ctl
LOAD DATA
INFILE *
APPEND --APPEND的使用可以在表为非空的情况下也可以输入数据
INTO TABLE EMP
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(empno, ename, job, mgr,
hiredate DATE(20) "DD-Month-YYYY",/*格式以DD-Month-YYYY出现,并且DATE的大小为20*/
sal, comm,
deptno CHAR TERMINATED BY ':',/*这个应该是10:101中:前面的插入到deptno中,后面的插入到projno中/*
projno,
loadseq SEQUENCE(MAX,1) )/*意思是先找出loadseq中最大的数,然后在这数的基础上对要输入的数据加1,比如loadseq现在最大的数似乎1,那么下一个数就是2.*/
BEGINDATA
7782, "Clark", "Manager", 7839, 09-June-1981, 2572.50,, 10:101
7839, "King", "President", , 17-November-1981, 5500.00,, 10:102
7934, "Miller", "Clerk", 7782, 23-January-1982, 920.00,, 10:102
7566, "Jones", "Manager", 7839, 02-April-1981, 3123.75,, 20:101
7499, "Allen", "Salesman", 7698, 20-February-1981, 1600.00, 300.00, 30:103
7654, "Martin", "Salesman", 7698, 28-September-1981, 1312.50, 1400.00, 30:103
7658, "Chan", "Analyst", 7566, 03-May-1982, 3450,, 20:101
Case Study 4:
控制文件ulcase4.ctl
LOAD DATA
INFILE "ulcase4.dat"
DISCARDFILE "ulcase4.dis" --指定discardfile文件
DISCARDMAX 999 --在终止运行前允许999条discard记录
REPLACE /*如果表中存在数据那么会先删除此条数据,再输入新的数据*/
CONTINUEIF (1) = '*' /*省略THIS因为THIS是默认值,也可以写成CONTINUEIF THIS(1) = '*',表示如果这条记录的第一列为*,那么下一条记录合并到此条记录中,如果这条记录的第一列不是*,那么这条记录就为这条physical记录的最后一条logical记录,并且每一条记录的前面都要有*号标明*/
INTO TABLE EMP
( EMPNO POSITION(01:04) INTEGER EXTERNAL,
ENAME POSITION(06:15) CHAR,
JOB POSITION(17:25) CHAR,
MGR POSITION(27:30) INTEGER EXTERNAL,
SAL POSITION(32:39) DECIMAL EXTERNAL,
COMM POSITION(41:48) DECIMAL EXTERNAL,
DEPTNO POSITION(50:51) INTEGER EXTERNAL,
HIREDATE POSITION(52:60) INTEGER EXTERNAL)
数据文件:
*7782 CLARK MA
NAGER 7839 2572.50 -10 2512-NOV-85
*7839 KING PR
ESIDENT 5500.00 2505-APR-83
*7934 MILLER CL
ERK 7782 920.00 2508-MAY-80
*7566 JONES MA
NAGER 7839 3123.75 2517-JUL-85
*7499 ALLEN SA
LESMAN 7698 1600.00 300.00 25 3-JUN-84
*7654 MARTIN SA
LESMAN 7698 1312.50 1400.00 2521-DEC-85
*7658 CHAN AN
ALYST 7566 3450.00 2516-FEB-84
* CHEN AN --empno空
ALYST 7566 3450.00 2516-FEB-84
*7658 CHIN AN --违反唯一性约束
ALYST 7566 3450.00 2516-FEB-84
--最后两条数据因为违反主键约束,所以不能输入,记录在ulcase4.bad文件中
sql>sqlldr userid=u1/u1 control=ulcase4.ctl log=ulcase4.log;
Case Study 5:
控制文件ulcase5.ctl
LOAD DATA
INFILE 'ulcase5.dat'
BADFILE 'ulcase5.bad'
DISCARDFILE 'ulcase5.dis'
REPLACE
INTO TABLE EMP
(EMPNO POSITION(1:4) INTEGER EXTERNAL,
ENAME POSITION(6:15) CHAR,
DEPTNO POSITION(17:18) CHAR,
MGR POSITION(20:23) INTEGER EXTERNAL)
INTO TABLE PROJ
-- PROJ has two columns, both not null: EMPNO and PROJNO
WHEN PROJNO != ' '
(EMPNO POSITION(1:4) INTEGER EXTERNAL,
PROJNO POSITION(25:27) INTEGER EXTERNAL) -- 1st proj
INTO TABLE PROJ
WHEN PROJNO != ' '
(EMPNO POSITION(1:4) INTEGER EXTERNAL,
PROJNO POSITION(29:31) INTEGER EXTERNAL) -- 2nd proj
INTO TABLE PROJ
WHEN PROJNO != ' '
(EMPNO POSITION(1:4) INTEGER EXTERNAL,
PROJNO POSITION(33:35) INTEGER EXTERNAL) -- 3rd proj
数据文件:
1234 BAKER 10 9999 101 102 103
1234 JOKER 10 9999 777 888 999
2664 YOUNG 20 2893 425 abc 102
5321 OTOOLE 10 9999 321 55 40
2134 FARMER 20 4555 236 456
2414 LITTLE 20 5634 236 456 40
6542 LEE 10 4532 102 321 14
2849 EDDS xx 4555 294 40
4532 PERKINS 10 9999 40
1244 HUNT 11 3452 665 133 456
123 DOOLITTLE 12 9940 132
1453 MACDONALD 25 5532 200
SQL> SELECT empno, ename, mgr, deptno FROM emp;
EMPNO ENAME MGR DEPTNO
------ ------ ------ ------
1234 BAKER 9999 10
5321 OTOOLE 9999 10
2134 FARMER 4555 20
2414 LITTLE 5634 20
6542 LEE 4532 10
4532 PERKINS 9999 10
1244 HUNT 3452 11
123 DOOLITTLE 9940 12
1453 MACDONALD 5532 25
说明:因为JOKER违反了主键约束,YONNG违反了PROJNO中的字段为NUMBER的约束,EDDS违反了EMP中DEPTNO的NUMBER约束,所以这三条记录被放在rejected文件里。而WHEN语句是将记录过滤出来,并没有将他们放进rejected文件里.
SQL> SELECT * from PROJ order by EMPNO;
EMPNO PROJNO
------ ------
123 132
1234 101
1234 103
1234 102
1244 665
1244 456
1244 133
1453 200
2134 236
2134 456
2414 236
2414 456
2414 40
4532 40
5321 321
5321 40
5321 55
6542 102
6542 14
6542 321
这里一共20条记录,在第一个WHEN语句里一共存入了7条记录,2条rejected,其实应该有3条丢弃,但是EDDS这条记录即违反了EMP中的约束也违反了PROJ中的的非空约束,所以就变成了3条丢弃,三条被WHEN过滤掉。第二个3条rejected,2条不满足WHEN条件,第三个是3个rejected,3条不满足WHEN条件
Case Study 6:
控制文件ulcase6.ctl
load data
infile 'ulcase6.dat'
replace
into table emp
sorted indexes (empix) /*因为是direct path,所以先对索引进行重新排序,这样不至于浪费太多的空间*/
(empno position(1:4),
ename position(6:15),
job position(17:25),
mgr position(27:30) nullif mgr=blanks, --如果此字段为空,那么用blanks代替
sal position(32:39) nullif sal=blanks,
comm position(41:48) nullif comm=blanks,
deptno position(50:51) nullif empno=blanks)
Case Study 7
控制文件:ulcase7.ctl
LOAD DATA
INFILE 'ULCASE7.DAT'
APPEND
INTO TABLE emp
1) WHEN (57) = '.'
2) TRAILING NULLCOLS
3) (hiredate SYSDATE,
4) deptno POSITION(1:2) INTEGER EXTERNAL(3)
5) NULLIF deptno=BLANKS,
job POSITION(7:14) CHAR TERMINATED BY WHITESPACE /*是指字段之间不管是空格还是tab,还是两者混合体就作为一个间隔符对待*/
6) NULLIF job=BLANKS "UPPER(:job)",
7) mgr POSITION(28:31) INTEGER EXTERNAL
TERMINATED BY WHITESPACE, NULLIF mgr=BLANKS,
ename POSITION(34:41) CHAR
TERMINATED BY WHITESPACE "UPPER(:ename)",
empno POSITION(45) INTEGER EXTERNAL
TERMINATED BY WHITESPACE,
sal POSITION(51) CHAR TERMINATED BY WHITESPACE
8) "TO_NUMBER(:sal,'$99,999.99')",
9) comm INTEGER EXTERNAL ENCLOSED BY '(' AND '%' /*因为是comm是数值类型,所以取’(’与’%’之间的数字。但是为什么下面要乘以100呢?*/
":comm * 100"
)
数据文件:ulcase7.dat
Today's Newly Hired Employees
Dept Job Manager MgrNo Emp Name EmpNo Salary (Comm)
---- -------- -------- ----- -------- ----- --------- ------
20 Salesman Blake 7698 Shepard 8061 $1,600.00 (3%)
Falstaff 8066 $1,250.00 (5%)
Major 8064 $1,250.00 (14%)
30 Clerk Scott 7788 Conrad 8062 $1,100.00
Ford 7369
DeSilva 8063 $800.00
Manager King 7839 Provo 8065 $2,975.00
sql脚本:创建触发器和包
CREATE OR REPLACE PACKAGE uldemo7 AS -- Global Package Variables
last_deptno NUMBER(2); /*如果这里如果显示权限不足,
grant create procedure to user_name;*/
last_job VARCHAR2(9);
last_mgr NUMBER(4);
END uldemo7;
/
CREATE OR REPLACE TRIGGER uldemo7_emp_insert /*如果权限不足,grant create trigger to user_name;
BEFORE INSERT ON emp
FOR EACH ROW
BEGIN
IF :new.deptno IS NOT NULL THEN
uldemo7.last_deptno := :new.deptno; -- save value for later
ELSE
:new.deptno := uldemo7.last_deptno; -- use last valid value
END IF;
IF :new.job IS NOT NULL THEN
uldemo7.last_job := :new.job;
ELSE
:new.job := uldemo7.last_job;
END IF;
IF :new.mgr IS NOT NULL THEN
uldemo7.last_mgr := :new.mgr;
ELSE
:new.mgr := uldemo7.last_mgr;
END IF;
END;
/
Case Study 8
一共三个文件ulcase8.ctl ulcase8.dat ulcase8.sql
控制文件ulcase8.ctl
LOAD DATA
1) INFILE 'ulcase8.dat' "fix 129"
BADFILE 'ulcase8.bad'
TRUNCATE
INTO TABLE lineitem
PARTITION (ship_q1)
2) (l_orderkey position (1:6) char,
l_partkey position (7:11) char,
l_suppkey position (12:15) char,
l_linenumber position (16:16) char,
l_quantity position (17:18) char,
l_extendedprice position (19:26) char,
l_discount position (27:29) char,
l_tax position (30:32) char,
l_returnflag position (33:33) char,
l_linestatus position (34:34) char,
l_shipdate position (35:43) char,
l_commitdate position (44:52) char,
l_receiptdate position (53:61) char,
l_shipinstruct position (62:78) char,
l_shipmode position (79:85) char,
l_comment position (86:128) char)
数据文件ulcase8.dat
1 151978511724386.60 7.04.0NO09-SEP-6412-FEB-9622-MAR-96DELIVER IN
PERSONTRUCK iPBw4mMm7w7kQ zNPL i261OPP
1 2731 73223658958.28.09.06NO12-FEB-9628-FEB-9620-APR-96TAKE BACK RETURN
MAIL 5wM04SNyl0AnghCP2nx lAi
1 3370 3713 810210.96 .1.02NO29-MAR-9605-MAR-9631-JAN-96TAKE BACK RETURN
REG AIRSQC2C 5PNCy4mM
1 5214 46542831197.88.09.06NO21-APR-9630-MAR-9616-MAY-96NONE
AIR Om0L65CSAwSj5k6k
1 6564 6763246897.92.07.02NO30-MAY-9607-FEB-9603-FEB-96DELIVER IN
PERSONMAIL CB0SnyOL PQ32B70wB75k 6Aw10m0wh
1 7403 160524 31329.6 .1.04NO30-JUN-9614-MAR-9601 APR-96NONE
FOB C2gOQj OB6RLk1BS15 igN
2 8819 82012441659.44 0.08NO05-AUG-9609-FEB-9711-MAR-97COLLECT COD
AIR O52M70MRgRNnmm476mNm
3 9451 721230 41113.5.05.01AF05-SEP-9629-DEC-9318-FEB-94TAKE BACK RETURN
FOB 6wQnO0Llg6y
3 9717 1834440788.44.07.03RF09-NOV-9623-DEC-9315-FEB-94TAKE BACK RETURN
SHIP LhiA7wygz0k4g4zRhMLBAM
3 9844 1955 6 8066.64.04.01RF28-DEC-9615-DEC-9314-FEB-94TAKE BACK RETURN
REG AIR6nmBmjQkgiCyzCQBkxPPOx5j4hB 0lRywgniP1297
sql文件:
create table lineitem
(l_orderkey number,
l_partkey number,
l_suppkey number,
l_linenumber number,
l_quantity number,
l_extendedprice number,
l_discount number,
l_tax number,
l_returnflag char,
l_linestatus char,
l_shipdate date,
l_commitdate date,
l_receiptdate date,
l_shipinstruct char(17),
l_shipmode char(7),
l_comment char(43))
partition by range (l_shipdate)--创建分区表
(
partition ship_q1 values less than (TO_DATE('01-APR-1996', 'DD-MON-YYYY'))
tablespace p01,
partition ship_q2 values less than (TO_DATE('01-JUL-1996', 'DD-MON-YYYY'))
tablespace p02,
partition ship_q3 values less than (TO_DATE('01-OCT-1996', 'DD-MON-YYYY'))
tablespace p03,
partition ship_q4 values less than (TO_DATE('01-JAN-1997', 'DD-MON-YYYY'))
tablespace p04
)
Case Study 9:
一个控制文件ulcase9.ctl,一个sql脚本ulcase9.sql,6个数据文件ulcase1-6.dat,这六个数据文件都是lob文件
控制文件ulcase9.ctl
LOAD DATA
INFILE *
INTO TABLE EMP
REPLACE
FIELDS TERMINATED BY ','
( EMPNO INTEGER EXTERNAL,
ENAME CHAR,
JOB CHAR,
MGR INTEGER EXTERNAL,
SAL DECIMAL EXTERNAL,
COMM DECIMAL EXTERNAL,
DEPTNO INTEGER EXTERNAL,
1) RES_FILE FILLER CHAR,--解释在后面
2) "RESUME" LOBFILE (RES_FILE) TERMINATED BY EOF NULLIF RES_FILE = 'NONE' --解释在后面
)
BEGINDATA
7782,CLARK,MANAGER,7839,2572.50,,10,ulcase91.dat
7839,KING,PRESIDENT,,5500.00,,10,ulcase92.dat
7934,MILLER,CLERK,7782,920.00,,10,ulcase93.dat
7566,JONES,MANAGER,7839,3123.75,,20,ulcase94.dat
7499,ALLEN,SALESMAN,7698,1600.00,300.00,30,ulcase95.dat
7654,MARTIN,SALESMAN,7698,1312.50,1400.00,30,ulcase96.dat
7658,CHAN,ANALYST,7566,3450.00,,20,NONE
1)2)动态的指定LOB文件,RES_FILE是一个变量,下面的BINGINDATA最后一个字段例如:ulcase91.dat传入到LOBFILE中,再读取.TERMINATED BY EOF表示从开始的第一个字节读取到文件的最后一个字节,RES_FILE=’NULL’表示如果LOB文件文件中没有数据,那么就为NULL.
数据文件ulcase91-6.dat
Input Data Files
Career Objective: Manage a sales team with consistent record breaking
Education: BA Business University of Iowa 1992
Experience: 1992-1994 - Sales Support at MicroSales Inc.
Won "Best Sales Support" award in 1993 and 1994
1994-Present - Sales Manager at MicroSales Inc.
Most sales in mid-South division for 2 years
Career Objective: President of large computer services company
Education: BA English Literature Bennington, 1985
Experience: 1985-1986 - Mailroom at New World Services
1986-1987 - Secretary for sales management at
1988-1989 - Sales support at New World Services
1990-1992 - Salesman at New World Services
1993-1994 - Sales Manager at New World Services
1995 - Vice President of Sales and Marketing at
1996-Present - President of New World Services
Career Objective: Work as a sales support specialist for a services
Education: Plainview High School, 1996
Experience: 1996 - Present: Mail room clerk at New World Services
Career Objective: Work in senior sales management for a vibrant and
Education: BA Philosophy Howard Univerity 1993
Experience: 1993 - Sales Support for New World Services
1994-1995 - Salesman for New World Services. Led in
US sales in both 1994 and 1995.
1996 - present - Sales Manager New World Services. My
sales team has beat its quota by at least 15% each
Career Objective: Senior Sales man for agressive Services company
Education: BS Business Administration, Weber State 1994
Experience: 1993-1994 - Sales Support New World Services
1994-present - Salesman at New World Service. Won sales
award for exceeding sales quota by over 20%
Career Objective: Salesman for a computing service company
Education: 1988 - BA Mathematics, University of the North
Experience: 1988-1992 Sales Support, New World Services
1993-present Salesman New World Services
sqlldr>sqlldr u1/u1 control=ulcase9.dat
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29840459/viewspace-1684980/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29840459/viewspace-1684980/