数据库类型研究

我从USER_TAB_COLS中取内容，从USER_COL_COMMENTS中取备注可以得到下面的表结构

SELECT 
USER_TAB_COLS.COLUMN_ID AS 列序号 ,
USER_TAB_COLS.COLUMN_NAME AS 列名 , 
USER_TAB_COLS.DATA_TYPE AS 数据类型,
USER_TAB_COLS.DATA_LENGTH AS 长度,
USER_TAB_COLS.NULLABLE AS 是否可空,
USER_COL_COMMENTS.COMMENTS AS 备注
FROM USER_TAB_COLS
INNER JOIN USER_COL_COMMENTS ON
USER_COL_COMMENTS.TABLE_NAME = USER_TAB_COLS.TABLE_NAME
AND USER_COL_COMMENTS.COLUMN_NAME = USER_TAB_COLS.COLUMN_NAME
WHERE USER_TAB_COLS.TABLE_NAME = 'AI_HFSC_ZH_CHK' ORDER BY 列序号

但是如果用plsql查询该表的表结构，可以发现不一样的状况

如果出错，那一定是我的语句找错了，那么，我应该怎么改这个语句，让我得到的数据与plsql提供的表结构数据一致呢？

select A.COLUMN_ID as 列序号,
       A.COLUMN_NAME as 列名,
       case
         when A.DATA_TYPE = 'CHAR' then
          A.DATA_TYPE || '(' || A.DATA_LENGTH || ')'
         when A.DATA_TYPE = 'VARCHAR2' then
          A.DATA_TYPE || '(' || A.DATA_LENGTH || ')'
         when A.DATA_TYPE = 'DATE' then
          A.DATA_TYPE
         when A.DATA_TYPE = 'NUMBER' and A.DATA_PRECISION IS NULL AND
              a.DATA_SCALE = 0 then
          'INTEGER'
         when A.DATA_TYPE = 'NUMBER' AND a.DATA_SCALE = 0 then
          A.DATA_TYPE || '(' || A.DATA_PRECISION || ')'
         when A.DATA_TYPE = 'NUMBER' AND a.DATA_SCALE != 0 then
          A.DATA_TYPE || '(' || A.DATA_PRECISION || ',' || A.DATA_SCALE || ')'
       END as 数据类型,
     
       decode(C.COLUMN_NAME, null, '', '√') as 主键,
       decode(A.NULLABLE, 'Y', '√') as 可为空,
       B.comments as 备注
  from sys.user_tab_cols A,
       　sys.user_col_comments B,
       (select col.column_name, c.table_name, col.owner
          from user_constraints c, user_cons_columns col 　　
         where c.constraint_name = col.constraint_name 　　and
         c.constraint_type = 'P') C
 where upper(A.TABLE_NAME) = 'AI_HFSC_ZH_CHK' 　　
       and A.TABLE_NAME = B.table_name 　　
       and A.COLUMN_NAME = B.column_name
 　　  and A.Table_Name = C.TABLE_NAME(+) 　　
       and A.COLUMN_NAME = C.COLUMN_NAME(+) 　　
 
 order by A.TABLE_NAME, A.Column_Id

如果说查询当前User所有的外键

--查询外键约束的列名： 
select ucc.constraint_name,ucc.table_name,ucc.column_name
from user_cons_columns ucc 
left join user_constraints uc on uc.constraint_name=ucc.constraint_name
where uc.constraint_type='R' ;

 然后加入对于外键的查询    

