為開發人員編寫每天凌晨2點執行一個儲存過程_dbms_job

wisdomone1發表於2010-07-20
Create Or Replace Procedure P_O_Ins_Upd_Classappraiseinfo
As
Begin
merge into O_CLASSAPPRAISEINFO a
 using(select a.classid,count(b.appraise) commentnumber,count(distinct a.userid) engagednumber,ceil(sum(b.appraise)/count(distinct b.userid)) starlevel
       from  o_classpartyinfo a,o_classpartyinfo b
       where a.logid=b.logid and a.classid=b.classid  and b.appraise is not  null
       group by a.classid
      ) b
  on (a.classid=b.classid)
 when matched then update set  a.appraisename=b.commentnumber,a.engagednumber=b.engagednumber,a.starlevel=b.starlevel
 When Not Matched Then Insert (A.Appraiseid,A.Classid,A.Appraisename,A.Engagednumber,A.Starlevel,A.Createtime)
                       Values(Lpad(Seq_Onlinemv.Nextval,16,'0'),B.Classid,B.Commentnumber,B.Engagednumber,B.Starlevel,Sysdate);
commit;                      
End;
/
 

exec P_O_Ins_Upd_Classappraiseinfo
 

declare
  v_job BINARY_INTEGER;
begin
dbms_job.submit(v_job,'P_O_Ins_Upd_Classappraiseinfo;',trunc(sysdate+24/24)+2/24,'trunc(sysdate+24/24)+2/24+24/24');
end;
/

select job,to_char(next_date,'yyyy-mm-dd hh24:mi:ss'),interval from user_jobs;

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/9240380/viewspace-668438/,如需轉載,請註明出處,否則將追究法律責任。

相關文章