基于SQL结果自动电子邮件 [英] Automated e-mail based on SQL results

问题描述

我们需要写一个bash脚本来执行一个SQL查询，然后通过发送一些结果的自动电子邮件。我们的SQL版本是Oracle8i企业版发行8.1.7.4.0和SELECT语句来得到我们想要的结果是：

We need to write a BASH script to perform a SQL query and then send an automated e-mail using some of the results. Our SQL version is Oracle8i Enterprise Edition Release 8.1.7.4.0 and the SELECT statement to get the results that we want is:

SELECT kred_lim.kunr, kust_adr.ku_email, kred_lim.kred_limit, kred_lim.kred_zu_zahlen
FROM kred_lim, kust_adr
WHERE kred_lim.kred_zu_zahlen > kred_lim.kred_limit
AND kred_lim.kunr = kust_adr.ku_nr;

因此​​，这将显示客户数，客户的电子邮件地址，信用额度，并仅限于客户当前未完成的订单价值谁的未完成订单的价值低于其信用额度更大。我们只是停留在哪里开始，或如何将这些成果转化为电子邮件。

So this will show the customer number, customer e-mail address, credit limit and current outstanding orders value limited to customers who's outstanding orders value is greater than their credit limit. We are just stuck on where to start, or how to incorporate those results into an e-mail.

我们只需要发送到每一个简单的电子邮件，并解释说，他们是在他们的信用额度，并显示在电子邮件的信用额度和目前未完成订单值。

We just need a simple e-mail sent to each one, explaining that they're over their credit limit and showing their credit limit and current outstanding orders value in that e-mail.

任何实例或指针将AP preciated。

Any examples or pointers would be appreciated.

推荐答案

我会写：

#!/bin/bash

sql="
    set pagesize 0
    set feedback 0

    SELECT kred_lim.kunr ||','|| kust_adr.ku_email ||','|| kred_lim.kred_limit ||','|| kred_lim.kred_zu_zahlen
    FROM kred_lim, kust_adr
    WHERE kred_lim.kred_zu_zahlen > kred_lim.kred_limit
    AND kred_lim.kunr = kust_adr.ku_nr;
"

email_template="Subject: Credit limit
To: %s

Customer number: %s
Credit limit: %s
Current orders: %s
"

sqlplus $user/$pass@$db <<< "$sql" |
while IFS=, read -r num email limit orders; do
    printf "$email_template" "$email" "$num" "$limit" "$orders" |
    /path/to/sendmail -f [e-mail] -oi -t
done

这篇关于基于SQL结果自动电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！