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

Oracle PL/SQL 操作 COM 對象完整指南：以 Excel 自動化為例

編輯：Oracle 資料庫基礎

前言

PL/SQL 作為 Oracle 專屬的程序化語言，自 Oracle 8 起支援透過外部程序呼叫 C 語言模組，Oracle 8i 更引入 Java 整合。本文將深入探討如何利用 PL/SQL 呼叫外部 C 程序，進而操作 Windows 平台上的 COM 對象，並以實際的 Excel 自動化匯出範例完整示範操作流程。

為何需要外部程序？

雖然 PL/SQL 在資料庫邏輯處理上效能優異且安全，但在特定場景下其他語言更具優勢：C 語言擅長系統資源操作與裝置控制，Java 則在網路應用上更強。當 PL/SQL 無法滿足需求時，透過外部程序機制整合其他語言模組即為最佳解決方案。

早期版本僅能靠 DBMS_PIPE、DBMS_ALERT 搭配 OCI 或預編譯器實現跨語言通訊，架構複雜。外部程序機制簡化為在 PL/SQL 中建立函數映射至外部函數，使用方式與原生函數無異，大幅降低開發門檻。

核心運作原理

當 PL/SQL 呼叫外部 C 例程時，Oracle 監聽器啟動 extproc 進程，動態載入共享庫（Windows 下為 DLL）。此進程作為緩衝層，接收 PL/SQL 指令轉發至共享庫執行，再將結果回傳。進程生命週期隨資料庫 Session 存在，Session 結束或呼叫完成即自動終止。

監聽器與 extproc 進程架構圖

圖 1：監聽器與 extproc 進程交互架構

環境配置步驟

1. 監聽器設定

編輯 $ORACLE_HOME/network/admin/listener.ora，關鍵參數如下：

LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = CHANET)(PORT = 1521)) ) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = extproc1)) ) ) )
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = D:\oracle\ora92) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = ORADB) (ORACLE_HOME = D:\Oracle\ora92) (SID_NAME = ORADB) ) )

兩項關鍵設定：

(ADDRESS = (PROTOCOL = IPC)(KEY = extproc1))：啟用 IPC 協議供外部程序通訊
(SID_DESC =(SID_NAME = PLSExtProc) (ORACLE_HOME = D:\Oracle\ora92) (PROGRAM = extproc) )：註冊外部程序服務識別碼

2. 網路服務名稱配置

編輯 tnsnames.ora 加入：

EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) )

參數必須與 listener.ora 完全一致。完成後重啟監聽器並驗證：

lsnrctl stop
lsnrctl start
tnsping EXTPROC_CONNECTION_DATA

若回傳 OK 即代表服務正常。可進一步執行 extproc 測試進程啟動狀態。

COM 對象操作機制

Oracle 在 Windows 平台提供 COM 操作介面，屬於 C 外部程序模式。核心操作包含三類：取得屬性值、設定屬性值（唯讀屬性除外）、呼叫方法。

操作 COM 對象工作原理

圖 2：COM 對象操作流程圖

前置作業

SQL> CONNECT SYSTEM/chanet@oradb
SQL> CREATE USER chanet IDENTIFIED BY chanet;
SQL> GRANT CREATE LIBRARY TO chanet;
SQL> CONNECT chanet/chanet@oradb;
SQL> @D:\Oracle\ora92\com\comwrap.sql;

執行 comwrap.sql 會建立 ORDCOM 套件，封裝所有 COM 操作基礎函數。

資料類型對照表

PL/SQL 型別	COM API 型別
VARCHAR2	BSTR
BOOLEAN	BOOL
BINARY_INTEGER	BYTE, INT, LONG
DOUBLE PRECISION	DOUBLE, FLOAT, CURRENCY
DATE	DATE

核心函數說明

函數名稱	功能描述
CreateObject	建立 COM 對象實例
DestroyObject	釋放對象資源
GetLastError	取得最後錯誤資訊
GetProperty	讀取屬性值
SetProperty	設定屬性值
InitArg / SetArg	初始化與設定呼叫參數
Invoke	呼叫方法或函數
GetArg / InitOutArg	取得輸出參數

