2.1　Oracle数据类型

Oracle内定的数据类型如表2-2所示。

Oracle数据库除内定的数据类型外，还支持自定义数据类型，包括对象类型（Object Type）、嵌套类型（Nested Table Type）和可变数组类型（Varray Data Type）3种。其中，对象类型（Object Type）主要是：记录数据类型（Record）和记录表类型（Table），记录数据类型（Record）存储的是一条记录，记录表类型（Table）用来存储多条记录。如果记录数据类型（Record）是一维数组，那么，记录表类型（Table）就是二维数组。

关于Oracle数据库内定的这些数据类型中，最不好把握的是4个大对象类型：blob、clob、nclob和bfile。在这4个大对象类型中，存储文章（Office文件或PDF文件等）、存储图片（jpg、png及bmp等）、存储音频（MPEG或MP3等）以及存储视频（MP4或AVI）等，建议使用blob（与字符集无关的数据类型，最大数据量为4GB）；对于存储富文本（网站上的文章等），建议使用clob（与字符集有关的数据类型，最大数据量为4GB）。其他两个，nclob和bfile，建议不要使用。blob和clob基本可满足任何需求。

2.1.1　字符型

Oracle数据库中常用的字符型数据主要包括两个，一个是char，另一个是varchar2；除此之外，还有一个不常用到的字符型数据是raw，但是它在数据字典中非常有优势，可提高数据库的效率，本小节中我们也会一并介绍。

1.char

char字符类型：固定长度的字节（byte）数据或字符（char）数据，长度由语法格式中的size指定，当实际数据长度小于定义的长度时，实际存储长度仍为定义的长度，末尾不足的部分使用空格字符补齐到定义的长度，其最大允许长度是2000字节或字符，默认长度和最小长度为1字节。

注：nchar也是Oracle数据库的字符类型，Oracle 11g下，其最大允许长度是1000字节或字符，与char的差异是支持Unicode格式的数据。

Unicode是为了解决传统字符编码方案的局限而产生的一个编码方案，它为每种语言中的每个字符设定了统一并且唯一的二进制编码，以满足跨语言、跨平台进行文本转换和处理的要求。Unicode编码共有三种具体实现，分别为utf-8、utf-16和utf-32，其中utf-8占用1～4字节，utf-16占用2～4字节，utf-32占用4字节。Unicode编码在全球范围的信息交换领域均有广泛的应用

char字符类型语法格式如下：

char［（size ［byte | char］）］

上面语法格式中，byte表示字节，char表示字符。

查看当前数据库字符长度是采用byte还是char，可以通过＂show parameter NLS_LENGTH_SEMANTICS；＂查看。一般默认是byte，即按照byte（字节）来计算字符长度。如char（2000），表示2000字节长。如果打算以字符或字节计长，而不管数据库采用的是什么字符计长标准，可以这样来写，字节计长：char（2000 byte），字符计长：char（2000 char）。

注：在SQL中如果定义了char（2000）的表字段，而实际存储的内容超过了2000字节或字符，则报错。

另外，经测试，即便表字段定义为char（2000 char），即最多存储2000字符，在数据库字符集为NLS_CHARACTERSET=ZHS16GBK的情况下（一个汉字占2字节），该字段最多可以存储1000个汉字而非2000个汉字。如果表字段定义为char（2000）或char（2000byte）那么它最多可以存储多少个汉字就要视数据库字符集编码而定。如果当前数据库采用的是GBK（NLS_CHARACTERSET=ZHS16GBK）字符集，即一个汉字占2个字节，最多可存1000个汉字；如果是UTF8字符集编码，即一个汉字占3个字节，则最多可存储2000/3=666个汉字。具体情况可通过length（）函数求得占用字符数，通过lengthb（）或者vsize（）函数求得占用字节数。如下面的例子代码：

2.varchar2

varchar2字符类型：长度可变的字节（byte）数据或字符（char）数据，长度由语法格式中的size指定，最大长度是4000字节或字符，最小长度为1字节或1字符。与char不同，必须为varchar2指定size。

注：varchar2也是Oracle数据库的字符类型，Oracle 11g下，其最大允许长度是2000字节或字符，与Varchar2的差异是，支持Unicode格式的数据。Unicode说明同上。

Oracle 12c的Varchar2字符类型，其最大可支持到32767字节。

其语法格式如下：

