<oracle-11> 数据类型

选择一个[color=red]正确的数据类型[/color]，这看上去再容易不过了，但屡屡发生选择不当的情况。选择适当的数据类型至关重要，[color=red]而且很难事后再做改变[/color]。

[b]11.1 oracle数据类型概述[/b]
Oracle提供了22种不同的sql数据类型。
（1） [b]char[/b]:这是一个定长字符串，会[color=red]用空格填充来达到其最大长度[/color]。非null的char(10)总是包含10字节信息（使用了默认国家语言支持，NLS,设置）。char字段最多可以存储2000字节的信息。
（2） [b]nchar[/b]:这是一个包含unicode格式数据的定长字符串。Unicode是一种字符串编码规范。非null的nchar(10)总是包含10个字符的信息。Nchar字段最多可以存储2000字节的信息。
（3） [color=red][b]varchar2[/b][/color]:目前这也是varchar的同义词。这是一个变长字符串，与char类型不同，[color=red]它不会用空格填充至最大长度[/color]。Varchar2(10)可能包含0~10[color=red]字节[/color]的信息（使用默认NLS设置）。varchar2最多可以存储4000字节的信息。
（4） [b]nvarchar2[/b]:这是一个包含unicode格式数据的变长字符串。Nvarchar2(10)可以包含0~10个字符信息。Nvarchar2最多可以存储4000字节的信息。
（5） [b]raw[/b]: 这是一种变长二进制数据类型，这说明采用这种数据类型存储的数据不会发生字符集转换。可以把它看做由数据库存储的信息的二进制字节串。这种类型最多可以存储2000字节的信息。
（6） [color=red][b]number[/b][/color]:这种数据类型能存储[color=red]精度最多达38位的数字[/color]。每个数存储在一个变长字段中，其长度在0~22字节之间。Oracle的number类型精度很高，[b]远远高于许多编程语言中的float和double类型[/b]。
（7） [b]binary_float[/b]:这时oracle 10g release 1及以上版本中才有的一种新类型。他是一个32位单精度浮点数，可以支持至少6位精度，占用磁盘上5字节的存储空间。
（8） [b]binary_double[/b]：这是一种oracle 10g release1及以上版本中才有的新类型。它是一个64位双精度浮点数，可以支持至少13位精度，占用磁盘上9字节的存储空间。
（9） [b]long[/b]: 这种类型能存储最多2GB的字符数据。由于long类型有许多限制，而且提供long类型[color=red]只是为了保证向后兼容[/color]，所以[color=red]强烈建议[/color]新应用中[color=red]不要使用long类型[/color]。而且现有的应用中也要尽可能将long类型转换为clob类型。
（10） [b]long raw[/b]:这种类型最多能存储2GB的二进制信息。由于long同样的原因，建议在将来所有开发中都使用Blob类型，另外现有的应用中也尽可能将long raw转换为blob类型。
（11） [color=red][b]date[/b][/color]: 这是一个[b]7字节的定宽日期/时间数据类型[/b]。其中总包含7个属性，世纪，世纪中哪一年，月份，月中的哪一天，小时，分钟和秒。
（12） [color=red][b][b]timestamp[/b][/b][/color]：这是一个7字节或11字节的定宽日期/时间数据类型。它与date数据类型不同，因为[color=red]timestamp可以包含小数秒[/color]，带小数秒的timestamp在小数点右边最多可以保留9位。
（13） [b]timestamp with time zone[/b]：与前一种类型类似，这是一个13字节的定宽timestamp，不过它还提供了时区（time zone）支持。数据中会随timestamp存储关于时区的额外信息，所以原先插入的time zone会与数据一同保留。
（14） [b]timestamp with local time zone[/b]: 与timestamp类似，这是一种7字节或11字节的定宽日期/时间数据类型。不过，这种类型对时区敏感。如果在数据库中有修改，会参考数据中提供的timezone,根据数据库时区对数据中的日期/时间部分进行规范化。
（15） [b]interval year to month[/b]: 这是一个5字节的定宽数据类型。用于存储一个时段，这个类型将时段存储为年数和月数。可以再日期运算中使用这种时间间隔使一个date或timestamp类型增加或减少一段时间。
（16） [b]interval day to second[/b]: 这是一个11字节的定宽数据类型，用于存储一个时段，这个类型将时段存储为天/小时/分钟/秒数, 还可以有最多9位小数秒。
（17） [b]Bfile[/b]：这种数据类型允许在数据库列中存储一个oracle目录对象和一个文件名，并读取这个文件。这实际上允许你以一种只读的方式访问数据库服务器上可用的操作系统文件，这好像他们存储在数据库表本身中一样。
（18） [b]Blob[/b]：在oracle 9i及以前的版本中，这种数据类型允许存储最多4GB数据，在oracle 10g及以上的版本中允许存储最多4GB * 10(数据库块大小)字节的数据。Blob包含不需要进行字符集转换的二进制数据，如果要存储电子表格，字处理文档，图像文件等就很适合采用这种数据类型。
（19） [b]Clob[/b]: 在oracle 9i及以前的版本中，这种数据类型允许存储最多4GB的数据，在oracle 10g及以上的版本中允许存储最多4GB*10（数据库块大小）字节的数据。Clob包含要进行字符集转换的信息。这种数据类型很适合存储大块纯文本信息。如果你的纯文本只有4000字节或更少，那么这种数据类型并不适用，varchar2数据类型就够用了。
（20） [b]Nclob[/b]: 在oracle 9i及以前的版本中这种数据类型允许存储最多4GB的数据，在oracle 10g及以上的版本中允许存储最多4GB * 10(数据库块大小)字节的数据。NCLOB存储用数据库国家字符集编码的信息，而且像CLOB一样，这些信息要进行字符集转换。
（21） [color=red][b]Rowid[/b][/color]:rowed实际上是数据库中一行的10字节地址。Rowid中编码有足够的信息，足以在磁盘上定位这一行，以及标识rowid指向的对象。
（22） [b]Urowid[/b]: urowid是一个通用rowid，用于表（如IOT和通过异构数据库网关访问的没有固定rowid的表）。Urowid是行主键值的一种表示。因此，取决于所指向的对象，urowid的大小很有所变化。

