[Oracle] 테이블 명세서 뷰

CREATE OR REPLACE FORCE EDITIONABLE VIEW "UNIVCOOP_DEV"."V_COLUMNINFO" ("TABLE_COMMENTS", "TABLE_NAME", "COLUMN_COMMENTS", "COLUMN_NAME", "COLUMN_NAME2", "COLUMN_NAME_DAO", "JAVA_TYPE", "JAVA_ATTR", "PK_FLAG", "FK_FLAG", "NULL_FLAG", "DATA_TYPE", "DATA_LENGTH") AS 
  SELECT A1.TABLE_COMMENTS TABLE_COMMENTS
     , A1.TABLE_NAME TABLE_NAME
     , A1.COLUMN_COMMENTS COLUMN_COMMENTS
     , A1.COLUMN_NAME COLUMN_NAME
     ,  LOWER(SUBSTR(REPLACE(INITCAP(A1.COLUMN_NAME),'_'),1,1))
       ||
       SUBSTR(REPLACE(INITCAP(A1.COLUMN_NAME),'_'),2) AS COLUMN_NAME2
     , '#{' || LOWER(SUBSTR(REPLACE(INITCAP(A1.COLUMN_NAME),'_'),1,1))
       ||
       SUBSTR(REPLACE(INITCAP(A1.COLUMN_NAME),'_'),2) || '},'  AS COLUMN_NAME_DAO
     , JAVA_TYPE
     , A1.JAVA_ATTR || LOWER(SUBSTR(REPLACE(INITCAP(A1.COLUMN_NAME),'_'),1,1))
       ||
       SUBSTR(REPLACE(INITCAP(A1.COLUMN_NAME),'_'),2) || ';' AS JAVA_ATTR
     , (CASE
           WHEN B1.CONSTRAINT_TYPE = 'P'
              THEN 'Y'
        END) PK_FLAG
     , (CASE
           WHEN B1.CONSTRAINT_TYPE = 'R'
              THEN 'Y'
        END) FK_FLAG
     , A1.NULL_FLAG
     , A1.DATA_TYPE
     , A1.DATA_LENGTH
    
  FROM (SELECT B.COMMENTS TABLE_COMMENTS
             , A.TABLE_NAME TABLE_NAME
             , C.COMMENTS COLUMN_COMMENTS
             , A.COLUMN_NAME COLUMN_NAME
             , (CASE A.NULLABLE
                   WHEN 'Y'
                      THEN 'Y'
                END) NULL_FLAG
             , A.DATA_TYPE DATA_TYPE
             , A.DATA_PRECISION
             , A.DATA_SCALE
             , (CASE
                   WHEN A.DATA_TYPE IN ('CHAR', 'VARCHAR2')
                      THEN '(' || A.DATA_LENGTH || ')'
                   WHEN A.DATA_TYPE = 'NUMBER'
                   AND A.DATA_SCALE = 0
                   AND A.DATA_PRECISION IS NOT NULL
                      THEN '(' || A.DATA_PRECISION || ')'
                   WHEN A.DATA_TYPE = 'NUMBER'
                   AND A.DATA_SCALE <> 0
                      THEN '(' || A.DATA_PRECISION || ',' || A.DATA_SCALE
                           || ')'
                END
               ) DATA_LENGTH
              , (CASE
			                   WHEN A.DATA_TYPE IN ('CHAR', 'VARCHAR2')
			                      THEN 'String'
			                   WHEN A.DATA_TYPE = 'NUMBER'
			                        AND A.DATA_SCALE = 0
			                        AND A.DATA_PRECISION IS NOT NULL
			                      THEN 'Integer'
			                   WHEN A.DATA_TYPE = 'NUMBER'
			                        AND A.DATA_SCALE <> 0
			                      THEN 'Decimal'
			                    WHEN A.DATA_TYPE = 'NUMBER'
			                      THEN 'Long'
			                   WHEN A.DATA_TYPE = 'DATE'
			                      THEN 'LocalDateTime'
			                   WHEN A.DATA_TYPE = 'TIMESTAMP'
			                      THEN 'Timestamp'
			                    WHEN A.DATA_TYPE = 'CLOB'
			                      THEN 'Clob'
			                END
			      ) AS JAVA_TYPE
			  , 'private ' || (CASE
			                   WHEN A.DATA_TYPE IN ('CHAR', 'VARCHAR2')
			                      THEN 'String'
			                   WHEN A.DATA_TYPE = 'NUMBER'
			                        AND A.DATA_SCALE = 0
			                        AND A.DATA_PRECISION IS NOT NULL
			                      THEN 'Integer'
			                   WHEN A.DATA_TYPE = 'NUMBER'
			                        AND A.DATA_SCALE <> 0
			                      THEN 'Decimal'
			                    WHEN A.DATA_TYPE = 'NUMBER'
			                      THEN 'Long'
			                   WHEN A.DATA_TYPE = 'DATE'
			                      THEN 'LocalDateTime'
			                   WHEN A.DATA_TYPE = 'TIMESTAMP'
			                      THEN 'Timestamp'
			                    WHEN A.DATA_TYPE = 'CLOB'
			                      THEN 'Clob'
			                END
			               )|| ' ' AS JAVA_ATTR 
             , A.COLUMN_ID
          FROM USER_TAB_COLUMNS A
             , USER_TAB_COMMENTS B
             , USER_COL_COMMENTS C
         WHERE (A.TABLE_NAME = B.TABLE_NAME)
           AND (    A.TABLE_NAME = C.TABLE_NAME
                AND A.COLUMN_NAME = C.COLUMN_NAME
               )) A1
     , (SELECT A.TABLE_NAME
             , A.COLUMN_NAME
             , B.CONSTRAINT_TYPE
          FROM USER_CONS_COLUMNS A
             , USER_CONSTRAINTS B
         WHERE (A.CONSTRAINT_NAME = B.CONSTRAINT_NAME)
           AND B.CONSTRAINT_TYPE IN ('P', 'R')) B1
WHERE (    A1.TABLE_NAME = B1.TABLE_NAME(+)
        AND A1.COLUMN_NAME = B1.COLUMN_NAME(+))
ORDER BY A1.TABLE_NAME, A1.COLUMN_ID
;