varchar2（size ［byte | char］）。

查看当前数据库字符长度是采用byte还是char，可以通过＂show parameter NLS_LENGTH_SEMANTICS；＂查看。一般默认是byte，如varchar2（4000），表示4000字节长。如果打算以字符或字节计长，而不管数据库采用的是什么字符计长标准，可以这样来写，字节计长：varchar2（4000 byte），字符计长：varchar2（4000 char）。

注：varchar2在Oracle的SQL和PL/SQL中都有使用，前者中varchar2的最大支持长度为4000字节；而后者中最大支持长度为32767字节。这就是有读者问，在PL/SQL中定义了32767（字符/字节），为什么在表的字段中不能定义大于4000字节的原因了。

另外，在SQL中如果定义了varchar2（4000）的表字段，如果实际存储的内容超过4000字节或字符，多余的部分将自动去除而不会报出任何错误信息。

另外，经测试，即便表字段定义为varchar2（4000 char），在数据库字符集为NLS_CHARACTERSET=ZHS16GBK的情况下（一个汉字占2个字节），该字段最多可以存储2000个汉字而非4000个汉字。如果定义字段为varchar2（4000）或varchar2（4000byte）那么它最多可以存储多少个汉字就要视数据库字符集编码决定。如果当前数据库采用的是GBK（NLS_CHARACTERSET=ZHS16GBK）字符集，即一个汉字占2个字节，则最多可存2000个汉字；如果是UTF8字符集编码，即一个汉字占3个字节，则最多可存储4000/3=1333个汉字。具体情况可通过length（）函数求得占用字符数，通过lengthb（）或者vsize（）函数求得占用字节数。如下面的例子代码：

3.raw

raw数据类型也属于字符型，很少被使用，但笔者认为这个数据类型很不错，有它的优势且在Oracle数据字典里面有很多地方用到这个数据类型，如v$process的addr字段等。

raw用于存储二进制格式的数据，但是这样的存储有什么好处呢？主要是：在传输raw数据时或者将raw数据从一个数据库移到另一个数据库时，Oracle服务器不执行字符集转换，存储实际列值所需要的字节数完全依赖于值本身的大小，最多为2000字节。这样一来，在数据库效率上会提高，而且对数据由于字符集的不同而导致不一致的可能性就通过raw给排除了。

raw类似于char，声明方式raw（L），L为长度，以字节为单位，作为表列最大2000，作为PL/SQL变量，最大32767字节，示例代码如下：

2.1.2　数值型

Oracle数值型主要是number，但其不同的定义将导致输出结果不一样，希望读者重点关注这一问题。

1.number类型定义

number数值类型：变长度数字类型，可以用来存储0，正负定点或者浮点数。其语法格式如下：

number［（precision ［，scale］）］

其中precision指定精度，即最大的数字位数（小数点前和后的数字位数的总和），最大位数为38位；scale指定小数点右边的数字位数。

2.number类型定义数据举例

下面来看使用number类型定义数据的例子，首先创建一张名为testnumber的表，然后向这个新建的表中添加一些数据，最后输出这些数据。具体步骤如下。

（1）在SQL*Plus里创建表，表名为testnumbers，SQL语句如下：

（2）向表中加入数据，代码如下：

（3）输出数据，将下面的语句放在SQL Developer环境下运行：

3.number类型原始值与实际存储值对照

不同的数值类型定义对于同一个原始数据值，其实际存储在数据库中的数据值是不一样的，如表2-3所示。

续上表

2.1.3　日期时间型

日期时间型主要有3个：date、timestamp和interval。其中，timestamp及interval类型的日期数据相对date复杂一些，希望读者能花点儿时间按照书中的内容研究一下这两个数据类型。

1.date类型

date类型用于存储日期和时间信息，包括世纪、年、月、日、时、分、秒，最小精度为秒。其语法格式如下：

date

下面通过两个小示例来说明date日期型数据。

【示例2-1】使用SYSDATE函数获取当前服务器的时间。

为了使日期显示格式符合中国传统习惯，需将当前会话的日期显示格式调整为'年、月、日时、分、秒'，通过ALTER SESSION SET NLS_DATE_FORMAT='yyyy-mm-dd hh24：mi：ss'命令实现，命令中还涉及TO_CHAR（）转换函数，将日期或数值型数据转换为字符型数据，DUAL为Oracle伪表，它们的用法在后面有关章节介绍。语句如下：

