oracle建立簡單預存程序樣本

標籤：exception   where   hat   creat   com   技術   預存程序   roc   span   

1、建立預存程序

create or replace procedure P_DMI_AGENT is
begin
delete DMI_AGENT;
insert into DMI_AGENT SELECT * FROM DMI_AGENT_v_sysdate;
insert into DMI_AGENT SELECT * FROM DMI_AGENT_v_sysdate1;
insert into DMI_AGENT SELECT * FROM DMI_AGENT_v_sysdate2;
insert into DMI_AGENT_LOG(dateid,taskno,successcnt)values(to_number(to_char(sysdate, ‘yyyymmdd‘)),‘團體人力推送介面‘,‘1‘);
exception when others then
insert into DMI_AGENT_LOG(dateid,taskno,successcnt)values(to_number(to_char(sysdate, ‘yyyymmdd‘)),‘團體人力推送介面‘,‘0‘);
end;

2、建立執行計畫

declare
job integer;
begin
dbms_job.submit(job,‘P_DMI_AGENT;‘,trunc(sysdate),‘trunc(sysdate)+1‘);
commit;
end;

3、查詢計劃

SELECT j.JOB,j.LAST_DATE,j.LAST_SEC,j.NEXT_DATE,j.NEXT_SEC,j.interval,j.WHAT FROM dba_jobs j where j.WHAT = ‘P_DMI_AGENT;‘;

 

oracle建立簡單預存程序樣本