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博客

为开发人员编写每天凌晨2点运行一个存储过程_dbms_job

最新文章