SQL*Loader 筆記 （一） 熱身練習

dbasdk發表於2014-07-25
SQL筆記
練習內容來自湯姆大叔的程式設計藝術第十五章“資料載入和解除安裝”

SQL*Loader是載入資料的主流方法
傳統路徑：SQLLDR會利用SQL插入的方式載入資料
直徑路徑：直接格式化資料塊
     從一個平面檔案讀取資料，並將其直接寫至格式化的資料庫塊，而繞開整個SQL引擎，同時還能避免redo、undo的生成。要在一個沒有任何資料的資料庫中充分載入資料，最快的方法就是採用並行直接路徑載入
外部表：允許訪問作業系統檔案，就好像它們是資料庫表一樣

資料解除安裝技術
平面檔案解除安裝(flat file unload)
資料泵解除安裝(data pump unload)

實驗1
檔案路徑
[oracle@node1 mysqlldr]$ pwd
/home/oracle/mysqlldr
[oracle@node1 mysqlldr]$ ll
total 16
-rw-r--r-- 1 oracle oinstall   76 Jul 22 09:53 afiedt.buf
-rw-r--r-- 1 oracle oinstall  173 Jul 22 09:56 demo1.ctl
-rw-r--r-- 1 oracle oinstall 1611 Jul 22 09:57 demo1.log
-rw-r--r-- 1 oracle oinstall  111 Jul 22 09:47 dept.sql

配置檔案demo1.ctl
[oracle@node1 mysqlldr]$ cat demo1.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ','
(DEPTNO,DNAME,LOC)
BEGINDATA
10,Sales,Virginia
20,Accounting,Virginia
30,Consulting,Virginia
40,Finance,Virginia
[oracle@node1 mysqlldr]$

建立表指令碼
[oracle@node1 mysqlldr]$ cat dept.sql
create table dept
(deptno number(2) constraint dept_pk primary key,
dname varchar2(14),
loc   varchar2(13)
)
/

執行命令
sqlldr userid=alex/alex control=/home/oracle/mysqlldr/demo1.ctl


實驗2
配置檔案中第四條記錄的最後一個欄位有很多的字元
[oracle@node1 mysqlldr]$ cat demo2.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ','
(DEPTNO,DNAME,LOC)
BEGINDATA
10,Sales,Virginia
20,Accounting,Virginia
30,Consulting,Virginia
40,Finance,Virginia text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text moretext more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text moretext more text more text more text more text more text more text more text moretext more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text moretext more text more text more text more

清空之前插入的資料
ALEX@devdb1 >truncate table dept;


執行命令
[oracle@node1 mysqlldr]$ sqlldr userid=alex/alex control=/home/oracle/mysqlldr/demo2.ctl


在demo2.log中出現了exceeds maximum length 最大長度不夠
[oracle@node1 mysqlldr]$ cat demo2.log

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO                              FIRST     *   ,       CHARACTER
DNAME                                NEXT     *   ,       CHARACTER
LOC                                  NEXT     *   ,       CHARACTER

Record 4: Rejected - Error on table DEPT, column LOC.
Field in data file exceeds maximum length

Table DEPT:
  3 Rows successfully loaded.
  1 Row not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

資料只成功載入3條，最後一條沒有成功
ALEX@devdb1 >select * from dept;

DEPTNO DNAME                                      LOC
---------- ------------------------------------------ ---------------------------------------
        10 Sales                                      Virginia
        20 Accounting                                 Virginia
        30 Consulting                                 Virginia

demo2.bad檔案中記錄了載入失敗的資料
[oracle@node1 mysqlldr]$ cat demo2.bad
40,Finance,Virginia text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text moretext more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text moretext more text more text more text more text more text more text more text moretext more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text moretext more text more text more text more


修改控制檔案demo2.ctl，將char預設的255擴充套件到1000
[oracle@node1 mysqlldr]$ cat demo2.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ','
(DEPTNO,DNAME ,LOC char(1000))
BEGINDATA
10,Sales,Virginia
20,Accounting,Virginia
30,Consulting,Virginia
40,Finance,Virginia text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text moretext more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text moretext more text more text more text more text more text more text more text moretext more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text moretext more text more text more text more

更改表dept的欄位loc為varchar2（1000）
ALEX@devdb1 >alter table dept modify loc varchar2(1000);

Table altered.

ALEX@devdb1 >desc dept;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                    NOT NULL NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(1000)

清空表後重新載入
truncate table dept;

$ sqlldr userid=alex/alex control=/home/oracle/mysqlldr/demo2.ctl

驗證結果插入成功
select * from dept;

    DEPTNO DNAME
---------- ------------------------------------------
LOC
--------------------------------------------------------------------------------
        10 Sales
Virginia

        20 Accounting
Virginia

        30 Consulting
Virginia


    DEPTNO DNAME
