大家好,我有一段sql代码,需要每日定时执行,该怎么办啊?
代码如下:
merge into pos_points a
using(select distinct(a.ac_id) ac_id,0 points,to_date(a.opn_date,'yyyymmdd') opn_date,c.br_name opn_br_no,a.name customer_name,d.id_no customer_id,
case when d.id_type='1' then '身份证'
when d.id_type='2' then '户口簿'
when d.id_type='3' then '护照'
when d.id_type='4' then '军人证'
when d.id_type='5' then '回乡证'
when d.id_type='6' then '居住证'
when d.id_type='7' then '驾照'
end id_type,0 count
from dd_mst@dhcc a,mdm_ac_rel@dhcc b,com_branch@dhcc c,cif_id_code_rel@dhcc d
where a.ac_id=b.ac_id and (mdm_code='0020' or mdm_code='0021')
and to_date(a.opn_date,'yyyy-mm-dd')=trunc(sysdate-1)
and a.opn_br_no=c.br_no and a.cif_no=d.cif_no and d.id_type !='N') t
on(a.ac_id=t.ac_id)
when matched then
update set a.points=a.points
when not matched then
insert values(t.ac_id,t.points,t.opn_date,t.opn_br_no,t.customer_name,t.customer_id,t.id_type,t.count);commit;insert into pointschange_rec select b.ac_id,a.ac_no,
case
when a.stat='0' then
0
else
1
end,a.tx_amt,to_date(a.tx_date,'yyyymmdd'),
to_date(case
when a.stat='0' then
''
else
to_char(a.tx_date)
end,'yyyymmdd'),trunc(sysdate),a.mrchnm
from chnl_self_trade@dhcc a,mdm_ac_rel@dhcc b
where a.ttyp='2'
and a.ac_no=b.ac_no
and to_date(tx_date,'yyyymmdd')=trunc(sysdate-1);commit;merge into pos_points a
using(select ac_id ac_id, trunc(sum(b.pcsum * (b.addreduce_flag*(-2)+1))) pnt_change,count(*) pcount from pointschange_rec b where b.pcdate=trunc(sysdate-1) group by b.ac_id) t
on(a.ac_id=t.ac_id)
when matched then
update set a.points=a.points+pnt_change,a.count=a.count + t.pcount
when not matched then
insert values (t.ac_id,t.pnt_change,sysdate,'','','','',pcount);commit;
大家帮帮我啊
代码如下:
merge into pos_points a
using(select distinct(a.ac_id) ac_id,0 points,to_date(a.opn_date,'yyyymmdd') opn_date,c.br_name opn_br_no,a.name customer_name,d.id_no customer_id,
case when d.id_type='1' then '身份证'
when d.id_type='2' then '户口簿'
when d.id_type='3' then '护照'
when d.id_type='4' then '军人证'
when d.id_type='5' then '回乡证'
when d.id_type='6' then '居住证'
when d.id_type='7' then '驾照'
end id_type,0 count
from dd_mst@dhcc a,mdm_ac_rel@dhcc b,com_branch@dhcc c,cif_id_code_rel@dhcc d
where a.ac_id=b.ac_id and (mdm_code='0020' or mdm_code='0021')
and to_date(a.opn_date,'yyyy-mm-dd')=trunc(sysdate-1)
and a.opn_br_no=c.br_no and a.cif_no=d.cif_no and d.id_type !='N') t
on(a.ac_id=t.ac_id)
when matched then
update set a.points=a.points
when not matched then
insert values(t.ac_id,t.points,t.opn_date,t.opn_br_no,t.customer_name,t.customer_id,t.id_type,t.count);commit;insert into pointschange_rec select b.ac_id,a.ac_no,
case
when a.stat='0' then
0
else
1
end,a.tx_amt,to_date(a.tx_date,'yyyymmdd'),
to_date(case
when a.stat='0' then
''
else
to_char(a.tx_date)
end,'yyyymmdd'),trunc(sysdate),a.mrchnm
from chnl_self_trade@dhcc a,mdm_ac_rel@dhcc b
where a.ttyp='2'
and a.ac_no=b.ac_no
and to_date(tx_date,'yyyymmdd')=trunc(sysdate-1);commit;merge into pos_points a
using(select ac_id ac_id, trunc(sum(b.pcsum * (b.addreduce_flag*(-2)+1))) pnt_change,count(*) pcount from pointschange_rec b where b.pcdate=trunc(sysdate-1) group by b.ac_id) t
on(a.ac_id=t.ac_id)
when matched then
update set a.points=a.points+pnt_change,a.count=a.count + t.pcount
when not matched then
insert values (t.ac_id,t.pnt_change,sysdate,'','','','',pcount);commit;
大家帮帮我啊
Job在PL/SQL的Job中添加,what写存储过程的名字,加个;Next date写你要从什么时候开始运行,
Interval写间隔,Next Date+1就表示隔天执行,应该这样就可以了把。
可能别的高手有更好的办法。
先写个存储过程,比如叫SP_INSERT
然后写JOB
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'SP_INSERT;'
,next_date => to_date('11/11/2008 06:00:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'TRUNC(SYSDATE+1)+1/4'
,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
大哥这句话是什么意思啊?
trunc是取整函数
trunc(sysdate)当把前时间取整,就是当天的0点
TRUNC(SYSDATE+1)当前时间+1天再取整,就是第二天0点
TRUNC(SYSDATE+1)+1/4就是第二天0点再加上6小时
1/4=6/24
其实如果不要求很精确的话,直接SYSDATE+1也可以的,只要你首次执行是6点,不过时间长了会有累积误差的用TRUNC(SYSDATE+1)+1/4能保证每次是6点整执行