【示例2-2】分别使用SYSDATE函数和SYSTIMESTAMP函数获取当前服务器的时间。

SQL>SELECT SYSDATE，SYSTIMESTAMP FROM dual；　　［00037］

运行结果如图2-1所示。

图2-1说明：SYSTIMESTAMP（）函数输出的当前日期时间比SYSDATE（）多出小数秒的部分“.189000”。

2.timestamp类型

timestamp日期类型是date类型的扩展，可以存储年、月、日、小时、分钟、秒，同时还可以存储秒的小数部分。timestamp日期类型包含时区，关于时区在此重点说明一下。

数据库默认时区，可以在CREATE DATABASE命令中设置，也可以使用ALTER DATABASE SET time_zone=…来修改。如果没有特别的指定，数据库默认将遵从主机操作系统时区设置。所有支持的时区记录在V$timezone_names动态性能表中（SELECT *FROM V$timezone_names；）。

时区有3种表示方法，全名、缩写和相对于标准时间（格林尼治时间）的固定偏移，如格林尼治标准时间相应的3种表示方法分别为：Etc/Greenwich、GMT和+00：00（格林尼治）。

关于数据库时区和会话时区，如果读者在大型的全球组织中工作，那么访问的数据库所在的时区就可能与本地时区不同。数据库的时区称为数据库时区（DATABASE TIME ZONE），数据库会话的时区设置称为会话时区（SESSION TIME ZONE）。

数据库时区由数据库参数TIME_ZONE控制，DBA可以在数据库的init.ora或spfile.ora文件中修改TIME_ZONE参数的设置，也可以使用ALTER DATABASE SET TIME_ZONE = offset | region（例如，ALTER DATABASE SET TIME_ZONE = '-8：00'或ALTER DATABASE SET TIME_ZONE = 'PST'）来修改TIME_ZONE参数的设置。DBTIMEZONE函数用于查看数据库时区。

例如，获得数据库的时区：SQL>SELECT DBTIMEZONE FROM DUAL；，可以看到输出结果为'+00：00'，这说明数据库使用操作系统设置的时区，在计算机上设置为PST（太平洋标准时间）。

会话时区是针对特定会话的时区。默认情况下，会话时区与操作系统时区相同。可以使用ALTER SESSION语句设置TIME_ZONE参数来修改会话时区（例如，ALTER SESSION SET TIME_ZONE = 'PST'，将本地时区设置为太平洋标准时间）。也可以将TIME_ZONE参数设置为LOCAL，将时区设置为运行ALTER SESSION语句的计算机的操作系统所用的时区。还可以将TIME_ZONE参数设置为DBTIMEZONE，这就将时区设置为数据库所用的时区。SESSIONTIMEZONE函数用于查看会话时区。

例如，得到当前会话的时区：SQL>SELECT SESSIONTIMEZONE FROM DUAL；，输出结果为'+08：00'，说明会话时区比UTC早8小时。

如何查看会话时区中的当前日期，SYSDATE函数得到数据库的日期设置，这个函数可以得到数据库时区中的日期。CURRENT_DATE函数用于查看会话时区中的日期。例如，SELECT CURRENT_DATE FROM DUAL；。

timestamp的3种语法格式为：

其中，fractional_seconds_precision为可选项，指定秒的小数部分的精度，取值范围为0～9，默认值为9。

下面，分别介绍timestamp3种语法的使用。

（1）timestamp［（fractional_seconds_precision）］

下面通过一个小示例从实践角度讲解timestamp［（fractional_seconds_precision）］语法的使用。

【示例2-3】timestamp日期类型在默认精度为9的情况下输出日期和时间。

SQL>SELECT TIMESTAMP '2017-07-0608：14：15.1231567' from dual；　［00038］

运行结果如图2-2所示。

图2-2所示为在小数秒默认精度为9的情况下输出的结果，假如改变默认精度，我们看下面这个语句序列，该语句序列为Oracle的匿名块，目的是定义一个小数秒精度为7的变量，如v_inteval timestamp（7）；，然后输出这个变量值，查看输出结果。将下面的语句放在SQL Developer环境下执行。

运行结果如图2-3所示。

图2-3说明：如果小数秒精度为7，则结果为06-7月-1708.16.15.1231567上午；如果精度为8，则结果为06-7月-1708.16.15.12315670上午；最大精度为9，是默认值。

