Oracle資料庫開發實戰技巧與經驗分享

前言

Oracle資料庫作為大型關聯式資料庫管理系統的領軍者，長期佔據高端市場主導地位。其強大的管理功能與持續創新的技術迭代，始終是IT業界關注的焦點。筆者在嶺澳核電站專案中，採用Oracle 7.3作為後端平台，搭配Developer 2000與Designer 2000作為前端開發工具，採行Client/Server架構。在實際開發過程中，累積了一些實用技巧，現整理分享供同行參考。

一、SQL*Plus 進階應用技巧

1. 查找與刪除重複記錄

利用 ROWID 偽欄位可高效識別重複資料。以下範例以 EM5_PIPE_PREFAB 表為例，依 DRAWING 與 DSNO 兩欄位判斷重複：

-- 查找重複記錄
SELECT DRAWING, DSNO FROM EM5_PIPE_PREFAB
WHERE ROWID != (SELECT MAX(ROWID) FROM EM5_PIPE_PREFAB D
                WHERE EM5_PIPE_PREFAB.DRAWING = D.DRAWING
                  AND EM5_PIPE_PREFAB.DSNO = D.DSNO);

-- 刪除重複記錄（保留 ROWID 最大的一筆）
DELETE FROM EM5_PIPE_PREFAB
WHERE ROWID != (SELECT MAX(ROWID) FROM EM5_PIPE_PREFAB D
                WHERE EM5_PIPE_PREFAB.DRAWING = D.DRAWING
                  AND EM5_PIPE_PREFAB.DSNO = D.DSNO);

2. 快速重新編譯所有視圖

資料庫遷移或重建後，視圖可能因跨表空間引用失效需重新編譯。利用動態 SQL 生成腳本：

SQL> SPOOL ON.SQL
SQL> SELECT 'ALTER VIEW ' || TNAME || ' COMPILE;' FROM TAB;
SQL> SPOOL OFF
SQL> @ON.SQL

同理可快速批量授權或建立同義詞：

SELECT 'GRANT SELECT ON ' || TNAME || ' TO USERNAME;' FROM TAB;
SELECT 'CREATE SYNONYM ' || TNAME || ' FOR USERNAME.' || TNAME || ';' FROM TAB;

3. 善用外部連接提升查詢效能

避免使用 NOT IN 子查詢，因其會導致嵌套循環比較，效能極差。改用外部連接（Outer Join）可大幅縮短查詢時間：

-- 低效寫法（NOT IN）
SELECT PAY_NO, PROJECT_NAME FROM A
WHERE A.PAY_NO NOT IN (SELECT PAY_NO FROM B WHERE VALUE >= 120000);

-- 高效寫法（LEFT OUTER JOIN）
SELECT A.PAY_NO, A.PROJECT_NAME FROM A
LEFT JOIN B ON A.PAY_NO = B.PAY_NO AND B.VALUE >= 120000
WHERE B.PAY_NO IS NULL;

4. 觸發器中引用新舊欄位值

在資料庫觸發器中，使用 :OLD 參考修改前的值，:NEW 參考修改後的值。例如：:OLD.DEPT_NO、:NEW.DEPT_NO。

二、Oracle Developer 2000 表單開發技巧

1. 自訂 FMX 執行視窗標題

在 Form 級觸發器 WHEN-NEW-FORM-INSTANCE 中撰寫：

SET_WINDOW_PROPERTY(FORMS_MDI_WINDOW, TITLE, 'POINT SYSTEM 歡迎使用');

2. 隱藏選單中的 Window 選項

1. 建立一個 Menu Item，命名為 WINDOW
2. 屬性設定：
   Menu Item Type: Magic
   Command Type: Null
   Magic Item: Window
   Label: 留空

3. 建立動態下拉清單

在 Form 的 WHEN-NEW-FORM-INSTANCE 觸發器呼叫自訂 Procedure DYN_LIST，並將資料項屬性設為 List Item，即可依資料庫內容動態刷新選項。

4. 多記錄且欄位眾多時的畫面佈局

採用雙畫布策略：
- 畫布 1 (Content 型)：放置關鍵識別欄位（如 PRN 代碼、設備代碼）
- 畫布 2 (Stack 型)：放置其餘大量欄位
透過水平捲軸或 Tab 鍵切換，兼顧概覽與詳細錄入。

5. 受限觸發器中執行 COMMIT

在資料庫端建立 Procedure DB_SQL_COMMIT，再於受限觸發器中呼叫該 Procedure，繞過 Form 端限制。

6. 記錄序號自動遞增

在區塊級觸發器 WHEN-CREATE-RECORD 中設定：

:VO_ID := :SYSTEM.TRIGGER_RECORD;

7. 參數傳遞呼叫另一 Form 並過濾資料

使用 PARAMETER LIST 傳遞 WHERE 條件，於目標 Form 的 WHEN-NEW-FORM-INSTANCE 以 SET_BLOCK_PROPERTY 套用 DEFAULT_WHERE。

8. 主從關聯連續輸入避免重複提示儲存

修改 Program Unit 中的 PROCEDURE Clear_All_Master_Details，調整儲存提示邏輯。

三、資料庫管理實務

1. 清空資料表釋放空間

使用 TRUNCATE TABLE 表名; 而非 DELETE 或 DROP，可真正釋放 Tablespace 空間，避免反覆操作導致空間耗盡。

2. 資料庫檔案移動流程

1. 關閉實例：SVRMGR> SHUTDOWN
2. OS 層面移動檔案（如 Solaris mv 指令）
3. 啟動至 Mount 模式並執行 ALTER DATABASE RENAME FILE
4. 開啟資料庫：ALTER DATABASE OPEN

此法適用性優於 ALTER TABLESPACE。