SQL*Loader之CASE1

浏览数：23 / 时间：2015年06月12日

最近项目涉及到将文本文件中的数据导入到Oracle数据库中，故研究了下SQL*Loader，官档提供的资料不是很丰富，很多案例中出现的语句在官档中找不到出处。但它提供的案例本身却彰显出了SQL*Loader功能的强大。鉴于Oracle 11g的软件本身没有携带这些案例，需要专门到官方网站下载Oracle Database 11g Release 2 Examples，甚是麻烦。在此，将这些案例分享，也方便以后研究、借鉴。

因官方文档还没有研究完，手里还有本《Oracle SQL*Loader: The Definitive Guide》。故案例先行，理论在后。

这11个案例下载地址：http://pan.baidu.com/s/1o6Hl57G

一共有11个案例，案例基本上包括三部分，控制文件、SQL脚本、数据文件。有的没有数据文件，直接在控制文件中提供。

CASE1

1. SQL脚本

[oracle@node3 ulcase]$ cat ulcase1.sql

set termout offdrop table emp;
drop table dept;

create table emp
       (empno number(4) not null,
        ename char(10),
        job char(9),
        mgr number(4),
        hiredate date,
        sal number(7,2),
        comm number(7,2),
        deptno number(2));

create table dept
       (deptno number(2),
        dname char(14) ,
        loc char(13) ) ;

exit

2. 控制文件

[oracle@node3 ulcase]$ cat ulcase1.ctl

-- NAME
-- ulcase1.ctl - SQL*Loader Case Study 1: Loading Variable-Length Data
--
-- DESCRIPTION
-- This case study demonstrates the following:
--
-- A simple control file identifying one table and three columns
-- to be loaded.
--
-- Including data to be loaded from the control file itself, so
-- there is no separate datafile.
--
-- Loading data in stream format, with both types of delimited
-- fields: terminated and enclosed.--
-- NOTES ABOUT THIS CONTROL FILE
-- The LOAD DATA statement is required at the beginning of the
-- control file.
--
-- INFILE * specifies that the data is found in the control file
-- and not in an external file.
--
-- The INTO TABLE statement is required to identify the table to
-- be loaded (dept) into. By default, SQL*Loader requires the
-- table to be empty before it inserts any records.
--
-- FIELDS TERMINATED BY specifies that the data is terminated by
-- commas, but may also be enclosed by quotation marks. Datatypes
-- for all fields default to CHAR.
--
-- The names of columns to load are enclosed in parentheses.
-- If no datatype or length is specified and the field is delimited
-- with ENCLOSED BY or with TERMINATED BY, then the default
-- datatype is CHAR and the default length is 255. If ENCLOSED BY
-- or TERMINATED BY is not specified, then the default type is CHAR
-- and the default length is 1.
--
--  BEGINDATA specifies the beginning of the data.
--
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ‘,‘ OPTIONALLY ENCLOSED BY ‘"‘
(DEPTNO, DNAME, LOC)
BEGINDATA
12,RESEARCH,"SARATOGA"
10,"ACCOUNTING",CLEVELAND
11,"ART",SALEM
13,FINANCE,"BOSTON"
21,"SALES",PHILA.
22,"SALES",ROCHESTER
42,"INT‘L","SAN FRAN"

执行后结果：

[oracle@node3 ulcase]$ sqlplus scott/tiger @ulcase1.sql

[oracle@node3 ulcase]$ sqlldr userid=scott/tiger control=ulcase1.ctl

SQL> select * from dept;

    DEPTNO DNAME      LOC
---------- -------------- -------------
    12 RESEARCH      SARATOGA
    10 ACCOUNTING      CLEVELAND
    11 ART          SALEM
    13 FINANCE      BOSTON
    21 SALES      PHILA.
    22 SALES      ROCHESTER
    42 INT‘L      SAN FRAN

7 rows selected.