总结了一些Oracle数据类型集解

作者:课课家教育更新于： 2019-05-21 11:33:36

　　在Oracle数据库中，每个关系表都由许多列组成。给每一列指派特定的数据类型来定义将在这个列中存储得数据类型。

　　1、CHAR

　　最多可以以固定长度的格式存储2000个字符或字节。默认指定为以字符形式进行存储，这个数据类型是固定长度的，并且当位数不够时，需要在其右边添加空格来补满。

　　例如：

　　CREATETABLEtest

　　(namechar(20))

　　2、VARCHAR和VARCHAR2

　　最多可以以可变长度来存储4000B，因此不需要空格来作补充。VARCHAR2比VARCHAR更适合使用，由于兼容性的原因，所以仍然在Oracle数据库中保留着VARCHAR。

　　例如：

　　CREATETABLEtest

　　(namevarchar2(20))

　　3、NCHAR

　　NLS（nationallanguagesupport，国际语言支持）的数据类型仅可以存储由数据库NLS字符集定义的Unicode字符集。该数据类型最多可以存储2000B。NCHAR的列在位数不够时需要在右边填充空格。例如：

　　CREATETABLEtest

　　(nameNchar(20))

　　注意：在Oracle9i数据库及其更新的版本中，仅使用Unicode数据类型

　　4、NVARCHAR2

　　NLS的数据类型与VARCHAR2数据类型等价。这个数据类型最多可存储4000B。例如：

　　CREATETABLEtest

　　(nameNvarchar2(20))

　　注意：在Oracle9i数据库及其更新的版本中，仅使用Unicode数据类型

　　5、NUMBER

　　用于存储零、正数、定长负数以及浮点数。NUMBER数据类型可以以NUMBER(P,S)的

　　形式来定义数字的精度和范围。这里：p表示精度（1-38），它表示存储在列中数

　　字的总长度是p位：s表示范围，它表示小数点后的位数。该取值范围可以从-84到127。例如：

　　CREATETABLEtest

　　(namenumber(5))

　　使用精度5来定义一个正数（如12345）。

　　CREATETABLEtest

　　(namenumber(5,2))

　　使用精度5和范围2来定义一个数字。符合这种数据类型的数字值如123.45和12.34

　　6、LONG

　　LONG类型的列存储可变长度的字符串，最多可以存储2GB的数据。LONG类型的列有很多在VARCHAR2类型列中所具有的特征。可以使用LONG类型的列来存储LONG类型的文本字符串。LONG数据类型的使用是为了向前兼容的需要。建议使用LOB数据类型来代替LONG类型。例如：

　　CREATETABLEtest

　　(namelong)

　　7、DATE

　　用于在数据库中存储日期和时间。存储时间的精度可以达到1/100s。不提供时区的相关信息。例如：

　　CREATETABLEtest

　　(nameDATE)

　　8、TIMESTAMP

　　使用年、月、日、小时、分钟、秒域来对日期/时间提供更详细的支持。最多可以使用9位数字的精度来存储秒（受底层操作系统支持的限制）。这个数据类型没有时区的相关信息，它可以在Oracle9i数据库及其更新的版本中使用。例如：

　　CREATETABLEtest

　　(timestamp_columnTIMESTAMP);

　　9、TIMESTAMPWITHTIMEZONE

　　包含TIMESTAMP数据类型中的所有域，除此之外，还包含两个额外的域：timezone_hour和timezone_minute。这个数据类型包含支持时区的相关信息。这个数据类型可以在Oracle9i数据库及其更新的版本中使用。例如：

　　CREATETABLEtest

　　(timestamp_column

　　TIMESTAMPWITH

　　TIMEZONE);

　　10、TIMESTAMPWITHLOCALTIMEZONE

　　除了在数据库中存储的时区采用标准化以外，所包含的其他域与TIMESTAMP数据类型中的域相同。当选择列时，将日期/时间标准为会话的时区。这个数据类型可以在Oracle9i数据库及其更新的版本中使用。例如

　　CREATETABLEtest

　　(timestamp_column

　　TIMESTAMPWITH

　　LOCALTIMEZONE);

　　11、INTERVALYEARTOMONTH

