前言
Oracle里有“time”数据类型吗?绝大多数熟悉oracle的人都会说没有,但事实就是如此么?
下面这个图一定会让不少人大跌眼镜
分析思路
前菜
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,当然这两种其实都会报错的。
但这就说明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"开头的内置类型,均不可用于建表
但是,你会发现,这个报错,竟然和上面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 视图
到目前阶段,基本可以判定,在dba_types里owner为空的类型,以能用的地方作为区分,至少可以分3类,
- 所有地方能用的(代表:varchar2/number/date)
- 仅在plsql中能用的(代表:boolean/pls_integer)
- 只有create table里不能用的,但可以作为列被select、可以作为视图字段类型、可以在plsql中使用(代表:time)
然后继续研究这个time后面的两个数字的含义。
回到standard包,看到这两行
SUBTYPE TIME_UNCONSTRAINED IS TIME(9);
SUBTYPE TIME_TZ_UNCONSTRAINED IS TIME(9) WITH TIME ZONE;
我们尝试用建view的方式来查看用这两个字段类型建的字段长什么样
第一个数字的范围为 0-9.
可是,0到9的秒数都是完整的,联想到之前曾见过同事讨论timestamp(6)和timestamp(9),自然就联想到了类似的含义,即这个数字表示秒后小数点多少位,但秒后咋格式化来着我忘了,就查了下nls_database_parameters,这一查,又发现个之前没注意的参数
这里time明明白白摆这了。。。
下面这个sql验证了我的猜想
select to_char(cast('12:34:56.987654321' as time(5)),'hh24:mi:ss XFF') from dual;
接下来就是第2个数字
貌似不对,没有出现7,先dump看看
找到7了,原来第2个数字7,表示的是time类型数据所占的字节数,而且这个长度不会由于time精度而变化,始终是固定的7位。至于带时区的time,则为固定的9位
餐后甜点
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类型”这一结论,还希望读者们在以后的工作实践中,能具备深挖问题的探索精神及逻辑思维能力。