Oracle中的数据类型

Oracle中的数据类型
Ø简介
本⽂主要来讨论 Oracle 中的数据类型，包括以下内容：
1.概念介绍
2.数值类型
3.字符类型
4.⽇期类型
5.⼤数据与⼆进制类型
6.列举常⽤类型的数字代码
1.概念介绍
在我们的程序中有各种数据类型，⽽在 Oracle 中也有很多种类型，其实每⼀种语⾔的数据类型都会跟数据库中的数据类型⼤致的对应起来。

⽐如：在 Oracle 中主要分为四⼤数据类型，即：数字类型、字符类型、⽇期类型，以及⼤数据与⼆进制类型，下⾯就来讨论着⼏种常⽤的数据类型。

2.数值类型
1)NUMBER(Type = 2)
number 类型是 Oracle 中常⽤的数据类型，即可以⽤于存储整数
整数，也可以存储⼩数
⼩数，是⼀个⾮常有意思的数据类型。

number 是⼀个可变长度
可变长度的数据类型，并且始终保持四舍五⼊
四舍五⼊的原则。

number 可以指定两个参数 number(p,s)：
l p表⽰有效位，从左边第⼀个⾮0的数字开始数，到结尾的长度，取值范围：1 ~ 38；
l s表⽰⼩数位，取值范围：-84 ~ 127。

注意：
注意：通常情况下（需要整数位时），应该 p > s，例如：
1.number(3,2) 只能存储：0.01 ~ 9.99 和 -0.01 ~ -9.99
2.number(3,3) 只能存储：0.001 ~ 0.999 和 -0.001 ~ -0.999
3.number(2,3) 只能存储：0.001 ~ 0.099 和 -0.001 ~ -0.099
n number 可以表⽰的数值范围：-1.0e-130 ~ 1.0e+126，占⽤空间为1 ~ 22 个字节。

下⾯看⼏个常见的例⼦：
1.number
create table t_number(num number);
insert into t_number values(56);
insert into t_number values(56.78);
select * from t_number;
执⾏以上语句，实际存储为56和56.78。

当不指定有效位和⼩数位时，number 可根据实际值⽽确定。

查看占⽤字节情况：select num, dump(num) from t_number;
56占⽤2个字节；⽽56.78占⽤3个字节
2.number(3)
create table t_number3(num number(3));
insert into t_number3 values(999);
insert into t_number3 values(28.56);
select * from t_number3;
执⾏以上语句，实际存储为999和29，表⽰存储为整数形式，⼩数位进⾏四舍五⼊。

number(3) 相当于 number(3,0)，取值范围为：-999~999
3.number(5,2)
create table t_number5_2(num number(5,2));
insert into t_number5_2 values(128.264);
insert into t_number5_2 values(128.265);
select * from t_number5_2;
执⾏以上语句，实际存储为128.26和128.27，当⼩数位⼤于指定范围时，将进⾏四舍五⼊。

n更多例⼦
范围插⼊值1结果插⼊值2结果
number(5,2)23.5623.560.0080.01
number(4,3)123.45报错 3.45 3.45
number(5,5)0.20.20.0000120.00001
number(3,-2)182200142.85100
number(2,3)0.123报错0.0230.023 0.23报错
number(3,3)0.2340.2340.230.23 0.10.1
number(6,3)999.9995999.9990.00950.01
n总结
1)有效位范围表⽰，从左边数第⼀个⾮0数值开始，到最后⼀个数值的长度范围。

例如：number(3,2) 最⼤值：9.99，最⼩值：0.01 或 0
2)是不是发现，number 确实是⼀个很有意思的数据类型。

它通过有效位(P)和⼩数位(S)，就可以精确设置该字段可以存储的数据范围。

3)在平常开发中，对于已知长度数值类型，建议指定有效位和⼩数位；对于未知长度的数值，可以使⽤ number。

2)其他数值类型介绍
类型描述存储描述
DECIMAL(P,S)数字类型P为有效位，S为⼩数
INTEGER整数类型⼩的整数
FLOAT浮点数类型NUMBER(38), 双精度
REAL实数类型NUMBER(63), 精度更⾼
create table t_test_number(deci decimal, i integer, f float, r real);
insert into t_test_number values(12.23, 23.55, 22.35, 32.523);
select * from t_test_number;
执⾏以上语句，将看到如下结果：
说明：
说明：这些数据类型应该都属于 number 类型的“变种”，⽽且使⽤ number 类型都能替代这些数据类型，所以这⾥不过多去研究它们。

3.字符类型
1)CHAR(Type = 96)
⽤于存储固定长度的字符串，⼀个空间占⽤1个字节
个字节，最多存储2000个字节
个字节，即：char(2000)，这⾥的2000表⽰字节数。