　　用于存储一个时间段，由月份和年组成。需要5B来存储。这个数据类型可以在Oracle9i数据库及其更新的版本中使用。例如：

　　SELECTINTERVAL

　　'01-05'

　　YEARTOMONTH-

　　INTERVAL'01-02'YEAR

　　TOMONTHFROMdual;

　　12、INTERVALDAYTOSECOND

　　用于存储一个时间段，由日和秒组成。需要11B来存储。这个数据类型可以在Oracle9i数据库及其更新的版本中使用。例如：

　　SELECTINTERVAL

　　'10010:20:42.22'

　　DAY(3)TOSECOND(2)-

　　INTERVAL

　　'10110:20:42.22'

　　DAY(3)TOSECOND(2)

　　FROMdual;

　　13、RAW

　　用于存储raw类型的二进制数据。最多可以存储2000B。建议使用BLOB来代替它。例如：

　　CREATETABLEtest

　　(raw_column

　　RAW(2000));

　　14、LONGRAW

　　用于存储raw类型的二进制数据。最多可以存储2GB的数据。建议使用BLOB来代替它。例如：

　　CREATETABLEtest

　　(raw_columnLONGRAW);

　　15、ROWID

　　表中ROWID类型的字符串表示。使用这个数据类型来存储由ROWID类型伪列的返回值。例如：

　　CREATETABLEtest

　　(rowid_columnROWID);

　　16、UROWID

　　在索引组织表中表示逻辑行地址。例如：

　　CREATETABLEtest

　　(urowid_columnUROWID);

　　17、CLOB

　　用于存储基于字符的大对象。在Oracle9i数据库中最多可以存储4GB的数据，这比Oracle10g数据库中可存储的最大数据还要大，这也是现在数据库规定块大小的一个因素（~4GB*数据块大小）。例如：

　　CREATETABLEtest

　　(clob_columnCLOB);

　　18、NCLOB

　　可以使用由数据库国际字符集所定义的字符集来存储仅为Unicode类型的基于字符的数据。最多可以存储4GB的数据，这比Oracle10g数据库中可存储的最大数据还要大，这也是现在数据库规定块大小的一个因素（~4GB*数据块大小）。例如：

　　CREATETABLEtest

　　(nclob_columnNCLOB);

　　19、BLOB

　　最多可以存储4GB数据的二进制大对象，这比Oracle10g数据库中可存储的最大数据还要大，这也是现在数据库规定块大小的一个因素（~4GB*数据库块大小）。例如：

　　CREATETABLEtest

　　(blob_columnBLOB);

　　20、BFILE

　　存储指向数据库外部文件的定位符。外部文件最大为4GB。例如：

　　CREATETABLEtest

　　(bfile_columnBFILE);

　　21、BINARY_FLOAT

　　该数据类型是一个基于ANSI_IEEE745标准的浮点数据类型。它定义了一个32位的双精度浮点数。这个数据类型需要5B的存储空间。例如：

　　CREATETABLEtest

　　(b_floatBINARY_FLOAT);

　　22、BINARY_DOUBLE

　　该数据类型是一个基于ANSI_IEEE745标准的双精度浮点数据类型，它定义了一个32位的双精度浮点数。这个数据类型需要9B节的存储空间。例如：

　　CREATETABLEtest

　　(b_floatBINARY_DOUBLE);

　　关于各种数据类型的注释

　　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,current_valueVARCHAR2(2000))COMPRESS;

　　ALTERTABLEmy_comp_tabMOVECOMPRESS;

　　关于CHAR和VARCHAR2数据类型，常常会有这样一个问题：到底是用哪一个数据类型最好？下面是对这个问题的一些指导性建议：

　　通常VARCHAR2数据类型比CHAR数据类型优先使用。

　　如果数据大小是变化的则在数据库中使用VARCHAR2数据类型可以节省空间。

　　如果VARCHAR2数据类型列中的数据需要频繁更新，则VARCHAR2数据类型列的扩展可能会导致行连接或者行迁移的发生。当最终需要使用VARCHAR2数据类型列总大小的时候，可以考虑使用CHAR数据类型来代替VARCHAR2数据类型。

　　注意：

