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';