通常也就是1000个汉字，或者2000个字母、数字等。

⽰例：
create table t_char(name char(10));
insert into t_char values('中国');
insert into t_char values('ab12');
commit;
select name, dump(name) from t_char;
执⾏以上语句将看到如下结果：
由图可见，两次插⼊的字符都不⾜指定长度，便以空格的字节
空格的字节(32)补齐
补齐了，总长度依然是10个字节；另外，当插⼊数据时超过指定长度将报错
报错，。

2)NCHAR(Type = 96)
根据字符集⽽定的固定长度
固定长度字符串。

⽤于存储固定长度的字符串，⼀个空间占⽤2个字节
个字节，最多存储2000个字节
个字节，即：nchar(1000)，注意这⾥的10 00表⽰字符数。

为什么说是字符数呢？N表⽰是以 unicode 编码格式存储，⽆论中⽂或英⽂、数字都以⼀个字符（2个字节）来存储，这也是 CHAR 与 NCHAR 最⼤的区别。

看⽰例：
create table t_nchar(name nchar(10));
insert into t_nchar values('中国');
insert into t_nchar values('ab12');
commit;
select name, dump(name) from t_nchar;
由图可见，⽆论是中⽂或英⽂都是⼀个字符占⽤两个字节，所以 nchar(10) 可以存储10中⽂，同时也只能存储10英⽂或数字。

例如：
insert into t_nchar values('中华⼈民共和国中华⼈');
insert into t_nchar values('abcde12345');
3)VARCHAR2(Type = 1)
VARCHAR2 与 CHAR 类似，不同的是 VARCHAR2 ⽤于存储可变长度的字符串。

最⼤长度是 CHAR 的两倍，也就是4000个字节
个字节，即：varchar2(40
00) ，通常也就是2000个汉字，或者4000个字母、数字等。

⽰例：
create table t_varchar2(name varchar2(10));
insert into t_varchar2 values('中国');
insert into t_varchar2 values('ab12');
commit;
select name, dump(name) from t_varchar2;
执⾏以上语句将看到如下结果：
可以看到与 char 不⼀样，两次插⼊都没有满⾜10个字节，是多少个字节就存多少个字节，并没有以空格(byte 32)去补齐，帮我们节省了空间。

4)NVARCHAR2(Type = 4)
根据字符集⽽定的可变长度
可变长度字符串。

NVARCHAR2 与 NCHAR 类似，不同的是 NVARCHAR2 ⽤于存储可变长度的字符串。

最⼤长度是 NCHAR 的两倍，也就是4000个字节
个字节，即：nvarchar2(2000) ，通常也就是2000个汉字、字母、数字等。

⽰例：
create table t_nvarchar2(name nvarchar2(10));
insert into t_nvarchar2 values('中国');
insert into t_nvarchar2 values('ab12');
commit;
select name, dump(name) from t_nvarchar2;
执⾏以上语句将看到如下结果：
由图可见，与 NCHAR ⼀样，⼀个英⽂或数字都占⽤了两个字节，但是长度是不固定的。

l使⽤ NVARCHAR2 类型存储多国语⾔，例如：
CREATE TABLE T1(Id int, Name nvarchar2(256));
INSERT INTO T1 VALUES(1, N'اﻟﺰﻧﺪاﻧﻲﻋﺰﻳﺰاﻟﻤﺠﻴﺪﺪ');
INSERT INTO T1 VALUES(2, N'中国 abc');
SELECT * FROM T1;
DROP TABLE T1;
n总结
四种字符类型⽐较：
类型可变长度⼀个空间占⽤字
节数最⼤字节数(bytes)适⽤场景
CHAR否12000、 char(2000)定长：英⽂、数字
NCHAR否22000, nchar(1000)定长：中⽂
VARCHAR2是14000, varchar2(40000)变长：英⽂、数字、
中⽂
NCARCHAR2是24000, ncarchar2(2000)变长：中⽂
1.，都是占2个或2个以上字节；
2.对于英⽂或数字，在 CHAR 和 VARCHAR2 中占⼀个字节，在 NCHAR 和 NVARCHAR2 中占两个字节。

n VARCHAR2 与 CHAR 的区别：
⼤家可能为想既然 varchar2 这么有优势，为什么需要 char 呢，不是多余么？
答案：当然不是，char 也有它⾃⼰的有优势。

当我们存储已知固定长度的数据时，⽐如：⼿机号（11位）、⾝份证号码（18位）等，可以考虑使⽤ ch ar。

因为，在查询数据时，对于char 类型字段，是全字符整体匹配；⽽varchar2 是⼀个字符⼀个字符的进⾏匹配。

所以，从查询效率上，char 要⽐
varchar2 ⾼。

nchar 与 nvarchar2 同样如此。

