个人中心
文章发布
退出
作者热门文章
- ubuntu12.04环境下使用kvm ioctl接口实现最简单的虚拟机
- Ubuntu 通过无线网络安装Ubuntu Server启动系统后连接无线网络的方法
- 在Ubuntu上搭建网桥的方法
- ubuntu 虚拟机上网方式及相关配置详解
32
4
CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.
这篇CFSDN的博客文章Sql Loader怎么使用由作者收集整理，如果你对这篇文章有兴趣，记得点赞哟.
SQL*Loader（SQLLDR）是Oracle的高速批量数据加载工具。这是一个非常有用的工具，可用于多种平面文件格式向Oralce数据库中加载数据。今天看了申请了*loader的使用，自己小试了下，记录在这 。
1、假设要插入数据的表ftest，字段是（id,username,password,sj） 。
2、导入表的数据 以txt格式存储，名为data.txt 。
|
1
2
3
4
5
|
1 f f 2010-8-19
2 f1 f1 2010-8-19
3 f2 f2 2010-8-19
4 f3 f3 2010-8-19
5 f4 f4 2010-8-19
|
3、写控制文件，格式为ctl，命名为cont.ctl 内容如下:
|
1
2
3
4
5
|
load
data
infile
'c:\data.txt'
insert
into
table
ftest
fields terminated
by
" "
(id,username,
password
,sj)
|
注：如果表中没有数据就用insert，有数据就用append，删除旧数据插入新的数据用replace或truncate 。
4 在cmd命令窗口中执行 。
|
1
|
sqlldr fyzh/fyzh control=c:\cont.ctl data=c:\data.txt
|
5 在plsql中查看表ftest 。
查看已成功插入.
重新学习sqlldr 。
sqlldr导入数据的一个最简单例子:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
load
data
infile *
--告诉sqlldr要加载的数据就包含在控制文件本身
into
table
dept
--加载到哪个表
fields terminated
by
','
--数据加载形式应该是逗号分隔的值
(deptno,dname,loc)
--所要加载的列
begindata
--告诉sqlldr后面的行市要加载到dept表的数据
10,Sales,Virginia
20,Accounting,Virginia
30,Consulting,Virginia
40,Finance,Virginia
create
table
dept
(deptno number(2)
constraint
dept_pk
primary
key
,
dname varchar2(14),
loc varchar2(13)
)
sqlldr userid=gwm/gwm@fgisdb control=c:\demol.ctl
select
*
from
dept;
1 10 Sales Virginia
2 20 Accounting Virginia
3 30 Consulting Virginia
4 40 Finance Virginia
|
sqlldr导入的四种加载方式:
APPEND ：原先的表有数据 就加在后面 INSERT：装载空表 如果原先的表有数据 sqlloader会停止 默认值 REPLACE ：原先的表有数据 原先的数据会全部删除 TRUNCATE ：指定的内容和replace的相同 会用truncate语句删除现存数据 。
用SQLLDR加载数据的FAQ 。
1、如何加载定界数据 。
1)定界数据即用某个特殊字符分隔的数据，可能用引号括起，这是当前平面文件最常见的数据格式。 对于定界数据，最常用的格式是逗号分隔值格式。采用这种文件格式，数据中的每个字段与下一个字段用一个逗号分隔。文本串可以用引号括起，这样就串本身包含逗号。如果串还必须包含引号，一般约定是使用两个引号。加载定界数据，相应的典型控制文件与前面例子相似，但是fields terminated by子句通常如下指定:
|
1
|
fields terminated
by
','
optionally enclose
by
'"'
|
它指定用逗号分隔数据字段，每个字段可以用双引号括起。如果把这个控制文件的最后部分修改如下
|
1
2
3
4
5
6
7
8
9
10
11
12
|
fields terminated
by
','
optionally enclosed
by
'"'
(deptno,dname,loc)
begindata
10,Sales,"Virginia,USA
"
20,Accounting,"
Va,
""
USA
""
"
30,Consulting,Virginia
40,Finance,Virginia
select * from dept
1 10 Sales Virginia,USA
2 20 Accounting Va,"
USA"
3 30 Consulting Virginia
4 40 Finance Virginia
|
2)另一种常用的格式是制表符定界数据。有两种方法使用terminated by子句来加载这种数据:
terminated by X'09' --使用十六进制格式的制表符；若用ASCII，制表符应该是9 。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
terminated
by
whitespace
--使用terminated by whitespace
load
data
infile *
into
table
dept
replace
fields terminated
by
whitespace
(deptno,dname,loc)
begindata
10 Sales Virginia
select
*
from
dept;
1 10 Sales Virginia
--使用terminated by X'09'
load
data
infile *
into
table
dept
replace
fields terminated
by
X
'09'
(deptno,dname,loc)
begindata
10 Sales Virginia
select
*
from
dept;
1 10
|
Sales --因为一旦遇到一个制表符就会输出一个值.
因此，将10赋给deptno，dname得到了null，因为在第一个制表符和第二个制表符之间没有数据 。
3）sqlldr的filler关键字使用 。
如跳过制表符 。
|
1
2
3
4
5
6
7
8
9
10
|
load
data
infile *
into
table
dept
replace
fields terminated
by
X
'09'
(deptno,dummy1 filler,dname,dummy2 filler,loc)
begindata
10 Sales Virginia
select
*
from
dept;
1 10 Sales Virginia
|
2、如何加载固定格式数据 。
要加载定宽的固定位置数据，将会在控制文件中使用position关键字.
|
1
2
3
4
5
6
7
8
9
10
11
12
|
load
data
infile *
into
table
dept
replace
(deptno position(1:2),
dname position(3:16),
loc position(17:29)
)
begindata
10Accounting Virginia,USA
select
*
from
dept;
1 10 Accounting Virginia,USA
|
这个控制文件没有使用terminated by子句；而是使用了position来告诉sqlldr 字段从哪里开始，到哪里结束。 对于position，我们可以使用重叠的位置，可以在记录中来回反复。如下修改dept表:
|
1
|
alter
table
dept
add
entire_line
varchar
(29);
|
。
并使用如下控制文件:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
load
data
infile *
into
table
dept
replace
(deptno position(1:2),
dname position(3:16),
loc position(17:29),
entire_line position(1:29)
)
begindata
10Accounting Virginia,USA
select
*
from
dept;
1 10 Accounting Virginia,USA 10Accounting
Virginia,USA
|
使用position时，可以使用相对偏移量，也可以使用绝对偏移量。前面的例子使用了绝对偏移量，明确指定字段从哪开始，从哪结束，也可以将 前面的控制文件改写如下:
|
1
2
3
4
5
6
7
8
9
10
11
12
|
load
data
infile *
into
table
dept
replace
(deptno position(1:2),
dname position(*:16),
loc position(*:29),
entire_line position(1:29)
)
begindata
10Accounting
Virginia,USA
|
*指示控制文件得出上一个字段在哪里结束。因此，在这种情况下，(*:16)与(3:16)是一样的。注意，控制文件可以混合使用相对位置和绝对位置。 另外，使用*表示法时，可以把它与偏移量相加。例如dname从deptno结束之后的；两个字符开始，可以使用（*+2:16），即相当于（5:16）. 。
position子句中的结束位置必须是数据结束的绝对列位置。有时，可能指定每个字段的长度更为容易，特别是如果这些字段是连续的。采用这种 。
方式，只需告诉sqlldr：记录从第一个字节开始，然后指定每个字段的长度。如下:
|
1
2
3
4
5
6
7
8
9
10
11
12
|
load
data
infile *
into
table
dept
replace
(deptno position(1)
char
(2),
dname position(*)
char
(14),
loc position(*)
char
(13),
entire_line position(1)
char
(29)
)
begindata
10Accounting Virginia,USA
select
*
from
dept;
|
3、如何加载日期 。
使用sqlldr加载日期只需在控制文件中date数据类型，并指定要使用的日期掩码。这个日期掩码与数据库中to_char和to_date中使用的日期掩码一样.
如修改dept表如下:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
alter
table
dept
add
last_updated
date
;
load
data
infile *
into
table
dept
replace
fields terminated
by
','
(deptno,
dname,
loc,
last_updated
date
'dd/mm/yyyy'
)
begindata
10,Accounting,Virginia,1/5/2000
select
*
from
dept;
1 10
Accounting
Virginia
2000-5-1
|
4、如何使用函数加载数据 。
如果想确保加载的数据是大写的，可以改写控制文件如下:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
load
data
infile *
into
table
dept
replace
fields terminated
by
','
(deptno,
dname
"upper(:dname)"
,
loc
"upper(:loc)"
,
last_updated
date
'dd/mm/yyyy'
)
begindata
10,Accounting,Virginia,1/5/2000
select
*
from
dept;
1 10
ACCOUNTING
VIRGINIA
2000-5-1
|
如下控制文件加载数据无法导入 。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
load
data
infile *
into
table
dept
replace
fields terminated
by
','
(deptno,
dname
"upper(:dname)"
,
loc
"upper(:loc)"
,
last_updated
date
'dd/mm/yyyy'
,
entire_line
":deptno||:dname||:loc||:last_updated"
)
begindata
10,Accounting,Virginia,1/5/2000
|
1）TRAILING NULLCOLS的使用：一般默认用的好 。
解决方法，就是使用TRAILING NULLCOLS。这样，如果输入记录中不存在某一列的数据，sqlldr就会为该列绑定一个null值.
这种情况下，增加TRAILING NULLCOLS会导致绑定变量:entire_line成为null.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
load
data
infile *
into
table
dept
replace
fields terminated
by
','
TRAILING NULLCOLS
(deptno,
dname
"upper(:dname)"
,
loc
"upper(:loc)"
,
last_updated
date
'dd/mm/yyyy'
,
entire_line
":deptno||:dname||:loc||:last_updated"
)
begindata
10,Accounting,Virginia,1/5/2000
select
*
from
dept;
1 10 ACCOUNTING VIRGINIA 10AccountingVirginia1/5/2000 2000-5-1
|
2）case在sqlldr中的使用 。
假设输入文件中有以下格式的日期： HH24:MI:SS:只有一个时间；日期时间默认为sysdate DD/MM/YYYY:只有一个日期，时间默认为午夜0点 HH24:MI:SS DD/MM/YYYY:日期时间都显式提供 。
可用如下的控制文件 。
|
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
|
load
data
infile *
into
table
dept
replace
fields terminated
by
','
TRAILING NULLCOLS
(deptno,
dname
"upper(:dname)"
,
loc
"upper(:loc)"
,
last_updated
"case
when length(:last_updated)>9
then to_date(:last_updated,'hh24:mi:ss dd/mm/yyyy')
when instr(:last_updated,':')>0
then to_date(:last_updated,'hh24:mi:ss')
else to_date(:last_updated,'dd/mm/yyyy')
end"
)
begindata
10,Sales,Virginia,12:03:03 17/10/2005
20,Accounting,Virginia,02:23:54
30,Consulting,Virginia,01:24:00 21/10/2006
40,Finance,Virginia,17/8/2005
alter
session
set
nls_date_format=
'dd-mon-yyyy hh24:mi:ss'
;
select
*
from
dept;
|
5、如何加载有内嵌换行符的数据 。
1）用非换行符的其它字符来表示换行符，并在加载时使用一个sql函数用一个CHR(10)替换该文本.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
alter
table
dept
add
comments varchar2(4000);
--使用下列来加载文本
load
data
infile *
into
table
dept
replace
fields terminated
by
','
trailing nullcols
(deptno,
dname
"upper(:dname)"
,
loc
"upper(:loc)"
,
comments
"replace(:comments,'\\n',chr(10))"
--'\\n'换行符用chr(10)这个代替
)
begindata
10,Sales,Virginia,this
is
the sales\noffice
in
Virginia
|
注：调用中必须用\\n来表示替换符，而不是\n 。
2）在infile指令上使用FIX属性，加载一个定长平面文件。 使用该方法，输入数据必须出现在定长记录中。对于固定位置的数据，使用FIX属性就特别合适，这些文件一般为定长文件。 另外使用该方法时，数据必须在外部存储，不能存储在控制文件本身.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
--控制文件
load
data
infile demo.dat
"fix 80"
--指定了输入数据文件demo.dat，这个文件中每个记录80字节
into
table
dept
replace
fields terminated
by
','
trailing nullcols
(deptno,
dname
"upper(:dname)"
,
loc
"upper(:loc)"
,
comments
)
--数据文件
10,Sales,Virginia,this
is
the sales\noffice
in
Virginia
20,，,Sales,Virginia,this
is
the sales\noffice
in
Virginia
|
。
注:
在unix上，行结束标记是\n即CHR(10),而windows nt平台的行结束标记是\r\n即CHR(13)||CHR(10)； 可以在控制文件中使用trim内置sql函数来完成截断尾部的空白符 。
|
1
|
select
*
from
dept;
|
3)在infile指令在、上使用VAR属性，加载一个变宽文件，在该文件使用的格式中，每一行前几个字节指定了这一行的长度 。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
--控制文件
load
data
infile demo.dat
"var 3"
--表明了前三个字节用于记录每一行的字节数
into
table
dept
replace
fields terminated
by
','
trailing nullcols
(deptno,
dname
"upper(:dname)"
,
loc
"upper(:loc)"
,
comments
)
--数据文件
05410,Sales,Virginia,this
is
the sales office
in
Virginia
|
注：在unix上换行符只算一个字节，在windows nt上算两个字节 。
|
1
|
select
*
from
dept;
|
4）在infile指令上使用STR属性，加载一个变宽文件，其中用某个字符序列来表示行结束符，而不是用换行符表示 STR属性以十六进制指定，要得到十六进制串，最容易的办法就是使用sql和utl_raw来生成十六进制串。如在unix平台，行结束标记是CHR(10)，我们的特殊字符是一个管道符号（|），则可以写成:
|
1
|
select
utl_raw.cast_to_raw(
'|'
||chr(10))
from
dual;
--可见在unix上为x'7C0A'
|
在windows上用 。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
select
utl_raw.cast_to_raw(
'|'
||chr(13)||chr(10))
from
dual;
--为x'7C0D0A'
--控制文件
load
data
infile demo.dat
"str x'7C0D0A'"
into
table
dept
replace
fields terminated
by
','
trailing nullcols
(deptno,
dname
"upper(:dname)"
,
loc
"upper(:loc)"
,
comments
)
--数据文件
10,Sales,Virginia,this
is
the sales
office
in
Virginia|
select
*
from
dept;
|
6、加载lob数据 。
1）加载内联的lob数据。这些lob数据通常内嵌有换行符和其他特殊字符 。
|
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
|
--修改表dept
truncate table dept;
alter table dept drop column comments;
alter table dept add comments clob;
--数据文件
10,Sales,Virginia,
this
is the sales
office in Virginia|
20,Accounting,Virginia,
this
is the Accounting
office in Virginia|
30,Consuling,Virginia,
this
is the Consuling
office in Virginia|
40,Finance,Virginia,"
this
is the Finance
office in Virginia,it has embedded commas and is
much longer than the other comments filed.If you
feel the need to add
double
quotes text in here like
this
:
""
you will need to
double
up those quotes!
""
to
preserve them in the string. This field keeps going
for
up to
1000000 bytes (because of the control file definition I used)
or until we hit the magic and of record marker,
the | followed by an end of line - it is right here ->"|
--控制文件
load data
infile demo.dat
"str x'7C0D0A'"
into table dept
replace
fields terminated by
','
optionally enclosed by
'"'
trailing nullcols
(deptno,
dname
"upper(:dname)"
,
loc
"upper(:loc)"
,
comments
char
(1000000) --sqlldr默认输入的字段都是
char
(255)。
char
(1000000)表示允许输入多达1000000个字符
)
select * from dept;
|
2)加载外联的lob数据.
需要把包含有一些文件名的数据文件加载在lob中，而不是让lob数据与结构化数据混在一起。这样就不必使用上述的4种方法之一来避开输入数据中 的内嵌换行符问题，而这种情况在大量的文本或二进制数据中频繁出现。sqlldr称这种额外的数据文件为lobfile。 sqlldr还可以支持加载结构化数据文件。可以告诉sqlldr如何从另外一个文件解析lob数据，这样就可以加载其中的一部分作为结构化数据中的每一行。 sqlldr称这种外部引用的文件为复杂二级数据文件.
lobfile数据采用以下某种格式
定长字段（从lobfile加载字节100到10000）； 定界字段（以某个字符结束，或用某个字符括起）；--最常见，以一个文件结束符（EOF）结束 长度/值对，这是一个边长字段 。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
--加载数据的表
create
table
lob_demo
(owner varchar2(255),
time_stamp
date
,
filename varchar2(255),
data blob)
--假设有一目录，其中包含想要加载到数据库中的文件。以下为想要加载文件的owner，time_stamp，文件名及文件本身
load
data
infile *
replace
into
table
lob_demo
(owner position(17:25),
time_stamp position(44:55)
date
"Mon DD HH24:MI"
,
filename position(57:100),
data lobfile(filename) terminated
by
EOF
)
begindata
-rw-r
--r-- 1 tkyte tkyte 1220342 jun 17 15:26 classes12.zip
select
owner,time_stamp,filename,dbms_lob.getlength(data)
from
lob_demo;
|
3）将lob数据加载到对象列 。
一般用于加载图像 。
|
1
2
3
4
|
create
table
image_load(
id number,
name
varchar2(255),
image ordsys.ordimage)
--首先要了解ordsys.ordimage类型
|
加载这种数据的控制文件如下所示:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
load
data
infile *
into
table
image_load
replace
fields terminated
by
','
(id,
name
,
file_name filler,
image
column
object
(
source
column
object
(
localdata lobfile(file_name) terminated
by
EOF
nullif
file_name=
'none'
)
)
)
begindata
1,icons,icons.gif
|
注：column object告诉sqlldr这不是一个列名，而是列名的一部分.
使用的列名是image.source.localdata 。
|
1
|
select
*
from
image_load
|
|
1
2
3
4
5
6
|
--继续编辑加载进来数据的属性
begin
for
c
in
(
select
*
from
image_load) loop
c.image.setproperties;
--setproperties是ordsys.ordimage类型提供的方法，处理图像本身，并用适当的值更新对象的其余属性
end
loop;
end
;
|
额外介绍:
使用plsql加载lob数据 。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
create
table
demo (id
int
primary
key
,theclob clob)
create
or
replace
directory dir1
as
'D:\oracle'
;
SQL> host echo
'hello world!'
>d:/oracle/test.txt
declare
l_clob clob;
l_bfile bfile;
begin
insert
into
demo
values
(1, empty_clob()) returning theclob
into
l_clob;
l_bfile := bfilename(
'DIR1'
,
'test.txt'
);
dbms_lob.fileopen(l_bfile);
dbms_lob.loadfromfile(l_clob, l_bfile, dbms_lob.getlength(l_bfile));
dbms_lob.fileclose(l_bfile);
end
;
select
dbms_lob.getlength(theclob),theclob
from
demo;
|
注:
创建的目录默认为大写DIR1，如果目录写成dir1就会提示错误，如果要想使用混有大小写的目录名，在创建这样的目录时应该带引号的标识符，如下所示:
|
1
|
create
or
replace
directory
"dir2"
as
'D:\oracle'
;
|
以上内容是小编给大家分享的关于SqlLoader怎么使用的相关资料，希望大家喜欢.
最后此篇关于Sql Loader怎么使用的文章就讲到这里了,如果你想了解更多关于Sql Loader怎么使用的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！ 。
32
4
0
SQL、PL-SQL 和 T-SQL 之间有什么区别？ 谁能解释一下这三者之间的区别，并提供每一个的相关使用场景？ 最佳答案 SQL 是一种对集合进行操作的查询语言。 它或多或少是标准化的，几乎所有关
这个问题已经有答案了: What is the difference between SQL, PL-SQL and T-SQL? (6 个回答) 已关闭 9 年前。 我对 SQL 的了解足以完成我的
我在数据库中有一个 USER 表。该表有一个 RegistrationDate 列，该列有一个默认约束为 GETDATE()。 使用 LINQ 时，我没有为 RegistrationDate 列提供任
我有一个可能属于以下类型的字符串 string expected result 15-th-rp 15 15/12-rp 12 15-12-th
很难说出这里问的是什么。这个问题模棱两可、含糊不清、不完整、过于宽泛或言辞激烈，无法以目前的形式合理回答。如需帮助澄清此问题以便可以重新打开，visit the help center . 9年前关闭
我有一个存储过程(称为 sprocGetArticles)，它从文章表中返回文章列表。这个存储过程没有任何参数。 用户可以对每篇文章发表评论，我将这些评论存储在由文章 ID 链接的评论表中。 有什么方
我目前正在做一个 *cough*Oracle*cough* 数据库主题。讲师介绍embedded SQL作为让其他语言(例如 C、C++)与(Oracle)数据库交互的方式。 我自己做了一些数据库工作
SQL Server 中 SQL 语句的最大长度是多少？这个长度是否取决于 SQL Server 的版本？ 例如，在 DECLARE @SQLStatement NVARCHAR(MAX) = N'S
这个问题已经有答案了: Simple way to transpose columns and rows in SQL? (9 个回答) 已关闭 8 年前。 CallType
预先感谢您对此提供的任何帮助。 假设我有一个查询，可以比较跨年的数据，从某个任意年份开始，永无止境(进入 future )，每年同一时期直到最后一个完整的月份(其特点是一月数据永远不会显示至 2 月
我在数据库中有一个 USER 表。该表有一个 RegistrationDate 列，该列的默认约束为 GETDATE()。 使用 LINQ 时，我没有为 RegistrationDate 列提供任何数
下面是我试图用来检查存储过程是否不存在然后创建过程的 sql。它会抛出一个错误:Incorrect syntax near the keyword 'PROCEDURE' IF NOT EXISTS
我有一个同事声称动态 SQL 在许多情况下比静态 SQL 执行得更快，所以我经常看到 DSQL 到处都是。除了明显的缺点，比如在运行之前无法检测到错误并且更难阅读，这是否准确？当我问他为什么一直使用
来自 lobodava 的动态 SQL 查询是: declare @sql nvarchar(4000) = N';with cteColumnts (ORDINAL_POSITION, CO
使用 SQL Server 中的存储过程执行动态 SQL 命令的现实优点和缺点是什么 EXEC (@SQL) 对比 EXEC SP_EXECUTESQL @SQL ？ 最佳答案 sp_executes
我有这个有效的 SQL 查询: select sum(dbos.Points) as Points, dboseasons.Year from dbo.StatLines dbos i
我正在调试一些构建成功运行的 SQL 命令的代码。 然而，在查询结束时，查询结果似乎被写入了一个文本文件。 完整的查询如下 echo SELECT DATE,DATETABLE,DATE,APPDAT
我有一些创建表的 .sql 文件(MS SQL 数据库): 表_1.sql: IF OBJECT_ID (N'my_schema.table1', N'U') IS NOT NULL DROP TAB
我写了下面的 SQL 存储过程，它一直给我错误@pid = SELECT MAX(... 整个过程是: Alter PROCEDURE insert_partyco @pname varchar(20
我在 SQL Server 2005 中有包含两列 Fruit 和 Color 的表，如下所示 Fruit Colour Apple Red Orange
我是一名优秀的程序员,十分优秀！