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 ，否則有可能會影響到資料庫正常的運行。

9.11 Database Link

Oracle Database Link (DB Link) 簡單來說，就是在兩個不同的 DB 之間建立起一個橋樑，提供了跨資料庫查詢的一個途徑，例如有兩個資料庫 DB1 與 DB2 ，由 DB1 建立一個 DB Link 到 DB2 ，那麼就可以由 DB1 發起一條查詢的指令來查詢 DB2 的 Table :

Database Link 的原理是透過 Tnsnames 來連線到另一個資料庫，換句話說，我們建立 DB Link 其實就是在告訴系統如何連線到另一個資料庫，由系統自動幫我們連線到另一個資料庫並且把資料撈取回來。 DB Link 可以分為 Public Database Link 與 Private Database Link 兩種， Public Database Link 表示此資料庫的所有使用者都可以使用；而 Private Database Link 只有建立此 DB Link 的使用者可以使用。使用 create database link 的語法來建立:

SQL> create public database link ora12 connect to hr identified by hr using 'ORA12';       (建立 public DB Link) SQL> create database link ora12 connect to hr identified by hr using 'ORA12';       (建立 private DB Link)

其中:

• connect to : 表示連線到另一個資料庫的使用者名稱。

• identified by : 表示連線使用者的密碼。

• using : 表示連線到另一個資料庫的 Tnsnames ，需要先在 $ORACLE_HOME/network/admin/tnsnames.ora 裡面設定。

這邊要注意的是，只有在後面的 using 才需要使用單引號，前面輸入帳號密碼不需要用單引號。如果要建立的是 Public Database Link 只需要加上 public 關鍵字就可以了。建立好 DB Link 之後，我們就可以使用 @ 語法來查詢另一個資料庫的資料，例如查詢 ORA12 資料庫的 employees :

SQL> select * from employees@ORA12;

要得知目前資料庫有多少個 Database Link ，使用 dba_db_links 來查詢:

SQL> select * from dba_db_links;

Database Link 與資料庫的 Global Name 有著一定的關係， Global Name 預設為 db_name.db_domain ，在資料庫建立時如果有設定 db_domain 參數，那麼 Global Name 就會自動設定為 db_name.db_domain ，如果在創建時沒有設定 db_domain ，那麼即便後來設定了 db_domain ， Global Name 也不會自動變更，例如 ORA11 這個資料庫在建立時設定 db_domain 為 oracle.com ，那麼它的 Global Name 就為 ORA11.oracle.com 。

當參數 global_names 設定為 TRUE 的時候，此時所建立 DB Link 的名稱就會帶上 db_domain ，並且此 DB Link 的命名必須與所連線資料庫的 Global Name 名稱相同才能夠成功的連線，例如目前資料庫的 Global Name 為 ORA11.ORACLE.COM ，那麼所建立的 DB Link 也會自動帶上 ORACLE.COM :

雖然成功建立了，但很有可能還是會連線失敗:

這個訊息說明 DB Link 的名稱為 ORA12.ORACLE.COM 但是對方資料庫的 Global Name 為 ORA12 ，這兩者是不相同的，所以無法成功連線過去，此時必須要把 ORA12 這個資料庫的 Global Name 改為 ORA12.ORACLE.COM 才可以成功的連線:

這邊順帶提一下，更改 Global Name 的方式有兩種，一種是使用 alter database rename global_name ，一種是直接 update sys.PROPS$ :

SQL> alter database rename global_name to ORA11.ORACLE.COM;       (更改 global name 為 ORA11.ORACLE.COM) SQL> update sys.PROPS$ set value$='ORA11' where name='GLOBAL_DB_NAME'; SQL> commit;       (更改 global name 為 ORA11)

總結來說，當參數 global_names 為 TRUE 的時候， DB Link 的名稱必須使用對方資料庫的 Global Name ，當 Global Name 帶有 db_domain 時， DB Link 的名稱也會自動帶上 db_domain 。建議使用 DB Link 這項功能時， global_names 使用預設的 FALSE ，這樣名稱的設定會比較直觀，我們想要替 DB Link 取甚麼名稱就取甚麼，也不用考慮到對方資料庫的 Global Name ，用起來也不會有 db_domain 的問題。最後要注意的是，當 DB Link 建立之後，就不可以隨意地更改 global_names 參數或是 Global Name ，否則會造成系統無法辨識已經存在的 DB Link 名稱，會出現如下情況，明明可以查詢的到，但是系統卻無法辨識:

若要驗證 DB Link 的連線是否可行，最簡單的方式就是執行一段 select 能否成功，問題就在於 Private Database Link 必須要以 DB Link 的擁有者登入才可以進行測試連線，其它使用者是無法驗證 Private Database Link 的連線的:

 

此時可以藉由建立下列 Procedure 來測試 Private Database Link ，把這個 Procedure 建立在 Private Database Link 的擁有者底下，以這個例子來說就是建在 HR 底下來驗證 priv_ora12 這個 Private Database Link :

create or replace procedure hr.dblink_test(link_name varchar2) is type lnkcurtyp is ref cursor; cur_link_name lnkcurtyp; glname varchar2(30); begin  open cur_link_name for 'select * from global_name@'||link_name;  loop   fetch cur_link_name into glname;   exit when cur_link_name%notfound;   dbms_output.put_line('GL_NAME : '||glname);  end loop;  close cur_link_name; end; /

透過這個 Procedure ，即便不是使用 DB Link 擁有者也可以驗證 Private Database Link 是否可以連線:

最後要提的是，從 Oracle 11.2.0.4 開始，增加了安全性，已經無法查得 DB Link 連線使用者的密碼了:

 

