SQL*Loader实验笔记【二】

 

 

所有SQL*Loader实验笔记

实验案例总结（1-7）：     SQL*Loader实验笔记【一】

实验案例总结（8-13）：   SQL*Loader实验笔记【二】

实验案例总结（14-19）： SQL*Loader实验笔记【三】

 

8）加载序列

  • 初始化
CJ@db11g>create table t2 (seqno int,name varchar2(20));
 
Table created.
  • 控制文件
load data
infile *
into table t2
replace
(
seqno RECNUM,
name Position(1:1024)
)
BEGINDATA
chen
jian
wang
  • 验证结果
CJ@db11g>select * from t2;
 
     SEQNO NAME
---------- --------------------
         1 chen
         2 jian
         3 wang
  • 备注
  1. RECNUM关键字指定序列，也可以指定SEQUENCE参数，如：
INTO TABLE dept
(deptno  SEQUENCE(1, 3),
 dname   POSITION(1:14) CHAR)

 

9）载入有换行符的数据（REPLACE）

  • 初始化
CJ@db11g>desc t;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 DEPTNO                                                         NUMBER(2)
 DNAME                                                          VARCHAR2(14)
 LOC                                                            VARCHAR2(13)
 LAST_UPTIME                                                    DATE
 ALL_LINE                                                       VARCHAR2(100)
  • 控制文件
LOAD DATA
INFILE *
INTO TABLE t
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPTIME date 'dd/mm/yyyy',
ALL_LINE "replace(:all_line,'n',chr(10))"
)
BEGINDATA
10,Sales,Virginia,01/04/2001,This is the SalesnOffice in Virginia
20,Accounting,Virginia,13/04/2001,This is the AccountingnOffice in Virginia
30,Consulting,Virginia,14/04/2001,This is the ConsultingnOffice in Virginia
40,Finance,Virginia,14/05/2001,This is the FinancenOffice in Virginia
  • 验证结果
CJ@db11g>select *from t;
 
    DEPTNO DNAME          LOC           LAST_UPTI ALL_LINE
---------- -------------- ------------- --------- ----------------------------------------------------------------------------------------------------
        10 SALES          VIRGINIA      01-APR-01 This is the Sales
                                                  Office i
                                                   Virgi
                                                  ia
 
        20 ACCOUNTING     VIRGINIA      13-APR-01 This is the Accou
                                                  ti
                                                  g
                                                  Office i
                                                   Virgi
                                                  ia
 
    DEPTNO DNAME          LOC           LAST_UPTI ALL_LINE
---------- -------------- ------------- --------- ----------------------------------------------------------------------------------------------------
 
        30 CONSULTING     VIRGINIA      14-APR-01 This is the Co
                                                  sulti
                                                  g
                                                  Office i
                                                   Virgi
                                                  ia
 
        40 FINANCE        VIRGINIA      14-MAY-01 This is the Fi
                                                  a
                                                  ce
 
    DEPTNO DNAME          LOC           LAST_UPTI ALL_LINE
---------- -------------- ------------- --------- ----------------------------------------------------------------------------------------------------
                                                  Office i
                                                   Virgi
                                                  ia
  • 备注
  1. 加载的数据中使用非换行符的其他字符表示换行符（本例中使用了“n”，这里的方法是使用replace函数用一个CHR(10)替换该文本。
  2. chr()函数的作用是将某个ascii码转换为字符，和ascii()函数对应，这里的10转换就是换行符。

 

10）载入有换行符的数据（FIX）

  • 初始化
CJ@db11g>desc t;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                             NUMBER(5)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(50)

数据内容

10,bean1,ora
cle beanbee user           20,bean2,oracle bean
bee user           30,bean3,oracle beanbee us
er           40,beanbee4,o
racle beanbee user
  • 控制文件
LOAD DATA
INFILE 'test.csv' "fix 40"
INTO TABLE t
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
DEPTNO,
DNAME,
LOC
)
  • 验证结果
CJ@db11g>select * from t;
 
    DEPTNO DNAME          LOC
---------- -------------- --------------------------------------------------
        10 bean1          ora
                          cle beanbee user
 
        20 bean2          oracle bean
                          bee user
 
        30 bean3          oracle beanbee us
                          er
  • 备注
  1. 使用FIX属性必须指定INFILE选项。
  2. 使用FIX方法，输入数据必须出现在定长记录中，每个记录于输入数据集中所有其他记录的长度都相同，即有相同的字节书，所以这种方式多使用于定长输入文件，可以使用unix下的od工具检验输入数据的合法性，od工具会将文件以八进制（和其他格式）转储到屏幕上，同时可以对输出格式化使其每行显示10个字符，如下：
[oracle@bean ~]$ od -c -w10 -v test.csv
0000000   1   0   ,   b   e   a   n   1   ,   o
0000012   r   a  \n   c   l   e       b   e   a
0000024   n   b   e   e       u   s   e   r   
0000036                                       
0000050   2   0   ,   b   e   a   n   2   ,   o
0000062   r   a   c   l   e       b   e   a   n
0000074  \n   b   e   e       u   s   e   r   
0000106                                       
0000120   3   0   ,   b   e   a   n   3   ,   o
0000132   r   a   c   l   e       b   e   a   n
0000144   b   e   e       u   s  \n   e   r   
0000156                                       
0000170   4   0   ,   b   e   a   n   b   e   e
0000202   4   ,   o  \n   r   a   c   l   e   
0000214   b   e   a   n   b   e   e       u   s
0000226   e   r  \n
0000231

