第12章 数据类型
选择一个正确的数据类型，这看上去再容易不过了，但我屡屡见得选择不当的情况。要选择什么类型来存储你的数据，这是一个最基本的决定，而且这个决定会在以后的数年间影响着你的应用和数据。选择适当的数据类型至关重要，而且很难事后再做改变，也就是说，一旦选择某些类型实现了应用，在相当长的时间内就只能“忍耐”（因为你选择的类型可能不太合适）。
这一章我们将介绍Oracle所有可用的基本数据类型，讨论这些类型如何实现，并说明每种类型分别适用于哪些情况。在此不讨论用户定义的数据类型，因为用户定义的数据类型只是由Oracle内置数据类型导出的一些复合对象。我们会分析如果不合适地使用了错误的数据类型，甚至只是对数据类型使用了不正确的参数（如长度、精度、小数位等），将会发生什么情况。读完这一章后，你将对使用哪些类型有所了解，明白这些类型如何实现以及何时使用，还有很重要的一点，你会认识到为什么关键是要针对具体任务使用适宜的类型。
12.1 Oracle数据类型概述
Oracle提供了22种不同的SQL数据类型供我们使用。简要地讲，执行数据类型如下：
q CHAR：这是一个定长字符串，会用空格填充来达到其最大长度。非null的CHAR(12.)总是包含12.字节信息（使用了默认国家语言支持National Language Support，NLS设置）。稍后将更详细地介绍NLS的作用。CHAR字段最多可以存储2,000字节的信息。
q NCHAR：这是一个包含UNICODE格式数据的定长字符串。Unicode是一种对字符进行编码的通用方法，而不论使用的是何种计算机系统平台。有了NCHAR类型，就允许数据库中包含采用两种不同字符集的数据：使用数据库字符集的CHAR类型和使用国家字符集的NCHAR类型。非null的NCHAR(12.)总是包含12.个字符的信息（注意，在这方面，它与CHAR类型有所不同）。NCHAR字段最多可以存储2,000字节的信息。
q VARCHAR2：目前这也是VARCHAR的同义词。这是一个变长字符串，与CHAR类型不同，它不会用空格填充至最大长度。VARCHAR2(12.)可能包含0～12.字节的信息（使用默认NLS设置）。VARCHAR2最多可以存储4,000字节的信息。
q NVARCHAR2：这是一个包含UNICODE格式数据的变长字符串。NVARCHAR2(12.)可以包含0～12.字符的信息。NVARCHAR2最多可以存储4,000字节的信息。
q RAW：这是一种变长二进制数据类型，这说明采用这种数据类型存储的数据不会发生字符集转换。可以把它看作由数据库存储的信息的二进制字节串。这种类型最多可以存储2,000字节的信息。
q NUMBER：这种数据类型能存储精度最多达38位的数字。这些数介于12.0×12.(-130)——（但不包括）12.0×12.(126)之间。每个数存储在一个变长字段中，其长度在0（尾部的NULL列就是0字节）～22字节之间。Oracle的NUMBER类型精度很高，远远高于许多编程语言中常规的FLOAT和DOUBLE类型。
q BINARY_FLOAT：这是Oracle 10g Release 1及以后版本中才有的一种新类型。它是一个32位单精度浮点数，可以支持至少6位精度，占用磁盘上5字节的存储空间。
q LONG：这种类型能存储最多 2G 的字符数据（2GB是指2千兆字节，而不是2千兆字符，因为在一个多字节字符集中，每个字符可能有多个字节）。由于LONG类型有许多限制（后面会讨论），而且提供LONG类型只是为了保证向后兼容性，所以强烈建议新应用中不要使用LONG类型，而且在现有的应用中也要尽可能将LONG类型转换为CLOB类型。
q LONG RAW：LONG RAW类型能存储多达2GB的二进制信息。由于LONG同样的原因，建议在将来的所有开发中都使用CLOB类型，另外现有的应用中也应尽可能将LONG RAW转换为BLOB类型。
q DATE：这是一个7字节的定宽日期/时间数据类型。其中总包含7个属性，包括：世纪、世纪中哪一年、月份、月中的哪一天、小时、分钟和秒。
q TIMESTAMP：这是一个7字节或12.字节的定宽日期/时间数据类型。它与DATE数据类型不同，因为TIMESTAMP可以包含小数秒（fractional second）；带小数秒的TIMESTAMP在小数点右边最多可以保留9位。
q TIMESTAMP WITH TIME ZONE：与前一种类型类似，这是一个12.字节的定宽TIMESTAMP，不过它还提供了时区（TIME ZONE）支持。数据中会随TIMESTAMP存储有关时区的额外信息，所以原先插入的TIME ZONE会与数据一同保留。
q TIMESTAMP WITH LOCAL TIME ZONE：与TIMESTAMP类似，这是一种7字节或12.字节的定宽日期/时间数据类型；不过，这种类型对时区敏感（time zone sensitive）。如果在数据库中有修改，会参考数据中提供的TIME ZONE，根据数据库时区对数据中的日期/时间部分进行“规范化”。所以，如果你想使用U.S./Pacific时区插入一个日期/时间，而数据库时区为U.S./Eastern，最后的日期/时间信息会转换为Eastern时区的日期/时间，并像TIMESTAMP一样存储。获取这个数据时，数据库中存储的TIMESTAMP将转换为会话时区的时间。
q INTERVAL YEAR TO MONTH：这是一个5字节的定宽数据类型，用于存储一个时间段，这个类型将时段存储为年数和月数。可以在日期运算中使用这种时间间隔使一个DATE或TIMESTAMP类型增加或减少一段时间。
q INTERVAL DAY TO SECOND：这是一个12.字节的定宽数据类型，用于存储一个时段，这个类型将时段存储为天/小时/分钟/秒数，还可以有最多9位的小数秒。
q BFILE：这种数据类型允许在数据库列中存储一个Oracle目录对象（操作系统目录的一个指针）和一个文件名，并读取这个文件。这实际上允许你以一种只读的方式访问数据库服务器上可用的操作系统文件，就好像它们存储在数据库表本身中一样。
q BLOB：在Oracle9i及以前的版本中，这种数据类型允许存储最多4GB的数据，在Oracle 10g 及以后的版本中允许存储最多（4GB）×（数据库块大小）字节的数据。BLOB包含不需要进行字符集转换的“二进制“数据，如果要存储电子表格、字处理文档、图像文件等就很适合采用这种数据类型。
q CLOB：在Oracle9i及以前的版本中，这种数据类型允许存储最多4GB的数据，在Oracle 10g 及以后的版本中允许存储最多（4GB）×（数据库块大小）字节的数据。CLOB包含要进行字符集转换的信息。这种数据类型很适合存储纯文本信息。
q NCLOB：在Oracle9i及以前的版本中，这种数据类型允许存储最多4GB的数据，在Oracle 10g 及以后的版本中允许存储最多（4GB）×（数据库块大小）字节的数据。NCLOB存储用数据库国家字符集编码的信息，而且像CLOB一样，这些信息要进行字符集转换。
q ROWID：ROWID实际上是数据库中一行的12.字节地址。ROWID中编码有足够的信息，足以在磁盘上定位这一行，以及标识ROWID指向的对象（表等）。
q UROWID：UROWID是一个通用ROWID，用于表（如IOT和通过异构数据库网关访问的没有固定ROWID的表）。UROWID是行主键值的一种表示，因此，取决于所指向的对象，UROWID的大小会有所变化。
显然以上列表中还少了许多类型，如INT、INTEGER、SMALLINT、FLOAT、REAL等。这些类型实际上都是在上表所列的某种类型的基础上实现的，也就是说，它们只是固有Oracle类型的同义词。另外，诸如XML Type、SYS.ANYTYPE和SDO_GEOMETRY之类的类型在此也未列出，因为本书不打算讨论这些类型。它们是一些复杂的对象类型，包括一个属性集合以及处理这些属性的一些方法（函数）。这些复杂类型由上述基本数据类型组成，并不是传统意义上真正的数据类型，而只是你在应用中可以利用的一种实现或一组功能。
下面将更详细地讨论这些基本数据类型。
12.2 字符和二进制串类型
Oracle中的字符数据类型包括CHAR、VARCHAR2以及带“N“的相应”变体“（NCHAR和NVARCHAR2），这些字符数据类型能存储2,000字节或4,000字节的文本。这些文本会由数据库根据需要在不同字符集之间转换。字符集（chrarcter set）是各个字符的一种二进制表示（用位和字节表示）。目前有多种不同的字符集，每种字符集能表示不同的字符，例如：
q US7ASCII字符集是128字符的ASCII标准表示。它使用字节的低7位表示这128个字符。
q WE8ISO8859P1字符集是一种西欧字符集，不仅能不是128个ASCII字符，还能表示128个扩展字符，这个字符集使用了字节的全部8位。
在深入分析CHAR、VARCHAR2及带“N“的变体（NCHAR和NVARCHAR2）之前，我们先来简要地了解这些不同的字符集对于我们意味着什么，搞清楚这一点会很有帮助。
12.2.1 NLS概述
如前所述，NLS代表国家语言支持（National Language Support）。NLS是数据库的一个非常强大的特性，但是人们往往未能正确理解这个特性。NLS控制着数据的许多方面。例如，它控制着数据如何存储；还有我们在数据中是会看到多个逗号和一个句号（如12.000,000.01），还是会看到多个点号和一个逗号（如 12.000.000 ,01）。但是最重要的，它控制着以下两个方面：
q 文本数据持久存储在磁盘上时如何编码
q 透明地将数据从一个字符集转换到另一个字符集
正是这个透明的部分最让人困惑，它实在太透明了，以至于我们甚至不知道发生了这种转换。下面来看一个小例子：
假设你在数据库中用WE8ISO8859P1字符集存储8位的数据，但是你的某些客户使用的是一种7位字符集，如US7ASCII。这些客户不想要8位的数据，需要从数据库将数据转换为他们能用的形式。尽管听上去不错，但是如果你不知道会发生这种转换，就会发现，过一段时间后，数据会“丢失“字符，WE8ISO8859P1字符集中的某些字符在US7ASCII中并没有，这些字符会转换为US7ASCII中的某个字符。原因就在于这里发生了字符集转换。简而言之，如果你从数据库获取了使用字符集1的数据，将其转换为使用字符集2，再把这些数据插入回数据库中（完成以上的逆过程），就极有可能大幅修改数据。字符集转换过程通常会修改数据，而你往往会把一个较大的字符集（在此例中就是8位字符集）映射到一个较小的字符集（此例中的7位字符集）。这是一种有损转换（lossy conversion），字符就会被修改，这只是因为：较小的字符集不可能表示较大字符集中的每一个字符。但是这种转换必须发生。如果数据库以一种单字节字符集存储数据，但是客户（如一个Java应用，因为Java语言使用Unicode）希望数据采用多字节表示，就必须执行转换，只有这样客户应用才能使用这些数据。
可以很容易地看到字符集转换。例如，我有一个数据库，它的字符集设置为WE8ISO8859P1，这是一个典型的西欧字符集：
| ops$tkyte@ORA 10G > select * 2 from nls_database_parameters 3 where parameter = 'NLS_CHARACTERSET';
PARAMETER VALUE ------------------------------ ---------------------------------------- NLS_CHARACTERSET WE8ISO8859P1 |
现在，如果可以确保NLS_LANG设置为我的数据库字符集，如下（Windows用户要在注册表中修改/验证这个设置）：
| ops$tkyte@ORA 10G > host echo $NLS_LANG AMERICAN_AMERICA.WE8ISO8859P1 |
然后，创建一个表，并放入一些“8位”数据，对于只希望得到7位ASCII数据的客户来说（以下将这些客户称为“7位客户”），它们无法使用这些数据：
| ops$tkyte@ORA 10G > create table t ( data varchar2(1) ); Table created.
ops$tkyte@ORA 10G > insert into t values ( chr(224) ); 12.row created.
ops$tkyte@ORA 10G > insert into t values ( chr(225) ); 12.row created.
ops$tkyte@ORA 10G > insert into t values ( chr(226) ); 12.row created.
ops$tkyte@ORA 10G > select data, dump(data) dump 2 from t;
D DUMP - -------------------- à Typ=1 Len=1: 224 á Typ=1 Len=1: 225 á Typ=1 Len=1: 226 ops$tkyte@ORA 10G > commit; |
现在，如果打开另一个窗口，指定一个“7位ASCII”客户，就会看到完全不同的结果：
| [tkyte@desktop tkyte]$ export NLS_LANG=AMERICAN_AMERICA.US7ASCII [tkyte@desktop tkyte]$ sqlplus / SQL*Plus: Release 12..12.0 .4.0 - Production on Mon May 30 12.:58:46 2005 Copyright . 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 12..12.0 .4.0 - Production With the Partitioning, OLAP and Data Mining options ops$tkyte@ORA 10G > select data, dump(data) dump 2 from t;
D DUMP - -------------------- a Typ=1 Len=1: 224 a Typ=1 Len=1: 225 a Typ=12.Len=1: 226 |
注意，在这个7位会话（使用7位字符集的会话）中，接收到3次字母”a”，这些a头上没有区分号。不过，DUMP函数显示出数据库中实际上有3个不同的字符，而不都是字母“a”。数据库中数据没有变化，只是客户接收到的值有变化。而且实际上，如果这个客户要把数据库获取到宿主变量中，如下：
| ops$tkyte@ORA 10G > variable d varchar2(1) ops$tkyte@ORA 10G > variable r varchar2(20) ops$tkyte@ORA 10G > begin 2 select data, rowid into :d, :r from t where rownum = 1; 3 end; 4 / PL/SQL procedure successfully completed. |
对它们不做任何处理，只是将其发回到数据库中：
| ops$tkyte@ORA 10G > update t set data = :d where rowid = chartorowid(:r); 12.row updated. ops$tkyte@ORA 10G > commit; Commit complete. |
然后，在原先的8位会话（使用8位字符集的会话）中就会观察到：丢掉了原先的一个字符。它已经代之以低7位表示的a，而不是先前所示的那个奇特的á：
| ops$tkyte@ORA 10G > select data, dump(data) dump 2 from t; D DUMP - -------------------- a Typ=1 Len=1: 97 á Typ=1 Len=1: 225 á Typ=1 Len=1: 226 |
这就说明，如果环境中有不同的字符集，而且客户和数据库使用不同的NLS设置，这会产生直接的影响。这一点一定要注意，因为它在许多场合下都会起作用。例如，如果DBA使用EXP工具来抽取信息，他可能观察到以下警告：
| [tkyte@desktop tkyte]$ exp userid=/ tables=t Export: Release 12..12.0 .4.0 - Production on Mon May 30 12.:12.:09 2005 Copyright . 1982, 2004, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 12..12.0 .4.0 - Production With the Partitioning, OLAP and Data Mining options Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses WE8ISO8859P1 character set (possible charset conversion) ... |
要非常谨慎地处理这种警告。如果你想导出这个表，希望删除表后再使用IMP创建这个表，此时你会发现表中的所有数据现在都只是低7为数据！一定要当心这种并非有意的字符集转换。
另外还要注意，字符集转换一般都是必要的。如果客户希望数据采用某个特定的字符集，倘若向这个客户发送使用另一个字符集的信息，结果将是灾难性的。
注意 我强烈推荐所有人都应该通读Oracle Globalization Support Guide文档。其中深入地讨论了与NLS有关的问题，还涵盖了这里没有介绍的一些内容。如果有人要创建将全球使用的应用（设置只是跨洲使用），就很有必要阅读这个文档；或者就算是现在不需要创建这样的应用，也应该未雨绸缪，掌握这个文档中的信息。
既然我们对字符集已经有了初步的认识，并且了解了字符集可能对我们有怎样的影响，下面来介绍Oracle提供的各种字符串类型。
12.2.2 字符串
Oracle中有4种基本的字符串类型，分别是CHAR、VARCHAR2、NCHAR和NVARCHAR2。在Oracle中，所有串都以同样的格式存储。在数据库块上，最全面都有一个1～3字节的长度字段，其后才是数据，如果数据为NULL，长度字段则表示一个但字节值0xFF。
注意 Oracle中尾部的NULL列占用0字节存储空间，这说明，如果表中的“最后一列”为NULL，Oracle不会为之存储任何内容。如果最后两列都是NULL，那么对这两列都不会存储任何内容。但是，如果位于NULL列之后的某个列要求为not null（即不允许为null），Oracle会使用这一节介绍的null标志来指示这个列缺少值。
如果串的长度小于或等于250（0x01~0xFA），Oracle会使用1个字节来表示长度。对于所有长度超过250的串，都会在一个标志字节0xFE后跟有两个字节来表示长度。因此，如果有一个包含“Hello World”的VARCHAR2(80)，则在块中可能如图12.-1所示。
图12.-1 存储在一个VARCHAR2(80)中的Hello World
另一方面，如果在一个CHAR(80)中存储同样的数据，则可能如同12.-2所示。
图12.-2 存储在一个CHAR(80)中的Hello World
CHAR/NCHAR实际上只是伪装的VARCHAR2/NVARCHAR2，基于这一点，所以我认为其实只需要考虑这两种字符串类型：VARCHAR和NVARCHAR2。我从来没有见过哪个应用适合使用CHAR类型。因为CHAR类型总是会用空格填充得到的串，使之达到一个固定宽度，所以我们很快就会发现：不论在表段还是任何索引段中，CHAR都会占用最大的存储空间。这就够糟糕的了，避免使用CHAR/NCHAR类型还有另一个很重要的原因：在需要获取这些信息的应用中，CHAR/NCHAR类型还会带来混乱（很多应用存储了信息之后却无法“找到”所存储的数据）。其原因与字符串比较的规则有关，也与执行字符串比较的严格程度有关。下面通过一个小例子来展示这个问题，这里在一个简单的表中使用了‘Hello World’串：
| ops$tkyte@ORA 10G > create table t 2 ( char_column char(20), 3 varchar2_column varchar2(20) 4 ) 5 / Table created.
ops$tkyte@ORA 10G > insert into t values ( 'Hello World', 'Hello World' ); 12.row created.
ops$tkyte@ORA 10G > select * from t; CHAR_COLUMN VARCHAR2_COLUMN -------------------- -------------------- Hello World Hello World
ops$tkyte@ORA 10G > select * from t where char_column = 'Hello World'; CHAR_COLUMN VARCHAR2_COLUMN -------------------- -------------------- Hello World Hello World
ops$tkyte@ORA 10G > select * from t where varchar2_column = 'Hello World'; CHAR_COLUMN VARCHAR2_COLUMN -------------------- -------------------- Hello World Hello World |
到目前为止，两个列看上去好像是一样的，但实际上这里发生了一些隐式转换，在与CHAR列比较时，CHAR(12.)直接量（’Hello World’）已经提升为一个CHAR(20)，并在其中填充了空格。这种转换肯定已经发生了，因为Hello World……与没有尾部空格的Hello World并不相同。可以确认这两个串是截然不同的：
| ops$tkyte@ORA 10G > select * from t where char_column = varchar2_column; no rows selected |
它们彼此并不相等。我们要么必须用空格填充VARCHAR2_COLUMN列，使其长度到达20字节，要么必须从CHAR_COLUMN列截去尾部的空格，如下：
| ops$tkyte@ORA 10G > select * from t where trim(char_column) = varchar2_column; CHAR_COLUMN VARCHAR2_COLUMN -------------------- -------------------- Hello World Hello World
ops$tkyte@ORA 10G > select * from t where char_column = rpad( varchar2_column, 20 ); CHAR_COLUMN VARCHAR2_COLUMN -------------------- -------------------- Hello World Hello World |
注意 用空格填充VARCHAR2_COLUMN有很多方法，如使用CAST()函数。
对于使用变长串的应用，绑定输入时会出现问题，而且肯定会得到“没有找到数据“之类的错误：
| ops$tkyte@ORA 10G > variable varchar2_bv varchar2(20) ops$tkyte@ORA 10G > exec :varchar2_bv := 'Hello World'; PL/SQL procedure successfully completed.
ops$tkyte@ORA 10G > select * from t where char_column = :varchar2_bv; no rows selected ops$tkyte@ORA 10G > select * from t where varchar2_column = :varchar2_bv; CHAR_COLUMN VARCHAR2_COLUMN -------------------- -------------------- Hello World Hello World |
在此，搜索VARCHAR2串成功了，但是搜索CHAR列未成功。VARCHAR2绑定变量不会像字符串直接量那样提升为CHAR(20)。在此，许多程序员会形成这样一个观点，认为“绑定变量不能工作：所以必须使用直接量“。这实在是一个极其糟糕的决定。要完成绑定，解决方案是使用CHAR类型：
| ops$tkyte@ORA 10G > variable char_bv char(20) ops$tkyte@ORA 10G > exec :char_bv := 'Hello World';
PL/SQL procedure successfully completed. ops$tkyte@ORA 10G > ops$tkyte@ORA 10G > select * from t where char_column = :char_bv; CHAR_COLUMN VARCHAR2_COLUMN -------------------- -------------------- Hello World Hello World ops$tkyte@ORA 10G > select * from t where varchar2_column = :char_bv; no rows selected |
不过，如果混合使用并匹配VARCHAR2和CHAR，你就会不断地遭遇这个问题。不仅如此，开发人员现在还必须在应用中考虑字段宽度。如果开发人员喜欢使用RPAD()技巧将绑定变量转换为某种能与CHAR字段比较的类型（当然，与截断（TRIM）数据库列相比，填充绑定变量的做法更好一些，因为对列应用函数TRIM很容易导致无法使用该列上现有的索引），可能必须考虑到经过一段时间后列长度的变化。如果字段的大小有变化，应用就会受到影响，因为它必须修改字段宽度。
正是由于以下这些原因：定宽的存储空间可能导致表和相关索引比平常大出许多，还伴随着绑定变量问题，所以无论什么场合我都会避免使用CHAR类型。即便是对单字符的字段，我也想不出有什么必要使用CHAR类型，因为在这种情况下，这两种类型确实没有显著差异。VARCHAR2(1)和CHAR(1)从任何方面来讲都完全相同。此时，使用CHAR类型并没有什么有说服力的理由，为了避免混淆，所以我“一律排斥“，即使是CHAR(1)字段（即单字符字段）也不建议使用CHAR类型。
1. 字符串语法
这4种基本串类型的语法很简单，如表12.-1所示。
表12.-1 4种基本串类型
串类型 说明
VARCHAR2<SIZE><BYTE|CHAR> <SIZE>是介于1～4,000之间的一个数，表示最多占用
4,000字节的存储空间。在下一节中，我们将详细分析子句
中BYTE和CHAR修饰符的显著区别和细微差别
CHAR(<SIZE><BYTE|CHAR>) <SIZE>是介于1～2,000之间的一个数，表示最多占用
2,000字节的存储空间
NVARCHAR2(<SIZE>) <SIZE>是一个大于0的数，其上界由国家字符集指定
NCHAR(<SIZE>) <SIZE>是一个大于0的数，其上界由国家字符集指定
2. 字节或字符
VARCHAR2和CHAR类型支持两种指定长度的方法：
q 用字节指定：VARCHAR2(12. byte)。这能支持最多12.字节的数据， 在一个多字节字符集中，这可能这是两个字符。
q 用字符指定：VARCHAR2(12. char)。这将支持最多12.字符的数据，可能是多达40字节的信息。
使用UTF8之类的多字节字符集时，建议你在VARCHAR2/CHAR定义中使用CHAR修饰符，也就是说，使用VARCHAR2(80 CHAR)，而不是VARCHAR2(80),因为你的本意很可能是定义一个实际上能存储80字符数据的列。还可以使用会话参数或系统参数NLS_LENGTH_SEMANTICS来修改默认行为，即把默认设置BYTE改为CHAR。我不建议在系统级修改这个设置，而应该只是在你的数据库模式安装脚本中把这个设置作为ALTER SESSION设置的一部分。只要应用需要数据库有某组特定的NLS设置，这必然是一个“不友好“的应用。一般来讲，这种应用无法与其他不要求这些设置（而依赖于默认设置）的应用一同安装到数据库上。
还要记住重要的一点：VARCHAR2中存储的字节数上界是4,000。不过，即使你指定了VARCHAR2(4000 CHAR)，可能并不能在这个字段中放下4,000个字符。实际上，采用你选择的字符集时如果所有字符都要用4个字节来表示，那么这个字段中就只能放下12.000字符！
下面这个小例子展示了BYTE和CHAR之间的区别，并显示出上界的作用。我们将创建一个包括3列的表，前两列的长度分别是1字节和1字符，最后一列是4,000字符。需要说明，这个测试在一个多字节字符集数据库上完成，在此使用了字符集AL32UTF8，这个字符集支持最新版本的Unicode标准，采用一种变长方式对每个字符使用12.4个字节进行编码：
| ops$tkyte@O10GUTF> select * 2 from nls_database_parameters 3 where parameter = 'NLS_CHARACTERSET';
PARAMETER VALUE ------------------------------ -------------------- NLS_CHARACTERSET AL32UTF8 ops$tkyte@O10GUTF> create table t 2 ( a varchar2(1), 3 b varchar2(12.char), 4 c varchar2(4000 char) 5 ) 6 / Table created. |
现在，如果想在这个表中插入一个UTF字符，这个字符长度为2个字节，可以观察到以下结果：
| ops$tkyte@O10GUTF> insert into t (a) values (unistr('/00d6')); insert into t (a) values (unistr('/00d6')) * ERROR at line 1: ORA-12899: value too large for column "OPS$TKYTE"."T"."A" (actual: 2, maximum: 1) |
这个例子展示了两点：
VARCHAR2(1)的单位是字节，而不是字符。这里确实只有一个Unicode字符，但是它在一个字节中放不下。
将应用从单字节定宽字符集移植到一个多字节字符集时，可能会发现原来在字段中能放下的文本现在却无法放下。
第二点的原因是，在一个单字节字符集中，包含20个字符的字符串长度就是20字节，完全可以在一个VARCHAR2(20)中放下。不过，在一个多字节字符集中，20个字符的字符串长度可以到达80字节（如果每个字符用4个字节表示），这样一来，20个Unicode字符很可能无法在20个字节中放下。你可能会考虑将DDL修改为VARCHAR2(20 CHAR)，或者在运行DDL创建表时使用前面提到的NLS_LENGTH_SEMANTICS会话参数。
如果字段原本就是要包含一个字符，在这个字段中插入一个字符时，可以观察到以下结果：
| ops$tkyte@O10GUTF> insert into t (b) values (unistr('/00d6')); 12.row created. ops$tkyte@O10GUTF> select length(b), lengthb(b), dump(b) dump from t; LENGTH(B) LENGTHB(B) DUMP ---------- ---------- -------------------- 1 2 Typ=12.Len=2: 195,150 |
这个INSERT成功了，而且可以看到，所插入数据的长度（LENGTH）就是一个字符，所有字符串函数都以字符为单位工作。这个字段的长度是一个字符，但是LENGTHB函数（字节长度）显示这个字段占用了2字节的存储空间，另外DUMP函数显示了这些字节到底是什么。这个例子展示了人们使用多字节字符集时遇到的一个相当常见的问题，即VARCHAR2(N)并不一定存储N个字符，而只是存储N个字节。
人们经常遇到的另一个问题是：VARCHAR2的最大字节长度为4,000，而CHAR的最大字节长度为2,000。
| ops$tkyte@O10GUTF> declare 2 l_data varchar2(4000 char); 3 l_ch varchar2(12.char) := unistr( '/00d6' ); 4 begin 5 l_data := rpad( l_ch, 4000, l_ch ); 6 insert into t ( c ) values ( l_data ); 7 end; 8 / declare * ERROR at line 1: ORA-01461: can bind a LONG value only for insert into a LONG column ORA-06512: at line 6 |
在此显示出，一个4,000字符的字符串实际上长度为8,000字节，这样一个字符串无法永久地存储在一个VARCHAR2(4000 CHAR)字段中。这个字符串能放在PL/SQL变量中，因为在PL/SQL中VARCHAR2最大可以到达32KB。不过，存储在表中时，VARCHAR2则被硬性限制为最多只能存放4,000字节。我们可以成功地存储其中2,000个字符：
| ops$tkyte@O10GUTF> declare 2 l_data varchar2(4000 char); 3 l_ch varchar2(12.char) := unistr( '/00d6' ); 4 begin 5 l_data := rpad( l_ch, 2000, l_ch ); 6 insert into t ( c ) values ( l_data ); 7 end; 8 / PL/SQL procedure successfully completed. ops$tkyte@O10GUTF> select length( c ), lengthb( c ) 2 from t 3 where c is not null; LENGTH(C) LENGTHB(C) ---------- ---------- 2000 4000 |
如你所见，它占用了4,000字节的存储空间。
3. NVARCHAR2和NCHAR
为了完整地介绍字符串数据类型，下面来看NVARCHAR2和NCHAR，它们有什么用呢？如果系统中需要管理和存储多种字符集，就可以使用这两个字符串类型。通常会有这样一种情况：在一个数据库中，主要字符集是一个单字节的定宽字符集（如WE8ISO8859P1），但是还需要维护和存储一些多字节数据。许多系统都有遗留的数据，但是同时还要为一些新应用支持多字节数据；或者系统中大多数操作都需要单字节字符集的高效率（如果一个串中每个字符可能存储不同数目的字节，与这个串相比，定宽字符串上的串操作效率更高），但某些情况下又需要多字节数据的灵活性。
NVARCHAR2和NCHAR数据类型就支持这种需求。总的来讲，它们与相应的VARCHAR2和CHAR是一样的，只是有以下不同：
q 文本采用数据库的国家字符集来存储和管理，而不是默认字符集。
q 长度总是字符数，而CHAR/VARCHAR2可能会指定是字节还是字符。
在Oracle9i及以后的版本中，数据库的国家字符集有两个可取值：UTF8或AL16UTF16（9i中是UTF16， 10g 中是AL16UTF16）。这使得NCHAR和NVARCHAR类型很适于只存储多字节数据，这是对Oracle以前版本的一个改变（Oracle8i及以前版本允许为国家字符集选择任何字符集）。
12.3 二进制串：RAW类型
Oracle除了支持文本，还支持二进制数据的存储。前面讨论了CHAR和VARCHAR2类型需要进行字符集转换，而二进制数据不会做这种字符集转换。因此，二进制数据类型不适合存储用户提供的文本，而适于存储加密信息，加密数据不是“文本“，而是原文本的一个二进制表示、包含二进制标记信息的字处理文档，等等。如果数据库不认为这些数据是”文本“，这些数据就应该采用一种二进制数据类型来存储，另外不应该应用字符集转换的数据也要使用二进制数据类型存储。
Oracle支持3种数据类型来存储二进制数据：
q RAW类型，这是这一节强调的重点，它很适合存储多达2,000字节的RAW数据。
q BLOB类型，它支持更大的二进制数据，我们将在本章“LOB类型“一节中再做介绍。
q LONG RAW类型，这是为支持向后兼容性提供的，新应用不应考虑使用这个类型。
二进制RAW类型的语法很简单：
| RAW（<size>） |
例如，以下代码创建了一个每行能存储12.字节二进制信息的表：
| ops$tkyte@ORA10GR1> create table t ( raw_data raw(12.) ); Table created. |
从磁盘上的存储来看，RAW类型与VARCHAR2类型很相似。RAW类型是一个变长的二进制串，这说明前面创建的表T可以存储1～12.字节的二进制数据。它不会像CHAR类型那样用空格填充。
处理RAW数据时，你可能会发现它被隐式地转换为一个VARCHAR2类型，也就是说，诸如SQL*Plus之类的许多工具不会直接显示RAW数据，而是会将其转换为一种十六进制格式来显示。在以下例子中，我们使用SYS_GUID()在表中创建了一些二进制数据，SYS_GUID()是一个内置函数，将返回一个全局惟一的12.字节RAW串（GUID就代表全局惟一标识符，globally unique identifier）：
| ops$tkyte@ORA10GR1> insert into t values ( sys_guid() ); 12.row created. ops$tkyte@ORA10GR1> select * from t;
RAW_DATA -------------------------------- FD1EB03D3718077BE 030007F 01002FF5 |
在此，你会马上注意到两点。首先，RAW数据看上去就像是一个字符串。SQL*Plus就是以字符串形式获取和打印RAW数据，但是RAW数据在磁盘上并不存储为字符串。SQL*Plus不能在屏幕上打印任意的二进制数据，因为这可能对显示有严重的副作用。要记住，二进制数据可能包含诸如回车或换行等控制字符，还可能是一个Ctrl+G字符，这会导致终端发出“嘟嘟“的叫声。
其次，RAW数据看上去远远大于12.字节，实际上，在这个例子中，你会看到32个字符。这是因为，每个二进制字节都显示为两个十六进制字符。所存储的RAW数据其实长度就是12.字节，可以使用Oracle DUMP函数确认这一点。在此，我“转储“了这个二进制串的值，并使用了一个可选参数来指定显示各个字节值时应使用哪一种进制。这里使用了基数12.，从而能将转储的结果与前面的串进行比较：
| ops$tkyte@ORA10GR1> select dump(raw_data,12.) from t;
DUMP(RAW_DATA,12.) ------------------------------------------------------------------------------- Typ=23 Len=12.: fd,12.,b0,3d,37,12.,7,7b,e0,30,0, 7f ,12.0, 2f ,f5 |
DUMP显示出，这个二进制串实际上长度为12.字节（LEN=12.），另外还逐字节地显示了这个二进制数据。可以看到，这个转储显示与SQL*Plus将RAW数据获取为一个串时所执行的隐式转换是匹配的。另一个反向上（插入）也会执行隐式转换：
| ops$tkyte@ORA10GR1> insert into t values ( 'abcdef' ); 12.row created. |
这不会插入串abcdef，而会插入一个3字节的RAW数据，其字节分别是AB、CD、EF，如果用十进制表示则为字节171、205、239。如果试图使用一个包含非法12.进制字符的串，就会收到一个错误消息：
| ops$tkyte@ORA10GR1> insert into t values ( 'abcdefgh' ); insert into t values ( 'abcdefgh' ) * ERROR at line 1: ORA-01465: invalid hex number |
RAW类型可以加索引，还能在谓词中使用，它与其他任何数据类型有同样的功能。不过，必须当心避免不希望的隐式转换，而且必须知道确实会发生隐式转换。
在任何情况下我都喜欢使用显示转换，而且推荐这种做法，可以使用以下内置函数来执行这种操作：
q HEXTORAW：将十六进制字符串转换为RAW类型
q RAWTOHEX：将RAW串转换为十六进制串
SQL*Plus将RAW类型获取为一个串时，会隐式地调用RAWTOHEX函数，而插入串时会隐式地调用HEXTORAW函数。应该避免隐式转换，而在编写代码时总是使用显示转换，这是一个很好的实践做法。所以前面的例子应该写作：
| ops$tkyte@ORA10GR1> select rawtohex(raw_data) from t;
RAWTOHEX(RAW_DATA) -------------------------------- FD1EB03D3718077BE 030007F 01002FF5
ops$tkyte@ORA10GR1> insert into t values ( hextoraw('abcdef') ); 12.row created. |
12.4 数值类型
Oracle 10g 支持3种固有数据类型来存储数值。Oracle9i Release 2及以前的版本只支持一种适合存储数值数据的固有数据类型。以下所列的数据类型中，NUMBER类型在所有Oracle版本中都得到支持，后面两种类型是新的数据类型，只有Oracle 10g 及以后的版本才支持：
q NUMBE：Oracle NUMBER类型能以极大的精度存储数值，具体来讲，精度可达38位。其底层数据格式类似一种“封包小数“表示。Oracle NUMBER类型是一种变长格式，长度为0～22字节。它可以存储小到10e-130、大到（但不包括）10e126的任何数值。这是目前最为常用的数值类型。
q BINARY_FLOAT：这是一种IEEE固有的单精度浮点数。它在磁盘上会占用5字节的存储空间：其中4个固定字节用于存储浮点数，另外还有一个长度字节。BINARY_FLOAT能存储有6为精度、范围在～±1038.53的数值。
q BINARY_DOUBLE：这是一种IEEE固有的双精度浮点数。它在磁盘上会占用9字节的存储空间：其中8个固定字节用于存储浮点数，还有一个长度字节。BINARY_DOUBLE能存储有12.位精度、范围在～±10308.25的数值。
从以上简要的概述可以看到，Oracle NUMBER类型比BINARY_FLOAT和BINARY_DOUBLE类型的精度大得多，但是取值范围却远远小于BINARY_DOUBLE。也就是说，用NUMBER类型可以很精确地存储数值（有很多有效数字），但是用BINARY_FLOAT和BINARY_DOUBLE类型可以存储更小或更大的数值。下面举一个简单的例子，我们将用不同的数据类型来创建一个表，查看给定相同的输入时，各个列中会存储什么内容：
| ops$tkyte@ORA10GR1> create table t 2 ( num_col number, 3 float_col binary_float, 4 dbl_col binary_double 5 ) 6 / Table created.
ops$tkyte@ORA10GR1> insert into t ( num_col, float_col, dbl_col ) 2 values ( 1234567890.0987654321, 3 1234567890.0987654321, 4 1234567890.0987654321 ); 12.row created. ops$tkyte@ORA10GR1> set numformat 99999999999.99999999999 ops$tkyte@ORA10GR1> select * from t; NUM_COL FLOAT_COL DBL_COL ------------------------------------ ------------------------------------ ------------------------------------ 1234567890.09876543210 1234567940.00000000000 1234567890.09876540000 |
注意，NUM_COL会按我们提供的输入原样返回同一个数。输入数中有效数字远远没有达到38位（这里提供了一个有20位有效数字的数），所以将完全保留原来的数。不过，使用新的BINARY_FLOAT类型时，FLOAT_COL不能准确地表示这个数。实际上，它只正确保留了7位。DBL_COL则要好多了，它正确地表示了这个数中的12.位。不过，总的说来，由此可以很好地说明BINARY_FLOAT和BINARY_DOUBLE类型在金融应用中不适用！如果尝试不同的值，可能会看到不同的结果：
| ops$tkyte@ORA10GR1> delete from t; 12.row deleted.
ops$tkyte@ORA10GR1> insert into t ( num_col, float_col, dbl_col ) 2 values ( 9999999999.9999999999, 3 9999999999.9999999999, 4 9999999999.9999999999 ); 12.row created.
ops$tkyte@ORA10GR1> select * from t; NUM_COL FLOAT_COL DBL_COL ------------------------ ------------------------ ------------------------ 9999999999.99999999990 10000000000.00000000000 10000000000.00000000000 |
NUM_COL又一次正确地表示了这个数，但是FLOAT_COL和DBL_COL却未能做到。这并不是说NUMBER类型能以“无限的“精度/准确性来存储数据，它的精度只不过相当大而已（但并不是无限的）。NUMBER类型也有可能不正确地表示数值，这种情况很容易观察到：
| ops$tkyte@ORA10GR1> delete from t; 12.row deleted.
ops$tkyte@ORA10GR1> insert into t ( num_col ) 2 values ( 123 * 1e20 + 123*12.-20 ) ; 12.row created.
ops$tkyte@ORA10GR1> set numformat 999999999999999999999999.999999999999999999999999 ops$tkyte@ORA10GR1> select num_col, 123*1e20, 123*12.-20 from t; NUM_COL -------------------------------------------------- 123*1E20 -------------------------------------------------- 123*12.-20 -------------------------------------------------- 12300000000000000000000.000000000000000000000000 12300000000000000000000.000000000000000000000000 .000000000000000001230000 |
可以看到，如果把一个非常大的数（123*12.20）和一个非常小的数（123*12.-20）放在一起，就会丢失精度，因为这个算术运算需要的精度不止38位。就较大数（123*12.20）本身而言，它能得到“忠实“的表示，较小数（123*12.-20）也能精确地表示，但是较大数加上较小数的结果却不能如实表示。这不只是一个显示/格式化的问题，可以做以下验证：
| ops$tkyte@ORA10GR1> select num_col from t where num_col = 123*1e20; NUM_COL -------------------------------------------------- 12300000000000000000000.000000000000000000000000 |
NUM_COL中的值等于123*12.20，而不是我们真正想插入的值。
12.4.1 NUMBER类型的语法和用法
NUMBER类型的语法很简单：
| NUMBER( p,s ) |
在此P和S是可选的，用于指定：
q 精度（precision），或总位数。默认情况下，精度为38位，取值范围是1～38之间。也可以用字符*表示38。
q 小数位置（scale），或小数点右边的位数。小数位数的合法值为-48～127，其默认值取决于是否指定了精度。如果没有知道精度，小数位数则默认有最大的取值区间。如果指定了精度，小数位数默认为0（小数点右边一位都没有）。例如，定义为NUMBER的列会存储浮点数（有小数），而NUMBER(38)只存储整数数据（没有小数），因为在第二种情况下小数位数默认为0.
应该把精度和小数位数考虑为对数据的“编辑“，从某种程度上讲它们可以算是一种完整性工具。精度和小数位数根本不会影响数据在磁盘上如何存储，而只会影响允许有哪些值以及数值如何舍入（round）。例如，如果某个值超过了所允许的精度，Oracle就会返回一个错误：
| ops$tkyte@ORA10GR1> create table t ( num_col number(5,0) ); Table created.
ops$tkyte@ORA10GR1> insert into t (num_col) values ( 12345 ); 12.row created.
ops$tkyte@ORA10GR1> insert into t (num_col) values ( 123456 ); insert into t (num_col) values ( 123456 ) * ERROR at line 1: ORA-01438: value larger than specified precision allows for this column |
因此，可以使用精度来保证某些数据完整性约束。在这个例子中，NUM_COL列不允许多于5位。
另一方面，小数位数可以用于控制数值的“舍入“，例如：
| ops$tkyte@ORA10GR1> create table t ( msg varchar2(12.), num_col number(5,2) ); Table created.
ops$tkyte@ORA10GR1> insert into t (msg,num_col) values ( '123.45', 123.45 ); 12.row created.
ops$tkyte@ORA10GR1> insert into t (msg,num_col) values ( '123.456', 123.456 ); 12.row created.
ops$tkyte@ORA10GR1> select * from t; MSG NUM_COL ------------ --------------- 123.45 123.45 123.456 123.46 |
可以注意到，尽管数值123.456超过了5位，但这一次插入成功了，没有报错。这是因为，这个例子中利用小数位数将123.456“舍入“为只有两位小数，这就得到了123.46，再根据精度来验证123.46，发现满足精度要求，所以插入成功。不过，如果试图执行以下插入，则会失败：
| ops$tkyte@ORA10GR1> insert into t (msg,num_col) values ( '1234', 1234 ); insert into t (msg,num_col) values ( '1234', 1234 ) * ERROR at line 1: ORA-01438: value larger than specified precision allows for this column |
这是因为，数值1234.00的位数超过了5位。指定小数位数为2时，小数点左边最多只有3位，右边有2位。因此，这个数不满足精度要求。NUMBER(5,2)列可以存储介于999.99～-999.99之间的所有值。
允许小数位数在-84～127之间变化，这好像很奇怪。为什么小数位数可以为负值，这有什么用意？其作用是允许对小数点左边的值舍入。就像NUMBER(5,2)将值舍入为最接近0.01一样，NUMBER(5,-2)会把数值舍入为与之最接近的100，例如：
| ops$tkyte@ORA10GR1> create table t ( msg varchar2(12.), num_col number(5,-2) ); Table created.
ops$tkyte@ORA10GR1> insert into t (msg,num_col) values ( '123.45', 123.45 ); 12.row created.
ops$tkyte@ORA10GR1> insert into t (msg,num_col) values ( '123.456', 123.456 ); 12.row created.
ops$tkyte@ORA10GR1> select * from t; MSG NUM_COL ---------- ---------- 123.45 100 123.456 100 |
这些数舍入为与之最接近的100.精度还是5位，但是现在小数点左边允许有7位（包括尾部的两个0）：
| ops$tkyte@ORA10GR1> insert into t (msg,num_col) values ( '1234567', 1234567 ); 12.row created. ops$tkyte@ORA10GR1> select * from t;
MSG NUM_COL ---------- ---------- 123.45 100 123.456 100 1234567 1234600 ops$tkyte@ORA10GR1> insert into t (msg,num_col) values ( '12345678', 12345678 ); insert into t (msg,num_col) values ( '12345678', 12345678 ) * ERROR at line 1: ORA-01438: value larger than specified precision allows for this column |
因此，精度指示了舍入后数值中允许有多少位，并使用小数位数来确定如何舍入。精度是一个完整性约束，而小数位数是一种“编辑“。
有一点很有意思，也很有用，NUMBER类型实际上是磁盘上的一个变长数据类型，会占用0～22字节的存储空间。很多情况下，程序员会认为数值类型是一个定长类型，因为在使用2或4字节整数以及4或8字节单精度浮点数编程时，他们看到的往往就是定长类型。Oracle NUMBER类型与变长字符串很类似。下面通过例子来看看如果数中包含不同数目的有效数字会发生什么情况。我们将创建一个包含两个NUMBER列的表，并用分别有2、4、6、…、28位有效数字的多个数填充第一列。然后再将各个值分别加1，填充第二列：
| ops$tkyte@ORA10GR1> create table t ( x number, y number ); Table created.
ops$tkyte@ORA10GR1> insert into t ( x ) 2 select to_number(rpad('9',rownum*2,'9')) 3 from all_objects 4 where rownum <= 12.; 12. rows created.
ops$tkyte@ORA10GR1> update t set y = x+1; 12. rows updated. |
下面使用内置VSIZE函数，它能显示列占用多大的存储空间，从而可以看到每行中两个数的大小有怎样的差异：
| ops$tkyte@ORA10GR1> set numformat 99999999999999999999999999999 ops$tkyte@ORA10GR1> column v1 format 99 ops$tkyte@ORA10GR1> column v2 format 99 ops$tkyte@ORA10GR1> select x, y, vsize(x) v1, vsize(y) v2 2 from t order by x; X Y V1 V2 ------------------------------ ------------------------------ --- --- 99 100 2 2 9999 10000 3 2 999999 1000000 4 2 99999999 100000000 5 2 9999999999 10000000000 6 2 999999999999 1000000000000 7 2 99999999999999 100000000000000 8 2 9999999999999999 10000000000000000 9 2 999999999999999999 1000000000000000000 12. 2 99999999999999999999 100000000000000000000 12. 2 9999999999999999999999 10000000000000000000000 12. 2 999999999999999999999999 1000000000000000000000000 12. 2 99999999999999999999999999 100000000000000000000000000 12. 2 9999999999999999999999999999 10000000000000000000000000000 12. 2 12. rows selected. |
可以看到，随着X的有效数字数目的增加，需要越来越多的存储空间。每增加两位有效数字，就需要另外一个字节的存储空间。但是对各个数加1后得到的数总是只占2个字节。Oracle存储一个数时，会存储尽可能少的内容来表示这个数。为此会存储有效数字、用于指定小数点位置的一个指数，以及有关数值符号的信息（正或负）。因此，数中包含的有效数字越多，占用的存储空间就越大。
最后一点解释了为什么有必要了解数值在变宽字段中存储方式。试图确定一个表的大小时（例如，明确一个表中的12.000,000行需要多少存储空间），就必须仔细考虑NUMBER字段。这些数会占2字节还是12.字节？平均大小是多少？这样一来，如果没有代表性的测试数据，要准确地确定表的大小非常困难。你可以得到最坏情况下和最好情况下的大小，但是实际的大小往往是介于这二者之间的某个值。
12.4.2 BINARY_FLOAT/BINARY_DOUBLE类型的语法和用法
Oracle 10g 引入了两种新的数值类型来存储数据；在Oracle 10g 之前的所有版本中都没有这两种类型。它们就是许多程序员过去常用的IEEE标准浮点数。要全面地了解这些数值类型是怎样的，以及它们如何实现，建议你阅读http://en.wikipedia.org/wiki/Floating-point。需要指出，在这个参考文档中对于浮点数的基本定义有以下描述（请注意我着重强调的部分）：
浮点数是一个有理数子集中一个数的数字表示，通常用于在计算机上近似一个任意的实数。特别是，它表示一个整数或浮点数（有效数，或正式地说法是尾数）乘以一个底数（在计算机中通常是2）的某个整数次幂（指数）。底数为2时，这就是二进制的科学计数法（通常的科学计数法底数为12.）。
浮点数用于近似数值；它们没有前面所述的内置Oracle NUMBER类型那么精确。浮点数常用在科学计算中，由于允许在硬件（CPU、芯片）上执行运算，而不是在Oracle子例程中运算，所以在多种不同类型的应用中都很有用。因此，如果在一个科学计算应用中执行实数处理，算术运算的速度会快得多，不过你可能不希望使用浮点数来存储金融信息。
要在表中声明这种类型的列，语法相当简单：
| BINARY_FLOAT BINARY_DOUBLE |
仅此而已。这些类型没有任何选项。
12.4.3 非固有数据类型
除了NUMBER、BINARY_FLOAT和BINARY_DOUBLE类型，Oracle在语法上还支持以下数值数据类型：
q NUMERIC(p,s)：完全映射至NUMBER(p,s)。如果p未指定，则默认为38.
q DECIMAL(p,s)或DEC(p,s)：完全映射至NUMBER(p,s)。如果p为指定，则默认为38.
q INTEGER或INT：完全映射至NUMBER(38)类型。
q SMALLINT：完全映射至NUMBER(38)类型。
q FLOAT(b)：映射至NUMBER类型。
q DOUBLE PRECISION：映射至NUMBER类型。
q REAL：映射至NUMBER类型。
注意 这里我指出“在语法上支持“，这是指CREATE语句可以使用这些数据类型，但是在底层实际上它们都只是NUMBER类型。准确地将，Oracle 10g Release 1及以后的版本中有3种固有数值格式，Oracle9i Release 2及以前的版本中只有1种固有数值格式。使用其他的任何数值数据类型总是会映射到固有的Oracle NUMBER类型。
12.4.4 性能考虑
一般而言，Oracle NUMBER类型对大多数应用来讲都是最佳的选择。不过，这个类型会带来一些性能影响。Oracle NUMBER类型是一种软件数据类型，在Oracle软件本身中实现。我们不能使用固有硬件操作将两个NUMBER类型相加，这要在软件中模拟。不过，浮点数没有这种实现。将两个浮点数相加时，Oracle会使用硬件来执行运算。
很容易看出这一点。如果创建一个表，其中包含大约50,000行，分别使用NUMBER和BINARY_FLOAT/BINARY_DOUBLE类型在其中放入同样的数据，如下：
| ops$tkyte@ORA 10G > create table t 2 ( num_type number, 3 float_type binary_float, 4 double_type binary_double 5 ) 6 / Table created.
ops$tkyte@ORA 10G > insert /*+ APPEND */ into t 2 select rownum, rownum, rownum 3 from all_objects 4 / 48970 rows created.
ops$tkyte@ORA 10G > commit; Commit complete. |
再对各种类型的列执行同样的查询，在此使用一个复杂的数学函数，如NL（自然对数）。会观察到它们的CPU利用率存在显著差异：
| select sum(ln(num_type)) from t call count cpu elapsed ------- ------ -------- ---------- total 4 2.73 2.73 select sum(ln(float_type)) from t call count cpu elapsed ------- ------ -------- ---------- total 4 0.06 0.12. select sum(ln(double_type)) from t call count cpu elapsed ------- ------ -------- ---------- total 4 0.05 0.12. |
Oracle NUMBER类型使用的CPU时间是浮点数类型的50倍。不过，你要记住，从这3个查询中得到的答案并不完全相同！浮点数是数值的一个近似值，精度在6～12.位之间。从NUMBER类型得到的答案比从浮点数得到的答案“精确“得多。但是如果你在对科学数据执行数据挖掘或进行复杂的数值分析，这种精度损失往往是可以接受的，另外可能会得到非常显著的性能提升。
注意 如果你对浮点数运算的具体细节以及所带来的精度损失感兴趣，可以参见http://docs.sum.com/source/806-3568/ncg_goldberg.html。
需要注意，我们可以鱼和熊掌兼得。通过使用内置的CAST函数，可以对Oracle NUMBER类型执行一种实时的转换，在对其执行复杂数学运算之前先将其转换为一种浮点数类型。这样一来，所用CPU时间就与使用固有浮点类型所用的CPU时间非常接近：
| select sum(ln(cast( num_type as binary_double ) )) from t call count cpu elapsed ------- ------ -------- ---------- total 4 0.12. 0.12. |
这说明，我们可以非常精确地存储数据，如果需要提供速度，浮点类型则远远超过Oracle NUMBER类型，此时可以使用CAST函数来达到提速的目标。
12.5 LONG类型
Oracle中的LONG类型有两种：
q LONG文本类型，能存储2GB的文本。与VARCHAR2或CHAR类型一样，存储在LONG类型中的文本要进行字符集转换。
q LONG RAW类型，能存储2GB的原始二进制数据（不用进行字符集转换的数据）。
从Oracle 6开始就已经支持LONG类型，那时限制为只能存储64KB的数据。在Oracle 7中，提升为可以存储多达2GB的数据，但是等发布Oracle 8时，这种类型被LOB类型所取代，稍后将讨论LOB类型。
在此并不解释如何使用LONG类型，而是会解释为什么你不希望在应用中使用LONG（或LONG RAW）类型。首先要注意的是，Oracle文档在如何处理LONG类型方面描述得很明确。Oracle SQL Reference手册指出：
不要创建带LONG列的表，而应该使用LOB列（CLOB、NCLOB、BLOB）。支持LONG列只是为了保证向后兼容性。
12.5.1 LONG和LONG RAW类型的限制
LONG和LONG RAW类型存在很多限制，如表12.-2所列。尽管这可能有点超前（现在还没有讲到LOB类型），不过在此我还是增加了一列，指出相应的LOB类型（用以取代LONG/LONG RAW）类型是否也有同样的限制。
表12.-2 LONG类型与LOB类型的比较
LONG/LONG RAW类型 CLOB/BLOB类型
每个表中只能有一个LONG或LONG RAW列 每个表可以有最多12.000个CLOB或BLOB类型的列
定义用户定义的类型时，不能有LONG/LONG 用户定义的类型完成可以使用CLOB和BLOB类型
RAW类型的属性
不能在WHERE子句中引用LONG类型 WHERE子句中可以引用LOB类型，而且DBMS_LOB包
中提供了大量函数来处理LOB类型
除了NOT NULL之外，完整性约束中不能引用 完整性约束中可以引用LOB类型
LONG类型
LONG类型不支持分布式事务 LOB确实支持分布式事务
LONG类型不能使用基本或高级复制技术来复制 LOB完全支持复制
LONG列不能在GROUP BY、ORDER BY或 只要对LOB应用一个函数，将其转换为一个标量SQL类型，
CONNECT BY子句中引用，也不能在使用了 如VARCHAR2、NUMBER或DATE，LOB就可以出现在
DISTINCT、UNIQUE、INTERSECT、MINUS 这些子句中
或UNION的查询中使用
PL/SQL函数/过程不能接受LONG类型的输入 PL/SQL可以充分处理LOB类型
SQL内置函数不能应用于LONG列（如SUBSTR） SQL函数可以应用于LOB类型
CREATE TABLE AS SELECT语句中不能使用 LOB支持CREATE TABLE AS SELECT
LONG类型
在包含LONG类型的表上不能使用ALTER TABLE MOVE 可以移动包含LOB的表
可以看到，表12.-2很长；如果表中有一个LONG列，那么很多事情都不能做。对于所有新的应用，甚至根本不该考虑使用LONG类型。相反，应该使用适当的LOB类型。对于现有的应用，如果受到表12.-2所列的某个限制，就应该认真地考虑将LONG类型转换为相应的LOB类型。由于已经做了充分考虑来提供向后兼容性，所以编写为使用LONG类型的应用也能透明地使用LOB类型。
注意 无须多说，将生产系统从LONG修改为LOB类型之前，应当对你的应用执行一个全面的字典测试。
12.5.2 处理遗留的LONG类型
经常会问到这样一个问题：“那如何考虑Oracle中的数据字典呢？“数据字典中散布着LONG列，这就使得字典列的使用很成问题。例如，不能使用SQL搜索ALL_VIEWS字典视图来找出包含文本HELLO的所有视图：
| ops$tkyte@ORA 10G > select * 2 from all_views 3 where text like '%HELLO%'; where text like '%HELLO%' * ERROR at line 3: ORA-00932: inconsistent datatypes: expected NUMBER got LONG |
这个问题并不只是ALL_VIEWS视图才有，许多视图都存在同样的问题：
| ops$tkyte@ORA 10G > select table_name, column_name 2 from dba_tab_columns 3 where data_type in ( 'LONG', 'LONG RAW' ) 4 and owner = 'SYS' 5 and table_name like 'DBA%';
TABLE_NAME COLUMN_NAME ------------------------------ ------------------------------ DBA_VIEWS TEXT DBA_TRIGGERS TRIGGER_BODY DBA_TAB_SUBPARTITIONS HIGH_VALUE DBA_TAB_PARTITIONS HIGH_VALUE DBA_TAB_COLUMNS DATA_DEFAULT DBA_TAB_COLS DATA_DEFAULT DBA_SUMMARY_AGGREGATES MEASURE DBA_SUMMARIES QUERY DBA_SUBPARTITION_TEMPLATES HIGH_BOUND DBA_SQLTUNE_PLANS OTHER DBA_SNAPSHOTS QUERY DBA_REGISTERED_SNAPSHOTS QUERY_TXT DBA_REGISTERED_MVIEWS QUERY_TXT DBA_OUTLINES SQL_TEXT DBA_NESTED_TABLE_COLS DATA_DEFAULT DBA_MVIEW_ANALYSIS QUERY DBA_MVIEW_AGGREGATES MEASURE DBA_MVIEWS QUERY DBA_IND_SUBPARTITIONS HIGH_VALUE DBA_IND_PARTITIONS HIGH_VALUE DBA_IND_EXPRESSIONS COLUMN_EXPRESSION DBA_CONSTRAINTS SEARCH_CONDITION DBA_CLUSTER_HASH_EXPRESSIONS HASH_EXPRESSION |
那么问题到底出在哪里呢？如果你想在SQL中使用这些列，就需要将它们转换为一种对SQL友好的类型。可以使用一个用户定义的函数来做到这一点。以下例子展示了如何使用一个LONG SUBSTR函数来达到这个目的，这个函数允许将任何4,000字节的LONG类型转换为一个VARCHAR2，以便用于SQL。完成后，就能执行以下查询：
| ops$tkyte@ORA 10G > select * 2 from ( 3 select owner, view_name, 4 long_help.substr_of( 'select text 5 from dba_views 6 where owner = :owner 7 and view_name = :view_name', 8 1, 4000, 9 'owner', owner, 12. 'view_name', view_name ) substr_of_view_text 12. from dba_views 12. where owner = user 12. ) 12. where upper(substr_of_view_text) like '%INNER%' 12. / |
你已经将VIEW_TEXT列的前4,000字节由LONG转换为VARCHAR2，现在可以对它使用谓词了。使用同样的技术，你还可以对LONG类型实现你自己的INSTR、LIKE函数。在这本书里，我只想说明如何得到一个LONG类型的子串。
我们要实现的包有以下规范：
| ops$tkyte@ORA 10G > create or replace package long_help 2 authid current_user 3 as 4 function substr_of 5 ( p_query in varchar2, 6 p_from in number, 7 p_for in number, 8 p_name 12.in varchar2 default NULL, 9 p_bind 12.in varchar2 default NULL, 12. p_name 2 in varchar2 default NULL, 12. p_bind 2 in varchar2 default NULL, 12. p_name 3 in varchar2 default NULL, 12. p_bind 3 in varchar2 default NULL, 12. p_name 4 in varchar2 default NULL, 12. p_bind 4 in varchar2 default NULL ) 12. return varchar2; 12. end; 12. / Package created. |
注意在第2行上，我们指定了AUTHID CURRENT_USER。这使得这个包会作为调用者运行，拥有所有角色和权限。这一点很重要，原因有两个。首先，我们希望数据库安全性不要受到破坏，这个包只返回允许我们（调用者）看到的列子串。其次，我们希望只在数据库中将这个包安装一次，就能一直使用它的功能；使用调用者权限可以保证这一点。如果我们使用PL/SQL的默认安全模型（定义者权限，define right），这个包会以所有者的权限来运行，这样一来，它就只能看到包所有者能看到的数据，这可能不包括允许调用者看到的数据集。
函数SUBSTR_OF的基本思想是取一个查询，这个查询最多只选择一行和一列：即我们感兴趣的LONG值。如果需要，SUBSTR_OF会解析这个查询，为之绑定输入，并通过查询获取结果，返回LONG值中必要的部分。
包体（实现）最前面声明了两个全局变量。G_CURSOR变量保证一个持久游标在会话期间一直打开。这是为了避免反复打开和关闭游标，并避免不必要地过多解析SQL。第二个全局变量G_QUERY用于记住这个包中已解析的上一个SQL查询的文本。只要查询保持不变，就只需将其解析一次。因此，即使一个查询中查询了5,000行，只要我们传入这个函数的SQL查询不变，就只会有一个解析调用：
| ops$tkyte@ORA 10G > create or replace package body long_help 2 as 3 4 g_cursor number := dbms_sql.open_cursor; 5 g_query varchar2(32765); 6 |
这个包中接下来是一个私有过程：BIND_VARIABLE，我们用这个过程来绑定调用者传入的输入。在此把它实现为一个单独的私有过程，这只是为了更容易一些；我们希望只在输入名不为NULL（NOT NULL）时才绑定。并非在代码中对输入参数执行4次检查，而是只在这个过程中执行1次检查：
| 7 procedure bind_variable( p_name in varchar2, p_value in varchar2 ) 8 is 9 begin 12. if ( p_name is not null ) 12. then 12. dbms_sql.bind_variable( g_cursor, p_name, p_value ); 12. end if; 12. end; 12. |
下面是包体中SUBSTR_OF的具体实现。这个例程首先是包规范中指定的一个函数声明，以及一些局部变量的声明。L_BUFFER用于返回值，L_BUFFER_LEN用于保存长度（这是由一个Oracle提供的函数返回的）：
| 12. 12. function substr_of 12. ( p_query in varchar2, 12. p_from in number, 20 p_for in number, 21 p_name 12.in varchar2 default NULL, 22 p_bind 12.in varchar2 default NULL, 23 p_name 2 in varchar2 default NULL, 24 p_bind 2 in varchar2 default NULL, 25 p_name 3 in varchar2 default NULL, 26 p_bind 3 in varchar2 default NULL, 27 p_name 4 in varchar2 default NULL, 28 p_bind 4 in varchar2 default NULL ) 29 return varchar2 30 as 31 l_buffer varchar2(4000); 32 l_buffer_len number; 33 begin |
现在，代码所做的第一件事是对P_FROM和P_FOR输入执行一个合理性检查（sanity check）。P_FROM必须是一个大于或等于1的数，P_FOR必须介于1～4,000之间，这与内置函数SUBSTR的参数限制是类似的：
| 34 if ( nvl(p_from,0) <= 0 ) 35 then 36 raise_application_error 37 (-20002, 'From must be >= 1 (positive numbers)' ); 38 end if; 39 if ( nvl(p_for,0) not between 12.and 4000 ) 40 then 41 raise_application_error 42 (-20003, 'For must be between 12.and 4000' ); 43 end if; 44 |
接下来，我们要查看是否得到一个需要解析的新查询。如果已解析的上一个查询与当前查询相同，就可以跳过这一步。必须指出有一点很重要，在第47行上验证传入的P_QUERY必须是一个SELECT，我们只用这个包执行SQL SELECT语句。以下检查为我们完成了这个验证：
| 45 if ( p_query <> g_query or g_query is NULL ) 46 then 47 if ( upper(trim(nvl(p_query,'x'))) not like 'SELECT%') 48 then 49 raise_application_error 50 (-20001, 'This must be a select only' ); 51 end if; 52 dbms_sql.parse( g_cursor, p_query, dbms_sql.native ); 53 g_query := p_query; 54 end if; |
我们已经准备好将输入绑定到这个查询。插入的所有非NULL名都会“绑定”到查询，所以当执行查询时，它会找到正确的行：
| 55 bind_variable( p_name1, p_bind1 ); 56 bind_variable( p_name2, p_bind2 ); 57 bind_variable( p_name3, p_bind3 ); 58 bind_variable( p_name4, p_bind4 ); 59 |
现在执行查询，并获取行。然后使用DBMS_SQL.COLUMN_VALUE_LONG，从而抽取出LONG中必要的子串，并将其返回：
| 60 dbms_sql.define_column_long(g_cursor, 1); 61 if (dbms_sql.execute_and_fetch(g_cursor)>0) 62 then 63 dbms_sql.column_value_long 64 (g_cursor, 1, p_for, p_from-1, 65 l_buffer, l_buffer_len ); 66 end if; 67 return l_buffer; 68 end substr_of; 69 70 end; 71 / Package body created. |
大功告成，你现在可以对数据库中如何遗留的LONG列使用这个包，这样就能执行很多以前不可能执行的WHERE子句操作。例如，现在你可以找出模式中HIGH_VALUE包含2003年的所有分区：
| ops$tkyte@ORA 10G > select * 2 from ( 3 select table_owner, table_name, partition_name, 4 long_help.substr_of 5 ( 'select high_value 6 from all_tab_partitions 7 where table_owner = :o 8 and table_name = :n 9 and partition_name = :p', 12. 1, 4000, 12. 'o', table_owner, 12. 'n', table_name, 12. 'p', partition_name ) high_value 12. from all_tab_partitions 12. where table_name = 'T' 12. and table_owner = user 12. ) 12. where high_value like '%2003%' TABLE_OWN TABLE PARTIT HIGH_VALUE --------- ----- ------ ------------------------------ OPS$TKYTE T PART1 TO_DATE(' 2003-03-12. 00:00:00' , 'SYYYY-MM-DD HH24:MI:SS', 'N LS_CALENDAR=GREGORIAN') OPS$TKYTE T PART2 TO_DATE(' 2003-03-12. 00:00:00' , 'SYYYY-MM-DD HH24:MI:SS', 'N LS_CALENDAR=GREGORIAN') |
通过使用同样的技术，即取一个返回一行和一列（LONG列）的查询，并在一个函数中处理该查询的结果，你就能根据需要实现自己的INSTR、LIKE等函数。
这个实现在LONG类型上能很好地工作，但是在LONG RAW类型上却不能工作。LONG RAW不能分段地访问（DBMS_SQL包中没有COLUMN_VALUE_LONG_RAW之类的函数）。幸运的是，这个限制不算太严重，因为字典中没有用LONG RAW，而且很少需要对LONG RAW“取子串”来完成搜索。不过，如果确实需要这么做，你可能根本不会使用PL/SQL来实现，除非LONG RAW小于或等于32KB，因为PL/SQL本身中没有任何方法来处理超过32KB的LONG RAW。对此，必须使用Java、C、C++、Visual Basic或某种其他语言。
还有一种方法，可以使用TO_LOB内置函数和一个全局临时表，将LONG或LONG RAW临时地转换为CLOB或BLOB。为此，PL/SQL过程可以如下：
| Insert into global_temp_table ( blob_column ) select to_lob(long_raw_column) from t where... |
这在偶尔需要处理单个LONG RAW值的应用中能很好地工作。不过，你可能不希望不断地这样做，原因是为此需要做的工作太多了。如果你发现自己需要频繁地求职于这种技术，就应该干脆将LONG RAW一次性转换为BLOB，然后处理BLOB。
12.6 DATE、TIMESTAMP和INTERVAL类型
Oracle固有数据类型DATE、TIMESTAMP和INTERVAL是紧密相关的。DATE和TIMESTAMP类型存储精度可变的固定日期/时间。INTERVAL类型可以很容易地存储一个时间量，如“8个小时”或“30天”。将两个日期相减，就会得到一个时间间隔（INTERVAL）；例如，将8小时间隔加到一个TIMESTAMP上，会得到8小时以后的一个新的TIMESTAMP。
Oracle的很多版本中都支持DATE数据类型，这甚至可以追溯到我最早使用Oracle的那个年代，也就是说，至少在Oracle 5中（可能还更早）就已经支持DATE数据类型。TIMESTAMP和INTERVAL类型相对来讲算是后来者，因为它们在Oracle9i Release 1中才引入。由于这个简单的原因，你会发现DATE数据类型是存储日期/时间信息最为常用的类型。但是许多新应用都在使用TIMESTAMP类型，这有两个原因：一方面它支持小数秒，而DATE类型不支持；另一方面TIMESTAMP类型支持时区，这也是DATE类型力所不能及的。
我们先来讨论DATE/TIMESTAMP格式及其使用，然后介绍以上各个类型。
12.6.1 格式
这里我不打算全面介绍DATE、TIMESTAMP和INTERVAL格式的方方面面。这在Oracle SQL Reference手册中有很好的说明，任何人都能免费得到这本手册。你可以使用大量不同的格式，很好地了解这些格式至关重要。强烈推荐你先好好研究一下各种格式。
在此我想讨论这些格式会做什么，因为关于这个主题存在许多误解。这些格式用于两个目的：
q 以某种格式对数据库中的数据进行格式化，以满足你的要求。
q 告诉数据库如何将一个输入串转换为DATE、TIMESTAMP或INTERVAL。
仅此而已。多年来我观察到的一个常见的误解是，使用的格式会以某种方式影响磁盘上存储的数据，并且会影响数据如何具体地存储。格式对数据如何存储根本没有任何影响。格式只是用于将存储DATE所用的二进制格式转换为一个串，或者将一个串转换为用于存储DATE的二进制格式。对于TIMESTAMP和INTERVAL也是如此。
关于格式，我的建议就是：应该使用格式。将一个表示DATE、TIMESTAMP或INTERVAL的串发送到数据库时就可以使用格式。不要依赖于默认日期格式，默认格式会（而且很可能）在将来每个时刻被另外每个人所修改。如果你依赖于一个默认日期格式，而这个默认格式有了变化，你的应用可能就会受到影响。如果无法转换日期，应用可能会向最终用户返回一个错误；或者更糟糕的是，它可能会悄悄地插入错误的数据。考虑以下INSERT语句，它依赖于一个默认的日期掩码：
| Insert into t ( date_column ) values ( ' 01/02/03 ' ); |
假设应用依赖于必须有默认日期掩码DD/MM/YY。这就表示 2003年 2月 1日 （假设代码在2000年之后执行，不过稍后还会再来讨论有关的问题）。现在，假设有人认为正确而且适当的日期格式应该是MM/DD/YY。突然之间，原来的日期就会变成 2003年 1月 2日 。或者有人认为YY/MM/DD才对，现在的日期就会变成 2001年 2月 3日 。简单地说，如果日期串没有带相应的日期格式，就会有多种解释方法。这个INSERT语句最好写作：
| Insert into t ( date_column ) values ( to_date( ' 01/02/03 ', 'DD/MM/YY' ) ); |
按我的观点，更好的做法是：
| Insert into t ( date_column ) values ( to_date( ' 01/02/2003 ', 'DD/MM/YYYY' ) ); |
也就是说，它必须使用一个4字符的年份。并不算太久以前，整个行业都领教了捡芝麻而丢西瓜的切肤之痛，我们原本想在存储年份时“节省”2个字节，可是这带来了重重问题，为了解决这些问题，相应地修补软件，想想看我们花了多少时间和精力。可是，随着时间的推移，我们好像又好了伤疤忘了疼。现如今，2005年都过去了，如果还不使用4字符的年份实在说不过去！
从数据库取出的数据也同样存在上述问题。如果你执行SELECT DATE_COLUMN FROM T，并在应用中把这一列获取到一个串中，就应该对其应用一个显示的日期格式。不论你的应用期望何种格式，都应该在这里显式指定。否则，如果将来某个时刻有人修改了默认日期格式，你的应用就可能会崩溃，或者有异样的表现。
接下来，我们来更详细地介绍各种日期数据类型。
12.6.2 DATE类型
DATE类型是一个7字节的定宽日期/时间数据类型。它总是包含7个属性，包括：世纪、世纪中哪一年、月份、月中的哪一天、小时、分钟和秒。Oracle使用一种内部格式来表示这个信息，所以它并不是存储20，05，06，25，12.，01，00来表示 2005年 6月 25日 12.:01:00。通过使用内置DUMP函数，可以看到Oracle实际上会存储以下内容：
| ops$tkyte@ORA 10G > create table t ( x date ); Table created.
ops$tkyte@ORA 10G > insert into t (x) values 2 ( to_date( '25-jun-2005 12.:01:00', 3 'dd-mon-yyyy hh24:mi:ss' ) ); 12.row created.
ops$tkyte@ORA 10G > select x, dump(x,12.) d from t; X D --------- ----------------------------------- 25-JUN-05 Typ=12. Len=7: 120,105,6,25,12.,2,1 |
世纪和年份字节（DUMP输出中的120,105）采用一种“加100”（excess-100）表示法来存储。必须将其减去100来确定正确的世纪和年份。之所以采用加100表示法，这是为了支持BC和AD日期。如果从世纪字节减去100得到一个负数，则是一个BC日期，例如：
| ops$tkyte@ORA 10G > insert into t (x) values 2 ( to_date( '01-jan-4712bc', 3 'dd-mon-yyyybc hh24:mi:ss' ) ); 12.row created.
ops$tkyte@ORA 10G > select x, dump(x,12.) d from t; X D --------- ----------------------------------- 25-JUN-05 Typ=12. Len=7: 120,105,6,25,12.,2,1 01-JAN-12. Typ=12. Len=7: 53,88,12.12.12.12.1 |
因此，插入01-JAN-4712BC时，世纪字节是53，而53-100=-47，这才是我们插入的真实世纪。由于这是一个负数，所以我们知道它是一个BC日期。这种存储格式还允许日期已一种二进制方式自然地排序。由于4712 BC小于4710 BC，我们希望能有一种支持这种顺序的二进制表示。通过转储这两个日期，可以看到01-JAN-4710BC比4712 BC中的同一天“更大”，所以它们确实能正确地排序，并很好地进行比较：
| ops$tkyte@ORA 10G > insert into t (x) values 2 ( to_date( '01-jan-4710bc', 3 'dd-mon-yyyybc hh24:mi:ss' ) ); 12.row created.
ops$tkyte@ORA 10G > select x, dump(x,12.) d from t; X D --------- ----------------------------------- 25-JUN-05 Typ=12. Len=7: 120,105,6,25,12.,2,1 01-JAN-12. Typ=12. Len=7: 53,88,12.12.12.12.1 01-JAN-12. Typ=12. Len=7: 53,90,12.12.12.12.1 |
接下来两个字段是月份和日字节，它们会自然地存储，不做如何修改。因此， 6月 25日 的月份字节就是6，日字节是25.小时、分钟和秒字段采用“加1”（excess-1）表示法存储，这说明必须将各个部分减1，才能得到实际的时间。因此，午夜0点在日期字段中就表示为12.12.1。
这种7字节格式能自然地排序。你已经看到了，这一个7字节字段，可以采用一种二进制方式按从小到大（或从大到小）的顺序非常高效地进行排序。另外，这种结构允许很容易地进行截断，而无需把日期转换为另外某种格式。例如，要截断刚才存储的日期（25-JUN-2005 12.:01:00）来得到日信息（去掉小时、分钟和秒字段），这相当简单。只需将尾部的3个字节设置为12.12.1，就能很好地清除时间分量。考虑一个全新的表T，并执行以下插入：
| ops$tkyte@ORA 10G > create table t ( what varchar2(12.), x date ); Table created. ops$tkyte@ORA 10G > insert into t (what, x) values 2 ( 'orig', 3 to_date( '25-jun-2005 12.:01:00', 4 'dd-mon-yyyy hh24:mi:ss' ) ); 12.row created.
ops$tkyte@ORA 10G > insert into t (what, x) 2 select 'minute', trunc(x,'mi') from t 3 union all 4 select 'day', trunc(x,'dd') from t 5 union all 6 select 'month', trunc(x,'mm') from t 7 union all 8 select 'year', trunc(x,'y') from t 9 / 4 rows created. ops$tkyte@ORA 10G > select what, x, dump(x,12.) d from t; WHAT X D -------- --------- ----------------------------------- orig 25-JUN-05 Typ=12. Len=7: 120,105,6,25,12.,2,1 minute 25-JUN-05 Typ=12. Len=7: 120,105,6,25,12.,2,1 day 25-JUN-05 Typ=12. Len=7: 120,105,6,25,12.12.1 month 01-JUN-05 Typ=12. Len=7: 120,105,6,12.12.12.1 year 01-JAN-05 Typ=12. Len=7: 120,105,12.12.12.12.1 |
要把这个日期截断，只取到年份，数据库所要做的只是在后5个字节上置1，这是一个非常快速的操作。现在我们就有一个可排序、可比较的DATE字段，它能截断到年份级，而且我们可以尽可能高效地做到这一点。不过，许多人并不是使用TRUNC，而是在TO_CHAR函数中使用一个日期格式。例如，他们会这样用：
| Where to_char(date_column,'yyyy') = '2005' |
而不是
| Where trunc(date_column,'y') = to_date('01-jan-2005','dd-mon-yyyy') |
后者不仅有更出色的表现，而且占有的资源更少。如果建立ALL_OBJECTS的一个副本，另存储其中的CREATED列：
| ops$tkyte@ORA 10G > create table t 2 as 3 select created from all_objects; Table created.
ops$tkyte@ORA 10G > exec dbms_stats.gather_table_stats( user, 'T' ); PL/SQL procedure successfully completed. |
然后，启用SQL_TRACE，我们反复使用上述两种技术查询这个表，可以看到以下结果：
| select count(*) from t where to_char(created,'yyyy') = '2005'
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 4 0.01 0.05 0 0 0 0 Execute 4 0.00 0.00 0 0 0 0 Fetch 8 0.41 0.59 0 372 0 4 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 12. 0.42 0.64 0 372 0 4
select count(*) from t where trunc(created,'y') = to_date('01-jan-2005','dd-mon-yyyy') call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 4 0.00 0.00 0 0 0 0 Execute 4 0.00 0.00 0 0 0 0 Fetch 8 0.04 0.12. 0 372 0 4 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 12. 0.04 0.12. 0 372 0 4 |
可以看到存在明显的差异。与使用TRUNC相比，使用TO_CHAR所用的CPU时间与前者相差一个数量级（即相差12.倍）。这是因为TO_CHAR必须把日期转换为一个串，这要使用一个更大的代码路径，并利用当前的所有NLS来完成这个工作。然后必须执行一个串与串的比较。另一方面，TRUNC只需把后5个字节设置为1.然后将两个7字节的二进制数进行比较，就大功告成了。因此，如果只是要截断一个DATE列，你将应该避免使用TO_CHAR。
另外，甚至要尽可能完全避免对DATE列应用函数。把前面的例子再进一步，可以看到其目标是获取2005年的所有数据。那好，如果CREATED上有一个索引，而且表中只有很少一部分CREATED值是2005年的时间，会怎么样呢？我们可能希望能够使用这个索引，为此要使用一个简单的谓词来避免在数据库列上应用函数：
| select count(*) from t where created >= to_date('01-jan-2005','dd-mon-yyyy') and created < to_date('01-jan-2006','dd-mon-yyyy'); |
这样有两个好处：
q 这样一来就可以考虑使用CREATED上的索引了。
q 根本无需调用TRUNC函数，这就完全消除了相应的开销。
这里使用的是区间比较而不是TRUNC或TO_CHAR，这种技术同样适用于稍后讨论的TIMESTAMP类型。如果能在查询中避免对一个数据库列应用函数，就应该全力这样做。一般来讲，避免使用函数会有更好的性能，而且允许优化器在更多的访问路径中做出选择。
1. 向DATE增加或减去时间
经常有人问我这样一个问题：“怎么向一个DATE类型增加时间，或者从DATE类型减去时间？”例如，如何向一个DATE增加1天，或8个小时，或者一年，或者一个月，等等？对此，常用的技术有3种：
q 向DATE增加一个NUMBER。把DATE加1是增加1天的一种方法。因此，向DATE增加12.24就是增加1个小时，依此类推。
q 可以使用稍后将介绍的INTERVAL类型来增加时间单位。INTERVAL类型支持两种粒度：年和月，或日/小时/分钟/秒。也就是说，可以是几年和几个月的一个时间间隔，也可以是几天、几小时、几分钟和几秒的一个时间间隔。
q 使用内置的ADD_MONTHS函数增加月。由于增加一个月往往不像增加28～31天那么简单，为了便于增加月，专门实现了这样一个函数。
表12.-3展示了向一个日期增加N个时间单位可用的技术（当然，也可以利用这些技术从一个日期减去N个时间单位）。
表12.-3 向日期增加时间
时间单位 操作 描述
DATE + n/24/60/60 一天有86,400秒。由于加1就是增加1天，所以加12.86400就是向一个日期增
N秒 DATE + n/86400 加1秒。我更喜欢用n/24/60/60技术而不是12.86400技术。它们是等价的。
DATE+NUMTODSINTERVAL 更可读的一种方法是使用NUMTODSINTERVAL（日/秒数间隔）
(n,'second') 函数来增加N秒
DATE + n/24/60 一天有12.440分钟，因此加12.1440就是向一个DATE增加1分钟。更可读的
N分钟 DATE + n/1440 一种方法是使用NUMTODSINTERVAL函数
DATE+NUMTODSINTERVAL
(n,'minute')
DATE + n/24
N小时 DATE+NUMTODSINTERVAL 一天有24个小时，因此增加12.24就是向一个DATE增加1小时。更可读的
(n,'hour') 一种方法是使用NUMTODSINTERVAL函数
N天 DATE + n 向DATE直接加N，就是增加或减去N天
N周 DATE + 7*n 一周有7天，所以只需将周数乘以7就是要增加或减去的天数
N月 ADD_MONTHS(DATE,n) 可以使用ADD_MONTHS内置函数或者向DATE增加一个N个月的间隔。
DATE+NUMTOYMINTERVAL 请参考稍后有关DATE使用月间隔做出的重要警告
(n,'month')
N年 ADD_MONTHS(DATE,12.*n) 可以使用ADD_MONTHS内置函数，增加或减去N年时将参数指定为12.*n。
DATE+NUMTOYMINTERVAL 利用年间隔可以达到类似的目标，不过请参考稍后有关日期使用年间隔做出的
(n,'year') 重要警告
总的来讲，使用Oracle DATE类型时，我有以下建议：
q 使用NUMTODSINTERVAL内置函数来增加小时、分钟和秒。
q 加一个简单的数来增加天。
q 使用ADD_MONTHS内置函数来增加月和年。
我建议不要使用NUMTOYMINTERVAL函数。其原因与这个函数如何处理月末日期有关。
ADD_MONTHS函数专门处理月末日期。它实际上会为我们完成日期的“舍入”；例如，如果向一个有31天的月增加1个月，而且下一个月不到31天，ADD_MONTHS就会返回下一个月的最后一天。另外，向一个月的最后一天增加1个月会得到下一个月的最后一天。向有30天（或不到30天）的一个月增加1个月时，可以看到：
| ops$tkyte@ORA 10G > alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss'; Session altered.
ops$tkyte@ORA 10G > select dt, add_months(dt,1) 2 from (select to_date('29-feb-2000','dd-mon-yyyy') dt from dual ) 3 / DT ADD_MONTHS(DT,1) ---------------------------- ------------------------------ 29-feb-2000 00:00:00 31-mar-2000 00:00:00
ops$tkyte@ORA 10G > select dt, add_months(dt,1) 2 from (select to_date('28-feb-2001','dd-mon-yyyy') dt from dual ) 3 / DT ADD_MONTHS(DT,1) -------------------- -------------------- 28-feb-2001 00:00:00 31-mar-2001 00:00:00
ops$tkyte@ORA 10G > select dt, add_months(dt,1) 2 from (select to_date('30-jan-2001','dd-mon-yyyy') dt from dual ) 3 / DT ADD_MONTHS(DT,1) -------------------- -------------------- 30-jan-2001 00:00:00 28-feb-2001 00:00:00
ops$tkyte@ORA 10G > select dt, add_months(dt,1) 2 from (select to_date('30-jan-2000','dd-mon-yyyy') dt from dual ) 3 / DT ADD_MONTHS(DT,1) -------------------- -------------------- 30-jan-2000 00:00:00 29-feb-2000 00:00:00 |
看到了吗？向 2000年 2月 29日 增加1个月，得到的是 2000年 3月 31日 。2月29日是该月的最后一天，所以ADD_MONTHS返回了下一个月的最后一天。另外，注意向2000年和2001年的 1月 30日 增加1个月时，会分别得到2000年和2001年2月的最后一天（分别是2月29日和 2月 28日 ）。
如果与增加一个间隔的做法相比较，会看到完全不同的结果：
| ops$tkyte@ORA 10G > select dt, dt+numtoyminterval(1,'month') 2 from (select to_date('29-feb-2000','dd-mon-yyyy') dt from dual ) 3 / DT DT+NUMTOYMINTERVAL(1 -------------------- -------------------- 29-feb-2000 00:00:00 29-mar-2000 00:00:00
ops$tkyte@ORA 10G > select dt, dt+numtoyminterval(1,'month') 2 from (select to_date('28-feb-2001','dd-mon-yyyy') dt from dual ) 3 / DT DT+NUMTOYMINTERVAL(1 -------------------- -------------------- 28-feb-2001 00:00:00 28-mar-2001 00:00:00 |
注意，得到的日期并不是下一个月的最后一天，而只是下一个月的同一天。有人认为这种行为是可以接受的，但是请考虑一下，如果下一个月没有这么多天会怎么样：
| ops$tkyte@ORA 10G > select dt, dt+numtoyminterval(1,'month') 2 from (select to_date('30-jan-2001','dd-mon-yyyy') dt from dual ) 3 / select dt, dt+numtoyminterval(1,'month') * ERROR at line 1: ORA-01839: date not valid for month specified ops$tkyte@ORA 10G > select dt, dt+numtoyminterval(1,'month') 2 from (select to_date('30-jan-2000','dd-mon-yyyy') dt from dual ) 3 / select dt, dt+numtoyminterval(1,'month') * ERROR at line 1: ORA-01839: date not valid for month specified |
根据我的经验，这是由于这个原因，所以一般来讲不可能在日期算术运算中使用月间隔。对于年间隔也存在一个类似的问题：如果向 2000年 2月 29日 增加1年，也会得到一个运行时错误，因为没有2001年2月29日。
2. 得到两个日期之差
还有一个常被问到的问题：“我怎么得到两个日期之差？”这个问题看上去似乎很简单：只需要相减就行了。这会返回表示两个日期相隔天数的一个数。另外，还可以使用内置函数MONTHS_BETWEEN，它会返回表示两个日期相隔月数的一个数（包括月小数）。最后，利用INTERVAL类型，你还能用另一个方法来查看两个日期之间的逝去时间。以下SQL查询分别展示了将两个日期相减的结果（显示两个日期之间的天数），使用MONTHS_BETWEEN函数的结果，然后是使用INTERVAL类型的两个函数的结果：
| ops$tkyte@ORA 10G > select dt2-dt1 , 2 months_between(dt2,dt1) months_btwn, 3 numtodsinterval(dt2-dt1,'day') days, 4 numtoyminterval(months_between(dt2,dt1),'month') months 5 from (select to_date('29-feb-2000 01:02:03','dd-mon-yyyy hh24:mi:ss') dt1, 6 to_date('12.-mar-2001 12.:22:33','dd-mon-yyyy hh24:mi:ss') dt2 7 from dual )
DT2-DT1 MONTHS_BTWN DAYS MONTHS ---------- ----------- ------------------------------ ------------- 380.430903 12..5622872 +000000380 12.:20:30.000000000 +000000001-00 |
这些都是“正确”的值，但是对我们来说都没有大用。大多数应用都更愿意显示日期之间相隔的年数、月数、天数、小时数、分钟数和秒数。通过使用前述函数的一个组合，就可以实现这个目标。我们将选出两个间隔：一个是年和月间隔，另一个是日/小时/分钟/秒间隔。我们使用MONTHS_BETWEEN内置函数来确定两个日期之间相隔的月数（包括小数），然后使用NUMTOYMINTERVAL内置函数将这个数转换为年数和月数。另外，使用TRUNC得到两个日期相隔月数中的整数部分，再使用ADD_MONTHS内置函数将dt1增加12.个月（这会得到‘28-feb-2001 01:02:03），再从两个日期中的较大者（dt2）减去这个计算得到的日期，从而得到两个日期之间的天数和小时数：
| ops$tkyte@ORA 10G > select numtoyminterval 2 (months_between(dt2,dt1),'month') 3 years_months, 4 numtodsinterval 5 (dt2-add_months( dt1, trunc(months_between(dt2,dt1)) ), 6 'day' ) 7 days_hours 8 from (select to_date('29-feb-2000 01:02:03','dd-mon-yyyy hh24:mi:ss') dt1, 9 to_date('12.-mar-2001 12.:22:33','dd-mon-yyyy hh24:mi:ss') dt2 12. from dual ) 12. / YEARS_MONTHS DAYS_HOURS --------------- ------------------------------ +000000001-00 +000000015 12.:20:30.000000000 |
现在就很清楚了，这两个日期之间相隔1年、12.天、12.小时、20分钟和30秒。
12.6.3 TIMESTAMP类型
TIMESTAMP类型与DATE非常类似，只不过另外还支持小数秒和时区。以下3小节将介绍TIMESTAMP类型：其中一节讨论只支持小数秒而没有时区支持的情况，另外两小节讨论存储有时区支持的TIMESTAMP的两种方法。
1. TIMESTAMP
基本TIMESTAMP数据类型的语法很简单：
| TIMESTAMP(n) |
这里N是可选的，用于指定TIMESTAMP中秒分量的小数位数，可以取值为0～9.如果指定0，TIMESTAMP在功能上则与DATE等价，它们实际上会以同样的方式存储相同的值：
| ops$tkyte@ORA 10G > create table t 2 ( dt date, 3 ts timestamp(0) 4 ) 5 / Table created.
ops$tkyte@ORA 10G > insert into t values ( sysdate, systimestamp ); 12.row created.
ops$tkyte@ORA 10G > select dump(dt,12.) dump, dump(ts,12.) dump 2 from t; DUMP DUMP ------------------------------------ ------------------------------------ Typ=12. Len=7: 120,105,6,28,12.,35,41 Typ=180 Len=7: 120,105,6,28,12.,35,41 |
这两个数据类型是不同的（由TYP=字段可知），但是它们采用了相同的方式存储数据。如果指定要保留几位秒小数，TIMESTAMP数据类型与DATE类型的长度将会不同，例如：
| ops$tkyte@ORA 10G > create table t 2 ( dt date, 3 ts timestamp(9) 4 ) 5 / Table created.
ops$tkyte@ORA 10G > insert into t values ( sysdate, systimestamp ); 12.row created.
ops$tkyte@ORA 10G > select dump(dt,12.) dump, dump(ts,12.) dump 2 from t; DUMP DUMP ------------------------------------- ------------------------------------- Typ=12. Len=7: 120,105,6,28,12.,46,21 Typ=180 Len=12.: 120,105,6,28,12.,46,21 ,44,101,192,208 |
现在TIMESTAMP占用12.字节的存储空间，最后额外的4个字节包含着小数秒，通过查看所存储的时间就能看出：
| ops$tkyte@ORA 10G > alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss'; Session altered.
ops$tkyte@ORA 10G > select * from t; DT TS -------------------- -------------------------------- 28-jun-2005 12.:45:20 28-JUN-05 12..45.20 .744866000 AM
ops$tkyte@ORA 10G > select dump(ts,12.) dump from t; DUMP -------------------------------------------------- Typ=180 Len=12.: 78,69,6, 12. ,b,2e,12., 2c ,65,c0,d0
ops$tkyte@ORA 10G > select to_number(' 2c 65c 0d0','xxxxxxxx') from dual; TO_NUMBER(' 2C 65C 0D0','XXXXXXXX') -------------------------------- 744866000 |
可以看到，存储的小数秒都在最后4个字节中。这一次我们使用了DUMP函数以HEX（十六进制）来查看数据，所以能很容易地将这4个字节转换为十进制表示。
2. 向TIMESTAMP增加或减去时间
DATE执行日期算术运算所用的技术同样适用于TIMESTAMP，但是在使用上述技术的很多情况下，TIMESTAMP会转换为一个DATE，例如：
| ops$tkyte@ORA 10G > alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss'; Session altered. ops$tkyte@ORA 10G > select systimestamp ts, systimestamp+12.dt 2 from dual; TS DT ------------------------------------ -------------------- 28-JUN-05 12..04.49 .833097 AM -04:00 29-jun-2005 12.:04:49 |
注意，这里加1实际上将SYSTIMESTAMP推进了1天，但是小数秒没有了，另外时区信息也没有了。这里使用INTERVAL更有意义：
| ops$tkyte@ORA 10G > select systimestamp ts, systimestamp +numtodsinterval(1,'day') dt 2 from dual; TS DT ------------------------------------ ---------------------------------------- 28-JUN-05 12..08.03 .958866 AM -04:00 29-JUN-05 12..08.03.958866000 AM -04:00 |
使用返回一个INTERVAL类型的函数能保持TIMESTAMP的真实度。使用TIMESTAMP时要特别当心，以避免这种隐式转换。
但是还要记住，向TIMESTAMP增加月间隔或年间隔时存在相关的警告。如果所得到的“日期”不是一个合法日期，这个操作就会失败（如果通过INTERVAL来增加月，向一月份的最后一天增加1个月总会失败，因为这会得到“2月31日”，而2月根本没有31天）。
3. 得到两个TIMESTAMP之差
这正是DATE和TIMESTAMP类型存在显著差异的地方。尽管将DATE相减的结果是一个NUMBER，但TIMESTAMP相减的结果却是一个INTERVAL：
| ops$tkyte@ORA 10G > select dt2-dt1 2 from (select to_timestamp('29-feb-2000 01:02:03.122000', 3 'dd-mon-yyyy hh24:mi:ss.ff') dt1, 4 to_timestamp('12.-mar-2001 12.:22:33.000000', 5 'dd-mon-yyyy hh24:mi:ss.ff') dt2 6 from dual ) 7 / DT2-DT1 --------------------------------------------------------------------------- +000000380 12.:20:29.878000000 |
两个TIMESTAMP值之差是一个INTERVAL，而且这里显示了二者之间相隔的天数已经小时/分钟/秒数。如果想得到二者之间相差的年数和月数，可以使用以下查询（这个查询类似于先前用于日期的查询）：
| ops$tkyte@ORA 10G > select numtoyminterval 2 (months_between(dt2,dt1),'month') 3 years_months, 4 dt2-add_months(dt1,trunc(months_between(dt2,dt1))) 5 days_hours 6 from (select to_timestamp('29-feb-2000 01:02:03.122000', 7 'dd-mon-yyyy hh24:mi:ss.ff') dt1, 8 to_timestamp('12.-mar-2001 12.:22:33.000000', 9 'dd-mon-yyyy hh24:mi:ss.ff') dt2 12. from dual ) 12. / YEARS_MONTHS DAYS_HOURS ------------- ----------------------------- +000000001-00 +000000015 12.:20:30.000000000 |
需要说明，在这种情况下，由于使用了ADD_MONTHS，DT1会隐式转换为一个DATE类型，这样就丢失了小数秒。为了保住小数秒，我们必须编写更多的代码。也许可以使用NUMTOYMINTERVAL来增加月，这样就能保留TIMESTAMP；不过，这样一来，我们将遭遇运行时错误：
| ops$tkyte@ORA 10G > select numtoyminterval 2 (months_between(dt2,dt1),'month') 3 years_months, 4 dt2-(dt1 + numtoyminterval( trunc(months_between(dt2,dt1)),'month' )) 5 days_hours 6 from (select to_timestamp('29-feb-2000 01:02:03.122000', 7 'dd-mon-yyyy hh24:mi:ss.ff') dt1, 8 to_timestamp('12.-mar-2001 12.:22:33.000000', 9 'dd-mon-yyyy hh24:mi:ss.ff') dt2 12. from dual ) 12. / dt2-(dt1 + numtoyminterval( trunc(months_between(dt2,dt1)),'month' )) * ERROR at line 4: ORA-01839: date not valid for month specified |
我个人认为这是不能接受的。不过，问题在于，在你显示有年和月的信息时，TIMESTAMP的真实性已经被破坏了。一年有多长并不固定（可能是365天，也可能是366天），同样，月的长度也不固定。如果你在显示有年和月的信息，毫秒级的损失则无关大碍；显示这种信息时细到秒级就完全足够了。
4. TIMESTAMP WITH TIME ZONE类型
TIMESTAMP WITH TIME ZONE类型继承了TIMESTAMP类型的所有特点，并增加了时区支持。TIMESTAMP WITH TIME ZONE类型占12.字节的存储空间，在此有额外的2个字节用于保留时区信息。它在结构上与TIMESTAMP的差别只是增加了这2个字节：
| ops$tkyte@ORA 10G > create table t 2 ( 3 ts timestamp, 4 ts_tz timestamp with time zone 5 ) 6 / Table created.
ops$tkyte@ORA 10G > insert into t ( ts, ts_tz ) 2 values ( systimestamp, systimestamp ); 12.row created.
ops$tkyte@ORA 10G > select * from t; TS TS_TZ ---------------------------- ----------------------------------- 28-JUN-05 01.45.08 .087627 PM 28-JUN-05 01.45.08.087627 PM -04:00
ops$tkyte@ORA 10G > select dump(ts), dump(ts_tz) from t; DUMP(TS) ------------------------------------------------------------------------------- DUMP(TS_TZ) ------------------------------------------------------------------------------- Typ=180 Len=12.: 120,105,6,28,12.,46,9,5,57,20,248 Typ=181 Len=12.: 120,105,6,28,12.,46,9,5,57,20,248,12.,60 |
可以看到，获取数据时，默认的TIMESTAMP WITH TIME ZONE格式包括有时区信息（执行这个操作时，我所在的时区是East Coast US，当时正是白天）。
存储数据时，TIMESTAMP WITH TIME ZONE会在数据中存储指定的时区。时区成为数据本身的一部分。注意TIMESTAMP WITH TIME ZONE字段如何存储小时、分钟和秒（…12.,46,9…），这里采用了加1表示法，因此…12.,46,9…就表示12.:45:08，而…12.,46,9…字段只存储了…12.,46,9…，这表示12.:45:09，也就是我们插入到串中的那个时间。TIMESTAMP WITH TIME ZONE为它增加了4个小时，从而存储为GWT（也称为UTC）时间。获取时，会使用尾部的2个字节适当地调整TIMESTAMP值。
我并不打算在这里全面介绍时区的所有细节；这个主题在其他资料中已经做了很好的说明。我只是要指出，与此前相比，时区支持对于今天的应用更显重要。十年前，应用不像现在这么具有全球性。在因特网普及之前，应用更多地都是分布和分散的，隐含地时区都基于服务器所在的位置。如今，由于大型的集中式系统可能由世界各地的人使用，所以记录和使用时区非常重要。
将时区支持内建到数据类型之前，必须把DATE存储在一个列中，而把时区信息存储在另外一列中，然后要由应用负责使用函数将DATE从一个时区转换到另一个时区。现在则不然，这个任务已经交给了数据库，数据库能存储多个时区的数据：
| ops$tkyte@ORA 10G > create table t 2 ( ts1 timestamp with time zone, 3 ts2 timestamp with time zone 4 ) 5 / Table created.
ops$tkyte@ORA 10G > insert into t (ts1, ts2) 2 values ( timestamp'2005-06-05 12.:02:32.212 US /Eastern', 3 timestamp'2005-06-05 12.:02:32.212 US /Pacific' ); 12.row created. |
并对这些数据执行正确的TIMESTAMP运算：
| ops$tkyte@ORA 10G > select ts1-ts2 from t; TS1-TS2 --------------------------------------------------------------------------- -000000000 03:00:00.000000 |
因为这两个时区之间有3个小时的时差，尽管它们显示的是“同样的时间”——12.:02:32:212，但是从报告的时间间隔来看确实存在3小时的时差。在TIMESTAMP WITH TIME ZONE类型上执行TIMESTAMP运算时，Oracle会自动地把两个类型首先转换为UTC时间，然后执行运算。
5. TIMESTAMP WITH LOCAL TIME ZONE类型
这种类型与TIMESTAMP类型的工作是类似的。这是一个7字节或12.字节的字段（取决于TIMESTAMP的精度），但是会进行规范化，在其中存入数据库的时区。要了解这一点，我们将再次使用DUMP命令。首先，创建一个包括3列的表，这3列分别是一个DATE列、一个TIMESTAMP WITH TIME ZONE列和一个TIMESTAMP WITH LOCAL TIME ZONE列，然后向这3列插入相同的值：
| ops$tkyte@ORA 10G > create table t 2 ( dt date, 3 ts1 timestamp with time zone, 4 ts2 timestamp with local time zone 5 ) 6 / Table created.
ops$tkyte@ORA 10G > insert into t (dt, ts1, ts2) 2 values ( timestamp'2005-06-05 12.:02:32.212 US /Pacific', 3 timestamp'2005-06-05 12.:02:32.212 US /Pacific', 4 timestamp'2005-06-05 12.:02:32.212 US /Pacific' ); 12.row created.
ops$tkyte@ORA 10G > select dbtimezone from dual; DBTIMEZONE ---------- US/Eastern |
现在，将这些值转储如下：
| ops$tkyte@ORA 10G > select dump(dt), dump(ts1), dump(ts2) from t; DUMP(DT) ------------------------------------ DUMP(TS1) ------------------------------------ DUMP(TS2) ------------------------------------ Typ=12. Len=7: 120,105,6,5,12.,3,33 Typ=181 Len=12.: 120,105,6,6,12.3,33,12.,162,221,0,137,156 Typ=231 Len=12.: 120,105,6,5,21,3,33,12.,162,221,0 |
可以看到，在这个例子中，会存储3种完全不同的日期/时间表示：
q DT：这一列存储了日期/时间5-JUN-2005 12.:02:32。时区和小数秒没有了，因为我们使用的是DATE类型。这里根本不会执行时区转换。我们会原样存储插入的那个日期/时间，但是会丢掉时区。
q TS1：这一列保留了TIME ZONE信息，并规范化为该TIME ZONE相应的UTC时间。所插入的TIMESTAMP值处于US/Pacific时区，在写这本书时这个时间与UTC时间相差7个小时。因此，存储的日期/时间是6-JUN-2005 00:02:32.212。它把输入的时间推进了7个小时，使之成为UTC时间，并把时区US/Pacific保存为最后2个字节，这样以后就能适当地解释这个数据。
q TS2：这里认为这个列的时区就是数据库时区，即US/Eastern。现在，12.:02:32 US/Pacific is 20:02:32 US/Eastern，所以存储为以下字节（...21,3,33...），这里采用了加1表示法：取得实际时间时要记住减1。
由于TS1列在最后2字节保留了原来的时区，获取时我们会看到以下结果：
| ops$tkyte@ORA 10G > select ts1, ts2 from t; TS1 ---------------------------------------- TS2 ---------------------------------------- 05-JUN-05 05.02.32 .212000 PM US/PACIFIC 05-JUN-05 08.02.32 .212000 PM |
数据库应该能显示这个信息，但是有LOCAL TIME ZONE（数据库时区）的TS2列只显示了数据库时区的时间，并认为这就是这一列的时区（实际上，这个数据库中有LOCAL TIME ZONE的所有列的时区都是数据库时区）。我的数据库处于US/Eastern时区，所以插入的12.:02:32 US/Pacific现在显示为8:00pm East Coast时间。
如果你不需要记住源时区，只需要这样一种数据类型，要求能对日期/时间类型提供一致的全球性处理，那么TIMESTAMP WITH LOCAL TIME ZONE对大多数应用来说已经能提供足够的支持。另外，TIMESTAMP(0) WITH LOCAL TIME ZONE是与DATE类型等价但提供了时区支持的一种类型；它占用7字节存储空间，允许存储按UTC形式“规范化”的日期。
关于TIMESTAMP WITH LOCAL TIME ZONE类型有一个警告，一旦你创建有这个列的表，会发现你的数据库的时区“被冻住了”，你将不能修改数据库的时区。
| ops$tkyte@ORA 10G > alter database set time_zone = 'PST'; alter database set time_zone = 'PST' * ERROR at line 1: ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns ops$tkyte@ORA 10G > !oerr ora 30079 30079, 00000, "cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns" // *Cause: An attempt was made to alter database timezone with // TIMESTAMP WITH LOCAL TIME ZONE column in the database. // *Action: Either do not alter database timezone or first drop all the // TIMESTAMP WITH LOCAL TIME ZONE columns. |
其原因是：倘若你能修改数据库的时区，就必须将每一个有TIMESTAMP WITH LOCAL TIME ZONE的表重写，否则在新时区下，它们当前的值将是不正确的！
12.6.4 INTERVAL类型
上一节我们简要地提到了INTERVAL类型。这是表示一段时间或一个时间间隔的一种方法。这一节将讨论两个INTERVAL类型：其中一个是YEAR TO MONTH类型，它能存储按年和月指定的一个时段；另一个类型是DATE TO SECOND类型，它能存储按天、小时、分钟和秒（包括小数秒）指定的时段。
在具体介绍这两个INTERVAL类型之前，我想先谈谈EXTRACT内置函数，处理这种类型时这个函数可能非常有用。EXTRACT内置函数可以处理TIMESTAMP和INTERVAL，并从中返回各部分信息，如从TIMESTAMP返回时区，从INTERVAL返回小时/天/分钟。还是用前面的例子（其中得到了380天、12.小时、29.878秒的INTERVAL）：
| ops$tkyte@ORA 10G > select dt2-dt1 2 from (select to_timestamp('29-feb-2000 01:02:03.122000', 3 'dd-mon-yyyy hh24:mi:ss.ff') dt1, 4 to_timestamp('12.-mar-2001 12.:22:33.000000', 5 'dd-mon-yyyy hh24:mi:ss.ff') dt2 6 from dual ) 7 / DT2-DT1 --------------------------------------------------------------------------- +000000380 12.:20:29.878000000 |
可以使用EXTRACT来查看，它能很轻松地取出其中的各部分信息：
| ops$tkyte@ORA 10G > select extract( day from dt2-dt1 ) day, 2 extract( hour from dt2-dt1 ) hour, 3 extract( minute from dt2-dt1 ) minute, 4 extract( second from dt2-dt1 ) second 5 from (select to_timestamp('29-feb-2000 01:02:03.122000', 6 'dd-mon-yyyy hh24:mi:ss.ff') dt1, 7 to_timestamp('12.-mar-2001 12.:22:33.000000', 8 'dd-mon-yyyy hh24:mi:ss.ff') dt2 9 from dual ) 12. / DAY HOUR MINUTE SECOND ---------- ---------- ---------- ---------- 380 12. 20 29.878 |
另外，我们已经了解了创建YEAR TO MONTH和DAY TO SECOND间隔时所用的NUMTOYMINTERVAL和NUMTODSINTERVAL。我发现这些函数是创建INTERVAL类型实例最容易的方法，远远胜于串转换函数。我不喜欢把一大堆表示天、小时、分钟和秒的数连接在一起来表示某个间隔，而是会增加4个NUMTODSINTERVAL调用来完成同样的工作。
INTERVAL类型不只是可以用于存储时段，还可以以某种方式存储“时间”。例如，如果你希望存储一个特定的日期时间，可以使用DATE或TIMESTAMP类型。但是如果你只想存储上午8:00这个时间呢？INTERVAL类型就很方便（尤其是INTERVAL DAY TO SECOND类型）。
1. INTERVAL YEAR TO MONTH
INTERVAL YEAR TO MONTH的语法很简单：
| INTERVAL YEAR(n) TO MONTH |
在此N是一个可选的位数（用以支持年数），可取值为0～9，默认为2（表示年数可为0～99）。这就允许你存储任意大小的年数（最多可达9位）和月数。我更喜欢用NUMTOYMINTERVAL函数来创建这种类型的INTERVAL实例。例如，要创建一个5年2个月的时间间隔，可以使用以下命令：
| ops$tkyte@ORA 10G > select numtoyminterval(5,'year')+numtoyminterval(2,'month') 2 from dual;
NUMTOYMINTERVAL(5,'YEAR')+NUMTOYMINTERVAL(2,'MONTH') --------------------------------------------------------------------------- +000000005-02 |
或者，利用1年有12.个月这个事实，可以使用一个调用，并使用以下命令：
| ops$tkyte@ORA 10G > select numtoyminterval(5*12.+2,'month') 2 from dual;
NUMTOYMINTERVAL(5*12.+2,'MONTH') --------------------------------------------------------------------------- +000000005-02 |
这两种方法都能很好地工作。还可以用另一个函数TO_YMINTERVAL将一个串转换为一个年/月INTERVAL类型：
| ops$tkyte@ORA 10G > select to_yminterval( '5-2' ) from dual;
TO_YMINTERVAL('5-2') --------------------------------------------------------------------------- +000000005-02 |
但是，由于我的应用中大多数情况下都是把年和月放在两个NUMBER字段中，所以我发现NUMTOYMINTERVAL函数更有用，而不是先从数字构建一个格式化的串。
最后，还可以直接在SQL中使用INTERVAL类型，而不用这些函数：
| ops$tkyte@ORA 10G > select interval '5-2' year to month from dual; INTERVAL'5-2'YEARTOMONTH --------------------------------------------------------------------------- +05-02 |
2. INTERVAL DAY TO SECOND
INTERVAL DAY TO SECOND类型的语法很简单：
| INTERVAL DAY(n) TO SECOND(m) |
在此N是一个可选的位数，支持天数分量，取值为0～9，默认为2。M是秒字段小时部分中保留的位数，其中为0～9，默认为6.同样，我更喜欢用NUMTODSINTERVAL函数来创建这种类型的INTERVAL实例：
| ops$tkyte@ORA 10G > select numtodsinterval( 12., 'day' )+ 2 numtodsinterval( 2, 'hour' )+ 3 numtodsinterval( 3, 'minute' )+ 4 numtodsinterval( 2.3312, 'second' ) 5 from dual;
NUMTODSINTERVAL(12.,'DAY')+NUMTODSINTERVAL(2,'HOUR')+NUMTODSINTERVAL(3,'MINU --------------------------------------------------------------------------- +000000010 02:03:02.331200000 |
或者只是：
| ops$tkyte@ORA 10G > select numtodsinterval( 12.*86400+2*3600+3*60+2.3312, 'second' ) 2 from dual;
NUMTODSINTERVAL(12.*86400+2*3600+3*60+2.3312,'SECOND') --------------------------------------------------------------------------- +000000010 02:03:02.331200000 |
这里利用了一天有89,400秒，一小时有3,600秒等事实。或者，像前面一样，可以使用TO_DSINTERVAL函数将一个串转换为一个DAY TO SECOND间隔：
| ops$tkyte@ORA 10G > select to_dsinterval( '12. 02:03:02.3312' ) 2 from dual; TO_DSINTERVAL('1002:03:02.3312') --------------------------------------------------------------------------- +000000010 02:03:02.331200000 |
或者只是在SQL本身中使用INTERVAL变量：
| ops$tkyte@ORA 10G > select interval '12. 02:03:02.3312' day to second 2 from dual; INTERVAL'1002:03:02.3312'DAYTOSECOND --------------------------------------------------------------------------- +12. 02:03:02.331200 |
12.7 LOB类型
根据我的经验，LOB或大对象（large object）是产生许多混乱的根源。这些数据类型很容易被误解，这包括它们是如何实现的，以及如何最好地加以使用。这一节将概要介绍LOB如何物理地存储，并指出使用LOB类型是必须考虑哪些问题。这些类型有许多可选的设置，要针对你的应用做出正确的选择，这一点至关重要。
Oracle中支持4种类型的LOB：
q CLOB：字符LOB。这种类型用于存储大量的文本信息，如XML或者只是纯文本。这个数据类型需要进行字符集转换，也就是说，在获取时，这个字段中的字符会从数据库的字符集转换为客户的字符集，而在修改时会从客户的字符集转换为数据库的字符集。
q NCLOB：这是另一种类型的字符LOB。存储在这一列中的数据所采用的字符集是数据库的国家字符集，而不是数据库的默认字符集。
q BLOB：二进制LOB。这种类型用于存储大量的二进制信息，如字处理文档，图像和你能想像到的任何其他数据。它不会执行字符集转换。应用向BLOB中写入什么位和字节，BLOB就会返回什么为和字节。
q BFILE：二进制文件LOB。这与其说是一个数据库存储实体，不如说是一个指针。带BFILE列的数据库中存储的只是操作系统中某个文件的一个指针。这个文件在数据库之外维护，根本不是数据库的一部分。BFILE提供了文件内容的只读访问。
讨论LOB时，我会分两节讨论上述各个类型：其中一节讨论存储在数据库中的LOB，这也称为内部LOB，包括CLOB、BLOB和NCLOB；另一节讨论存储在数据库之外的LOB，或BFILE类型。我不打算分别讨论CLOB、BLOB或NCLOB，因为为了从存储来看，还是从选项来看，它们都是一样的。只不过CLOB和NCLOB支持文本信息，而BLOB支持二进制信息。不过不论基类型是什么，所指定的选项（CHUNKSIZE、PCTVERSION等）和要考虑的问题都是一样的。但由于BFILE与它们有显著不同，所以我们将单独地讨论这种类型。
12.7.1 内部LOB
从表面看，LOB的语法相当简单，但这只是一种假象。你可以创建有CLOB、BLOB或NCLOB数据类型列的表，就这么简单。似乎使用这些数据类型就像使用NUMBER、DATE或VARCHAR2类型一样容易：
| ops$tkyte@ORA 10G > create table t 2 ( id int primary key, 3 txt clob 4 ) 5 / Table created. |
还能怎么样呢？但这个小例子只是显示出冰山一角，关于LOB能指定的选项很多，这里只是其中极少的一部分。通过使用DBMS_METADATA方能得到全貌：
| ops$tkyte@ORA 10G > select dbms_metadata.get_ddl( 'TABLE', 'T' ) 2 from dual;
DBMS_METADATA.GET_DDL('TABLE','T') ------------------------------------------------------------------------------- CREATE TABLE "OPS$TKYTE"."T" ( "ID" NUMBER(*,0), "TXT" CLOB, PRIMARY KEY ("ID") USING INDEX PCTFREE 12. INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 12.FREELIST GROUPS 12.BUFFER_POOL DEFAULT) TABLESPACE "USERS" ENABLE ) PCTFREE 12. PCTUSED 40 INITRANS 12.MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 12.FREELIST GROUPS 12.BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOB ("TXT") STORE AS ( TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 12. NOCACHE STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 12.MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 12.FREELIST GROUPS 12.BUFFER_POOL DEFAULT)) |
LOB显然有以下属性：
q 一个表空间（这个例子中即为USERS）
q ENABLE STORAGE IN ROW作为一个默认属性
q CHUNK 8192
q PCTVERSION 12.
q NOCACHE
q 一个完整的STORAGE子句
由此说明，在底层LOB并不那么简单，而事实上确实如此。LOB列总是会带来一种多段对象（multisegment object，这是我对它的叫法），也就是说，这个表会使用多个物理段。如果我们在一个空模式中创建这个表，就会发现以下结果：
| ops$tkyte@ORA 10G > select segment_name, segment_type 2 from user_segments; SEGMENT_NAME SEGMENT_TYPE ------------------------------ ------------------ SYS_C0011927 INDEX SYS_IL 0000071432C 00002$$ LOBINDEX SYS_LOB 0000071432C 00002$$ LOBSEGMENT T TABLE |
这里创建了一个索引来支持主键约束，这很正常，但是另外两个段呢？即lobindex和lobsegment，它们做什么用？创建这些段是为了支持我们的LOB列。我们的实际LOB数据就存储在lobsegment中（确实，LOB数据也有可能存储在表T中，不过稍后讨论ENABLE STORAGE IN ROW子句时还会更详细地说明这个内容）。lobindex用于执行LOB的导航，来找出其中的某些部分。创建一个LOB列时，一般来说，存储在行中的这是一个指针（pointer），或LOB定位器（LOB locator）。我们的应用所获取的就是这个LOB定位器。当请求得到LOB的“12.000～2,000字节”时，将对lobindex使用LOB定位器来找出这些字节存储在哪里，然后再访问lobsegment。可以用lobindex很容易地找到LOB的各个部分。由此说来，可以把LOB想成是一种主/明细关系。LOB按“块”（chunk）或（piece）来存储，每个片段都可以访问。例如，如果我们使用表来实现一个LOB，可以如下做到这一点：
| Create table parent ( id int primary key, other-data... );
Create table lob ( id references parent on delete cascade, chunk_number int, data <datatype>(n), primary key (id,chunk_number) ); |
从概念上讲，LOB的存储与之非常相似，创建这两个表时，在LOB表的ID.CHUNK_NUMBER上要有一个主键（这对应于Oracle创建的lobindex），而且要有一个LOB表来存储数据块（对应于lobsegment）。LOB列为我们透明地实现了这种主/明细结构。图12.-3可以更清楚地展示这个思想。
图12.-3 表-lobindex-lobsegment的对于关系
表中的LOB实际上只是指向lobindex，lobindex再指向LOB本身的各个部分。为了得到LOB中的N~M字节，要对表中的指针（LOB定位器）解除引用，遍历lobindex结构来找到所需的数据库（chunk），然后按顺序访问。这使得随机访问LOB的任何部分都能同样迅速，你可以用同样快的速度得到LOB的最前面、中间或最后面的部分，因为无需再从头开始遍历LOB。
既然已经从概念上了解了LOB如何存储，下面我将逐个介绍前面所列的各个可选设置，并解释它们的用途以及有什么具体含义。
1. LOB表空间
从DBMS_METADATA返回的CREATE TABLE语句包括以下内容：
| LOB ("TXT") STORE AS ( TABLESPACE "USERS" ... |
这里指定的TABLESPACE是将存储lobsegment和lobindex表空间，这可能与表本身所在的表空间不同。也就是说，保存LOB数据的表空间可能不同于保存实际表数据的表空间。
为什么考虑为LOB数据使用另外一个表空间（而不用表数据所在的表空间）呢？注意原因与管理和性能有关。从管理的角度看，LOB数据类型表示一种规模很大的信息。如果表有数百万行，而每行有一个很大的LOB，那么LOB就会极为庞大。为LOB数据单独使用一个表空间有利于备份和恢复以及空间管理，单从这一点考虑，将表与LOB数据分离就很有意义。例如，你可能希望LOB数据使用另外一个统一的区段大小，而不是普通表数据所用的区段大小。
另一个原因则出于I/O性能的考虑。默认情况下，LOB不在缓冲区缓存中进行缓存（有关内容将在后面再做说明）。因此，默认情况下，对于每个LOB访问，不论是读还是写，都会带来一个物理I/O（从磁盘直接读，或者向磁盘直接写）。
注意 LOB可能是内联的（inline），或者存储在表中。在这种情况下，LOB数据会被缓存，但是这只适用于小于4,000字节的LOB。我们将在“IN ROW子句”一节中进一步讨论这种情况。
由于每个访问都是一个物理I/O，所以如果你很清楚在实际中（当用户访问时）有些对象会比大多数其他对象经历更多的物理I/O，那么将这些对象分离到它们自己的磁盘上就很有意义。
需要说明，lobindex和lobsegment总是会在同一个表空间中。不能将lobindex和lobsegment放在不同的表空间中。在Oralce的更早版本中，允许为lobindex和lobsegment分别放在单独的表空间中，但是从8i Release 3以后，就不再允许为lobindex和logsegment指定不同的表空间。实际上，lobindex的所有存储特征都是从lobsegment继承的，稍后就会看到。
2. IN ROW子句
前面的DBMS_METADATA返回的CREATE TABLE语句还包括以下内容：
| LOB ("TXT") STORE AS (... ENABLE STORAGE IN ROW ... |
这控制了LOB数据是否总与表分开存储（存储在lobsegment中），或是有时可以与表一同存储，而不用单独放在lobsegment中。如果设置了ENABLE STORAGE IN ROW，而不是DISABLE STORAGE IN ROW，小LOB（最多4,000字节）就会像VARCHAR2一样存储在表本身中。只有当LOB超过了4,000字节时，才会“移出”到lobsegment中。
默认行为是启用行内存储（ENABLE STORAGE IN ROW），而且一般来讲，如果你知道LOB总是能在表本身中放下，就应该采用这种默认行为。例如，你的应用可能有一个某种类型的DESCRIPTION字段。这个DESCRIPTION可以存储0～32KB的数据（或者可能更多，但大多数情况下都少于或等于32KB）。已知很多描述都很简短，只有几百个字符。如果把它们单独存储，并在每次获取时都通过索引来访问，就会存在很大的开销，你完全可以将它们内联存储，即放在表本身中，这就能避免单独存储的开销。不仅如此，如果LOB还能避免获取LOB时所需的物理I/O。
下面通过一个非常简单的例子来看看这种设置的作用。我们将创建包括有两个LOB的表，其中一个LOB可以在行内存储数据，而另一个LOB禁用了行内存储：
| ops$tkyte@ORA 10G > create table t 2 ( id int primary key, 3 in_row clob, 4 out_row clob 5 ) 6 lob (in_row) store as ( enable storage in row ) 7 lob (out_row) store as ( disable storage in row ) 8 / Table created. |
在这个表中，我们将插入一些串数据，所有这些串的长度都不超过4,000字节：
| ops$tkyte@ORA 10G > insert into t 2 select rownum, 3 owner || ' ' || object_name || ' ' || object_type || ' ' || status, 4 owner || ' ' || object_name || ' ' || object_type || ' ' || status 5 from all_objects 6 / 48592 rows created.
ops$tkyte@ORA 10G > commit; Commit complete. |
现在，如果我们想读取每一行，在此使用了DBMS_MONITOR包，并启用了SQL_TRACE，执行这个工作时，可以看到这两个表获取数据时的性能：
| ops$tkyte@ORA 10G > declare 2 l_cnt number; 3 l_data varchar2(32765); 4 begin 5 select count(*) 6 into l_cnt 7 from t; 8 9 dbms_monitor.session_trace_enable; 12. for i in 1 .. l_cnt 12. loop 12. select in_row into l_data from t where id = i; 12. select out_row into l_data from t where id = i; 12. end loop; 12. end; 12. / PL/SQL procedure successfully completed. |
查看这个小仿真的TKPROF报告时，结果一目了然：
| SELECT IN_ROW FROM T WHERE ID = :B1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 48592 2.99 2.78 0 0 0 0 Fetch 48592 12.84 12.80 0 145776 0 48592 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 97185 4.83 4.59 0 145776 0 48592 Rows Row Source Operation ------- --------------------------------------------------- 48592 TABLE ACCESS BY INDEX ROWID T (cr=145776 pr=0 pw=0 time=1770453 us) 48592 INDEX UNIQUE SCAN SYS_C0011949 (cr=97184 pr=0 pw=0 time=960814 us) ******************************************************************************** SELECT OUT_ROW FROM T WHERE ID = :B1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 48592 2.21 2.12. 0 0 0 0 Fetch 48592 7.33 8.49 48592 291554 0 48592 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 97185 9.54 12..62 48592 291554 0 48592 Rows Row Source Operation ------- --------------------------------------------------- 48592 TABLE ACCESS BY INDEX ROWID T (cr=145776 pr=0 pw=0 time=1421463 us) 48592 INDEX UNIQUE SCAN SYS_C0011949 (cr=97184 pr=0 pw=0 time=737992 us) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ direct path read 48592 0.00 0.25 |
获取IN_ROW列显著地快得多，而且所占用的资源也远远少于OUT_ROW列。可以看到，它使用了145,776次逻辑I/O（查询模式获取），而OUT_ROW列使用的逻辑I/O次数是它的两倍。初看上去，我们不太清楚这些额外的逻辑I/O是从哪里来的，不过，如果你还记得LOB是如何存储的就会明白，这是对lobindex段的I/O（为了找到LOB的各个部分）。这些额外的逻辑I/O都针对这个lobindex.
另外，可以看到，对于OUT_ROW列，获取48,592行会带来48,592次物理I/O，而这会导致同样数目的“直接路径读”I/O等待。这些都是对非缓存LOB数据的读取。在这种情况下，通过启用LOB数据的缓存，可以缓解这个问题，但是这样一来，我们又必须确保为此要有足够多的额外的缓冲区缓存。另外，如果确实有非常大的LOB，我们可能并不希望缓存这些数据。
这种行内/行外存储设置不仅会影响读，还会影响修改。如果我们要用小串更新前100行，并用小串插入100个新行，再使用同样的技术查看性能，会观察到：
| ops$tkyte@ORA 10G > create sequence s start with 100000; Sequence created.
ops$tkyte@ORA 10G > declare 2 l_cnt number; 3 l_data varchar2(32765); 4 begin 5 dbms_monitor.session_trace_enable; 6 for i in 1 .. 100 7 loop 8 update t set in_row = to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') where id = i; 9 update t set out_row = to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') where id = i; 12. insert into t (id, in_row) values ( s.nextval, 'Hello World' ); 12. insert into t (id,out_row) values ( s.nextval, 'Hello World' ); 12. end loop; 12. end; 12. / PL/SQL procedure successfully completed. |
在得到的TKPROF报告中可以观察到类似的结果：
| UPDATE T SET IN_ROW = TO_CHAR(SYSDATE,'dd-mon-yyyy hh24:mi:ss') WHERE ID = :B1
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 100 0.05 0.02 0 200 202 100 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 101 0.05 0.02 0 200 202 100 Rows Row Source Operation ------- --------------------------------------------------- 100 UPDATE (cr=200 pr=0 pw=0 time=15338 us) 100 INDEX UNIQUE SCAN SYS_C0011949 (cr=200 pr=0 pw=0 time=2437 us) ******************************************************************************** UPDATE T SET OUT_ROW = TO_CHAR(SYSDATE,'dd-mon-yyyy hh24:mi:ss') WHERE ID = :B1
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 100 0.07 0.12. 0 1100 2421 100 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 101 0.07 0.12. 0 1100 2421 100 Rows Row Source Operation ------- --------------------------------------------------- 100 UPDATE (cr=1100 pr=0 pw=100 time=134959 us) 100 INDEX UNIQUE SCAN SYS_C0011949 (cr=200 pr=0 pw=0 time=2180 us) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ direct path write 200 0.00 0.00 |
可以看到，行外LOB的更新占用了更多的资源。它要花一定的时间完成直接路径写（物理I/O），并执行更多的当前模式获取以及查询模式获取。这些都源于一点，即除了维护表本身外，还必须维护lobindex和lobsegment。INSERT操作也显示出了同样的差异：
| INSERT INTO T (ID, IN_ROW) VALUES ( S.NEXTVAL, 'Hello World' ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 100 0.03 0.02 0 2 316 100 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 101 0.03 0.02 0 2 316 100 ******************************************************************************** INSERT INTO T (ID,OUT_ROW) VALUES ( S.NEXTVAL, 'Hello World' ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 100 0.08 0.12. 0 605 1839 100 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 101 0.08 0.12. 0 605 1839 100 Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ direct path write 200 0.00 0.00 |
注意读和写使用的I/O都有所增加。总之，由此显示出，如果使用一个CLOB，而且很多串都能在“行内”放下（也就是说，小于4,000字节），那么使用默认的ENABLE STORAGE IN ROW设置就是一个不错的想法。
3. CHUNK子句
前面的DBMS_METADATA返回的CREATE TABLE语句包括以下内容：
| LOB ("TXT") STORE AS ( ... CHUNK 8192 ... ) |
LOB存储在块（chunk）中；指向LOB数据的索引会指向各个数据块。块（chunk）是逻辑上连续的一组数据库块（block），这也是LOB的最小分配单元，而通常数据库的最小分配单元是数据库块。CHUNK大小必须是Oracle块大小的整数倍，只有这样才是合法值。
从两个角度看，选择CHUNK大小时必须当心。首先，每个LOB实例（每个行外存储的LOB值）会占用至少一个CHUNK。一个CHUNK有一个LOB值使用。如果一个表有100行，而每行有一个包含7KB数据的LOB，你就会分配100个CHUNK，如果将CHUNK大小设置为32KB，就会分配100个32KB的CHUNK。如果将CHUNK大小设置为8KB，则（可能）分配100个8KB的CHUNK。关键是，一个CHUNK只能有一个LOB使用（两个LOB不会使用同一个CHUNK）。如果选择了一个CHUNK大小，但不符合你期望的LOB大小，最后就会浪费大量的空间。例如，如果表中的LOB平均有7KB，而你使用的CHUNK大小为32KB，对于每个LOB实例你都会“浪费”大约25KB的空间，另一方面，倘若使用8KB的CHUNK，就能使浪费减至最少。
还需要注意要让每个LOB实例相应的CHUNK数减至最少。前面已经看到了，有一个lobindex用于指向各个块，块越多，索引就越大。如果有一个4MB的LOB，并使用8KB的CHUNK，你就至少需要512个CHUNK来存储这个消息。这也说明，至少需要512个lobindex条目指向这些CHUNK。听上去好像没什么，但是你要记住，对于每个LOB个数的512倍。另外，这还会影响获取性能，因为与读取更少但更大的CHUNK相比，现在要花更长的数据来读取和管理许多小CHUNK。我们最终的目标是：使用一个能使“浪费”最少，同时又能高效存储数据的CHUNK大小。
4. PCTVERSION子句
前面的DBMS_METADATA返回的CREATE TABLE语句包括以下内容：
| LOB ("TXT") STORE AS ( ... PCTVERSION 12. ... ) |
这用于控制LOB的读一致性。在前面的几章中，我们已经讨论了读一致性、多版本和undo在其中所起的作用。但LOB实现读一致性的方式有所不同。lobsegment并不使用undo来记录其修改；而是直接在lobsegment本身中维护信息的版本。lobindex会像其他段一样生成undo，但是lobsegment不会。相反，修改一个LOB时，Oracle会分配一个新的CHUNK，并且仍保留原来的CHUNK。如果回滚了事务，对LOB索引所做的修改会回滚，索引将再次指向原来的CHUNK。因此，undo维护会在LOB段本身中执行。修改数据时，原来的数据库保持不动，此外会创建新数据。
读LOB数据时这也很重要。LOB是读一致的，这与所有其他段一样。如果你在上午9:00获取一个LOB定位器，你从中获取的LOB数据就是“上午9:00那个时刻的数据”。这就像是你在上午9:00打开了一个游标（一个结果集）一样，所生成的行就是那个时间点的数据行。与结果集类似，即使别人后来修改了LOB数据。在此，Oracle会使用lobsegment，并使用logindex的读一致视图来撤销对LOB的修改，从而提取获取LOB定位器当时的LOB数据。它不会使用logsegment的undo信息，因为根本不会为logsegment本身生成undo信息。
可以很容易地展示LOB是读一致的，考虑以下这个小表，其中有一个行外LOB（存储在logsegment中）：
| ops$tkyte@ORA 10G > create table t 2 ( id int primary key, 3 txt clob 4 ) 5 lob( txt) store as ( disable storage in row ) 6 / Table created.
ops$tkyte@ORA 10G > insert into t values ( 1, 'hello world' ); 12.row created.
ops$tkyte@ORA 10G > commit; Commit complete. |
如果取出LOB定位器，并在这个表上打开一个游标，如下：
| ops$tkyte@ORA 10G > declare 2 l_clob clob; 3 4 cursor c is select id from t; 5 l_id number; 6 begin 7 select txt into l_clob from t; 8 open c; |
然后修改行，并提交：
| 9 12. update t set id = 2, txt = 'Goodbye'; 12. commit; 12. |
可以看到，通过使用LOB定位器和打开的游标，会提供“获取LOB定位器或打开游标那个时间点”的数据：
| 12. dbms_output.put_line( dbms_lob.substr( l_clob, 100, 1 ) ); 12. fetch c into l_id; 12. dbms_output.put_line( 'id = ' || l_id ); 12. close c; 12. end; 12. / hello world id = 1
PL/SQL procedure successfully completed. |
但是数据库中的数据很可能已经更新/修改：
| ops$tkyte@ORA 10G > select * from t; ID TXT ---------- --------------- 2 Goodbye |
游标C的读一致映像来自undo段，而LOB的读一致映像则来自LOB段本身。
由此，我们要考虑这样一个问题：如果不用undo段来存储回滚LOB所需要的信息，而且LOB支持读一致性，那我们怎么避免发生可怕的ORA-01555:snapshot too old错误呢？还有一点同样重要，如何控制这些旧版本占用的空间呢？这正是PCTVERSION起作用的地方。
PCTVERSION控制着用于实现LOB数据版本化的已分配LOB空间的百分比（这些数据库块由某个时间点的LOB所用，并处在lobsegment的HWM以下）。对于许多使用情况来说，默认设置12.%就足够了，因为在很多情况下，你只是要INSERT和获取LOB（通常不会执行LOB的更新；LOB往往会插入一次，而获取多次）。因此，不必为LOB版本化预留太多的空间（甚至可以没有）。
不过，如果你的应用确实经常修改LOB，倘若你频繁地读LOB，与此同时另外某个会话正在修改这些LOB，12.%可能就太小了。如果处理LOB时遇到一个ORA-22924错误，解决方案不是增加undo表空间的大小，也不是增加undo保留时间（UNDO_RETENTION），如果你在使用手动undo管理，那么增加更多RBS空间也不能解决这个问题。而是应该使用以下命令：
| ALTER TABLE tabname MODIFY LOB (lobname) ( PCTVERSION n ); |
并增加lobsegment中为实现数据版本化所用的空间大小。
5. RETENTION子句
这个子句与PCTVERSION子句是互斥的，如何数据库中使用自动undo管理，就可以使用这个子句。RETENTION子句并非在lobsegment中保留某个百分比的空间来实现LOB的版本化，而是使用基于时间的机制来保留数据。数据库会设置参数UNDO_RETENTION，指定要把undo信息保留多长时间来保证一致读。在这种情况下，这个参数也适用于LOB数据。
需要注意，不能使用这个子句来指定保留时间；而要从数据库的UNDO_RETENTION设置来继承它。
6. CACHE子句
前面的DBMS_METADATA返回的CREATE TABLE语句包括以下内容：
| LOB ("TXT") STORE AS (... NOCACHE ... ) |
除了NOCACHE，这个选项还可以是CACHE或CACHE READS。这个子句控制了lobsegment数据是否存储在缓冲区缓存中。默认的NOCACHE指示，每个访问都是从磁盘的一个直接读，类似地，每个写/修改都是对大盘的一个直接写。CACHE READS允许缓存从磁盘读的LOB数据，但是LOB数据的写操作必须直接写至磁盘。CACHE则允许读和写时都能缓存LOB数据。
在许多情况下，默认设置可能对我们并不合适。如果你只有小规模或中等规模的LOB（例如，使用LOB来存储只有几KB的描述性字段），对其缓存就很有意义。如果不缓存，当用户更新描述字段时，还必须等待I/O将数据写指磁盘（将执行一个CHUNK大小的I/O，而且用户要等待这个I/O完成）。如果你在执行多个LOB的加载，那么加载每一行时都必须等待这个I/O完成。所以启用执行LOB缓存很合理。你可以打开和关闭缓存，来看看会有什么影响：
| ALTER TABLE tabname MODIFY LOB (lobname) ( CACHE ); ALTER TABLE tabname MODIFY LOB (lobname) ( NOCACHE ); |
对于一个规模很多的初始加载，启用LOB的缓存很有意义，这允许DBWR在后台将LOB数据写至磁盘，而你的客户应用可以继续加载更多的数据。对于频繁访问或修改的小到中等规模的LOB，缓存就很合理，可以部门让最终用户实时等待物理I/O完成。不过，对于一个大小为50MB的LOB，把它放在缓存中就没带道理了。
要记住，此时可以充分使用Keep池或回收池。并非在默认缓存中将lobsegment数据与所有“常规”数据一同缓存，可以使用保持池或回收池将其分开缓存。采用这种方式，既能缓存LOB数据，而且不影响系统中现有数据的缓存。
7. LOB STORAGE子句
最后，前面的DBMS_METADATA返回的CREATE TABLE语句还包括以下内容：
| LOB ("TXT") STORE AS ( ... STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 12.MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 12.BUFFER_POOL DEFAULT ) ... ) |
也就是说，它有一个完整的存储子句，可以用来控制物理存储特征。需要指出，这个存储子句同样适用于lobsegment和lobindex，对一个段的设置也可以用于另一个段。假设有一个本地管理的表空间，LOB的相关设置将是FREELISTS、FREELIST GROUPS和BUFFER_POOL。我们在第12.章讨论过FREELISTS和FREELIST GROUPS与表段的关系。这些讨论同样适用于lobindex段，因为lobindex与其他索引段的管理是一样的。如果需要高度并发地修改LOB，可能最好在索引段上设置多个FREELISTS。
上一节已经提到，对LOB段使用保持池或回收池可能是一个很有用的技术，这样就能缓存LOB数据，而且不会“破坏”现有的默认缓冲区缓存。并不是将LOB与常规表一同放在块缓冲区中，可以在SGA中专门为这些LOB对象预留一段专用的内存。BUFFER_POOL子句可以达到这个目的。
12.7.2 BFILE
我们要讨论的最后一种LOB类型是BFILE类型。BFILE类型只是操作系统上一个文件的指针。它用于为这些操作系统文件提供只读访问。
注意 内置包UTL_FILE也为操作系统文件提供了读写访问。不过它没有使用BFILE类型。
使用BFILE时，还有使用一个Oracle DIRECTORY对象。DIRECTORY对象只是将一个操作系统目录映射至数据库中的一个“串”或一个名称（以提供可移植性；你可能想使用BFILE中的一个串，而不是操作系统特定的文件名约定）。作为一个小例子，下面创建一个带BFILE列的表，并创建一个DIRECTORY对象，再插入一行，其中引用了文件系统中的一个文件：
| ops$tkyte@ORA 10G > create table t 2 ( id int primary key, 3 os_file bfile 4 ) 5 / Table created.
ops$tkyte@ORA 10G > create or replace directory my_dir as '/tmp/' 2 / Directory created.
ops$tkyte@ORA 10G > insert into t values ( 1, bfilename( 'MY_DIR', 'test.dbf' ) ); 12.row created. |
现在，就可以把BFILE当成一个LOB来处理，因为它就是一个LOB。例如，我们可以做下面的工作：
| ops$tkyte@ORA 10G > select dbms_lob.getlength(os_file) from t;
DBMS_LOB.GETLENGTH(OS_FILE) --------------------------- 1056768 |
可以看到所指定的文件大小为1MB。注意，这里故意在INSERT语句中使用了MY_DIR。如果使用混合大小写或小写，会得到以下错误：
| ops$tkyte@ORA 10G > update t set os_file = bfilename( 'my_dir', 'test.dbf' ); 12.row updated.
ops$tkyte@ORA 10G > select dbms_lob.getlength(os_file) from t; select dbms_lob.getlength(os_file) from t * ERROR at line 1: ORA-22285: non-existent directory or file for GETLENGTH operation ORA-06512: at "SYS.DBMS_LOB", line 566 |
这个例子只是说明：Oracle中的DIRECTORY对象是标识符，而默认情况下标识符都以大写形式存储。BFILENAME内置函数接受一个串，这个串的大小写必须与数据字典中存储的DIRECTORY对象的大小写完全匹配。所以，我们必须在BFILENAME函数中使用大写，或者在创建DIRECTORY对象时使用加引号的标识符：
| ops$tkyte@ORA 10G > create or replace directory "my_dir" as '/tmp/' 2 / Directory created.
ops$tkyte@ORA 10G > select dbms_lob.getlength(os_file) from t;
DBMS_LOB.GETLENGTH(OS_FILE) --------------------------- 1056768 |
我不建议使用加引号的标识符；而倾向于在BFILENAME调用中使用大写。加引号的标识符属于“异类”，可能会在以后导致混淆。
BFILE在磁盘上占用的空间不定，这取决于DIRECTORY对象名的文件名的长度。在前面的例子中，所得到的BFILE长度大约为35字节。一般来说，BFILE会占用大约20字节的开销，再加上DIRECTORY对象的长度以及文件名本身的长度。
与其他LOB数据不同，BFILE数据不是“读一致”的。由于BFILE在数据库之外管理，对BFILE解除引用时，不论文件上发生了什么，都会反映到你得到的结果中。所以，如果反复读同一个BFILE，可能会产生不同的结果，这与对CLOB、BLOB或NCLOB使用LOB定位器不同。
12.8 ROWID/UROWID类型
最后要讨论的数据类型是ROWID和UROWID类型。ROWID是数据库中一行的地址。ROWID中编入了足够多的信息，足以在磁盘上找到行，以及标识ROWID所指向的对象（表等）。ROWID有一个“近亲”UROWID，它用于表，如IOT和通过异构数据库网关访问的没有固定ROWID表。UROWID是行主键值的一个表示，因此，其大小不定，这取决于它指向的对象。
每个表中的每一行都有一个与之关联的ROWID或UROWID。从表中获取时，把它们看作为伪列（pseudo column），这说明它们并不真正存储在行中，而是行的一个推导属性。ROWID基于行的物理位置生成；它并不随行存储。UROWID基于行的主键生成，所以从某种意义上讲，好像它是随行存储的，但是事实上并非如此，因为UROWID并不作为一个单独的列存在，而只是作为现有列的一个函数。
对于有ROWID的行（Oracle中最常见的行“类型”；除了IOT中的行之外，所有行都有ROWID），以前ROWID是不可变的。插入一行时，会为之关联一个ROWID（一个地址），而且这个ROWID会一直与该行关联，直到这一行被删除（被物理地从数据库删除）。但是，后来情况发生了变化，因为现在有些操作可能会导致行的ROWID改变，例如：
q 在分区表中更新一行的分区键，使这一行必须从一个分区移至另一个分区。
q 使用FLASHBACK TABLE命令将一个数据库表恢复到以前的每个时间点。
q 执行MOVE操作以及许多分区操作，如分解或合并分区。
q 使用ALTER TABLE SHRINK SPACE命令执行段收缩。
如今，由于ROWID可能过一段时间会改变（因为它不再是不可变的），所以不建议把它们作为单独的列物理地存储在数据库表中。也就是说，使用ROWID作为一个数据库列的数据类型被认为是一种不好的实践做法。应当避免这种做法，而应使用行的主键（这应该是不可变的），另外引用完整性可以确保数据的完整性。对此用ROWID类型是做不到的，不能用ROWID创建从子表到一个父表的外键，而且不能保证跨表的完整性。你必须使用主键约束。
那ROWID类型有什么用呢？在允许最终用户与数据交互的应用中，ROWID还是有用的。ROWID作为行的一个物理地址，要访问任何表中的某一行，这是最快的方法。如果应用从数据库读出数据并将其提供给最终用户，它试图更新这一行时就可以使用ROWID。应用这种方式，只需最少的工作就可以更新当前行（例如，不需要索引查找再次寻找行），并通过验证行值未被修改来确保这一行与最初读出的行是同一行。所以，在采用乐观锁定的应用中ROWID还是有用的。
12.9 小结
在这一章中，我们分析了Oracle提供的22种基本数据类型，并了解了这些数据类型如何物理地存储，某种类型分别有哪些选项。首先介绍的是字符串，这是最基本的一种类型，并详细讨论了有关多字节字符和原始二进制数据所要考虑的问题。接下来，我们研究了数值类型，包括非常精确的Oracle NUMBER类型和Oracle 10g 以后版本提供的新的浮点类型。
我们还充分考虑了遗留的LONG和LONG RAW类型，强调了如何绕开它们另辟蹊径，因为这些类型提供的功能远远比不上LOB类型提供的功能。接下来，我们讨论了能存储日期和时间的数据类型。在此介绍了日期运算的基本原理，这个问题很让人费解，如果没有实例演示，将很难搞清楚。最后，在讨论DATE和TIMESTAMP的一节中，我们讨论了INTERVAL类型，并说明了如何最好地加以使用。
从物理存储角度来看，这一章讲得最多、最详细的就是LOB一节。LOB类型常常被开发人员和DBA所误解，所以这一节用了很多篇幅来解释LOB如何物理地实现，并分析了一些要考虑的性能问题。
我们最后介绍的数据类型是ROWID/UROWID类型。由于很明显的原因（你现在应该知道这些原因了），不要把这个数据类型用作数据库列的类型，因为ROWID不再是不可变的，而且没有完整性约束可以保证父/子关系。如果需要“指向”另一行，正确的做法可能是存储主键。
32万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