[b]11.2 字符和二进制串类型[/b]
[b]11.2.1 NLS概述[/b]
NLS代表国家语言支持（national language support）。NLS是数据库的一个非常强大的特性，但是人们往往未能正确理解这个特性。NLS控制着数据的许多方面。例如，它控制着数据如何存储；我们在数据库中会看到多个逗号和一个点号（如1,000,000,000.01）还是看到多个点号和一个逗号（如1.000.000,01）。但是最重要的是它控制着以下两个方面：
[color=red]（1） 文本数据持久存储在磁盘上时如何编码。
（2） 透明地将数据从一个字符集转换到另一个字符集。[/color]

[b]11.2.2 字符串[/b]
Oracle中有4种基本的字符串类型，分别是char, varchar2, nchar和nvarchar2。[color=red]在oracle中，所有串都以同样的格式存储[/color]。在数据库块上，[b]最前面都有一个1-3字节的长度字段，其后才是数据[/b]。如果数据为null, 长度字段则表示为一个单字节oxFF。Tom说到，[color=red]从来没有见过哪个应用适合使用char类型[/color]。因为char总是会用空格来填充得到的串，使之达到一个固定宽度，不论是表段还是索引段中，char都会占用最大的存储空间。除此之外，还有绑定变量问题。所以，无论什么场合都要避免使用char类型。即使对于单字符的字符字段也是如此。

[b]Char和varchar2类型支持两种指定长度的方法。[/b]
（1） [color=red]用字节指定[/color]：varchar2([color=red]10 byte[/color])。这能支持最多10字节的数据，在一个多字节字符集中，这可能只是两个字符。
（2） [b]用字符指定[/b]：varchar2([color=red]10 char[/color])。这将支持最多10字符的数据，可能是多达40字节的信息。另外，varchar2(4000 char)理论上最多支持4000个字符的数据，不过由于oracle中字符串数据类型限制为4000字节，所以可能无法得到全部4000个字符。[color=red][b]如果后面不带类型，一般默认是字节[/b][/color]，使用utf8之类的多字节字符集时，建议使用char修饰符。

[b]11.3 二进制串： raw类型[/b]
Oracle除了支持文本，还支持二进制数据的存储。前面讨论的char和varchar2类型需要进行字符集转换，而二进制数据不会做这种字符集转换。因此，[b]二进制数据类型不适合存储用户提供的文本，而适于存储加密信息，加密数据不是文本，而是原文本的一个二进制表示，包括二进制标记信息的字处理文档等等。[/b]