可以注意到上面导入的数据中40的行没有导入成功，这就是输入数据格式不对，可以看到最后一行有一个 \n 结束，这个情况会在导入的时候出现报错：

SQL*Loader-501: Unable to read file (test.csv)

SQL*Loader-566: partial record found at end of datafile

SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.

可以在od的输出看到每行记录之间出现了一个空行，其实这个都是空格，需要注意的是这些空格会在导入后在数据库中保留，如下：

CJ@db11g>select deptno,'"'||loc||'"' loc from t;
 
    DEPTNO LOC
---------- ----------------------------------------------------
        10 "ora
           cle beanbee user           "
 
        20 "oracle bean
           bee user           "
 
        30 "oracle beanbee us
           er           "

后续的数据处理可以使用SQL的TRIM函数截断。

 

11）载入有换行符的数据（VAR）

  • 初始化
CJ@db11g>desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                             NUMBER(5)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(50)

数据内容

复制代码
04610,Sales,This is the Sales
Office in Virginia
05620,Accounting,This is the Accounting
Office in Virginia
05630,Consulting,This is the Consulting
Office in Virginia
05040,Finance,This is the Finance
Office in Virginia
复制代码
  • 控制文件
LOAD DATA
INFILE 'test.csv' "var 3"
INTO TABLE t
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
DEPTNO,
DNAME,
LOC
)
  • 验证结果
CJ@db11g>select * from t;
 
    DEPTNO DNAME          LOC
---------- -------------- --------------------------------------------------
        10 Sales          This is the Sales
                          Office in Virginia
 
        20 Accounting     This is the Accounting
                          Office in Virginia
 
        30 Consulting     This is the Consulting
                          Office in Virginia
 
        40 Finance        This is the Finance
                          Office in Virginia
  • 备注
  1. 使用VAR属性必须指定INFILE选项。
  2. 通过VAR属性指定每个记录的总字节数，即记录的总长度，这样就可以方便的加载包含换行符的变长记录了。
  3. VAR 3表示在每一行记录的前3个字节指定这一行记录的字节总数，如056表示这一行记录共有56个字节。

 

12）载入有换行符的数据（STR）

  • 初始化
CJ@db11g>desc t;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                             NUMBER(5)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(50)

数据内容

复制代码
10,bean1,ora
cle beanbee user|
20,bean2,oracle bean
bee user1|
30,bean3,oracle beanbee us
er12|
40,beanbee4,o
racle be
anbee us
er123|
复制代码
  • 控制文件
LOAD DATA
INFILE 'test.csv' "str x'7C0A'"
INTO TABLE t
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
DEPTNO,
DNAME,
LOC
)
  • 验证结果
CJ@db11g>select * from t;
 
        10 bean1          ora
                          cle beanbee user
 
        20 bean2          oracle bean
                          bee user1
 
        30 bean3          oracle beanbee us
                          er12
 
        40 beanbee4       o
                          racle be
                          anbee us
                          er123
  • 备注
  1. 使用STR属性必须指定INFILE选项。
  2. 使用STR属性可以指定某个字符序列为一个新的行结束符，这样SQL*Loader就不会以新的行结束符来标识一行的末尾，对于加载含有换行的数据，这种方式有着较上面三种方式更大的便利。
  3. 用来指定为新的换行符的字符序列通常会使用某个特殊标记，然后加上一个换行符（在一个文本编辑器中查看输入数据时，就能很容易的看到行结束符）
  4. STR属性以十六进制指定，要得到所需的具体的十六进制串，可以使用SQL中的utl_raw.cast_to_raw方法，之后再将得到的数值添加到控制文件中。获取如下：
CJ@db11g>select utl_raw.cast_to_raw('|'||chr(10)) from dual;

UTL_RAW.CAST_TO_RAW('|'||CHR(10))
--------------------------------------------------------------------------------
7C0A

 

 

13）使用nullif子句

  • 初始化
CJ@db11g>create table t3 (x varchar2(10),y varchar2(10));
 
Table created.
  • 控制文件
LOAD DATA
INFILE *
INTO TABLE t3
REPLACE
(
x position(1:2) integer external nullif x='1',
y position(3:8)
)
BEGINDATA
1 10
20lg
  • 验证结果
CJ@db11g>select *from t3;
 
X          Y
---------- ----------
           10
20         lg
  • 备注
  1. integer external nullif x='1'      判断x字段，如果x的值为1，就将其置为空
  2. 与其相同的用法有defaultif，如指定col2 POSITION (6:8) INTEGER EXTERNAL DEFAULTIF col1 = 'aname'，那么就是当字段为aname时将其置为空

 

posted @ 2016-07-30 10:46  zfswff  阅读(534)  评论(0)  编辑  收藏  举报