[Spring Boot] myBatis Procedure / function 호출

Spring Boot / Oracle / Mybatis 차례

 

<mapper namespace="com.gaeyou.project.store.mapper.ProjectMapper">
    <!-- 프로시저 호출하기 -->
    <insert id="saveData" parameterType="com.gaeyou.project.entity.ProjectDTO" statementType="CALLABLE">
        { CALL /* saveData */
            SP_SAVE_DATA (
                 IN_OPERATION  => #{operation  , mode=IN  , jdbcType=VARCHAR}
                ,IN_DATA       => #{data       , mode=IN  , jdbcType=VARCHAR}
                ,RETURN_CODE   => #{returnCode , mode=OUT , jdbcType=VARCHAR}
                ,ERROR_MSG     => #{errorMsg   , mode=OUT , jdbcType=VARCHAR}
            )
        }
    </insert>
    
    <!-- 함수 호출하기 -->
    <select id="retrieveData" parameterType="com.gaeyou.project.entity.ProjectDTO" resultType="com.gaeyou.project.entity.ProjectDTO">
        <![CDATA[
            SELECT /* retrieveData */
                FN_RETRIEVE_DATA(
                     #{data, jdbcType="VARCHAR"}
                    ,#{name, jdbcType="VARCHAR"}
                    ,#{age,  jdbcType="NUMERIC"}
                ) AS RESULT_VALUE
             FROM DUAL
        ]]>
    </select>
</mapper>

CREATE OR REPLACE PROCEDURE SP_SAVE_DATA (
      IN_OPERATION IN VARCHAR2     --
    , IN_DATA      IN VARCHAR2     --
    , RETURN_CODE  IN OUT VARCHAR2 --
    , ERROR_MSG    IN OUT VARCHAR2 --
) IS

BEGIN
    RETURN_CODE := 0
    ERROR_MSG   := '';
    
    IF TRIM(IN_OPERATION) IS NULL THEN
        RETURN_CODE := -1;
        ERROR_MSG   := 'NO SELECTED INFO';
        RETURN
    END IF;
    
    IF IN_OPERATION = 'C' THEN
        INSERT INTO TABLE(
            DATA
        )
        SELECT 
            IN_DATA
          FROM DUAL;
          
    ELSEIF IN_OPERATION = 'U' THEN
        UPDATE TABLE SET
            DATA = IN_DATA;
            
    ELSEIF IN_OPERATION = 'D' THEN
        DELETE FROM TABLE;
    END IF;
    
EXCEPTION WHEN OTHER THEN
    RETURN_CODE := -1;
    ERROR_MSG   := 'EXCEPTION ERROR'
END;

CREATE OR REPLACE FUNCTION FN_RETRIEVE_DATA (
     IN_DATA IN VARCHAR2
    ,IN_NAME IN VARCHAR2
    ,IN_AGE  IN NUMBER
) RETURN NUMBER

PRAGMA AUTONOMOUS_TRANSACTION;
RETURN_VAL NUMBER;
BEGIN

    IF IN_DATA IS NULL OR IN_NAME IS NULL THEN
        RETURN -1;
    END IF;
    
    BEGIN
        SELECT 
            1 AS RETURN_VAL
         FROM DUAL
    END
    
    COMMIT
    RETURN RETURN_VAL

EXEPTION WHEN OTHERS THEN
    RETURN -1;
END;

Spring Boot / Oracle / Mybatis 차례