2008年12月17日 星期三

[Oracle] 開發 & 使用 Stored Procedures

概觀

PL/SQL 是在資料庫端執行的,因此若是將資料庫處理的程式都寫成 stored procedure,撰寫程式時就不需要對資料庫進行頻繁的連線與資料往來,如此一來效能也會相對有所提昇。

在 PL/SQL 中,程式設計的基本單元為獨立的 stored procedurefunction 或是 package:

  1. stored procedure:沒有回傳值。

  2. function:有回傳值。

  3. package類似 Java 中的 package 或是 .NET 中的 namespace 一樣,可以用來區隔相同名稱的 stored procedure 或是 function。

Procedures & Functions

procedure 與 function 是由三個主要部份所組成,分別是:

  1. 宣告部份(declarative-part)
    此部份所使用的關鍵字為「DECLARE」,用來定義變數與常數之用。

  2. 執行部份(executable-part)
    此部份是程式執行的部份,會使用關鍵字「BEGIN」與「END」包住來表示開始與結束。

  3. 例外處理部份(exception-handling part)
    透過關鍵字「EXCEPTION」來處理程式中可能會發生的錯誤情況。

建立 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 根據其所在之處,有三種不同的有效範圍:

  1. standalone procedure(function):不包含於任何的 package 或是 subprogram 中,屬於 schema-level。

  2. packaged subprogram:定義於 package 中,由 package 進行區隔。

  3. local subprogram:定義於 subprogram 或是 PL/SQL 程式碼區段中,這一類的程式無法從外界被呼叫,類似 Inner(Nested) Class。

將 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…LOOPLOOP…EXIT WHEN 兩種不同的用法,以下就不寫這麼長的範例了,直接給 syntax,要會用應該也不難了!

WHILE…LOOP

WHILE condition
LOOP
……
END LOOP;

LOOP…EXIT WHEN


LOOP
……
EXIT WHEN condition
……
EXIT LOOP

2 則留言:

  1. "宣告部份(declarative-part)
    此部份所使用的關鍵字為「DECLARE」,用來定義變數與常數之用。"

    請問這邊應該是 IS / AS,不是 DECLARE 吧??

    回覆刪除