NC某个子集的内容按照另外的一个子集的内容自动定时同步更新
场景：某家公司，任职资格的子集的内容，按照内部异动记录的最新记录，自动每晚2点定时更新。
实现方式：NC本身无这个定时任务，实现思路为写存储过程，然后要么通过Oracle的定时任务处理，要么通过客开调存储过程在后台任务部署节点进行配置。这里由于客开资源紧张，通过Oracle自带的定时任务处理。（要做到实时也可以，通过触发器处理，这里不展开）
--1、拼装hi_psndoc_glbdef1要插入的记录，已经写成视图了HR_ZD_HI_PSNGLBDEF1
select
to_char(sysdate,'yyyy-mm-dd hh:ii:ss') as CREATIONTIME,
case when 1=1 then '1001111000000014FA0N' else '' end CREATOR,
hi_psnjob.jobglbdef6 as glbdef1,
hi_psnjob.pk_psncl as glbdef10,
hi_psnjob.jobglbdef14 as glbdef11,
hi_psnjob.jobglbdef15 as glbdef12,
hi_psnjob.jobglbdef16 as glbdef13,
hi_psnjob.pk_postseries as glbdef2,
hi_psnjob.pk_post as glbdef3,
hi_psnjob.jobglbdef8 as glbdef4,
hi_psnjob.jobglbdef9 as glbdef5,
hi_psnjob.jobglbdef10 as glbdef6,
hi_psnjob.jobglbdef11 as glbdef7,
hi_psnjob.jobglbdef12 as glbdef8,
hi_psnjob.jobglbdef13 as glbdef9,
case when 1=1 then 'Y' else '' end LASTFLAG,
case when 1=1 then '~' else '' end MODIFIER,
tablea.pk_psndoc as pk_psndoc,
tablea.pk_psndoc as pk_psndoc_sub,
case when 1=1 then 0 else null end RECORDNUM,
to_char(sysdate,'yyyy-mm-dd hh:ii:ss')as TS,
case when 1=1 then 0 else null end DR
from(
--再次锁定每人一条记录s
SELECT TMP.pk_psndoc,TMP.pk_psnjob
FROM (SELECT
T.*,
ROW_NUMBER() OVER(PARTITION BY T.pk_psndoc ORDER BY T.pk_psnjob DESC) FLAG
FROM (
--每人一条记录s
select
DISTINCT
hi_psnjob.pk_psndoc,
HI_PSNJOB.pk_psnjob
from hi_psnjob
where 1=1
and hi_psnjob.ismainjob ='Y'
and hi_psnjob.pk_hrorg ='0001AA10000000004Z0N'
and hi_psnjob.lastflag ='Y'
--每人一条记录e
)T
) TMP
WHERE TMP.FLAG = 1 -- 取第二条时：flag = 2
--再次锁定每人一条记录e
)tablea LEFT OUTER JOIN hi_psnjob on hi_psnjob.pk_psnjob=tablea.pk_psnjob
--where tablea.pk_psndoc='000111100000000JK8II'
--2、先把历史的任职资格全部干掉
DELETE from hi_psndoc_glbdef1 where pk_psndoc in (select pk_psndoc from HR_ZD_HI_PSNGLBDEF1)
--2.1 任职资格全部干掉存储过程
CREATE or replace PROCEDURE DEL_ZD_HI_PSNGLBDEF1 -- LASTEDUCATIONN存储过程名称
is
BEGIN
DELETE from hi_psndoc_glbdef1 where pk_psndoc in (select pk_psndoc from HR_ZD_HI_PSNGLBDEF1);
end;
--3、根据工作记录同步更新到任职资格表里面去
insert into hi_psndoc_glbdef1(
CREATIONTIME,
CREATOR,
glbdef1,
glbdef10,
glbdef11,
glbdef12,
glbdef13,
glbdef2,
glbdef3,
glbdef4,
glbdef5,
glbdef6,
glbdef7,
glbdef8,
glbdef9,
LASTFLAG,
MODIFIER,
pk_psndoc,
pk_psndoc_sub,
RECORDNUM,TS,DR
)(select * from HR_ZD_HI_PSNGLBDEF1)
--3.1 根据工作记录同步更新到任职资格表里面去存储过程
CREATE or replace PROCEDURE ADD_ZD_HI_PSNGLBDEF1 -- LASTEDUCATIONN存储过程名称
is
BEGIN
insert into hi_psndoc_glbdef1(
CREATIONTIME,
CREATOR,
glbdef1,
glbdef10,
glbdef11,
glbdef12,
glbdef13,
glbdef2,
glbdef3,
glbdef4,
glbdef5,
glbdef6,
glbdef7,
glbdef8,
glbdef9,
LASTFLAG,
MODIFIER,
pk_psndoc,
pk_psndoc_sub,
RECORDNUM,TS,DR
)(select * from HR_ZD_HI_PSNGLBDEF1);
end;
--4、设置定时任务
declare tm_joba number; --tm_joba定时任务名称
begin
sys.dbms_job.submit(
tm_joba, --任务名称
'DEL_ZD_HI_PSNGLBDEF1;ADD_ZD_HI_PSNGLBDEF1;', --执行的过程
sysdate, --执行时间
'TRUNC(sysdate) + 1 +2/ (24)'); --下次执行时间
COMMIT;
end;