[Mybatis] 한번에 여러 쿼리 작성하기 (Oracle PL/SQL BEGIN - END 구문)

서버 개발시 비지니스 로직을 개발시에 DB 구문을 여러개 동시에 쓰면 좋겠다는 생각이 들때가 있다. 이런 경우 PL/SQL 문법 중  BEGIN - END 로 여러 실행 구문을 정의해 주면 한 트랜잭션에서 여러개의 구문을 수행하게 할 수 있다.

 

여기서 잠시 PL/SQL에 대해서 알아보기로 한다.

 

PL/SQL이란

 

주로 자료 내부에서 SQL 명령문만으로 처리하기에는 복잡한 자료의 저장이나 프로시저와 트리거 등을 작성하는 데 쓰인다. PL/SQL의 구조는 에이다 프로그래밍 언어를 본떠 만들어졌다고 알려졌다. 따라서 두 언어는 그 구조가 범용 언어인 파스칼의 구문과 비슷하다. 범용 언어인 C와 C++ 그리고 파스칼 및 포트란 등의 프로그래밍 언어와는 다른 점으로 범용 언어들이 컴퓨터 시스템에서 특정한 작업을 처리하기 위해 만들어진 언어라고 볼 때 PL/SQL은 단지 오라클의 관계형 데이터베이스 (RDBMS)에서만 사용된다는 점이다.

PL/SQL 외에도 각 관계형 데이터베이스마다 확장 언어들이 있다. 이러한 확장 언어의 대표적인 예로 마이크로소프트의 마이크로소프트 SQL 서버와 SybaseASE에는 트랜잭트 SQL(Transact SQ; TSQL)이 있고 PostgreSQL에는 PL/pgSQL 마지막으로 IBM DB2는 ISO SQL의 SQL/PSM 표준을 따르는 SQL Procedural를 포함한다

 

PL/SQL의 블록 구조

-- 전체가 하나의 블록이고 구성요소는 아래와 같다

 

DECLARE

-- 선언부(옵션, 생략가능)

-- 변수나 상수를 정의

 

BEGIN

-- 실행부 (필수, BEGEN-END)

-- 로직 수행 (일반 SQL문, 조건문, 반복문 등)

 

EXCEPTION

-- 예외 처리부(옵션, 생략 가능)

 

END;

 

EXAMPLE #1

    <select id="updateParticipantInfo" parameterType="java.util.HashMap" resultType="com.sdp.vo.hybrid.KioskAttendPrdVO">
        /* DELETE 후 여러 INSERT를 수행함 */
        BEGIN
            DELETE FROM adhrncprdct
             WHERE lctreid=CAST(#{lctreId} AS INTEGER)
            ;

        <if test="existParticipantData == true">
            INSERT INTO adhrncprdct(lctreid, adhrncdc, partcptde, thumb, thumbimgcours, adhrncimg, adhrncimgcours, ordr)
            VALUES
            <foreach collection="participantImgList" item="data" index="index" open="" separator="," close="">
                (CAST(#{lctreId} AS INTEGER), #{data.adhrncDc}, to_char(#{data.partcptDt}::Date, 'YYYYMMDD'), #{data.thumb}, #{data.thumbImgCours}, #{data.adhrncImg}, #{data.adhrncImgCours}, CAST(#{data.ordrNo} AS INTEGER))
            </foreach>
            ;

        </if>
        END;
    </select>

 

EXAMPLE #2

<update id="updateAllDeviceCategory" parameterType="java.util.HashMap">
        /* DELETE, UPDATE, DELETE ... 를 혼합하여 수행함. */
        BEGIN
           delete devicecate
            WHERE POSM_DEVICE_ID in (SELECT posm_device_id
                                       FROM catecode
                                      WHERE code IN (<foreach collection="Codes" item="Code" separator=",">#{Code}</foreach>))
              and (group_id != #{groupId} or group_id is null)
            ;
           update devicecate
              set allgroupid = (WITH CTE(group_id, parnts_group_id, id_path, depth) AS (
                                            SELECT
                                                A.GROUP_ID, A.PARNTS_GROUP_ID,
                                                TO_CHAR(A.GROUP_ID) ID_PATH,
                                                1 AS depth
                                            FROM
                                                GROUP_MNG A
                                            WHERE 1=1
                                            AND A.PARNTS_GROUP_ID IS NULL
                                            UNION ALL
                                            SELECT
                                                E.GROUP_ID, E.PARNTS_GROUP_ID,
                                                TO_CHAR(C.ID_PATH || '|' || E.GROUP_ID) AS ID_PATH,
                                                C.depth + 1 AS depth
                                            FROM CTE C
                                            JOIN GROUP_MNG E ON E.PARNTS_GROUP_ID = C.GROUP_ID
                                        )
                                        SELECT '|'||id_path||'|' AS id_path FROM CTE
                                        WHERE 1=1 AND GROUP_ID = #{groupId})
            WHERE group_id = #{groupId}
            ;
           delete CATEMAP
            WHERE code IN (<foreach collection="Codes" item="Code" separator=",">#{Code}</foreach>)
              and group_id != #{groupId}
            ;
           update CATEMAP
              set allgroupid = (WITH CTE(group_id, parnts_group_id, id_path, depth) AS (
                                            SELECT
                                                A.GROUP_ID, A.PARNTS_GROUP_ID,
                                                TO_CHAR(A.GROUP_ID) ID_PATH,
                                                1 AS depth
                                            FROM
                                                GROUP_MNG A
                                            WHERE 1=1
                                            AND A.PARNTS_GROUP_ID IS NULL
                                            UNION ALL
                                            SELECT
                                                E.GROUP_ID, E.PARNTS_GROUP_ID,
                                                TO_CHAR(C.ID_PATH || '|' || E.GROUP_ID) AS ID_PATH,
                                                C.depth + 1 AS depth
                                            FROM CTE C
                                            JOIN GROUP_MNG E ON E.PARNTS_GROUP_ID = C.GROUP_ID
                                        )
                                        SELECT '|'||id_path||'|' AS id_path FROM CTE
                                        WHERE 1=1 AND GROUP_ID = #{groupId})
            WHERE group_id = #{groupId}
            ;
        END;
    </update>

 

-- The End --