html中的oracle结果集类似于SQL*Plus(仅使用SQL)

Question

为了直接给出一个例子，下面是我想做的（不使用SQLPlus）：在SQL中Plus:

SQL> set feed off markup html on
SQL> select * from emp where rownum<=2;

给出以下输出：

<p>
<table border="1" width="90%" summary="Script output">
<tr>
<th scope="col">
EMPNO
</th>
<th scope="col">
ENAME
</th>
<th scope="col">
JOB
</th>
<th scope="col">
MGR
</th>
<th scope="col">
HIREDATE
</th>
<th scope="col">
SAL
</th>
<th scope="col">
COMM
</th>
<th scope="col">
DEPTNO
</th>
</tr>
<tr>
<td align="right">
      7369
</td>
<td>
SMITH
</td>
<td>
CLERK
</td>
<td align="right">
      7902
</td>
<td>
17-DEC-80
</td>
<td align="right">
       800
</td>
<td align="right">
&nbsp;
</td>
<td align="right">
        20
</td>
</tr>
<tr>
<td align="right">
      7499
</td>
<td>
ALLEN
</td>
<td>
SALESMAN
</td>
<td align="right">
      7698
</td>
<td>
20-FEB-81
</td>
<td align="right">
      1600
</td>
<td align="right">
       300
</td>
<td align="right">
        30
</td>
</tr>
</table>
<p>

有没有一种简单的方法只在SQL中实现这一点？（例如，使用一个或多个Oracle的XML函数）。任何建议都是非常受欢迎的。

编辑：为了进一步澄清，我不关心空白或p/table标签以及每个标签的属性，只关心结构化的html。

Answer 1

SQL Fiddle

Oracle 11g R2架构设置:

CREATE TABLE EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) AS
SELECT 7369, 'Smith', 'Clerk', 7902, DATE '1980-12-17', 800, CAST( NULL AS NUMBER(5,2) ), 20 FROM DUAL UNION ALL
SELECT 7499, 'Allen', 'Salesman', 7698, DATE '1981-02-20', 1600, 300, 30 FROM DUAL

查询1：

SELECT XMLElement(
         "p",
         XMLElement(
           "table",
           XMLElement( "tr",
             XMLForest(
               'empno'    AS "th",
               'ename'    AS "th",
               'job'      AS "th",
               'mgr'      AS "th",
               'hiredate' AS "th",
               'sal'      AS "th",
               'comm'     AS "th",
               'deptno'   AS "th"
             )
           ),
           XMLAgg(
             XMLElement( "tr",
               XMLForest(
                 empno    AS "td",
                 ename    AS "td",
                 job      AS "td",
                 mgr      AS "td",
                 hiredate AS "td",
                 sal      AS "td",
                 comm     AS "td",
                 deptno   AS "td"
               )
             )
           )
         )
       ).getClobVal() AS XML
FROM   EMP

结果：

|                                                                                                                                                                                                                                                                                                                                                                            XML |
|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| <p><table><tr><th>empno</th><th>ename</th><th>job</th><th>mgr</th><th>hiredate</th><th>sal</th><th>comm</th><th>deptno</th></tr><tr><td>7369</td><td>Smith</td><td>Clerk</td><td>7902</td><td>1980-12-17</td><td>800</td><td>20</td></tr><tr><td>7499</td><td>Allen</td><td>Salesman</td><td>7698</td><td>1981-02-20</td><td>1600</td><td>300</td><td>30</td></tr></table></p> |