10.2 SQL Loader

Oracle SQL Loader 是一個能夠把資料從檔案匯入到資料庫的一種工具，檔案格式可以是純文字檔 (Text File) ，或者是以逗號分隔 (CSV 格式) 的檔案…等，只要是檔案中的資料欄位可以與資料庫 Table 的欄位互相匹配，基本上都可以使用 SQL Loader 將資料從檔案匯入到 Oracle 資料庫。

執行 SQL Loader 使用的是 sqlldr 這個命令，只要執行 sqlldr 就會提示如何使用:

實際上只要簡單執行 sqlldr <帳號>/<密碼>@<db> sqldr.ctl 就可以了，意思是使用 sqlldr 登入資料庫並執行 sqldr.ctl 裡面的內容， sqldr.ctl 是 SQL Loader 的 control file ，其實就是所謂的設定檔，裡面要定義資料來源、欄位的 mapping …等資訊，這個檔案是我們執行 sqlldr 之前要自己編寫的，整個 SQL Loader 的核心其實就在於如何撰寫這個 control file 。

SQL Loader 使用的 control file 是有固定格式的，必須遵循這四個項目依序來編寫:

• Options 子句: 設定 SQL Loader 的 Options ，例如容錯筆數、是否使用 Direct Load 、 是否使用 Parallel …等， Options 子句可以添加或者是不使用。

• Load Data 子句: 設定資料來源。

• Load Method 子句: 設定匯入資料的方式:

• INSERT : 直接使用 insert 命令匯入資料，這個方式必須在 Table 沒有任何資料的時候才可以使用。

• APPEND : 使用 insert 匯入資料，且資料都是 new rows ，在原本的資料之後新增到 Table 中。

• REPLACE : 使用 delete 的方式刪除 Table 所有的資料，然後再 insert 資料進去。

• TRUNCATE : 使用 truncate 的方式清除 Table 所有的資料，然後再 insert 資料進去。

• Into Table 子句 : 用來設定資料來源與資料庫 Table 欄位的 mapping 。

了解了 control file 的格式之後，就可以來編寫它，例如要將一個 CSV 格式的資料匯入 emp 這個 Table 當中，那麼就可以編輯一個名為 emp.ctl 的 control file 如下 :

options(errors=100,direct=true) load data infile 'emp.csv' truncate into table emp fields terminated by ',' optionally enclosed by '"' trailing nullcols (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE DATE "YYYY-MM-DD hh24:mi:ss", JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)

首先 options 設定容錯筆數為 100 ，並且執行 direct load ； Load Data 子句設定來源檔案為 emp.csv ； Load Method 子句設定為 TRUNCATE ； into table emp 表示將資料匯入 emp 這個 Table ，以下將針對欄位做 mapping :

• fields terminated by ',' : 表示資料來源的欄位是以逗號分隔。

• optionally enclosed by '"' : 表示資料來源當中，使用雙引號 “ 框起來的資料視為同一個欄位的資料，例如一筆資料為 “ Taipei,Taiwan “ ，雖然是逗號分隔，但是在雙引號裡面， “ Taipei,Taiwan “ 代表是同一個欄位的資料，如果沒有用雙引號，那麼在逗號分隔下，就會區分為兩個欄位 Taipei 與 Taiwan 。

• trailing nullcols : 代表這個欄位如果沒資料的話，那麼就視為是 null 。

最後將 Table 所有的欄位列出來，每個逗號分隔的資料剛好可以跟 Table 的這些欄位做匹配，這邊要注意的是， SQL Loader 讀取資料的時候，預設的資料型態都是 char ，如果 Table 所定義的欄位型態不是 char 的話，那麼就要在這邊進行設定，例如 HIRE_DATE 這個欄位的型態為 DATE ，所以在這邊就要特別註記為 DATE ，且格式為 "YYYY-MM-DD hh24:mi:ss" 。

編寫好 control file 之後，那麼就可以使用 sqlldr 將資料匯入資料庫了:

其中 bad 是設定 bad file ，表示如果有資料格式錯誤無法匯入，那麼就將這筆資料寫入 bad file 裡面； discard 表示 discard file ，雖然資料格式正確，但是條件不符，那麼就把這筆資料寫入 discard file ，例如匯入的資料當中與 Primary Key 的欄位出現重複的資料，那麼這些重複的資料就無法匯入，並且寫入 discard file 當中。

sqlldr 執行完畢後，可以檢視 log file 確認是否匯入的過程當中有產生錯誤，以及匯入的資料量是否正確:

除此之外，如果資料是屬於固定長度格式的 Text File ，也可以設定 control file 如下:

options(errors=100,direct=true) load data infile 'sales.txt' truncate into table sales (SALE_NO    POSITION(1:5)  INTEGER EXTERNAL,  SNAME      POSITION(6:13)  CHAR,   TITLE      POSITION(14:33)  CHAR,   DEPT_NO    POSITION(34:35)  CHAR,  SALARY     POSITION(36:44)  INTEGER EXTERNAL,  COMMISION  POSITION(45:49)  INTEGER EXTERNAL,  MANAGER_ID POSITION(50:54)  INTEGER EXTERNAL    APP_DATE   POSITION(56:65)  DATE "YYYY/MM/DD" )

