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.logColumn Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- DEPTNO FIRST * , CHARACTER DNAME NEXT * , CHARACTER LOC NEXT * , CHARACTERRecord 4: Rejected - Error on table DEPT, column LOC. Field in data file exceeds maximum lengthTable 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 Virginia20 Accounting Virginia30 Consulting Virginiademo2.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 Virginia20 Accounting Virginia30 Consulting VirginiaDEPTNO 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 textmore text m ore text more text more text more text more text more text more textmore text more text more text more text more text more text more text more textDEPTNO DNAME ---------- ------------------------------------------ LOC --------------------------------------------------------------------------------more text more text more text more text more text more text more text more textmoretext 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,VirginiaALEX@devdb1 >select * from dept;DEPTNO DNAME LOC ---------- ---------- --------------------10 Sales Virginia,USA <===字段中包含分隔符 , 20 Accounting Va,”USA” <===字段中包含” 30 Consulting Virginia40 Finance Virginia

實驗4 使用FILLER參數來過濾掉不需要的列

[oracle@node1 mysqlldr]$ cp demo3.ctl demo4.ctl [oracle@node1 mysqlldr]$ vi demo4.ctlLOAD 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,hahatruncate table dept;sqlldr userid=alex/alex control=/home/oracle/mysqlldr/demo4.ctl[oracle@node1 mysqlldr]$ cat demo4.logColumn 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/alexALEX@devdb1 >select * from dept;DEPTNO DNAME LOC ---------- --------------- ---------------10 Sales Virginia,USA20 Accounting Va,"USA"30 Consulting Virginia40 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.ctlcol dname for a15 col loc for a15select * from dept;DEPTNO DNAME LOC LAST_UPDATED ---------- --------------- --------------- -------------------10 Sales Virginia 2014/05/01 00:00:0020 Accounting Va,"USA" 2014/06/03 00:00:0030 Consulting Virginia 2014/07/22 00:00:0040 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:0020 ACCOUNTING VIRGINIA 2002/08/09 00:00:0030 CONSULTING VIRGINIA 2008/08/08 00:00:0040 FINANCE VIRGINIA 2014/06/03 00:00:00

本文轉自ICT時空 dbasdk博客，原文鏈接： SQL*Loader 筆記 （一） 熱身練習，如需轉載請自行聯系原博主。

總結

以上是生活随笔為你收集整理的SQL*Loader 笔记 （一） 热身练习的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。