資通 HCP(Human Capital Planner)人力資產規劃系統,為使用 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 就無法查詢到先前的資料了。SQL> insert into hr_emptmp(seg_segment_no,id_no_sz,name_sz) values('A001',1001,'雷歐娜'); 1 row inserted SQL> insert into hr_emptmp(seg_segment_no,id_no_sz,name_sz) values('A001',1002,'希瓦娜'); 1 row inserted SQL> insert into hr_emptmp(seg_segment_no,id_no_sz,name_sz) values('A001',1003,'奧莉安娜'); 1 row inserted SQL> update hr_emptmp set name_sz ='珍娜' where id_no_sz='1003' ; 1 row updated SQL> select * from hr_emptmp; SEG_SEGMENT_NO ID_NO_SZ NAME_SZ -------- ---------- -------------------------------------------------------------------------------- A001 1001 雷歐娜 A001 1002 希瓦娜 A001 1003 珍娜 SQL> delete from hr_emptmp where id_no_sz='1003' ; 1 row deleted SQL> select * from hr_emptmp ; SEG_SEGMENT_NO ID_NO_SZ NAME_SZ -------- ---------- --------------------------------------------------------------------------------
A001 1001 雷歐娜 A001 1002 希瓦娜
可以針對此 table 進行 update、delete 等操作SQL> commit; Commit complete SQL> select *from hr_emptmp ; SEG_SEGMENT_NO ID_NO_SZ NAME_SZ -------- ---------- --------------------------------------------------------------------------------
SQL>
因為已經 commit,所以查詢不到任何資料。
再重複上述的 insert 動作,然後 rollback。SQL> Rollback ; Rollback complete SQL> 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 人力資源規劃系統,如何協助企業提升營運管理績效嗎?
★詳細產品資訊請洽線上免費諮詢服務或服務窗口 - 汪小姐 Tel:02 -2522-1351 Ext. 301