create or replace procedure SYNC_PRODUCT_TREE is
begin
merge into ecc_tcm.TCM_PDM_PRD_TREE a
using (select ITEM_LEVEL,
ITEM_TYPE,
ITEM_NO,
ITEM_PARENT_NO,
CN_NAME,
EN_NAME,
INNER_MODEL,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE
from PRD_TREE_V@t_xl --
where ITEM_LEVEL <= 5 and to_char(LAST_UPDATE_DATE,'yyyy-mm-dd') = to_char(sysdate-1,'yyyy-mm-dd')
start with (ITEM_LEVEL = 1 and inner_model= '手机产品'and cn_name in('移动互联产品','其他终端产品','定制产品') )
connect by prior ITEM_NO = ITEM_PARENT_NO ) c
on (a.ITEM_NO = c.ITEM_NO)
when matched then
update
set
ITEM_LEVEL = c.ITEM_LEVEL,
ITEM_TYPE = c.ITEM_TYPE,
ITEM_NO = c.ITEM_NO,
ITEM_PARENT_NO = c.ITEM_PARENT_NO,
CN_NAME = c.CN_NAME,
EN_NAME = c.EN_NAME,
INNER_MODEL = c.INNER_MODEL,
LAST_UPDATED_BY = c.LAST_UPDATED_BY,
LAST_UPDATE_DATE = c.LAST_UPDATE_DATE,
CREATED_BY = c.CREATED_BY,
CREATION_DATE = c.CREATION_DATE
when not matched THEN
insert
(
ITEM_LEVEL,
ITEM_TYPE,
ITEM_NO,
ITEM_PARENT_NO,
CN_NAME,
EN_NAME,
INNER_MODEL,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
IS_USE)
values
(
C.ITEM_LEVEL,
C.ITEM_TYPE,
C.ITEM_NO,
C.ITEM_PARENT_NO,
C.CN_NAME,
C.EN_NAME,
C.INNER_MODEL,
C.LAST_UPDATED_BY,
C.LAST_UPDATE_DATE,
C.CREATED_BY,
C.CREATION_DATE,
'T'
);
commit;
--多个merge
exception
when others then
rollback;
end SYNC_PRODUCT_TREE;