Skip to content →

使用Oracle Job实现定时向管理员发送信息

1.构造实现功能(发送消息)的存储过程:

CREATE OR REPLACE PROCEDURE "ADMIN"."SEND_EMP_BIRTHDAY_MSG" is
 cursor emp_cursoris
 select eid, ename
 from admin.employee
 where to_char(sysdate, 'mm-dd') = to_char(birthdate, 'mm-dd');
eid number(5);
 ename varchar2(20);
 msgdesc varchar2(3000);
begin
 msgdesc := '';
 for rname in emp_cursor loop
 msgdesc := msgdesc || '\n员工编号: ' || rname.eid
|| ' 员工姓名: ' || rname.ename;
 end loop;
 if length(msgdesc) > 0
 then
 msgdesc := '今天是以下员工的生日:\n' || msgdesc;
insert into admin.messagevalues(admin.msgid_sequence.nextval,
sysdate, '系统提示', 'admin', msgdesc, 1, 1, 0);
dbms_output.put_line(msgdesc);
end if;
dbms_output.put_line(msgdesc);
end;

2.创建JOB

variable job1 number;
begin
dbms_job.submit(:job1,'send_emp_birthday_msg;',sysdate,'sysdate+1');
--每天运行send_emp_birthday_msg过程一次
end;

3. 运行JOB

begin
dbms_job.run(:job1);
end;

Published in CrazyBus

Comments

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    This site uses Akismet to reduce spam. Learn how your comment data is processed.