（2）timestamp［（fractional_seconds_precision）］ with time zone

示例2-3是在没有指定时区的情况下执行的，即采用当前数据库默认时区。而timestamp with time zone数据类型是可以指定时区的timestamp。时区的偏移是指本地时间和格林尼治（UTC）时间之间的差异（小时和分钟）。因此，它不会存储数据库时区，但是有一个指示用来说明该时间所使用的时区。其语法为：

timestamp ［（fractional_seconds_precision）］ with time zone

如果两个timestamp with time zone，相对于UTC代表相同的时间，则认为这两个timestamp with time zone是相等的，而不管这2个timestamp with time zone所代表的具体时间。例如，timestamp '2017-07-068：00：00 -8：00'和timestamp '2017-07-0611：00：00 -5：00'是相等的，虽然它们分别表示当地的8点和11点，其中-8：00以及-5：00，表示相对UTC的偏移量。

timestamp如果没有使用时区元素，并且Oracle的ERROR_ON_OVERLAP_TIME SESSION参数设置为TRUE，则Oracle会返回错误。如果ERROR_ON_OVERLAP_TIME SESSION参数设置为FALSE，则Oracle认为该TIMESTAMP WITH TIME ZONE为标准时间。通过ALTER SESSION SET ERROR_ON_OVERLAP_TIME=FALSE；命令修改当前会话（SESSION）的ERROR_ON_OVERLAP_TIME参数值。

【示例2-4】timestamp日期类型在设置精度为7且加入时区的情况下输出日期和时间。

代码如下：

运行结果为：05-5月-1711.31.15.1231567下午+08：00，如图2-4所示。

说明：该结果多了一个时区偏移量+08：00，说明该时间为北京时间。

接下来我们再来看一下两个时区时间的比较，将下面的语句序列放在SQL Developer环境下运行。

运行结果如图2-5所示。

图2-5说明：两个时间是一致的，-8：00与-5：00时区差（8-5）3小时，即-5：00时区的11点相当于-8：00时区的8点，时差3小时。

（3）timestamp with local time zone

timestamp with local time zone数据类型也是可以指定时区的timestamp，和timestamp with time zone不同的是，它存储的是数据库的时区，时区偏移量并不存储，并且会根据查询用户端的时区进行相应的转换。当用户提交数据，Oracle返回的是用户所在的本地时区。时区的偏移是指本地时间和格林尼治（UTC）时间之间的差异（小时或分钟）。timestamp with local time zone主要用于C/S二层系统应用。其语法如下：

timestamp ［（fractional_seconds_precision）］ with local time zone

下面，我们通过一个较为完整的示例说明timestamp with local time zone的用法。

【示例2-5】本地时区（即数据库时区）的timestamp with local time zone的用法。

验证timestamp with time zone类型数据的时区是否被存储，判断的依据是该类型的数据输出结果是否包含时区元素（偏移量），若包含则说明被存储，否则未存储。对于timestamp with local time zone类型的数据，其时区元素是不被存储的。下面的验证过程分为几步实施。

（1）为了验证timestamp with time zone类型数据的时区是否被存储，先创建一张表，分别定义：date、timestamp with time zone和timestamp with local time zone类型的3个字段，依次对应的3个字段分别为：date_std、date_tz和date_ltz，在最后数据输出时，主要看date_tz字段值，这个字段值就是要验证其时区是否被存入数据库。

创建表的命令如下：

SQL>CREATE TABLE times（date_std date，date_tz timestamp with time zone，date_ltz timestamp with local time zone）；　　［00042］

（2）得到当前数据库的默认时区，代码如下：

SQL>select property_value from database_properties where property_name='DBTIMEZONE'；

或

SELECT DBTIMEZONE FROM DUAL；　　［00043］

运行结果如图2-6所示。

图2-6说明：当前数据库的时区为00：00，数据库使用操作系统设置的时区，计算机上设置为PST，即标准时间。

（3）修改当前会话时区为中国所在时区（Asia亚洲/shanghai上海+08：00），代码如下：

或

ALTER SESSION SET time_zone='-8：00'；　　［00044］

查询当前会话时区，代码如下：

SELECT SESSIONTIMEZONE FROM DUAL；

（4）修改当前会话时区默认日期格式，代码如下：

