Oracle根据用户名和表名查询表的字段和字段类型等信息

标签：用户名 name column MEDICAL mr 表名 Oracle DCS select

1 该用户下所有表的字段筛选方法

select a.column_name as uploadcolumn
  from user_tab_columns a
 where a.DATA_TYPE = 'VARCHAR2'
   and a.TABLE_NAME = 'DCS_MEDICAL_RECORD'

View Code

注释：查询当前登录用户下的表名为dcs_medical_record的所有字段类型为varchar2的字段名。

2 查询当前用户下的ORI_DCS_MEDICAL_SETTLE表中的所有字段名以及注释

select lower(a.column_name) column_name, a.comments
  from user_col_comments a
 where a.table_name = 'ORI_DCS_MEDICAL_SETTLE'

View Code

3 查询当前用户下的表ORI_DCS_MEDICAL_SETTLE和表MR_MEDICAL_RECORD中重合的字段的字段名、字段类型、字段注释

select lower(b.column_name) column_name, b.data_type, a.comments
  from user_tab_columns b
  left join user_col_comments a
    on b.COLUMN_NAME = a.column_name
 where b.table_name = 'MR_MEDICAL_RECORD'
   and a.table_name = 'ORI_DCS_MEDICAL_SETTLE';

View Code

4 查询表mr_icuinfo 中mr_sl_id=2873的所有数据以及统计总数

select r.*
  from (select n.pk_id,
               n.mr_sl_id,
               n.zzjhbflx,
               n.jzzjhssj,
               n.czzjhssj,
               n.hj,
               count(*) over() total
          from hos_drgs.mr_icuinfo n
         where 1 = 1
           and n.mr_sl_id = '2873') r;

View Code

 

标签：用户名,name,column,MEDICAL,mr,表名,Oracle,DCS,select
From： https://www.cnblogs.com/daytoy105/p/16655770.html