.Net程序员学用Oracle系列(5)：三大数据类型

87632219

2017-01-12

关注关注

《.Net程序员学用Oracle系列：导航目录》

本文大纲

1、Oracle 数据类型概述
2、字符类型 2.1、字符集 & NLS
2.2、常见的两种字符串
2.3、NCHAR & NVARCHAR2

3、[strong]数值类型[/strong]

3.1、固有数值类型
3.2、非固有数值类型
3.3、性能考虑

4、[strong]日期类型[/strong]

4.1、日期格式
4.2、常用日期类型及四个案例

5、[strong]总结[/strong]

5.1、Oracle 与 SQL Server 数据类型比较
5.2、Oracle 数据类型使用感言

选择一个正确的数据类型，这看上去再容易不过了，但我们屡屡见到选择不当的情况。要选择什么类型来存储你的数据，这是一个最基本的决定，而且这个决定会在以后的数年间影响着你的应用和数据。因此选择适当的数据类型至关重要，而且很难事后再做改变，也就是说，一旦选择某些类型实现了应用，在相当长的时间内就只能“忍耐”，因为你选择的类型可能不太合适。

上面这段话来自 Thomas Kyte（Oracle公司核心技术集团副总裁）的——《Oracle Database 9i/10g/11g编程艺术》。我对这段话描述的情况感同身受，因此引用了过来。本文第二节内容来自该书第 12 章第一节——《Oracle 数据类型概述》，有删改。

Oracle 数据类型概述

Oracle 提供了 22 种不同的 SQL 数据类型供我们使用，分别如下：

CHAR：这是一种定长字符串，会用空格填充来达到其最大长度。非 null 的 CHAR(10) 总是包含 10 字节信息，使用默认 NLS 设置，CHAR 最多可以存储 2000 字节的信息。
NCHAR：这是一种包含 UNICODE 格式数据的定长字符串。有了 NCHAR 类型，就允许数据库中包含采用两种不同字符集的数据：使用数据库字符集的 CHAR 类型和使用国家字符集的 NCHAR 类型。非 null 的 NCHAR(10) 总是包含 10 个字符的信息，NCHAR 最多可以存储 2000 字节的信息。
VARCHAR2：目前这也是 VARCHAR 的同义词。这是一种变长字符串，与 CHAR 类型不同，它不会用空格填充至最大长度。VARCHAR2(10) 可能包含 0~10 字节的信息，使用默认 NLS 设置，VARCHAR2 最多可以存储 4000 字节的信息。
NVARCHAR2：这是一种包含 UNICODE 格式数据的变长字符串。NVARCHAR2(10) 可以包含 0~10 个字符的信息，NVARCHAR2 最多可以存储 4000 字节的信息。
RAW：这是一种变长的二进制数据类型，采用这种数据类型存储的数据不会发生字符集转换。可以把它看作是由数据库存储的信息的二进制字节串。RAW 最多可以存储 2000 字节的信息。
NUMBER：这种数据类型能存储精度多达 38 位的数字。这些数介于 1.0 * 10-130 至 1.0 * 10 126（不含）之间。每个数存储在一个变长字段中，其长度在 0（尾部的 NULL 列就是 0 字节）~22字节之间。Oracle 的 NUMBER 类型精度很高，远远高于许多编程语言中常规的 float 和 double 类型。
BINARY_FLOAT：这是 Oracle Database 10g Release 1 及以上版本中才有的一种新类型。它是一种 32 位单精度浮点数，可以支持至少 6 为精度，占用磁盘上 5 字节的存储空间。
BINARY_DOUBLE：这是 Oracle Database 10g Release 1 及以上版本中才有的一种新类型。它是一种 64 位双精度浮点数，可以支持至少 13 位精度，占用磁盘上 9 字节的存储空间。
LONG：这种类型能存储最多 2GB 的字符数据（2GB 指的是 2 千兆字节，而不是 2 千兆字符，因为在一个多字节字符集中，每个字符可能有多个字节）。由于 LONG 类型有许多限制，提供 LONG 类型也只是为了保证向后兼容性，所以强烈建议新应用中不要使用 LONG 类型，而且现有应用中的 LONG 也要尽可能转换为 CLOB 类型。
LONG RAW：LONG RAW 类型能存储多达 2GB 的二进制信息。由于 LONG 同样的原因，建议用 BLOB 类型全面替代 LONG RAW 类型。
DATE：这是一种 7 字节的定宽日期数据类型。它总是包含 7 个属性，包括：世纪、世纪中那一年、月份、月份中那一天、小时、分钟和秒。
TIMESTAMP：这是一种 7 字节或 11 字节的定宽日期类型。与 DATE 类型不同的是 TIMESTAMP 还包含小数秒(fractional second)，带小数秒的 TIMESTAMP 在小数点右边最多可保留 9 位。
TIMESTAMP WITH TIME ZONE：与 TIMESTAMP 类似，这是一种 13 字节的定宽 TIMESTAMP，不过它还提供了对时区(time zone)的支持。
TIMESTAMP WITH LOCAL TIME ZONE：与 TIMESTAMP 类似，这是一种 7 字节或 11 字节的定宽日期数据类型。这种类型对时区敏感，会根据数据库或会话的时区自动转换为对应时区的日期。
INTERVAL YEAR TO MONTH：这是一种 5 字节的定宽数据类型，用于存储一个时段，这个类型将时段存储为年数和月数。可以在日期运算中使用这种时间间隔使一个 DATE 或 TIMESTAMP 类型增加或减少一段时间。
INTERVAL DAY TO SECOND：这是一种 11 字节的定宽数据类型，用于存储一个时段，这个类型将时段存储为天/小时/分钟/秒数，还可以有最多 9 位的小数秒。
BFILE：这种数据类型允许在数据库列中存储一个 Oracle 目录对象（操作系统目录的一个指针）和一个文件名，并读取这个文件。这实际上允许你以一种只读的方式访问数据库服务器上可用的操作系统文件，就好像它们存储在数据库表中一样。
BLOB：在 Oracle Database 10g 及以上版本中允许存储最多 4GB×10（数据块大小）字节的数据。BLOB 包含不需要进行字符集转换的“二进制”数据，如果要存储电子表格、字处理文档、图像文件等就很适合采用这种数据类型。
CLOB：在 Oracle Database 10g 及以上版本中允许存储最多 4GB×10（数据块大小）字节的数据。CLOB 包含要进行字符集转换的信息，这种数据类型很适合存储大块儿纯文本信息。如果你的纯文本信息只有 4000 字节或更少，那么这种数据类型并不适用，这种情况更适合用 VARCHAR2。
NCLOB：在 Oracle Database 10g 及以上版本中允许存储最多 4GB×10（数据块大小）字节的数据。NCLOB 存储用数据库国家字符集编码的信息，而且像 CLOB 一样需要进行字符集转换。
ROWID：ROWID 实际上是数据库中一行的 10 字节地址。ROWID 中编码有足够的信息，足以在磁盘上定位这一行，以及标识 ROWID 指向的对象（表等）。
UROWID：UROWID 是一种通用的 ROWID，用于表（如 IOT 和通过异构数据库网关访问的没有固定 ROWID 的表）。UROWID 是行主键值的一种表示，因此取决于所指向的对象，UROWID 的大小会有所变化。