不同於逗號分隔，無須設定 “fields terminated by” 等參數，而改由 POSITION 參數設定，直接做欄位的 mapping ， SALE_NO POSITION(1:5) 表示 Text File 的第一位到第五位的資料屬於欄位 SALE_NO ； SNAME POSITION(6:13) 表示第六位到第十三位的資料屬於欄位 SNAME … 以此類推。

SQL Loader 的 control file 所可以註記的資料型態共三種 :

• CHAR : 代表文字型態，為預設的資料型態，預設的長度為 255 ， Table 定義的 char 或 varchar2 ，對 SQL Loader 來說都是以 char 表示。如果 Table 的資料長度大於 255 (例如 1000) ，那麼就要特別註記長度為 char(1000) 。

• INTEGER EXTERNAL (或 DECIMAL EXTERNAL) : 代表數字型態， Table 定義的數字型態如 Number 、 INT …等，對 SQL Loader 來說都是以 INTEGER EXTERNAL (或 DECIMAL EXTERNAL) 來表示。

• DATE : 代表日期型態，與 Table 所表示的 DATE 意義相同。

實務上最常用的大概就是匯入固定長度的 Text File 與 CSV 格式的檔案了，雖然 SQL Loader 的 control file 不是那麼好編寫，但只要熟悉之後，這會是一個很好用的工具。

10.1 SQLPLUS

SQLPLUS是 Oracle 資料庫最原始的工具，它提供了一個與 Oracle 資料庫溝通的介面，當我們沒有一些工具例如 sqldeveloper 、 PL/SQL Developer 、 TOAD …等輔助時，就必須使用 SQLPLUS 來操作，因此身為 DBA 還是必須要熟悉 SQLPLUS 。

SQLPLUS 這項工具只要安裝 Oracle Server 或是 Client 軟體就可以使用，連線資料庫的方式有兩種，一種是透過 Oracle Net 的方式，也就是使用 sqlplus <帳號>/<密碼>@<tnsnames> 登入， @ 後面是 tnsnames.ora 裡面設定的名稱；另外一種方式是直接在 Oracle Server 上設定環境變數 ORACLE_SID ，例如登入 orcl 這個資料庫就必須在 Server 上設定 ORACLE_SID=orcl ，然後使用 sqlplus <帳號>/<密碼> 進行登入的動作。

對於 DBA 來說，最常使用的莫過於在 Oracle Server 上使用 sqlplus / as sysdba 進行登入，這個動作就是直接以 sys 最高權限的方式進行登入，但是要求作業系統的使用者必須是具有 dba 群組的權限才可以使用，否則就會出現 ORA-01017 invalid username/password 的錯誤，這邊所謂的 dba 群組指的是在 ORACLE 軟體安裝的時候，有一個步驟要輸入 dba 群組的名稱，例如安裝的時候將 dba 群組設定為 oinstall ，那麼作業系統的使用者就必須屬於 oinstall 的群組才可以使用 sqlplus / as sysdba 。一般來說， dba 群組的設定都是設定為 dba ，透過查詢 $ORACLE_HOME/rdbms/lib 底下的 config.c 這個檔案，就可以知道當初安裝的時候所設定的群組名稱為何:

config.c 這個檔案在不同的平台名稱也不同，如果是 Linux 、 HPUX ，就是查詢 config.c ； 如果是 AIX 、 Solaris ，就查詢 config.s 。

SQLPLUS 的命令結尾都以分號 “ ; ” 或者是斜線 “ / ” 來表示，如果是 PL/SQL 程式相關的語法，則最後都以斜線 “ / ” 來表示結束。執行 script 的話，則是使用 “ @ ” 代表執行，例如 @utlrp.sql ，表示執行 utlrp.sql 這個 script 。另外雙橫線 “ -- “ 表示註釋符號，在每一行前面增加 “ -- “ 表示不執行此行，如果是整段的話則是以斜線星號開頭 “ /* ” 以及星號斜線結尾 “ */ ” 來註釋整段。要檢視上一次 SQLPLUS 執行的命令，則是使用 “ l “ 或者是 “ list “ 指令，如果要重複執行上一次的命令，則是直接使用斜線 “ / “ 就可以重複執行上一次的命令。

透過 product_user_profile 可以用來限制使用者於 SQLPLUS 所使用的命令，如果 product_user_profile 不存在的話，可以透過執行 $ORACLE_HOME/sqlplus/admin 底下的 pupbld.sql 來建立，例如要限制 scott 這個使用者不能執行 truncate 這個命令，那麼就可以在 product_user_profile 新增一筆資料來表示 scott 不能使用 truncate :

SQL> connect system/oracle SQL> insert into product_user_profile (PRODUCT,USERID,ATTRIBUTE,CHAR_VALUE) values ('SQL*Plus','SCOTT','TRUNCATE','DISABLED'); SQL> commit;

在 product_user_profile 限制了之後，使用 scott 就無法進行 truncate 命令了:

如果要解除這個限制只要刪除 product_user_profile 裡面的紀錄就行了: 