n VARCHAR 与 VARCHAR2 的区别
⾸先，Oracle ⼀直不⿎励使⽤ VARCHAR 类型，尽管 VARCHAR 数据类型当前与 VARCHAR2 是同义的，但是建议使⽤ VARCHAR2 类型。

经过测试，并没有发现有什么区别：
create table t_varchar(id number, name varchar(4000));
insert into t_varchar values('中国');
insert into t_varchar values('ab12');
commit;
select name, dump(name) from t_varchar;
1)都是存储可变长度的字符串；
2)存储最⼤长度没区别；
3)就连存储类型都与 varchar2 ⼀致：
4)对空字符串和 null 的处理（⽐如判断条件）
百思不得其解啊？
5)当存储多国语⾔时，需要采⽤ NVARCHAR2，⽽ VARCHAR2 不能。

n注意事项：
1)在 ORACLE 数据库汉字占⽤⼏个字节，是根据 ORACLE 中字符集编码决定，⼀般情况，数据库的 NLS_CHARACTERSET 为 AL32UTF8 或 UTF
8，⼀个汉字占三到四个字节
三到四个字节；如果 NLS_CHARACTERSET 为 ZHS16GBK，则⼀个字符占两个字节
两个字节。

2)通常在创建表时，对于字符串类型（char、varchar2等），建议指定长度为16的倍数，例如：char(16)、char(32)、char(64)、char(128)等，这样
便是有规律的，从可读性上有利于阅读和记忆，当然这并不是强制性的。

3)当我们创建表时，对于存储已知固定长度的字符串，建议使⽤ char/nchar 类型；⽽未知长度的字符串，建议使⽤ varchar2/nvarchar2 类型。

4.⽇期类型
Oracle ⽇期类型主要使⽤ DATE 和 TIMESTAMP 数据类型。

1)DATE
date ⽤于存储⽇期和时间类型，date 类型的默认格式为：DD-MM-YYYY，当我插⼊熟悉的 YYYY-MM-DD 格式时，需要进⾏格式转换。

⽰例：
create table t_date(birth date);
下⾯是插⼊数据的⼏种⽅式：
插⼊语句结果
insert into t_date values('22-12⽉-2018');2018/12/22
insert into t_date values(to_date('2018-12-22', 'y
yyy-mm-dd'));
2018/12/22
insert into t_date values(to_date('2018-12-22 15:
22:16', 'yyyy-mm-dd hh24:mi:ss'));
2018/12/22 15:22:16
insert into t_date values('2018-12-22');ORA-01861: ⽂字与格式字符串不匹配
insert into t_date values('22-12-18');ORA-01843: ⽆效的⽉份
insert into t_date values('22-12-2018');ORA-01843: ⽆效的⽉份
insert into t_date values('12⽉-22-2018');ORA-01861: ⽂字与格式字符串不匹配
insert into t_date values('22-12⽉-2018 10:17:25' );ORA-01830: ⽇期格式图⽚在转换整个输⼊字符串之前结束
insert into t_date values(to_date('2018-12-22 15:
22:16', 'yyyy-mm-dd hh:mi:ss'));
ORA-01849: ⼩时值必须介于 1 和 12 之间
2)TIMESTAMP
时间戳类型，与 DATE 相⽐较，TIMESTAMP 类型具有⼩数位毫秒数，⽐ DATE 的精度更⾼。

⽰例：create table t_timestamp(times timestamp);
下⾯是插⼊数据的⼏种⽅式：
插⼊语句结果
insert into t_timestamp values('22-12⽉-2018');22-12⽉-18 12.00.00.000000 上午
insert into t_timestamp values(to_date('2018-12-
22', 'yyyy-mm-dd'));
22-12⽉-18 12.00.00.000000 上午
insert into t_timestamp values(to_date('2018-12-
22 15:22:26', 'yyyy-mm-dd hh24:mi:ss'));
22-12⽉-18 03.22.26.000000 下午
insert into t_timestamp values(to_timestamp('20 18-12-22 15:22:26.256', 'yyyy-mm-dd hh24:mi:ss .ff'));22-12⽉-18 03.22.26.256000 下午
#注意：未指定毫秒数长度时，根据具体值的毫秒数进⾏转换（⽐如6位就转为6位）。

insert into t_timestamp values(to_timestamp('20 18-12-22 15:22:26.2567', 'yyyy-mm-dd hh24:mi: ss.ff4'));22-12⽉-18 03.22.26.256700 下午#指定毫秒数
5.⼤数据类型与⼆进制类型
从前⾯的字符类型了解到，varchar2 最⼤可以存储2000个中⽂或4000个英⽂；⽽nvarchar2 只能存2000个中⽂或英⽂，当着两个数据类型不够存储时，我们可以考虑使⽤⼤数据 long、clob 等类型来存储。

1)LONG
⽤于存储可变长度的超长字符串，最⼤长度为2G，通常⽤于存储备注字段，或者 varchar2 和 nvarchar2 不够存储时。

⽰例：
create table t_long(name long);
insert into t_long values('李a1');
提⽰：LONG 是⼀种较⽼的数据类型，将来会逐渐被BLOB、CLOB、NCLOB等⼤的对象数据类型所取代。

2)CLOB
CLOB 是⼀种字符型⼤型对象（Character Large Object），最⼤长度为4G，存储与字符集相关。

⽰例：
create table t_clob(name clob);
insert into t_clob values('李a2');
3)NCLOB
根据字符集⽽定的字符数据，最⼤长度为4G。

⽰例：
create table t_nclob(name nclob);
insert into t_nclob values('李a3');
4)BLOB
BLOG 是⼀种⼆进制⼤型对象（Binary Large Object），最⼤长度为4G，适⽤于存储⾮⽂本的字节流数据，如：视频、⾳频等。

⽰例：
create table t_blob(name blob);
提⽰：
提⽰：由于视频、⾳频⽂件存储在数据库中会进⾏转码（压码、解码），所以通常并不会将这些数据存储在数据库中。

但是存储在数据库中会⽐较安全，也⽀持该⽅式去存储。

5)RAW
固定长度
固定长度的⼆进制数据，最⼤长度 2000个字节，可存储多媒体视频、⾳频格式等。

6)LONG RAW
可变长度的⼆进制数据，最⼤长度2G，与 RAW 类似。

可变长度
7)BFILE
存储在数据库之外的⼆进制数据，最⼤长度4G。

8)ROWID
⾏地址，⼗六进制串，表⽰⾏在所在的表中唯⼀的⾏地址，该数据类型主要⽤于返回ROWID伪列，常⽤在可以将表中的每⼀条记录都加以唯⼀标识的场合。

数据表中记录的唯⼀⾏号，占10个字节。

例如：
SELECT t1.rowid,dump(t1.rowid) bytes, t1.empno, t1.ename FROM emp t1;
9)NROWID
⼆进制数据表中记录的唯⼀⾏号，最⼤长度4000个字节。