---------- ------------------------------------------
LOC
--------------------------------------------------------------------------------
        40 Finance
Virginia text more text more text more text more text more text more text more t
ext more text more text more text more text more text more text more text more t
ext moretext more text more text more text more text more text more text more te
xt more text more text more text more text more text more text more text more te
xt more text more text more text more text moretext more text more text more tex
t more text more text more text more text moretext more text more text more text
 more text m
ore text more text more text more text more text more text more text
 more text more text more text more text more text more text more text more text

    DEPTNO DNAME
---------- ------------------------------------------
LOC
--------------------------------------------------------------------------------
 more text more text more text more text more text more text more text more text
 moretext more text more text more text more

實驗3

配置檔案中加入選項“ ”區域中代表一個完整的欄位，可忽略其中的,號。
如果欄位中含有”，需要用”“來替代
[oracle@node1 mysqlldr]$ cat demo3.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(DEPTNO,DNAME,LOC)
BEGINDATA
10,Sales,"Virginia,USA"
20,Accounting,"Va,""USA"""
30,Consulting,Virginia
40,Finance,Virginia


ALEX@devdb1 >select * from dept;

    DEPTNO DNAME      LOC
---------- ---------- --------------------
        10 Sales      Virginia,USA   <===欄位中包含分隔符 , 
        20 Accounting Va,”USA”    <===欄位中包含” 
        30 Consulting Virginia
        40 Finance    Virginia

實驗4 使用FILLER引數來過濾掉不需要的列
[oracle@node1 mysqlldr]$ cp demo3.ctl demo4.ctl
[oracle@node1 mysqlldr]$ vi demo4.ctl

LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(DEPTNO,DNAME,LOC,dummy1 filler)
BEGINDATA
10,Sales,"Virginia,USA",haha
20,Accounting,"Va,""USA""",haha
30,Consulting,Virginia,haha
40,Finance,Virginia,haha

truncate table dept;

sqlldr userid=alex/alex control=/home/oracle/mysqlldr/demo4.ctl
 
[oracle@node1 mysqlldr]$ cat demo4.log


   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO                              FIRST     *   ,  O(") CHARACTER           
DNAME                                NEXT     *   ,  O(") CHARACTER           
LOC                                  NEXT     *   ,  O(") CHARACTER           
DUMMY1                               NEXT     *   ,  O(") CHARACTER           
  (FILLER FIELD)


[oracle@node1 mysqlldr]$ sqlplus alex/alex

ALEX@devdb1 >select * from dept;

    DEPTNO DNAME           LOC
---------- --------------- ---------------
        10 Sales           Virginia,USA
        20 Accounting      Va,"USA"
        30 Consulting      Virginia
        40 Finance         Virginia

實驗5 日期型別的載入
ALEX@devdb1 >alter table dept add last_updated date;

Table altered.

ALEX@devdb1 >desc dept;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                    NOT NULL NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(1000)
 LAST_UPDATED                                       DATE


[oracle@node1 mysqlldr]$ cat demo5.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(DEPTNO,DNAME,LOC,LAST_UPDATED date 'yyyy-mm-dd')
BEGINDATA
10,Sales,Virginia,2014-05-01
20,Accounting,"Va,""USA""",2014-06-03
30,Consulting,Virginia,2014-07-22
40,Finance,Virginia,2014-07-23

[oracle@node1 mysqlldr]$ sqlldr userid=alex/alex control=/home/oracle/mysqlldr/demo5.ctl

col dname for a15
col loc for a15

select * from dept;

    DEPTNO DNAME           LOC             LAST_UPDATED
---------- --------------- --------------- -------------------
        10 Sales           Virginia        2014/05/01 00:00:00
        20 Accounting      Va,"USA"        2014/06/03 00:00:00
        30 Consulting      Virginia        2014/07/22 00:00:00
        40 Finance         Virginia        2014/07/23 00:00:00


實驗6 函式載入

[oracle@node1 mysqlldr]$ cat demo6.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc),
LAST_UPDATED date 'yyyy-mm-dd')
BEGINDATA
10,Sales,Virginia,1998-09-01
20,Accounting,Virginia,2002-08-09
30,Consulting,Virginia,2008-08-08
40,Finance,Virginia,2014-06-03

[oracle@node1 mysqlldr]$ sqlldr userid=alex/alex control=demo6.ctl
ALEX@devdb1 >select * from dept;

    DEPTNO DNAME           LOC             LAST_UPDATED
---------- --------------- --------------- -------------------
        10 SALES           VIRGINIA        1998/09/01 00:00:00
        20 ACCOUNTING      VIRGINIA        2002/08/09 00:00:00
        30 CONSULTING      VIRGINIA        2008/08/08 00:00:00
        40 FINANCE         VIRGINIA        2014/06/03 00:00:00

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29734436/viewspace-1226638/，如需轉載，請註明出處，否則將追究法律責任。

相關文章