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 資料庫還是必須熟悉這些最基本的東西，這樣功夫才會紮實。

9.12 PL SQL Objects

Oracle PL/SQL Objects 指的是與程式相關的物件， Function 、 Procedure 、 Package ， Trigger 都屬於 PL/SQL Objects 。

Function 主要的功能是用來回傳一段程式所得出來的結果，所以 Function 本身會帶有 return 關鍵字， return 代表的是這段程式碼所要回傳的結果，資料型態可以是 number 、 varchar2 ，甚至是 boolean ，例如我們要計算公司員工的所得稅，當所得小於 5000 時，所得稅為薪資的 15% ，當所得大於 5000 時，則為薪資的 33% ，透過建立如下 Function 可以用來計算出所得稅:

SQL> create or replace function compute_tax (salary in number) return number  as      begin       if salary < 5000 then        return salary * 0.15;      else        return salary * 0.33;      end if;      end; /

利用此 Function 很快的就可以得出每個員工所需要繳的所得稅:

Procedure 主要是用來執行一段程式碼，每次要執行時只需呼叫這個 Procedure 就好，不用再寫一大段程式來執行。例如要幫公司的員工加薪 5% (salary * 1.05) ，就可以把這段程式寫成 Procedure :

SQL> create or replace procedure raise_salary        as       begin         update hr.emp set salary = salary * 1.05;        commit;      end;      /

未來只要是幫員工加薪，就可以直接執行這個 Procedure :

Package 可以說是 Function 與 Procedure 的組合體，一個 Package 可以包含多個 Function 與 Procedure ，我們可以把一些具有相關性的程式碼做成 Package ，好處是方便管理以及調用。 Package 本身分成兩個部分， spec 與 body ， spec 就有如 Package 的頭部，用來定義此 Package 包含了哪些 Function 或 Procedure ； body 就是 spec 所定義的那些 Function 與 Procedure 所使用的實際程式碼，例如我們把上述範例的 Function 與 Procedure 做成一個 Package ，首先定義此 Package 的 spec :

SQL> create or replace package emp_salary as      function compute_tax (salary in number) return number;      procedure raise_salary;      end; /

然後再定義此 Package 的 body ，其實內容就是上述 Function 與 Procedure 的內容:

SQL> create or replace package body emp_salary as      function compute_tax (salary in number) return number as      begin       if salary < 5000 then        return salary * 0.15;      else        return salary * 0.33;      end if;      end;      procedure raise_salary as      begin        update hr.emp set salary = salary * 1.05;        commit;      end;   end; /

Package 的使用方式就是 Package 名稱後面加上所要執行的 Function 或是 Procedure ，例如執行此 Package 裡面的 Procedure :

SQL> exec emp_salary.raise_salary;

Trigger 也是由一段程式碼所構成，主要的功能是當某事件發生時，需要立即觸發執行某些動作，這些事件可以分為 DML Trigger 、 DDL Trigger 以及 Database Trigger ，其中又可以分為 Before Trigger 以及 After Trigger ， Before 表示在此動作完成前就必須要先執行完此 Trigger ；而 After 表示在此動作做完之後才執行此 Trigger。 DML Trigger 指的是當發生 Insert 、 Delete 、 Update 行為時，需要執行的動作。 DML Trigger 最常被拿來做為 Standard Audit 所用，例如設定一個 DML Trigger ，當 emp 裡面的薪資 (salary) 欄位被異動時，馬上紀錄這一筆更新前後的資料到另一個 Table 當中:

SQL> create or replace trigger t_emp        before update on emp for each row       begin       if :old.salary <> :new.salary then        insert into aud_emp values (user,:old.salary,:new.salary,sysdate);        end if;       end t_emp; /

DDL Trigger 用來設定當某個使用者產生了 DDL 行為時，需要執行的動作， DDL 行為如 create 、 drop 、 alter …等都可以用來觸發 DDL Trigger ，例如建立一個 DDL Trigger ，當 HR 這個使用者執行了 create 的動作之後，將這個行為記錄下來 (insert 到 ddl_log 這個 table) :

SQL> connect hr/hr SQL> create table ddl_log (oper varchar2(20),obj_owner varchar2(20), obj_name varchar2(20), oper_user varchar2(20), oper_date date); SQL> create or replace trigger t_create         before create on schema       begin         insert into ddl_log select ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name, user, sysdate from  dual;       end; /

這邊要注意的是， DDL Trigger 是要設定在要觸發的使用者底下，例如上述的 DDL Trigger 是要記錄 HR 這個使用者的 create 行為，那麼這個 DDL Trigger 就必須建立在 HR 這個使用者底下。

Database Trigger 用來設定當發生 Database 層級事件的時候所要做的動作， Database 層級的事件例如使用者的登入與登出 (logon 、 logoff) ，資料庫的啟動與關閉 (startup 、 shutdown) …等。例如建立一個 Database Trigger ，當使用者登入時，將其登入行為記錄下來:

SQL> create table logon_log (logon_user varchar2(20),logon_ip varchar2(20), logon_date date); SQL> create or replace trigger t_logon         after logon on database       begin         insert into logon_log values (sys_context('USERENV','SESSION_USER'),sys_context('USERENV','IP_ADDRESS'),sysdate);       end; /

所有的 PL/SQL Objects 都必須要編譯 (compile) 成功才可以被使用，在建立的過程當中會檢查程式語法是否正確，以及所需要的權限是否足夠，如果有問題的話這些物件仍然可以被建立但會出現 compile error，它的狀態會是 Invalid 不可以被使用，當修復問題後，我們可以使用 alter 指令來進行重新 compile :

SQL> alter function compute_tax compile;       (重新 compile function) SQL> alter procedure raise_salary compile;       (重新 compile procedure) SQL> alter package emp_salary compile;       (重新 compile package) SQL> alter package emp_salary compile body;       (重新 compile package body) SQL. alter trigger t_emp compile;       (重新 compile trigger)

這邊要注意的是，由於 Package 有分為 spec 與 body ，所以兩者都必須要 compile 成功才可以使用。

如果有很多物件需要 compile ， 在 $ORACLE_HOME/rdbms/admin 底下有一個 utlrp.sql ，執行這個腳本可以 compile 資料庫所有的物件:

SQL> @?/rdbms/admin/utlrp.sql

如果不使用這些程式了，可以使用 drop 指令來將其刪除，除此之外，只有 Trigger 可以將它的狀態設定為 disable 或 enable ，暫時不使用它可以先將 Trigger 設定為 disable ，例如:

SQL> alter trigger t_logon disable;      (disable t_logon 這個 trigger) SQL> alter trigger t_logon enable;      (enable t_logon 這個 trigger)

要得知系統有哪些 PL/SQL Objects 可以從 dba_objects 或是 user_objects 來查詢，而 Trigger 另外還有 dba_triggers 、 user_triggers 可以查。

如果要知道某一個 PL/SQL Objects 裡面寫著甚麼程式碼，除了透過工具查詢外 (如 sqldeveloper) ，還可以使用 dba_source 來查詢，例如查詢上述 raise_salary 這個 procedure 包含了哪些程式碼:

PL/SQL Objects 原則上是屬於程式設計的範圍， DBA 除了撰寫平日維運所需的一些腳本外，對這些程式的部分是比較少涉獵的，對於 DBA 來說，只需要關注 dba_objects 裡面是否存在 Invalid Objects 就好了，一般來說，一個正式的營運環境是不會有 Invalid Objects ，尤其是 SYS 、 SYSTEM 這類的系統使用者更是不可以有 Invalid Objects ，否則有可能會影響到資料庫正常的運行。