SQL*Loader之CASE1

近来项目涉嫌到将文件文件中的数据导入到Oracle数据库中,故研商了下SQL*Loader,官档提供的素材不是很丰盛,很多案例中冒出的言辞在官档中找不到出处。但它提供的案例本人却突显出了SQL*Loader成效的雄强。鉴于Oracle
11g的软件自个儿并未带走那一个案例,需求特地到官方网站下载Oracle Database
11g Release 2
Examples
,甚是麻烦。在此,将这个案例分享,也利于未来商量、借鉴。

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

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

共计有拾三个案例,案例基本上蕴含三局地,控制文件、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
  1. 操纵文件

[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.

计算:在本例中,

       1> INFILE
*代表要求导入的多寡在该控制文件中,而不在外部文件中。

       2> INTO TABLE语句钦赐数量导入到哪个表中。

       3> 值域首先被逗号截断,有的值被双括号括起来了。

       4>
对应的列括在括号中,借使没有点名数据类型和长度,在有ENCLOSED BY or
TE帕杰罗MINATED
BY的事态下,数据文件中对应的多寡暗中认可的是CHA宝马X5类型,字符长度255.在并未ENCLOSED
BY or TE酷威MINATED BY的状态下,暗许的也是CHASportage类型,但字符长度是1.

       5> BEGINDATA代表控制文件中央控制制新闻的完工,数据的起来。

 

相关文章