Oralce 数据类型（9i&10g学习）

Oracle提供了22种不同的SQL数据类型供我们使用。

CHAR：这是一个定长字符串，会用空格填充来达到其最大长度。非null的CHAR(12)总是包含12字节信息（使用了默认国家语言支持National Language Support，NLS设置）。最大2000个字节

NCHAR：这是一个包含UNICODE格式数据的定长字符串。Unicode是一种对字符进行编码的通用方法，而不论使用的是何种计算机系统平台。有了NCHAR类型，就允许数据库中包含采用两种不同字符集的数据：使用数据库字符集的CHAR类型和使用国家字符集的NCHAR类型。非null的NCHAR(12)总是包含12个字符的信息（注意，在这方面，它与CHAR类型有所不同）。NCHAR字段最多可以存储2,000字节的信息。

VARCHAR2：目前这也是VARCHAR的同义词。这是一个变长字符串，与CHAR类型不同，它不会用空格填充至最大长度。VARCHAR2(12)可能包含0～12.字节的信息（使用默认NLS设置）。VARCHAR2最多可以存储4,000字节的信息。

NVARCHAR2：这是一个包含UNICODE格式数据的变长字符串。NVARCHAR2(12)可以包含0～12字符的信息。NVARCHAR2最多可以存储4,000字节的信息。
CHAR/NCHAR实际上只是伪装的VARCHAR2/NVARCHAR2,基于这一点，所以我认为其实只需要考虑这两种字符串类型：VARCHAR和NVARCHAR2。我从来没有见过哪个应用适合使用CHAR类型。因为CHAR类型总是会用空格填充得到的串，使之达到一个固定宽度，所以我们很快就会发现：不论在表段还是任何索引段中，CHAR都会占用最大的存储空间CHAR/NCHAR实际上只是伪装的VARCHAR2/NVARCHAR2,基于这一点，所以我认为其实只需要考虑这两种字符串类型：VARCHAR和NVARCHAR2。我从来没有见过哪个应用适合使用CHAR类型。因为CHAR类型总是会用空格填充得到的串，使之达到一个固定宽度，所以我们很快就会发现：不论在表段还是任何索引段中，CHAR都会占用最大的存储空间

CHAR/NCHAR实际上只是伪装的VARCHAR2/NVARCHAR2,基于这一点，所以我认为其实只需要考虑这两种字符串类型：VARCHAR和NVARCHAR2。我从来没有见过哪个应用适合使用CHAR类型。因为CHAR类型总是会用空格填充得到的串，使之达到一个固定宽度，所以我们很快就会发现：不论在表段还是任何索引段中，CHAR都会占用最大的存储空间

Oracle中尾部的NULL列占用0字节存储空间，这说明，如果表中的“最后一列”为NULL，Oracle不会为之存储任何内容。如果最后两列都是NULL，那么对这两列都不会存储任何内容。但是，如果位于NULL列之后的某个列要求为not null（即不允许为null），Oracle会使用这一节介绍的null标志来指示这个列缺少值。

RAW：这是一种变长二进制数据类型，这说明采用这种数据类型存储的数据不会发生字符集转换。可以把它看作由数据库存储的信息的二进制字节串。这种类型最多可以存储2,000字节的信息。一般不推荐使用

NUMBER：这种数据类型能存储精度最多达38位的数字。这些数介于12.0×1.(-130)——（但不包括）12.0×12(126)之间。每个数存储在一个变长字段中，其长度在0（尾部的NULL列就是0字节）～22字节之间。Oracle的NUMBER类型精度很高，远远高于许多编程语言中常规的FLOAT和DOUBLE类型。numer也会丢失精度。
NUMBER( p,s )
在此P和S是可选的，用于指定：
精度（precision），或总位数。默认情况下，精度为38位，取值范围是1～38之间。也可以用字符*表示38。
小数位置（scale），或小数点右边的位数。小数位数的合法值为-48～127，其默认值取决于是否指定了精度。

可用于四舍五入：如number(5,2)插入12345.126真正插入的值为12345.13

NUMBER类型实际上是磁盘上的一个变长数据类型，会占用0～22字节的存储空间，。Oracle存储一个数时，会存储尽可能少的内容来表示这个数。为此会存储有效数字、用于指定小数点位置的一个指数，以及有关数值符号的信息（正或负）。因此，数中包含的有效数字越多，占用的存储空间就越大

BINARY_FLOAT/BINARY_DOUBLE：这是Oracle 10g Release 1及以后版本中才有的一种新类型。它是一个32位单精度浮点数，可以支持至少6位精度，占用磁盘上5字节的存储空间。
浮点数用于近似数值m它们没有前面所述的内置Oracle NUMBER类型那么精确。浮点数常用在科学计算中，由于允许在硬件（CPU、芯片）上执行运算，而不是在Oracle子例程中运算，所以在多种不同类型的应用中都很有用。因此，如果在一个科学计算应用中执行实数处理，算术运算的速度会快得多，不过你可能不希望使用浮点数来存储金融信息。

