SQL*Loader

lfjigu

SQL*Loader Features

特性：

SQL*Loader loads data from external files into tables of an Oracle database. It has a
powerful data parsing engine that puts little limitation on the format of the data in the
datafile. You can use SQL*Loader to do the following:
■ Load data across a network. This means that you can run the SQL*Loader client on
a different system from the one that is running the SQL*Loader server.
■ Load data from multiple datafiles during the same load session.
■ Load data into multiple tables during the same load session.
■ Specify the character set of the data.
■ Selectively load data (you can load records based on the records' values).
■ Manipulate the data before loading it, using SQL functions.
■ Generate unique sequential key values in specified columns.
■ Use the operating system's file system to access the datafiles.

Figure 6–1 SQL*Loader Overview
SQL*Loader Parameters
SQL*Loader is invoked when you specify the sqlldr command and, optionally,
parameters that establish session characteristics.
In situations where you always use the same parameters for which the values seldom
change, it can be more efficient to specify parameters using the following methods,
rather than on the command line:
■ Parameters can be grouped together in a parameter file. You could then specify the
name of the parameter file on the command line using the PARFILE parameter.
■ Certain parameters can also be specified within the SQL*Loader control file by
using the OPTIONS clause.
Parameters specified on the command line override any parameter values specified in
a parameter file or OPTIONS clause.

参数：

SQL*Loader Parameters
SQL*Loader is invoked when you specify the sqlldr command and, optionally,
parameters that establish session characteristics.
In situations where you always use the same parameters for which the values seldom
change, it can be more efficient to specify parameters using the following methods,
rather than on the command line:
■ Parameters can be grouped together in a parameter file. You could then specify the
name of the parameter file on the command line using the PARFILE parameter.

■ Certain parameters can also be specified within the SQL*Loader control file by

using the OPTIONS clause.
Parameters specified on the command line override any parameter values specified in
a parameter file or OPTIONS clause

我们使用sqlldr来调用SQL*Loader，参数可以写在命令之后也可以写成参数文件的形式进行调用

控制文件：

SQL*Loader Control File
The control file is a text file written in a language that SQL*Loader understands. The
control file tells SQL*Loader where to find the data, how to parse and interpret the
data, where to insert the data, and more.

Although not precisely defined, a control file can be said to have three sections.
The first section contains sessio n-wide information, for example:
■ Global options such as bindsize, rows, records to skip, and so on
■ INFILE clauses to specify where the input data is located
■ Data to be loaded

参数文件中大致包含三部分：

1.全局项：绑定大小，行数，要跳过处理的记录数等

2.INFILE子句指定了数据文件的路径

3.要加载的数据

The keywords CONSTANT and ZONE have special meaning to SQL*Loader and are
therefore reserved

关键字CONSTANT和ZONE在SQL*Loader中是保留字，不建议在要导入的表中使用（如字段，表名等不要用这两个保留字）。

下面来举个例子：

先编写控制文件：

load data
infile 'example.dat' "fix 11"
into table example
fields terminated by ',' optionally enclosed by '"'
(col1, col2)

example.dat:
001, cd, 0002,fghi,
00003,lmn,
1, "pqrs",
0005,uvwx,


上面的是控制文件的一种格式：Fixed Record Format，这里规定了每条记录的固定长度

示例2：

load data
infile 'example.dat' "var 3"
into table example
fields terminated by ',' optionally enclosed by '"'
(col1 char(5),
col2 char(7))
example.dat:
009hello,cd,010world,im,
012my,name is,


这是控制文件的另一种格式：Variable Record Format，这里规定了字段的最大长度