SQL> delete product_user_profile where userid='SCOTT'; SQL> commit;

這邊要注意的是， product_user_profile 是屬於 system 使用者，要新增或刪除資料時，必須使用 system 登入操作才可以。

除了限制使用者個命令之外， SQLPLUS 也可以設定 login script ，位於 ORACLE Server 上的 $ORACLE_HOME/sqlplus/admin 目錄下有一個 glogin.sql ，只需把需要在 login 時執行的命令設定在 glogin.sql ，那麼只要登入 SQLPLUS 就會自動執行此命令，如果不想在 Server 端設定，也可以在 Client 端的 $ORACLE_HOME/sqlplus/admin 底下，設定 login.sql 。

SQLPLUS 預設的提示符號為 SQL> ，要更改的話可以使用 set sqlprompt 命令來更改，例如:

SQL> set sqlprompt '&_connect_identifier > '      (顯示目前登入的 DB 名稱) SQL> set sqlprompt "_user _privilege> "       (顯示目前登入的使用者與權限) SQL> set sqlprompt "_user _privilege 'on' _date >"       (顯示目前登入的時間)

由於 SQLPLUS 算是一個比較陽春的工具，所以撈出來的資料有可能雜亂無章，這時候就需要設定一些環境變數來格式化輸出的結果，例如執行一段腳本如下:

col username format a12 col sid format 999999 col spid format a10 col status format a8 select a.username username,a.sid sid,a.serial# serial#,a.status,b.spid spid,        to_char(a.logon_time,'yyyymmdd hh24:mi:ss') logon_time   from v$session a,v$process b where a.paddr=b.addr order by a.logon_time;

其中 col … format 表示用來格式化這個欄位的長度， format a12 表示此欄位為文字型態，長度為 12 ， format 999999 表示此欄位為數字型態，長度為 6 位數。將欄位格式化之後，執行 SQL 所輸出的結果就會比較整齊。

除了格式化欄位長度之外，還有比較一些常用的環境變數如下:

• set linesize <n> : 用來設定一行的長度， set linesize 180 表示一行輸出的長度為 180 。

• set pagesize <n> : 用來設定輸出一頁有幾行。

• set timing on / off : 用來設定是否顯示 SQL 語法的執行時間。

• set echo on / off : 在執行腳本時，用來設定是否將腳本的命令顯示出來。

• set head on / off : 用來設定是否顯示欄位名稱。

• set feedback on / off : 例如執行一段 select 查詢 ，最後 SQLPLUS 都會顯示出查詢了多少個 rows ，透過腳本執行時，可使用feedback on / off 來設定是否顯示出這一段文字。

• set markup html on : 設定輸出為 HTML 格式。

在實務上來說，使用 SQLPLUS 有幾個比較常用的命令如下:

• describe (desc) : 用來顯示 Table 欄位資訊或者是 Package 的 spec 資訊，例如要知道 dba_tables 有哪些欄位則執行 desc dba_tables 。

• host (!) : 在 SQLPLUS 裡面執行 host 或是驚嘆號 “ ! “ ，可以回到作業系統的 shell 當中，雖然直接 exit 退出 SQLPLUS 就好，不過如果當 SQLPLUS 裡面已經設定了一些環境變數，或者已經格式化一些查詢，此時想要保留設定又需要暫時回到 OS 查詢一些東西，就可以使用 host 指令暫時跳出 SQLPLUS 。

• spool : 將 SQLPLUS 的查詢結果輸出到一個檔案，先使用 spool <檔案名稱> ，然後執行 SQL 查詢，最後再 spool off 。

• save <filename> : 將 SQLPLUS 所執行的查詢 SQL 語法，儲存到一個檔案當中。

• edit : 在 SQLPLUS 裡面啟用文字編輯器，這是一個非常好用的功能，當我們輸入一段冗長的 SQL 語法，一執行下去發現某個地方寫錯了，最令人頭疼的是要重新再輸入一次這麼長的一段語法，其實此時只要輸入 “ edit “ ，那麼 SQLPLUS 就會開啟預設的文字編輯器，只要使用 edit 打開文字編輯器，那麼我們就可以很輕鬆地修改語法中錯誤的地方，不用重新輸入了。預設文字編輯器在 Windows 平台會開啟記事本，而 Unix 平台則是會開啟 vi ，如果 edit 無法進入編輯模式，那麼就必須使用 define_editor 來設定文字編輯器，例如要設定 vi 為文字編輯器，那麼就執行 define_editor=vi ，之後再使用 edit 就可以開啟編輯模式了。

• set autotrace on / off / traceonly : 用來檢視 SQL 語法的執行計畫，如果是 set autotrace on ，那麼此 SQL 語法會真正的執行然後顯示出執行計畫；如果使用 set autotrace traceonly ，那麼 SQL 語法只會顯示出執行計畫，不會真正執行。

• copy from : 用來複製 Table 的資料，例如將 orcl 資料庫裡面的 HR 使用者的 employees 這個 Table 複製過來:

雖然現在有很多資料庫工具用起來很方便，但學習 Oracle 資料庫還是必須熟悉這些最基本的東西，這樣功夫才會紮實。