/* 
신규 건 처리
- 테이블 관계의 최상위 레벨인 “건물정보"부터 처리
- 신규분외에 누락된 자료가 있을 경우를 위해 UPDATE 후 INSERT하도록 구현
*/
-- 건물정보 처리
UPDATE 건물정보 A
 SET (A.법정동코드, A.시도명, A.시군구명, ……) = 
(SELECT 법정동코드, 시도명, 시군구명, …… FROM 건물정보변경분 B 
	WHERE B.건물관리번호 = A.건물관리번호 AND B.이동사유코드 IN ('31', '34') AND ROWNUM = 1) 
WHERE EXISTS (SELECT 1 FROM 건물정보변경분 C WHERE C.건물관리번호 = A.건물관리번호 AND C.이동사유코드 IN ('31', '34'))

INSERT INTO 건물정보 (법정동코드, 시도명, 시군구명……)
SELECT 법정동코드, 시도명, 시군구명 …… FROM 건물정보변경분 A
WHERE A.이동사유코드 IN ('31', '34') AND NOT EXISTS (SELECT 1 FROM 건물정보 B WHERE B.건물관리번호 = A.건물관리번호)

-- 지번정보 처리
UPDATE 지번정보 A
 SET (A.법정동코드, A.시도명, A.시군구명, ……) = 
 	(SELECT B.법정동코드, B.시도명, B.시군구명, …… FROM 지번정보변경분 B 
 	WHERE B.도로명코드 = A.도로명코드 AND B.지하여부 = A.지하여부 AND B.건물본번 = A.건물본번 AND B.건물부번 = A.건물부번 
 		  AND B.지번일련번호= A.지번일련번호 AND B.이동사유코드 IN ('31', '34') AND ROWNUM = 1) 
WHERE EXISTS (SELECT 1 FROM 관련지번변경분 C WHERE  C.도로명코드 = A.도로명코드 AND C.지하여부 = A.지하여부 AND C.건물본번 = A.건물본번 
													AND C.건물부번 = A.건물부번 AND C.지번일련번호= A.지번일련번호 AND C.이동사유코드 IN ('31', '34'))
INSERT INTO 지번정보 (법정동코드, 시도명, 시군구명……)
SELECT 법정동코드, 시도명, 시군구명 …… FROM 지번정보변경분 A WHERE A.이동사유코드 IN ('31', '34') 
AND NOT EXISTS (SELECT 1 FROM 지번정보 B WHERE B.도로명코드 = A.도로명코드 AND B.지하여부 = A.지하여부 AND B.건물본번 = A.건물본번 
  												AND B.건물부번 = A.건물부번 AND B.지번일련번호= A.지번일련번호)

-- 보조출입구 처리
UPDATE 보조출입구정보 A
 SET (A.도로명코드, A.지하여부, A.건물본번, ……) = 
 	(SELECT B.도로명코드, B.지하여부, 건물본번, …… FROM 보조출입구정보변경분 B 
 	WHERE B.시군구코드 = B.시군구코드 AND B.출입구일련번호 = A.출입구일련번호 AND B.이동사유코드 IN ('31', '34') AND ROWNUM = 1) 
WHERE EXISTS (SELECT 1 FROM 보조출입구정보변경분 C WHERE C.시군구코드 = A.시군구코드 AND C.출입구일련번호 = A.출입구일련번호 AND C.이동사유코드 IN ('31', '34'))

INSERT INTO 보조출입구정보 (도로명코드, 지하여부, 건물본번 ……)
SELECT 도로명코드, 지하여부, 건물본번 …… FROM 보조출입구변경분 A WHERE A.이동사유코드 IN ('31', '34') 
AND NOT EXISTS (SELECT 1 FROM 보조출입구정보 B WHERE B.시군구코드 = A.시군구코드 AND B.출입구일련번호 = A.출입구일련번호 )

/* ORACLE 9i이상 버전에서 MERGE문 사용 예시 */
MERGE INTO 건물정보 A
USING (SELECT 법정동코드, 시도명, 시군구명, 읍면동명...... FROM 건물정보변경분 WHERE 이동사유코드 IN ('31', '34')) B 
ON (A.건물관리번호 = B.건물관리번호)
WHEN MATCHED THEN
  UPDATE SET A.법정동코드 = B.법정동코드, A.시도명 = B.시도명, A.시군구명 = B.시군구명, ……
WHEN NOT MATCHED THEN
  INSERT (A. 법정동코드 , A. 시도명 , A. 시군구명 , A.읍면동명, ……)
  VALUES (B. 법정동코드 , B. 시도명 , B. 시군구명 , B.읍면동명, ……)

/* 
폐지 건 처리
- 테이블 관계의 최하위 레벨인 “지번정보”, “보조출입구정보” 부터 처리
*/
-- 지번처리
DELETE FROM 지번정보 A
WHERE EXISTS (SELECT 1 FROM 지번정보변경분 B WHERE B.도로명코드 = A.도로명코드 AND B.지하여부 = A.지하여부 AND B.건물본번 = A.건물본번 
				AND B.건물부번 = A.건물부번 AND B.지번일련번호= A.지번일련번호 AND B.이동사유코드 = '63')

-- 보조출입구정보처리
DELETE FROM 보조출입구정보 A
WHERE EXISTS (SELECT 1 FROM 보조출입구정보변경분 B WHERE B.시군구코드 = A.시군구코드 AND B.출입구일련번호 = A.출입구일련번호 AND B.이동사유코드 = '63')