-----------解决了外键的问题，但时  数据类型、备注 又出问题了
select A.COLUMN_ID as 列序号,
       A.COLUMN_NAME as 列名,
       case
         when A.DATA_TYPE = 'CHAR' then
          A.DATA_TYPE || '(' || A.DATA_LENGTH || ')'
         when A.DATA_TYPE = 'VARCHAR2' then
          A.DATA_TYPE || '(' || A.DATA_LENGTH || ')'
         when A.DATA_TYPE = 'DATE' then
          A.DATA_TYPE
         when A.DATA_TYPE = 'NUMBER' and A.DATA_PRECISION IS NULL AND
              a.DATA_SCALE = 0 then
          'INTEGER'
         when A.DATA_TYPE = 'NUMBER' AND a.DATA_SCALE = 0 then
          A.DATA_TYPE || '(' || A.DATA_PRECISION || ')'
         when A.DATA_TYPE = 'NUMBER' AND a.DATA_SCALE != 0 then
          A.DATA_TYPE || '(' || A.DATA_PRECISION || ',' || A.DATA_SCALE || ')'
       END as 数据类型,
       decode(C.COLUMN_NAME, null, '', '√') as 主键,
       decode(d.constraint_name,null, '', '√') as 外键,
       decode(A.NULLABLE, 'Y', '√') as 可为空,
       B.comments as 备注
  from sys.user_tab_cols A,
       　sys.user_col_comments B,
       (select col.column_name, c.table_name, col.owner
          from user_constraints c, user_cons_columns col 　　
         where c.constraint_name = col.constraint_name 　　and
         c.constraint_type = 'P') C,
         (select ucc.constraint_name,ucc.table_name,ucc.column_name
from user_cons_columns ucc 
left join user_constraints uc on uc.constraint_name=ucc.constraint_name
where uc.constraint_type='R') D
  
 where A.TABLE_NAME = 'ACT_RU_JOB' 　　
       and A.TABLE_NAME = B.table_name 　　
       and A.COLUMN_NAME = B.column_name
 　　  and A.Table_Name = C.TABLE_NAME(+) 　　
       and A.COLUMN_NAME = C.COLUMN_NAME(+) 　　
       and a.TABLE_NAME=d.table_name(+) and a.COLUMN_NAME=d.column_name(+)
 order by A.TABLE_NAME, A.Column_Id

　应该是什么地方出错了

想了想，修改后可以得到

-----------和plsql的column选项卡完全一样的表格

SELECT A.COLUMN_ID as 列序号,
       A.COLUMN_NAME as 列名,
       case
         when A.DATA_TYPE in ('CHAR', 'NCHAR') then
          A.DATA_TYPE || '(' || A.CHAR_COL_DECL_LENGTH || ')'
         when A.DATA_TYPE in ('VARCHAR2', 'NVARCHAR2' ， 'RAW') then
          A.DATA_TYPE || '(' || A.CHAR_LENGTH || ')'
         when A.DATA_TYPE = 'FLOAT' then
          A.DATA_TYPE || '(' || A.DATA_PRECISION || ')'
         when A.DATA_TYPE = 'UROWID' then
          A.DATA_TYPE || '(' || A.DATA_LENGTH || ')'
         when A.DATA_TYPE = 'NUMBER' and A.DATA_PRECISION IS NULL AND
              a.DATA_SCALE = 0 then
          'INTEGER'
         when A.DATA_TYPE = 'NUMBER' AND a.DATA_SCALE = 0 then
          A.DATA_TYPE || '(' || A.DATA_PRECISION || ')'
         when A.DATA_TYPE = 'NUMBER' AND a.DATA_SCALE != 0 then
          A.DATA_TYPE || '(' || A.DATA_PRECISION || ',' || A.DATA_SCALE || ')'
         else
          A.DATA_TYPE
       END as 数据类型,
       c.primary_enable as 主键,
       d.foreign_enable as 外键,
       decode(A.NULLABLE, 'Y', '√') as 可为空,
       B.comments as 备注
  from sys.user_tab_cols A
  left join sys.user_col_comments B
    on A.TABLE_NAME = B.table_name
   and A.COLUMN_NAME = B.column_name

  left join (select col.column_name,
                    c.table_name,
                    case
                      when col.COLUMN_NAME is not null and
                           c.status = 'ENABLED' then
                       '√'
                      else
                       ''
                    end as primary_enable
               from user_constraints c, user_cons_columns col 　　
              where c.constraint_name = col.constraint_name 　　and
              c.constraint_type = 'P') C
    on A.Table_Name = C.TABLE_NAME
   and A.COLUMN_NAME = C.COLUMN_NAME
  left join (select uc.constraint_name,
                    ucc.table_name,
                    ucc.column_name,
                    case
                      when ucc.COLUMN_NAME is not null and
                           uc.status = 'ENABLED' then
                       '√'
                      else
                       ''
                    end as foreign_enable
               from user_cons_columns ucc
               left join user_constraints uc
                 on uc.constraint_name = ucc.constraint_name
              where uc.constraint_type = 'R') D
    on A.Table_Name = D.TABLE_NAME
   and A.COLUMN_NAME = D.COLUMN_NAME
 where A.TABLE_NAME = '{tableInfo.TableName}'
 order by A.TABLE_NAME, A.COLUMN_ID

 或者使用和原数据表一样的列名

