Oracle获取某列的数据类型及某表的所有列名

怼烎@ 2022-05-21 12:50 343阅读 0赞

**（作者：陈玓玏）**

在使用数据库的时候，经常会出现用create as select、insert into select这些语句来复制表的操作，但是这些操作做来做去常常把**数据类型给做丢**，导致最后复制出来的表大多都变成了varchar2这类的字符串型，导致在对字段使用函数时常常报错，比如对一个显示为数字但实际为字符串类型的字段做除法，会报需要一个数字但获得一个字符串这类的错。

那么我有时候为了代码的灵活性，会希望根据字段类型来判断是否需要先将字段做类型转换，当然了，**最好的方法是始终记得先做类型转换**。但是既然研究了这个问题，还是分享一下这个麻烦的方法。

**功能有很多，但是通过下面这一段代码就可以全部看到。**

下面这段代码的功能是：从数据库中的所有表中找出user\_name这个用户的所有表，从这些表中找出表明为table\_name的表，从这个表中找出col\_name列，获得它的数据类型。

select data_type from all_tab_columns WHERE TABLE_name=upper('table_name') and owner='user_name' and column_name='col_name'

这段代码虽然短，**但是它有四个坑**，其中有三个都跟表名有关。

**1、表名是固定写法：**  
这里面的data\_type、all\_tab\_columns是固定写法，我觉得可能是在建表后，数据库中就已经有了一系列的表，用来存储整个数据库的表结构、表描述、字段类型等信息，所以你要按规则来写，自己瞎改改只会越改越错。

**2、表名的写作规范：**  
如果不是当前用户下的表，表名不能写在table\_name里面，如果写了虽然不报错，但是会没有任何结果。比如平时取某个表中的所有数据，你可以直接写从user\_name.table\_name表中取数，但是这个里面不能这么写，你可以写

select * from user_tab_columns TABLE_name=upper('table_name') and owner='user_name'

也可以像上面那样从all\_tab\_columns里面查找，但是不能写

select * from all_tab_columns TABLE_name=upper('user_name.table_name')

这样写虽然不报错，但是也不会有任何结果，因为就没有这样名字的表。

**3、关于from后面接什么表名的问题：**  
因为都是固定表名，所以要弄明白。如果是从一个表里面直接取它所有列的信息，写from cols，如果是直接取某个用户的所有表的列信息，写from user\_tab\_columns，但是前提是你已经在当前用户下了，如果是所有的表的所有列的信息，写from all\_tab\_columns,如果是dba的，当然是写from dba\_tab\_columns，如果这里的表名写错了，就会报错说没有这个表。

**4、大小写问题：**  
因为Oracle是大小写都可以的，但是在查询表信息写条件时，必须要把表名和列名都大写，也就是写到where子句里的都要大写。最保险的做法是，where子句中的所有内容，都加上upper函数，保证大写，否则会报错，或者是没有任何正确结果返回。

**补充问题**  
虽然这里只给出了字段类型是用data\_type来取，但其实还有很多表信息是可以获得的，比如表的描述，表的所有列名。这个如果用select \* from就可以查看所有可以获取的信息，然后select你想要的字段就可以了，比如列名就是column\_name。