查看常用数据类型长度

1 字符类型

字符类型与字符集还有有关的。在GBK中，一个汉字对应2个字节，在UTF8中一个汉字对应3个字节。在这里统一使用AL32UTF8字符集进行测试：

SQL> select dump('112') from dual;
 
DUMP('112')
--------------------------------------------------------------------------------
Typ=96 Len=3: 49,49,50
 
SQL> select dump('你') from dual;
 
DUMP('你')
--------------------------------------------------------------------------------
Typ=96 Len=3: 228,189,160
 
SQL> select dump('你1') from dual;
 
DUMP('你1')
--------------------------------------------------------------------------------
Typ=96 Len=4: 228,189,160,49

 

 

2 字数类型

 

SQL> select dump(112) from dual;
 
DUMP(112)
--------------------------------------------------------------------------------
Typ=2 Len=3: 194,2,13
 
SQL> select dump(1123) from dual;
 
DUMP(1123)
--------------------------------------------------------------------------------
Typ=2 Len=3: 194,12,24
 
SQL> select dump(11234) from dual;
 
DUMP(11234)
--------------------------------------------------------------------------------
Typ=2 Len=4: 195,2,13,35

注：数字类型的公式为floor((n+1)/2) +1

 

 

 

3 时间类型

 

SQL> select dump(to_date('2013-09','yyyy-mm')) from dual;
 
DUMP(TO_DATE('2013-09','YYYY-M
--------------------------------------------------------------------------------
Typ=13 Len=8: 221,7,9,1,0,0,0,0
 
SQL> select dump(to_date('2013-09-12','yyyy-mm-dd')) from dual;
 
DUMP(TO_DATE('2013-09-12','YYY
--------------------------------------------------------------------------------
Typ=13 Len=8: 221,7,9,12,0,0,0,0
 
SQL> select dump(to_date('2013-09-12 12:20:20','yyyy-mm-dd hh24:mi:ss')) from dual;
 
DUMP(TO_DATE('2013-09-1212:20:
--------------------------------------------------------------------------------
Typ=13 Len=8: 221,7,9,12,12,20,20,0
 
SQL>

 

 

注：date类型统一为8个字节

 

 timestamp时间类型：

 

SQL> select dump(to_timestamp('2013-09-10','yyyy-mm-dd')) from dual;
 
DUMP(TO_TIMESTAMP('2013-09-10'
--------------------------------------------------------------------------------
Typ=187 Len=20: 221,7,9,10,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0
 
SQL> select dump(to_timestamp('2013-09-10 12:30:30','yyyy-mm-dd hh24:mi:ss')) from dual;
 
DUMP(TO_TIMESTAMP('2013-09-101
--------------------------------------------------------------------------------
Typ=187 Len=20: 221,7,9,10,12,30,30,0,0,0,0,0,0,0,3,0,0,0,0,0
 

注：timestamp的时间类型为20个字节