而 DB Link 的建立又要求輸入連線使用者的名稱與密碼，那麼要怎麼移轉 DB Link 的設定 ? 這個時候就必須使用 Data Pump 將 DB Link 的設定 export 再 import 到目標端，使用這種方式就可以避免掉帳號密碼的問題，執行 expdp 時使用 full=y 與 include=db_link 就可以單獨匯出 Database Link :

雖然 Database Link 可以跨越資料庫來查詢資料，但畢竟它還是透過網路連線把資料撈回來，建議不要使用在太複雜的 SQL 查詢上，例如使用 DB Link 進行過多的 join 或是子查詢，這種情況當效能出現問題時會不容易進行優化。

 

9.10 Sequence 與 Synonym

Sequence 就是一組序列號，每次取號後自動增加，一般用來作為需要照數列排序的地方，或者是作為欄位唯一值的設定，例如訂單編號，當接收到一筆訂單之後會賦予這筆訂單一個編號，格式可能是 “PO0001” ，在 “PO” 後面賦予一個數值，此時使用 Sequence 取號就可以代表訂單下單的順序，以及確保每一筆訂單編號都是唯一值。 Sequence 是一個獨立的 Object ，使用 create sequence 來建立，例如建立一個名為 po_seq 的 Sequence :

SQL> create sequence po_seq minvalue 0 maxvalue 1000 increment by 1 start with 1 cache 20 order  cycle;

其中:

• minvalue : 設定此 sequence 的最小值，預設為 nominvalue ，從 1 開始。

• maxvalue : 設定此 sequence 的最大值，預設為 nomaxvalue ，最大可達 10 的 28 次方減 1 。

• increment by : 表示此 sequence 每個序列號增加多少，設定為 1 表示每個序列號增加 1 ，即 1 、 2 、 3 … 如此下去。

• start with : 設定此 sequence 從第幾號開始增加。

• cache : 設定 sequence 事先要把多少個序列號放入 cache 中，設定為 20 表示先將 20 個號碼放入 cache ，從 cache 取號有助於提升效能。對於 RAC 環境來說， cache 的設定是必須的，因為 RAC 透過兩個 Instance 同時取號的話很容易會發生 enq: SQ – contention 的等待，透過 cache 的設定可以避免此等待事件的發生，建議在 RAC 環境下 cache 設定為 100 以上。

• order : 此設定是確保 sequence 序列號是按照數列增加的，在有設定 cache 的 RAC 環境下，照時間的先後所取出來的序列號可能是跳號的，例如 cache 設置為 20 ，那麼第一個 Instance 會先 cache 1 ~ 20 的號碼；而第二個 Instance 會 cache 21 ~ 40 的號碼，如果從 Instance 1 與 Instance 2 輪流來取號的話，所產生的順序可能為 1 、 2 、 21 、 3 、 22 … 產生跳號的情況。使用 order 的設定是確保在這種有 cache 的情況下也不會跳號，即便從不同的 Instance 取號，取出的順序仍然會是照順序的 1 、 2 、 3 … 。雖然 order 的設定可以確保不會跳號，但效能上會比 noorder 還來的差，有些系統的序列號只是要確保此欄位的資料不會重複，並沒有一定要照順序的要求，那麼就可以使用 noorder 來提升效能。這個項目預設是 noorder 。

• cycle : 設定當 sequence 達到 maxvalue 之後，是否要回到從第一個號碼開始，預設為 nocycle 。

Sequence 取號的方式是使用 nextval 函式，例如:

SQL> select po_seq.nextval from dual;         (直接取號) SQL> insert into products values (po_seq.nextval, sysdate);       (insert 的時候帶入 sequence 序列號)

這邊要注意的是，只要進行過 nextval 取號， Sequence 就無法回到上一號。

Sequence 的資訊可以透過 dba_sequences 或是 user_sequences 來查詢。如果有 Sequence 重建的需求時，可以利用 dba_sequences 抓出每個 Sequence 目前的序列號以及相關的屬性，進而組合出 Sequence 建立的語法，例如抓出 HR 與 SCOTT 兩個使用者的所有 Sequence 設定:

SQL> select 'drop sequence '||sequence_owner||'.'||sequence_name||';'||chr(10)||' create sequence '||sequence_owner||'.'||sequence_name||' minvalue '||min_value||' maxvalue '||max_value||' increment  by '||increment_by||' start with '||to_char(last_number)||case when cache_size > 0 then ' cache '||cache_size else ' nocache ' end||case order_flag when 'Y' then 'order ' else ' noorder ' end||case cycle_flag when 'Y' then ' cycle ' else ' nocycle ' end||';' as "seq_create"   from dba_sequences where sequence_owner in ('HR','SCOTT') order by sequence_owner;

Synonym 代表的是同義字，當使用者要存取其他使用者的物件時，必須使用 schema.object_name ，若是建立了同義字，那麼就可以簡化語法，物件前面不需要在加上 “schema.” 。 Synonym 可以分為 Public Synonym 與 Private Synonym ， Public Synonym 表示為公用的同義字，必須具有 DBA 權限才可以建立，所有使用者都可以使用 Public Synonym ， 例如建立一個 Public Synonym emp 來代表 hr.employees 這個 Table :

SQL> create public synonym emp for hr.employees;

Synonym 的定義也可以使用 db link ，例如建立 Public Synonym jobs 代表 HR 資料庫的 open_job 這個 Table :

SQL> create public synonym jobs for open_job@hr;

Private Synonym 表示同義字只有當前的使用者可以使用，也就是使用者將自己的某一個物件設定為另外一個別名而已，例如 hr 這個使用者建立一個 Private Synonym dept 來代表 departments 這個 Table:

SQL> create synonym dept for departments;

查詢 Synonym 相關資訊可以由 dba_synonyms 或 user_synonyms 來得知。