오라클 INSERT ver3.0

---

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 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;

        colName := readData.COLUMN_NAME;

        IF isComt = 'Y' THEN
            colText := '/* ' || readData.COLUMN_TEXT || ' */';
            inptText := textHead || RPAD(colName, 40, ' ') || colText;
        ELSE
            inptText := TRIM(textHead) || colName;
        END IF;

        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 'RGST_ID' THEN inptText := textHead || ':loginId';
            WHEN 'RGST_DT' THEN inptText := textHead || 'SYSDATE';
            WHEN 'UPDT_ID' THEN inptText := textHead || ':loginId';
            WHEN 'UPDT_DT' THEN inptText := textHead || 'SYSDATE';
            WHEN 'UPDT_HIT' THEN inptText := textHead || '0';
            ELSE inptText := textHead || ':' || camelText || '';
        END CASE;

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

    returnSql := returnSql || ')';

    RETURN returnSql;
END;

---