最近使用SQL*Plus命令生成html文件,遇到一些有意思的知识点,顺便记录一下,方便以后需要的时候而这些知识点又忘记而捉急。好记性不如烂笔头吗!
为什么要用SQL*Plus生成html文件?
有些人肯定会问,我使用SQL*Plus为什么要生成html文件呢? SQL*Plus本身就是一个命令工具,生成html文件有必要吗? 下面是个人的一些看法,如有不对,敬请指正:
有很大一部分ORACLE DBA都习惯使用SQL*Plus管理、维护数据库,而且命令玩得相当熟、相当溜, SQL*Plus占用资源少,速度快,即使在如今UI工具(Toad、PL/SQL Developer..)大行其道的年代, 还是有很多DBA或SQL*Plus爱好者广泛使用SQL*Plus, 不过SQL*Plus确实也有很多不足的地方,例如格式设置复杂,如果不设置格式,输出又相当不友好,另外,命令界面是需要刷屏的,有时候你需要往前翻滚,查看前面输出内容;有时候,你需要将那些输出保存起来,方便你事后分析、查阅; 有时候你需要保存你那些查询的SQL。这时候SQL*Plus就暴露弊端了,当然,你会说我可以使用spool保存为文本,这个当然可以,但是文本格式的文件在很多方面没有html文件方便......
如何使用SQL*Plus生成html文件?
其实SQL*Plus生成html文件很简单,主要是使用 set markup html on(指定是否生成HTML格式,ON生成,OFF不生成,默认是OFF) 和spool命令,如下所示
set markup html on;
spool salgrade.html
select * from scott.salgrade; --需要查询的SQL脚本
spool off
set markup html off
exit
如何使用SQL*Plus生成漂亮的html文件?
其实上面案例生成的html文件是很丑陋的,如果要生成漂亮的html文件,则必须对输出的html格式进行设置。
MARK[UP] HTML [OFF|ON]
[HEAD text] [BODY text] [TABLE text]
[ENTMAP {ON|OFF}]
[SPOOL {OFF|ON}]
[PRE[FORMAT] {OFF|ON}]
如上所示,可以设置HEAD、BODY 、TABLE的格式,
[HEAD text]
设置html文件的head格式
[BODY text]
设置html文件的body格式
[TABLE text]
设置html文件的table格式
[ENTMAP {ON|OFF}]
指定在SQL * Plus中是否用HTML字符实体如<, >, " and &等替换特殊字符<, >, " and & 。默认设置是ON
其实要生成漂亮的html文件,还是在于html的css设置、调整,这个是个复杂、细致活。当然网上有一些好看的css设置模板,你可以参考、借鉴、甚至直接拿过来使用。
SET MARKUP HTML ON ENTMAP OFF;
SET MARKUP HTML ON SPOOL ON -
HEAD '<title>My Html Report Test</title> -
<style type="text/css"> -
table {border:1px solid #9ec9ec; width:60%;border-width:1px 0px 0px 1px;text-align:center;font-size:12px} -
td {border:solid #9ec9ec; border-width:0px 1px 1px 0px; padding:4px 0px;}-
</style>'
SPOOL htmltest.html
SELECT * FROM SCOTT.SALGRADE;
SPOOL OFF;
SET MARKUP HTML OFF;
一些生成漂亮html文件的模板。
SET MARKUP HTML ON SPOOL ON -
HEAD '<title>My Report</title> -
<style type="text/css"> -
table {-
font-family: arial, sans-serif;-
font-size: 11px;-
color: #333333;-
border-width: 1px;-
border-color: #3A3A3A;-
border-collapse: collapse;-
}-
th {-
border-width: 1px;-
padding: 8px;-
border-style: solid;-
border-color: #517994;-
background-color: #B2CFD8;-
}-
tr:hover td {-
background-color: #DFEBF1;-
}-
td {-
border-width: 1px;-
padding: 8px;-
border-style: solid;-
border-color: #517994;-
background-color: #ffffff;-
}-
</style>'
spool htmltest.html
select * from scott.salgrade;
spool off;
set markup html off;
SET MARKUP HTML ON SPOOL ON -
HEAD '<title>My Report</title> -
<style type="text/css"> -
table{-
font-family: verdana, arial, sans-serif;-
font-size: 11px;-
color: #333333;-
border-width: 1px;-
border-color: #3A3A3A;-
border-collapse: collapse;-
}-
th {-
border-width: 1px;-
padding: 8px;-
border-style: solid;-
border-color: #FFA6A6;-
background-color: #D56A6A;-
color: #ffffff;-
}-
tr:hover td {-
cursor: pointer;-
background-color: #F7CFCF;-
}-
td {-
border-width: 1px;-
padding: 8px;-
border-style: solid;-
border-color: #FFA6A6;-
background-color: #ffffff;-
}-
</style>'
spool htmltest.html
select * from scott.salgrade;
spool off;
set markup html off;
参考资料:
https://docs.oracle.com/cd/B19306_01/server.102/b14357/ch7.htm
https://blog.mafr.de/2006/12/27/creating-html-reports-with-sqlplus-2/
简单:
set feedback off
set markup html on;
spool e:/data/salgrade.html
select * from scott.emp;
spool off
set markup html off
exit
结果:
EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
---|---|---|---|---|---|---|---|
7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | 20 | |
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 |
7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 |
7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | 20 | |
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30 |
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | 30 | |
7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | 10 | |
7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | 20 | |
7839 | KING | PRESIDENT | 1981-11-17 00:00:00 | 5000 | 10 | ||
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30 |
7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | 20 | |
7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | 30 | |
7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | 20 | |
7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | 10 |
set feedback off
set markup html on;
set markup html on spool on preformat off entmap on -
head ' -
<title> SCOTT.EMP表的数据</title> -
<style type="text/css"> -
body {font:11px Courier New,Helvetica,sans-serif; color:black; background:White;} -
p {font:11px Courier New,Helvetica,sans-serif; color:black; background:White;} -
table,tr,td {font:11px Courier New,Helvetica,sans-serif; color:Black; background:#FFFFCC; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;} -
th {font:bold 11px Courier New,Helvetica,sans-serif; color:White; background:#0066cc; padding:0px 0px 0px 0px;} -
h1 {font:bold 12pt Courier New,Helvetica,Geneva,sans-serif; color:White; background-color:White; border-bottom:1px solid #cccc99; margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;} -
h2 {font:bold 11pt Courier New,Helvetica,Geneva,sans-serif; color:White; background-color:White; margin-top:4pt; margin-bottom:0pt;} -
a {font:11px Courier New,Helvetica,sans-serif; color:#663300; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
a.link {font:11px Courier New,Helvetica,sans-serif; color:#663300; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
a.noLink {font:11px Courier New,Helvetica,sans-serif; color:#663300; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
a.noLinkBlue {font:11px Courier New,Helvetica,sans-serif; color:#0000ff; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
a.noLinkDarkBlue {font:11px Courier New,Helvetica,sans-serif; color:#000099; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
a.noLinkRed {font:11px Courier New,Helvetica,sans-serif; color:#ff0000; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
a.noLinkDarkRed {font:11px Courier New,Helvetica,sans-serif; color:#990000; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
a.info:hover {background:#eee;color:#000000; position:relative;} -
a.info span {display: none; } -
a.info:hover span {font-size:11px!important; color:#000000; display:block;position:absolute;top:30px;left:40px;width:150px;border:1px solid #ff0000; background:#FFFF00; padding:1px 1px;text-align:left;word-wrap: break-word; white-space: pre-wrap; white-space: -moz-pre-wrap} -
</style>' -
body 'BGCOLOR="#C0C0C0"'
SET MARKUP html TABLE 'WIDTH="100%" border="1" summary="Script output" cellspacing="0px" style="border-collapse:collapse;" '
define data_path=E:/data
col ymd new_value v_ymd
select to_char(sysdate,'YYYYMMDDHH24MISS') ymd FROM dual;
spool &data_path/result_html_&&v_ymd..html
select * from scott.emp;
spool off
set markup html off
exit
结果:
EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
---|---|---|---|---|---|---|---|
7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | 20 | |
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 |
7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 |
7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | 20 | |
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30 |
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | 30 | |
7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | 10 | |
7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | 20 | |
7839 | KING | PRESIDENT | 1981-11-17 00:00:00 | 5000 | 10 | ||
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30 |
7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | 20 | |
7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | 30 | |
7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | 20 | |
7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | 10 |
About Me
.............................................................................................................................................
● 本文作者:小麦苗,部分内容整理自网络,若有侵权请联系小麦苗删除
● 本文在itpub(http://blog.itpub.net/26736162/abstract/1/)、博客园(http://www.cnblogs.com/lhrbest)和个人微信公众号(xiaomaimiaolhr)上有同步更新
● 本文itpub地址:http://blog.itpub.net/26736162/abstract/1/
● 本文博客园地址:http://www.cnblogs.com/lhrbest
● 本文pdf版、个人简介及小麦苗云盘地址:http://blog.itpub.net/26736162/viewspace-1624453/
● 数据库笔试面试题库及解答:http://blog.itpub.net/26736162/viewspace-2134706/
● DBA宝典今日头条号地址:http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826
.............................................................................................................................................
● QQ群号:230161599(满)、618766405
● 微信群:可加我微信,我拉大家进群,非诚勿扰
● 联系我请加QQ好友(646634621),注明添加缘由
● 于 2018-01-01 06:00 ~ 2018-01-31 24:00 在魔都完成
● 文章内容来源于小麦苗的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解
● 版权所有,欢迎分享本文,转载请保留出处
.............................................................................................................................................
● 小麦苗的微店:https://weidian.com/s/793741433?wfr=c&ifr=shopdetail
● 小麦苗出版的数据库类丛书:http://blog.itpub.net/26736162/viewspace-2142121/
● 好消息:小麦苗OCP、OCM开班啦,详情请点击:http://blog.itpub.net/26736162/viewspace-2148098/
.............................................................................................................................................
使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号(xiaomaimiaolhr)及QQ群(DBA宝典),学习最实用的数据库技术。
小麦苗的微信公众号 小麦苗的DBA宝典QQ群2 《DBA笔试面宝典》读者群 小麦苗的微店
.............................................................................................................................................