SQL>ALTER SESSION SET nls_timestamp_tz_format='YYYY-MM-DD HH24：MI：SS TZD：TZR'；

或

SQL>ALTER SESSION SET nls_timestamp_tz_format='YYYY-MM-DD HH24：MI：SS TZH'；　　［00045］

（5）修改当前会话日期格式（不含时区的日期），代码如下：

SQL>ALTER SESSION SET nls_timestamp_format='YYYY-MM-DD HH24：MI：SS'；

SQL>ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24：MI：SS'； ［00046］

（6）向表中加入一条数据：

（7）输出数据

注意：当前的时区为中国所在时区。

SQL>select * from times；　　［00048］

输出结果如图2-7所示。

（8）转换当前会话时区到数据库默认时区，代码如下：

SQL>ALTER SESSION SET time_zone=DBTIMEZONE；　　［00049］

查询当前会话时区，代码如下：

SELECT SESSIONTIMEZONE FROM dual；　　［00050］

输出结果如图2-8所示。

（9）输出表数据。

SQL>select * from times；　　［00051］

输出结果如图2-9所示。

图2-9说明：timestamp with time zone类型的date_tz字段，其数据包含了时区元素，说明时区被存入表中，而其他两个字段数据没有包含时区元素，因此，其时区并没有被存储。

最后得出的结论是：timestamp with time zone类型数据，其时区元素连同日期时间一并被存入数据库。而timestamp with local time zone类型数据，其时区不被存入数据库。另外，date类型是不含时区的。

2.1.4　关于日期时间型timestamp和data以及字符串数据类型之间的转换与处理

1.将字符型转成timestamp

在实际开发过程中，有时需要将字符型的日期格式数据转换成timestamp，需通过to_timestamp（）函数实现，命令如下：

SQL>SELECT TO_TIMESTAMP（'2017-07-0610.06.11.123456789上午'，'YYYY-MM-DD HH：MI：SS.FF AM'）FROM DUAL；　　［00052］

输出结果如图2-10所示。

2.将timestamp转成date型

在实际开发过程中，有时需要将timestamp类型数据转换成date类型，需通过cast（）函数实现，命令如下：

SQL>SELECT cast（to_timestamp（'2017-07-0610.06.11.123456789上午'，'yyyy-mm-dd hh：mi：ss.ff AM'）as date）timestamp_to_date from dual；　　［00053］

输出结果如图2-11所示。

3.date型转成timestamp

在实际开发过程中，有时需要将date类型数据转换成timestamp类型，同样也需要通过cast（）函数实现，命令如下：

SQL>select cast（SYSDATE as timestamp）date_to_timestamp from dual；［00054］

4.将一个时区的时间转换到另一个时区

此应用场景一般是跨时区的数据访问，如阿里巴巴，其应用范围遍布全球，像这样的应用，少不了用到跨时区数据的访问。时区转换需通过函数new_time（）实现。

下面的语句首先将日期格式的字符串'2017-07-0708：27：12'转换为日期型，通过函数TO_DATE（）实现，然后，通过new_time（）函数将这个日期从'PST'（太平洋标准时间）时区转换到'EST'（东部标准时间）时区。关于时区标识，请读者查阅相关资料。最后通过TO_CHAR（）函数再将这个新时区的时间转换回字符型，命令如下：

SQL>SELECT TO_CHAR（NEW_TIME（TO_DATE（'2017-07-0708：27：12'，'yyyy-mm-dd hh24：mi：ss'），'PST'，'EST'），'yyyy-mm-dd hh24：mi：ss'）FROM dual；　　［00055］

输出结果如图2-13所示。

5.获取timestamp格式的系统时间

timestamp格式的系统时间通过SYSTIMESTAMP函数实现，命令如下：

SQL>SELECT SYSTIMESTAMP FROM DUAL；　　［00056］

6.日期时间型相加减

两个date的日期相减得出的是天数，而两个timestamp的日期相减得出的是除天数外，还包括时分秒小数秒。

SQL>select（systimestamp - to_timestamp（'2017-07-0310.06.11.123456789上午'，'yyyy-mm-dd hh：mi：ss.ff AM'））as两timestamp日期相减from dual；　　［00057］

输出结果如图2-14所示。

SQL>select sysdate - to_date（'2017-07-03'，'yyyy-mm-dd'）from dual；　［00058］

运行结果如图2-15所示。

