您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 開發實戰經驗談：SQL*Plus 技巧、Forms/Reports 進階應用與 DBA 管理精要

編輯：Oracle 資料庫基礎

專案背景

嶺澳核電站資料庫平台：Oracle 7.3 後端 + Developer 2000 / Designer 2000 前端，C/S 架構。分享實戰開發與管理心得。

一、SQL*Plus 實用技巧

1. 查找與刪除重複記錄

-- 查重 (以 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. 批量編譯視圖/授權/同義詞

資料庫遷移重建後，視圖跨表空間引用失效，需重新編譯：

SPOOL compile_views.sql
SELECT 'ALTER VIEW ' || tname || ' COMPILE;' FROM tab;
SPOOL OFF
@compile_views.sql

-- 批量授權
SELECT 'GRANT SELECT ON ' || tname || ' TO username;' FROM tab;
-- 批量建同義詞
SELECT 'CREATE SYNONYM ' || tname || ' FOR username.' || tname || ';' FROM tab;

3. 外部聯結替代 NOT IN 提升效能

-- 原寫法：NOT IN 子查詢，10000x10000 比對，30 分鐘
SELECT pay_no, project_name FROM a
WHERE a.pay_no NOT IN (SELECT pay_no FROM b WHERE value >= 120000);

-- 改寫：LEFT JOIN + IS NULL，約 1 分鐘
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, 'POWER SYSTEM 歡迎使用');

2. 隱藏選單中的 Window 項目

建立 Menu Item：Type=Magic, Command Type=Null, Magic Item=Window, Label 留白。

3. 動態下拉清單 (List Item)

-- Form WHEN-NEW-FORM-INSTANCE 呼叫
PROCEDURE dyn_list IS
    rg RECORDGROUP;
BEGIN
    rg := CREATE_GROUP_FROM_QUERY('spec_rg', 'SELECT specification FROM cable_account');
    POPULATE_GROUP(rg);
    POPULATE_LIST('EBOP_CABLE_ACCOUNT.SPECIFICATION', rg);
END;

4. 多記錄 + 大量欄位的畫面佈局

Canvas 1 (Content) 放主鍵欄位；Canvas 2 (Stack) 放其餘欄位。建立 Block 時選 Canvas 1，再將非主鍵欄位 Canvas 屬性改為 Canvas 2。水平捲軸/TAB/Enter 可瀏覽所有欄位。

5. 受限觸發器中提交資料

建立 Server 端 Procedure DB_SQL_COMMIT，受限觸發器中呼叫 DB_SQL_COMMIT; 繞過限制。

6. 記錄序號自動遞增

Block 級 WHEN-CREATE-RECORD 觸發器：:VO.VO_ID := :SYSTEM.TRIGGER_RECORD;

7. 呼叫另一 Form 並傳遞查詢條件

-- 呼叫端
DECLARE pm PARAMLIST;
BEGIN
    pm := GET_PARAMETER_LIST('PM');
    IF NOT ID_NULL(pm) THEN DESTROY_PARAMETER_LIST('PM'); END IF;
    pm := CREATE_PARAMETER_LIST('PM');
    ADD_PARAMETER(pm, 'THE_WHERE', TEXT_PARAMETER, 'EM_NAME=''EM4'' AND EM_PROJECT_NAME=''支架預制''');
    OPEN_FORM('PAYMENT', ACTIVATE, SESSION, pm);
END;

-- 被呼叫 Form (PAYMENT) WHEN-NEW-FORM-INSTANCE
IF :PARAMETER.THE_WHERE IS NOT NULL THEN
    SET_BLOCK_PROPERTY('PAYMENT', DEFAULT_WHERE, :PARAMETER.THE_WHERE);
END IF;

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

修改 Program Unit CLEAR_ALL_MASTER_DETAILS，調整邏輯實現自動儲存。

三、資料庫管理精要

1. 清空表資料必用 TRUNCATE

DROP TABLE、DELETE * FROM table 皆不釋放表空間高水位，反覆操作會耗盡表空間。TRUNCATE TABLE table_name 瞬間釋放空間並重置高水位。

2. 資料庫檔案移動標準流程 (Solaris 例)

Server Manager 關閉實例：SHUTDOWN
OS 層移動檔案：mv /ora13/orarun/document.dbf /ora12/orarun/
啟動至 MOUNT 狀態：STARTUP MOUNT
重命名檔案：ALTER DATABASE RENAME FILE '/ora13/orarun/document.dbf' TO '/ora12/orarun/document.dbf';
開啟資料庫：ALTER DATABASE OPEN

使用 ALTER DATABASE 而非 ALTER TABLESPACE 適用性更強。