sql 整理

CREATE TABLE `CELLOL.TAPS_CT_SRC_GRP_MSTR` (
  `SRC_GRP_ID` varchar(50),
  `SRC_GRP_NM` varchar(100),
  `SRC_GRP_LEVEL` varchar(30),
  `PARENT_SRC_GRP_ID` varchar(50),
  'DEL_YN' varchar(1),
  'INS_PERSON_ID' varchar(50),
  'INS_DTM' TIMESTAMP(10),
  'UPD_PERSON_ID' varchar(50),
  'UPD_DTM' TIMESTAMP(10),
) primary key SRC_GRP_ID

 

CREATE TABLE `CELLOL.TAPS_CT_SRC_GRP_MPG` (
  `LSS_ID` varchar(50),
  `SRC_GRP_ID` varchar(50),
  `SRC_GRP_LEVEL1` varchar(30),
  `SRC_GRP_LEVEL2` varchar(30),
  `SRC_GRP_LEVEL3` varchar(30),
  `SRC_GRP_LEVEL4` varchar(30),
  `SRC_GRP_LEVEL5` varchar(30),
  `USE_YN` varchar(1),
  'DEL_YN' varchar(1),
  'INS_PERSON_ID' varchar(50),
  'INS_DTM' TIMESTAMP(10),
  'UPD_PERSON_ID' varchar(50),
  'UPD_DTM' TIMESTAMP(10),
) primary LSS_ID,SRC_GRP_ID

WITH TEMP_TAB(SRC_GRP_ID,SRC_GRP_LEVEL,PARENT_SRC_GRP_ID,PATH) as(
    SELECT SRC_GRP_ID,TO_NUMBER(SRC_GRP_LEVEL) as SRC_GRP_LEVEL,PARENT_SRC_GRP_ID,SRC_GRP_ID as PATH
    from CELLOL.TAPS_CT_SRC_GRP_MSTR
    where SRC_GRP_LEVEL=1 AND DEL_YN='N'
    union all
    select A.SRC_GRP_ID,B.SRC_GRP_LEVEL+1 as SRC_GRP_LEVEL,A.PARENT_SRC_GRP_ID,B.PATH||'||'||A.SRC_GRP_ID AS PATH
    from CELLOL.TAPS_CT_SRC_GRP_MSTR A,TEMP_TAB B
    where A.PARENT_SRC_GRP_ID=B.SRC_GRP_ID
    AND DEL_YN='N'
)
,SRC_GRP AS (
    select SRC_GRP_ID,SRC_GRP_LEVEL,PATH
    SUBSTR(PATH,1,3) as SRC_GRP_LEVEL1,
    SUBSTR(PATH,6,8) as SRC_GRP_LEVEL2,
    SUBSTR(PATH,16,8) as SRC_GRP_LEVEL3
    from TEMP_TAB
)
,SRC_MAP_MASTER AS(
    SELECT A.SRC_GRP_LEVEL1,L1.SRC_GRP_NM as SRC_GRP_LEVEL1_NM,
    A.SRC_GRP_LEVEL2,L2.SRC_GRP_NM as SRC_GRP_LEVEL2_NM,
    A.SRC_GRP_LEVEL3,L3.SRC_GRP_NM as SRC_GRP_LEVEL3_NM
    from SRC_GRP A,
    CELLOL.TAPS_CT_SRC_GRP_MSTR L1,
    CELLOL.TAPS_CT_SRC_GRP_MSTR L2,
    CELLOL.TAPS_CT_SRC_GRP_MSTR L3
    where A.SRC_GRP_LEVEL=3
    AND A.SRC_GRP_LEVEL1 = L1.SRC_GRP_ID
    AND L1.DEL_YN='N'
    AND A.SRC_GRP_LEVEL2 = L2.SRC_GRP_ID
    AND L2.DEL_YN='N'
    AND A.SRC_GRP_LEVEL3 = L3.SRC_GRP_ID
    AND L3.DEL_YN='N'
    order by SRC_GRP_LEVEL1_NM,SRC_GRP_LEVEL2_NM,SRC_GRP_LEVEL3_NM)
