ORACLE常见数据类型详解

最新推荐文章于 2024-07-25 16:26:05 发布

winter2summer

最新推荐文章于 2024-07-25 16:26:05 发布

阅读量602

点赞数

分类专栏： oracle

oracle 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

1、字符类型
• CHAR：一个定长字符串，当位数不足自动用空格填充来达到其最大长度。如非NULL的CHAR(12)总是包含12字节信息。CHAR字段最多可以存储2,000字节的
信息。
• VARCHAR2：目前这也是VARCHAR 的同义词。这是一个变长字符串，与CHAR 类型不同，它不会用空格填充至最大长度。VARCHAR2(12)可能包含0～

12字节的信息。VARCHAR2最多可以存储4,000 字节的信息。

CHAR和VARCHAR2的比较
CHAR(4) “A“ 实际在数据库中存储为"A “
“ABCDE”超长报错
VARCHAR2(4) “A” 存储的还是“A”
“ABCDE”超长报错
汉字：每个汉字占多少字节，要看具体的编码方式，如UTF-8（1-3字节）、
GB2312（2字节）、GBK（2字节）、GB18030（1、2、4字节）

2、数字类型

• NUMBER：该数据类型能存储精度最多达38位的数字。每个数存储在一个变长字段中，其长度在0～22字节之间。Oracle的NUMBER类型精度很高，远远高于许多编程语言中常规的FLOAT和DOUBLE类型。

NUMBER( p,s ) p表示精度（总长度） s表示小数位置且四舍五入

NUMBER(10,3) 10是总长度，3是小数位数的长度
123.456
123.4567 ：将存储为123.457
12345679.899 ：精度超长了，10是总长度，3是小数位，整数位为10-3=7位
NUMBER(10)==NUMBER(10,0) Java.lang.Integer
NUMBER(19)==NUMBER(19,0) java.lang.Long

3、日期类型

• DATE：一个7字节的定宽日期/时间数据类型。其中总包含7个属性，包括：世纪、世纪中哪一年、月份、月中的哪一天、小时、分钟和秒。

• TIMESTAMP：一个7 字节或12.字节的定宽日期/时间数据类型。它与DATE 数据类型不同，因为TIMESTAMP 可以包含小数秒（fractional second）；带小数秒的TIMESTAMP 在小数点右边最多可以保留9位。

4、二进制及大文本数据

• BLOB： (binary large object)在Oracle9i及以前的版本中，这种数据类型允许存储最多4GB的数据，在Oracle 10g及以后的版本中允许存储最多（4GB）×（数据库块大小）字节的数据。BLOB包含不需要进行字符集转换的“二进制“数据，如果要存储电子表格、字处理文档、图像文件等就很适合采用

• CLOB：（Character Large Object）在Oracle9i及以前的版本中，这种数据类型允许存储最多4GB的数据，在Oracle 10g及以后的版本中允许存储最多（4GB）×（数据库块大小）字节的数据。CLOB包含要进行字符集转换的信息。这种数据类型很适合存储纯文本信息。

Oracle用变长的方式存储存储number数据，每个number值是以科学计数法的形式存储在数据库中的：一个字节用来存储符号/指数，多达20个字节来存储尾数。然而存储的number数据的最大精度为38位。

例如，412在数据库中的存储形式为4.12 X 10^2，用一个字节来存储指数2，用另外两个字节来存储数字4,1,2，所以412在数据库中共占用3个字节。

对于符号/指数部分，用第8位表示数值的符号，0表示负数，1表示正数。用剩余的7位表示指数，但是在计算时，必须将指数的值扣除64后，才是真正的指数值。

例如，如果符号/指数部分的值为223 ，它的二进制表示为11011111。它的第8为为1，这表示数值为正数，为了决定指数值，去掉第8位，剩下的7位1011111的值为95，扣除64后，得到31，这是真正的指数值。\

如果符号/指数部分的值为100，它的二进制表示为01100100。它的第8为为0，这表示数值为负数，将01100100 取补，得到10011011，去掉第8位，剩下的7位0011011的值为27，扣除64后，得到-37，这是真正的指数值。

对于尾数部分，每个值表示的值是100进制，并且为了避免二进制的0，在尾数部分的每个字节上都自动加1，所以每个尾数字节表示的数值范围为1－100，实际上，它们表示的真正的数值范围为0－99。如果是一个负数，则尾数部分会以102结束。

