달력

3

« 2024/3 »

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
.. .. ..

[펌] : http://cafe.daum.net/dbmsclub/LLPP/21?docid=1Ex4ELLPP2120091217141531

 

일반적으로 텍스트 데이터의 적재프로그램을 만들다보면, 기존에 있는 자료에 대해서는 업데이트를 수행하고 신규자료인 경우는 등록을 해야하는 업무가 있습니다. 비단, 적재뿐아니라 통계분석 데이터의 생성인 경우에도 이 내용은 해당이 되는데 보통은 upsert라고 해서 delete and insert기법을 많이들 활용하십니다만, 오라클에서는 merge구문을 지원합니다. 이 문장은 oracle이 알아서 update할것인지 insert할것인지 데이터를 선별해서 처리를 해주게 되는데요. 뭐 많이 어려운 구문도 아니고 사용하기도 간편하니까 어떤 방식으로 사용할 수 있는지 확인해봅시다.

 

문장 표준) MERGE INTO TARGET_TABLE A /* 실제 데이터가 저장될 TARGET TABLE입니다. */

USING (SELECT COL_A,COL_B... /* TARGET에 저장할 데이터가 있는 원본 데이터가 있는 테이블 또는 QUERY입니다 */

FROM SOURCE_TABLE

WHERE 조건들....) B

ON ( A.COL_A = B.COL_A ) /* 원본과 타겟의 컬럼이 어떤 값으로 중복되는지 체크할 조건입니다. 이 값으로 */

/* 오라클은 업데이트해야할지 INSERT해야할지를 결정합니다. */

WHEN MATCHED THEN /* 만약 조건이 같은 경우 UPDATE할 내용을 기술합니다. */

UPDATE SET

A.COL_B = B.COL_B

,A.COL_C = B.COL_B

WHEN NOT MATCHED THEN /* INSERT해야할 내용을 기술합니다. */

INSERT (COL_A,COL_B,COL_C....)

VALUES ( B.COL_A,B.COL_B,....);

위의 형식으로 구문을 사용하시게 되면, 오라클은 데이터가 UPSERTED되었다고 결과가 나오게 되고, 몇개의 ROW가 UPDATE또는 INSERT되었는지 확인하실 수 있습니다.

 

예제)

MERGE INTO DATA1004_6 M
USING (SELECT TRADE_DATE AS YMD
,MIN(REAL_CLASSCODE||LPAD(REPLACE(UNIT_SIZE,' ',''),7,'0')||UNIT_CODE) PM_KEY
,REAL_CLASSCODE
,REPLACE(UNIT_SIZE,' ','') AS UNITSIZE /* RPAD쓰지 않아도 CHAR TYPE이라 자동 ' '추가됨 */
,UNIT_CODE AS UNITCODE
,GRADE_CODE AS GRADECODE
,AVG(JOSA_PRICE) AS P_MEAN
FROM SRC_DATA1004
WHERE TRADE_DATE >= in_st_dt
AND TRADE_DATE <= in_ed_dt
AND JOSA_GUBUN_CODE = '6' -- 도소매
AND JOSA_PRICE <> 0
GROUP BY TRADE_DATE,REAL_CLASSCODE,UNIT_SIZE,UNIT_CODE,GRADE_CODE) T
ON ( M.YMD = T.YMD
AND M.PM_KEY = T.PM_KEY
AND M.GRADECODE = T.GRADECODE)
WHEN MATCHED THEN
UPDATE SET
M.P_MEAN = T.P_MEAN
WHEN NOT MATCHED THEN
INSERT (YMD,PM_KEY,REAL_CLASSCODE,UNITSIZE,UNITCODE,GRADECODE,P_MEAN)
VALUES (T.YMD,T.PM_KEY,T.REAL_CLASSCODE,T.UNITSIZE,T.UNITCODE,T.GRADECODE,T.P_MEAN);

위 예제의 경우는 SRC_DATA1004 테이블의 데이터를 읽어서 해당하는 값이 DATA1004_6에 존재하는 경우는 UPDATE하고

존재하지 않는 경우는 INSERT하는 프로그램입니다. 일주일치를 수행하는데 약 1만건 정도인데, 2-4초정도 걸리는군요.

 

프로그램코딩시 유의할 오류유형)

1.ORA-30926 에러가 발생하는 경우

ORA-30926 원본 테이블의 고정 행 집합을 가져올 수 없습니다.

ORA-30926 unable to get a stable set of rows in the source tables

- MERGE 를 사용하는 경우

1) INTO 절에 사용되는 테이블에 Primary Key 를 사용하는 경우
즉 INSERT 구문에서 DUPLICATE가 발생하거나 UPDATE 에 MULTI
ROW가 UPDATE되는 경우

2) ON 구문에서 UPDATE되는 ROW가 1개 이상일 경우
즉 ON 구문에서 맞는 테이블 값이 하나 이상일 경우

2.ORA-00001 에러가 발생하는 경우

이 에러는 TARGET이 되는 테이블에는 중복건이 없어서 UPDATE를 할려고 했는데,

SOURCE테이블 자체에서 데이터가 중복건이 있어서 두번INSERT하려고 하다가 문제가 발생합니다.

보통 문자타입이나 사이즈를 잘못잡아서 INSERT할 대상자체를 중복인지 아닌지 모르는 경우에

발생하게 되므로, TRIM처리를 해서 비교하시든 파일자체에 중복이 있는지 확인을 하시든 하는 방법으로

에러를 처리하실 수 있습니다.

.
:
Posted by .07274.