SPRING 쿼리를 2개를 1개로 줄이는 Merge 이용하기

Merge방법을 사용하면 코드를 줄이고 가독성을 높일 수 있다. 아직 생소하지만 계속 노력을 해야되겠다.

 

1.일단 쿼리에 대해서 작성한다.

sql
--1
   SELECT   PROD_NAME prodName
           ,   SUM(PROD_SALE * CART_QTY) money
      FROM     PROD, CART 
      WHERE    PROD_ID = CART_PROD
      GROUP BY PROD_NAME
      HAVING   SUM(PROD_SALE * CART_QTY) >= 10000000;
      

--merge : LPROD테이블을 LPROD2테이블로 복제해보자
   --2
    CREATE TABLE LPROD2
    AS 
    SELECT * FROM LPROD;

--MERGE
    --3
    MERGE INTO LPROD2 A --대상 테이블
    USING DUAL 
    ON (A.LPROD_GU='P403') --조건절(주로 기본키 데이터)
    WHEN MATCHED THEN --조건절에 해당하는 데이터가 있으면 실행
        UPDATE SET A.LPROD_CNT=A.LPROD_CNT+1 
    WHEN NOT MATCHED THEN --조건절에 해당하는 데이터가 없으면 실행
    INSERT(LPROD_ID , LPROD_GU,LPROD_NM,LPROD_CNT)
    VALUES(FN_NEXT_LPROD_ID(),'P404','기구류',0);
    
    --LPROD2 테이블의 LPROD_ID값을 1 증가시켜 리턴해주는 FUNCTION을 만들어보자
    --FUNCTION명 : FN_NEXT_LPROD_ID
    --4.
    SELECT NVL(MAX(LPROD_ID),0)+1 FROM LPROD2;
    ----------------------------------------------------------
    CREATE OR REPLACE FUNCTION FN_NEXT_LPROD_ID
    RETURN NUMBER
    IS V_ID NUMBER;
    BEGIN
        --PL/SQL에서 SELECT와 함께 꼭 INTO를 쓰자
        SELECT NVL(MAX(LPROD_ID),0)+1 INTO V_ID 
        FROM LPROD2;
        RETURN V_ID;
    END;
    
    --LPROD2 테이블의 LPROD_GU값을 1증가시켜 리턴해주는 서브쿼리를 만들어보자
    --P403 -> 1증가 -> P405
    SELECT MAX(LPROD_GU) 
        ,SUBSTR(MAX(LPROD_GU),1,1)
        ||TRIM(SUBSTR(MAX(LPROD_GU),2)+1)
    FROM LPROD2;
    --P와 405을 분리시키고 문제를 해결
-- 스브쿼리 만들기


--5.UPDATE를 하거나 INSERT를 하는거임 조건에 맞는거
MERGE INTO LPROD2 A  -- 대상 테이블
USING DUAL
ON(A.LPROD_GU = 'P404') -- 조건절(주로 기본키 데이터)
WHEN MATCHED THEN -- 조건절에 해당하는 데이터가 있으면 실행
    UPDATE SET A.LPROD_CNT = A.LPROD_CNT + 1
WHEN NOT MATCHED THEN -- 조건절에 해당하는 데이터가 없으면 실행
    INSERT (LPROD_ID, LPROD_GU, LPROD_NM, LPROD_CNT)
    VALUES ((SELECT NVL(MAX(LPROD_ID), 0) + 1 FROM LPROD2), (SELECT SUBSTR(MAX(LPROD_GU), 1, 1)
            || TRIM(SUBSTR(MAX(LPROD_GU), 2) + 1)
            FROM   LPROD2), '가구류', 0)
;

 

어떤 코드를 줄일 수 있는지 알아보자

insert와 update이 두개의 코드를 줄일 수 있다.

<!--
<insert id="insertPost" parameterType="bookVO">
	<selectKey order="BEFORE" keyProperty="bookId" resultType="int">
		SELECT NVL(MAX(BOOK_ID),0) + 1 FROM BOOK
	</selectKey>

	INSERT INTO BOOK (BOOK_ID,TITLE,CATEGORY,PRICE,INSERT_DATE,CONTENT)
	VALUES (#{bookId},#{title},#{category},#{price},SYSDATE,#{content})
</insert>
 -->

<!-- MERGE를 이용한 조건 INSERT문
	UPDATE : BOOKVO.BOOKID가 있음 => controller에서 detail.jsp?bookId=1로 redirect 시 parameter값을 이용해서
	INSERT : BOOKVO.BOOKID가 없음
	그래서 서브쿼리로 작성 INSERT BOOKID를 SELECTKEY 다 넣기
-->

 

Merge를 이용하면 코드를 간결하게 작성이 가능하다.

<insert id="insertPost" parameterType="bookVO"  >
		<selectKey order="AFTER" keyProperty="bookId" resultType="int">
			SELECT MAX(BOOK_ID)  FROM BOOK
		</selectKey>
		MERGE INTO BOOK A
		USING DUAL ON(A.BOOK_ID=#{bookId})
		WHEN MATCHED THEN
		UPDATE BOOK
		   SET TITLE = #{title}
		    ,  CATEGORY = #{category}
		    ,  PRICE = #{price}
		    ,  CONTENT = #{content}
		    ,  INSERT_DATE = SYSDATE
		WHEN NOT MATCHED THEN
		INSERT (BOOK_ID,TITLE,CATEGORY,PRICE,INSERT_DATE,CONTENT) 
		VALUES ((SELECT NVL(MAX(BOOK_ID),0) + 1 FROM BOOK),#{title},#{category},#{price},SYSDATE,#{content})
	</insert>
	<!-- INSERT INTO BOOK 빼고 WHERE BOOK_ID = #{bookId} 빼기 -->