注：TO_CHAR函数支持date和timestamp，但是TRUNC则不支持timestamp数据类型。

7.让timestamp只支持秒的小数点后面6位

SQL>select to_char（systimestamp，'yyyymmdd hh24：mi：ss xff6'）FROM dual；　　［00059］

注：xff6（小数点后6位）也可以为xff7（小数点后7位）xff8（小数点后8位）xff9（小数点后9位），但只到6是有效的。

运行结果如图2-16所示。

8.获取系统时间

SQL>SELECT sysdate，systimestamp，to_char（systimestamp，'yyyymmdd hh24：mi：ss xff6'）FROM dual；　　［00060］

运行结果如图2-17所示。

2.1.5　interval时间间隔数据类型

所谓时间间隔就是指日期的间隔数量，如2年3个月、2天3小时、2小时3分钟、2分钟3秒等，这些数据就是时间间隔。

nterval类型主要包括两个：一个是interval year to month；另一个是interval day to second。

该数据类型同timestamp一样，不太容易理解，希望读者按照下面的内容在自己的电脑上反复试验，才能真正理解其含义。下面我们分别介绍这两个数据类型。

1.interval year to month数据类型

interval year to month类型用来存储由“年”到“月”构成的时间间隔，其语法格式如下：

interval 'integer ［- integer］' {year | month} ［（precision）］［to {year |month}］

该数据类型的时间差只精确到年和月，不含日（day）。precision为年或月的精确域，有效范围是0到9，默认值为2。

下面给出几个简单的小例子，说明其用法。

（1）与interval类型相关的函数

●numtodsinterval（n，'interval_unit'）：将n转换成interval_unit所指定的值，interval_unit可以为：DAY、HOUR、MINUTE、SEConD。注意，该函数不可以转换成year和month。

●numtoyminterval（n，'interval_unit'）：将n转换成interval_unitinterval_unit所指定的值，interval_unit可以为：YEAR、MONTH。

例如下面的示例：

（2）interval year to month用法

为了验证interval year to month的用法，需创建一张表，表名为'bb'，字段为a、b、c，数据类型依次为'date'、'date'、'interval year（9）to month'，然后，向bb表中插入一条数据，最后将a减b的结果转化为时间间隔并存入c字段。

第一步：创建表bb，命令如下：

SQL>CREATE TABLE bb（a date，b date，c interval year（9）to month）；

SQL>desc bb；　　［00062］

第二步：向bb表中插入数据，命令如下：

第三步：将a-b的结果转化为时间间隔后存入c，命令如下：

SQL>UPDATE bb set c = numtoyminterval（a-b，'month'）；

或

SQL>UPDATE bb set c = numtoyminterval（MONTHS_BETWEEN（a，b），'month'）；

COMMIT；　　［00064］

第四步：输出bb表内容，命令如下：

SQL>select a-b，c from bb；或SELECT MONTHS_BETWEEN（a，b），c FROM bb；［00065］

运行结果如图2-18所示。

图2-18说明：将a减b=186天变成了15年6个月（相当于186个月，函数numtoyminterval并不把186当成“天”来处理，而是当成“月”来处理，这一点请读者注意。最合理的做法是将a减b变成“月”后再加入numtoyminterval函数。将a减b变成月的函数是MONTHS_BETWEEN（a，b））。

2.interval day to second数据类型

interval day to second类型用来存储由“天”到“秒”构成的时间间隔，其语法格式如下：

其中：

●leading_precision值的范围为0～9，默认为2。

●time_expr的格式为HH［：MI［：SS［.n］］］ or MI［：SS［.n］］ or SS［.n］，n表示微秒。

●范围值是：HOUR： 0 to 23、MINUTE： 0 to 59、second： 0 to 59.999999999。

下面说明其用法。

（1）INTERVAL '15：12：10.222' DAY TO SECOND（3）表示1天5小时12分10.222秒。

SQL>select INTERVAL '15：12：10.222' DAY TO SECOND（3）from dual； ［00066］

运行结果如图2-19所示。

（2）INTERVAL '15：12' DAY TO MINUTE表示1天5小时12分。

SQL>select INTERVAL '15：12' DAY TO MINUTE from dual；　　［00067］

运行结果如图2-20所示。

（3）INTERVAL '1005' DAY（3）TO HOUR表示100天5小时，100为天，5为小时，day（3）为天的精度，默认值为2。