SELECT A.COLUMN_ID as 列序号,A.COLUMN_NAME as 列名,
                           case
                             when A.DATA_TYPE = 'NUMBER' and A.DATA_PRECISION IS NULL AND a.DATA_SCALE = 0 then
                              'INTEGER'
                             when A.DATA_TYPE = 'TIMESTAMP(6)'then
                              'TIMESTAMP'
                             else
                               A.DATA_TYPE
                           END as 数据类型,
                                                      
                           case
                             when A.DATA_TYPE in ('CHAR','NCHAR') then
                              to_char(A.CHAR_COL_DECL_LENGTH)
                             when A.DATA_TYPE in ('VARCHAR2','NVARCHAR2'，'RAW','UROWID') then
                              to_char(A.CHAR_LENGTH)
                             when A.DATA_TYPE = 'FLOAT' then
                              to_char(A.DATA_PRECISION)
                             when A.DATA_TYPE = 'NUMBER' and A.DATA_PRECISION IS NULL AND a.DATA_SCALE = 0 then
                              ''
                             when A.DATA_TYPE = 'NUMBER' AND a.DATA_SCALE = 0 then
                              to_char(A.DATA_PRECISION)
                             when A.DATA_TYPE = 'NUMBER' AND a.DATA_SCALE != 0 then
                              to_char(A.DATA_PRECISION || ',' || A.DATA_SCALE)
                             when A.DATA_TYPE = 'TIMESTAMP(6)' then
                              '6'
                              ELSE
                               ''
                           END as 长度,
                           c.primary_enable as 主键,d.foreign_enable as 外键,decode(A.NULLABLE, 'Y', '√') as 可为空,B.comments as 备注
                      from sys.user_tab_cols A
                      left join sys.user_col_comments B on A.TABLE_NAME = B.table_name
                       and A.COLUMN_NAME = B.column_name

left join (select col.column_name, c.table_name,case when col.COLUMN_NAME is not null and c.status='ENABLED' then '√' else '' end as primary_enable
                                   from user_constraints c, user_cons_columns col 　　
      where c.constraint_name = col.constraint_name 　　and
                                  c.constraint_type = 'P') C
                        on A.Table_Name = C.TABLE_NAME
                       and A.COLUMN_NAME = C.COLUMN_NAME
                      left join (select uc.constraint_name, ucc.table_name, ucc.column_name,case when ucc.COLUMN_NAME is not null and uc.status='ENABLED' then '√' else '' end as foreign_enable
                                   from user_cons_columns ucc
                                   left join user_constraints uc
                                     on uc.constraint_name = ucc.constraint_name
                                  where uc.constraint_type = 'R') D
                        on A.Table_Name = D.TABLE_NAME
                       and A.COLUMN_NAME = D.COLUMN_NAME
                     where A.TABLE_NAME = '{tableInfo.TableName}'
                     order by A.TABLE_NAME, A.COLUMN_ID

当然，处理得还不足够完善，