观察number存储的最好的工具是dump函数，它可以将number在数据库中的内部存储值显现给我们：

如果123456.789.的dump值为：

DUMP(C1)

------------------------------------------

Typ=2 Len=6: 195,13,35,57,79,91

这表示在数据库内部用6个字节表示123456.789。每个字节如下

第1个字节 = 195 --> 表示为二进制为11000011

表示数值为正数，指数的值为67－64＝3

第2个字节 = 13

因为表示尾数，减1后得到12。

第3个字节= 35

因为表示尾数，减1后得到34

第4个字节= 57

因为表示尾数，减1后得到56.

第5个字节= 79

因为表示尾数，减1后得到78.

第6个字节= 91

因为表示尾数，减1后得到90.

我们得到指数为3，尾数部分的数值是基于100的，所以表示的数值为：

12 x (100 e 2) + 34 x (100 e 1) + 56 x (100 e 0) + 78 x (100 e -1) +

90 x (100 e -2) = 123456.789

如果 -123456.789.的dump值为：

DUMP(C1)

------------------------------------------

Typ=2 Len=7: 60,89,67,45,23,11,102

这表示在数据库内部用6个字节表示123456.789。每个字节如下：

注意：该数值以102结束，表示该数值是一个负数。

第1个字节= 60 -->表示为二进制为00111100

第8位为0，表示数值是一个负数。该值取反后为11000011，去掉第8位，表示67，扣除64后，得到3，该指数的真正值为3。

第2个字节= 89

因为表示尾数，减1后得到88，因为为负数，所以真正的数值为100 – 88得12

第3个字节= 67

因为表示尾数，减1后得到66，因为为负数，所以真正的数值为100 – 66得34

第4个字节= 45

因为表示尾数，减1后得到44，因为为负数，所以真正的数值为100 – 44得56

第5个字节= 23

因为表示尾数，减1后得到22，因为为负数，所以真正的数值为100 – 22得78

第6个字节= 11

因为表示尾数，减1后得到10，因为为负数，所以真正的数值为100 – 10得90

第7个字节= 102

这表示这是最后一个字节

我们得到指数为3，尾数部分的数值是基于100的，所以表示的数值为：

12 x (100 e 2) + 34 x (100 e 1) + 56 x (100 e 0) + 78 x (100 e -1) +

90 x (100 e -2) = 123456.789

因为这是一个负数，所以真正得数值为-123456.789

当然也可以根据公式来推算数值占用得空间：

数值number(p,s)占用得空间为：

length = floor [(p+1)/2] + 1

备注：如果该数值为负数，需要再加一个字节。

所以你的NUMBER（5,2)占用的字节数为：floor [(p+1)/2] + 1 ＝ floor [(5+1)/2] + 1 ＝ 3 ＋ 1 ＝ 4

所以如果NUMBER（5,2)表示整数，占用4个字节，如果表示负数，占用5个字节。

oracle数据类型看起来非常简单，但用起来会发现有许多知识点，本文是我对ORACLE日期数据类型的一些整理，都是开发入门资料，与大家分享：
注：由于INTERVAL及TIME ZONE实际用得比较少，所以本文内容未涉及这两个方面。

1、常用日期型数据类型
1.1、DATE
这是ORACLE最常用的日期类型，它可以保存日期和时间，常用日期处理都可以采用这种类型。DATE表示的日期范围可以是公元前4712年1月1日至公元9999年12月31日
date类型在数据库中的存储固定为7个字节，格式为：
第1字节：世纪+100
第2字节：年
第3字节：月
第4字节：天
第5字节：小时+1
第6字节：分+1
第7字节：秒+1

1.2、TIMESTAMP(p)
这也是ORACLE常用的日期类型，它与date的区别是不仅可以保存日期和时间，还能保存小数秒，小数位数可以指定为0-9，默认为6位，所以最高精度可以到ns(纳秒)，数据库内部用7或者11个字节存储，如果精度为0，则用7字节存储，与date类型功能相同，如果精度大于0则用11字节存储。
格式为：
第1字节：世纪+100
第2字节：年
第3字节：月
第4字节：天
第5字节：小时+1
第6字节：分+1
第7字节：秒+1
第8-11字节：纳秒，采用4个字节存储，内部运算类型为整形

注：TIMESTAMP日期类型如果与数值进行加减运算会自动转换为DATE型，也就是说小数秒会自动去除。