6.列举常⽤类型的数字代码
CREATE TABLE Temp01(ID number, Data_Type varchar2(16),Value varchar2(256),Type varchar2(256));
declare
v_integer integer:=22;
v_decimal decimal(5,2):=22.6;
v_float float:=22.7;
v_real real:=22.8;
v_number number(5,2):=22.9;
v_char char(1):='z';
v_nchar nchar(1):='中';
v_varchar2 varchar2(2):='z';
v_nvarchar2 nvarchar2(2):='中';
v_varchar varchar(1):='z';
v_date date:= to_date('2020-12-31','yyyy-mm-dd');
v_timestamp timestamp:= to_timestamp('2020-12-31 23:59:59.999','yyyy-mm-dd hh24:mi:ss.ff3');
v_long long:='aaa';
begin
INSERT INTO Temp01 SELECT*FROM(
SELECT1,'integer', to_char(v_integer),dump(v_integer)FROM dual UNION
SELECT2,'decimal(5,2)', to_char(v_decimal),dump(v_decimal)FROM dual UNION
SELECT3,'float', to_char(v_float),dump(v_float)FROM dual UNION
SELECT4,'real', to_char(v_real),dump(v_real)FROM dual UNION
SELECT5,'number(5,2)', to_char(v_number),dump(v_number)FROM dual UNION
SELECT6,'char(1)', to_char(v_char),dump(v_char)FROM dual UNION
SELECT7,'nchar(1)', to_char(v_nchar),dump(v_nchar)FROM dual UNION
SELECT8,'varchar2(2)', to_char(v_varchar2),dump(v_varchar2)FROM dual UNION
SELECT9,'nvarchar2(2)', to_char(v_nvarchar2),dump(v_nvarchar2)FROM dual UNION
SELECT10,'varchar(1)', to_char(v_varchar),dump(v_varchar)FROM dual UNION
SELECT11,'date', to_char(v_date),dump(v_date)FROM dual UNION
SELECT12,'timestamp', to_char(v_timestamp),dump(v_timestamp)FROM dual UNION
SELECT13,'long', to_char(v_long),dump(v_long)FROM dual
) t;
end;
SELECT*FROM Temp01;
DROP TABLE Temp01;
数字代码：
字节占⽤情况，以及该类型代表的数字代码
从上图可以看到，每种数据类型的字节占⽤情况
1)数字类型，type=2;
2)固定长度字符类型，type=96;
3)可变长度字符类型，type=1;
4)⽇期类型，type=12;
5)时间戳类型，type=180;。