Oracle将SQL查询结果生成为HTML网页文件
方式一：
1、表数据生成为html格式内容的CLOB对象
2、把CLOB的信息转成文件(HTML)
方式二：
命令行执行批处理程序生成HTML文件
下面详细来看：
方式一的实现：
一、登录Oracle数据库创建临时表scott_emp，作为本次演练使用。
scott_emp该表就是本次演练中的数据表，根据scott.emp表来创建。
CREATE TABLE scott_emp AS select * from scott.emp;
二、创建函数sql_to_html_xslt()，输入要执行的SQL和标题，即可将SQL查询结果返回为html格式内容的CLOB对象。
2.1、创建函数sql_to_html_xslt()
有两种方式创建函数，一是从SQL窗口中执行SQL脚本创建函数，二是在命令行执行脚本文件来创建。
两者的区别是，SQL窗口中执行SQL脚本中html中表示空格的转义符号“ ”需要改成“& ”，即多一个“&”符号。
2.1.1、SQL窗口执行SQL脚本创建函数sql_to_html_xslt()
可以直接用PL/SQL打开一个SQL窗口，复制下面的SQL脚本然后执行：
create or replace function sql_to_html_xslt(p_sql in varchar2, p_title varchar2 default null)
return clob as
/*
Copyright DarkAthena(darkathena@qq.com)
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
*/
/* author:DarkAthena
name:query sql to a html-table (with xslt)
date:2021-10-28
EMAIL:darkathena@qq.com
example 1:
select sql_to_html_xslt(Q'{select * from job_history}') html_table
from dual;
example 2:
select sql_to_html_xslt(Q'{select * from job_history}','this is title') html_table
from dual;
*/
l_ctx dbms_xmlgen.ctxhandle;
l_num_rows pls_integer;
l_xml xmltype;
l_html xmltype;
l_returnvalue clob;
l_xml_to_html_stylesheet varchar2(4000);
l_css varchar2(4000);
begin
l_xml_to_html_stylesheet := q'^<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<table border="1">
<xsl:apply-templates select="ROWSET/ROW[1]" />
</table>
</xsl:template>
<xsl:template match="ROW">
<tr><xsl:apply-templates mode="th" /></tr>
<xsl:apply-templates select="../ROW" mode="td" />
</xsl:template>
<xsl:template match="ROW/*" mode="th">
<th><xsl:value-of select="local-name()" /></th>
</xsl:template>
<xsl:template match="ROW" mode="td">
<tr><xsl:apply-templates /></tr>
</xsl:template>
<xsl:template match="ROW/*">
<td><xsl:apply-templates /></td>
</xsl:template>
</xsl:stylesheet>^';
l_css := '<style type=''text/css''>
body {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;}
p {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;}
table,tr,td {font:10pt Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;}
th {font:bold 10pt Arial,Helvetica,sans-serif; color:#336699; background:#cccc99; padding:0px 0px 0px 0px;}
h1 {font:16pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; border-bottom:1px solid #cccc99; margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;}
h2 {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; margin-bottom:0pt;}
a {font:9pt Arial,Helvetica,sans-serif; color:#663300; background:#ffffff; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}
</style>'; ---CSS ---- from SQLPLUS spool html style
l_returnvalue := '<!DOCTYPE HTML><html><head><body>' || l_css || '<h1>' ||
p_title || '</h1>';
l_ctx := dbms_xmlgen.newcontext(p_sql);
dbms_xmlgen.setnullhandling(l_ctx, dbms_xmlgen.empty_tag);
l_xml := dbms_xmlgen.getxmltype(l_ctx, dbms_xmlgen.none);
--dbms_output.put_line(l_xml.getClobVal());
l_num_rows := dbms_xmlgen.getnumrowsprocessed(l_ctx);
dbms_xmlgen.closecontext(l_ctx);
if l_num_rows > 0 then
l_html := l_xml.transform(xmltype(l_xml_to_html_stylesheet));
dbms_lob.append(l_returnvalue, l_html.getclobval());
end if;
dbms_lob.append(l_returnvalue, '</body>' || chr(10) || '</html>');
return replace(l_returnvalue,' <td/>',' <td>& </td>');
exception
when others then
raise;
end;
/
2.1.2、在命令行执行脚本文件sql_to_html_xslt.fnc创建函数sql_to_html_xslt()
2.1.2.1、网盘下载脚本文件：sql_to_html_xslt.fnc
网盘下载：https://pan.baidu.com/s/1LHgwPqa01_Ig07deJP6vZA?pwd=yyds
2.1.2.2、命令行执行脚本文件创建函数sql_to_html_xslt()
下载好文件(sql_to_html_xslt.fnc)后，
用PL/SQL打开一个命令行窗口，输入命令：
@D:\tmp\sql_to_html_xslt.fnc
（注意：实际的目录名称根据自己的文件位置修改，文件所在目录不要有空格）。
执行过程即结果如下图所示：
2.2、调用函数sql_to_html_xslt()
2.2.1、执行SQL查询（带title参数查询）
SELECT sql_to_html_xslt(q'{select * from scott_emp}', 'sql_to_html') FROM dual;
2.2.2、执行SQL查询（不带title参数查询）
SELECT sql_to_html_xslt(q'{select * from scott_emp}') FROM dual;
三、以管理员身份登录，创建目录html_dir并授权给当前需要使用的用户。
以 “sys@TZQ AS SYSDBA” 身份登录，执行创建目录，并授权给当前使用的用户：
CREATE OR REPLACE DIRECTORY html_dir AS 'D:\tmp\html';
GRANT READ,WRITE ON DIRECTORY html_dir TO LOG;
四、创建存过clob_to_file()，把CLOB的信息转成文件(HTML)
4.1、创建存过clob_to_file()
clob_to_file()存过代码如下：
CREATE OR REPLACE PROCEDURE clob_to_file(p_dir IN VARCHAR2,
p_file IN VARCHAR2,
p_clob IN CLOB) AS
l_output utl_file.file_type;
l_amt NUMBER DEFAULT 32000;
l_offset NUMBER DEFAULT 1;
l_length NUMBER DEFAULT nvl(dbms_lob.getlength(p_clob)
,0);
BEGIN
l_output := utl_file.fopen(p_dir
,p_file
,'w'
,32760);
WHILE (l_offset < l_length) LOOP
utl_file.put(l_output
,dbms_lob.substr(p_clob
,l_amt
,l_offset));
utl_file.fflush(l_output);
l_offset := l_offset + l_amt;
END LOOP;
utl_file.new_line(l_output);
utl_file.fclose(l_output);
END;
/
4.2、调用存过clob_to_file()，将CLOB内容生成HTML文件。
执行下面匿名块，在 *D:\tmp\html* 目录下生成HTML文件 html_test_001.html ：
DECLARE
v_clob CLOB;
BEGIN
-- 参考上面3.2步骤的使用，返回CLOB字段的HTML内容给到临时变量 v_clob
SELECT sql_to_html_xslt(q'{select * from scott_emp}')
INTO v_clob
FROM dual;
clob_to_file('HTML_DIR' -- 目录
,'html_test_001.html' -- 文件名
,v_clob); -- CLOB字段的值 v_clob
END;
执行结果如下：
4.3、查看HTML文件。
查看 *D:\tmp\html* 目录下的HTML文件 html_test_001.html 内容：
方式二的实现：
一、创建SQL文件：D:\tmp\html.sql
set feedback off
set markup html on;
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON HEAD "<TITLE>Department Report</TITLE> <STYLE type='text/css'> <!-- BODY {background: #FFFFC6} --> </STYLE>" BODY "TEXT='#FF00Ff'" TABLE "WIDTH='90%' BORDER='5'"
define data_path=D:\tmp\scripts
col ymd new_value v_ymd
select to_char(sysdate,'YYYYMMDDHH24MISS') ymd FROM dual;
spool D:\tmp\scripts\result_html_&&v_ymd..html
select * from scott.emp;
spool off
set markup html off
exit
二、创建批处理BAT程序：D:\tmp\sql_to_html.bat
sqlplus log/1@tzq @D:\tmp\html.sql > D:\tmp\html.txt
exit;
三、双击文件(D:\tmp\sql_to_html.bat)，运行批处理BAT程序
会调用命令行窗口，然后一闪而过，代表BAT批处理程序已经执行完了。
四、查看生成的HTML文件
至此，Oracle将SQL查询结果生成为HTML网页文件，整个流程结束了，大家有什么不明白的地方可以给我留言，谢谢！
150
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
