【DB笔试面试446】如何将文本文件或Excel中的数据导入数据库？

参数

含义解释

userid

Oracle用户名/口令

control

控制文件名

log

记录的日志文件名

bad

错误文件名，记录错误的未加载数据

data

数据文件名，data参数只能指定一个数据文件。如果控制文件通过infile参数指定了数据文件，并且指定多个，那么在执行sqlldr命令时，先加载data参数指定的数据文件，控制文件中第一个infile指定的数据文件被忽略，但后续的infile指定的数据文件继续有效

rows

每次提交的记录数，默认情况下，常规路径为64

direct

使用直通路径方式导入，不使用buffer cache。通过direct path api发送数据到服务器端的加载引擎，加载引擎按照数据块的格式处理数据并直接写入数据文件，因此效率较高。该参数默认为FALSE。注意：含序列时不能设置direct=true

parfile

参数文件：包含参数说明的文件的名称

parallel

执行并行加载（默认为FALSE）

bindsize

常规路径绑定数组的大小（以字节计，默认为256000）

discard

废弃文件名，默认情况不产生

discardmax

允许废弃的文件的数目

skip

要跳过的逻辑记录的数目（默认为0），如：skip=3，表示数据文件的前三行不导入库

load

要加载的逻辑记录的数目如：load=5，表示要加载5条记录

errors

允许的错误的数目（默认为50）

silent

运行过程中隐藏消息

file

要从以下对象中分配区的文件

skip_unusable_indexes

不允许/允许使用无用的索引或索引分区（默认为FALSE）

skip_index_maintenance

没有维护索引，将受到影响的索引标记为无用（默认为FALSE）

commit_discontinued

提交加载中断时已加载的行（默认为FALSE），该参数默认为FALSE，表示当load被异外中止后，已load的数据是不是自动提交

readsize

读取缓冲区的大小（默认为1048576，单位为字节，即1M）

external_table

使用外部表进行加载

columnarrayrows

直接路径列数组的行数（默认为5000）

streamsize

直接路径流缓冲区的大小（以字节计，默认为256000）

multithreading

在直接路径中使用多线程

resumable

启用或禁用当前的可恢复会话（默认为FALSE）

resumable_name

有助于标识可恢复语句的文本字符串

resumable_timeout

RESUMABLE的等待时间（以秒计，默认7200）

date_cache

日期转换高速缓存的大小（以条目计，默认为1000）

序号

问题描述

数据举例

控制文件写法举例

解决方法

1

数据文件里的数据是定长，没有分隔符

SMITH   CLEAK      3904

ALLEN   SALESMAN   2891

WARD    SALESMAN   3128

KING    PRESIDENT  2523

TRUNCATE INTO TABLE BONUS

(

ENAME position(1:5),

JOB position(7:15),

SAL position(17:20)

)

position关键字用来指定列的开始和结束位置，如JOB position(7:15)是指从第7个字符开始截止到第15个字符作为JOB列的列值。position的写法也很灵活，要实现上述功能还可以换成下列几种形式：

● position(*+2:15)：直接指定数值的方式叫作绝对偏移量，如果使用*号，专业名词叫相对偏移量，表示上一个字段从哪里结束，这次就从哪里开始。相对偏移量也可以再做运算，比如position(*+2:15)就表示从上次结束的位置+2的地方开始。

● position(*) char(9)：这种相对偏移量+类型和长度的优势在于，只需要为第一列指定开始位置，其他列只需要指定列长度就可以了，实际使用中比较省事

2

数据文件中的列比要导入的表的列少，且空列又必须赋值

SMITH   CLEAK      3904

ALLEN   SALESMAN   2891

WARD    SALESMAN   3128

KING    PRESIDENT  2523

TRUNCATE INTO TABLE BONUS

(

ENAME position(1:5),

JOB position(7:15),

SAL position(17:20),

comm '0'

)

COMM "SUBSTR(:SAL,1,1)" 这里的COMM列可以取SAL值的第一列，并赋值给COMM列，当然也可以用PL/SQL编写自定义的函数来赋值

3

要加载的数据中包含分隔符

SMITH,CLEAK,3904

ALLEN,"SALER,M",2891

WARD,"SALER,""S""",3128

KING,PRESIDENT,2523

TRUNCATE INTO TABLE BONUS

FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY  '"'

(ENAME,JOB,SAL)

OPTIONALLY  ENCLOSED  BY参数指明定界符为双引号。CSV格式文件默认定界符就是双引号，可以根据实际情况修改OPTIONALLY的参数值

4

数据文件中的列比要导入的表中列多

SMITH   CLEAK      3904

ALLEN   SALESMAN   2891

WARD    SALESMAN   3128

KING    PRESIDENT  2523

truncate table bonus

fields terminated by ","

(ename,job,sal,fcol filler)

1、将数据文件中多的列删除

2、在控制文件中采用filler关键字过滤从而不录入这列数据

5

多个数据文件导入同一张表

infile kkk.dat

infile kkk2.dat

infile kkk3.dat

load data

infile kkk.dat

infile kkk2.dat

infile kkk3.dat

truncate table bonus

fields terminated by ","

(ename , job ,sal )

多个数据文件导入同一张表，条件就是这些数据文件的格式要相同，在控制文件中可以写多个文件

6

同一个数据文件要导入不同的表

bon  smith

bon  allen

mgr  king

mgr  smm

load data

infile kkk.dat

discardfile ldr_case9.dsc

truncate

into table B when tab='bon'

(tab filler position(1:3),ename position(5:9))

into table M when tab='mgr'

(tab filler position(1:3),ename position(5:9))

需求是将以MGR开头的记录导入M表，以BON开头的记录导入B表，其他记录存入废弃文件中。这种情况下，可以使用WHEN关键字。