显然以上列表中还少了许多类型，如 INT、INTEGER、SMALLINT、FLOAT、REAL 等。这些类型实际上都是在上表所列的某种基本类型的基础上实现的，也就是说，它们只是固有 Oracle 类型的同义词。还有 XMLType、SYS.ANYTYPE、SEO_GEOMETRY 等复杂类型，总之 Oracle 中的类型是很多的。

字符类型

字符集 & NLS

字符集(character set)是各个字符的一种二进制表示（用位和字节表示）。

NLS代表国家语言支持(National Language Support)。它控制着数据库的两个方面：

文本数据持久存储在磁盘上时如何编码
透明的将数据从一个字符集转换到另一个字符集

关于字符集&NLS，这里并不会详细讲解，主要是我也不太懂。我曾遇到实施人员把数据库里的大部分字符数据都搞成乱码的情况，确定是字符集问题之后，经过多次测试终于搞定。

常见的两种字符串

定长字符串(CHAR(x)&NCHAR(x))：CHAR 和 NCHAR 实际上只是伪装的 VARCHAR2/NVARCHAR2，区别是 CHAR 和 NCHAR 总是会用空格填充来达到一个固定长度。所以不论是表段还是索引段，CHAR 和 NCHAR 都会占用最大的存储空间。更严重的是 CHAR 和 NCHAR 还会带来“混乱”。几乎所有应用都不适合用 CHAR 和 NCHAR，因此如果你要决定一个字段的类型，建议忽略 CHAR 和 NCHAR 的存在。
变长字符串(VARCHAR2(x)&NVARCHAR2(x))：VARCHAR2 是 VARCHAR 的同义词，它们的制造商分别是 Oracle 和 ANSI。一开始接触 Oracle 的时候我就很好奇为啥大家普遍用 VARCHAR2，而不是 VARCHAR，后来才知道原来 VARCHAR2 这个怪异的类型是 Oracle 私有的，而且 Oracle 只确保它的兼容性，不保证标准的 VARCHAR 的兼容性，我猜这可能是大家普遍选用 VARCHAR2 的原因吧。VARCHAR2 有两个特殊的地方在开发中需要格外的注意，以防出现意外： VARCHAR2 把空字符串与 NULL 做等同处理，而 VARCHAR 仍按照空字符串处理
VARCHAR2 中一个字符要用几个字节来存储，取决于所选用的字符集

