数据库输出HTML格式报表的测试-数据库

发布时间：2011-11-12 浏览：5204

当需要在数据库中开发输出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('我的测试报表');

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