技術交流

資通 HCP 臨時表功能，提升程式執行速度

蘇建星

更新於 2019-10-29

資通 HCP 人資系統為使用 Oracle 資料庫的應用系統，處理資料量比較龐大時可利用建立 partition table 方式來解決，但此方法必須在 Enterprise 版本才有支援，若我們只需處理一個龐大 table 中的少數資料時，我們可以考慮以臨時表來處理。

Global Temporary Table（臨時表）

Oracle 臨時表可根據資料存在的週期 transaction 或 session 分為以下兩種：

若臨時表中的數據只在 session 中存在，當此 session 結束的時候，Oracle 自動清除臨時表中數據。建立語法如下：
```
Create Global Temporary Table Table_Name（Col1 Type1,Col2 Type2...）On Commit Preserve Rows；
```
若臨時表中的數據只在 transaction 中存在。當一個 transaction 結束（commit or rollback），Oracle 自動清除臨時表中數據。
```
Create Global Temporary Table Table_Name（Col1 Type1,Col2 Type2...）On Commit Delete Rows；
```

臨時表中的數據只對當前 session 有效，每個 session 都有自己的臨時數據，並且不能查詢到其它 session 臨時表中的數據。

資料處理範例

session 結束後資料就沒有了，當 commit 的時候則資料還在，當 rollback 的時候則資料也是一樣被倒回


insert into hr_emptmp(seg_segment_no,id_no_sz,name_sz)
values('A001',1001,'崔斯塔娜');
insert into hr_emptmp(seg_segment_no,id_no_sz,name_sz)
values('A001',1002,'黛安娜');
insert into hr_emptmp(seg_segment_no,id_no_sz,name_sz)
values('A001',1003,'索娜');
SQL> select *from hr_emptmp ;
SEG_SEGMENT_NO  ID_NO_SZ  NAME_SZ
------ -------- 
A001 1001 崔斯塔娜
A001 1002 黛安娜
A001 1003 索娜
SQL> commit;
Commit complete
SQL> select * from hr_emptmp ;
SEG_SEGMENT_NO  ID_NO_SZ  NAME_SZ
------ -------- 
A001 1001 崔斯塔娜
A001 1002 黛安娜
A001 1003 索娜
SQL> insert into hr_emptmp(seg_segment_no,id_no_sz,name_sz)
values('A001',1004,'卡特蓮娜');
1 row inserted
SQL> select * from hr_emptmp ;
SEG_SEGMENT_NO  ID_NO_SZ  NAME_SZ
------ -------- 
A001 1001 崔斯塔娜
A001 1002 黛安娜
A001 1003 索娜
A001 1004 卡特蓮娜
SQL> rollback ;
Rollback complete
SQL> select * from hr_emptmp ;
SEG_SEGMENT_NO  ID_NO_SZ  NAME_SZ
------ -------- 
A001 1001 崔斯塔娜
A001 1002 黛安娜
A001 1003 索娜

transaction 結束就刪除資料：

在一個 session 中 insert 記錄，若沒有進行 commit 或者 rollback 之前，可以對 insert 的記錄進行 update、delete 等操作。但當進行 commit 或者 rollback 的時候，table 是屬於 transaction 的臨時表，那麼在這個 session 就無法查詢到先前的資料了。


insert into hr_emptmp(seg_segment_no,id_no_sz,name_sz)
values('A001',1001,'雷歐娜');
// 1 row inserted
insert into hr_emptmp(seg_segment_no,id_no_sz,name_sz)
values('A001',1002,'希瓦娜');
// 1 row inserted
insert into hr_emptmp(seg_segment_no,id_no_sz,name_sz)
values('A001',1003,'奧莉安娜');
// 1 row inserted
update hr_emptmp set name_sz ='珍娜' where id_no_sz='1003' ;
// 1 row updated
select * from hr_emptmp;
SEG_SEGMENT_NO  ID_NO_SZ  NAME_SZ
-------- ---------- 
A001 1001 雷歐娜
A001 1002 希瓦娜
A001 1003 珍娜
delete from hr_emptmp where id_no_sz='1003' ;
1 row deleted
select * from hr_emptmp ;
SEG_SEGMENT_NO  ID_NO_SZ  NAME_SZ
-------- ---------- 
A001 1001 雷歐娜
A001 1002 希瓦娜
// 可以針對此 table 進行 update、delete 等操作
commit;
Commit complete
SQL> select *from hr_emptmp ;
SEG_SEGMENT_NO  ID_NO_SZ  NAME_SZ
-------- ----------

因為已經 commit，所以查詢不到任何資料。再重複上述的 insert 動作，然後 rollback。


Rollback ;
// Rollback complete
select * from hr_emptmp ;
SEG_SEGMENT_NO  ID_NO_SZ  NAME_SZ

因為已經 rollback，所以也查詢不到任何資料。

臨時表的優缺點

優點：存放在記憶體之中，而非 Data File 裡面，所以 SESSION 獨立，也就是「不同的 DB Session，看不到另一個 DB Session 的資料。」
缺點：不能使用 %type、%rowtype。在程式開發階段，不易查找 Temporary Table 資料。

HCP 臨時表的應用

處理每日出勤異常時將所需處理人員及刷卡資料移至臨時表中。
處理薪資查詢時將一些查詢資料由原來的薪資主檔及明細檔移至臨時表中。
程式執行過程中可能需要存放一些臨時資料，這些資料在整個程式的 session 需要用到。

為帶給客戶更完善的系統服務，HCP 使用 Oracle 資料庫應用系統，不僅能處理龐大的資料量，也可藉由臨時表功能提升程式執行速度，讓使用者能迅速完成系統作業，提升企業管理營運效率。

HCP 一例一休專區

Tags：HR 系統、考勤管理、集團人事管理