오라클 UPDATE myBatis 생성함수 ver2.1

---

앞단과 뒷단을 잘라서 생성하여, 칼럼이 많은 경우에 발생하는 버퍼사이즈 오류를 해결

---

CREATE OR REPLACE FUNCTION FN_DEV_UPDATESQL (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);
    forCnt NUMBER := 0;
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;

        IF isComt = 'Y' THEN
            colText := '/* ' || readData.COLUMN_TEXT || ' */';
        END IF;

        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;

        colName := colName || ' = #{' || camelText || '}';

        IF isComt = 'Y' THEN
            colName := RPAD(colName, 40);
        END IF;

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

        IF inptText IS NULL THEN
            CONTINUE;
        END IF;

        forCnt := forCnt + 1;
        IF forCnt = 40 THEN
          IF isTail = 'Y' THEN
              returnSql := '';
          ELSIF isTail = 'N' THEN
              RETURN returnSql;
          END IF;
        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;