利用 Oracle 內建 HTP (Hypertext Procedures) 套件,在 PL/SQL 程序中直接產生 HTML,透過 mod_plsql (Apache) 發布為 Web 報表,無需中介層。
<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 即可執行。
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
PROCEDURE main IS
BEGIN
owa_util.mime_header(ccontent_type => 'application/vnd.ms-excel');
html_header('我的測試報表');
...
END;
瀏覽器會直接啟動 Excel 開啟報表。