select 'KOREA' as AREA_NM,'T100' as LSS_ID,'SDSK' as LSS_NM
MSTR.SRC_GRP_LEVEL1,MSTR.SRC_GRP_LEVEL1_NM,
MSTR.SRC_GRP_LEVEL2,MSTR.SRC_GRP_LEVEL2_NM,
MSTR.SRC_GRP_LEVEL3,MSTR.SRC_GRP_LEVEL3_NM,
NVL(MPG.USE_YN,'N') as USE_YN
from SRC_MAP_MASTER MSTR left join CELLOL.TAPS_CT_SRC_GRP_MPG MPG
on MPG.SRC_GRP_ID = MSTR.SRC_GRP_LEVEL3 and MPG.LSS_ID = MSTR.LSS_ID

 

 

insert into  CELLOL.TAPS_CT_SRC_GRP_MSTR(SRC_GRP_ID,SRC_GRP_NM,SRC_GRP_LEVEL,PARENT_SRC_GRP_ID,DEL_YN) values
('AIR','Air Trans','1',null,'N')
insert into  CELLOL.TAPS_CT_SRC_GRP_MSTR(SRC_GRP_ID,SRC_GRP_NM,SRC_GRP_LEVEL,PARENT_SRC_GRP_ID,DEL_YN) values
('SG000681','Air(PROD-Dont Delete)','2','AIR','N')
insert into  CELLOL.TAPS_CT_SRC_GRP_MSTR(SRC_GRP_ID,SRC_GRP_NM,SRC_GRP_LEVEL,PARENT_SRC_GRP_ID,DEL_YN) values
('SG000683','Equipment(PROD)','3','SG000681','N')
insert into  CELLOL.TAPS_CT_SRC_GRP_MSTR(SRC_GRP_ID,SRC_GRP_NM,SRC_GRP_LEVEL,PARENT_SRC_GRP_ID,DEL_YN) values
('SG000684','Express(PROD)','3','SG000681','N')
insert into  CELLOL.TAPS_CT_SRC_GRP_MSTR(SRC_GRP_ID,SRC_GRP_NM,SRC_GRP_LEVEL,PARENT_SRC_GRP_ID,DEL_YN) values
('SG000682','General(PROD)','3','SG000681','N')

 

MERGE INTO CELLOL.TAPS_CT_SRC_GRP_MPG T
USING(
    select #{lssId} as LSS_ID,
    #{srcGrpLevel03} as SRC_GRP_ID,
    #{srcGrpLevel01} as SRC_GRP_LEVEL1,
    #{srcGrpLevel02} as SRC_GRP_LEVEL2,
    #{srcGrpLevel03} as SRC_GRP_LEVEL3,
    #{useYn} AS USE_YN,
    'N' as DEL_YN,
    #{userId} as USER_ID
    from dual
)SRC
on (T.LSS_ID=SRC.LSS_ID AND T.SRC_GRP_ID=SRC.SRC_GRP_ID)
WHEN MATCHED THEN UPDATE
   set T.USE_YN=SRC.USE_YN,T.UPD_PERSON_ID=SRC.USER_ID,T.UPD_DTM=SYSDATE
WHEN NOT MATCHED THEN
   insert (T.LSS_ID,T.SRC_GRP_ID,T.SRC_GRP_LEVEL1,T.SRC_GRP_LEVEL2,T.SRC_GRP_LEVEL3,T.USE_YN,T.DEL_YN,T.INS_PERSON_ID,T.INS_DTM)
   values
   (SRC.LSS_ID,SRC.SRC_GRP_ID,SRC.SRC_GRP_LEVEL1,SRC.SRC_GRP_LEVEL2,SRC.SRC_GRP_LEVEL3,SRC.USE_YN,SRC.DEL_YN,SRC.USER_ID,SYSDATE)