幾個SQLLDR的典型案例

    今天打算玩玩SQLLDR這個小工具，於是按照三思兄的塗抹一書中幾個比較典型的案例進行操作，為了以後方便查詢於是記錄如下。
    1.將excel檔案匯入
1.1首先將execl檔案另存為csv檔案(該檔案中各欄位以","分割);
1.2配置control檔案，control檔案ldr_case1.txt內容如下:
load data
infile "d:\test.csv"
truncate into table bonus
fields terminated by ","
(ename,job,sal)
1.3最後執行sqlldr scott/tiger control=ldr_case1.txt即可

    2.載入的資料中包含分隔符
2.1透過optinally enclosed選項來遮蔽分隔符;
2.2配置control檔案，control檔案ldr_case1.txt內容如下:
load data
infile "d:\test.txt
truncate into table bonus
fields terminated by "," optionally enclosed by '"'
(ename,job,sal)
2.3資料檔案test.txt內容如下:
smith,"cleak,",3905
allen,"sales,""man""",2892
2.4執行sqlldr scott/tiger control=ldr_case1.txt即可;
2.5最後透過select顯示執行的結果:
SQL> select * from bonus;                                                      
ENAME      JOB                  SAL       COMM
---------- ------------------------------ ---------- ----------                
smith     cleak,                        3905           
allen      sales,"man"                2892

    3.資料檔案中的列比要匯入的表中列多
3.1透過tcol filler選項來遮蔽多餘的列;
3.2配置control檔案，control檔案ldr_case1.txt內容如下:
load data
infile "d:\test.txt"
truncate into table bonus
fields terminated by "," optionally enclosed by '"'
(ename,tcol filler,sal)
3.3執行sqlldr scott/tiger control=ldr_case1.txt即可;
3.4最後透過select顯示執行的結果;
SQL> select * from bonus;                                                      
ENAME      JOB                  SAL       COMM
---------- ------------------------------ ---------- ----------                
smith                                      3905           
allen                                       2892

    4.明確指定只載入nn~mm行
4.1只匯入資料檔案中的第2~4行,透過skip和load選項實現該需求;
4.2配置控制檔案，control檔案ldr_case1.txt內容如下:
load data
infile "d:\test.txt"
truncate into table bonus
fields terminated by "," optionally enclosed by '"'
(ename,job,sal)
4.3資料檔案test.txt內容如下:
smith,"cleak,",3905
allen,"sales,""man""",2892
king,worker,1000
jane,manager,3000
micile,president,5000
4.4執行sqlldr scott/tiger control=ldr_case1.txt skip=1 load=3即可;
4.5執行select驗證結果;
SQL> select * from bonus;
ENAME      JOB                    SAL       COMM
---------- ------------------------------ ---------- ----------
allen     sales,"man"                 2892                                     
king      worker                         1000                 
jane      manager                      3000

    5.匯入某些欄位無值的資料
5.1透過trailing nullcols選項遮蔽掉某些列無值的情況:
5.2配置控制檔案，control檔案ldr_case1.txt內容如下:
load data
infile "d:\test.txt"
truncate into table bonus
fields terminated by ","  trailing nullcols
(ename,job,sal)
5.3資料檔案test.txt的內容如下:
smith,cleak,3905
king,worker,1000
jane,manager,
5.4執行sqlldr scott/tiger control=ldr_case1.txt
5.5執行select驗證結果
SQL> select * from bonus;                                                      
ENAME      JOB                    SAL       COMM                
---------- ------------------------------ ---------- ----------
smith      cleak                        3905                           
king       worker                       1000
jane       manager