오라클 UPDATE myBatis 생성함수

---

/* 실행 */
SELECT FN_DEV_UPDATESQL('BAM_CATEGORYDETAIL') FROM DUAL;

/* 결과 */
    <update id="updateBasic" parameterType="mdata">
    <![CDATA[
    UPDATE BAM_CATEGORYDETAIL
       SET COMN_CD1 = #{comnCd1}                   /* 공통코드1 */
         , COMN_CD2 = #{comnCd2}                   /* 공통코드2 */
         , COMN_CD3 = #{comnCd3}                   /* 공통코드3 */
         , COMN_NM = #{comnNm}                     /* 공통명 */
         , CD_EXPL = #{cdExpl}                     /* 코드설명 */
         , CD_RNKN = #{cdRnkn}                     /* 코드순위 */
         , RMRK = #{rmrk}                          /* 비고 */
         , DEL_YN = #{delYn}                       /* 삭제여부 */
         , AMEN_ID = #{_loginId}                   /* 수정자ID */
         , UPDT_DT = SYSDATE                       /* 수정일시 */
     WHERE HSPT_KEY = #{hsptKey}
       AND CLSF_CD = #{clsfCd}
       AND COMN_CD = #{comnCd}
    ]]>
    </update>

함수생성

CREATE OR REPLACE FUNCTION FN_DEV_UPDATESQL (tableName VARCHAR2)
    RETURN VARCHAR2
IS
    returnSql VARCHAR2(4000);
    enterStr VARCHAR2(4000);
    textHead VARCHAR2(4000);
    colName VARCHAR2(4000);
    colText VARCHAR2(4000);
    colType VARCHAR2(4000);
    inptText VARCHAR2(4000);
    tableText VARCHAR2(4000);
    whereText VARCHAR2(4000);
    camelText VARCHAR2(4000);
