create or replace trigger tri_fill_datetime_to_message
--只有在更新資料後才需要加入日期時間資訊
before update
on mytable
for each row
begin
--當 action 更新的值為 1 時，在執行以下動作
if :new.action = 1 then
--設定新資料的日期時間資訊
:new.log_date := to_char(sysdate, 'YYYYMMDD');
:new.log_time := to_char(sysdate, 'HH24MISS');
:new.log_datetime := sysdate;
end if;
end tri_fill_datetime_to_message;
前言
現在的程式語言中，幾乎都提供了例外處理的功能，當然 PL/SQL 也不能例外，除了提供例外處理的功能外，還可以讓使用者自訂例外喔!
Oracle 內建的例外種類
其實在 Oracle 當中就已經內建了許多例外的定義了，例如：NO_DATA_FOUND(當 SELECT INTO 的語法沒有回傳任何資料列時會觸發)、CASE_NOT_FOUND(當條件中沒有任何一項符合且沒有 ELSE 選項時會觸發)，詳細的內建例外說明，可以參考官方網站的文件。
而例外的使用方式如下：
以下用個範例說明如何使用 Oracle 的內建範例：
自訂例外
自訂的例外在宣告上很有彈性，可以放在 subprogram 中，也可以放在 package specification 或是 package body 中，完全隨使用者方便。
而自訂例外的語法如下：
接著是自訂例外觸發條件與例外處理的範例：
何謂 Collection ?
之前有提到 record 型態，可以讓使用者自訂不同的型別集合；而 collection 則是相同資料型態的集合，簡單來說可以將它視為 Array。
collection 是用來存放一群相同型態的資料用，由於可以直接透過索引(index)或是鍵值(key)直接存取，因此速度很快；而在 Oracle 中提供三種不同的 collection：
這裡介紹較為常用的 index-by table，其存在的型態可以是 Array(用索引值取得值)，或是 key-value 的集合(用 key 取得值)。
為何 Collection 效能優越?
要了解 collection 為什麼要能優越，就必須先知道 collection 是如何運作的，使用 collection 的方式大致如下：
從上面可以知道 collection 是與 cursor 搭配使用的，將資料從資料庫取出後便暫存於記憶體中，因此處理起來的效能才會優越。
使用範例
以下用範例說明：
何謂 Cursor ?
cursor 為 PL/SQL 中內建的一種 pointer，可用來擷取整個資料集合，並讓使用者可以逐筆資料的進行存取。
在 Oracle 中，cursor 分為兩種，分別是：
預設中，Oracle 提供了上限 50 個 cursor 可供使用，但若因為開發需求而導致同時需要更多的 cursor 時，可以透過調整起始參數「OPEN_CURSOR」來達成。
使用 Explicit Cursor
每個 explicit cursor 都會有以下幾個屬性作為程式控制之用：
為了暫時存放從 cursor 擷取出來的資料列，必須定義一個暫存的變數，而這個變數的型態通常為一列資料(row data)。
以下介紹 cursor 使用的語法： (更詳細的語法可以參考官方網站文件)
DECLARE
--宣告 cursor，並指定擷取資料集合的 SQL 語法(query_definition)
CURSOR cursor_name type IS query_definition;
--開啟 cursor(此時會根據宣告時中定義的 SQL 語法取得資料集合)
OPEN cursor_name
LOOP
--取得 cursor 目前所在的資料列(必須使用暫存變數來儲存取得的資料列)
FETCH record INTO buffer_var;
--當 cursor 已經將資料集合的資料巡覽完畢
EXIT WHEN cursor_name%NOTFOUND;
--各式各樣的處理工作
...;
END LOOP;
--關閉 cursor(之後 cursor 就不能再繼續使用)
CLOSE cursor_name;
接著介紹使用範例：
--PROCEDURE 的實作內容
PROCEDURE eval_department(department_id IN employees.department_id%TYPE) AS
--宣告暫存變數，儲存 cursor data
emp_record employees%ROWTYPE;
--宣告變數，檢查所有員工是否需要重新評估績效
all_evals BOOLEAN;
--今日日期
today DATE;
--宣告 cursor
CURSOR emp_cursor IS
SELECT *
FROM employees e
WHERE department_id = e.department_id;
BEGIN
--根據月份判斷是否重新計算績效
today := SYSDATE;
IF(EXTRACT(MONTH FROM today) < 6) THEN
--僅針對新進員工計算績效
all_evals := FALSE;
ELSE
--全部重新計算
all_evals := TRUE;
END IF;
--開啟 cursor
OPEN emp_cursor;
DBMS_OUTPUT.PUT_LINE('Determining evaluations necessary in department #' || department_id);
LOOP
--透過 cursor 取得一筆資料並存入暫存變數
FETCH emp_cursor INTO emp_record;
--若 cursor 已將資料取完，則跳出迴圈
EXIT WHEN emp_cursor%NOTFOUND;
IF all_evals THEN
--呼叫之前宣告的 procedure
add_eval(emp_record.employee_id, today);
ELSIF(eval_frequency(emp_record.employee_id) = 2) THEN
--呼叫之前宣告的 procedure
add_eval(emp_record.employee_id, today);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Processed ' || emp_cursor%ROWCOUNT || ' records.');
--關閉 cursor
CLOSE emp_cursor;
END eval_department;
使用 Cursor Variable：REF Cursor
從上面 cursor 的使用方式可以知道，由於 cursor 的使用必須寫在 subprogram 中，並無法在程式中動態的變更所擷取的資料，因此在使用上是靜態的。
然而，若要在程式中動態的改變 cursor 所擷取的資料，或是將 cursor 所取到的資料集合當作參數傳到另一個 subprogram 中，就必須改用 REF Cursor 了!
什麼是 cursor variable 呢? 簡單來說，就是「儲存指向實際 cursor 的指標的變數」，以下用一張簡單的圖來說明：
其中假設實際 cursor 所在的記憶體位址為 0XBC34AD，而 cursor variable 儲存的就是 0XBC34AD 這個記憶體位址，而非 cursor 本身。
也由於 cursor variable 有如此特性，因此假設同時有多個 cursor 同時開啟，就可以將不同的記憶體位址指定給 cursor variable，這樣就可以讓 cursor variable 很快速的取得不同 cursor 從資料庫所擷取到的不同資料。
【附註】cursor 將資料從資料庫取出後，就存放於記憶體中，接著後續的處理就會很快速。
瞭解 cursor variable 與 ref cursor 的關係後，就先來說明使用的語法：
DECLARE
--定義 ref cursor 與回傳值的資料型態(return_type)，稱為 strongly typed
--也可不定義回傳的資料型態，稱為 weakly typed
TYPE cursor_type IS REF CURSOR RETURN return_type;
--定義 cursor variable(為 cursor pointer，指向實際的 cursor)
cursor_variable cursor_type;
--定義變數儲存 cursor 回傳的資料
single_record return_type;
--使用 cursor variable 開啟 cursor，並依照 query_definition 從資料庫取得資料
OPEN cursor_variable FOR query_definition;
LOOP
--取得一筆資料，暫存入 single_record 中
FETCH record INTO single_record;
--資料集合已經巡覽完畢，離開迴圈
EXIT WHEN cursor_name%NOTFOUND;
--任何其他實作置於此處
...;
END LOOP;
--關閉 cursor
--注意! 不是 cursor_variable 喔!
CLOSE cursor_name;
有一點需要注意到的是，在定義 ref cursor 時不一定需要同時定義回傳值(return_type)。
若是有定義回傳值，則 cursor 取回來的資料為 strongly typed；反之若是沒有定義回傳值，cursor 取回的資料則為 weakly typed。
若是 strongly typed，若是在程式撰寫中發生與定義型別不符的錯誤，就可以在編譯(compile)時期發現；而若是 weakly typed，由於沒有定義回傳型別，因此在使用時可以給定任何型別，若是使用上小心點，似乎也提供了蠻大的彈性。
REF CURSOR 使用範例
首先先將 ref cursor 與其他 subprogram 的定義放到 package specification 中：
CREATE OR REPLACE PACKAGE EMP_EVAL AS
-- 宣告 PROCEDURE 與 FUNCTION
PROCEDURE eval_department(department_id IN employees.department_id%type);
--計算所有人的績效
PROCEDURE eval_everyone;
--定義 ref cursor
TYPE emp_refcursor_type IS REF CURSOR RETURN employees%ROWTYPE;
END EMP_EVAL;
接著是其他 subprogram 的實作部份：
--PROCEDURE 的實作內容
PROCEDURE eval_department(department_id IN employees.department_id%TYPE) AS
--宣告 cursor variable (型態為 package specification 中定義的 emp_refcursor_type)
emp_cursor emp_refcursor_type;
--目前正在處理的部門ID
department_curr departments.department_id%TYPE;
BEGIN
department_curr := department_id;
FOR loop_c IN 1..3
LOOP
--開啟 cursor
OPEN emp_cursor FOR
SELECT *
FROM employees e
WHERE e.department_id = department_curr;
eval_loop_control(emp_cursor);
DBMS_OUTPUT.PUT_LINE('Determining necessary evaluations in department #' || department_curr);
--關閉 cursor
CLOSE emp_cursor;
department_curr := department_curr + 10;
END LOOP;
END eval_department;
PROCEDURE add_eval(emp_record IN employees%ROWTYPE, today IN DATE) AS
BEGIN
--新增資料
INSERT INTO evaluations VALUES( evaluations_seq.NEXTVAL,
emp_record.employee_id,
today,
emp_record.job_id,
emp_record.manager_id,
emp_record.department_id,
0);
END add_eval;
PROCEDURE eval_loop_control(emp_cursor IN emp_refcursor_type) AS
--定義暫存變數，儲存透過 cursor 擷取的資料
emp_record employees%ROWTYPE;
--宣告變數，檢查所有員工是否需要重新評估績效
all_evals BOOLEAN;
--今日日期
today DATE;
BEGIN
--取得今天的日期
today := SYSDATE;
IF(EXTRACT(MONTH FROM today) < 6) THEN
all_evals := FALSE;
ELSE
all_evals := TRUE;
END IF;
LOOP
--透過 cursor 擷取一筆資料
FETCH emp_cursor INTO emp_record;
--當 cursor 已經將資料擷取完畢，跳離迴圈
EXIT WHEN emp_cursor%NOTFOUND;
IF all_evals THEN
add_eval(emp_record, today);
ELSIF(eval_frequency(emp_record.employee_id) = 2) THEN --呼叫其他 subprogram，先忽略這邊
add_eval(emp_record, today);
END IF;
END LOOP;
END eval_loop_control;
PROCEDURE eval_everyone AS
--宣告 cursor variable (型態為 package specification 中定義的 emp_refcursor_type)
emp_cursor emp_refcursor_type;
BEGIN
--開啟 cursor，取得資料集合
OPEN emp_cursor FOR SELECT * FROM employees;
DBMS_OUTPUT.PUT_LINE('Determining the number of necessary evaluations');
--呼叫 subprogram，傳入 cursor variable
eval_loop_control(emp_cursor);
DBMS_OUTPUT.PUT_LINE('Processed ' || emp_cursor%ROWCOUNT || ' records.');
--關閉 cursor (上面呼叫的 subprogram 並未把 cursor 關閉)
CLOSE emp_cursor;
END eval_everyone;
何謂 RECORD ?
RECORD 是由一群各自儲存於不同欄位的相關資料所組合而成的群組，每個資料都有各自的名稱與資料型態。
使用 RECOED 的好處在於當 RECORD 被當作參數丟入 subprogram 處理的時候，或是同時包含多個不同 table 中的不同欄位時，處理的效率是很好的。
建立 RECORD 資料型態
首先，先了解建立 RECORD 型態的語法：RECORD 的使用
當 RECORD 定義好後就可以使用了，以下為使用範例：概觀
PL/SQL 是在資料庫端執行的，因此若是將資料庫處理的程式都寫成 stored procedure，撰寫程式時就不需要對資料庫進行頻繁的連線與資料往來，如此一來效能也會相對有所提昇。
在 PL/SQL 中，程式設計的基本單元為獨立的 stored procedure、function 或是 package：
Procedures & Functions
procedure 與 function 是由三個主要部份所組成，分別是：
建立 Stored Procedures & Functions
建立 stored procedure 的語法如下：
CREATE OR REPLACE procedure_name(arg1 data_type, ...)
AS
BEGIN
....
END procedure_name;
以下來個建立 procedure 的範例：(官方網站使用說明)
CREATE OR REPLACE PROCEDURE ADD_EVALUATION
(
EVALUATION_ID IN NUMBER,
EMPLOYEE_ID IN NUMBER,
EVALUATION_DATE IN DATE,
JOB_ID IN VARCHAR2,
MANAGER_ID IN NUMBER,
DEPARTMENT_ID IN NUMBER
)
AS
BEGIN
NULL;
END ADD_EVALUATION;
再來個建立 function 的範例：
CREATE OR REPLACE FUNCTION CALCULATE_SCORE
(
CAT IN VARCHAR2,
SCORE IN NUMBER,
WEIGHT IN NUMBER
)
RETURN NUMBER
AS
BEGIN
RETURN NULL;
END CALCULATE_SCORE;
刪除 Stored Procedures & Functions
語法更簡單了：
DROP PROCEDURE ADD_EVALUATION;
Packages
procedure 與 function 根據其所在之處，有三種不同的有效範圍：
將 procedure 或是 function 定義於 package 之下有什麼好處呢? 若是 standalone 的 procedure(function)，所能接收的參數型態僅能是向量型態(NUMBER、VARCHAR2、DATE)的變數，若是比較複雜的結構像是 RECORD 就沒辦法了；但若是定義在 package 中就可以囉!
而 package 包含了兩個部份，分別是定義(specification)的部份與實作(body)的部份；而整個作法很類似 C 語言中針對 function 的定義與處理，也就是「在 specification 中定義公用的 procedure(or function) 以及相關參數，並在 body 中實作定義」。
關於 package 的詳細使用方式，可以參考官方網站的文章。
建立 Package
範例(specification)：
--定義 PACKAGE
CREATE OR REPLACE PACKAGE EMP_EVAL
AS
-- 宣告 PROCEDURE 與 FUNCTION
PROCEDURE EVAL_DEPARTMENT(DEPARTMENT_ID IN NUMBER);
FUNCTION CALCULATE_SCORE(EVALUATION_ID IN NUMBER, PERFORMANCE_ID IN NUMBER) RETURN NUMBER;
END EMP_EVAL;
範例(body)：
--設定 PACKAGE 的實作內容
CREATE OR REPLACE PACKAGE BODY EMP_EVAL
AS
--PROCEDURE 的實作內容
PROCEDURE EVAL_DEPARTMENT(DEPARTMENT_ID IN NUMBER)
AS
BEGIN
--實作於此處
NULL;
END EVAL_DEPARTMENT;
--FUNCTION 的實作內容
FUNCTION CALCULATE_SCORE(EVALUATION_ID IN NUMBER, PERFORMANCE_ID IN NUMBER) RETURN NUMBER
AS
BEGIN
--實作於此處
RETURN NULL;
END CALCULATE_SCORE;
END EMP_EVAL;
刪除 Package
DROP PACKAGE EMP_EVAL;
變數(variable)與常數(constant)的定義
撰寫 procedure 與 function 的優點是在 PL/SQL 中，可以使用由 Oracle 提供，但不能用於 table 欄位定義的資料型態，像是 BOOLEAN、RECORD、REF … 等等。
以下用個簡單範例說明 variable 與 constant 的使用方式：
--設定 PACKAGE 的實作內容
CREATE OR REPLACE PACKAGE BODY EMP_EVAL
AS
--PROCEDURE 的實作內容
PROCEDURE EVAL_DEPARTMENT(DEPARTMENT_ID IN NUMBER)
AS
BEGIN
--實作於此處
NULL;
END EVAL_DEPARTMENT;
--FUNCTION 的實作內容
FUNCTION CALCULATE_SCORE(EVALUATION_ID IN NUMBER, PERFORMANCE_ID IN NUMBER) RETURN NUMBER
AS
--變數(variable)與常數(constant)宣告於此處
n_score NUMBER(1, 0);
--VARIABLE
n_weight NUMBER;
--VARIABLE
max_score CONSTANT NUMBER(1, 0) := 9;
--CONSTANT
max_weight CONSTANT NUMBER(8, 8) := 1;
BEGIN
--實作於此處
RETURN NULL;
END CALCULATE_SCORE;
END EMP_EVAL;
將變數(or 常數)與資料表欄位型態連結
若是要將 variable 或是 constant 與 table 中欄位的型態設定為相同，為了避免欄位型態的改變而造成程式發生錯誤，可透過「%TYPE」關鍵字來處理，以下舉個例子：
--設定 PACKAGE 的實作內容
CREATE OR REPLACE PACKAGE BODY EMP_EVAL
AS
--PROCEDURE 的實作內容
PROCEDURE EVAL_DEPARTMENT(DEPARTMENT_ID IN NUMBER)
AS
BEGIN
--實作於此處
NULL;
END EVAL_DEPARTMENT;
--FUNCTION 的實作內容
--將 EVALUATION_ID 的型態與 SCORES 中的 EVALUATION_ID 欄位型態連結
--將 PERFORMANCE_ID 的型態與 SCORES 中的 PERFORMANCE_ID 欄位型態連結
FUNCTION CALCULATE_SCORE(EVALUATION_ID IN SCORES.EVALUATION_ID%TYPE, PERFORMANCE_ID IN SCORES.PERFORMANCE_ID%TYPE) RETURN NUMBER
AS
--變數(variable)與常數(constant)宣告於此處
--將 n_score 的型態與 SCORES 中的 SCORE 欄位型態連結
--將 n_weight 的型態與 PERFORMANCE_PARTS 中的 WEIGHT 欄位型態連結
--常數的部分也是連結到 TABLE 的欄位型態
n_score SCORES.SCORE%TYPE;
--VARIABLE
n_weight PERFORMANCE_PARTS.WEIGHT%TYPE;
--VARIABLE
max_score CONSTANT SCORES.SCORE%TYPE := 9;
--CONSTANT
max_weight CONSTANT PERFORMANCE_PARTS.WEIGHT%TYPE := 1;
BEGIN
--實作於此處
RETURN NULL;
END CALCULATE_SCORE;
END EMP_EVAL;
如此一來，若是 SCORE 中的 EVALUATION_ID、PERFORMANCE_ID 的型態改變，程式中定義的 variable 或是 constant 的型態也會跟著改變囉。
設定變數值
設定變數值有很多方式，以下舉例：
直接指定或是經由計算：
FUNCTION CALCULATE_SCORE(EVALUATION_ID IN SCORES.EVALUATION_ID%TYPE, PERFORMANCE_ID IN SCORES.PERFORMANCE_ID%TYPE) RETURN NUMBER
AS
n_score SCORES.SCORE%TYPE;
--VARIABLE
n_weight PERFORMANCE_PARTS.WEIGHT%TYPE;
--VARIABLE
running_total NUMBER := 0;
--計算中使用
max_score CONSTANT SCORES.SCORE%TYPE := 9;
--CONSTANT
max_weight CONSTANT PERFORMANCE_PARTS.WEIGHT%TYPE := 1;
BEGIN
--實作於此處
running_total := max_score * max_weight;
RETURN running_total;
END CALCULATE_SCORE;
從資料庫中取值：
FUNCTION CALCULATE_SCORE(EVALUATION_ID IN SCORES.EVALUATION_ID%TYPE, PERFORMANCE_ID IN SCORES.PERFORMANCE_ID%TYPE) RETURN NUMBER
AS
n_score SCORES.SCORE%TYPE;
--VARIABLE
n_weight PERFORMANCE_PARTS.WEIGHT%TYPE;
--VARIABLE
running_total NUMBER := 0;
--計算中使用
max_score CONSTANT SCORES.SCORE%TYPE := 9;
--CONSTANT
max_weight CONSTANT PERFORMANCE_PARTS.WEIGHT%TYPE := 1;
BEGIN
--實作於此處
SELECT s.SCORE INTO n_score
--設定 n_score 的值
FROM SCORES s
WHERE EVALUATION_ID = s.EVALUATION_ID;
SELECT p.weight INTO n_weight
--設定 n_weight 的值
FROM PERFORMANCE_PARTS p
WHERE PERFORMANCE_ID = p.PERFORMANCE_ID;
--計算 running_total
running_total := max_score * max_weight;
--回傳值
RETURN running_total;
END CALCULATE_SCORE;
從資料庫取值，並將新增一筆資料至另外一個 table
procedure add_eval(employee_id in employees.employee_id%type, today in date)
as
job_id employees.job_id%type;
manager_id employees.manager_id%type;
department_id employees.department_id%type;
begin
--從 employees 表格中取得值
select e.job_id into job_id
from employees e
where employee_id = e.employee_id;
select e.manager_id into manager_id
from employees e
where employee_id = e.employee_id;
select e.department_id into department_id
from employees e
where employee_id = e.employee_id;
--新增資料
insert into evaluations values(evaluations_seq.NEXTVAL, employee_id, today, job_id, manager_id, department_id, 0);
end add_eval;
流程控制
IF 與 CASE 的搭配範例：
function eval_frequency(employee_id in employees.employee_id%type) return pls_integer
as
hire_date employees.hire_date%type;
today employees.hire_date%type;
eval_freq pls_integer;
job_id employees.job_id%type;
begin
--設定今天日期
select sysdate into today from dual;
--取得員工開始工作日期
select e.hire_date into hire_date from employees e where e.employee_id = employee_id;
--判斷年資是否大於十年
if((hire_date + (interval '120' month)) < employee_id =" employee_id;">
另外還有 WHILE…LOOP 與 LOOP…EXIT WHEN 兩種不同的用法，以下就不寫這麼長的範例了，直接給 syntax，要會用應該也不難了!
WHILE…LOOP
WHILE condition
LOOP
……
END LOOP;
LOOP…EXIT WHEN
LOOP
……
EXIT WHEN condition
……
EXIT LOOP
