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