除了NUMBER、BINARY_FLOAT和BINARY_DOUBLE类型，Oracle在语法上还支持以下数值数据类型：
NUMERIC(p,s)：完全映射至NUMBER(p,s)。如果p未指定，则默认为38.
DECIMAL(p,s)或DEC(p,s)：完全映射至NUMBER(p,s)。如果p为指定，则默认为38.
INTEGER或INT：完全映射至NUMBER(38)类型。
SMALLINT：完全映射至NUMBER(38)类型。
FLOAT(b)：映射至NUMBER类型。
DOUBLE PRECISION：映射至NUMBER类型。
REAL：映射至NUMBER类型。

注意 这里我指出“在语法上支持“，这是指CREATE语句可以使用这些数据类型，但是在底层实际上它们都只是NUMBER类型。准确地将，Oracle 10g Release 1及以后的版本中有3种固有数值格式，Oracle9i Release 2及以前的版本中只有1种固有数值格式。使用其他的任何数值数据类型总是会映射到固有的Oracle NUMBER类型。

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

LONG：这种类型能存储最多2G的字符数据，与VARCHAR2或CHAR类型一样，存储在LONG类型中的文本要进行字符集转换。由于LONG类型有许多限制，而且提供LONG类型只是为了保证向后兼容性，所以强烈建议新应用中不要使用LONG类型，而且在现有的应用中也要尽可能将LONG类型转换为CLOB类型。

Oracle SQL Reference手册指出：不要创建带LONG列的表，而应该使用LOB列（CLOB、NCLOB、BLOB）。支持LONG列只是为了保证向后兼容性。

LONG RAW：LONG RAW类型能存储多达2GB的二进制信息。由于LONG同样的原因，建议在将来的所有开发中都使用CLOB类型，另外现有的应用中也应尽可能将LONG RAW转换为BLOB类型。

DATE：这是一个7字节的定宽日期/时间数据类型。其中总包含7个属性，包括：世纪、世纪中哪一年、月份、月中的哪一天、小时、分钟和秒。Oracle使用一种内部格式来表示这个信息，所以它并不是存储20，05，06，25，12.，01，00来表示2005年6月25日12.:01:00

格式对数据如何存储根本没有任何影响。格式只是用于将存储DATE所用的二进制格式转换为一个串，或者将一个串转换为用于存储DATE的二进制格式。对于TIMESTAMP和INTERVAL也是如此。应该使用格式。将一个表示DATE、TIMESTAMP或INTERVAL的 串发送到数据库时就可以使用格式。不要依赖于默认日期格式。

常用日期函数

add_months(d,n)<d是日期，n是月数>：返回指定日期加上指定月数后的日期值。 
months_between(d1,d2)<d1,d2都是两个日期>:返回两个日期之间的月数。 
last_day(d)<d是日期>:返回指定日期当月的最后一天的日期值。 
round(d,[fmt])<d是日期，fmt指定格式模型>:返回日期值，此日期四舍五入为格式模型指定的单位。
(fmt是一个选项，默认舍入为最靠近的那一天。
如果格式为‘year’则舍入到年的开始,即1月1日；
如果格式为‘month’则舍入到月的第一天；
如果格式为‘day’则舍入到最靠近的星期天。)
next_day(d,day)<d是日期，day指定周内任何一天 如‘星期一’>：返回指定的下一个星期几的日期。
trunc(d,[fmt])<d是日期，fmt指定格式模型>:返回日期值，指定日期截断为格式模型指定的单位的日期。(与round函数类似,不同的是它只舍不入。)
extract(fmt from d)<fmt指定格式模型,d是日期>:提取日期时间类型中的特定部分。(fmt取值可以是:year,month,day,hour,minute,second;注意此处的格式不能使用单引号。)

TIMESTAMP：这是一个7字节或12.字节的定宽日期/时间数据类型。它与DATE数据类型不同，因为TIMESTAMP可以包含小数秒（fractional second）；带小数秒的TIMESTAMP在小数点右边最多可以保留9位。

TIMESTAMP(n)这里N是可选的，用于指定TIMESTAMP中秒分量的小数位数，可以取值为0～9.如果指定0，TIMESTAMP在功能上则与DATE等价，它们实际上会以同样的方式存储相同的值。

DATE执行日期算术运算所用的技术同样适用于TIMESTAMP，但是在使用上述技术的很多情况下，TIMESTAMP会转换为一个DATE，所以精度会丢失。所以用INTERVAL类型的函数更合适

tkyte@ORA10G> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
Session altered.
ops$tkyte@ORA10G> 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@ORA10G> 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天）。

尽管将DATE相减的结果是一个NUMBER，但TIMESTAMP相减的结果却是一个INTERVAL

TIMESTAMP WITH TIME ZONE：与前一种类型类似，这是一个12.字节的定宽TIMESTAMP，不过它还提供了时区（TIME ZONE）支持。数据中会随TIMESTAMP存储有关时区的额外信息，所以原先插入的TIME ZONE会与数据一同保留。

