sqlloader 直接路径和常规路径_SQL*Loader实验笔记【三】

所有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-012two row1902-01-013three row1903-01-014four row1904-01-015five row1905-01-016six row1906-01-017serven row1907-01-018eight row1908-01-019nine row1909-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.txt2,two row,1902-01-01,note2.txt3,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.

数据内容

7499ALLEN SALESMAN7698 1600.00 300.00 30

7566JONES MANAGER7839 3123.75 20

7654MARTIN SALESMAN7698 1312.50 1400.00 30

7658CHAN ANALYST7566 3450.00 20

7782CLARK MANAGER7839 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 INPERSONTRUCK iPBw4mMm7w7kQ zNPL i261OPP1 2731 73223658958.28.09.06NO12-FEB-9628-FEB-9620-APR-96TAKE BACK RETURNMAIL 5wM04SNyl0AnghCP2nx lAi1 3370 3713 810210.96 .1.02NO29-MAR-9605-MAR-9631-JAN-96TAKE BACK RETURNREG AIRSQC2C 5PNCy4mM1 5214 46542831197.88.09.06NO21-APR-9630-MAR-9616-MAY-96NONE AIR Om0L65CSAwSj5k6k1 6564 6763246897.92.07.02NO30-MAY-9607-FEB-9603-FEB-96DELIVER INPERSONMAIL CB0SnyOL PQ32B70wB75k 6Aw10m0wh1 7403 160524 31329.6 .1.04NO30-JUN-9614-MAR-9601 APR-96NONE FOB C2gOQj OB6RLk1BS15 igN2 8819 82012441659.44 0.08NO05-AUG-9609-FEB-9711-MAR-97COLLECT COD AIR O52M70MRgRNnmm476mNm3 9451 721230 41113.5.05.01AF05-SEP-9629-DEC-9318-FEB-94TAKE BACK RETURNFOB 6wQnO0Llg6y3 9717 1834440788.44.07.03RF09-NOV-9623-DEC-9315-FEB-94TAKE BACK RETURNSHIP LhiA7wygz0k4g4zRhMLBAM3 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工具先检查一下。