SELECT A.*
, CASE WHEN A.COMMENTS IS NOT NULL THEN CHR(10) || '/* ' || LPAD(A.COLUMN_ID, 3, ' ') || '. ' || A.COMMENTS || ' */' || CHR(10) END
|| 'private ' || RPAD(A.VO_VAR_TYPE, 8, ' ') || A.VO_VAR_NAME || ';' AS VO_VAR
, CASE WHEN A.COLUMN_ID = 1 AND 'MULTILINE' = 'MULTILINE1'
THEN CHR(10) || 'SELECT' || CHR(10) || LPAD(' ', 8, ' ')
WHEN A.COLUMN_ID = 1
THEN CHR(10) || 'SELECT' || LPAD(' ', 2, ' ')
ELSE RPAD(LPAD(',', 6, ' '), 8, ' ')
END
|| ALIAS_COLUMN_NAME || '/* ' || LPAD(A.COLUMN_ID, 3, ' ') || '. ' || A.COMMENTS || ' */'
|| CASE WHEN A.COLUMN_ID = MAX(A.COLUMN_ID) OVER()
THEN CHR(10) || ' FROM ' || A.OWNER || '.' || A.TABLE_NAME || ' ' || NVL(:ALIAS, 'A')
|| CHR(10) || ' WHERE 1 = 1'
END AS SELECT_SQL
, ' AND (' || CHR(10)
|| ' ' || ALIAS_COLUMN_NAME || '= #{' || A.VO_VAR_NAME || '}' || A.ALIAS_COMMENT || CHR(10)
|| ' OR #{' || A.VO_VAR_NAME || '} IS NULL' || CHR(10)
|| ' )' AS WHERE_SQL
, CASE WHEN A.COLUMN_ID = 1
THEN 'UPDATE ' || A.OWNER || '.' || A.TABLE_NAME || ' ' || NVL(:ALIAS, 'A') || CHR(10)
|| ' SET '
ELSE ' , '
END || ALIAS_COLUMN_NAME || '= #{' || A.VO_VAR_NAME || '}' || A.ALIAS_COMMENT
|| CASE WHEN A.COLUMN_ID = MAX(A.COLUMN_ID) OVER()
THEN CHR(10) || ' WHERE 1 = 1'
END AS UPDATE_SQL
, ' AND ' || ALIAS_COLUMN_NAME || '= #{' || A.VO_VAR_NAME || '}' || A.ALIAS_COMMENT
AS WHERE_SQL2
, CASE WHEN A.COLUMN_ID = 1
THEN 'INSERT' || CHR(10) || ' INTO ' || A.OWNER || '.' || A.TABLE_NAME || '(' || CHR(10) || LPAD(' ', 8, ' ')
ELSE ' , '
END || A.NO_ALIAS_COLUMN_NAME || '/* ' || LPAD(A.COLUMN_ID, 3, ' ') || '.' || A.COMMENTS || ' */'
|| CASE WHEN A.COLUMN_ID = MAX(A.COLUMN_ID) OVER()
THEN CHR(10) || ' ) VALUES('
END AS INSERT_SQL
, CASE WHEN A.COLUMN_ID = 1
THEN ' '
ELSE ' , '
END || '#{' || A.VO_VAR_NAME ||'}' || A.VALUES_COMMENT
|| CASE WHEN A.COLUMN_ID = MAX(A.COLUMN_ID) OVER()
THEN CHR(10) || ' )'
END AS VALUES_SQL
, MOD(MAX_COLUMN_SIZE, 4)
, MAX_COLUMN_SIZE
, LENGTH(A.COLUMN_NAME)
FROM (
SELECT A.*
, A.COLUMN_NAME || LPAD(' ', 4 - MOD(MAX_COLUMN_SIZE, 4) + MAX_COLUMN_SIZE - LENGTH(A.COLUMN_NAME), ' ') AS NO_ALIAS_COLUMN_NAME
, LPAD(' ', 4 - MOD(MAX_COLUMN_SIZE, 4) + MAX_COLUMN_SIZE - LENGTH(A.COLUMN_NAME), ' ') || '/* ' || LPAD(A.COLUMN_ID, 3, ' ') || '.' || A.COMMENTS || ' */' AS NO_ALIAS_COMMENT
, A.ALIAS || A.COLUMN_NAME || LPAD(' ', 4 - MOD( LENGTH(A.ALIAS) + MAX_COLUMN_SIZE, 4) + MAX_COLUMN_SIZE - LENGTH(A.COLUMN_NAME), ' ') AS ALIAS_COLUMN_NAME
, LPAD(' ', 7 - MOD(LENGTH(MAX(A.VO_VAR_NAME) OVER()), 4) + LENGTH(MAX(A.VO_VAR_NAME) OVER()) - LENGTH(A.VO_VAR_NAME), ' ') || '/* ' || LPAD(A.COLUMN_ID, 3, ' ') || '.' || A.COMMENTS || ' */' AS ALIAS_COMMENT
, LPAD(' ', 5 - MOD(LENGTH(MAX(A.VO_VAR_NAME) OVER()), 4) + LENGTH(MAX(A.VO_VAR_NAME) OVER()) - LENGTH(A.VO_VAR_NAME), ' ') || '/* ' || LPAD(A.COLUMN_ID, 3, ' ') || '.' || A.COMMENTS || ' */' AS VALUES_COMMENT
FROM (
SELECT A.OWNER
, A.TABLE_NAME
, A.COLUMN_ID
, B.COMMENTS
, A.COLUMN_NAME
, A.DATA_TYPE
, CASE WHEN A.DATA_TYPE = 'DATE' THEN 'Date' ELSE 'String' END AS VO_VAR_TYPE
, LOWER(SUBSTR(A.COLUMN_NAME, 1, 1)) || REPLACE(SUBSTR(INITCAP(A.COLUMN_NAME), 2), '_', '') AS VO_VAR_NAME
, MAX(LENGTH(A.COLUMN_NAME)) OVER() AS MAX_COLUMN_SIZE
, NVL(:ALIAS, 'A') || '.' AS ALIAS
FROM ALL_TAB_COLUMNS A
, ALL_COL_COMMENTS B
WHERE (
A.OWNER = UPPER(:OWNER)
OR (
:OWNER IS NULL
AND A.OWNER = ''
)
)
AND A.TABLE_NAME = UPPER(:TABLE_NAME)
AND B.OWNER(+) = A.OWNER
AND B.TABLE_NAME(+) = A.TABLE_NAME
AND B.COLUMN_NAME(+) = A.COLUMN_NAME
) A
) A
ORDER
BY A.COLUMN_ID;
오라클/SQL