前言
Oracle里有“time”數(shù)據(jù)類型嗎？絕大多數(shù)熟悉oracle的人都會(huì)說(shuō)沒有，但事實(shí)就是如此么？
下面這個(gè)圖一定會(huì)讓不少人大跌眼鏡
分析思路
前菜
oracle有很多東西不會(huì)寫在文檔里，需要自己去發(fā)掘，但是好在oracle有很多代碼是用可見的plsql語(yǔ)言，所以可以憑借這些能看到的代碼以及邏輯分析來(lái)了解oracle的“習(xí)慣做法”。
比如函數(shù)，如果只看官方文檔，對(duì)比DBA_OBJECTS里的sys用戶下的function,會(huì)發(fā)現(xiàn)官方文檔里缺了很多函數(shù)。另外也有相當(dāng)一部分官方文檔里有的函數(shù)，在DBA_OBJECTS里也沒有，而工具的自動(dòng)補(bǔ)全代碼功能卻可以把函數(shù)進(jìn)行補(bǔ)全，所以自然會(huì)去思考，這是個(gè)什么機(jī)制？
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里沒有對(duì)應(yīng)的名稱，則可能這些名稱可能是在dba_source里有
select * from dba_source h where lower(h.text) like '%function%nvl(%' and owner='SYS';
于是我在sys用戶下找到了standard這個(gè)package，里面就有很多特熟悉的函數(shù)，而且也符合我之前的一個(gè)理解：
在同一個(gè)schema下，只有包里的函數(shù)才能重名，而每一個(gè)函數(shù)都有確定的參數(shù)類型及參數(shù)個(gè)數(shù)
如果nvl這種支持多種類型的函數(shù)在Oracle里只是一個(gè)函數(shù)的話，自然不符合這個(gè)邏輯，于是自然就分析出一個(gè)機(jī)制：
使用時(shí)能夠不加包名，且可以傳入不同類型參數(shù)的同名函數(shù)，都定義在standard這個(gè)包里。
正餐
然后再回到type,先按照通常建表的方式，來(lái)輸入一段sql，會(huì)發(fā)現(xiàn)工具自動(dòng)提示了"time"這個(gè)關(guān)鍵詞和"time with tz"這個(gè)type，當(dāng)然這兩種其實(shí)都會(huì)報(bào)錯(cuò)的。
但這就說(shuō)明oracle不支持time類型了么？
如果問(wèn)oracle里支持哪些類型，先看官方文檔沒問(wèn)題，但由于前面的經(jīng)驗(yàn)，一定會(huì)懷疑文檔寫得不全，就會(huì)去查dba_types，太多了，大概掃一眼，加個(gè)owner為空的條件，就是oracle里的所有“基本”數(shù)據(jù)類型，除了發(fā)現(xiàn)的確有"time"這個(gè)類型外，還發(fā)現(xiàn)有些類型的名稱和實(shí)際使用的不完全一致，比如常見的"PLS_INTEGER"在視圖里的名稱和CODE均為"PL/SQL PLS INTEGER"。
select * from dba_types where owner is null;
我們已知，對(duì)于PLS_INTEGER類型，只能用于存儲(chǔ)過(guò)程里，不能作為表字段使用，這是因?yàn)閛racle對(duì)其進(jìn)行了限制。可以大膽猜測(cè)"PLS"開頭的內(nèi)置類型，均不可用于建表
但是，你會(huì)發(fā)現(xiàn)，這個(gè)報(bào)錯(cuò)，竟然和上面time類型的報(bào)錯(cuò)不一樣。
聯(lián)想到之前的standard包的機(jī)制，就會(huì)想使用的名稱是不是也定義到這個(gè)包里去了，打開一看果然。
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);
本文標(biāo)題里的time類型，在oracle里的確有，但使用存在限制，而且這個(gè)限制和pls類型的表現(xiàn)還不一樣，建表時(shí)的報(bào)錯(cuò)不是無(wú)效數(shù)據(jù)類型，而是提示少了屬性或關(guān)鍵詞，但按照package里對(duì)time類型的用法，也是無(wú)法建表的。
create table test_time_table(a time(9));
然后，換個(gè)方法繼續(xù)嘗試。
建表不能用，先搞個(gè)函數(shù)return試試
create or replace function test_time_func return time is
begin
return '21:19:00';
end;
/
select test_time_func from dual;
發(fā)現(xiàn)竟然可以被select ！！
要知道oracle里雖然有boolean類型，也是不能被select的，這更加說(shuō)明了time類型和那些pls類型是不一樣的。
這個(gè)函數(shù)我是直接讓它隱式轉(zhuǎn)換了，也沒有報(bào)錯(cuò)，那么接下來(lái)直接嘗試cast
select cast('12:34:56' as time) from dual
能查！，對(duì)它to_char
select to_char(cast('12:34:56' as time),'hh24:mi:ss') from dual
沒毛病了，這妥妥的time類型，連to_char格式化都能用！
于是乎，嘗試create table as,看看它這個(gè)類型到底怎么用的
不行，那么嘗試create view as ，因?yàn)関iew也可以查看字段類型
看上去貌似是 time(0)(7) ？
先再來(lái)一個(gè)create table as select * from 視圖
到目前階段，基本可以判定，在dba_types里owner為空的類型，以能用的地方作為區(qū)分，至少可以分3類，
- 所有地方能用的（代表：varchar2/number/date）
- 僅在plsql中能用的（代表：boolean/pls_integer）
- 只有create table里不能用的,但可以作為列被select、可以作為視圖字段類型、可以在plsql中使用（代表：time）
然后繼續(xù)研究這個(gè)time后面的兩個(gè)數(shù)字的含義。
回到standard包，看到這兩行
SUBTYPE TIME_UNCONSTRAINED IS TIME(9);
SUBTYPE TIME_TZ_UNCONSTRAINED IS TIME(9) WITH TIME ZONE;
我們嘗試用建view的方式來(lái)查看用這兩個(gè)字段類型建的字段長(zhǎng)什么樣
第一個(gè)數(shù)字的范圍為 0-9.
可是，0到9的秒數(shù)都是完整的，聯(lián)想到之前曾見過(guò)同事討論timestamp(6)和timestamp(9)，自然就聯(lián)想到了類似的含義，即這個(gè)數(shù)字表示秒后小數(shù)點(diǎn)多少位，但秒后咋格式化來(lái)著我忘了，就查了下nls_database_parameters,這一查，又發(fā)現(xiàn)個(gè)之前沒注意的參數(shù)
這里time明明白白擺這了。。。
下面這個(gè)sql驗(yàn)證了我的猜想
select to_char(cast('12:34:56.987654321' as time(5)),'hh24:mi:ss XFF') from dual;
接下來(lái)就是第2個(gè)數(shù)字
貌似不對(duì)，沒有出現(xiàn)7，先dump看看
找到7了，原來(lái)第2個(gè)數(shù)字7，表示的是time類型數(shù)據(jù)所占的字節(jié)數(shù)，而且這個(gè)長(zhǎng)度不會(huì)由于time精度而變化，始終是固定的7位。至于帶時(shí)區(qū)的time，則為固定的9位
餐后甜點(diǎn)
time類型的內(nèi)容基本就研究得差不多了，接下來(lái)回到standard包，看一下最前面的一些代碼
type DATE is DATE_BASE;
type NUMBER is NUMBER_BASE;
subtype FLOAT is NUMBER; -- NUMBER(126)
subtype REAL is FLOAT;
可以看到有 *_base 這樣的，搜整個(gè)包，可以得到以下6種
- DATE_BASE
- NUMBER_BASE
- CHAR_BASE
- BLOB_BASE
- CLOB_BASE
- BFILE_BASE
"base"意思為基本，也就是說(shuō)，oracle將它所使用的所有基本類型，分成了這6大類。
然后 "type NUMBER is NUMBER_BASE"即為NUMBER是屬于 NUMBER_BASE之一；
"subtype FLOAT is NUMBER;"即FLOAT為NUMBER的子類型（這里需要注意，子類型的二進(jìn)制存儲(chǔ)值，并不一定就和原類型一致，比如 “subtype BINARY_FLOAT is NUMBER;”）。
繼續(xù)看這個(gè)包，會(huì)對(duì)oracle有更深入的了解，當(dāng)然本篇就不繼續(xù)展開了，留一點(diǎn)空間讓讀者們來(lái)探索吧。
總結(jié)
雖然本文標(biāo)題為"time"類型，且文章主線也是圍繞“time”類型展開，但更多的是作者本人敢于對(duì)“權(quán)威”的質(zhì)疑，以及用邏輯思維結(jié)合實(shí)驗(yàn)對(duì)未知事物的深挖的記錄。希望讀者們學(xué)到的不僅僅是“oracle里有time類型”這一結(jié)論，還希望讀者們?cè)谝院蟮墓ぷ鲗?shí)踐中，能具備深挖問(wèn)題的探索精神及邏輯思維能力。
- 本文作者： DarkAthena
- 本文鏈接： https://www.darkathena.top/archives/oracle-time-data-type-is-exists
- 版權(quán)聲明： 本博客所有文章除特別聲明外，均采用CC BY-NC-SA 3.0 許可協(xié)議。轉(zhuǎn)載請(qǐng)注明出處！
