萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網頁製作 >> HTML 腳本教學

Oracle PL/SQL 使用 HTP 套件直接輸出 HTML 報表:完整套件代碼與部署教學

架構概覽

利用 Oracle 內建 HTP (Hypertext Procedures) 套件,在 PL/SQL 程序中直接產生 HTML,透過 mod_plsql (Apache) 發布為 Web 報表,無需中介層。

Apache mod_plsql 設定 (dads.conf)

<Location /ss>
  SetHandler pls_handler
  Order deny,allow
  Allow from all
  AllowOverride None
  PlsqlDatabaseUsername dinya
  PlsqlDatabasePassword dinya
  PlsqlDatabaseConnectString dinya:1521:ora10g
  PlsqlAuthenticationMode Basic
</Location>

重啟 Apache 後,瀏覽 https://host:port/ss/schema.package.proc 即可執行。

報表套件完整代碼 (cux_html_report)

CREATE OR REPLACE PACKAGE cux_html_report AS
  FUNCTION t(p_size NUMBER, p_content VARCHAR2) RETURN VARCHAR2;
  TYPE c_sql IS REF CURSOR;
  PROCEDURE main;
  PROCEDURE html_header(p_title VARCHAR2);
  PROCEDURE html_body(p_sql VARCHAR2, p_column_count NUMBER, p_subtitle VARCHAR2, p_align VARCHAR2, p_black VARCHAR2);
  PROCEDURE html_tail;
  PROCEDURE table_h(p_width NUMBER, p_border NUMBER, p_align VARCHAR2);
  PROCEDURE table_t;
  PROCEDURE tr_(p_col_count NUMBER, p_column VARCHAR2, p_align VARCHAR2, p_black VARCHAR2);
  PROCEDURE tr_h; PROCEDURE tr_t;
  PROCEDURE td_(p_col_count NUMBER, p_content VARCHAR2, p_align VARCHAR2, p_black VARCHAR2);
  PROCEDURE report_header(p_title VARCHAR2);
  PROCEDURE report_subtitle(p_col_count NUMBER, p_content VARCHAR2, p_align VARCHAR2, p_black VARCHAR2);
  PROCEDURE report_tail(p_content VARCHAR2);
END;
/

CREATE OR REPLACE PACKAGE BODY cux_html_report AS
  -- 分割字串輔助函數
  FUNCTION t(p_size NUMBER, p_content VARCHAR2) RETURN VARCHAR2 IS ... 

  PROCEDURE main IS
  BEGIN
    html_header('我的測試報表');
    report_header('我的標題');
    html_body(
      'select t.owner||'';''||t.object_name||'';''||t.object_id||'';''||t.object_type||'';''||t.owner
       from all_objects t where t.owner=''DINYA'' and rownum<=100',
      5, '序號;對象名稱;ID;類型', 'center', '<b></b>');
    report_tail('這裡是報表尾');
    html_tail;
  END;

  -- HTP 輸出 HTML 結構
  PROCEDURE html_header(p_title VARCHAR2) IS
  BEGIN
    htp.p('<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">');
    htp.p('<html><head><meta http-equiv="Content-Type" content="text/html; charset=gb2312">');
    htp.p('<title>' || NVL(TRIM(p_title), '無標題文檔') || '</title></head><body>');
  END;

  PROCEDURE html_body(...) IS
    c c_sql; v_column VARCHAR2(4000);
  BEGIN
    table_h(90,1,'center');
    report_subtitle(p_column_count, p_subtitle, p_align, p_black);
    OPEN c FOR p_sql;
    LOOP
      FETCH c INTO v_column; EXIT WHEN c%NOTFOUND;
      tr_(p_column_count, v_column, 'left', '');
    END LOOP;
    table_t;
  END;

  PROCEDURE html_tail IS BEGIN htp.p('</body></html>'); END;

  -- 表格/列/儲存格輸出封裝
  PROCEDURE table_h(...) IS BEGIN htp.p('<table width="'||p_width||'%" border="'||p_border||'" align="'||p_align||'">'); END;
  PROCEDURE table_t IS BEGIN htp.p('</table>'); END;
  PROCEDURE tr_(...) IS BEGIN htp.p('<tr>'); td_(...); htp.p('</tr>'); END;
  PROCEDURE td_(...) IS ... -- 依 ; 分割內容輸出多個 <td>
  -- 報表標題/副標/尾部封裝略...
END;
/

-- 測試執行
-- SQL> @cux_html_report.pck
-- 瀏覽 https://host:7777/ss/cux_html_report.main

進階應用:直接輸出 Excel

PROCEDURE main IS
BEGIN
  owa_util.mime_header(ccontent_type => 'application/vnd.ms-excel');
  html_header('我的測試報表');
  ...
END;

瀏覽器會直接啟動 Excel 開啟報表。

copyright © 萬盛學電腦網 all rights reserved