SQL*Loader实验笔记【三】
所有SQL*Loader实验笔记
实验案例总结（1-7）： SQL*Loader实验笔记【一】
实验案例总结（8-13）： SQL*Loader实验笔记【二】
实验案例总结（14-19）： SQL*Loader实验笔记【三】
14）加载excel(csv)中的数据
- 初始化
CJ@db11g>create table t4 (x int,y varchar2(30),z date); Table created.
保存excel文件为csv格式
数据内容
1,one row,1901-01-01 2,two row,1902-01-01 3,three row,1903-01-01 4,four row,1904-01-01 5,five row,1905-01-01 6,six row,1906-01-01 7,serven row,1907-01-01 8,eight row,1908-01-01 9,nine row,1909-01-01 10,ten row
- 控制文件
LOAD DATA INFILE 'test.csv' APPEND INTO TABLE t4 FIELDS TERMINATED BY ',' TRAILING NULLCOLS ( x INTEGER EXTERNAL TERMINATED BY ",", y CHAR TERMINATED BY ",", z DATE "RRRR-MM-DD" TERMINATED BY "," )
- 验证结果
CJ@db11g>select * from t4;
X Y Z
---------- ------------------------------ ---------
1 one row 01-JAN-01
2 two row 01-JAN-02
3 three row 01-JAN-03
4 four row 01-JAN-04
5 five row 01-JAN-05
6 six row 01-JAN-06
7 serven row 01-JAN-07
8 eight row 01-JAN-08
9 nine row 01-JAN-09
10 ten row
10 rows selected.
- 备注
- 和普通的加载没有什么不同，值得注意的是这里可以使用在某个字段后面直接指定TERMINATED BY的方式，相比全局的设置更加的灵活。
15）导入固定格式的文件
- 初始化
CJ@db11g>desc t4 Name Null? Type ----------------------------------------- -------- ---------------------------- X NUMBER(38) Y VARCHAR2(30) Z DATE
数据内容
1one row 1901-01-01 2two row 1902-01-01 3three row 1903-01-01 4four row 1904-01-01 5five row 1905-01-01 6six row 1906-01-01 7serven row 1907-01-01 8eight row 1908-01-01 9nine row 1909-01-01
- 控制文件
LOAD DATA INFILE 'test.csv' REPLACE INTO TABLE t4 FIELDS TERMINATED BY ',' TRAILING NULLCOLS ( x POSITION(1:1) INTEGER EXTERNAL, y POSITION(2:12) CHAR "upper(:y)", z POSITION(13:22) DATE "RRRR-MM-DD" )
- 验证结果
CJ@db11g>select * from t4;
X Y Z
---------- ------------------------------ ---------
1 ONE ROW 190 01-JAN-01
2 TWO ROW 190 01-JAN-02
3 THREE ROW 1 01-JAN-03
4 FOUR ROW 19 01-JAN-04
5 FIVE ROW 19 01-JAN-05
6 SIX ROW 190 01-JAN-06
7 SERVEN ROW 01-JAN-07
8 EIGHT ROW 1 01-JAN-08
9 NINE ROW 19 01-JAN-09
9 rows selected.
16）导入指定文本数据到lob字段
- 初始化
CJ@db11g>desc t4 Name Null? Type ----------------------------------------- -------- ---------------------------- X NUMBER(38) Y VARCHAR2(30) Z DATE M CLOB
数据内容（每一个txt文件中都有文本数据）
1,one row,1901-01-01,note1.txt 2,two row,1902-01-01,note2.txt 3,three row,1903-01-01,note3.txt
- 控制文件
LOAD DATA INFILE 'test.csv' REPLACE INTO TABLE t4 FIELDS TERMINATED BY ',' TRAILING NULLCOLS ( x INTEGER EXTERNAL TERMINATED BY ",", y CHAR TERMINATED BY ",", z DATE "RRRR-MM-DD" TERMINATED BY ",", m_file_name FILLER CHAR TERMINATED BY ",", m LOBFILE(m_file_name) TERMINATED BY EOF )
- 验证结果
CJ@db11g>select *from t4;
X Y Z M
---------- ------------------------------ --------- --------------------------------------------------------------------------------
1 one row 01-JAN-01 . GOVERNMENT RIGHTS Programs, software, databases, and related documentation and
2 two row 01-JAN-02 . GOVERNMENT RIGHTS Programs, software, databases, and related documentation and
3 three row 01-JAN-03 . GOVERNMENT RIGHTS Programs, software, databases, and related documentation and
- 备注
- m_file_nam FILLER 这里指定数据文件中的“note1.txt“为文件名，filler使其过滤为空
- m字段的内容从m_file_name的文件中提取，这个由m LOBFILE(m_file_name) 来指定
17）向多表加载数据
- 初始化
CJ@db11g>desc emp Name Null? Type ----------------------------------------- -------- ---------------------------- EMPNO NUMBER(4) ENAME VARCHAR2(10) MGR NUMBER(4) DEPTNO NUMBER(2) CJ@db11g>desc proj Name Null? Type ----------------------------------------- -------- ---------------------------- EMPNO NOT NULL NUMBER PROJNO NOT NULL NUMBER
数据内容
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
- 控制文件
LOAD DATA INFILE 'test.csv' BADFILE 'test.bad' DISCARDFILE 'test.dsc' 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 WHEN projno != ' ' (empno POSITION(1:4) INTEGER EXTERNAL, projno POSITION(25:27) INTEGER EXTERNAL) INTO TABLE proj WHEN projno != ' ' (empno POSITION(1:4) INTEGER EXTERNAL, projno POSITION(29:31) INTEGER EXTERNAL) INTO TABLE proj WHEN projno != ' ' (empno POSITION(1:4) INTEGER EXTERNAL, projno POSITION(33:35) INTEGER EXTERNAL)
- 验证结果
CJ@db11g>select * from emp;
EMPNO ENAME MGR DEPTNO
---------- ---------- ---------- ----------
1234 BAKER 9999 10
1234 JOKER 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
10 rows selected.
CJ@db11g>select * from proj;
EMPNO PROJNO
---------- ----------
1234 101
1234 777
5321 321
2134 236
2414 236
6542 102
4532 40
1244 665
1234 102
1234 888
5321 55
EMPNO PROJNO
---------- ----------
2134 456
2414 456
6542 321
1244 133
1453 200
1234 103
1234 999
5321 40
2414 40
6542 14
1244 456
EMPNO PROJNO
---------- ----------
123 132
23 rows selected.
- 备注
- 这里使用了多个when对projno字段进行判断，即多条件判断。确保插入的正确性。
18）直接路径方式加载
- 初始化
CJ@db11g>desc emp Name Null? Type ----------------------------------------- -------- ---------------------------- EMPNO NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) CJ@db11g>create index ix_emp on emp(empno); Index created.
数据内容
7499 ALLEN SALESMAN 7698 1600.00 300.00 30 7566 JONES MANAGER 7839 3123.75 20 7654 MARTIN SALESMAN 7698 1312.50 1400.00 30 7658 CHAN ANALYST 7566 3450.00 20 7782 CLARK MANAGER 7839 2572.50 10 7839 KING PRESIDENT 5500.00 10 7934 MILLER CLERK 7782 920.00 10
- 控制文件
LOAD DATA INFILE 'test.csv' REPLACE INTO TABLE emp SORTED INDEXES (ix_emp) (empno POSITION(01:04) INTEGER EXTERNAL NULLIF empno=BLANKS, ename POSITION(06:15) CHAR, job POSITION(17:25) CHAR, mgr POSITION(27:30) INTEGER EXTERNAL NULLIF mgr=BLANKS, sal POSITION(32:39) DECIMAL EXTERNAL NULLIF sal=BLANKS, comm POSITION(41:48) DECIMAL EXTERNAL NULLIF comm=BLANKS, deptno POSITION(50:51) INTEGER EXTERNAL NULLIF deptno=BLANKS )
- 验证结果
CJ@db11g>select count(*) from emp;
COUNT(*)
----------
12
- 备注
SQL*Loader的两种操作模式：
- 传统路径：SQLLDR利用SQL加载数据。
- 直接路径：SQLLDR不适用SQL，直接格式数据块加载。
利用直接路径加载，能从一个平面文件读数据，并将其直接写至格式化的数据块，而绕过整个SQL引擎和undo生成，同时还可能避开redo生成，这种方式相对传统路径加载有很大的速度提升。
- 使用直接路径加载数据需要在命令行指定direct=true参数
- 上面可以看到表上已经有了一个索引，索引在直接加载过程中需要在加载完成后，排序新的键(索引列值)，并且将其与索引中已有的键合并在一起，这里指定SORTED INDEXES属性在sga中开辟一段小的内存空间用于维护这些临时的键值，从而实现快速加载。
19）导入分区表
- 初始化
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')),
partition ship_q2 values less than (TO_DATE('01-JUL-1996', 'DD-MON-YYYY')),
partition ship_q3 values less than (TO_DATE('01-OCT-1996', 'DD-MON-YYYY')),
partition ship_q4 values less than (TO_DATE('01-JAN-1997', 'DD-MON-YYYY'))
);
数据内容
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
- 控制文件
LOAD DATA INFILE 'test.csv' "fix 129" BADFILE 'test.bad' TRUNCATE INTO TABLE lineitem PARTITION (ship_q1) ( 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 )
- 验证结果
CJ@db11g>select * from lineitem;
L_ORDERKEY L_PARTKEY L_SUPPKEY L_LINENUMBER L_QUANTITY L_EXTENDEDPRICE L_DISCOUNT L_TAX L L L_SHIPDAT L_COMMITD L_RECEIPT L_SHIPINSTRUCT L_SHIPM L_COMMENT
---------- ---------- ---------- ------------ ---------- --------------- ---------- ---------- - - --------- --------- --------- ----------------- ------- -------------------------------------------
1 1519 7851 1 72 4386.6 7 4 N O 09-SEP-64 12-FEB-96 22-MAR-96 DELIVER IN PERSON TRUCK iPBw4mMm7w7kQ zNPL i261OPP
1 2731 732 2 36 58958.28 .09 .06 N O 12-FEB-96 28-FEB-96 20-APR-96 TAKE BACK RETURN MAIL 5wM04SNyl0AnghCP2nx lAi
1 3370 371 3 8 10210.96 .1 .02 N O 29-MAR-96 05-MAR-96 31-JAN-96 TAKE BACK RETURN REG AIR SQC2C 5PNCy4mM
- 备注
- 通过PARTITION关键字指定加载分区数据，其余和加载普通的数据没有什么不同。
- 这里的"fix 129"指定每列固定字符是129个，所以对于数据的格式有要求，我的就是直接复制的数据导致格式不对出现没有加载的行，对于要使用fix的地方还是建议使用od工具先检查一下。
本文版权由作者Beanbee和博客园共同所有，转载请注明出处。
来自【Beanbee的博客】:http://www.cnblogs.com/beanbee/