Oracle支持下面3种数据类型来存储[b]二进制数据[/b]。
（1） raw类型，它很适合存储多达2000字节的raw数据。
（2） blob类型，它支持更大的二进制数据。
（3） long raw类型，这是为支持向后兼容性提供的，新应用不应考虑使用这个类型。

从磁盘上的存储来看，raw类型与varchar2类型很相似。Raw类型是一个变长的二进制串。Raw类型可以加索引，还能在谓词中使用，它与其他任何类型有同样的功能。不过，必须当心避免不希望的隐式转换，而且必须知道确实会发生隐式转换。[color=red]推荐使用显示转换[/color]，可以使用以下内置函数来执行这种操作。
（1） [color=red]hextoraw[/color]: 将16禁止字符串转换为raw类型。
（2） [color=red]rawtohex[/color]: 将raw串转换为16进制串。

Sql*plus将raw类型获取为一个串时，会隐式调用rawtohex函数，而插入串时会隐式调用hextoraw函数。应该避免隐式转换，而在编写代码时总是使用显式转换：

Select rawtohex(raw_data) from t;
Insert into t values(hextoraw(‘abcdef’));

[b]11. 4 数值类型[/b]
Oracle 10g及以上版本支持3种固有数据类型来存储数值。Oracle 9i release2及以前的版本只支持一种适合存储数值数据的固有数据类型。以下所列的数据类型中，number类型在所有oracle版本中都得到支持，后两种类型是新的数据类型。
（1） [b]number[/b]: oracle number类型能以极大的精度存储数值，具体来讲，精度可达38位。Number类型是一种变长格式，长度为0-22字节。[b]这是目前最为常用的数值类型[/b]。
（2） [b]binary_float[/b]:这是一种IEEE固有的单精度浮点数。它在磁盘上会占用5字节的存储空间，其中4个固定字节用于存储浮点数，另外还有一个长度字节。Binary_float能存储6位精度的数值。
（3） [b]binary_double[/b]: 这是一种IEEE固有的双精度浮点数。它在磁盘上会占用9字节的存储空间。其中8个固定字节用于存储浮点数，还有一个长度字节。Binary_double能存储13位精度的数值。

[b]11.4.1 number类型的语法和用法[/b]
number类型的语法很简单：

number(p, s)

在此p和s都是可选的，用于指定:
(1) 精度（precision）,或总位数。默认情况下，精度为38位。
(2) 小数位数（scale）,或小数点右边的位数。小数位数的合法值为-84~127，其默认值取决于是否指定了精度。如果没有指定精度，小数位数则默认有最大的取值区间；如果指定了精度，小数位数默认为0。允许小数位数为-84~127看起来很奇怪，为什么小数位数可以是负值？其作用是允许对小数点左边的值舍入。就像number(5,2)将值舍入为最接近的0.01一样，number(5, -2)会把数值舍入为与之最接近的100.

[b]11.4.2 非固有数值类型[/b]
除number, binary_float和binary_double类型，oracle在语法上（指的是在create时可以使用这些数据类型，[color=red]但是底层实际上存储时都只是number类型[/color]）还支持以下数值数据类型。
（1） numeric(p, s):完全映射至number(p, s)。
（2） decimal(p, s): 完全映射至number(p, s)。
（3） integer或int:完全映射至number(38)类型。
（4） float(p): 映射至number类型。
（5） double precision: 映射至number类型。
（6） real: 映射至bumber类型。

[b]11.4.3 性能考虑[/b]
[color=red]一般而言，oracle number类型对大多数应用来讲都是最佳的选择[/color]。不过，这个类型会带来一些性能影响。Number类型是一种软件数据类型，在oracle软件本身中实现。我们不能使用固有硬件操作将两个number相加。不过，浮点数没有这种实现。将两个浮点数相加时，oracle会使用硬件来执行运算。[color=red]性能可以相差几十倍[/color]。
但是，浮点数是数值的一个近似值，精度在6-13位之间。从number类型得到的答案比从浮点数得到的答案精确的多。但是，如果你在对科学数据执行数据挖掘或复杂的数值计算，这种精度损失往往是可以接受的，另外可能会得到非常显著的性能提升。

