在 PL/SQL 过程中，将查询或引用游标包装在 HTML 表中

发布于 2024-12-09 05:36:16 字数 192 浏览 0 评论 0原文

如果您使用 SQL*Plus 来使用

SQL> set markup html on;

SQL*Plus 窗口并获得一些可爱的结果，这似乎非常容易。我们有一个预言机工作，需要通宵运行，并将结果通过电子邮件发送给许多人。我想将 sql 语句包装在 HTML 表中以包含在该消息中。最好的方法是什么？

原文

It seems really easy if you are using SQL*Plus to use

SQL> set markup html on;

and get some lovely results to the SQL*Plus window. we have an oracle job that runs overnight and sends an email of results to a number of people. I would like to wrap a sql statement in an HTML table to be in that message. What is the best way of doing that?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

挥剑断情 2024-12-16 05:36:16

来自发布于 DBA 网站我已经接近了我正在寻找的答案。 Tom Kyte 有一篇很棒的博客文章，其功能正是我所希望的。简而言之，这就是我实现的内容：

我创建了一个将 sys_refcursor 作为变量的函数：

CREATE OR REPLACE FUNCTION fncRefCursor2HTML(rf SYS_REFCURSOR)  RETURN CLOB
IS
    lRetVal      CLOB;
    lHTMLOutput  XMLType; 
    lXSL         CLOB;
    lXMLData     XMLType;

    lContext     DBMS_XMLGEN.CTXHANDLE;
BEGIN
    -- get a handle on the ref cursor --
    lContext := DBMS_XMLGEN.NEWCONTEXT(rf);
    -- setNullHandling to 1 (or 2) to allow null columns to be displayed --
    DBMS_XMLGEN.setNullHandling(lContext,1);
    -- create XML from ref cursor --
    lXMLData := DBMS_XMLGEN.GETXMLTYPE(lContext,DBMS_XMLGEN.NONE);

    -- this is a generic XSL for Oracle's default XML row and rowset tags --
    -- " " is a non-breaking space --
    lXSL := lXSL || q'[<?xml version="1.0" encoding="ISO-8859-1"?>]';
    lXSL := lXSL || q'[<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">]';
    lXSL := lXSL || q'[ <xsl:output method="html"/>]';
    lXSL := lXSL || q'[ <xsl:template match="/">]';
    lXSL := lXSL || q'[ <html>]';
    lXSL := lXSL || q'[  <body>]';
    lXSL := lXSL || q'[   <table border="1">]';
    lXSL := lXSL || q'[     <tr bgcolor="cyan">]';
    lXSL := lXSL || q'[      <xsl:for-each select="/ROWSET/ROW[1]/*">]';
    lXSL := lXSL || q'[       <th><xsl:value-of select="name()"/></th>]';
    lXSL := lXSL || q'[      </xsl:for-each>]';
    lXSL := lXSL || q'[     </tr>]';
    lXSL := lXSL || q'[     <xsl:for-each select="/ROWSET/*">]';
    lXSL := lXSL || q'[      <tr>]';    
    lXSL := lXSL || q'[       <xsl:for-each select="./*">]';
    lXSL := lXSL || q'[        <td><xsl:value-of select="text()"/> </td>]';
    lXSL := lXSL || q'[       </xsl:for-each>]';
    lXSL := lXSL || q'[      </tr>]';
    lXSL := lXSL || q'[     </xsl:for-each>]';
    lXSL := lXSL || q'[   </table>]';
    lXSL := lXSL || q'[  </body>]';
    lXSL := lXSL || q'[ </html>]';
    lXSL := lXSL || q'[ </xsl:template>]';
    lXSL := lXSL || q'[</xsl:stylesheet>]';

    -- XSL transformation to convert XML to HTML --
    lHTMLOutput := lXMLData.transform(XMLType(lXSL));
    -- convert XMLType to Clob --
    lRetVal := lHTMLOutput.getClobVal();

    RETURN lRetVal;
END;

然后在 PL/SQL Developer 的测试窗口中测试它

declare 
  l_cursor sys_refcursor;
begin
  open l_cursor for select * from employees;
  :x:= fncRefCursor2HTML(l_cursor);
  close l_cursor;  
end;

这是我长期以来一直希望找到的东西。谢谢汤姆·凯特！

From posting over On the DBA site I got close to the answer that I was looking for. Tom Kyte has a great blog post with a function that does exactly what I was hoping for. In short here is what I implemented:

I created a function that took a sys_refcursor as a variable:

CREATE OR REPLACE FUNCTION fncRefCursor2HTML(rf SYS_REFCURSOR)  RETURN CLOB
IS
    lRetVal      CLOB;
    lHTMLOutput  XMLType; 
    lXSL         CLOB;
    lXMLData     XMLType;

    lContext     DBMS_XMLGEN.CTXHANDLE;
