oracle data types,Oracle内置数据类型(Built-in Data Types)

1

VARCHAR2(size[BYTE|CHAR])

Variable-length character string having maximum lengthsizebytes or characters.Maximumsizeis 4000 bytes or characters, and minimum is 1 byte or 1 character. You must specifysizeforVARCHAR2.

BYTEindicates that the column will have byte length semantics.CHARindicates that the column will have character semantics.

VARCHAR2(4000BYTE)可以存4000个字母，2000个汉字

VARCHAR2(4000 CHAR)可以存4000个字母，4000个汉字

1

NVARCHAR2(size)

Variable-length Unicode character string having maximum lengthsizecharacters. The number of bytes can be up to two timessizeforAL16UTF16encoding and three timessizeforUTF8encoding. Maximum size is 2000 bytes. You must specifysizeforNVARCHAR2.

2

NUMBER[ (p[,s]) ]

Number having precisionpand scales.The precisionpcan range from 1 to 38. The scalescan range from -84 to 127. Both precision and scale are in decimal digits. ANUMBERvalue requires from 1 to 22 bytes.

2

FLOAT[(p)]

A subtype of theNUMBERdata type having precisionp. AFLOATvalue is represented internally asNUMBER.The precisionpcan range from 1 to 126 binary digits. AFLOATvalue requires from 1 to 22 bytes.To convert from binary to decimal precision, multiplynby0.30103. To convert from decimal to binary precision, multiply the decimal precision by3.32193.

8

LONG

Character data of variable length up to 2 gigabytes, or 231-1 bytes. Provided for backward compatibility.

12

DATE

Valid date range from January 1, 4712 BC, to December 31, 9999 AD. The default format is determined explicitly by theNLS_DATE_FORMATparameter or implicitly by theNLS_TERRITORYparameter. The size is fixed at 7 bytes.This data type contains the datetime fieldsYEAR,MONTH,DAY,HOUR,MINUTE, andSECOND.It does not have fractional seconds or a time zone.

21

BINARY_FLOAT

32-bit floating point number. This data type requires 4 bytes.

22

BINARY_DOUBLE

64-bit floating point number. This data type requires 8 bytes.

180

TIMESTAMP[(fractional_seconds_precision)]

Year, month, and day values of date, as well as hour, minute, and second values of time, wherefractional_seconds_precisionis the number of digits in the fractional part of theSECONDdatetime field.Accepted values offractional_seconds_precisionare 0 to 9. The default is 6.The default format is determined explicitly by theNLS_TIMESTAMP_FORMATparameter or implicitly by theNLS_TERRITORYparameter. The size is 7 or 11 bytes, depending on the precision. This data type contains the datetime fieldsYEAR,MONTH,DAY,HOUR,MINUTE, andSECOND. It contains fractional seconds but does not have a time zone.

181

TIMESTAMP[(fractional_seconds)]WITHTIMEZONE

All values ofTIMESTAMPas well as time zone displacement value, wherefractional_seconds_precisionis the number of digits in the fractional part of theSECONDdatetime field. Accepted values are 0 to 9. The default is 6. The default format is determined explicitly by theNLS_TIMESTAMP_FORMATparameter or implicitly by theNLS_TERRITORYparameter. The size is fixed at 13 bytes. This data type contains the datetime fieldsYEAR,MONTH,DAY,HOUR,MINUTE,SECOND,TIMEZONE_HOUR, andTIMEZONE_MINUTE. It has fractional seconds and an explicit time zone.

231

TIMESTAMP[(fractional_seconds)]WITHLOCALTIMEZONE

All values ofTIMESTAMPWITHTIMEZONE, with the following exceptions:

Data is normalized to the database time zone when it is stored in the database.

When the data is retrieved, users see the data in the session time zone.

The default format is determined explicitly by theNLS_TIMESTAMP_FORMATparameter or implicitly by theNLS_TERRITORYparameter. The size is 7 or 11 bytes, depending on the precision.

182

INTERVALYEAR[(year_precision)]TOMONTH

Stores a period of time in years and months, whereyear_precisionis the number of digits in theYEARdatetime field. Accepted values are 0 to 9. The default is 2. The size is fixed at 5 bytes.

183

INTERVALDAY[(day_precision)]TOSECOND[(fractional_seconds)]

Stores a period of time in days, hours, minutes, and seconds, where

day_precisionis the maximum number of digits in theDAYdatetime field. Accepted values are 0 to 9. The default is 2.

fractional_seconds_precisionis the number of digits in the fractional part of theSECONDfield. Accepted values are 0 to 9. The default is 6.

The size is fixed at 11 bytes.

23

RAW(size)

Raw binary data of lengthsizebytes. Maximumsizeis 2000 bytes. You must specifysizefor aRAWvalue.

24

LONG RAW

Raw binary data of variable length up to 2 gigabytes.

69

ROWID

Base 64 string representing the unique address of a row in its table. This data type is primarily for values returned by theROWIDpseudocolumn.

208

UROWID[(size)]

Base 64 string representing the logical address of a row of an index-organized table. The optionalsizeis the size of a column of typeUROWID. The maximum size and default is 4000 bytes.

96

CHAR[(size[BYTE|CHAR])]

Fixed-length character data of lengthsizebytes or characters.Maximumsizeis 2000 bytes. Default and minimumsizeis 1 byte.

BYTEandCHARhave the same semantics as forVARCHAR2.

CHAR(2000BYTE)可以存2000个字母，1000个汉字

CHAR(2000CHAR)可以存2000个字母，2000个汉字

96

NCHAR[(size)]

Fixed-length character data of lengthsizecharacters. The number of bytes can be up to two timessizeforAL16UTF16encoding and three timessizeforUTF8encoding. Maximum size is 1000 bytes. Default and minimumsizeis 1 character.

112

CLOB

A character large object containing single-byte or multibyte characters. Both fixed-width and variable-width character sets are supported, both using the database character set. Maximum size is (4 gigabytes - 1) * (database block size).

112

NCLOB

A character large object containing Unicode characters. Both fixed-width and variable-width character sets are supported, both using the database national character set. Maximum size is (4 gigabytes - 1) * (database block size). Stores national character set data.

113

BLOB

A binary large object. Maximum size is (4 gigabytes - 1) * (database block size).

114

BFILE

Contains a locator to a large binary file stored outside the database.Enables byte stream I/O access to external LOBs residing on the database server. Maximum size is 4 gigabytes.The locator maintains the directory name and the filename.