오라클 SELECT myBatis 생성함수

---

    <select id="retrieveBasic" parameterType="mdata" resultType="mdata">
    <![CDATA[
    SELECT '0' AS CHK
         , M.HSPT_KEY                              /* 병원_KEY */
         , M.CLSF_CD                               /* 분류코드 */
         , M.COMN_CD                               /* 공통코드 */
         , M.COMN_CD1                              /* 공통코드1 */
         , M.COMN_CD2                              /* 공통코드2 */
         , M.COMN_CD3                              /* 공통코드3 */
         , M.COMN_NM                               /* 공통명 */
         , M.CD_EXPL                               /* 코드설명 */
         , M.CD_RNKN                               /* 코드순위 */
         , M.RMRK                                  /* 비고 */
         , NVL(M.DEL_YN, 'N') AS DEL_YN            /* 삭제여부 */
         , M.REGI_ID                               /* 등록자ID */
         , M.RGST_DT                               /* 등록일시 */
         , M.AMEN_ID                               /* 수정자ID */
         , M.UPDT_DT                               /* 수정일시 */
      FROM BAM_CATEGORYDETAIL M /* 공통코드 */
     WHERE M.HSPT_KEY = #{hsptKey}
       AND M.CLSF_CD = #{clsfCd}
       AND M.COMN_CD = #{comnCd}
    ]]>
    </select>

위와 같은 myBatis SELECT문을 만들어주는 함수이다.

 

SELECT FN_DEV_SELECTSQL('BAM_CATEGORYDETAIL') FROM DUAL;

이렇게 사용할 수 있다.

 

CREATE OR REPLACE FUNCTION FN_DEV_SELECTSQL (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);
    textHead := '         , ';
    
    returnSql := '    <select id="retrieveBasic" parameterType="mdata" resultType="mdata">' || enterStr
              || '    <![CDATA[' || enterStr
              || '    SELECT ''0'' AS CHK' || enterStr;
    

    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
        tableText := readData.TABLE_TEXT;
        colText := '/* ' || readData.COLUMN_TEXT || ' */';
        
        colName := readData.COLUMN_NAME;
        IF SUBSTR(colName, -3) = '_YN' THEN
            inptText := textHead || RPAD('NVL(M.' || colName || ', ''N'') AS ' || colName, 40) || colText;
        ELSE
            inptText := textHead || RPAD('M.' || colName, 40, ' ') || colText;
        END IF;
        
        returnSql := returnSql || inptText || enterStr;
    END LOOP;
    
    returnSql := returnSql || '      FROM ' || tableName || ' M /* ' || tableText || ' */' || enterStr;
    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 || 'M.' || readData.COLUMN_NAME || ' = #{' || camelText || '}';
        returnSql := returnSql || inptText || enterStr;
        whereText := '       AND ';
    END LOOP;
    
    returnSql := returnSql || '    ]]>' || enterStr;
    returnSql := returnSql || '    </select>' || enterStr;

    RETURN returnSql;
END;