<i id='ZQYZ1'><tr id='ZQYZ1'><dt id='ZQYZ1'><q id='ZQYZ1'><span id='ZQYZ1'><b id='ZQYZ1'><form id='ZQYZ1'><ins id='ZQYZ1'></ins><ul id='ZQYZ1'></ul><sub id='ZQYZ1'></sub></form><legend id='ZQYZ1'></legend><bdo id='ZQYZ1'><pre id='ZQYZ1'><center id='ZQYZ1'></center></pre></bdo></b><th id='ZQYZ1'></th></span></q></dt></tr></i><div id='ZQYZ1'><tfoot id='ZQYZ1'></tfoot><dl id='ZQYZ1'><fieldset id='ZQYZ1'></fieldset></dl></div>
  • <small id='ZQYZ1'></small><noframes id='ZQYZ1'>

  • <legend id='ZQYZ1'><style id='ZQYZ1'><dir id='ZQYZ1'><q id='ZQYZ1'></q></dir></style></legend>

      • <bdo id='ZQYZ1'></bdo><ul id='ZQYZ1'></ul>
      <tfoot id='ZQYZ1'></tfoot>

        在 PL/SQL 过程中,在 HTML 表中包装查询或引用

        时间:2023-11-28
      1. <legend id='uWfJH'><style id='uWfJH'><dir id='uWfJH'><q id='uWfJH'></q></dir></style></legend>
      2. <tfoot id='uWfJH'></tfoot>

          • <bdo id='uWfJH'></bdo><ul id='uWfJH'></ul>

              <i id='uWfJH'><tr id='uWfJH'><dt id='uWfJH'><q id='uWfJH'><span id='uWfJH'><b id='uWfJH'><form id='uWfJH'><ins id='uWfJH'></ins><ul id='uWfJH'></ul><sub id='uWfJH'></sub></form><legend id='uWfJH'></legend><bdo id='uWfJH'><pre id='uWfJH'><center id='uWfJH'></center></pre></bdo></b><th id='uWfJH'></th></span></q></dt></tr></i><div id='uWfJH'><tfoot id='uWfJH'></tfoot><dl id='uWfJH'><fieldset id='uWfJH'></fieldset></dl></div>
                  <tbody id='uWfJH'></tbody>

                <small id='uWfJH'></small><noframes id='uWfJH'>

                  本文介绍了在 PL/SQL 过程中,在 HTML 表中包装查询或引用的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                  问题描述

                  如果您使用 SQL*Plus 来使用,那看起来真的很容易

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

                  SQL> set markup html on;
                  

                  并在 SQL*Plus 窗口中获得一些可爱的结果.我们有一个 oracle 工作,它会在一夜之间运行,并将结果通过电子邮件发送给许多人.我想将 sql 语句包装在 HTML 表中以包含在该消息中.这样做的最佳方法是什么?

                  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?

                  推荐答案

                  来自 在 DBA 站点 我接近了我正在寻找的答案.Tom Kyte 有一篇很棒的博文,其功能是正是我所希望的.简而言之,这是我实施的:

                  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:

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

                  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;
                  

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

                  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!

                  这篇关于在 PL/SQL 过程中,在 HTML 表中包装查询或引用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                  上一篇:查询帮助 - where 子句中的字符串有 &amp;特点 下一篇:我们如何在PL SQL中在特定时间后自动删除表行

                  相关文章

                  <legend id='iNzFe'><style id='iNzFe'><dir id='iNzFe'><q id='iNzFe'></q></dir></style></legend>

                  <small id='iNzFe'></small><noframes id='iNzFe'>

                    <bdo id='iNzFe'></bdo><ul id='iNzFe'></ul>

                  1. <i id='iNzFe'><tr id='iNzFe'><dt id='iNzFe'><q id='iNzFe'><span id='iNzFe'><b id='iNzFe'><form id='iNzFe'><ins id='iNzFe'></ins><ul id='iNzFe'></ul><sub id='iNzFe'></sub></form><legend id='iNzFe'></legend><bdo id='iNzFe'><pre id='iNzFe'><center id='iNzFe'></center></pre></bdo></b><th id='iNzFe'></th></span></q></dt></tr></i><div id='iNzFe'><tfoot id='iNzFe'></tfoot><dl id='iNzFe'><fieldset id='iNzFe'></fieldset></dl></div>
                    <tfoot id='iNzFe'></tfoot>