1.3、DATE与TIMESTAMP类型内部存储验证

 1 create table T

 2 (

 3   C1 DATE,

 4   C2 TIMESTAMP(9)

 5 );

 6 

 7 insert into t(c1,c2) values(date'2010-2-12',timestamp'2010-2-12 13:24:52.234123211');

 8 insert into t(c1,c2) values(

 9        to_date('2010-2-12 10:20:30','YYYY-MM-DD HH24:MI:SS'),

10        to_timestamp('2010-2-12 13:24:52.123456','YYYY-MM-DD HH24:MI:SS.FF6')

11 );

12 

13 SQL> select c1,dump(c1) c1_d,c2,dump(c2) c2_d from t;

C1                      C1_D                                    C2                                      C2_D
------------------------ -------------------------------------------------------------------------------------------------------------------------------------
2010-2-12               Typ=12 Len=7:120,110,2,12,1,1,1        12-FEB-10 01.24.52.234123211PM         Typ=180 Len=11: 120,110,2,12,14,25,53,13,244,111,203
2010-2-12 上午 10:20:30 Typ=12 Len=7:120,110,2,12,11,21,31     12-FEB-10 01.24.52.123456000PM         Typ=180 Len=11: 120,110,2,12,14,25,53,7,91,202,0

以下是为了测试是为了验证TIMESTAMP的小数位存储算法：

1 SQL> select c2,dump(c2,16) c2_d16 from t;

C2                                      C2_D16
------------------------------------------------------------------------------------------------------------------------
12-FEB-10 01.24.52.234123211PM         Typ=180 Len=11: 78,6e,2,c,e,19,35,d,f4,6f,cb
12-FEB-10 01.24.52.123456000PM         Typ=180 Len=11: 78,6e,2,c,e,19,35,7,5b,ca,0

SQL> select to_number('0df46fcb','xxxxxxxx') mydata1,to_number('075bca00','xxxxxxxx') mydata2 from dual;

MYDATA1 MYDATA2
---------- ----------
234123211 123456000

2、常见问题
2.1、如何取当前时间
sysdate--返回当前系统日期和时间，精确到秒
systimestamp--返回当前系统日期和时间，精确到毫秒
2.2、如何进行日期运算
日期型数据可以与数值加减得到新的日期，加减数值单位为天
sysdate+1--取明天的当前时间
sysdate-1/24--取当前时间的前一个小时

SQL> select sysdate d1,sysdate+1 d2,sysdate-1/24 d3 from dual;

D1 D2 D3
------------------------ ------------------------------------------------
2010-5-13 下午 10:55:16 2010-5-14 下午10:55:16 2010-5-13 下午 09:55:16

2.3、如何求两个日期的间隔时间
可以直接把两个日期相减，返回的单位为天，小时及分秒会换算成小数

SQL> select date'2012-01-01'-sysdate from dual;

DATE'2012-01-01'-SYSDATE
------------------------
597.046030092593

2.4、如何将日期转字符
to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')
2.5、如何将字符转日期
to_date('2010-02-24 15:01:54','YYYY-MM-DD HH24:MI:SS')
to_timestamp('1999-12-01 11:00:00.123456','YYYY-MM-DDHH:MI:SS.FF6')

3、常用日期函数
3.1、TO_CHAR(DATE,FORMATSTR)--格式化日期成字符

SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') d1 from dual;

D1
------------------------
2010-05-13 22:56:38

TO_CHAR的其它用法示例

1 SQL> SELECT TO_CHAR(date '2010-02-12', 'D') week_dayth,--周第几天(1-7)，星期天=1，星期一=2,星期二=3，星期三=4,星期四=5，星期五=6,星期六=7
2 TO_CHAR(date '2010-02-12', 'DD') month_dayth,--月第几天
3 TO_CHAR(date '2010-02-12', 'DDD') year_dayth,--年第几天
4 TO_CHAR(date '2010-02-12', 'DAY') weekdayname,--英文星期名
5 _CHAR(date '2010-02-12', 'w') month_weekth,--月第几周(0-4)
6 TO_CHAR(date '2010-02-12', 'ww') year_weekth--年第几周(0-53)
7 FROM DUAL;

WEEK_DAYTH MONTH_DAYTH YEAR_DAYTH WEEKDAYNAME MONTH_WEEKTHYEAR_WEEKTH
---------- ----------- ---------- ----------- -----------------------
6 12 043 FRIDAY 2 07

