因数据库设计、使用问题number(1-4,0)都影射成short
INT16=Short
INT32=Integer
INT64=Long
DECIMAL=BigDecimal
select temp.column_name columnname,
temp.data_type dataType,
temp.comments columnComment,
case temp.constraint_type
when 'P' then
'PRI'
when 'C' then
'UNI'
else
''
end "COLUMNKEY",
'' "EXTRA"
from (select col.column_id,
col.column_name,
DECODE(upper(col.data_type),
upper('Number'),
DECODE(col.DATA_SCALE,
0,
DECODE(sign(col.DATA_PRECISION - 18),
1,
upper('DECIMAL'),
DECODE(sign(col.DATA_PRECISION - 9),
1,
upper('int64'),
DECODE(sign(col.DATA_PRECISION - 4),
1,
upper('int32'),
upper('int16')))),
upper('DECIMAL')),
col.data_type) as data_type,
colc.comments,
uc.constraint_type,
-- 去重
row_number() over(partition by col.column_name order by uc.constraint_type desc) as row_flg
from user_tab_columns col
left join user_col_comments colc
on colc.table_name = col.table_name
and colc.column_name = col.column_name
left join user_cons_columns ucc
on ucc.table_name = col.table_name
and ucc.column_name = col.column_name
left join user_constraints uc
on uc.constraint_name = ucc.constraint_name
where col.table_name = upper('bm0001')) temp
where temp.row_flg = 1
order by temp.column_id