오라클 INSERT myBatis 생성함수 ver2

---

결과값이 4000자가 넘어서면 오류가 있어서,

주석을 제외하는 기능을 추가하였다...가

주석을 제외해도 4000자가 넘어서는 경우가 있어서,

상/하단으로 쪼개서 return할 수 있도록 보완하였다.

---

/* 사용예제 */
SELECT FN_DEV_INSERTSQL('BCI_CLAMMAIN' /* TABLE명 */, 'N', 'N') FROM DUAL
UNION ALL
SELECT FN_DEV_INSERTSQL('BCI_CLAMMAIN' /* TABLE명 */, 'N', 'Y') FROM DUAL;

---

실행결과

<insert id="insertBasic" parameterType="mdata">
<![CDATA[
INSERT INTO BCI_CLAMMAIN
(
       HSPT_KEY                                
     , CLAM_SQNO                               
     , CLAM_NO                                 
     , CLAM_NM                                 
     , AGRG_TYPE_CD                            
     , MED_DVSN                                
     , HSPT_BYKN_DVSN                          
     , CLAM_INS_TYPE                           
     , FXAM_DVSN_CD                            
     , VTRN_DVSN_CD                            
     , CLFR_MDFR_VRSN                          
     , SPCT_MDFR_VRSN                          
     , CLFR_MDFR_NO                            
     , HSPT_RGNO                               
     , RECV_INST                               
     , INSR_BYKN_DVSN                          
     , CLAM_DVSN                               
     , CLAM_UNIT_DVSN                          
     , CLAM_MED_DVSN                           
     , MED_FIEL_DVSN                           
     , MED_SHPE                                
     , CLAM_YM                                 
     , AGRG_STR_DY                             
     , AGRG_END_DY                             
     , CLAM_NOCS                               
     , EXPN_TAMT1                              
     , UCAMT                                   
     , UOAMT                                   
     , CLAMT                                   
     , FDAMT                                   
     , DBPS_MDCS                               
     , EXPN_TAMT2                              
     , VTRN_CLAMT                              
     , NOIN_UTAMT                              
     , VTRN_UCAMT                              
     , SMCS_TAMT                               
     , SMCS_UCAMT                              
     , SMCS_CLAMT                              
     , SMCS_VTRN_CLAMT                         
     , DFEE_MED_NODY                           
     , DFEE_DR_CNT                             
     , DFRN_INDX                               
     , DFEE_CLAMT                              
     , CRTN_DY                                 
     , CLAM_DY                                 
     , CLAM_PRSN_NM                            
     , WRTR_NM                                 
     , WRTR_DOBR                               
     , CLAM_PRMT_NO                            
     , VCEX_CLAM_PRTY                          
     , RFRN                                    
     , TRAI_CO                                 
     , CONV_DT                                 
     , SEND_DT                                 
     , RECV_DT                                 
     , CLAM_RCPN_NO                            
     , REGI_ID                                 
     , RGST_DT                                 
     , AMEN_ID                                 
     , UPDT_DT                                 
) VALUES (

       #{hsptKey}
     , #{clamSqno}
     , #{clamNo}
     , #{clamNm}
     , #{agrgTypeCd}
     , #{medDvsn}
     , #{hsptByknDvsn}
     , #{clamInsType}
     , #{fxamDvsnCd}
     , #{vtrnDvsnCd}
     , #{clfrMdfrVrsn}
     , #{spctMdfrVrsn}
     , #{clfrMdfrNo}
     , #{hsptRgno}
     , #{recvInst}
     , #{insrByknDvsn}
     , #{clamDvsn}
     , #{clamUnitDvsn}
     , #{clamMedDvsn}
     , #{medFielDvsn}
     , #{medShpe}
     , #{clamYm}
     , #{agrgStrDy}
     , #{agrgEndDy}
     , #{clamNocs}
     , #{expnTamt1}
     , #{ucamt}
     , #{uoamt}
     , #{clamt}
     , #{fdamt}
     , #{dbpsMdcs}
     , #{expnTamt2}
     , #{vtrnClamt}
     , #{noinUtamt}
     , #{vtrnUcamt}
     , #{smcsTamt}
     , #{smcsUcamt}
     , #{smcsClamt}
     , #{smcsVtrnClamt}
     , #{dfeeMedNody}
     , #{dfeeDrCnt}
     , #{dfrnIndx}
     , #{dfeeClamt}
     , #{crtnDy}
     , #{clamDy}
     , #{clamPrsnNm}
     , #{wrtrNm}
     , #{wrtrDobr}
     , #{clamPrmtNo}
     , #{vcexClamPrty}
     , #{rfrn}
     , #{traiCo}
     , #{convDt}
     , #{sendDt}
     , #{recvDt}
     , #{clamRcpnNo}
     , #{_loginId}
     , SYSDATE
     , #{_loginId}
     , SYSDATE
)
]]>
</insert>

---

생성함수

CREATE OR REPLACE FUNCTION FN_DEV_INSERTSQL (tableName VARCHAR2, isComt VARCHAR2 DEFAULT 'Y', isTail VARCHAR2 DEFAULT 'A')
    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 := '    <insert id="insertBasic" parameterType="mdata">' || enterStr
              || '    <![CDATA[' || enterStr
              || '    INSERT INTO ' || tableName || enterStr
              || '    (' || 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;
        inptText := textHead || RPAD(colName, 40, ' ') || colText;
        textHead := '         , ';

        returnSql := returnSql || inptText || enterStr;
    END LOOP;

    returnSql := returnSql || '    ) VALUES (' || enterStr;
    
    IF isTail = 'Y' THEN
        returnSql := '';
    ELSIF isTail = 'N' THEN
        RETURN returnSql;
    END IF;
        
    textHead := '           ';

    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
        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 || '#{_loginId}';
            WHEN 'UPDT_DT' THEN inptText := textHead || 'SYSDATE';
            WHEN 'REGI_ID' THEN inptText := textHead || '#{_loginId}';
            WHEN 'RGST_DT' THEN inptText := textHead || 'SYSDATE';
            ELSE inptText := textHead || '#{' || camelText || '}';
        END CASE;

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

    returnSql := returnSql || '    )' || enterStr;
    returnSql := returnSql || '    ]]>' || enterStr;
    returnSql := returnSql || '    </insert>' || enterStr;

    RETURN returnSql;
END;