英文:
function to send notification
问题
我想在每位员工的合同到期前三个月发送一条消息。
我尝试这样做：
从员工表中选择雇佣日期
然后将其与sysdate进行比较，如果雇佣日期比合同结束日期早3个月，则发送通知。
英文:
I would like to send a message to each employee three months before the end of their contract
I try to do this as:
select hire_date from the emp table
and compare it with sysdate then if hire date was less than the contract end date with 3 months then send notificatin end if
答案1
得分: 0
这不应该是一个函数，而应该是一个过程。例如：
create or replace procedure p_end_of_contract_mail is
begin
for cur_r in (select e_mail
from emp
where months_between(trunc(sysdate), end_of_contract_date) = 3
)
loop
apex_mail.send(
p_to => cur_r.e_mail,
p_from => 'noreply@my_application.com',
p_body => 'Your contract ends in 3 months',
p_subj => 'End of contract');
apex_mail.push_queue;
end;
/
你应该调度它，以便每天运行，并在必要时自动发送邮件，例如：
begin
dbms_scheduler.create_job (
job_name => 'end_of_contract',
job_type => 'PLSQL_BLOCK',
job_action => 'p_end_of_contract_mail;',
start_date => SYSTIMESTAMP,
enabled => true,
repeat_interval => 'FREQ=DAILY; INTERVAL=1'
);
end;
/
英文:
That shouldn't be a function, but a procedure. For example:
create or replace procedure p_end_of_contract_mail is
begin
for cur_r in (select e_mail
from emp
where months_between(trunc(sysdate), end_of_contract_date) = 3
)
loop
apex_mail.send(
p_to => cur_r.e_mail,
p_from => 'noreply@my_application.com',
p_body => 'Your contract ends in 3 months',
p_subj => 'End of contract');
apex_mail.push_queue;
end;
/
You'd schedule it so that it runs every day and automatically sends mail whenever necessary, e.g.
begin
dbms_scheduler.create_job (
job_name => 'end_of_contract',
job_type => 'PLSQL_BLOCK',
job_action => 'p_end_of_contract_mail;',
start_date => SYSTIMESTAMP,
enabled => true,
repeat_interval => 'FREQ=DAILY; INTERVAL=1'
);
end;
/
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论