NCHAR & NVARCHAR2

如果系统中需要管理和存储多种字符集，就可以使用这两个字符串类型。NCHAR/NVARCHAR2 与 CHAR/VARCHAR2 的不同主要有两点：

文本采用数据库的国家字符集来存储和管理，而不是默认字符集。
长度总是字符数，而 CHAR/VARCHAR2 可能会指定是字节还是字符。

如果一个串中每个字符可能存储不同数目的字节，与这个串相比，定宽字符串上的串操作效率更高。

数值类型

固有数值类型

NUMBER(p,s)：Oracle NUMBER 类型能以多达 38 位的精度存储数值。其底层数据格式类似于一种封包小数表示，长度为 0~22 字节。NUMBER(p,s) 中的 p 和 s 都是可选的，p(precision)用于指定精度，或总位数。默认情况下，精度为 38 位，取值范围是 1~38 之间。也可以用字符 * 表示 38。s(scale)用于指定小数位数，或小数点右边的位数。小数位数的合法值为 -84~127,其默认值取决于是否指定了精度。如果指定了精度，小数位数默认为 0，否则默认有最大的取值区间。下面给出两个案例来进一步说明： number(p) 与 number(p,0) 等同，都只能存储精度小于或等于 p 的的整数。
number(p,s) 只能存储精度小于或等于 p，刻度小于或等于 s 的小数。比如精度是2，保存时给的是整数 5，保存之后会变成 5.00。

新数值类型：Oracle Database 10g 引入了两种新的数值类型来存储数据，分别是 BINARY_FLOAT 和 BINARY_DOUBLE。它们就是许多程序员过去常用的 IEEE 标准浮点数。浮点数用于近似数值，它们没有 Oracle NUMBER 类型那么精确。浮点数常用在科学计算中，由于允许在硬件（CPU、芯片）上执行运算，而不是 Oracle 子例程中运算。因此，如果在一个科学计算应用中执行实数处理，算术运算的速度会快得多。

非固有数值类型

除了 NUMBER、BINARY_FLOAT 和 BINARY_DOUBLE，Oracle 在语法上还支持（在语法上支持是指 CREATE 语句可以使用这些数据类型，但是在底层实际上它们都是 NUMBER 类型）以下数值数据类型，这些数值数据类型总是会映射到固有的 Oracle NUMBER 类型。

NUMERIC(p,s)：完全映射至 NUMBER(p,s)。
DECIMAL(p,s) 或 DEC(p,s)：完全映射至 NUMBER(p,s)。
INTEGER 或 INT：完全映射至 NUMBER(38)。
SMALLINT：完全映射至 NUMBER(38)。
FLOAT(p)：映射至 NUMBER 类型。
DOUBLE(p)：映射至 NUMBER 类型。
REAL：映射至 NUMBER 类型。

性能考虑

一般而言，Oracle NUMBER 类型对大多数应用来讲都是最佳的选择，不过这个类型会带来性能影响。Oracle NUMBER 是一种软件数据类型，在 Oracle 软件本身中实现。不能使用固有硬件操作将两个 NUMBER 类型相加，这要在软件中模拟。而两个浮点数相加时，Oracle 会使用硬件来执行运算。

由于 Oracle NUMBER 精度高于浮点类型，而浮点类型的计算速度又远远超过 Oracle NUMBER。此时可以用 Oracle NUMBER 精确地存储数据，然后用 CAST 函数来达到提速的目的。示例：

SELECT SUM(LN(CAST(num_type AS BINARY_DOUBLE))) FROM t;

日期类型

日期格式

日期格式有两个作用：

以某种样式对数据库中的数据进行格式化，以满足你的需求；
告诉数据库如何将一个输入串转换为 DATE、TIMESTAMP 或 INTERVAL。

一个常见的误解是：使用格式会以某种方式影响磁盘上存储的数据，并且会影响数据如何具体的存储。格式对数据如何存储根本没有任何影响。格式只是用于将存储 DATE 所用的二进制格式转换为一个串，或者将一个串转换为用于存储 DATE 的二进制格式。对于 TIMESTAMP 和 INTERVAL 也是如此。

