Oracle 数据库“time”数据类型有吗?–关于对Oracle数据类型研究

前言

Oracle里有“time”数据类型吗?绝大多数熟悉oracle的人都会说没有,但事实就是如此么?
下面这个图一定会让不少人大跌眼镜
img-ExtqT2DZ-1655564231189

分析思路

前菜

oracle有很多东西不会写在文档里,需要自己去发掘,但是好在oracle有很多代码是用可见的plsql语言,所以可以凭借这些能看到的代码以及逻辑分析来了解oracle的“习惯做法”。

比如函数,如果只看官方文档,对比DBA_OBJECTS里的sys用户下的function,会发现官方文档里缺了很多函数。另外也有相当一部分官方文档里有的函数,在DBA_OBJECTS里也没有,而工具的自动补全代码功能却可以把函数进行补全,所以自然会去思考,这是个什么机制?

select * from dba_objects h where h.object_type='FUNCTION' AND OWNER='SYS';
select * from dba_objects h where h.object_name='NVL';

dba_objects里没有对应的名称,则可能这些名称可能是在dba_source里有

select * from dba_source h where lower(h.text) like '%function%nvl(%' and owner='SYS';

于是我在sys用户下找到了standard这个package,里面就有很多特熟悉的函数,而且也符合我之前的一个理解:

在同一个schema下,只有包里的函数才能重名,而每一个函数都有确定的参数类型及参数个数

如果nvl这种支持多种类型的函数在Oracle里只是一个函数的话,自然不符合这个逻辑,于是自然就分析出一个机制:

使用时能够不加包名,且可以传入不同类型参数的同名函数,都定义在standard这个包里。

正餐

然后再回到type,先按照通常建表的方式,来输入一段sql,会发现工具自动提示了”time”这个关键词和”time with tz”这个type,当然这两种其实都会报错的。

img-a3wVraVz-1655564231191
image-1655557668186
但这就说明oracle不支持time类型了么?

如果问oracle里支持哪些类型,先看官方文档没问题,但由于前面的经验,一定会怀疑文档写得不全,就会去查dba_types,太多了,大概扫一眼,加个owner为空的条件,就是oracle里的所有“基本”数据类型,除了发现的确有”time”这个类型外,还发现有些类型的名称和实际使用的不完全一致,比如常见的”PLS_INTEGER”在视图里的名称和CODE均为”PL/SQL PLS INTEGER”。

select * from dba_types where owner is null;

我们已知,对于PLS_INTEGER类型,只能用于存储过程里,不能作为表字段使用,这是因为oracle对其进行了限制。可以大胆猜测”PLS”开头的内置类型,均不可用于建表
image-1655557905392
但是,你会发现,这个报错,竟然和上面time类型的报错不一样。

联想到之前的standard包的机制,就会想使用的名称是不是也定义到这个包里去了,打开一看果然。

  type TIME is new DATE_BASE;
  type TIMESTAMP is new DATE_BASE;
  type "TIME WITH TIME ZONE" is new DATE_BASE;
  type "TIMESTAMP WITH TIME ZONE" is new DATE_BASE;
  type "INTERVAL YEAR TO MONTH" is new DATE_BASE;
  type "INTERVAL DAY TO SECOND" is new DATE_BASE;

  SUBTYPE TIME_UNCONSTRAINED IS TIME(9);
  SUBTYPE TIME_TZ_UNCONSTRAINED IS TIME(9) WITH TIME ZONE;
  SUBTYPE TIMESTAMP_UNCONSTRAINED IS TIMESTAMP(9);
  SUBTYPE TIMESTAMP_TZ_UNCONSTRAINED IS TIMESTAMP(9) WITH TIME ZONE;
  SUBTYPE YMINTERVAL_UNCONSTRAINED IS INTERVAL YEAR(9) TO MONTH;
  SUBTYPE DSINTERVAL_UNCONSTRAINED IS INTERVAL DAY(9) TO SECOND (9);

本文标题里的time类型,在oracle里的确有,但使用存在限制,而且这个限制和pls类型的表现还不一样,建表时的报错不是无效数据类型,而是提示少了属性或关键词,但按照package里对time类型的用法,也是无法建表的。

