oracle代码换算字典里的,Oracle数据字典（用于做代码生成工具用）

/*************************************************************** User Table 表名、注释、字段类型、字段长度、是否为空、等***************************************************************/SELECT COL.TABLE_NAME as TNAME,   TCOM.COMMENTS as TCMT,COL.COLUMN_NAME as COL_NM,   CCOM.COMMENTS as COL_CMT,   COL.DATA_TYPE as TYPE_CD,   DECODE(COL.DATA_TYPE, 'NUMBER',COL.DATA_PRECISION ||'.'||COL.DATA_SCALE,    COL.DATA_LENGTH) as LENGTH,COL.NULLABLE as NULL_YN,COL.DATA_DEFAULT as D_DEFAULT,COL.NUM_DISTINCT as NUM_DISTINCTFROM USER_TAB_COLUMNS COL,USER_TAB_COMMENTS TCOM,USER_COL_COMMENTS CCOMWHERE COL.TABLE_NAME  = TCOM.TABLE_NAME ANDCOL.TABLE_NAME  = CCOM.TABLE_NAME ANDCOL.COLUMN_NAME = CCOM.COLUMN_NAME ANDCOL.TABLE_NAME  = 'ITEM_CLASS'ORDER BY COL.TABLE_NAME ,    COL.COLUMN_ID   --> PK 数据库主键SELECT COL.TABLE_NAME as TNAME,   TCOM.COMMENTS as TCMT,COL.COLUMN_NAME as COL_NM,   CCOM.COMMENTS as COL_CMT,COL.COLUMN_ID ID,PKCOL.COLUMN_POSITION AS PK,   COL.DATA_TYPE as TYPE_CD,   DECODE(COL.DATA_TYPE, 'NUMBER',COL.DATA_PRECISION ||'.'||COL.DATA_SCALE,    COL.DATA_LENGTH) as LENGTH,COL.NULLABLE as NULL_YN,COL.DATA_DEFAULT as D_DEFAULT,COL.NUM_DISTINCT as NUM_DISTINCTFROM USER_TAB_COLUMNS COL,USER_TAB_COMMENTS TCOM,USER_COL_COMMENTS CCOM,( SELECT  AA.TABLE_NAME, AA.INDEX_NAME, AA.COLUMN_NAME, AA.COLUMN_POSITION  FROM   USER_IND_COLUMNS AA, USER_CONSTRAINTS BB  WHERE   BB.CONSTRAINT_TYPE = 'P'  AND AA.TABLE_NAME = BB.TABLE_NAME  AND AA.INDEX_NAME = BB.CONSTRAINT_NAME--  AND AA.TABLE_NAME IN ('ITEM_CLASS')  -- 이것 풀면 좀더 빠름) PKCOLWHERE COL.TABLE_NAME  = TCOM.TABLE_NAME AND COL.TABLE_NAME  = CCOM.TABLE_NAME AND COL.COLUMN_NAME = CCOM.COLUMN_NAMEAND COL.TABLE_NAME  = 'ITEM_CLASS' AND COL.COLUMN_NAME = PKCOL.COLUMN_NAME(+)AND COL.TABLE_NAME  = PKCOL.TABLE_NAME(+)ORDER BY COL.TABLE_NAME ,    COL.COLUMN_ID  /*************************************************************** 能区别字段是否是主键的***************************************************************/  SELECT USER_TAB_COLUMNS.TABLE_NAME as TNAME,            USER_TAB_COMMENTS.COMMENTS as TCMT,   USER_TAB_COLUMNS.COLUMN_NAME as COL_NM,      USER_COL_COMMENTS.COMMENTS as COL_CMT,            USER_TAB_COLUMNS.DATA_TYPE as TYPE_CD,            DECODE(USER_TAB_COLUMNS.DATA_TYPE, 'NUMBER',USER_TAB_COLUMNS.DATA_PRECISION ||'.'||USER_TAB_COLUMNS.DATA_SCALE,    USER_TAB_COLUMNS.DATA_LENGTH) as LENGTH,         USER_TAB_COLUMNS.NULLABLE as NULL_YN    FROM USER_TAB_COLUMNS,  USER_TAB_COMMENTS,  USER_COL_COMMENTSWHERE USER_TAB_COLUMNS.TABLE_NAME  = USER_TAB_COMMENTS.TABLE_NAME AND USER_TAB_COLUMNS.TABLE_NAME  = USER_COL_COMMENTS.TABLE_NAME AND USER_TAB_COLUMNS.COLUMN_NAME = USER_COL_COMMENTS.COLUMN_NAMEAND USER_TAB_COMMENTS.TABLE_TYPE = 'TABLE' AND ( USER_TAB_COLUMNS.COLUMN_NAME  = 'ITEM_CODE'   OR USER_TAB_COLUMNS.COLUMN_NAME  = 'ITEM_FROM_DATE' )ORDER BY USER_TAB_COLUMNS.TABLE_NAME ,  USER_TAB_COLUMNS.COLUMN_ID   --> 表名，最大的，最小select tname, '',  MAX(col1), MAX(col2)from (  SELECT USER_TAB_COLUMNS.TABLE_NAME as TNAME,      decode(USER_TAB_COLUMNS.COLUMN_NAME,'ITEM_CODE', USER_TAB_COLUMNS.COLUMN_NAME, '') COL1,   decode(USER_TAB_COLUMNS.COLUMN_NAME,'ITEM_FROM_DATE', USER_TAB_COLUMNS.COLUMN_NAME, '') COL2    FROM USER_TAB_COLUMNS,  USER_TAB_COMMENTS,  USER_COL_COMMENTSWHERE USER_TAB_COLUMNS.TABLE_NAME  = USER_TAB_COMMENTS.TABLE_NAME AND USER_TAB_COLUMNS.TABLE_NAME  = USER_COL_COMMENTS.TABLE_NAME AND USER_TAB_COLUMNS.COLUMN_NAME = USER_COL_COMMENTS.COLUMN_NAMEAND USER_TAB_COMMENTS.TABLE_TYPE = 'TABLE'AND ( USER_TAB_COLUMNS.COLUMN_NAME  = 'ITEM_CODE'   OR USER_TAB_COLUMNS.COLUMN_NAME  = 'ITEM_FROM_DATE' )ORDER BY USER_TAB_COLUMNS.TABLE_NAME ,         USER_TAB_COLUMNS.COLUMN_ID   )group by tname/*************************************************************** ***************************************************************/SELECT AA.TABLE_NAME, AA.INDEX_NAME, AA.COLUMN_NAME, AA.COLUMN_POSITIONFROM   SYS.DBA_IND_COLUMNS AA, SYS.DBA_CONSTRAINTS BBWHERE  AA.TABLE_OWNER = 'IMPORT'--AND    AA.TABLE_NAME IN ('ITEM_CLASS')AND BB.CONSTRAINT_TYPE = 'P'AND AA.TABLE_OWNER = BB.OWNERAND AA.TABLE_NAME = BB.TABLE_NAMEAND AA.INDEX_NAME = BB.CONSTRAINT_NAMEORDER BY AA.TABLE_NAME, AA.COLUMN_POSITION