데이터 조작어(DML) - merge (병합)

   -- 어떤 2개 이상의 테이블에 존재하는 데이터를 다른 테이블 한 곳으로 모으는 것(병합)이다.
   -- merge = insert + update

 

   ----- ***** 데이터베이스 링크(Database Link) 만들기 ***** -----

 

   0. DB 서버가 될 컴퓨터에서는 제어판-방화벽-TCP포트 1521 을 허용 으로 만들어야 한다.

 

   1. DB 클라이언트 컴퓨터의 탐색기에서 C:\OracleXE18C\product\18.0.0\dbhomeXE\network\admin 에 간다.
      
   2. tnsnames.ora 파일을 메모장으로 연다.
      
   3.

       TEACHER =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 211.238.142.40)(PORT = 1521))
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = XE)
          )
        )

   을 추가한다.
   HOST = 211.238.142.40 이 연결하고자 하는 원격지 오라클서버의 IP 주소이다.
   그런데 전제조건은 원격지 오라클서버(211.238.142.40)의 방화벽에서 포트번호 1521 을 허용 으로 해야 한다는 것이다.
        
   TEACHER 를 'Net Service Name (네트서비스명)' 이라고 부른다.
        
  4. 명령프롬프트를 열어서 원격지 오라클서버(211.238.142.40)에 연결이 가능한지 테스트를 한다.
  C:\Users\sist>tnsping TEACHER 5
        
 별칭 분석을 위해 TNSNAMES 어댑터 사용
 (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 211.238.142.40)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))에 접속하려고 시도하는 중
        확인(50밀리초)
        확인(60밀리초)
        확인(20밀리초)
        확인(30밀리초)
        확인(40밀리초)
        
  5. 데이터베이스 링크(Database Link) 만들기 */

 

---

 

        create database link teacherServer
        connect to hr identified by aclass
        using 'TEACHER';

-- Database link TEACHERSERVER이(가) 생성되었습니다.

       -- 이때 hr 과 암호 aclass 는 연결하고자 하는 원격지 오라클서버(211.238.142.40)의 계정명과 암호이다.
       -- using 다음에 나오는 'TEACHER' 은 tnsnames.ora 파일에서 추가해준 'Net Service Name (네트서비스명)'이다.

 

 

        select *
        from employees
        order by employee_id asc;
        
        update employees set first_name = '혜원', last_name = '김'
        where employee_id = 100; 
        -- 1 행 이(가) 업데이트되었습니다.
        
        commit;

        select *
        from employees                -- 로컬 서버
        order by employee_id asc;
        
        
        select *
        from employees@teacherServer  -- 원격지 오라클 서버(211.238.142.40)
        order by employee_id asc;

 

 

---

        ----- ***** 생성된 데이터베이스 링크를 조회하기 ***** -----

 

        select *
        from user_db_links;
        
      /*
       ----------------------------------------------------------------
        DB_LINK         USERNAME    PASSWORD     HOST       CREATED
       ---------------------------------------------------------------- 
        TEACHERSERVER	HR		    (NULL)       TEACHER	22/07/11	NO	NO	YES
                                              -- TEACHER 는 tnsnames.ora 파일에 존재하는 'Net Service Name (네트서비스명)'이다.
                                              -- (경로) C:\OracleXE18C\product\18.0.0\dbhomeXE\network\admin
   */

 

 

------ ***** 데이터베이스 링크 삭제하기 ***** ------

 drop database link teacherServer;

 

 

 

---

   -- 각 지점 DB서버에서 tbl_reservation_kimhyewon 이라는 테이블을 생성한다.

   create table tbl_reservation_kimhyewon
    (rsvno       varchar2(20)    -- 예약고유번호
    ,memberid    varchar2(20)    -- 회원ID
    ,ticketcnt   number          -- 티켓개수
    ,constraint PK_tbl_reservation_kimhyewon primary key(rsvno)
    );
   -- Table TBL_RESERVATION_KIMHYEWON이(가) 생성되었습니다.

   insert into tbl_reservation_kimhyewon(rsvno, memberid, ticketcnt)
   values('kimhyewon001', '김혜원', 3);
    
    commit;
   
   select *
   from tbl_reservation_kimhyewon;  -- 각 지점 DB서버에서 하는 것

 

   -- 아래는 본점DB서버(샘PC)에서만 하는 것이다.

    create table tbl_reservation_merge
    (rsvno       varchar2(20)    -- 예약고유번호
    ,memberid    varchar2(20)    -- 회원ID
    ,ticketcnt   number          -- 티켓개수
    ,constraint PK_tbl_reservation_merge primary key(rsvno)
    );
   
    select *
    from tbl_reservation_merge; -- 본점DB서버(샘)에서 하는 것 입니다.

 

   select *
   from tbl_reservation_merge@teacherServer;  -- 각 지점 DB서버 에서 하는 것 입니다.
   
   select *
   from tbl_reservation_kimhyewon;  -- 각 지점 DB서버 에서 하는 것 입니다.

  -- 아래는 각 지점 DB서버(샘)에서 하는 것 입니다.

   merge into tbl_reservation_merge@teacherServer R  -- 원격지(리모트)에 있는거랑 
   using tbl_reservation_kimhyewon L -- 로컬에 있는거랑 비교하고
   on (L.rsvno = R.rsvno)
   when matched then  -- 있으면 
        update set R.memberid = L.memberid
                 , R.ticketcnt = L.ticketcnt
   when not matched then  -- 없으면
        insert( rsvno, memberid, ticketcnt ) values( L.rsvno, L.memberid, L.ticketcnt );
   -- 1 행 이(가) 병합되었습니다.
   
   commit;

 

   update tbl_reservation_kimhyewon set memberid = 'Kim H.W', ticketcnt = 1
   where rsvno = 'kimhyewon001';
   
   commit;
   
   insert into tbl_reservation_kimhyewon(rsvno, memberid, ticketcnt)
   values('kimhyewon', '김혜원', 11);
   
    commit;

   select *
   from tbl_reservation_kimhyewon;  -- 각 지점 DB서버 에서 하는 것 입니다.
   
   select *
   from tbl_reservation_merge@teacherServer;  -- 각 지점 DB서버 에서 하는 것 입니다.

 

   merge into tbl_reservation_merge@teacherServer R  -- 원격지(리모트)에 있는거랑 
   using tbl_reservation_kimhyewon L -- 로컬에 있는거랑 비교하고
   on (L.rsvno = R.rsvno)
   when matched then  -- 있으면 
        update set R.memberid = L.memberid
                 , R.ticketcnt = L.ticketcnt
   when not matched then  -- 없으면
        insert( rsvno, memberid, ticketcnt ) values( L.rsvno, L.memberid, L.ticketcnt );
   -- 2개 행 이(가) 병합되었습니다.
   
   commit;  -- 각 지점 DB서버 에서 하는 것 입니다.

 

   select *
   from tbl_reservation_kimhyewon;  -- 각 지점 DB서버 에서 하는 것 입니다.
   
   select *
   from tbl_reservation_merge@teacherServer;  -- 각 지점 DB서버 에서 하는 것 입니다.