create table test_time_table(a time(9));

然后,换个方法继续尝试。
建表不能用,先搞个函数return试试

create or replace function test_time_func return time is
begin
  return '21:19:00';
end;
/
select test_time_func from dual;

发现竟然可以被select !!
要知道oracle里虽然有boolean类型,也是不能被select的,这更加说明了time类型和那些pls类型是不一样的。

这个函数我是直接让它隐式转换了,也没有报错,那么接下来直接尝试cast

select cast('12:34:56' as time) from dual


能查!,对它to_char

select to_char(cast('12:34:56' as time),'hh24:mi:ss') from dual

没毛病了,这妥妥的time类型,连to_char格式化都能用!

于是乎,尝试create table as,看看它这个类型到底怎么用的

不行,那么尝试create view as ,因为view也可以查看字段类型

看上去貌似是 time(0)(7) ?
先再来一个create table as select * from 视图
image-1655559334559

到目前阶段,基本可以判定,在dba_types里owner为空的类型,以能用的地方作为区分,至少可以分3类,

  1. 所有地方能用的(代表:varchar2/number/date)
  2. 仅在plsql中能用的(代表:boolean/pls_integer)
  3. 只有create table里不能用的,但可以作为列被select、可以作为视图字段类型、可以在plsql中使用(代表:time)

然后继续研究这个time后面的两个数字的含义。
回到standard包,看到这两行

  SUBTYPE TIME_UNCONSTRAINED IS TIME(9);
  SUBTYPE TIME_TZ_UNCONSTRAINED IS TIME(9) WITH TIME ZONE;

我们尝试用建view的方式来查看用这两个字段类型建的字段长什么样
image-1655559902113
image-1655560015331
第一个数字的范围为 0-9.
可是,0到9的秒数都是完整的,联想到之前曾见过同事讨论timestamp(6)和timestamp(9),自然就联想到了类似的含义,即这个数字表示秒后小数点多少位,但秒后咋格式化来着我忘了,就查了下nls_database_parameters,这一查,又发现个之前没注意的参数
image-1655560400757

这里time明明白白摆这了。。。
下面这个sql验证了我的猜想

select to_char(cast('12:34:56.987654321' as time(5)),'hh24:mi:ss XFF') from dual;

接下来就是第2个数字
image-1655561505539
貌似不对,没有出现7,先dump看看

找到7了,原来第2个数字7,表示的是time类型数据所占的字节数,而且这个长度不会由于time精度而变化,始终是固定的7位。至于带时区的time,则为固定的9位
image-1655562262671

餐后甜点

time类型的内容基本就研究得差不多了,接下来回到standard包,看一下最前面的一些代码

type DATE is DATE_BASE;

  type NUMBER is NUMBER_BASE;
  subtype FLOAT is NUMBER; -- NUMBER(126)
  subtype REAL is FLOAT; 

可以看到有 *_base 这样的,搜整个包,可以得到以下6种

  • DATE_BASE
  • NUMBER_BASE
  • CHAR_BASE
  • BLOB_BASE
  • CLOB_BASE
  • BFILE_BASE

“base”意思为基本,也就是说,oracle将它所使用的所有基本类型,分成了这6大类。
然后 “type NUMBER is NUMBER_BASE”即为NUMBER是属于 NUMBER_BASE之一;
“subtype FLOAT is NUMBER;”即FLOAT为NUMBER的子类型(这里需要注意,子类型的二进制存储值,并不一定就和原类型一致,比如 “subtype BINARY_FLOAT is NUMBER;”)。

继续看这个包,会对oracle有更深入的了解,当然本篇就不继续展开了,留一点空间让读者们来探索吧。

总结

虽然本文标题为”time”类型,且文章主线也是围绕“time”类型展开,但更多的是作者本人敢于对“权威”的质疑,以及用逻辑思维结合实验对未知事物的深挖的记录。希望读者们学到的不仅仅是“oracle里有time类型”这一结论,还希望读者们在以后的工作实践中,能具备深挖问题的探索精神及逻辑思维能力。


数据运维技术 » Oracle 数据库“time”数据类型有吗?–关于对Oracle数据类型研究