TIMESTAMP WITH LOCAL TIME ZONE：与TIMESTAMP类似，这是一种7字节或12字节的定宽日期/时间数据类型；不过，这种类型对时区敏感（time zone sensitive）。如果在数据库中有修改，会参考数据中提供的TIMEZONE，根据数据库时区对数据中的日期/时间部分进行“规范化”。所以，如果你想使用U.S./Pacific时区插入一个日期/时间，而数据库时区为U.S./Eastern，最后的日期/时间信息会转换为Eastern时区的日期/时间，并像TIMESTAMP一样存储。获取这个数据时，数据库中存储的TIMESTAMP将转换为会话时区的时间。

INTERVAL YEAR TO MONTH：这是一个5字节的定宽数据类型，用于存储一个时间段，这个类型将时段存储为年数和月数。可以在日期运算中使用这种时间间隔使一个DATE或TIMESTAMP类型增加或减少一段时间。

INTERVAL DAY TO SECOND：这是一个12字节的定宽数据类型，用于存储一个时段，这个类型将时段存储为天/小时/分钟/秒数，还可以有最多9位的小数秒。

LOB（blob/clob）
BLOB：在Oracle9i及以前的版本中，这种数据类型允许存储最多4GB的数据，在Oracle 10g及以后的版本中允许存储最多（4GB）×（数据库块大小）字节的数据。BLOB包含不需要进行字符集转换的“二进制“数据，如果要存储电子表格、字处理文档、图像文件等就很适合采用这种数据类型。
CLOB：在Oracle9i及以前的版本中，这种数据类型允许存储最多4GB的数据，在Oracle 10g及以后的版本中允许存储最多（4GB）×（数据库块大小）字节的数据。CLOB包含要进行字符集转换的信息。这种数据类型很适合存储纯文本信息。
NCLOB：在Oracle9i及以前的版本中，这种数据类型允许存储最多4GB的数据，在Oracle 10g及以后的版本中允许存储最多（4GB）×（数据库块大小）字节的数据。NCLOB存储用数据库国家字符集编码的信息，而且像CLOB一样，这些信息要进行字符集转换。

BFILE：这种数据类型允许在数据库列中存储一个Oracle目录对象（操作系统目录的一个指针）和一个文件名，并读取这个文件。这实际上允许你以一种只读的方式访问数据库服务器上可用的操作系统文件，就好像它们存储在数据库表本身中一样。BFILE与其他LOB相比不 是读一致的

在底层LOB并不那么简单，而事实上确实如此。LOB列总是会带来一种多段对象（multisegment object），也就是说，这个表会使用多个物理段。为什么考虑为LOB数据使用另外一个表空间（而不用表数据所在的表空间）呢？注意原因与管理和性能有关。

从管理的角度看:LOB数据类型表示一种规模很大的信息。如果表有数百万行，而每行有一个很大的LOB，那么LOB就会极为庞大。为LOB数据单独使用一个表空间有利于备份和恢复以及空间管理，单从这一点考虑，将表与LOB数据分离就很有意义。例如，你可能希望LOB数据使用另外一个统一的区段大小，而不是普通表数据所用的区段大小。
I/O性能的考虑:默认情况下，LOB不在缓冲区缓存中进行缓存因此，默认情况下，对于每个LOB访问，不论是读还是写，都会带来一个物理I/O（从磁盘直接读，或者向磁盘直接写）。
lobindex和lobsegment总是会在同一个表空间中。不能将lobindex和lobsegment放在不同的表空间中。
IN ROW子句
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 是读一致的

ROWID：ROWID实际上是数据库中一行的12字节地址。ROWID中编码有足够的信息，足以在磁盘上定位这一行，以及标识ROWID指向的对象（表等）。

UROWID：UROWID是一个通用ROWID，用于表（如IOT和通过异构数据库网关访问的没有固定ROWID的表）。UROWID是行主键值的一种表示，因此，取决于所指向的对象，UROWID的大小会有所变化。
现在有些操作可能会导致行的ROWID改变，例如：
在分区表中更新一行的分区键，使这一行必须从一个分区移至另一个分区。
使用FLASHBACK TABLE命令将一个数据库表恢复到以前的每个时间点。
执行MOVE操作以及许多分区操作，如分解或合并分区。
使用ALTER TABLE SHRINK SPACE命令执行段收缩。

显然以上列表中还少了许多类型，如INT、INTEGER、SMALLINT、FLOAT、REAL等。这些类型实际上都是在上表所列的某种类型的基础上实现的，也就是说，它们只是固有Oracle类型的同义词。另外，诸如XML Type、SYS.ANYTYPE和SDO_GEOMETRY之类的类型在此也未列出。它们是一些复杂的对象类型，包括一个属性集合以及处理这些属性的一些方法（函数）。这些复杂类型由上述基本数据类型组成，并不是传统意义上真正的数据类型，而只是你在应用中可以利用的一种实现或一组