Oracle sqlplus HTML 报告 - 交替行颜色
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4791367/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle sqlplus HTML report - alternating rows color
提问by dovka
I use Oracle sqlplus "set markup html on" to quickly convert query output into HTML report -
我使用 Oracle sqlplus "set markup html on" 将查询输出快速转换为 HTML 报告 -
It's very simlpe way to publish database report online.
在线发布数据库报告是一种非常简单的方式。
I'm missing however one thing - alternating colors for every other row, This is especially helpful while viewing wide reports.
但是我错过了一件事 - 每隔一行交替使用颜色，这在查看广泛的报告时特别有用。
is there any way to embed HTML color for every row, like make it dependent on mod(rownum/2) - even/odd row number ?
有没有办法为每一行嵌入 HTML 颜色，比如让它依赖于 mod(rownum/2) - 偶数/奇数行号？
thank you !
谢谢你 ！
回答by Tony Andrews
I don't think there is, using SET MARKUP HTML ON. You would have to write your own markup something like:
我不认为有，使用 SET MARKUP HTML ON。您必须编写自己的标记，例如：
select '<tr style="color:'
|| CASE mod(rownum,2) WHEN 0 THEN 'red' ELSE 'green' END
|| '"><td>' || ename || '</td></tr>' data
from
( select ename from emp order by ename );
Then add the surrounding table tags using PROMPTs or whatever.
然后使用提示或其他方式添加周围的表格标签。
回答by dovka
There is a great solution with CSS 3 (by expert web developer Leon Zinger). Before setting markup html on, inside of the head tag, print this:
CSS 3 有一个很好的解决方案（由专家 Web 开发人员 Leon Zinger 提供）。在设置标记 html 之前，在 head 标签内打印：
prompt <style>tr:nth-child(2n) { background-color: #CCE6FF ;} tr:nth-child(2n+1) { background-color: LightGray;}</style>
(obviously the colors can be customized to meet your needs)
（显然可以定制颜色以满足您的需求）
Here's another syntax to do alternation:
这是进行交替的另一种语法：
.row:nth-child(even) {
background: #dde;
}
.row:nth-child(odd) {
background: white;
}
回答by Yordan Georgiev
:: File: sqlplus-runner-html
@ECHO off
:: go the run dir
cd %~dp0
:: this is the dir containing the batch file
set MyDir=%CD%
for %%A in (%0) do set MyDriveLetter=%%~dA
for %%A in (%0) do set MyPath=%%~pA
for %%A in (%0) do set MyName=%%~nA
for %%A in (%0) do set MyExtension=%%~xA
:: ECHO MYNAME IS %0
:: ECHO MyDriveLetter is %MyDriveLetter%
:: ECHO MyPath is %MyPath%
:: ECHO MyName is %MyName%
:: ECHO MyExtension is %MyExtension%
:: the ora script is the same as the file name + the sql extension
set OraSqlScript=%MyDir%\%MyName%.sql
:: define the oracle user name to use to connect to ora
set OracleUserName=MyOracleUserName
:: get user input
:: SET /P Pass=[Please, provide the password for the %OracleUserName%:]
:: the pass to use to connect to the service
SET Pass=MySecretPass
:: define the name of the service
set TnsServiceName=ORASERVICENAME
:: define the log file
set LogFile=%MyDriveLetter%\%MyPath%\%MyName%.log
:: define the error log file
set ErrorLogFile=%MyDriveLetter%\%MyPath%\%MyName%.error.log
:: clear the result of the previous runs
del /q %MyDir%\*.lst
del /q %MyDir%\*.log
:: Action !!!
:: call with the listener service name
:: sqlplus %OracleUserName%/%Pass%@%TnsServiceName @%OraSqlScript% %MyName% > %LogFile% 2>%ErrorLogFile%
:: call without the listener service name
sqlplus %OracleUserName%/%Pass% @%OraSqlScript% %MyName% > %LogFile% 2>%ErrorLogFile%
:: Purpose:
:: create a generic wrapper for executing oracle sql commands trough the sqplplus
:: client
:: Requirements:
:: Windows XP or newer
:: sqlplus client on local windows
:: access to oracle database
:: %MyName%.sql with the proper sqlplus syntax to execute the sql statememtns
:: Usage:
:: call from dos or double-click in Explorer
:: VersionHistory
:: 1.1.0 --- ysg --- Removed listener name
:: 1.0.0 --- ysg --- Initial version
回答by Yordan Georgiev
/* File: sqlplus-runner-csv.sql v1.0.0 docs at the end */
/* do not print echo commands */
set echo off
/* do not require feedback from the user */
set feedback off
/* set a nice prompt */
set sqlprompt ''
/* -- separate columns with a comma */
set colsep ';'
/* -- only one header row */
set pagesize 0
/*-- remove trailing blanks */
set trimspool on
/* REMOVE THE WHITE SPACE */
set trimout on
/* -- this may or may not be useful...depends on your headings. */
set headsep ON
/* how- long the line should be */
set linesize 3000
/* print the headers as well */
set heading on
/* request full html for output */
/* set markup html on spool off */
/* define the file where the output of the results should be stored */
spool "&1..html"
-- START SQL
select '<html> <head> <title>tables</title> <style type="text/css"> table tbody tr td { background-color: #eee; } table tr.odd td { background-color: #fff; } </style> </head><body><table>
' from dual ;
-- specify grey ( #CCC ) and white colors
-- open the row
select '<tr class="'
|| CASE mod(rownum,2) WHEN 0 THEN 'odd' ELSE 'even' END
|| '">'
-- print a single table cell
|| ' <td>' || OWNER || '</td>'
-- print another single table cell
|| ' <td>' || TABLE_NAME || '</td>'
-- close the row
|| '</tr>' FROM
-- IF YOU NEED ORDER BY CLAUSE AS WELL,
(
SELECT OWNER , TABLE_NAME FROM ALL_TABLES ORDER BY OWNER , TABLE_NAME
)
;
select '</table></body></html>' from dual ;
-- STOP SQL
spool off ;
/* exit the sqlplus tool */
EXIT 0;
/*
Purpose:
- to provide a single sql statement to run agains an oracle database
- to output a single file passed as the first argument to the script
VersionHistory:
1.1.0 --- 2012.04.08 - 23:04:19 --- ysg --- Added order by clause
1.0.0 --- 2012.04.08 - 22:07:19 --- ysg --- Initial creation
*/
