Oracle数据类型资料

oracle数据类型

以下的大多数类型的描述都经本人验证，但不免有错误，请不吝赐教

(oracle:10.2.0.3.0,plsql:7.1.5.1398)

注：以下两个函数有助于理解数据类型

length()当前列存储值的字符长度：

vsize()当前列存储值所占用字节数。

1.CHAR

固定长度字符域，最大长度可达2000个字符或者字节。默认指定为以字符形式进行存储,

并且当位数不够时oracle在其右边添加空格来补满。

例如：

2.VARCHAR2

可变长度字符域，最大长度可幺4000个字符。

例如：

CREATETABLEtest{namevarchar2(20))

2.1.CHAR和VARCHAR2区别

1.CHAR的长度是固定的，而VARCHAR2的长度是可以变化的，比如，存储字符串，

对于CHAR(20),表示你存储的字符将占20个字节(包括17个空字符)，而同样的VARCHAR2

(20)则只占用3个字节的长度，20只是最大值，当你存储的字符小于20时，按实际长度存

储。

2.CHAR的效率比VARCHAR2的效率稍高。

3.目前VARCHAR是VARCHAR2的同义词。工业标准的VARCHAR类型可以存储空字

符串，但是Oracle不这样做，尽管它保留以后这样做的权利。Oracle自己开辟了一个数据

类型VARCHAR2,这个类型不是一个标准的VARCHAR,它将在数据库中varchar列可以

存储空字符串的特件改为存储NULL如假如你想有向后兼容的能力.Oracle建议使用

VARCHAR2而不是VARCHAR。

何时该用CHAR,何时该用varchar2?

CHAR与VARCHAR2是一对矛盾的统一•体，两者是互补的关系.VARCHAR2比CHAR

节省空间，在效率上比CHAR会稍微差一些，即要想获得效率，就必须牺牲一定的空间，

这也就是我们在数据库设计上常说的‘以空间换效率‘。VARCHAR2虽然比CHAR节

省空间，但是假如一个VARCHAR2列时常被修改，而且每次被修改的数据的长度不同，这

会引起‘行迁移'(RowMigration)现象，而这造成多余的I/O,是数据库设计和调整中要竭力

避免的，在这种情况下用CHAR代替VARCHAR2会更好一些。

3.NCHAR

多字节字符集的固定长度字符域，长度随字符集而定，最多为1000个字符(即2000个字节)。

不管是中文还是英文每一个字符都占2个字节。

NCHAH的列在位数不够时需要在右边填允空格。

例如：

CREATETABLEtest(nameNchar(20))

4.NVARCHAR2

多字节字符集的可变长度字符域，长度随字符集而定，最多为2000个字符(即4000个字节)。

5.LONG

LONG类型的列存储可变长度的字符串，最多可以存储2GB的数据。LONG数据类型的

使用是为了向前兼容的需要。建议使用CLOB数据类型来代替LONG类型。

例如：

CREATETABLEtest(namelong)

注：long类型主要用丁不需要作字符申搜索的长申数据，如果要进行字符搜索就要用

varchar2类型。不少工具，包恬sqPplus,处理long数据类型都是很烟难的。long数据类

型的使用中，要受限于磁盘的大小。一个表中只能包含一个long类型的列，此外还有不少

限制，如不能做索引、不能用于分布查询、不能做聚簇等等。不能用于分布查询

6.NUMBER

可变长度数值列，允许值为。、正数和负数。NUMBER值通常以4个字节或者更少的字节

存储，最多21字节。

Number的数据声明如下：

Number(p,s)：声明一个定点数，p(precision)为精度，它表示存储在列中数字的总长度：

s(scale)表示小数点右边的数字个数，p范围1-38.sc范围-84到127,s为负数时，Oracle

就对小数点左边的s个数字进行舍入。

Number(p)：声明一个整数，相当于Number(p,0)

Number：声明一个浮点数，其精度为38,要注意的是s的值没有应用，也就是说s的指不

能简单的理解为0,或者其他的数。

7.DATE

用于存储全部日期的固定长度(7个字行)字符域，时间作为F1期的一部份存储其中。除非

通过设置init.ora文件的NLS_DATE_FORMAT参数来取代日期格式，否则查询时，H期以

DD-MON-丫丫格式表示，如13-APR-99表示1999.4.13

存储时间的精度可以达到1/100S。不提供时区的相关信息。

例如:

CREATETABLEtest(nameDATE)

8.TIMESTAMP

使用年、月、日、小时、分钟、秒域来对日期/时间提供更详细的支持。最多可以使用9位

数字的精度来存储秒(受底层操作系统支持的限制)。这个数据类型没有时区的相关信息，

