oldwain随便写 http://oldwain.itpub.net/ <a href="http://oldwain.itpub.net">oldwain随便写</a>

也许你还不知道 - sqlplus的小秘密(4)

上一篇 / 下一篇 2005-09-08 00:00:00 / 个人分类：Oracle

这个也许不算什么秘密, 很多人大概都知道, 不过用过的人也许不多.

在8.1.7版本(也许是816? 不太确定)以后, sql*plus中有一个set markup html的命令, 可以将sql*plus的输出以html格式展现.

scott@O9I.US.ORACLE.COM> set markup html on spool on
">scott@O9I.US.ORACLE.COM> select empno, ename from emp where rownum<3;
<br>
<p>
<table border='1' width='90%' align='center' summary='Script output'>
<tr>
<th scope="col">
EMPNO
</th>
<th scope="col">
ENAME
</th>
</tr>
<tr>
<td align="right">
7369
</td>
<td>
SMITH
</td>
</tr>
<tr>
<td align="right">
7499
</td>
<td>
ALLEN
</td>
</tr>
</table>
<p>

">scott@O9I.US.ORACLE.COM>

注意其中的spool on, 当在屏幕上输出的时候, 我们看不出与不加spool on有什么区别, 但是当我们使用spool filename 输出到文件的时候, 会看到spool文件中出现了<html><body>等tag.

">scott@O9I.US.ORACLE.COM> spool c:emp.htmITPUB个人空间U$R9m:~ DB ]c
<br>
([k^0M}2f.G0">scott@O9I.US.ORACLE.COM> /ITPUB个人空间!@5o V]+o'{3i
<br>ITPUB个人空间yj~mMk
<p>
%yp5E$I`;~(tq*tG;?/t0<table border='1' width='90%' align='center' summary='Script output'>ITPUB个人空间mVrn[.gj X"Q
......此处省略

">scott@O9I.US.ORACLE.COM> spool off
p%n_bs&At0<br>

查看生成的emp.htm文件的内容:

<html>ITPUB个人空间 e,QHby K(D9N4I
<head>
#WAxQi"D w KE5t H0<meta http-equiv="Content-Type" content="text/html; charset=WINDOWS-936">ITPUB个人空间l~%_3J.Rq2C6x|Z2t
<meta name="generator" content="SQL*Plus 9.2.0">
;O-Iz4sgAE*m0<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><title>SQL*Plus Report</title>ITPUB个人空间^u` VG;Y
</head>ITPUB个人空间'M;C}c_?h%^
<body>ITPUB个人空间0v["h8q+R7r
">scott@O9I.US.ORACLE.COM> /
Hpf;rOm^WY z*j0<br>
5Y+s:[&~*O'NwW0<p>
'r}CO7QgcpLIm0<table border='1' width='90%' align='center' summary='Script output'>ITPUB个人空间/R:F ~`vtnV
<tr>
Gno l o+\0<th scope="col">
N_};x lj |Mw0EMPNO
!N"dMCd VD0</th>ITPUB个人空间0FeE_N0FjK C2h
<th scope="col">ITPUB个人空间-LmI%bh&i(Y
ENAME
z?7j$M:N:{E/R1nL0</th>ITPUB个人空间!SWKH Y~
</tr>ITPUB个人空间8J!M1B+d,]3\
<tr>
1P|tnpbs0<td align="right">ITPUB个人空间h8Pm[ Ev/DN]F(Z
7369
T4~B*H i){YYO0</td>ITPUB个人空间P)IQ)FL,}2Z
<td>
-kT e+[{f0SMITHITPUB个人空间3AA,uiid
</td>
Q\)D7}+oge0</tr>ITPUB个人空间;TL.qUsR
<tr>ITPUB个人空间5F:`#E~T-c#R4l6~ `!|
<td align="right">ITPUB个人空间E Mz!j,}lF"W-P
7499ITPUB个人空间8?![$?n2MN
</td>ITPUB个人空间9\C8Q\%W`)w
<td>
2Bt:D:sjN\0ALLENITPUB个人空间N*h K0XX1q
</td>
B K'_|,f`&sO0</tr>ITPUB个人空间 i]7ZN3tK5h+T
</table>
3YIWG BXJ'Jwm ]~0v0<p>

">scott@O9I.US.ORACLE.COM> spool offITPUB个人空间e%F#wE*[Y-CX#}
<br>ITPUB个人空间.l/VwD'n ^
</body>
#iNYh9Rw/qw0</html>

用ie打开emp.htm文件后的样式如下:

现在看看spool off的情况下:

">scott@O9I.US.ORACLE.COM> set markup html on spool off
z]*hpk\8Or_J0<br>
P2J"~ i.M)~:_0cOm0c0">scott@O9I.US.ORACLE.COM> spool c:emp2.htmITPUB个人空间 Z6k'Y-Q^^ k3YcS
<br>ITPUB个人空间6or;Q$?Pw
">scott@O9I.US.ORACLE.COM> /
q2g;\(?:q!e@I0<br>
)~6q4yS[.N2M0<p>
}]Q9wU:zFD0<table border='1' width='90%' align='center' summary='Script outpuITPUB个人空间P"j3l&J4Sk5y
......此处省略ITPUB个人空间%{ iRVk6D
">scott@O9I.US.ORACLE.COM> spool offITPUB个人空间;C_)~4b-O!IPu
<br>ITPUB个人空间}5Jl6VFr+S
">scott@O9I.US.ORACLE.COM>

查看生成的emp2.htm文件的内容:

">scott@O9I.US.ORACLE.COM> /
.b.I Y6J2rr,wE0<br>ITPUB个人空间#@\,~rJL kCV
<p>
<table border='1' width='90%' align='center' summary='Script output'>ITPUB个人空间5}c{"@$q3X h EO
<tr>ITPUB个人空间3Q$q{)f Z&Y+T-X-DM0k
<th scope="col">ITPUB个人空间&_(_:so~
EMPNO
#syf7k%n,?"g0</th>ITPUB个人空间!@_G!JDSWqv
<th scope="col">
C0mB|$CZ ub'Hw0ENAMEITPUB个人空间z\DO$a;US
</th>
X~2|}/{!h\ |+w0</tr>ITPUB个人空间mJ2S%n(@gQ
<tr>ITPUB个人空间{_(y&EN'aY3k2Gf
<td align="right">ITPUB个人空间U.`;ha!Cq
7369
5X6Z'I8G Ll Tt0</td>
3R`J,b^:X1R.u0<td>
Qr|4s(Gc,CM0SMITH
7ULX-aNZ0</td>ITPUB个人空间,b:R_1xMZ[
</tr>
'lf4lUJ0<tr>ITPUB个人空间dl4J+[9QA_&Y7k X)F
<td align="right">ITPUB个人空间,B#Vw p*u1U1y
7499
{*ts3m2V\0</td>
!~wM]5GBq)BH'S9p0<td>
gjV MK9X)|-kD EI0ALLENITPUB个人空间IOQ9U$^ I
</td>
r3DH`O0</tr>ITPUB个人空间w9~h#^/dC(]0h;z.n|
</table>
#x hl*KgQ|+d.c"H0<p>