[PL/SQL] Global Temporary Tables - 用途 & 做法

近來碰到一些問題就是在報表開發過程中, 因應需求會撰寫很多SQL而這些SQL可能抓取來自不同的Oracle Database的資料. 但是可能報表要一樣的結果但是不同的人寫就會出現不同的結果, 問題是正確答案只有一個啊. 原因就在前端複雜的schema 設計&流程邏輯, 常常因為開發人員的認知不同而出現落差.

這時如何在架構上讓資料抓取(Data Extraction)和報表呈現(Report Presentation)做有效的區隔就很重要(這邊注重的是資料庫端, 在應用端已有很多方案像Struts等). 就資料庫端而言, 有一個Oracle的技術 'Global Temporary Tables(GTT)' 就有探討的價值. 簡單的說, GTT可以將複雜的資料抓取邏輯隱藏並將結果用報表呈現人員習慣的Table 方式呈現. 另一個重點是該Table的資料只在特定Session中存在, 如果Session結束, 資料即消失, 但是 Table本身還是存在.

下面就來做個測試吧!

SQL> CREATE GLOBAL TEMPORARY TABLE worktable (x NUMBER(3));

Table created.

這時可以在行開啟另一個SQLPlus session, 可以看到方才另一個Session 建的GCC, 並且可以做DML, 如下.

SQL> INSERT INTO worktable (x) VALUES (1);

1 row created.

SQL> SELECT * FROM worktable;

    X
----------
    1

SQL> commit;

Commit complete.

SQL> SELECT * FROM worktable;

no rows selected

ㄟ!見鬼了, 怎麼一 Commit資料就不見了, 原因在見下面建GCC的參數. 建立GCC時,
用'On Commit Preserve Rows', Commit完後, 資料便不會消失.

SQL> CREATE GLOBAL TEMPORARY TABLE worktable

2  (x NUMBER(3))
3  ON COMMIT PRESERVE ROWS;

Table created.

SQL> INSERT INTO worktable (x) VALUES (1);

1 row created.

SQL> SELECT * FROM worktable;

    X
----------
    1

SQL> commit;

Commit complete.

SQL> SELECT * FROM worktable;

    X
----------
    1

這時如果從其他Sesseion看相同的GCC是看不到資料的, 如下.

SQL> SELECT * FROM worktable;

no rows selected

再試試 truncate 吧!

SQL> INSERT INTO worktable (x) VALUES (2)

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT * FROM worktable;

    X
----------
    2

SQL> TRUNCATE TABLE worktable;

Table truncated.

SQL> SELECT * FROM worktable;

no rows selected

SQL> commit;

Commit complete.

下Truncate指令一樣只會把Session內的資料清除.