SQL>select INTERVAL '1005' DAY（3）TO HOUR from dual；　　［00068］

运行结果如图2-21所示。

（4）INTERVAL '100' DAY（3）表示100天。

SQL>select INTERVAL '100' DAY（3）from dual；　　［00069］

运行结果如图2-22所示。

（5）INTERVAL '11：12：10.2222222' HOUR TO SECOND（7）表示11小时12分10.2222222秒。

SQL>select INTERVAL '11：12：10.2222222' HOUR TO SECOND（7）from dual；［00070］

运行结果如图2-23所示。

（6）INTERVAL '11：20' HOUR TO MINUTE表示11小时20分。

SQL>select INTERVAL '11：20' HOUR TO MINUTE from dual；　　［00071］

运行结果如图2-24所示。

（7）INTERVAL '10' HOUR表示10小时。

SQL>select INTERVAL '10' HOUR from dual；　　［00072］

运行结果如图2-25所示。

（8）INTERVAL '10：22' MINUTE TO SECOND表示10分22秒。

SQL>select INTERVAL '10：22' MINUTE TO SECOND from dual；　　［00073］

运行结果如图2-26所示。

（9）INTERVAL '10' MINUTE表示10分。

SQL>select INTERVAL '10' MINUTE from dual；　　［00074］

运行结果如图2-27所示。

（10）INTERVAL '120' HOUR（3）表示120小时。

SQL>select INTERVAL '120' HOUR（3）from dual；　　［00075］

运行结果如图2-28所示。

（11）INTERVAL '30.15315' SECOND（2，1）表示30.2秒，因为该地方秒的后面精度设置为1，要进行四舍五入。

SQL>select INTERVAL '30.12315' SECOND（2，1）from dual；　　［00076］

运行结果如图2-29所示。

（12）INTERVAL '20' DAY减去INTERVAL '210' HOUR等于interval '1106：00：00'day to second，表示20天-210小时=10天0秒。

SQL>select INTERVAL '20' DAY - INTERVAL '210' HOUR from dual；　　［00077］

运行结果如图2-30所示。

SQL>select INTERVAL '1106：00：00' DAY TO SECOND from dual；　　［00078］

运行结果如图2-31所示。

（13）interval day to second类型存储两个timestamp之间的时间差异，用日期、小时、分钟、秒.小数秒形式表示。

语法格式为：

day hh：mi：ss.xxxxxxxxx

或

hh：mi：ss.xxxxxxxxx

或

mi：ss.xxxxxxxxx

或

ss.xxxxxxxxx

其中xxxxxxxxx为小数秒。

第一步：创建表，代码如下：

SQL>CREATE TABLE tt1（a date，b date，c interval year（9）to month，d interval day（9）to second）；　　［00079］

第二步：插入数据，代码如下：

第三步：更新数据，代码如下：

第四步：输出数据。

select months_between（a，b）"a、b间隔月数"，c "年-月"，（a-b）*24*3600 "a、b间隔秒数"，d "天、小时、分钟、秒.小数秒" from tt1；　　［00082］

运行结果如图2-32所示。

（14）系统当前日期加上1年零2个月。

SQL>SELECT SYSDATE +interval '1-2' year to month FROM DUAL；　　［00083］

（15）系统当前日期减去2011年1月。

SQL>SELECT（SYSDATE - TO_DATE（'2011-01-01'））year to month FROM DUAL；　　［00084］

（16）系统当前日期减去2011年1月1日。

SQL>SELECT（SYSDATE - TO_DATE（'2011-01-01'））day（9）to second FROM DUAL；　　［00085］

（17）系统当前日期加上12年。

SQL>SELECT SYSDATE +interval '12' year（3）FROM DUAL；　　［00086］

（18）系统当前日期加上3个月。

SQL>SELECT SYSDATE +interval '3' month（2）FROM DUAL；　　［00087］

（19）时间间隔具体表示方法及比较。

2.1.6　ROWID伪列数据类型

关于ROWID伪列数据类型说明如下。

（1）ROWID是一种特殊类型的列，又称ROWID伪列。ROWID伪列可以像正常列一样使用SQL的SELECT语句访问。Oracle数据库每一行都有一个ROWID伪列。ROWID表示特定行的特定地址。ROWID伪列可以用ROWID数据类型来定义。

