如何在Shell脚本中绘制包含SQL查询结果的HTML表并发送到邮件

4si2a6ki 于 2022-12-13 发布在 Shell

关注(0)|答案(3)|浏览(149)

我想绘制包含SQL查询记录的HTML表，并在shell脚本中发送到邮件。下面是shell脚本：-

#!/usr/bin/ksh
out=$(for id in 1 2 3 4
      do
      sqlplus -s <user>/<password>@<db> <<EOF
      spool out.txt;
      select id, count(*) from <table> where id=${id};
      spool off;
      exit;
      EOF
      done)

 echo ${out} > out.txt
(uuencode out.txt out.txt)| cat out.txt| mailx -s "Before" abc@xyz.com

而且我得到的查询输出是不同的格式，而不是HTML表格格式。表格格式应该是这样的（在有边框线的表格中）

ID            Results
        Before      After
1          12         15
2          27         30 
3          45         52

请你帮我画出这个记录的HTML表。

shell

来源：https://stackoverflow.com/questions/58131723/how-to-draw-html-table-having-contain-result-of-sql-query-and-send-to-mail-in-sh

3条答案

按热度按时间

mrzz3bfm1#

您可以尝试将html代码放在consult之后和之前，并在sql中进行连接。例如：

#!/usr/bin/ksh

#start table
echo "<table><thead><tr><th>ID</th><th>BEFORE></th><th>AFTER</th></thead><tbody><th>" > out.txt
out=$(for id in 1 2 3 4
      do
      sqlplus -s <user>/<password>@<db> <<EOF
      SET HEADING OFF FEEDBACK OFF ECHO OFF PAGESIZE 0
      spool out.txt;
      select '<td>'||ID||'</td><td>' || BEFORE || '</td><td>' || BEFORE || '</td>' from <table> where id=${id};
      spool off;
      exit;
      EOF
      done)

 echo ${out} >> out.txt

 #finish table 
 echo "</th></tbody></table> >> out.txt

(uuencode out.txt out.txt)| cat out.txt| mailx -s "Before" abc@xyz.com

我没有测试代码

赞(0）回复(0）举报 2022-12-13

woobm2wo2#

现在，我在我的环境中重现了这个问题

#!/usr/bin/ksh

#start table
echo "<table><thead><tr><th>ID</th><th>BEFORE</th><th>AFTER</th></tr></thead><tbody><th>" > out.html

rot_sqlplus(){

sqlplus -s /nolog <<SQLPLUSEND
conn user/passwd@database
SET HEADING OFF;
SET FEEDBACK OFF;
SET ECHO OFF;
SET PAGESIZE 0;
select '<tr><td>'||ID|| '</td><td>' || BEFORE || '</td><td>' || BEFORE || '</td></tr>' from teste where id=$1;
quit
SQLPLUSEND

}

out=$(for id in 1 2 3 4
     do
        rot_sqlplus $id
      done)

 echo ${out} >> out.html

#finish table 
echo "</th></tbody></table>" >> out.html

cat out.html

输出结果. html

<table>
    <thead>
        <tr>
            <th>ID</th>
            <th>BEFORE</th>
            <th>AFTER</th>
            </tr>
    </thead>
    <tbody>
        <th>
            <tr>
                <td>1</td><td>12</td><td>12</td>
            </tr>
            <tr>
                <td>2</td><td>27</td><td>27</td></tr>
            <tr>
                <td>3</td><td>45</td><td>45</td>
            </tr>
        </th>
    </tbody>
</table>

在浏览器中查看