完整範例：將資料表匯出為 Excel 報表

1. 建立測試資料

CREATE TABLE IT_SALE_TAB(
  ITS_ID VARCHAR2(5),
  ITS_DATE DATE,
  ITS_TOTAL NUMBER
);
-- 插入 12 筆月份銷售資料...
COMMIT;

2. 建立操作套件 oraExcel

套件封裝 Excel Application、Workbook、Worksheet、Range、Font、Chart 等物件的完整操作流程，包含：

CreateExcelWorkSheet：啟動 Excel、建立活頁簿與工作表
setCellValues：多型別寫入儲存格（字串、數值、日期、浮點數）
setCellColWidth：調整欄寬
setCellLines：繪製邊框
setCellMerge：合併儲存格
setCellFont：設定字體屬性（大小、粗體等）
callMethod：呼叫 Range 方法（如 Merge）
InsertChart：插入圖表（支援長條圖、圓餅圖、3D 圖表等）
SaveToFile：另存檔案
CloseExcel：正確關閉並釋放所有 COM 物件

關鍵程式碼片段（建立 Excel 物件）：

FUNCTION CreateExcelWorkSheet(servername VARCHAR2) RETURN BINARY_INTEGER IS
BEGIN
  i := ordcom.CreateObject('Excel.Application', 0, servername, applicationToken);
  IF i != 0 THEN
    ordcom.GetLastError(err_src, err_desc, err_hpf, err_hpID);
    raise_application_error(-20000, err_src || err_desc || err_hpf || err_hpID);
  END IF;
  -- 取得 Workbooks 集合
  i := ordcom.GetProperty(applicationToken, 'WorkBooks', 0, WorkBooksToken);
  ordcom.InitArg(); ordcom.SetArg(-4167, 'I4');
  i := ordcom.Invoke(WorkBooksToken, 'Add', 1, WorkBookToken);
  -- 取得 Worksheets 集合並新增工作表
  ordcom.InitArg(); ordcom.SetArg('Sheet 1', 'BSTR');
  i := ordcom.GetProperty(applicationToken, 'WorkSheets', 0, WorkSheetToken1);
  i := ordcom.Invoke(WorkSheetToken1, 'Add', 0, WorkSheetToken);
  RETURN i;
END;

3. 建立匯出預存程序 dp_ToExcel

流程：建立 Excel → 寫入表頭與欄寬 → 迴圈寫入游標資料 → 繪製表格 → 設定標題字體合併 → 插入圖表 → 存檔關閉。

CREATE OR REPLACE PROCEDURE dp_ToExcel IS
  CURSOR c1 IS SELECT ITS_ID, ITS_DATE, ITS_TOTAL FROM IT_SALE_TAB;
  n BINARY_INTEGER := 2;
  ...
BEGIN
  i := oraExcel.CreateExcelWorkSheet('');
  -- 表頭
  i := oraExcel.setCellValues('A2', '序號', 'BSTR');
  i := oraExcel.setCellValues('B2', '日期', 'BSTR');
  i := oraExcel.setCellValues('C2', '銷售', 'BSTR');
  -- 資料迴圈
  FOR c1_rec IN c1 LOOP
    cellIndex := 'A' || n;
    i := oraExcel.setCellValues(cellIndex, c1_rec.ITS_ID, 'BSTR');
    cellIndex := 'B' || n;
    i := oraExcel.setCellValues(cellIndex, c1_rec.ITS_DATE, 'DATE');
    cellIndex := 'C' || n;
    i := oraExcel.setCellValuesReal(cellIndex, c1_rec.ITS_TOTAL, 'CY');
    n := n + 1;
  END LOOP;
  -- 合計列、表格線、字體、合併、圖表、存檔...
  i := oraExcel.SaveToFile(filename);
  i := oraExcel.CloseExcel();
END;