[color=red]需要注意的是，我们可以鱼和熊掌兼得。通过使用内置的cast函数，可以对oracle number类型执行一种实时的转换，在对其进行复杂的数学运算之前先将其转换为一种浮点数类型。这样一来，所用的cpu时间就与使用固有浮点数类型所用的cpu时间非常接近，同时也达到了精确地存储数据：[/color]
Select sum(ln(cast(num_type as binary_double))) from t;

[b]11.5 date, timestamp 和 interval类型[/b]
Oracle固有数据类型date, timestamp和interval是紧密相关的。Date和timestamp类型存储精度可变的固定日期/时间。Interval类型可以很容易存储一个时间量，如“8个小时”或“30天”。将两个时间戳相减就得到一个时间间隔。

11.5.1 格式
[color=red]关于格式，建议就是：应该明确使用格式[/color]。将一个表示date,timestamp或interval的串发送到数据库时就可以使用格式。[b]不要依赖于默认的日期格式[/b]，默认格式会在将来某个时刻被另外某个人所修改。
处于安全性考虑，如果没有一个明确的格式，永远不要使用to_char/to_date。没有人知道’01-02-03’究竟表示哪一天。
考虑一下insert语句，它依赖于一个默认的日期掩码：
Insert into t (date_column) values([color=darkred]‘01/02/03’[/color]);
这个insert最好写作：
Insert into t(date_column) values([color=red]to_date(‘01/02/03’, ‘DD/MM/YY’)[/color]);
但最好的做法是：
Insert into t(date_column) values([color=indigo]to_date(‘01/02/03’, ‘DD/MM/YYYY’)[/color]);
[b]也就是必须使用一个4字符的年份。[/b]

[b]11.5.2 date类型[/b]
Date类型是一个7字节的定宽日期/时间数据类型。它总是包含7个属性，包括：世纪，世纪中哪一年，月份，月中的哪一天，小时，分钟和秒。Oracle使用一种内部格式来表示这个信息，使用内置[b]dump函数[/b]可以看到oracle实际存储的内容。

[color=red][b]1. 向date增加或减去时间[/b][/color]
常用的技术有3种：
[color=red]（1） 向date增加一个number[/color]，把date加1是[b]增加1天[/b]的一种方法，因此，向date增加1/24就是增加1个小时，依次类推。
[b]（2） 使用interval类型来增加事件单位。[/b]Interval类型支持两种粒度：年和月，或日/小时/分钟/秒。也就是说，可以是几年和几个月的一个时间间隔，也可以是几天，几小时，几分钟和几秒的一个时间间隔。
[color=red][b][b]（3） 使用内置的add_month函数增加月。[/b][/b][/color]由于增加一个月往往不像增加28-31天那么简单，为了便于增加月，专门实现了这样一个函数。
[img]http://dl2.iteye.com/upload/attachment/0105/9524/05048260-31fb-36c7-a63b-937f95849cce.jpg[/img]

总的来讲，使用oracle date类型时，建议：
（1） 使用numtodsinterval内置函数来增加小时、分钟和秒。
（2） 加一个简单的数来增加天
（3） 使用add_months内置函数增加月和年。


[color=red]2. 得到两个日期之差[/color]
只是把[color=red]两个日期相减[/color]，会返回表示两个日期[color=red]相隔天数[/color]的一个数；另外，还可以使用内置函数[color=red]months_between[/color]，它会返回表示两个日期相隔[color=red]月数[/color]的一个数（包括月小数）；最后，还可以利用interval类型，使用numtodsinterval或numtoyminterval等内置函数。


[b]11.5.3 timestamp类型[/b]
Timestamp类型与date非常类似，只不过另外还支持小数秒和时区。

1. Timestamp
基本timestamp数据类型语法很简单：
Timestamp(n)
这里n是可选的，用于指定timestamp中秒分量的小数单位，可以取值为0-9。如果指定0，timestamp在功能上则与data等价，他们实际上会以同样的方式存储相同的值。

2. 向timestamp增加或减去时间
Date执行日期算术运算所用的技术同样适用于timestamp，但是使用上述技术的很多情况下，timestamp会转换为一个date.


3. 得到两个timestamp之差
这正是date和timestamp类型存在显著差异的地方。尽管将date相减的结果是一个number，但timestamp相减的结果却是一个interval.