-- 건물정보처리
DELETE FROM 건물정보 A
WHERE EXISTS (SELECT 1 FROM 건물정보변경분 B WHERE B.건물관리번호 = A.건물관리번호 AND B.이동사유코드 = '63')

/* 
보조출입구와 건물정보(주소정보) 결합 이용 시 
- 건물정보의 도로명주소 항목과 보조출입구의 도로명주소 항목을 조인하여 도로명주소별 보조출입구 정보 형태로 이용
- 보조출입구는 건물군에 하나 이상 존재하는 정보이므로 건물정보와 보조출입구 결합 이용 시 건물정보를 주소 단위로 중복 제거하여(Distinct) 이용
*/

SELECT DISTINCT 
       A.우편번호,
       A.시도명 || ' ' ||
       DECODE(A.시군구명,'','', A.시군구명||' ')|| DECODE(A.읍면동구분, '0', A.읍면동명||' ') ||A.도로명 ||' '||
       DECODE(A.지하여부, 0, '', '1','지하', '2', '공중', '3', '수상')||A.건물본번 ||DECODE(A.건물부번, 0, '', '-' || A.건물부번) ||
       CASE WHEN (A.읍면동구분 = '0' AND A.공동주택여부 = '0') THEN ''
            WHEN (A.읍면동구분 = '0' AND A.공동주택여부 IN ('1','2')) THEN DECODE(A.시군구용건물명, '', '', '(' || A.시군구용건물명 || ')' )
            WHEN (A.읍면동구분 = '1' AND A.공동주택여부 = '0') THEN '(' || A.읍면동명 || ')'
            WHEN (A.읍면동구분 = '1' AND A.공동주택여부 IN ('1','2')) THEN '(' || A.읍면동명 || DECODE(A.시군구용건물명, '', '', ',' || A.시군구용건물명 ) || ')' AS "도로명주소",
       A.출입구_x좌표 AS "주출입구_x좌표", A.출입구_y좌표 AS "주출입구_y좌표", 
       B.출입구_x좌표 AS "보조출입구_x좌표", B.출입구_y좌표 AS "보조출입구_y좌표"
  FROM 건물정보 A, 보조출입구정보 B
WHERE A.주소관할읍면동코드 = B.법정동코드
  AND A.도로명코드 = B.도로명코드
  AND A.지하여부 = B.지하여부
  AND A.건물본번 = B.건물본번
  AND A.건물부번 = B.건물부번

/* 
신규 건 처리
- 신규분외에 누락된 자료가 있을 경우를 위해 UPDATE 후 INSERT하도록 구현
*/
UPDATE 기초구간위치정보 A
 SET (A.도로구간일련번호,A.시도명,A.시군구명,A.읍면동코드……) = 
(SELECT 도로구간일련번호,시도명,시군구명,읍면동코드……  FROM 기초구간위치정보변경분 B 
	WHEREB.시군구코드 = A.시군구코드 
	AND B.기초구간일련번호 = A.기초구간일련번호 
	AND B.기초번호본번 = A.기초번호본번
	AND B.기초번호부번 = A.기초번호부번
	AND B.이동사유코드 = 'U') 
WHERE EXISTS (SELECT 1 FROM 기초구간위치정보변경분 C WHERE C.시군구코드 = A.시군구코드 
AND C.기초구간일련번호 = A.기초구간일련번호
AND C.기초번호본번 = A.기초번호본번
AND C.기초번호부번 = A.기초번호부번
AND C.이동사유코드 = 'U')

INSERT INTO 기초구간위치정보 (도로구간일련번호,시도명,시군구명,읍면동코드……)
SELECT 도로구간일련번호,시도명,시군구명,읍면동코드…… FROM 기초구간위치정보변경분 A
WHERE A.이동사유코드 = 'C' 
AND NOT EXISTS (SELECT 1 FROM 기초구간위치정보 B WHERE B.시군구코드 = A.시군구코드
				AND B.기초구간일련번호 = A.기초구간일련번호
				AND B.기초번호본번 = A.기초번호본번
				AND B.기초번호부번 = A.기초번호부번)


/* ORACLE 9i이상 버전에서 MERGE문 사용 예시 */
MERGE INTO 기초구간위치정보 A
USING (SELECT 기초번호본번,기초번호부번,도로구간일련번호……  FROM 기초구간위치정보변경분 WHERE 이동사유코드 IN ('C','U')) B 
ON (A.시군구코드 = B.시군구코드 AND A.기초구간일련번호 = B.기초구간일련번호 AND A.기초번호본번 = B.기초번호본번 AND A.기초번호부번 = B.기초번호부번)
WHEN MATCHED THEN
  UPDATE SET A.도로구간일련번호 = B.도로구간일련번호, A.시도명 = B.시도명, A.시군구명 = B.시군구명,........
WHEN NOT MATCHED THEN
  INSERT (A.도로구간일련번호,A.시도명,A.시군구명,A.읍면동코드,……) 
  VALUES (B.도로구간일련번호,B.시도명,B.시군구명,B.읍면동코드,……) 
  
-- 폐지처리
DELETE FROM 기초구간위치정보 A
WHERE EXISTS (SELECT 1 FROM 기초구간위치정보변경분 B WHERE B.시군구코드 = A.시군구코드
	AND B.기초구간일련번호 = A.기초구간일련번호
	AND B.기초번호본번 = A.기초번호본번
	AND B.기초번호부번 = A.기초번호부번
	AND B.이동사유코드 = 'D')