它主要用于高精度时间存储，它可以在Oracle9i数据库及其更新的版本中使用。

例如:

CREATETABLEtest(timestampcolumnTIMESTAMP);

Date转换成timestamp：

selectsysdate,cast(sysdateastimestamp),systimestampfromdual

另tto_char支持date和timestamp.trunc不支持timestamp.

获取系统时间的timestamp类型用systimestamp0

9.TIMESTAMPWITHTIMEZONE

包含TIMESTAMP数据类型中的所有域，除此之外，还包含两个额外的域：timezone_hour

和timezone_minuteo这个数据类型包含支持时区的相关信息，在数据库中占13字节。

这个数据类型可以在Oracle9i数据库及其更新的版本中使用。

例如：

CREATETABLEtest(timestampcolumntimestampwithtimezone);

10.TIMESTAMPwithlocaltimezone

除了在数据库中存储的时区采用标准化以外，所包含的其他域与TIMESTAMP数据类型中

的域相同。当选择列时，将日期/时间标准为会话的时区。该数据类型在TIMESTAMPWITH

TIMEZONE的基础上又有进一步.在用户提交时间给数据库时，该类型会转换成数据的时

区来保存数据，即在数据库中保存的时间是数据库本地的时区.当别的地方用户访问数据

库时在显示该类型数据时Oracle会将该时区转换成客户端的归间来显示.在数据库中保存

占7到11字节，具体看你定义的fractionalseconds的精度.

该数据类型可以在Oracle)数据库及其更新的版本中使用。

例如

CREATETABLEtest(timestamp_columntimestampwithlocaltimezone);

11.INTERVALYEARTOMONTH

用于存储一个时间段，山月份和年组成。需要5字节来存储。

这个数据类型可以在Oracle9i数据库及其更新的版本中使用。

例如：

SELECTINTERVAL'01-05'YEARTOMONTH-INTERVAL'01-02*YEARTOMONTH

FROMdual;

12.INTERVALDAYTOSECOND

用于存储一个时间段，由日和秒组成。需要11字节存储

该数据类型可以在Oracle。数据库及其更新的版本中使用。

例如：

SELECTINTERVAL'10010:20:42.22*DAY(3)TOSECOND(2)-INTERVAL'101

10:20:42.22'DAY(3)TOSECOND(2)FROMdual;

13.RAW

RAW表示二进制数据的可变长度字符域，最长为2000个字节，建议使用BLOB来代替它。

例如：

CREATETABLEtest(rawcolumnRAW(2000));

14.LONGRAW

LONGRAW表示二进制数据的可变长度字符域，最长为2GB。建议使用BLOB来代替它。

例如：

CREATETABLEtest

(raw_columnLONGRAW);

15.CLOB

字符大对象，最大长度为4GBo

例如：

CREATETABLEtest(clob_columnCLOB);

16.NCLOB

多字节字符集的CLOB数据类型，最大长度为4GB.

例如：

CREATETABLEtest(nclob_columnNCLOB);

17.BLOB

二进制大对象，最大长度为4GB.：,

例如：

CREATETABLEtest(blob_columnBLOB);

18.BFILE

外部二进制文件，大小由操作系统决定。存储指向数据库外部文件的定位符。

例如：

CREATETABLEtest(bfile_columnBFILE);

Oracle10引入两种新的数据类型：Binary_float和Binary_double»BinaryJIoat：

这两种数据类型是对现有的number数据类型的补充。

•需要存储空间小，BinaryJIoat和Binary_double数据类型分别需要5个字节和9个字节

的空间，number需要22字节。

•可以表示的数字范围更大，BinaryJIoat和Binary_double数据类型比number类型所能

存储更大或者更小的数字。

•执行运算速度快，Binary_float和Binary_double数据类型通常在硬件中执行，而number

需要使用软件转换。

・运算封闭，对Binaryfloat和Binarydouble数据类型的数学运算是封闭的，就是说返回

结果要末是数字要末是一个特殊值。

•取整透明，Binary_float和Binary_double数据类型使用二进制来表示数字，而number

则使用卜进制表示。

19.BINARY_FLOAT

存储一个单精度32位浮点数，该数据类型是一个基于ANSUEEE745标准的浮点数据类

型。需要5字节的存储空间。

例如：

CREATETABLEtest(bjloatBINARY_FLOAT);

20.BINARY_DOUBLE

存储一个双精度64位的浮点数，该数据类型是一个基于ANSIJEEE745标准的双精度浮点

数据类型，。需要9字节的存储空间。

例如：

CREATETABLEtest(b_floatBINARY_DOUBLE);

