數據庫輸出HTML格式報表的測試

發布: 2007-4-22 19:30 | 作者: dinya &nb | 來源: dinya | 查看: 198次 | 進入軟件測試論壇討論

當需要在數據庫中開發輸出HTML報表的時候, 我們可以在過程中直接使用htp包來實現html代碼和對數據處理后的輸出. 并輸出為需要的格式(HTML標記語言來控制) . 以下提供一個直接從數據庫生成HTML報表的測試例子, 需要根據自己的需要來進行修改或增加.

在使用前需要配置apache的連接, 這樣在調用過程的時候, 系統直接將生成的html輸出到WEB;

--配置apache連接:

如安裝在D:\system\https 下, D:\system\https\Apache\modplsql\conf\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服務.

--創建測試包:

create or replace package cux_html_report as

    function t(p_size in number, p_content in varchar2) return varchar2 ;
    type c_sql is ref cursor;

    procedure main;
    procedure html_header(p_title in varchar2);
    procedure html_body(p_sql in varchar2,p_column_count in number,p_subtitle in varchar2,p_align in varchar2,p_black in varchar2);
    procedure html_tail;

    procedure table_h(p_width in number,p_border in number,p_align in varchar2);
    procedure table_t;
    procedure tr_(p_col_count in number,p_column in varchar2,p_align in varchar2,p_black in varchar2);
    procedure tr_h;
    procedure tr_t;
    procedure td_(p_col_count in number,p_content in varchar2,p_align in varchar2,p_black in varchar2);

    procedure report_header(p_title in varchar2);
    procedure report_subtitle(p_col_count in number,p_content in varchar2,p_align in varchar2,p_black in varchar2);
    procedure report_tail(p_content in varchar2);
end ;
/
create or replace package body cux_html_report as

    function t(p_size in number, p_content in varchar2) return varchar2 as
        i number:=0;
        v_out varchar2(300):='';
        p_cont varchar2(1000):=p_content;
    begin
        for ii in 1..p_size loop
            select substr(p_cont,0,decode(instr(p_cont,';'),0,length(p_cont),instr(p_cont,';')-1))
            into v_out from dual ;
            p_cont:=substr(p_cont,instr(p_cont,';')+1);
            i:=i+1;
            if i=p_size then
                return v_out;
            end if;
        end loop;
    end ;

    /*******************************************************************************
        主程序
        需要處理分頁的問題
    ********************************************************************************/
    procedure main as
    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;

    --頁頭,固定格式
    procedure html_header(p_title in varchar2) as
    begin
        htp.p('
            <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
            <html>
            <head>
            <meta http-equiv="Content-Type" content="text/html; charset=gb2312">
            <title>'||nvl(trim(p_title),'無標題文檔')||'</title>
            </head>
            <body>');
    end ;

    --頁體, 根據SQL程序和字段數,展示數據
    procedure html_body(p_sql in varchar2,p_column_count in number,p_subtitle in varchar2,p_align in varchar2,p_black in varchar2) as
        i number:=0;
        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;
            i:=i+1;
            tr_(p_column_count,v_column,'left','');
        end loop;
        table_t;
    end ;

    --頁尾, 固定內容
    procedure html_tail as
    begin
        htp.p('</body> </html>');
    end ;


    --------------------------------------------------------------------------------
    --表頭, 表的屬性
    procedure table_h(p_width in number,p_border in number,p_align in varchar2) as
    begin
        htp.p('<table width="'||p_width||'%" border="'||p_border||'" align="'||p_align||'">');
    end ;

    --表尾
    procedure table_t as
    begin
        htp.p('</table>');
    end ;
    --------------------------------------------------------------------------------


    --表格行
    --------------------------------------------------------------------------------
    procedure tr_(p_col_count in number,p_column in varchar2,p_align in varchar2,p_black in varchar2) as
    begin
        htp.p('<tr>');
        td_(p_col_count,p_column,p_align,p_black);
        htp.p('</tr>');
    end;
    procedure tr_h as
    begin
        htp.p('<tr>');
    end ;
    procedure tr_t as
    begin
       htp.p('</tr>');
    end ;
    -------------------------------------------------------------------------------



    --表單元格
    -------------------------------------------------------------------------------
    procedure td_(p_col_count in number,p_content in varchar2,p_align in varchar2,p_black in varchar2) as
        v_cont varchar2(300):=p_content;
        v_out varchar2(300):='';
    begin
        for i in 1..p_col_count loop
            select substr(v_cont,0,decode(instr(v_cont,';'),0,length(v_cont),instr(v_cont,';')-1))
            into v_out from dual ;
            htp.p('<td align="'||p_align||'">'||substr(p_black,1,3)||v_out||substr(p_black,4)||'</td>');
            v_cont:=substr(v_cont,instr(v_cont,';')+1);
        end loop;
    end ;
    ------------------------------------------------------------------------------



    --報表內容部分
    -----------------------------------------------------------------------------
    procedure report_header(p_title in varchar2) as
    begin
        table_h(90,0,'center');
        tr_(1,p_title,'center','<b></b>');
        table_t;
    end ;

    procedure report_subtitle(p_col_count in number,p_content in varchar2,p_align in varchar2,p_black in varchar2) as
    begin
        tr_h;
        td_(p_col_count,p_content,p_align,p_black);
        tr_t;
    end ;

    procedure report_tail(p_content in varchar2) as
    begin
        table_h(90,0,'center');
        tr_(1,p_content,'left','');
        table_t;
    end;
    ----------------------------------------------------------------------------------


end ;
/

SQL> @d:\cux_html_report.pck

Package created.

Package body created.

SQL>

編譯沒有錯誤后, 使用http://dinya:7777/ss/cux_html_report.main 即可查看報表.

如果在main過程中使用 owa_util.mime_header(ccontent_type => 'application/vnd.ms-excel'); 可以直接將數據生成到Excel中.

如:

procedure main as
begin

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

需要使用的話, 可以根據需要修改上面的程序.