Oracle MERGE INTO를 활용해 값이 있는지 확인후 insert , update , delete ,selete 하는 방법 


이전에 포스팅 했던 MSSQL의 EXISTS 기능을 오라클에서 사용하는 방법이다


2016/08/12 - [Yame Programmer/SQL] - [MSSQL] 값이 존재하는지 확인 후 update insert [EXISTS] 활용



MSSQL을 사용했던 프로젝트를 Oracle로 마이그레이션 하게 되면서 다시 사용하게 되었다.


입사 초기에 Oracle를 사용하다 이후 쭉 MSSQL만 써서 기억이 가물가물 했다ㅋㅋ



1. 기본 포맷


1
2
3
4
5
6
MERGE INTO 테이블명  USING ( 서브쿼리 ) 
ON  ( 조건 )
WHEN MATCHED THEN 
조건에 값이 있는 경우 실행할 쿼리 
WHEN NOT MATCHED THEN 
조건에 값이 없는 경우 실행할 쿼리 
cs



2. 간단한 예제


1) 서브쿼리 없이 사용


1
2
3
4
5
6
7
8
9
10
11
12
13
MERGE INTO 
    TABLENAME  
USING DUAL 
    ON (PK_CD = 'PKCD001'  AND DATA_NAME = 'dataName')
    WHEN MATCHED THEN     
        UPDATE SET     
                DATA_COL1 = '업데이트 내용 1',             
                DATA_COL2 = '업데이트 내용 2',             
                DATA_COL3 = '업데이트 내용 3',         
        
    WHEN NOT MATCHED THEN
        INSERT (PK_CD, DATA_NAME,  DATA_COL1, DATA_COL2, DATA_COL3)
        VALUES('PKCD001', 'dataName', '넣을 내용1', '넣을 내용3', '넣을 내용3')
cs




2) 서브쿼리 사용

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
MERGE INTO 
    TABLE_NAME  TARGETTB
USING(
    SELECT 
        CHECKTB.PK_CD
        ,CHECKTB.DATA_NAME
    FROM
        TABLE_NAME CHECKTB
     LEFT JOIN
        JOIN_TB JT
    ON
        JT.DATA = CHECKTB.DATA 
        ) CHECK
 
    ON (TARGETTB.PK_CD = CHECK.PK_CD    AND   TARGETTB.DATA_NAME = CHECK.DATA_NAME)
    WHEN MATCHED THEN     
        UPDATE SET     
                DATA_COL1 = '업데이트 내용 1',             
                DATA_COL2 = '업데이트 내용 2',             
                DATA_COL3 = '업데이트 내용 3',         
        
    WHEN NOT MATCHED THEN
        INSERT (PK_CD, DATA_NAME,  DATA_COL1, DATA_COL2, DATA_COL3)
        VALUES('PKCD001', 'dataName', '넣을 내용1', '넣을 내용3', '넣을 내용3')
cs

ㅇ

꼭 INSERT UPDATE만 넣을 수 있는게 아니라 SELECT DELETE를 넣어서 사용 할 수도 있다.




저작자표시 비영리

'Yame Programmer > SQL' 카테고리의 다른 글

[MyBatis] Oracle 부적합한 열유형 null 처리 방법 jdbcType=VARCHAR 안쓰기  (2) 2017.01.11
[MSSQL] MSSQL 날짜 포맷 변환표  (0) 2017.01.09
[sql 튜닝] 이론 임시 내용[하드 파싱 소프트 파싱 캐시 블락]  (0) 2016.10.27
[sql튜닝] cmd 명령문 실행 계획  (0) 2016.10.27
[Oracle] 값이 존재하는지 확인 후 insert, update 하기  (0) 2016.10.05

Mssql 에서는 IF EXISTS 함수를 사용해 값이 존재하는지 확인 후


값이 있을때와 없을때 다른 쿼리를 사용 할 수 있다.


oracle에서는 MERGE 함수를 사용해 유사한 기능을 구현 할 수 있다.



1
2
3
4
5
6
7
8
9
10
11
12
13
 MERGE INTO 
     TBL_NAME -- table명
USING DUAL 
    ON (column1 = '존재하는지 확인할 값')
WHEN MATCHED THEN -- 존재하는 경우 실행   
    UPDATE SET  
        column2= 'update할 값1',
        column3= 'update할 값2'
WHEN NOT MATCHED THEN -- 존재하지 않는 경우 실행
    INSERT 
        (column1, column2, column3)
    VALUES
        ('존재하는지 확인할 값','insert할 값1', 'insert할 값2')
cs



위와 같은 쿼리문을 작성하면 된다


더 간단하게 나타내자면


1
2
3
4
5
6
7
8
 MERGE INTO 
     TBL_NAME -- table명
USING DUAL 
    ON (column1 = '존재하는지 확인할 값')
WHEN MATCHED THEN 
    -- 값이 존재하 경우 실행할 쿼리 
