small_program_task 這張表的資料是待發送的email通知，再次之前已經有一個job會定時掃描固定時間內未接收到小程式回報狀態將其寫入到該表，send_flag為N，表示為寄過通知。
email_notice_expired這個procedure負責寄出通信，簡單的HTML也直接寫在procedure裡面。
/*EMAIL通知procedure email_notice_expired
scan small_program_task表，將send_flag標記為N的寄出去。
written by milo 20170309
*/
CREATE OR REPLACE PROCEDURE email_notice_expired AS
v_email_recipient_string varchar(1000);
v_email_html_content varchar2(32767);
v_expired_minutes number;
v_last_active_time date;
v_station varchar(200);
v_program varchar(200);
v_timeout number;
v_task_id varchar2(36);
CURSOR c_task_table IS
SELECT t1.station,
t1.program_id,
t1.last_active_time,
t1.program_timeout,
t1.expired_time,
t2.mail_for_error,
t1.job_no
FROM small_program_task t1
left join TK_CUST_PROG_M t2
ON t1.station = t2.custom_no
WHERE t1.send_flag = 'N';
BEGIN
OPEN c_task_table;
LOOP
FETCH c_task_table
INTO v_station,
v_program,
v_last_active_time,
v_timeout,
v_expired_minutes,
v_email_recipient_string,
v_task_id;
EXIT WHEN c_task_table%NOTFOUND; --null or empty string
IF (v_email_recipient_string IS NULL OR v_email_recipient_string = ' ') THEN
CONTINUE;
END IF;
--dbms_output.put_line('v_email_recipient_string: ' || v_email_recipient_string); v_email_html_content := '<html>
<head>
<title></title>
</head>
<body>
<p> Hi ' || v_station || ',</p>
<p> This email is to notify you that the following program was not sent running
status to server. Please check it ASAP.</p>
<p> <Detail></p>
<p> ------------------------------------------------------------------------------</p>
<p> <b>Program Name | Last Active Time | Duration(Minutes)</b></p>
<p> ' || v_program || ' | ' ||
to_char(v_last_active_time,
'dd-mm-yyyy hh24:mi') || ' | ' ||
v_expired_minutes || '</p>
<p> ------------------------------------------------------------------------------</p>
</body>
</html>'; --call procedure to send email.
send_mail(p_to => v_email_recipient_string,
p_from => 'milo@***.com',
p_subject => 'Program Status Notice(' || v_program || ')',
p_text_msg => 'This is a test message.',
p_html_msg => v_email_html_content,
p_smtp_host => 'spam.***.com',
p_account => 'milo@***.com',
p_password => '***');
-- set send_flag 'Y' that an email has been sent.
update small_program_task set send_flag = 'Y' where job_no = v_task_id;
commit;
END LOOP;
CLOSE c_task_table;
END;
可直接先測試以上的procedure是否可以成功寄信出去。
--測試procedure email_notice_expired
begin
email_notice_expired;
end;
寫完了procedure，之後就是設置一個oracle排程去定時call這個procedure寄信就可以了。
/*call the stored procedure get_expired_program every 5 minutes.
written by milo on 20170309
*/
begin
sys.dbms_scheduler.create_job(job_name => 'PLOEC.EMAIL_NOTICE_EXPIRED_TASK',
job_type => 'STORED_PROCEDURE',
job_action => 'email_notice_expired',
start_date => to_date('06-03-2017 00:00:00', 'dd-mm-yyyy hh24:mi:ss'),
repeat_interval => 'Freq=Minutely;Interval=10',
end_date => to_date('07-03-2049 00:00:00', 'dd-mm-yyyy hh24:mi:ss'),
job_class => 'DBMS_JOB$',
enabled => true,
auto_drop => false,
comments => '每10分鐘掃呼叫get_expired_program一次');
end;
/
可用以下SQL statement查看schedule的下次執行時間等。
--查看當前的schedule
SELECT * FROM dba_scheduler_jobs WHERE job_name = 'EMAIL_NOTICE_EXPIRED_TASK';