Oracle数据类型

ORACLE基本数据类型（亦叫内置数据类型built-indatatypes)可以按类型分为：字符串类型、数字类型、日期类型、LOB类型、LONGRAW&RAW类型、ROWID&UROWID类型。在讲叙字符串类型前，先要讲一下编码。字符串类型的数据可依编码方式分成数据库字符集（CHAR/VARCHAR2/CLOB/LONG)和国际字符集(NCHAR/NVARCHAR2/NCLOB)两种。数据库中的字符串数据都通过字符集将字符转换为数字后(二进制)，才存储到数据块中。通过不同的编码集转换，即便是相同的字符，也可能会转换成不同的二进制编码。这也是产生乱码的原因。数据库的编码格式一般是在创建数据库时指定的。当然也可以修改数据库的编码。查看数据库视图所包含的数据类型:SELECT*FROMDBA_TYPESWHEREOWNERISNULL.具体细节情况参见Oracle®DatabaseSQLLanguageQuickReference10/11g或官方文档一字符串类型字符串数据类型还可以依据存储空间分为固定长度类型（CHAR/NCHAR)和可变长度类型（VARCHAR2/NVARCHAR2)两种.所谓固定长度：是指虽然输入的字段值小于该字段的限制长度，但是实际存储数据时，会先自动向右补足空格后，才将字段值的内容存储到数据块中。这种方式虽然比较浪费空间，但是存储效率较可变长度类型要好。同时还能减少数据行迁移情况发生。所谓可变长度：是指当输入的字段值小于该字段的限制长度时，直接将字段值的内容存储到数据块中，而不会补上空白，这样可以节省数据块空间。1.1CHAR类型CHAR(size[BYTE|CHAR])CHAR类型，定长字符串，会用空格填充来达到其最大长度。非NULL的CHAR（12）总是包含12字节信息。CHAR字段最多可以存储2,000字节的信息。如果创建表时，不指定CHAR长度，则默认为1。另外你可以指定它存储字节或字符，例如CHAR(12BYTYE)CHAR(12CHAR).一般来说默认是存储字节，你可以查看数据库参数NLS_LENGTH_SEMANTICS的值。SQLCode1.SQLshowparameternls_length_semantics;2.3.NAMETYPEVALUE4.----------------------------------------------5.6.nls_length_semanticsstringBYTE7.8.eg:9.10.CREATETABLETEST11.12.(13.14.NAME_OLDCHAR(10),15.16.NAME_NEWCHAR(10CHAR)17.18.)19.20.INSERTINTOTEST21.22.(NAME_OLD,NAME_NEW)23.24.SELECT'ABCDEFGHIJ','你清除字节与字符'FROMDUAL;25.26.COMMIT;27.28.INSERTINTOTEST29.30.(NAME_OLD,NAME_NEW)31.32.SELECT'你清除字节与字符','ABCDEFGHIJ'FROMDUAL;33.34.ORA-12899:列SYS.TEST.NAME_OLD的值太大(实际值:24,最大值:10)注意：数据库的NLS_CHARACTERSET为AL32UTF8，即一个汉字占用三到四个字节。如果NLS_CHARACTERSET为ZHS16GBK，则一个字符占用两个字节。如果串的长度小于或等于250（0x01~0xFA），Oracle会使用1个字节来表示长度。对于所有长度超过250的串，都会在一个标志字节0xFE后跟有两个字节来表示长度。因此，如果有一个包含“HelloWorld”的VARCHAR2(80)，则在块中可能如图12.-1所示1.2NCHAR类型这是一个包含UNICODE格式数据的定长字符串。NCHAR字段最多可以存储2,000字节的信息。它的最大长度取决于国家字符集。另外查询时，如果字段是NCHAR类型，则需要如下书写SELECTtranslated_descriptionFROMproduct_descriptionsWHEREtranslated_name=N'LCDMonitor11/PM';1.3VARCHAR类型不要使用VARCHAR数据类型。使用VARCHAR2数据类型。虽然VARCHAR数据类型目前是VARCHAR2的同义词，VARCHAR数据类型将计划被重新定义为一个单独的数据类型用于可变长度的字符串相比，具有不同的比较语义。1.4VARCHAR2类型变长字符串，与CHAR类型不同，它不会使用空格填充至最大长度。VARCHAR2最多可以存储4,000字节的信息。1.5：NVARCHAR2类型这是一个包含UNICODE格式数据的变长字符串。NVARCHAR2最多可以存储4,000字节的信息。二.数字类型2.1NUMBER类型NUMBER(P,S)是最常见的数字类型，可以存放数据范围为10^130~10^126（不包含此值)，需要1~22字节(BYTE)不等的存储空间。P是Precison的英文缩写，即精度缩写，表示有效数字的位数，最多不能超过38个有效数字S是Scale的英文缩写，可以使用的范围为-84~127。Scale为正数时，表示从小数点到最低有效数字的位数，它为负数时，表示从最大有效数字到小数点的位数下面是官方文档的示例ActualDataSpecifiedAsStoredAs123.89NUMBER123.89123.89NUMBER(3)124123.89NUMBER(6,2)123.89123.89NUMBER(6,1)123.9123.89NUMBER(3)124123.89NUMBER(4,2)exceedsprecision123.89NUMBER(6,-2)100.01234NUMBER(4,5).01234.00012NUMBER(4,5).00012.000127NUMBER(4,5).00013.0000012NUMBER(2,7).0000012.00000123NUMBER(2,7).00000121.2e-4NUMBER(2,5)0.000121.2e-5NUMBER(2,5)0.000012.2INTEGER类型INTEGER是NUMBER的子类型，它等同于NUMBER（38,0），用来存储整数。若插入、更新的数值有小数，则会被四舍五入。例如：CREATETABLETEST(IDINTEGER)查看表TEST的DDL定义如下所示CREATETABLESYS.TEST(IDNUMBER(*,0))PCTFREE10PCTUSED40INITRANS1MAXTRANS255NOCOMPRESSLOGGINGSTORAGE(INITIAL65536NEXT1048576MINEXTENTS1MAXEXTENTS2147483645PCTINCREASE0FREELISTS1FREELISTGROUPS1BUFFER_POOLDEFAULTFLASH_CACHEDEFAULTCELL_FLASH_CACHEDEFAULT)TABLESPACESYSTEM;INSERTINTOTESTSELECT12.34FROMDUAL;INSERTINTOTESTSELECT12.56FROMDUAL;SQLSELECT*FROMTEST;ID----------12132.3浮点数浮点数可以有一个十进制数点任何地方从第一个到最后一个数字，或者可以在所有有没有小数点。指数可能（可选）用于以下数量增加的范围（例如，1.777e-20)。刻度值不适用于浮点数字，因为可以显示在小数点后的位数的数量不受限制。二进制浮点数不同数量的值由Oracle数据库内部存储的方式。使用小数精度数存储值。完全相同号码存储范围和数量由支持的精度内的所有文本。正是因为使用小数精度（数字0到9）表示文本存储文本。使用二进制精度（数字0和1）存储二进制浮点数。这种存储方案不能代表所有确切地使用小数精度的值。频繁地，将值从十进制转换为二进制的精度时出现的错误时撤消值回从二进制转换为十进制精度。在字面0.1是一个这样的例子。Oracle数据库提供了专为浮点数的两种数值数据类型：BINARY_FLOATBINARY_FLOAT是32位、单精度浮点数字数据类型。可以支持至少6位精度,每个BINARY_FLOAT的值需要5个字节，包括长度字节。BINARY_DOUBLEBINARY_DOUBLE是为64位，双精度浮点数字数据类型。每个BINARY_DOUBLE的值需要9个字节，包括长度字节。在数字的列中，浮点数有小数精度。在BINARY_FLOAT或BINARY_DOUBLE的列中，浮点数有二进制的精度。二进制浮点数支持的特殊值无穷大和NaN（不是数字）。您可以指定列在表2-4范围内的浮点数。数字文本中定义了用于指定浮点数的格式。Table2-3FloatingPointNumberLimitsValueBinary-FloatBinary-DoubleMaximumpositivefinitevalue3.40282E+38F1.79769313486231E+308Minimumpositivefinitevalue1.17549E-38F2.22507485850720E-308FLOAT类型FLOAT类型也是NUMBER的子类型。Float(n),数n指示位的精度，可以存储的值的数目。N值的范围可以从1到126。若要从二进制转换为十进制的精度，请将n乘以0.30103。要从十进制转换为二进制的精度，请用3.32193乘小数精度。126位二进制精度的最大值是大约相当于38位小数精度。三.日期类型日期类型用于存储日期数据，但是并不是使用一般的格式（2012-08-08）直接存储到数据库的。3.1DATE类型DATE是最常用的数据类型，日期数据类型存储日期和时间信息。虽然可以用字符或数字类型表示日期和时间信息，但是日期数据类型具有特殊关联的属性。为每个日期值，Oracle存储以下信息：世纪、年、月、日期、小时、分钟和秒。一般占用7个字节的存储空间。3.2TIMESTAMP类型这是一个7字节或12字节的定宽日期/时间数据类型。它与DATE数据类型不同，因为TIMESTAMP可以包含小数秒，带小数秒的TIMESTAMP在小数点右边最多可以保留9位3.3TIMESTAMPWITHTIMEZONE类型这是TIMESTAMP类型的变种，它包含了时区偏移量的值3.4TIMESTAMPWITHLOCALTIMEZONE类型3.5INTERVALYEARTOMOTH3.6INTERVALDAYTOSECOND四.LOB类型内置的LOB数据类型包括BLOB、CLOB、NCLOB、BFILE（外部存储）的大型化和非结构化数据，如文本、图像、视屏、空间数据存储。BLOB、CLOB、NCLOB类型4.1CLOB数据类型它存储单字节和多字节字符数据。支持固定宽度和可变宽度的字符集。CLOB对象可以存储最多(4gigabytes-1)*(databaseblocksize)大小的字符4.2NCLOB数据类型它存储UNICODE类型的数据，支持固定宽度和可变宽度的字符集，NCLOB对象可以存储最多(4gigabytes-1)*(databaseblocksize)大小的文本数据。4.3BLOB数据类型它存储非结构化的二进制数据大对象，它可以被认为是没有字符集语义的比特流，一般是图像、声音、视频等文件。BLOB对象最多存储(4gigabytes-1)*(databaseblocksize)的二进制数据。4.4BFILE数据类型二进制文件，存储在数据库外的系统文件，只读的，数据库会将该文件当二进制文件处理五.RAW&LONGRAW类型5.1LONG类型它存储变长字符串，最多达2G的字符数据（2GB是指2千兆字节，而不是2千兆字符），与V