BEGIN
    -- get a handle on the ref cursor --
    lContext := DBMS_XMLGEN.NEWCONTEXT(rf);
    -- setNullHandling to 1 (or 2) to allow null columns to be displayed --
    DBMS_XMLGEN.setNullHandling(lContext,1);
    -- create XML from ref cursor --
    lXMLData := DBMS_XMLGEN.GETXMLTYPE(lContext,DBMS_XMLGEN.NONE);

    -- this is a generic XSL for Oracle's default XML row and rowset tags --
    -- " " is a non-breaking space --
    lXSL := lXSL || q'[<?xml version="1.0" encoding="ISO-8859-1"?>]';
    lXSL := lXSL || q'[<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">]';
    lXSL := lXSL || q'[ <xsl:output method="html"/>]';
    lXSL := lXSL || q'[ <xsl:template match="/">]';
    lXSL := lXSL || q'[ <html>]';
    lXSL := lXSL || q'[  <body>]';
    lXSL := lXSL || q'[   <table border="1">]';
    lXSL := lXSL || q'[     <tr bgcolor="cyan">]';
    lXSL := lXSL || q'[      <xsl:for-each select="/ROWSET/ROW[1]/*">]';
    lXSL := lXSL || q'[       <th><xsl:value-of select="name()"/></th>]';
    lXSL := lXSL || q'[      </xsl:for-each>]';
    lXSL := lXSL || q'[     </tr>]';
    lXSL := lXSL || q'[     <xsl:for-each select="/ROWSET/*">]';
    lXSL := lXSL || q'[      <tr>]';    
    lXSL := lXSL || q'[       <xsl:for-each select="./*">]';
    lXSL := lXSL || q'[        <td><xsl:value-of select="text()"/> </td>]';
    lXSL := lXSL || q'[       </xsl:for-each>]';
    lXSL := lXSL || q'[      </tr>]';
    lXSL := lXSL || q'[     </xsl:for-each>]';
    lXSL := lXSL || q'[   </table>]';
    lXSL := lXSL || q'[  </body>]';
    lXSL := lXSL || q'[ </html>]';
    lXSL := lXSL || q'[ </xsl:template>]';
    lXSL := lXSL || q'[</xsl:stylesheet>]';

    -- XSL transformation to convert XML to HTML --
    lHTMLOutput := lXMLData.transform(XMLType(lXSL));
    -- convert XMLType to Clob --
    lRetVal := lHTMLOutput.getClobVal();

    RETURN lRetVal;
END;

Then to test it in a Test window in PL/SQL Developer

declare 
  l_cursor sys_refcursor;
begin
  open l_cursor for select * from employees;
  :x:= fncRefCursor2HTML(l_cursor);
  close l_cursor;  
end;

This is something that I have been hoping to find for a long time. THANKS Tom Kyte!

回复收藏 0 原文

夜声 2024-12-16 05:36:16

一种选择是使用超文本函数HTF：

set define off

create table so14t (id number, data varchar2(25));

insert all
into so14t values(101, 'one hundred & one')
into so14t values(202, 'two hundred & two')
into so14t values(303, 'three hundred & three')
select 1 from dual;

declare
  v_html varchar2(32767);
begin
  v_html := htf.tableopen;

  for i in (select * from so14t) loop
    v_html := v_html || htf.tablerowopen;
    v_html := v_html || htf.tabledata(i.id);
    v_html := v_html || htf.tabledata(htf.escape_sc(i.data));
    v_html := v_html || htf.tablerowclose;
  end loop;

  v_html := v_html || htf.tableclose;

  dbms_output.put_line(v_html);
end;
/

打印（为了便于阅读而格式化）：

<TABLE ><TR><TD>101</TD><TD>one hundred & one</TD></TR>
        <TR><TD>202</TD><TD>two hundred & two</TD></TR>
        <TR><TD>303</TD><TD>three hundred & three</TD></TR></TABLE>

One option is to use hypertext functions HTF:

set define off

create table so14t (id number, data varchar2(25));

insert all
into so14t values(101, 'one hundred & one')
into so14t values(202, 'two hundred & two')
into so14t values(303, 'three hundred & three')
select 1 from dual;

declare
  v_html varchar2(32767);
begin
  v_html := htf.tableopen;

  for i in (select * from so14t) loop
    v_html := v_html || htf.tablerowopen;
    v_html := v_html || htf.tabledata(i.id);
    v_html := v_html || htf.tabledata(htf.escape_sc(i.data));
    v_html := v_html || htf.tablerowclose;
  end loop;

  v_html := v_html || htf.tableclose;

  dbms_output.put_line(v_html);
end;
/

Prints (formatted for readability):

<TABLE ><TR><TD>101</TD><TD>one hundred & one</TD></TR>
        <TR><TD>202</TD><TD>two hundred & two</TD></TR>
        <TR><TD>303</TD><TD>three hundred & three</TD></TR></TABLE>

回复收藏 0 原文

~没有更多了~