오라클 SELECT myBatis 생성함수 ver2

---

주석제거 기능 추가

/* 사용예제 */
SELECT FN_DEV_SELECTSQL('BCI_CLAMMAIN' /* TABLE명 */, 'N') FROM DUAL;

---

실행결과

<select id="retrieveBasic" parameterType="mdata" resultType="mdata">
<![CDATA[
SELECT '0' AS CHK
     , M.HSPT_KEY                              
     , M.CLAM_SQNO                             
     , M.CLAM_NO                               
     , M.CLAM_NM                               
     , M.AGRG_TYPE_CD                          
     , M.MED_DVSN                              
     , M.HSPT_BYKN_DVSN                        
     , M.CLAM_INS_TYPE                         
     , M.FXAM_DVSN_CD                          
     , M.VTRN_DVSN_CD                          
     , M.CLFR_MDFR_VRSN                        
     , M.SPCT_MDFR_VRSN                        
     , M.CLFR_MDFR_NO                          
     , M.HSPT_RGNO                             
     , M.RECV_INST                             
     , M.INSR_BYKN_DVSN                        
     , M.CLAM_DVSN                             
     , M.CLAM_UNIT_DVSN                        
     , M.CLAM_MED_DVSN                         
     , M.MED_FIEL_DVSN                         
     , M.MED_SHPE                              
     , M.CLAM_YM                               
     , M.AGRG_STR_DY                           
     , M.AGRG_END_DY                           
     , M.CLAM_NOCS                             
     , M.EXPN_TAMT1                            
     , M.UCAMT                                 
     , M.UOAMT                                 
     , M.CLAMT                                 
     , M.FDAMT                                 
     , M.DBPS_MDCS                             
     , M.EXPN_TAMT2                            
     , M.VTRN_CLAMT                            
     , M.NOIN_UTAMT                            
     , M.VTRN_UCAMT                            
     , M.SMCS_TAMT                             
     , M.SMCS_UCAMT                            
     , M.SMCS_CLAMT                            
     , M.SMCS_VTRN_CLAMT                       
     , M.DFEE_MED_NODY                         
     , M.DFEE_DR_CNT                           
     , M.DFRN_INDX                             
     , M.DFEE_CLAMT                            
     , M.CRTN_DY                               
     , M.CLAM_DY                               
     , M.CLAM_PRSN_NM                          
     , M.WRTR_NM                               
     , M.WRTR_DOBR                             
     , M.CLAM_PRMT_NO                          
     , M.VCEX_CLAM_PRTY                        
     , M.RFRN                                  
     , M.TRAI_CO                               
     , M.CONV_DT                               
     , M.SEND_DT                               
     , M.RECV_DT                               
     , M.CLAM_RCPN_NO                          
     , M.REGI_ID                               
     , M.RGST_DT                               
     , M.AMEN_ID                               
     , M.UPDT_DT                               
  FROM BCI_CLAMMAIN M /* 청구서 기본 */
 WHERE M.HSPT_KEY = #{hsptKey}
   AND M.CLAM_SQNO = #{clamSqno}
]]>
</select>

---

생성함수

CREATE OR REPLACE FUNCTION FN_DEV_SELECTSQL (tableName VARCHAR2, isComt VARCHAR2 DEFAULT 'Y')
    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;
        IF isComt = 'Y' THEN
            colText := '/* ' || readData.COLUMN_TEXT || ' */';
        END IF;

        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;

---

응용쿼리

WITH VDATA AS
(
SELECT 'BCI_SPCTDGNS' AS TABLE_NM
     , 'Y' AS IS_COMT
  FROM DUAL
)
SELECT FN_DEV_INSERTSQL(TABLE_NM, IS_COMT, 'N')
  FROM VDATA

UNION ALL

SELECT FN_DEV_INSERTSQL(TABLE_NM, IS_COMT, 'Y')
  FROM VDATA

UNION ALL

SELECT FN_DEV_UPDATESQL(TABLE_NM, IS_COMT)
  FROM VDATA

UNION ALL

SELECT FN_DEV_DELETESQL(TABLE_NM)
  FROM VDATA

UNION ALL

SELECT FN_DEV_SELECTSQL(TABLE_NM, IS_COMT)
  FROM VDATA

UNION ALL

SELECT FN_DEV_SELECTSQL(TABLE_NM, IS_COMT)
  FROM VDATA;