----创建需要定时执行的存储过程:
create or replace procedure P_CG_Abnormal_Judgment
is
p_budget_status VARCHAR2(50);
p_bu_pur_m_id VARCHAR2(50);
BEGIN
--建立游标
declare
Cursor ss is
select
t.bu_pur_m_id
from T_EIM_BU_PUR_M t
left join t_eap_wf_process_runtime r
on t.bu_pur_m_id = r.business_no
where r.process_module_id
in (
select process_guid
from (select
m.process_guid
from
t_eap_wf_process_module m
where m.process_name
in ('项目采购申请')
order by m.last_updated_date desc
)
where rownum = 1
)
and (process_status = '4');
BEGIN
for b in ss loop
SELECT
p.budget_status,
m.bu_pur_m_id
INTO
p_budget_status,
p_bu_pur_m_id
FROM
T_EIM_BU_PUR_M M
INNER JOIN T_EIM_BU_PROJECT_INFO T
ON M.PROJECT_CODE = T.PROJECT_CODE
LEFT JOIN t_pms_bu_budget_info P
ON T.WBS_CODE = P.WBS_CODE
and p.arrival_fee != p.alter_budget
and P.budget_status=0;
IF p_budget_status =0 THEN
insert into t_eap_wf_remind_email_runtime
(id,
userid,
remindtime,
remindstatus,
email_title,
email_content)
select sys_guid(),
user_id,
sysdate,
'0',
'项目采购申请异常信息',
p_budget_status
from T_DOA_BPC_EMAIL_USER
commit;
END IF;
dbms_output.put_line(p_bu_pur_m_id);
END loop;/*结束for循环*/
dbms_output.put_line('输出结束!');
end;
END P_CG_Abnormal_Judgment;
--删除存储过程
--DROP PROCEDURE P_CG_Abnormal_Judgment
----功能说明:每5分钟定时执行存储过程
create
declare job05 number;
begin
dbms_job.submit(job05,'P_CG_Abnormal_Judgment;',sysdate, 'sysdate+5/1400');
commit;
end;
--查询job
select * from user_jobs;
select job, what from dba_jobs
where what = 'P_CG_Abnormal_Judgment;'
--运行job
begin
dbms_job.run(272);
end;
--查询job下次运行的时间
select job,next_date,what from dba_jobs
where job='272';
--删除job
begin
dbms_job.remove(272);
end
注意这里面的分号