緣起
幾個月前, 有看到一篇針對 Oracle PL/SQL 進行重構的文章, 改用類似 Dependency Injection 的方式來進行設計, 直到最近才有時間撰寫網誌, 留下記錄.
該作者主要有2篇文章, 筆者在參考文件, 作了一些摘要說明. 真的很難想像 Oracle PL/SQL 可以實作一些 Design Pattern.
以下內容, 係筆者依目前工作上接觸到的產業進行情境設計.
完整程式範例, 筆者放在 GitHub, 請由此下載.
假設情境
以 證券後台系統 補單功能 的 現股賣出 交易為例, 其 交割金額 的邏輯如下:
(1) 價金(TradeAmt) = 成交價格 * 成交股數
(2) 交易稅(TradeTax) = 價金 * 千分之 3
==>這個是要給政府的
(3) 手續費(CommFee) = 價金 * 千分之 1.425
==> 這個是券商的收入, 是券商可操控的部份,可能會依一些條件, 而有不同的費率
==> 本例只依現場單或電子單, 而有不同的費率
==> 現場單: 千分之1.425 (CommFeeRate_NonEC)
==> 電子單: 千分之1.325 (CommFeeRate_EC)
(4) 交割金額(SettleAmt) = 價金 - 交易稅 - 手續費
==> 這個才是真正進入客戶帳戶的錢
本文以手續費為例, 假設各家券商的手續費率的商業邏輯都相同, 只有費率不同.
第一版程式
1. 摘要說明: 參考架構圖
(1) 共有2個 Package (可以視為 C# 的物件):
* pkgDIMain_v01 (主程式模組) : 此 Package 為外部程式呼叫的入口, 有一個 plCalcSettleAmtForCashSell() 方法, 供外部呼叫
* pkgDIPayment_v01 (計費模組): 此 Package 主要負責交易稅及手續費的計算.
2. 問題點如下:
(1) 手續費率寫死了, 沒有彈性
3. 架構圖如下:
 |
| 第一版程式架構圖 |
CREATE OR REPLACE PACKAGE pkgDIMain_v01
IS
/*******************************************************************************
NAME: plCalcSettleAmtForCashSell
PURPOSE: 進行現股賣出交易的應付金額計算 (以券商角度而言, 是要支付金額給客戶)
PARAMATER(S):
現股賣出的交易可以分為3段
(1) 價金(TradeAmt) = 成交價格 * 成交股數
(2) 交易稅(TradeTax) = 價金 * 千分之 3 //這個是要給政府的
(3) 手續費(CommFee) = 價金 * 千分之 1.425 //這個是券商的收入, 是券商可操控的部份,
//可能會依一些條件, 而有不同的費率
//本例只依現場單或電子單, 而有不同的費率
//現場單: 千分之1.425 (CommFeeRate_NonEC)
//電子單: 千分之1.325 (CommFeeRate_EC)
(4) 交割金額(SettleAmt) = 價金 - 交易稅 - 手續費 //這個才是真正進入客戶帳戶的錢
Mode Name Description
--------- ------------ -----------------------------------------------
IN pi_TradePrz 成交價格
IN pi_TradeQty 成交股數
OUT NOCOPY po_ReturnMsg 交割金額
*******************************************************************************/
PROCEDURE plCalcSettleAmtForCashSell (
pi_ChannelKind IN VARCHAR2, --通路種類 ('1': 現場, '2':電子)
pi_TradePrz IN NUMBER, --成交價格
pi_TradeQty IN NUMBER, --成交股數
po_SettleAmt OUT NOCOPY NUMBER --交割金額
);
END pkgDIMain_v01;
/
CREATE OR REPLACE PACKAGE BODY pkgDIMain_v01
IS
/*******************************************************************************
NAME: plCalcSettleAmtForCashSell
PURPOSE: 進行現股賣出交易的應付金額計算 (以券商角度而言, 是要支付金額給客戶)
*******************************************************************************/
PROCEDURE plCalcSettleAmtForCashSell(
pi_ChannelKind IN VARCHAR2, --通路種類 ('1': 現場, '2':電子)
pi_TradePrz IN NUMBER, --成交價格
pi_TradeQty IN NUMBER, --成交股數
po_SettleAmt OUT NOCOPY NUMBER --交割金額
)
IS
lv_TradeAmt NUMBER(16,2); --價金
lv_TradeTax NUMBER(16,2); --交易稅
lv_CommFee NUMBER(16,2); --手續費
BEGIN
lv_TradeAmt := pi_TradeQty * pi_TradePrz;
lv_TradeTax := pkgDIPayment_v01.fcGetTradeTax(lv_TradeAmt);
lv_CommFee := pkgDIPayment_v01.fcGetCommeFee(pi_ChannelKind, lv_TradeAmt);
po_SettleAmt := lv_TradeAmt - lv_TradeTax - lv_CommFee;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
RAISE;
END plCalcSettleAmtForCashSell;
BEGIN
-- Initialization
NULL;
END pkgDIMain_v01;
/
CREATE OR REPLACE PACKAGE pkgDIPayment_v01
IS
/*******************************************************************************
NAME: fcGetTradeTax
PURPOSE: 取得交易稅
PARAMATER(S):
Mode Name Description
--------- ------------ -----------------------------------------------
IN pi_TradeAmt 價金
RETURN -- 交易稅
*******************************************************************************/
FUNCTION fcGetTradeTax(
pi_TradeAmt IN NUMBER --價金
)
RETURN NUMBER;
/*******************************************************************************
NAME: fcGetCommFee
PURPOSE: 取得手續費
PARAMATER(S):
Mode Name Description
--------- ------------ -----------------------------------------------
IN pi_ChannelKind 通路種類 ('1': 現場, '2':電子)
IN pi_TradeAmt 價金
RETURN -- 手續費
*******************************************************************************/
FUNCTION fcGetCommeFee(
pi_ChannelKind IN VARCHAR2, --通路種類 ('1': 現場, '2':電子)
pi_TradeAmt IN NUMBER --價金
)
RETURN NUMBER;
END pkgDIPayment_v01;
/
CREATE OR REPLACE PACKAGE BODY pkgDIPayment_v01
IS
/*******************************************************************************
NAME: fcGetTradeTax
PURPOSE: 取得交易稅
*******************************************************************************/
FUNCTION fcGetTradeTax(
pi_TradeAmt IN NUMBER --價金
)
RETURN NUMBER
IS
lv_Result NUMBER(16,2);
BEGIN
lv_Result := pi_TradeAmt * 3 / 1000;
RETURN lv_Result;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
RAISE;
END fcGetTradeTax;
/*******************************************************************************
NAME: fcGetCommFee
PURPOSE: 取得手續費
*******************************************************************************/
FUNCTION fcGetCommeFee(
pi_ChannelKind IN VARCHAR2, --通路種類 ('1': 現場, '2':電子)
pi_TradeAmt IN NUMBER --價金
)
RETURN NUMBER
IS
lv_Result NUMBER(16,2);
BEGIN
lv_Result := 0;
IF pi_ChannelKind = '1' THEN
lv_Result := pi_TradeAmt * 1.425 / 1000;
ELSE
lv_Result := pi_TradeAmt * 1.325 / 1000;
END IF;
RETURN lv_Result;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
RAISE;
END fcGetCommeFee;
BEGIN
-- Initialization
NULL;
END pkgDIPayment_v01;
/
第二版程式
1. 摘要說明:
或許各位有想到, 可以把手續費率建到檔案, 亦即多一個 config table; 或者增加一個 Package 存放常數 (suppose pkgCommon_v02), 但這樣會造成 pkgDIPayment 還是相依於那個 config table 或 pkgCommon ; 也就是說, 當要部署 pkgDIPayment_v02 時, 那個 config table 也要跟著一併部署.
2. 問題點如下:
(1) 難以給其它系統使用, 因為相依於某個 config table 或 Package
3. 架構圖如下: 將手續費率放在一個 pkgCommon_v02 的 Package 裡, 供 pkgDIPayment_v02 使用
 |
| 第二版程式架構 |
4. 程式碼的部份, 由於不想佔用篇幅, 有興趣者, 可以自行下載 (本文的開頭, 有提供下載的連結)
第三版程式
1. 摘要說明:
換個方向, 假設我們可以由外部填值給 pkgDIPayment_v03, 而不是由 pkgPayment_v03 自己去取得, 那麼, 是否就可以增加 pkgDIPayment_v03 的可移植性了呢? 似乎有點 IOC (Inverse of Control) 的味道了?!
2. 架構圖如下:
 |
| 第三版程式架構圖 |
3. 程式碼如下:
(1) 新增一個 CONFIGURATION_VALUES table, 並建立 2 筆資料.
-- 建立資料表
CREATE TABLE CONFIGURATION_VALUES
(
PACKAGE_NAME VARCHAR2(30) not null,
PROPERTY VARCHAR2(50) not null,
VALUE VARCHAR2(4000)
)
;
-- 建立測試資料
INSERT INTO CONFIGURATION_VALUES (PACKAGE_NAME, PROPERTY, VALUE)
VALUES ('pkgDIPayment_v03', 'CommFeeRate_NonEC', '0.001425');
INSERT INTO CONFIGURATION_VALUES (PACKAGE_NAME, PROPERTY, VALUE)
VALUES ('pkgDIPayment_v03', 'CommFeeRate_EC', '0.001325');
COMMIT;
(2) 新增一個 pkgDI_Injector (相依注入模組): 負責在使用者登入時, 取得上述的參數資料, 將其注入到 pkgDIPayment. 此部份, 必須實作 2 段程式, 第1段建立該 Package, 第2段在登入的 trigger 呼叫該 Package 的 Inject() 方法
CREATE OR REPLACE PACKAGE pkgDI_Injector
IS
/*******************************************************************************
NAME: plInject
PURPOSE: 讀取設定的參數, 並注入各相關的模組
PARAMATER(S):
Mode Name Description
--------- ------------ -----------------------------------------------
*******************************************************************************/
PROCEDURE plInject;
END pkgDI_Injector;
/
CREATE OR REPLACE PACKAGE BODY pkgDI_Injector
IS
/*******************************************************************************
NAME: plInject
PURPOSE: 讀取設定的參數, 並注入各相關的模組
PARAMATER(S):
Mode Name Description
--------- ------------ -----------------------------------------------
*******************************************************************************/
PROCEDURE plInject
IS
BEGIN
-- loop over all properties that are configured in table CONFIGURATION_VALUES
FOR r IN (SELECT cve.package_name
, cve.property
, cve.value
FROM CONFIGURATION_VALUES cve
)
LOOP
-- for each property
EXECUTE IMMEDIATE 'begin '||r.package_name||'.plSet_'||r.property||'(:1); end;' USING r.value;
END LOOP;
END plInject;
END pkgDI_Injector;
/
CREATE OR REPLACE TRIGGER Dependency_Injection AFTER LOGON ON SCHEMA BEGIN pkgDI_Injector.plInject; END;
(3) 修改 pkgDIPayment 的程式
A. 加入 2 個私有欄位及 2個公開方法 (類似 C# 的 private field 及 set 程式區塊)
gv_CommFeeRate_NonEC NUMBER; --現場手續費率 (Private Field)
gv_CommFeeRate_EC NUMBER; --電子手續費率 (Private Field) >br /> plSet_CommFeeRate_NonEC(...) --設定現場交易的手續費率
plSet_CommFeeRate_EC(...) --設定電子交易的手續費率
CREATE OR REPLACE PACKAGE pkgDIPayment_v03
IS
/*******************************************************************************
NAME: fcGetTradeTax
PURPOSE: 取得交易稅
PARAMATER(S):
Mode Name Description
--------- ------------ -----------------------------------------------
IN pi_TradeAmt 價金
RETURN -- 交易稅
*******************************************************************************/
FUNCTION fcGetTradeTax(
pi_TradeAmt IN NUMBER --價金
)
RETURN NUMBER;
/*******************************************************************************
NAME: fcGetCommFee
PURPOSE: 取得手續費
PARAMATER(S):
Mode Name Description
--------- ------------ -----------------------------------------------
IN pi_ChannelKind 通路種類 ('1': 現場, '2':電子)
IN pi_TradeAmt 價金
RETURN -- 手續費
*******************************************************************************/
FUNCTION fcGetCommeFee(
pi_ChannelKind IN VARCHAR2, --通路種類 ('1': 現場, '2':電子)
pi_TradeAmt IN NUMBER --價金
)
RETURN NUMBER;
/*******************************************************************************
NAME: plSet_CommFeeRate_NonEC
PURPOSE: 設定現場交易的手續費率
PARAMATER(S):
Mode Name Description
--------- ------------ -----------------------------------------------
IN pi_CommFeeRate_NonEC 現場交易的手續費率
*******************************************************************************/
PROCEDURE plSet_CommFeeRate_NonEC(
pi_CommFeeRate_NonEC IN NUMBER --現場交易的手續費率
);
/*******************************************************************************
NAME: plSet_CommFeeRate_EC
PURPOSE: 設定電子交易的手續費率
PARAMATER(S):
Mode Name Description
--------- ------------ -----------------------------------------------
IN pi_CommFeeRate_EC 電子交易的手續費率
*******************************************************************************/
PROCEDURE plSet_CommFeeRate_EC(
pi_CommFeeRate_EC IN NUMBER --電子交易的手續費率
);
END pkgDIPayment_v03;
/
CREATE OR REPLACE PACKAGE BODY pkgDIPayment_v03
IS
gv_CommFeeRate_NonEC NUMBER; --現場手續費率 (Private Field)
gv_CommFeeRate_EC NUMBER; --電子手續費率 (Private Field)
/*******************************************************************************
NAME: fcGetTradeTax
PURPOSE: 取得交易稅
*******************************************************************************/
FUNCTION fcGetTradeTax(
pi_TradeAmt IN NUMBER --價金
)
RETURN NUMBER
IS
lv_Result NUMBER(16,2);
BEGIN
lv_Result := pi_TradeAmt * 3 / 1000;
RETURN lv_Result;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
RAISE;
END fcGetTradeTax;
/*******************************************************************************
NAME: fcGetCommFee
PURPOSE: 取得手續費
******************************************************************************/
FUNCTION fcGetCommeFee(
pi_ChannelKind IN VARCHAR2, --通路種類 ('1': 現場, '2':電子)
pi_TradeAmt IN NUMBER --價金
)
RETURN NUMBER
IS
lv_Result NUMBER(16,2);
BEGIN
lv_Result := 0;
IF pi_ChannelKind = '1' THEN
lv_Result := pi_TradeAmt * gv_CommFeeRate_NonEC;
ELSE
lv_Result := pi_TradeAmt * gv_CommFeeRate_EC;
END IF;
RETURN lv_Result;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
RAISE;
END fcGetCommeFee;
/*******************************************************************************
NAME: plSet_CommFeeRate_NonEC
PURPOSE: 設定現場交易的手續費率
*******************************************************************************/
PROCEDURE plSet_CommFeeRate_NonEC(
pi_CommFeeRate_NonEC IN NUMBER --現場交易的手續費率
)
IS
BEGIN
gv_CommFeeRate_NonEC := pi_CommFeeRate_NonEC;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
RAISE;
END plSet_CommFeeRate_NonEC;
/*******************************************************************************
NAME: plSet_CommFeeRate_EC
PURPOSE: 設定電子交易的手續費率
*******************************************************************************/
PROCEDURE plSet_CommFeeRate_EC(
pi_CommFeeRate_EC IN NUMBER --電子交易的手續費率
)
IS
BEGIN
gv_CommFeeRate_EC := pi_CommFeeRate_EC;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
RAISE;
END plSet_CommFeeRate_EC;
BEGIN
-- Initialization
NULL;
END pkgDIPayment_v03;
/
呼叫範例
DECLARE
lv_SettleAmt NUMBER(16,2);
BEGIN
pkgDIMain_v03.plCalcSettleAmtForCashSell(
'1',
10,
2000,
lv_SettleAmt
);
DBMS_OUTPUT.PUT_LINE('交割金額=' || lv_SettleAmt);
-- OUTPUT:
-- 交割金額=19911.5
END;
總結
本文的情境, 只是解決 pkgDIPayment_vxx 與手續費率設定的相依性, 將手續費率取得的方式, 由 pkgDIPayment 自行取得, 改為由外部傳入; 這樣有個好處, 就是其它系統 (例如: 證券前台系統) 要用到這個 Package, 只要複製過去即可, 不用再複製其它額外的設定檔.關於 PL/SQL 如何實作各種 Design Pattern 的部份, 可以參考本文參考文件的前2篇文章.
筆者對於 DI (相依性注入) 這個議題, 只是略知皮毛, 還沒有深入 Study, 如果本文有什麼不足或錯誤之處, 尚請各位指點, 謝謝.
參考文件
- Dependency Injection in PL/SQL – Remove Hard Coded Dependencies from your code
- 此為本篇網誌的主要參考文件, 它以一個 Email 的功能作範例
- Design Patterns in PL/SQL – Interface Injection for even looser coupling
- 此篇以 Logger 為範例, 描述如可由 Logon Trigger (Dependency Injector) 將實際的 Logger Package 傳入到 HRM_SALARY_RULES 這個 Package 裡, 供其使用; 其實, 就是採用類似 Setter Property 的方式, 將真正的 Logger Package 及 Procedure 名稱傳入.
- PL/SQL Packages
- Oracle 官方網站, 內含 PL/SQL Package 的語法結構及範例
- {Huan-Lin 學習筆記} Dependency Injection 筆記 (1)
- 內含一系列的 Dependency Injection 的介紹,
沒有留言:
張貼留言