ARES資通電子報

技術交流

PL/SQL TABLE 於 HCP 系統的應用

文 - 周文泰

PL/SQL TABLE 的正確解釋,在Oracle文件中正式名義為Oracle Collection。
Oracle Collection 有三種形式:

  1. Associative arrays : 它是多維、無使用極限的非連續集合,只能用於 PL/SQL,使用前需要宣告,但不需先定義資料庫物件。
    使用前的宣告:
    DECLARE
    TYPE seg_type is record (
    ACCOUNT_CODE HR_COST_BOOK_ACCOUNT.ACCOUNT_CODE%type,
    ACCOUNT_DESC HR_COST_BOOK_ACCOUNT.ACCOUNT_CODE%type,
    SEGMENT01 HR_COST_BOOK_ACCOUNT.SEGMENT01%type,
    SEGMENT02 HR_COST_BOOK_ACCOUNT.SEGMENT02%type,
    SEGMENT03 HR_COST_BOOK_ACCOUNT.SEGMENT03%type,
    SEGMENT04 HR_COST_BOOK_ACCOUNT.SEGMENT04%type,
    SEGMENT05 HR_COST_BOOK_ACCOUNT.SEGMENT05%type,
    SEGMENT06 HR_COST_BOOK_ACCOUNT.SEGMENT06%type,
    SEGMENT07 HR_COST_BOOK_ACCOUNT.SEGMENT07%type,
    SEGMENT08 HR_COST_BOOK_ACCOUNT.SEGMENT08%type,
    );
    TYPE seg_this IS TABLE OF seg_type INDEX BY BINARY_INTEGER;

  2. Nested Table:需要先定義資料庫物件為集合類型,在程式中可使用TABLE()函式化為Table來使用。它是多維、使用極限可在使用時依需求以程式指令EXTEND擴張。
    資料庫物件定義:
    CREATE OR REPLACE
    TYPE
    "OBJ_CHANGE_SHIFT" is object
    (
    psn_id varchar2(32),
    day date,
    shift_group_id number,
    carding varchar2(2)
    );
    CREATE OR REPLACE
    TYPE
    "TAB_CHANGE_SHIFT" is table of obj_change_shift;
    使用前的宣告:
    DECLARE
    vt_change_shift tab_change_shift := tab_change_shift(); /**** 個人的班別 (需要用構造函數初始化)****/

  3. VARRAYs :需要先定義資料庫物件為集合類型,有使用極限且連續集合。在定義 VARRAYs 資料庫物件時要指定它的最大範圍。

HCP 系統現階段正在發展成本分攤子系統:依設定的成本因子分攤比例來處理員工總成本分攤。隨者成本因子數量不定,分攤方式及比例也不定,在處理上是大量使用 Associative arrays,將設定及資料以多維多筆數結合,並將員工總成本依成本因子分攤設定來作分攤匯總。

HCP 系統考勤子系統中有員工個人行事曆展開,需要在 SQL 查詢中構造一張所有日期的記錄情况。在處理日期展開時基本上是使用 Nested Table 將排程日期區間展開,再以Table Join 連結員工工作時刻及排班班別設定,結果存入員工個人行事曆之中。

Oracle Collection有提供 Collection Methods, 是類似Function的函式,僅能使用於 PL/SQL 的語法中,不能在 SQL的語法中使用。Oracle Collection 提供了程式撰寫人員對於虛擬化的 Table 一個簡易的 Solution ,也增進程式執行上的效能及效益。

>>回電子報首頁