7

数据文件前n行不导入

sqlddr scott/scott control=ldr_case1.ctl skip=3 load=6

skip=3 load=6 表示前三行不导入，导入接下来的6行，即导入第4～9行

8

要加载的数据中有换行或回车符

10,aaaab

Office in Virginia

COMMENTS "REPLACE(:COMMENTS,CHR(10),'')"

用REPLACE函数将换行和回车符替换成空值。CHR(10)表示换行，CHR(13)表示回车

9

要加载的数据中含有\n符号

10,Sales \n Office in

COMMENTS "REPLACE(:COMMENTS,'\\n','')"

将\n替换成空值

10

nullif导入

1  10

20 lg

REPLACE

(DEPTNO position(1:2) integer external nullif DEPTNO='1',

DNAME position(3:8)

)

nullif DEPTNO='1'表示当导入deptno的值为'1'时，则该条记录不导入

11

某些字段有空值

trailing nullcols

trailing nullcols表示表的字段没有对应的值时允许为空

12

导入数据时需要修改数据、加入默认值

(

phonenumber ,

addtime sysdate,  --这里是默认值

remark "suit"--这里是默认值

)

在列的后边直接列出

13

载入每行的行号

SEQNO RECNUM

TEXT POSITION(1:1024))  

载入每行的行号用RECNUM

14

如何导入日期型数据

MODIFYDATE date(18) 'YYYY/MM/DD HH24:MI:SS'

在ctl文件中，字段的后面加入DATE 'yyyy-mm-dd HH24:MI:SS'即可

15

如何加载序列

SEQNUM  SEQUENCE(1,1)

SEQUENCE的算法有3种装载方法，这样数据文件中可以不用第一列

1、(1,1)，第一个1，此方法表示从1开始，第二个1代表步伐。

2、对于第一个1，还可以被更换为COUNT，计算表中的记录数后，加1开始算SEQUENCE

3、还有MAX，取表中该字段的最大值后加1开始算SEQUENCE

16

将数据文件中的数据当做表中的一列进行加载

trailing nullcols

(

id SEQUENCE(1,1),

text  char(4000) "TRIM(:text)"

)

数据文件不用分列，所有的数据均导入数据库中的表中一列

17

如何限制错误数量

在控制文件头加上OPTIONS (ERRORS=50)，表示最多允许出现50条错误

18

合并多行记录为一行记录

10,Sales,

Virginia,

1/5/2000

CONCATENATE 3 INTO TABLE DEPT

TRUNCATE

(COL1,COL2...)

其实这3行看成一行 10,Sales,Virginia,1/5/2000。通过关键字CONCATENATE可以把几行的记录看成一行记录。

序号

报错

原因

解决

1

没有第二个定界字符串

csv文件中含有多个换行符

如果csv是单个换行符的话，那么加入OPTIONALLY ENCLOSED BY  '"' 即可，若是有多个，则可以用微软的Excel打开文件，替换掉Excel中的强制换行符

2

ORA-26002: 表 RISK.TLHR上有定义的索引

SQL*Loader命令中含有PARALLEL这个关键字

当被导入的表中含有索引的时候，这个时候需要去掉PARALLEL这个关键字就可以了

3

SQL*Loader-510:  数据文件 (E:\lhr\sql\sqlldr\1000W-1200W.csv) 中物理记录超过最大限制 (4194304)

readsize设置过小

修改readsize为较大的值，比如设置成4M，默认为1048576，单位为字节，即1M

4

Record 1: Rejected - Error on table SQM.SQM_LHR_USER, column USER_NUM.

ORA-01722: invalid number

因为换行符的问题，如果INTEGER或者NUMBER类型的栏位位于表的最后，那么在Windows下加载数据的时候，最后其实会有CR/LF的换行符，在用sqlldr导入时会把换行符也算作那个数字的一部分，从而出错

加integer或者加“TERMINATED BY WHITESPACE”

5

导入数据库出现乱码

数据库字符集：zhs16gbk

客户端：zhs16gbk

数据文件：al32utf8

在控制文件中加入文件的编码格式CHARACTERSET  utf8

6

数据文件的字段超出最大长度

没有在每个字段后面定义数据类型，碰到此种情况，Oracle默认为该字段为VARCHAR(255)。也有可能定义了数据类型，但是数据长度的确超出4000字节长度

控制文件中对应的列后边加上CHAR(4000)

7

SQL*Loader-566

最后一行数据分隔符号后面没有回车

定义行结尾符

8

“ORA-12899: 列的值太大”错误

从文本中读取的字段值超过了数据库表字段的长度

用函数截取，如“ab CHAR(4000) "SUBSTRB(:ab,1,2000)",”

9

ORA-01461: 仅能绑定要插入LONG列的LONG值

字符类型在PL/SQL中作为变量存在，最大可支持32767个字节，但在SQL中通常只能够支持到4000字节(NCHAR为2000)，因此如果声明的变量长度超出了SQL中类型长度，并且变量实际值也超出类型可接受最大值时，就会触发ORA-01461错误

当数据文件中的字段值真实长度超过4000长度时，只有一个方法：将数据表字段类型改为CLOB类型或者新增加一个临时CLOB字段，sqlldr中的“CHAR(32767)”对于CLOB字段有效。导入后再通过SQL语句更新到真实字段中

10

Illegal combination of non-alphanumeric characters

非法非字母数字字符的组合

环境变量NLS_LANG的设置和文件字符集保持一致

11

ORA-26006-Incorrect bind variable in column

SQL*Loader版本问题

使用和数据库版本一致的SQL*Loader

12

SQL*Loader-2026: 加载因 SQL 加载程序无法继续而被终止

数据文件格式问题，行数据后没有回车，或行结尾符不确定

修改控制文件的格式或数据文件的内容