以下数据类型是专用数据类型:

21.ROWID

表示RowID的二进制数据，Oracle8RowlD的数值为10个字节，在Oracle7中使用的限定

RowID格式为6个字节。

例如:

CREATETABLEtest(rowid_columnROWID);

22.UROWID

UROWID用于数据寻址的二进制数据，最大长度为4000个字节。

例如：

CREATETABLEtest(urowid_columnUROWID);

关于各种数据类型的注释

1、关于CHAR和VARCHAR2数据类型的注释

由数据库字符集来确定特定的CHAR、VARCHAR或者VARCHAR2字符类型所占的字

节数。多字节字符集中的字符可以存储1~4B。CHAR或者VARCHAR2数据类型的大小

由该数据类型可以存储的字节数或者字符数来决定(这成为字符的语义)。所有定义的存

储大小都是默认以字节为单位的。如果使用多字节字符集(大部份常见的西方字符集都是

以单字节为单位的，而值得注意的是，有一个例外就是UTF字符集不是以单字节为单位

的)，则可

能需要定义以字符为单位的存储，下面的例子说明了这个问题：

CREATETABLEtest(nameVARCHAR2(20char));

建议在表的末尾处创建一些值为NULL的列。通过使用这种方怯，可以用VARCHAR2数

据类型来存储一些数据，但不能使用CHAR数据类型来存储数据，因为Oracle数据库是

以在一行中连续存储多个NULL值的方式来进行存储的。

Oracle9i数据库及其更新的版本提供数据压缩功能，该功能仅在包含于只读表空间中的表

中使用(也就是说，表中的数据将不会改变)。仅当通过下面的批量装载(bulk-load)操

作中的任意一种操作将数据装载进表中时，才会执行数据压缩操作：

使用createtableasselect(CTAS)操作来创建表。

在直接模式下的insert操作或者并行的insert操作.

SQL*Loader装载程序在直接模式下的操作.

注意：

如果使用update语句对数据进行了修改，则已经压缩过的数据块将不会再次压缩！因此，

一张很小的表可以快速地增长成一张很大的表。通过使用altertablemove命令可以在已

经存在的表中压缩数据。下面是创建压缩表的例子和对已经存在的表进行压缩的例子：

CREATETABLEmy_tab(idNUMBER,currenLvalueVARCHAR2(2000))COMPRESS;

ALTERTABLEmy_comp_tabMOVECOMPRESS;

2、大于NUMBER数据类型的注释

NUMBER数据类型内部使用科学计数法以可变长度格式来存储数据。使用1B存储指数,

而此外20B(这个字节数可以变化)用于存储该数字剩卜的部份。这种存储模式使得

NUMBER数据类型可以表示的精度为38位。

如果想要确定给定的数字所占字节数的大小，可以使用vsize函数，如下所示：

SQL>SELECTVSIZE(100)FROMdual;

在上面的例子中，该vsize函数用于将数字的大小指定为100.使用2B存储。1B存储数

字，另1B存储指数。也可以使用dump函数来确定任意列的具体大小，如下所示：

SQL>SELECTid,dump(id)didFROMtest;

IDDID

123Typ=2Len=3:194,2,24

141Typ=2Len=3:194,2,42

123456Typ=2Len=4:195,13,35,57

NUMBER数据类型可以有若干种形式来定义。可以根据需要使用或者禁用精度和范围的方

式来定义NUMBER数据类型.如果使用特定的精度来定义NUMBER歹人则当超i寸所

定义的精度时，Oracle数据库将会产生错误。例如，NUMBER(6,2)在存储数字1234.56

时，不会对该数字的值产生任何影响，而在存储数字123.456时将会对该数字进行四舍五

入，最终存储的数字将变成123.46：如果以相同的NUMBER数据类型来存储数字

12345.67时，数据库将产生错误，因为该数字的精度是7而不是6。

最后需要注意的是，也可以将列定义为没有精度的NUMBER数据类型，这表明将把

NUMBER数据类型的列作为没有范围属性的整数来看待。

3、关于LONGRAW数据类型的注释

LONGRAW数据类型用于存诸数据库无法解释的二进制数据。这个数据类型最多能够存储

2GB的数据并且它的存储容量是可变的。Oracle数据库不鼓励使用LONGRAW数据类

型，因为该数据类型已经由BLOB数据类型所代替了。Oracle数据库(Oracle9i数据库

及其更新的版本)已经提供了通过altertable命令将LONGRAW数据类型列转换到相应

的CLOB数据类型的能力。也可以使用to_lob函数将LONGRAW数据格式转换为

BLOB数据格式。

4、关于LONG数据类型的在群