BEGIN
    enterStr := CHR(13) || CHR(10);

    returnSql := '    <update id="updateBasic" parameterType="mdata">' || enterStr
              || '    <![CDATA[' || enterStr
              || '    UPDATE ' || tableName || enterStr;

    textHead := '       SET ';

    FOR readData IN
    (
      SELECT MCOLS.TABLE_NAME
           , CTABS.COMMENTS TABLE_TEXT
           , MCOLS.COLUMN_ID
           , DECODE(VKEYS.COLUMN_NAME, NULL, '', 'PK') AS PK_YN
           , MCOLS.COLUMN_NAME
           , CCOLS.COMMENTS COLUMN_TEXT
           , MCOLS.DATA_TYPE
           , MCOLS.DATA_LENGTH
           , DECODE(VKEYS.COLUMN_NAME, NULL, '0', '1') AS RTRV_CLMN
           , DECODE(MCOLS.TABLE_NAME, NULL, '0', '1') AS SLCT_CLMN
        FROM USER_TAB_COLUMNS MCOLS
           , USER_TAB_COMMENTS CTABS
           , USER_COL_COMMENTS CCOLS
           , (
               SELECT DISTINCT DKEYS.COLUMN_NAME
                    FROM USER_CONSTRAINTS MKEYS
                       , USER_CONS_COLUMNS DKEYS
                   WHERE MKEYS.TABLE_NAME = DKEYS.TABLE_NAME
                     AND MKEYS.CONSTRAINT_NAME = DKEYS.CONSTRAINT_NAME
                     AND MKEYS.CONSTRAINT_TYPE = 'P'
                     AND UPPER(MKEYS.TABLE_NAME) = UPPER(tableName)
           ) VKEYS
       WHERE MCOLS.TABLE_NAME = CTABS.TABLE_NAME (+)
         AND MCOLS.TABLE_NAME = CCOLS.TABLE_NAME (+)
         AND MCOLS.COLUMN_NAME = CCOLS.COLUMN_NAME (+)
         AND MCOLS.COLUMN_NAME = VKEYS.COLUMN_NAME (+)
         AND UPPER(MCOLS.TABLE_NAME) = UPPER(tableName)
       ORDER BY
             MCOLS.COLUMN_ID
    )
    LOOP
        IF readData.PK_YN = 'PK' THEN
            CONTINUE;
        END IF;
        
        tableText := readData.TABLE_TEXT;
        colText := '/* ' || readData.COLUMN_TEXT || ' */';

        colName := readData.COLUMN_NAME;
        
        

        camelText := '';

        FOR camelData IN
        (
            SELECT UPPER(SUBSTR(COLUMN_NAME, 1, 1)) || LOWER(SUBSTR(COLUMN_NAME, 2)) AS COLUMN_NAME
              FROM (
                    SELECT REGEXP_SUBSTR(readData.COLUMN_NAME, '[^_]+', 1, LEVEL) AS COLUMN_NAME
                      FROM DUAL
                    CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(readData.COLUMN_NAME, '[^_]+', '')) + 1
                   ) C
        )
        LOOP
            camelText := camelText || camelData.COLUMN_NAME;
        END LOOP;

        SELECT LOWER(SUBSTR(camelText, 1, 1)) || SUBSTR(camelText, 2)
          INTO camelText
          FROM DUAL;

        CASE readData.COLUMN_NAME
            WHEN 'AMEN_ID' THEN inptText := textHead || RPAD(colName || ' = #{_loginId}', 40) || colText;
            WHEN 'UPDT_DT' THEN inptText := textHead || RPAD(colName || ' = SYSDATE', 40) || colText;
            WHEN 'REGI_ID' THEN inptText := '';
            WHEN 'RGST_DT' THEN inptText := '';
            ELSE inptText := textHead || RPAD(colName || ' = #{' || camelText || '}', 40) || colText;
        END CASE;

        IF inptText IS NULL THEN
            CONTINUE;
        END IF;

        textHead := '         , ';
        returnSql := returnSql || inptText || enterStr;
    END LOOP;

    whereText := '     WHERE ';

    FOR readData IN
    (
      SELECT MCOLS.TABLE_NAME
           , CTABS.COMMENTS TABLE_TEXT
           , MCOLS.COLUMN_ID
           , DECODE(VKEYS.COLUMN_NAME, NULL, '', 'PK') AS PK_YN
           , MCOLS.COLUMN_NAME
           , CCOLS.COMMENTS COLUMN_TEXT
           , MCOLS.DATA_TYPE
           , MCOLS.DATA_LENGTH
           , DECODE(VKEYS.COLUMN_NAME, NULL, '0', '1') AS RTRV_CLMN
           , DECODE(MCOLS.TABLE_NAME, NULL, '0', '1') AS SLCT_CLMN
        FROM USER_TAB_COLUMNS MCOLS
           , USER_TAB_COMMENTS CTABS
           , USER_COL_COMMENTS CCOLS
           , (
               SELECT DISTINCT DKEYS.COLUMN_NAME
                    FROM USER_CONSTRAINTS MKEYS
                       , USER_CONS_COLUMNS DKEYS
                   WHERE MKEYS.TABLE_NAME = DKEYS.TABLE_NAME
                     AND MKEYS.CONSTRAINT_NAME = DKEYS.CONSTRAINT_NAME
                     AND MKEYS.CONSTRAINT_TYPE = 'P'
                     AND UPPER(MKEYS.TABLE_NAME) = UPPER(tableName)
           ) VKEYS
       WHERE MCOLS.TABLE_NAME = CTABS.TABLE_NAME (+)
         AND MCOLS.TABLE_NAME = CCOLS.TABLE_NAME (+)
         AND MCOLS.COLUMN_NAME = CCOLS.COLUMN_NAME (+)
         AND MCOLS.COLUMN_NAME = VKEYS.COLUMN_NAME (+)
         AND UPPER(MCOLS.TABLE_NAME) = UPPER(tableName)
       ORDER BY
             MCOLS.COLUMN_ID
    )
    LOOP
        IF readData.PK_YN IS NULL THEN
            CONTINUE;
        END IF;

        camelText := '';

        FOR camelData IN
        (
            SELECT UPPER(SUBSTR(COLUMN_NAME, 1, 1)) || LOWER(SUBSTR(COLUMN_NAME, 2)) AS COLUMN_NAME
              FROM (
                    SELECT REGEXP_SUBSTR(readData.COLUMN_NAME, '[^_]+', 1, LEVEL) AS COLUMN_NAME
                      FROM DUAL
                    CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(readData.COLUMN_NAME, '[^_]+', '')) + 1
                   ) C
        )
        LOOP
            camelText := camelText || camelData.COLUMN_NAME;
        END LOOP;

        SELECT LOWER(SUBSTR(camelText, 1, 1)) || SUBSTR(camelText, 2)
          INTO camelText
          FROM DUAL;

        inptText := whereText || readData.COLUMN_NAME || ' = #{' || camelText || '}';

        returnSql := returnSql || inptText || enterStr;
        whereText := '       AND ';
    END LOOP;

    returnSql := returnSql || '    ]]>' || enterStr;
    returnSql := returnSql || '    </update>' || enterStr;

    RETURN returnSql;
END;