3.2、TO_DATE(CHAR,FORMATSTR) --将字符转换成日期
to_date('2010-02-24 15:01:54','YYYY-MM-DD HH24:MI:SS')
格式备注：
HH表示12小时进制，HH24表示采用24小时进制，MM表示月份，MI表示分钟。

3.3、TRUNC(DATE)--返回DATE的日期部分，时间为0点0分0秒

SQL> select sysdate d1,trunc(sysdate) d2 from dual;

D1 D2
------------------------ ------------------------
2010-5-13 下午 10:59:18 2010-5-13

3.4、EXTRACT(DATA FROMDATEVALUE)--返回DATE的某一部份内容
如果DATEVALUE为DATE类型，则DATA可以是(YEAR、MONTH、DAY)
如果DATEVALUE为TIMESTAMP类型，则DATA可以是(YEAR、MONTH,DAY、HOUR、MINUTE、SECOND)

SQL> select sysdate d1,EXTRACT(YEAR FROM sysdate) thisyear,EXTRACT(MINUTE FROM systimestamp) thism from dual;

D1 THISYEAR THISM
------------------------ ---------- ----------
2010-5-13 下午11:05:06 2010 5
3.5、ADD_MONTHS(DATE,MONTHS) --在DATE增加月份得到新日期

ADD_MONTHS(sysdate,3) --当前日期加3个月
ADD_MONTHS是一个比较有意思的函数，它会自动处理大小月及闰月，如下所示

1   SQL> select ADD_MONTHS(date '2010-2-12', 1),

2   ADD_MONTHS(date '2010-2-27', 1),

3   ADD_MONTHS(date '2010-2-28', 1),

4   ADD_MONTHS(date '2010-1-31', 1)

5   from dual

6   ;

ADD_MONTHS(DATE'2010-2-12',1) ADD_MONTHS(DATE'2010-2-27',1)ADD_MONTHS(DATE'2010-2-28',1) ADD_MONTHS(DATE'2010-1-31',1)
----------------------------- ---------------------------------------------------------- -----------------------------
2010-3-12 2010-3-27 2010-3-31 2010-2-28

3.6、LAST_DAY(DATE)--返回日期所在月份的最后一天日期

SQL> select LAST_DAY(date '2010-2-12') from dual;

LAST_DAY(DATE'2010-2-12')
-------------------------
2010-2-28

3.7、NEXT_DAY(DATE,CHAR)--从给定日期开始返回下个CHAR指定星期的日期

SQL> SELECT NEXT_DAY(date'2010-2-21', 'MONDAY') NEXTDAY1,NEXT_DAY(date'2010-2-22', 'MONDAY') NEXTDAY2 FROM DUAL;

NEXTDAY1 NEXTDAY2
----------- -----------
2010-2-22 2010-3-1

TO_YMINTERVAL(CHAR)--返回[年-月]格式构成的时间间隔，一般用于日期加减运算

3.8、TO_DSINTERVAL(CHAR)--返回[天时:分:秒]格式构成的时间间隔，一般用于日期加减运算

SQL> select date'2010-2-12'+TO_YMINTERVAL('01-02') newdate from dual;

NEWDATE
------------------------------
2011-4-12

3.9、NUMTOYMINTERVAL(N,CHAR)--返回CHAR中指定单位的时间间隔数值，一般用于日期加减运算
char可以为YEAR,MONTH

1 SQL> select date '2010-2-12' + NUMTOYMINTERVAL(1, 'year') newdate1,

2 date '2010-2-12' + NUMTOYMINTERVAL(1, 'month') newdate2

3 from dual;

NEWDATE1 NEWDATE2
----------- -----------
2011-2-12 2010-3-12

3.10、NUMTODSINTERVAL(N,CHAR)--返回CHAR中指定单位的时间间隔数值，一般用于日期加减运算
char可以为DAY,HOUR,MINUTE,SECOND

1 SQL> select date '2010-2-12' + NUMTODSINTERVAL(1, 'DAY') newdate1,

2 date '2010-2-12' + NUMTODSINTERVAL(1, 'HOUR') newdate2

3 from dual;

NEWDATE1 NEWDATE2
----------- ------------------------
2010-2-13 2010-2-12 上午01:00:00

winter2summer

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录