　　通常来说，使用VARCHAR2(1)数据类型比使用CHAR(1)数据类型开销要大，但上面所讲的则是例外。

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

　　NUMBER数据类型内部使用科学计数法以可变长度格式来存储数据。使用1B存储指数，而另外20B（这个字节数可以变化）用于存储该数字剩下的部分。这种存储模式使得NUMBER数据类型可以表示的精度为38位。

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

　　SQL>SELECTVSIZE(100)FROMdual;

　　VSIZE(100)

　　----------

　　2

　　在上面的例子中，该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列，则当超过所定义的精度时，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数据类型的能力。也可以使用to_lob函数将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数据类型列上的数据不能超过4000B。但是可以将超过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-mon-yy,它表示日、月以及两位数的年，由破折号（-）将其分离。例如默认格式为：01-FEB-01。如果要重新定义日期格式，可以在数据库参数文件中设置nls_date_format变量。如果要改变日期的格式，也可以为特定的会话使

　　用altersession命令设置nls_date_format变量的值。如下所示：

　　SQL>SELECTsysdateFROMdual;

　　SYSDATE

　　---------

　　24-MAY-03

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

　　Sessionaltered.

　　SQL>SELECTsysdateFROMdual;

　　SYSDATE

　　--------------------

　　06/25/200916:49:09

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

　　如果想要在特定系统的所有会话中使用不同的日期格式，可以设置NLS_LANG操作系统的环境变量，并且同时将NLS_DATE_FORMAT作为操作系统环境变量进行设置。这将使得每一次登录系统时，会发出altersession命令。需要注意的是，只有当同时设置了环境变量NLS_LANG时，环境变量NLS_DATE_FORMATE才会生效。

　　注意：

　　如果使用RMAN（OracleRecoveryManager，Oracle恢复管理），则显示的左右日期格式都将是默认的日期格式。应该在启动RMAN之前设置NLS_LANG和NLS_DATE_FORMAT环境变量以设定所需要的日期格式。

　　在SQL语句中为了改变输出的格式，需要使用Oracle数据库的内建函数to_char。如果有基于字符的日期要插入到DATE数据类型列中，则需要使用to_date函数。关于to_char函数转换日期格式的例子如下所示：

　　SQL>SELECTto_char(sysdate,'mm/dd/yyyyhh24:mi:ss')the_dateFROMdual;

　　THE_DATE

　　-----------------

　　06/25/200917:05:36

　　在上面的例子中，将日期格式转化为由四位数表示的年以及24小时制、分钟、秒表示的时间格式。

　　6、关于TIMESTAMP和INTERVAL数据类型的注释

　　某些新的TIMESTAMP数据类型的值依赖于数据库中适当的时区设置。数据库的时区默认为当前操作系统的时区。在数据库创建的时候，可以通过在createdatabase命令中使用settime_zone参数为数据库设置不同的时区。也可以通过使用alterdatabasesettime_zone命令改变已经存在的数据库时区。使用altersession命令可以在会话级上修改时区设置。可以定义基于UTC(UniversalTimeCoordinated，协调世界时)小时偏移的时区，或者使用一个指定的时区，如CST或EST。下面是设置数据库时区的例子：

　　ALTERDATABASESETtime_zone='CST';

　　ALTERDATABASESETtime_zone='-05:00';

　　一些转换函数可以用于TIMESTAMP和INTERVAL数据类型。这些函数包括to_data、to_char、to_timestamp_tz、to_yminterval和to_dsinterval，同时nls_timestamp_format参数和nls_timestamp_tz_format参数也与TIMESTAMP和INTERVAL数据类型有一些特定的关联。

　　当从TIMESTAMP数据类型列中获取数据时，可以使用其他的内建函数。extract函数可以从TIMESTAMP数据类型列中获取特定的信息，如小时或者分钟的信息。如下所示（注意，从sysdate函数到TIMESTAMP数据类型格式的隐式转换）：

　　CREATETABLEmy_tab(test_colTIMESTAMP);

　　INSERTINTOmy_tabVALUES(sysdate);

　　SELECTtest_col,EXTRACT(HOURFROMtest_col)FROMmy_tab;

　　TEST_COLHOUR

　　-------------------------------------

　　25-JUNE-0905.18.50.000000PM9