WHEN NOT MATCHED THEN 
    -- 값이 존재하지 않을 경우 실행할 쿼리
cs



이렇게 나타낼 수 도 있으며


좀더 응용하자면


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
32
33
merge into target_table tt
    using 
        ( -- 조인하는 테이블
            select
                te.te_cd tt_cd
                ,te.description tt_disp
                ,te.jt_no jt_no
                ,decode(te.is_flag, 'normal', 'Y', 'N') is_flag
            from
                temp_table te
            left join join_table jt
                on te.te_no=jt.jt_no
            where
                1=1
        ) so
    on (tt.tt_cd=so.tt_cd and tt.jt_no=so.jt_no) -- 조인한 테이블의 값 
     
    when matched then
        update set
            tt.is_flag=so.is_flag
            ,tt.tt_disp=so.tt_disp
 
 
    when not matched then
            insert (tt_no, tt_cd, tt_disp, jt_no, is_flag, reg_date)
            values(
                sq_target_table.nextval
                , so.tt_cd
                , so.tt_disp
                , so.jt_no
                , so.is_flag
                , sysdate
            );
cs

(코드 출처 :  http://yangtaeho.tistory.com/entry/%EC%98%A4%EB%9D%BC%ED%81%B4-MERGE-%EC%82%AC%EC%9A%A9%ED%95%98%EA%B8%B0-update-insert-%EB%8F%99%EC%8B%9C%EC%97%90  


http://zinlee.tistory.com/185 에서 더 자세한 내용을 확인할 수 있습니다.

 )



이렇게 사용 할 수도 있다. 단순히 한 테이블에 대해서만 값이 존재하는지 확인 하는 것이 아니라


조인했을때의 값도 활용 할 수가 있다.

저작자표시 비영리

'Yame Programmer > SQL' 카테고리의 다른 글

[sql 튜닝] 이론 임시 내용[하드 파싱 소프트 파싱 캐시 블락]  (0) 2016.10.27
[sql튜닝] cmd 명령문 실행 계획  (0) 2016.10.27
[Oracle] java.sql.SQLException: ORA-00911: 문자가 부적합합니다.  (1) 2016.10.05
[MSSQL] mssql에서 dual과 같은 가상 테이블 사용 하기  (0) 2016.08.12
[MSSQL] 값이 존재하는지 확인 후 update insert [EXISTS] 활용  (0) 2016.08.12

테이블의 값을 변경하기 전에 해당 인덱스의 값이 있는지 확인 하고


insert하거나 update 하는 방법입니다.


ms sql의 exists를 활용하여 아래와 같은 쿼리문을 작성 할 수 있습니다.


1
2
3
4
5
6
7
8
9
10
11
IF EXISTS(
               해당 데이터가 존재 하는지 셀렉트
         )
    BEGIN
        --데이터가 있는 경우 실행할 쿼리
    END
    
    ELSE
        BEGIN
        --데이터가 없는 경우 실행할 쿼리
       END
cs

EXISTS 안에 확인할 데이터를 셀렉트 하는 쿼리를 넣고


데이터가 있는경우와 없는경우 실행할 쿼리를 작성 하면 됩니다.




1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
IF EXISTS(
                SELECT CODE_ID FROM tbl
                WHERE CODE_ID = 'code_001'
         )
    BEGIN
        SELECT 99 cnt
    END
    
    ELSE
        BEGIN
        insert into tbl
            (
            DEVICE_ID
              ,CODE_ID
              ,CODE_NAME
            )
        values
            (
              ,'device_001'
              ,'code_001'
              ,''
            )
       END
cs



위의 코드는 code_id라는 컬럼에 code_001이라는 데이터가 있는지 확인하고


있다면 99라는 숫자를 반환하고


없다면 데이터를 입력하게 하는 쿼리 입니다.



insert가 아닌 update도 사용 가능하며 일반 select문도 가능합니다.



값을 입력할때 있는지 확인하기 위해서 컨트롤러에서 DOA까지 두번이나 왕복할 필요가 없겠죠


제 포스팅이 도움이 되었나요? 

그렇다면 공감하기 한번 눌러주세요 블로거에게 큰 힘이 됩니다


저작자표시 비영리

'Yame Programmer > SQL' 카테고리의 다른 글

[Oracle] java.sql.SQLException: ORA-00911: 문자가 부적합합니다.  (1) 2016.10.05
[MSSQL] mssql에서 dual과 같은 가상 테이블 사용 하기  (0) 2016.08.12
[Mybatis] 비교연산자 부등호 >,< 안될때  (11) 2016.07.19
[mysql,MariaDB] 150 mysql foreign key constraint is incorrectly formed  (0) 2016.07.18
[Mysql] 구분자를 이용해 값을 로우로 출력하기  (0) 2016.07.14

+ Recent posts

티스토리툴바