LONG数据类型用于存储大星的字符文本。LONG数据类型会受到某些存储的限制，最多

可以存储2GB的数据。Oracle数据库不鼓励使用LONG数据类型，因为这个数据类型

已经由CLOB数据类型所代替了。Oracle数据库(Oracle9i数据库及其更新的版本)已

经提供了通过altertable命令将LONG数据类型列转换到相应的LOB数据类型的能

力。也可以使用tojob函数将LONG数据格式转换成CLOB数据格式。

在下面的SQL语质类型中可以引用LONG数据类型列：

*select列表

*在update语句的set子句中

*在insert语句的values子句中使用LONG数据类型将会受到许多的限制：

•在每一个表中只允许使用一个LONG数据类型的列。

*不能使用LONG数据类型的属性创建对象类型。

*Oracle数据库的where子句或者完整性约束不能引用LONG数据类型，仅有一种例外

的情况，就是LONG数据类型可以浮现在NULL和NOTNULL约束中，或者可以作为

NULL或者NOTNULL的where子句谓语的一部份。

*不能索引LONG数据类型的列。

*不能对包含LONG数据类型的列进行分布操作。所有包含LONG数据类型列的事务

必须在同一个数据库中协同工作。

*复制不支持LONG数据类型。

*如果所创建的表有LONG和CLOB两种数据类型的列，则在同一SQL语句中，同时

绑定到LONG和CLOB数据类型列上的数据不能超过4000Bo但是可

以将超过4000B的数据单独绑定到LONG数据类型或者CLOB数据类型列上。

除「前面所讲的约束外，当LONG数据类型浮现在SQL语句中时，还有另一些关于

LONG数据类型列的约束。首先需要注意的是，下面的操作不支持LONG数据类型列：

groupby

orderby

connectby

distinct

unique

.任何SQL的内建函数、表达式或者条件

•任何select语句（在该语句中，union,intersect或者minus操作将查询或者子查询结

合起来）

也会存在关于LONG数据类型的DDL约束，如下所示：

.在createcluster语句的表列中不能包含LONG数据类型列。

*在altertable...move语句中不能包含LONG数据类型列。

•在createtableasselect语句的选择列表中不能包含LONG数据类型列。

在PL/SQL程序单元和触发器中使用LONG数据类型列时，同样也存在一些约束，如下

所示：

*不能使用LONG数据类型表示触发器中的变量。

*触发器变量：new和:old的数据类型不能是LONG数据类型列。

*PL/SQL存储函数不能返回LONG数据类型，但是PL/SQL程序中的变量或者参数可以

使用LONG数据类型，无车不能从SQL语句中调用这种PL/SQL程序单元。

5、关于DATE数据类型的注释

DATE数据类型是Oracle数据库中自带的一种用于存储日期和时间的方法。当DATE数

据类型存储在数据库中的时候，这个数据类型占领7B的内部存储空间。这些字节分别用

来存储世纪、年、月、日、小时、分和秒的详细信息。默认的显示格式为dd-momyy,它表

示U、月以及两位数的年，由破折号（•）将其分离。例如默认格式为：01-FEB-01o如果

要重新定义日期格式，可以在数据库参数文件中设置nls_datejormat变量。如果要改变

日期的格式，也可以为特定的会话使用altersession命令设置nls_date_format变量的

值。如下所示：

SQL>SELECTsysdateFROMdual;

SYSDATE

24-MAY-03

SQL>ALTERSESSIONSETnlsdateformat='mm/dd/yyyyhh24:mi:ss';

Sessionaltered.

SQL>SELECTsysdateFROMdual;

SYSDATE

06/25/202216:49:09

在上面的例子中，将日期格式的时间标志改变为24小时制格式的时间，并且在年的前面

加之了世纪值。

如果想要在特定系统的所有会话中使用不同的日期格式；，可以设置NLS_LANG操作系统

的环境变量，并且同时将NL3_DATE_F0RMAT作为操作系统环境变量进行设置。这将使

得每一次登录系统时，会发出altersession命令。需要注意的是，惟独当同时设置了环境

变量NLS_LANG时，环境变量NLS_DATE_FORMATE才会生效。

注意：

如果使用RMAN(OracleRecoveryManager,Oracle恢复管理)，则显示的摆布日期格

式都将是默认的日期格式。应该在启动RMAN之前设置NLS_LANG和

NLS_DATE_FORMAT环境变量以设定所需要的日期格式。

在SQL语句中为了改变输出的格式，需要使用Oracle数据库的内建函数to_char<如果

有基于字符的日期要插入到DATE数据类型列中，则需要使用to_date函数。关于to_c