1.构造实现功能（发送消息）的存储过程：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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：
|
1 2 3 4 5 |
variable job1 number; begin dbms_job.submit(:job1,'send_emp_birthday_msg;',sysdate,'sysdate+1'); --每天运行send_emp_birthday_msg过程一次 end; |
3. 运行JOB：
|
1 2 3 |
begin dbms_job.run(:job1); end; |
Comments