최근 포토로그


오라클 MERGE INTO 구문에서 카운터 세기 MSSQL과 ORACLE

오라클에 Merge 가 있다.


아실랑가 모르겠지만, 오라클에서 복잡하게 대량으로 업데이트, 삭제 할려고 하면
서브쿼리 쓰던가 
조인뷰를 쓰던가
merge구문을 쓰면 된다.

근데 오라클은 Ms-sql과 달리, 쓰는 방식에 제약이 좀 따른다.
조인뷰의 /*+ BYPASS_UJVC */  힌트는 11g에서 안먹힘.

그래서 1:1의 수정이 아닌 경우에는 좌절됨.
서브쿼리는 속도가 느리다. 씨봉탱들....

그래서 merge를 쓰면 쾌적한 업데이트와 삭제를 하는데
문제는 이게 수행 결과값을 리턴하지 않는다는 것이다.
즉, 몇개 넣었다라는 메세지를 안보내준다.


위를 보다시피 안보인다.
뭐가 얼만큼 실행되었는지 보이지 않는다는 말이다.



한마디로 장님이 된다는 소리.

씨봉 100만건 업데이트 했는지, 10건 업데이트 했는지 모른다면 어찌 그걸 사용할 수 있으랴?

그래서 존나 인터넷으로 달렸다.

그래서 찾았다.
오호라 구글신이시여

먼저 package를 만들어야 해.
안 만들거면 뒤로가기 누르세요.
여기서 삽질하지 마시고.



CREATE OR REPLACE PACKAGE etl AS

   c_inserting CONSTANT PLS_INTEGER := 0;
   c_updating  CONSTANT PLS_INTEGER := 1;

   FUNCTION merge_counter (
            action_in IN PLS_INTEGER DEFAULT c_inserting
            ) RETURN PLS_INTEGER;

   FUNCTION get_merge_update_count RETURN PLS_INTEGER;

   FUNCTION get_merge_update_count (
            merge_count_in IN PLS_INTEGER
            ) RETURN PLS_INTEGER;

   FUNCTION get_merge_insert_count RETURN PLS_INTEGER;

   FUNCTION get_merge_insert_count (
            merge_count_in in PLS_INTEGER
            ) RETURN PLS_INTEGER;

   PROCEDURE reset_counters;

END etl;


CREATE OR REPLACE PACKAGE BODY etl AS

   g_update_counter PLS_INTEGER NOT NULL := 0;
   g_insert_counter PLS_INTEGER NOT NULL := 0;

   /*----------- FUNCTION merge_counter -----------*/
   FUNCTION merge_counter (
            action_in IN PLS_INTEGER DEFAULT c_inserting
            ) RETURN PLS_INTEGER IS
   BEGIN
      CASE action_in
         WHEN c_updating
            THEN g_update_counter := g_update_counter + 1;
         WHEN c_inserting
            THEN g_insert_counter := g_insert_counter + 1;
         ELSE
            RAISE PROGRAM_ERROR;
      END CASE;
      RETURN 0;
   END merge_counter;

   /*----------- FUNCTION get_merge_update_count V1 -----------*/
   FUNCTION get_merge_update_count
      RETURN PLS_INTEGER is
   BEGIN
      RETURN g_update_counter;
   END get_merge_update_count;

   /*----------- FUNCTION get_merge_update_count V2 -----------*/
   FUNCTION get_merge_update_count (
            merge_count_in IN PLS_INTEGER
            ) RETURN PLS_INTEGER IS
   BEGIN
      RETURN NVL( merge_count_in - g_insert_counter, 0 );
   END get_merge_update_count;

   /*----------- FUNCTION get_merge_insert_count V1 -----------*/
   FUNCTION get_merge_insert_count
      RETURN PLS_INTEGER IS
   BEGIN
      RETURN g_insert_counter;
   END get_merge_insert_count;

   /*----------- FUNCTION get_merge_insert_count V2 -----------*/
   FUNCTION get_merge_insert_count (
            merge_count_in IN PLS_INTEGER
            ) RETURN PLS_INTEGER IS
   BEGIN
      RETURN NVL( merge_count_in - g_update_counter, 0 );
   END get_merge_insert_count;

   /*----------- FUNCTION reset_counters -----------*/
   PROCEDURE reset_counters IS
   BEGIN
      g_update_counter := 0;
      g_insert_counter := 0;
   END reset_counters;

END etl;


여기까지만 쓰면 만사 오케이

SET SERVEROUTPUT ON;
BEGIN
    MERGE INTO  사원테이블1 X
    USING(
           SELECT a.emp_id, b.deptcd_acct  rep_comp_cd2 
               FROM 사원테이블1 a
                  , 사원테이블2 b
              WHERE a.emp_id= b.emp_id
                and b.status = '1'
          ) B
    ON (X.emp_id = B.emp_id)
    WHEN MATCHED THEN
          UPDATE 
          SET  X.in_user = '업데이트1'
            ,  X.in_time = SYSDATE
          ;
          
    DBMS_OUTPUT.PUT_LINE( TO_CHAR(SQL%ROWCOUNT) || ' 줄 반영되었어..');
    DBMS_OUTPUT.PUT_LINE( TO_CHAR(etl.get_merge_insert_count) || ' 줄 추가되었어.');
    DBMS_OUTPUT.PUT_LINE( TO_CHAR(etl.get_merge_update_count( SQL%ROWCOUNT ))|| ' 줄 수정되셨어.');

END;



보이지? 잘 나와





덧글

  • 블럭 2015/06/04 14:04 # 삭제 답글

    잘 보고 갑니다.. 씨봉탱...
  • 사기뀬 2016/10/04 17:06 # 삭제 답글

    이패키지는 transaction 단위로 call 해야 하는건데 찍을때만 DBMS_OUTPUT.PUT_LINE찍으면
    SQL%ROWCOUNT 가 update건수로 무조건 나온다네...무식한 양반아...
    타겟 테이블 전부 지우고 돌려보시게 update 건수로 나올걸세...
  • 나기드 2017/02/06 12:39 #

    무슨 소리인지 모르겠네요.

    제가 계속 쓰고 있고, 지금도 쓰고있고
    insert, update 건수가 정확히 나옵니다.

    무얼 해보시고 답글을 달아주세요.
댓글 입력 영역