（2）ROWID与磁盘驱动器中的特定位置相关。因此，ROWID是获取某个行最快速的方法。然而，一个行的ROWID会随着数据库的卸载和重新加载而发生变化。基于这一点考虑，不推荐在多个事务代码中使用ROWID伪列的值。

（3）Oracle的ROWID用来唯一标识表中的一条记录，是这条数据在数据库中存放的物理地址。

（4）Oracle的ROWID分为两种：物理ROWID和逻辑ROWID。索引组织表使用逻辑ROWID，其他类型的表使用物理ROWID。物理ROWID在Oracle 11g版本中又得到进一步扩展。下面描述物理扩展ROWID，由于约束ROWID仅仅是为了兼容早期版本，因此不做说明。

（5）用户可以定义ROWID类型的列或者变量，但是Oracle并不确保存在这些列或者变量中的数值就是有效的ROWID。

（6）ROWID就是表记录存在于文件系统中的物理位置，索引结构中包含ROWID，因此通过索引能快速定位表中的记录。

（7）数据库不允许通过SQL语句来改变标准的ROWID伪列的值。

关于ROWID值的物理扩展，一共有18位，每位采用64位编码，分别用A～Z、a～z、0～9、+、/共64个字符表示。A表示0，B表示1，……Z表示25，a表示26，……z表示51，0表示52，……9表示61，+表示62，/表示63。

我们通过下面的具体例子来看一下，代码如下：

运行结果如图2-33所示。

2.1.7　blob、clob、nclob及bfile数据类型

关于Oracle的blob、clob、nclob、bfile数据类型，说明如下：

●blob和clob都是大字段类型，blob全称为二进制大型对象（Binary Large Object）。它用于存储数据库中的大型二进制对象。可存储的最大大小为4GB字节。而clob全称为字符大对象（Character Large Object）。它与LONG数据类型类似，clob用于存储数据库中的大型单字节字符数据块，可以直接存储文字，不支持宽度不等的字符集。可存储的最大大小为4GB字节。为了更好地管理Oracle数据库，通常像图片、文件、音乐等信息就用blob字段来存储，先将文件转为二进制再存储进去。而像文章或者是较长的文字，就用clob存储，这样对以后的查询更新存储等操作都提供很大的方便；

●nclob：基于国家语言字符集的nclob数据类型，用于存储数据库中的固定宽度单字节或多字节字符的大型数据块，不支持宽度不等的字符集。可存储的最大大小为4GB字节；

●bfile：当大型二进制对象的大小大于4GB字节时，bfile数据类型用于将其存储在数据库外的操作系统文件中；当其大小不足4GB字节时，则将其存储在数据库内部的操作系统文件中，bfile列存储文件定位程序，此定位程序指向服务器上的大型二进制文件。

2.1.8　不同数据类型之间的转换约定

Oracle数据库的数据类型在没有人工干预的情况下是可以自动转换的（不是所有数据库都这样），下面说明自动转换的约定及依据原则。

1.Oracle数据库可以自动进行如下的类型转换

●字符类型（char、Nchar、varchar2、Nvarchar2）与数值类型（number）之间的相互转换。

●字符类型与日期时间类型（date）之间的相互转换。

●字符类型与ROWID类型之间的相互转换。

●字符类型与clob和nclob类型之间的相互转换。

2.在转换时所依据的基本原则

（1）进行算术运算时，Oracle数据库会自动将字符型转换为数值型或日期时间型。

（2）使用连接操作符（||）时，Oracle数据库会把非字符类型的数据转换为字符类型。

（3）对于查询语句，当查询条件中进行比较的是字符型和数值型的数据时，如果数据列是字符型，则Oracle数据库会自动将数据列的数据转换为数值型的数据。如果数据列是数值型，则Oracle数据库会自动将条件值的数据转换为字符型的数据。当进行比较的是字符型和日期时间型的数据时，Oracle数据库会自动将字符型的数据转换为日期时间型的数据。

在本节，主要讲解了Oracle数据库数据类型的“字符型”“数值型”“日期时间型”“关于日期时间型timestamp和data以及字符串数据类型之间的转换与处理”“interval时间间隔数据类型”“ROWID伪列数据类型”“blob、clob、nclob及bfile数据类型”及“不同数据类型之间的转换约定”等。本节属于Oracle数据库的基础，要求务必掌握。接下来，讲解Oracle数据库DML语句的使用与操作。