应该使用格式，而不要依赖于默认的日期格式，默认格式很可能会在将来的某个时刻被其他人改掉。如果被改掉，响应的应用也会受到负面影响，譬如格式错误，甚至插入错误数据和 SQL 诸如攻击。

常用日期类型及四个案例

Oracle 中有 DATE、TIMESTAMP 等多个日期类型，但常用作字段数据类型的只有 DATE，其它有些类型可能会在日期运算中涉及到。

案例一：向 DATE 增加或减去指定时间，对一个 DATE 变量加 1，相当于在变量基础上增加 1 天，增加 -1 就是减去 1 天，增加 124 就是增加 1 个小时，依此类推。示例：

SELECT fn_now+1 res FROM DUAL;      -- res：2016-09-11 19:21:30
SELECT fn_now-1 res FROM DUAL;      -- res：2016-09-09 19:21:30
SELECT fn_now+(1/24) res FROM DUAL; -- res：2016-09-10 20:21:30

案例二：两个日期相减会得到相隔的天数。示例：

SELECT t1.dt2-t1.dt1 days 
  FROM(SELECT TO_DATE('2015-08-01 01:02:03','yyyy-mm-dd hh24:mi:ss') dt1,
              TO_DATE('2016-09-02 19:42:30','yyyy-mm-dd hh24:mi:ss') dt2 
       FROM DUAL) t1;
-- days：398.778090277778，即约 398.8 天

案例三：使用内置函数 MONTHS_BETWEEN 会得到两个日期相隔的月数。示例：

SELECT MONTHS_BETWEEN(dt2,dt1) months 
FROM(SELECT TO_DATE('2015-08-01 01:02:03','yyyy-mm-dd hh24:mi:ss') dt1,
            TO_DATE('2016-09-02 19:42:30','yyyy-mm-dd hh24:mi:ss') dt2 
     FROM DUAL) t1;
-- months：13.0573577508961，即 13 个月

案例四：利用 INTERVAL 类型得到两个日期的间隔。示例：

SELECT NUMTOYMINTERVAL(TRUNC(MONTHS_BETWEEN(t1.dt2,t1.dt1)),'month') years_months,
       NUMTODSINTERVAL(t1.dt2-ADD_MONTHS(t1.dt1,TRUNC(MONTHS_BETWEEN(t1.dt2,t1.dt1))),'day') day_hours 
FROM(SELECT TO_DATE('2015-08-01 01:02:03','yyyy-mm-dd hh24:mi:ss') dt1,
            TO_DATE('2016-10-06 10:06:30','yyyy-mm-dd hh24:mi:ss') dt2 
     FROM DUAL) t1;
-- years_months：+000000001-02，即 1年 2个月
-- day_hours：+000000005 09:04:27.000000000，即 5天 9小时 4 分钟 27 秒

总结

Oracle 与 SQL Server 数据类型比较

有些业务的数据类型用布尔值是比较合适的，比如存储用户的激活状态，要么已激活，要么未激活，不会有第三种情况出现。在 SQL Server 中一般用 bit 类型，我曾想在 Oracle 中找个布尔类型，结果发现 Oracle 只在语法上对布尔类型提供支持。字段数据类型你想用？对不起，这个真没有！

有时候我们需要用整形，有时候需要用浮点型，SQL Server 中有 int、float、double、decimal 等一整套非常契合程序员三观的数值类型。如果你想在 Oracle 中直接用这些类型，不好意思，它不直接提供，它只在语法上支持这些类型，好在 Oracle 中的 NUMBER 可以代替上述所有类型。这也有个好处就是数据库编程容易了，但同时带来的一个坏处就是后台编程麻烦了。

SQL Server 中提供了日期(date)和日期时间(datetime)两个类型，开发中一般都是用 datetime 居多。如果你发现 Oracle 中没有 datetime，不必惊慌，因为它的 date 就相当于 SQL Server 中的 datetime，只是精度稍差，但一般也足够用了。

Oracle 数据类型使用感言

Oracle 中的数据类型看上去很多，但对于大多数应用而言，大部分类型不怎么好用，甚至永远都不应该使用，假如不慎用了，还有可能会引起灾难性的后果。有时候我觉得 Oracle 之于数据库颇有点 IE6 之于浏览器的味道。好的很多新的应用已不再选用 Oracle，而 Oracle 本身也在做大量改变，相信将来一切都会变得更好！

事实上但基于 Oracle 的开发，真正常用的数据类型也就字符、数值和日期“三大主流数据类型”。其它数据类型要么不好用（如 CHAR、NCHAR 等类型），要么极少用（如 BLOB、CLOB 等 LOB 类型），要么根本就不应该使用（如 LONG、LONG RAW 等类型）。