[Oracle] 開發 & 使用 Stored Procedures

概觀

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

在 PL/SQL 中，程式設計的基本單元為獨立的 stored procedure、function 或是 package：

stored procedure：沒有回傳值。
function：有回傳值。
package：類似 Java 中的 package 或是 .NET 中的 namespace 一樣，可以用來區隔相同名稱的 stored procedure 或是 function。

Procedures & Functions

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

宣告部份(declarative-part)
此部份所使用的關鍵字為「DECLARE」，用來定義變數與常數之用。
執行部份(executable-part)
此部份是程式執行的部份，會使用關鍵字「BEGIN」與「END」包住來表示開始與結束。
例外處理部份(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 根據其所在之處，有三種不同的有效範圍：

standalone procedure(function)：不包含於任何的 package 或是 subprogram 中，屬於 schema-level。
packaged subprogram：定義於 package 中，由 package 進行區隔。
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…LOOP 與 LOOP…EXIT WHEN 兩種不同的用法，以下就不寫這麼長的範例了，直接給 syntax，要會用應該也不難了!

WHILE…LOOP

WHILE condition 
LOOP
    ……
END LOOP;

LOOP…EXIT WHEN

LOOP
    ……
    EXIT WHEN condition
    ……
EXIT LOOP

標籤

2013年10月11日星期五

Oracle 預儲程序

[Oracle] 開發 & 使用 Stored Procedures

沒有留言:

張貼留言

標籤

2013年10月11日 星期五

Oracle 預儲程序

[Oracle] 開發 & 使用 Stored Procedures

沒有留言:

張貼留言

2013年10月11日星期五