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.56	23.56	0.008	0.01
number(4,3)	123.45	报错	3.45	3.45
number(5,5)	0.2	0.2	0.000012	0.00001
number(3,-2)	182	200	142.85	100
number(2,3)	0.123	报错	0.023	0.023
	0.23	报错
number(3,3)	0.234	0.234	0.23	0.23
	0.1	0.1
number(6,3)	999.9995	999.999	0.0095	0.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)，注意这里的1000表示字符数。

为什么说是字符数呢？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(4000) ，通常也就是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	否	1	2000、 char(2000)	定长：英文、数字
NCHAR	否	2	2000, nchar(1000)	定长：中文
VARCHAR2	是	1	4000, varchar2(40000)	变长：英文、数字、中文
NCARCHAR2	是	2	4000, ncarchar2(2000)	变长：中文

1.   对于中文，在这四种类型中存储，都是占2个或2个以上字节；

2.   对于英文或数字，在 CHAR 和 VARCHAR2 中占一个字节，在 NCHAR 和 NVARCHAR2 中占两个字节。

 

n  VARCHAR2 与 CHAR 的区别：

大家可能为想既然 varchar2 这么有优势，为什么需要 char 呢，不是多余么？

答案：当然不是，char 也有它自己的有优势。当我们存储已知固定长度的数据时，比如：手机号（11位）、身份证号码（18位）等，可以考虑使用 char。因为，在查询数据时，对于 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 或 UTF8，一个汉字占三到四个字节；如果 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', 'yyyy-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('2018-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('2018-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 (

    SELECT 1, 'integer', to_char(v_integer), dump(v_integer) FROM dual UNION

    SELECT 2, 'decimal(5,2)', to_char(v_decimal), dump(v_decimal) FROM dual UNION

    SELECT 3, 'float', to_char(v_float), dump(v_float) FROM dual UNION

    SELECT 4, 'real', to_char(v_real), dump(v_real) FROM dual UNION

    SELECT 5, 'number(5,2)', to_char(v_number), dump(v_number) FROM dual UNION

    SELECT 6, 'char(1)', to_char(v_char), dump(v_char) FROM dual UNION

    SELECT 7, 'nchar(1)', to_char(v_nchar), dump(v_nchar) FROM dual UNION

    SELECT 8, 'varchar2(2)', to_char(v_varchar2), dump(v_varchar2) FROM dual UNION

    SELECT 9, 'nvarchar2(2)', to_char(v_nvarchar2), dump(v_nvarchar2) FROM dual UNION

    SELECT 10, 'varchar(1)', to_char(v_varchar), dump(v_varchar) FROM dual UNION

    SELECT 11, 'date', to_char(v_date), dump(v_date) FROM dual UNION

    SELECT 12, 'timestamp', to_char(v_timestamp), dump(v_timestamp) FROM dual UNION

    SELECT 13, '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;