DataType（Oracle数据类型官方说明）

引自Oracle® Database SQL Reference 10g Release 2(10.2)的第2章Basic Elements of Oracle SQL的Datatypes小节：

 

Table2-1 Built-in Datatype Summary

Code	Datatype	Description
1	VARCHAR2(size [BYTE | CHAR])	Variable-length character string having maximum length size bytes or characters. Maximum size is 4000 bytes or characters, and minimum is 1 byte or 1 character. You must specify size for VARCHAR2. BYTE indicates that the column will have byte length semantics; CHAR indicates that the column will have character semantics.
1	NVARCHAR2(size)	Variable-length Unicode character string having maximum length size characters. The number of bytes can be up to two times size for AL16UTF16 encoding and three times size for UTF8 encoding. Maximum size is determined by the national character set definition, with an upper limit of 4000 bytes. You must specify sizefor NVARCHAR2.
2	NUMBER[(precision [, scale]])	Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127.
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 the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The size is fixed at 7 bytes. This datatype contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It does not have fractional seconds or a time zone.
21	BINARY_FLOAT	32-bit floating point number. This datatype requires 5 bytes, including the length byte.
22	BINARY_DOUBLE	64-bit floating point number. This datatype requires 9 bytes, including the length byte.
180	TIMESTAMP [(fractional_seconds)]	Year, month, and day values of date, as well as hour, minute, and second values of time, wherefractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values of fractional_seconds_precision are 0 to 9. The default is 6. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The sizes varies from 7 to 11 bytes, depending on the precision. This datatype contains the datetime fields YEAR,MONTH, DAY, HOUR, MINUTE, and SECOND. It contains fractional seconds but does not have a time zone.
181	TIMESTAMP [(fractional_seconds)] WITHTIME ZONE	All values of TIMESTAMP as well as time zone displacement value, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values are 0 to 9. The default is 6. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by theNLS_TERRITORY parameter. The size is fixed at 13 bytes. This datatype contains the datetime fields YEAR,MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, and TIMEZONE_MINUTE. It has fractional seconds and an explicit time zone.
231	TIMESTAMP [(fractional_seconds)] WITHLOCAL TIME ZONE	All values of TIMESTAMP WITH TIME ZONE, 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 the NLS_DATE_FORMAT parameter or implicitly by theNLS_TERRITORY parameter. The sizes varies from 7 to 11 bytes, depending on the precision.
182	INTERVAL YEAR [(year_precision)] TO MONTH	Stores a period of time in years and months, where year_precision is the number of digits in the YEARdatetime field. Accepted values are 0 to 9. The default is 2. The size is fixed at 5 bytes.
183	INTERVAL DAY [(day_precision)]  TO SECOND[(fractional_seconds)]	Stores a period of time in days, hours, minutes, and seconds, where day_precision is the maximum number of digits in the DAY datetime field. Accepted values are 0 to 9. The default is 2. fractional_seconds_precision is the number of digits in the fractional part of the SECOND field. Accepted values are 0 to 9. The default is 6. The size is fixed at 11 bytes.
23	RAW(size)	Raw binary data of length size bytes. Maximum size is 2000 bytes. You must specify size for a RAW value.
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 datatype is primarily for values returned by the ROWID pseudocolumn.
208	UROWID [(size)]	Base 64 string representing the logical address of a row of an index-organized table. The optional size is the size of a column of type UROWID. The maximum size and default is 4000 bytes.
96	CHAR [(size [BYTE | CHAR])]	Fixed-length character data of length size bytes. Maximum size is 2000 bytes or characters. Default and minimum size is 1 byte. BYTE and CHAR have the same semantics as for VARCHAR2.
96	NCHAR[(size)]	Fixed-length character data of length size characters. The number of bytes can be up to two times size forAL16UTF16 encoding and three times size for UTF8 encoding. Maximum size is determined by the national character set definition, with an upper limit of 2000 bytes. Default and minimum size is 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.

 

 

 

关于number数据类型的说明：

NUMBER Datatype

The NUMBER datatype stores zero as well as positive and negative fixednumbers with absolute values from 1.0 x 10^-130 to (but not including) 1.0 x 10¹²⁶.If you specify an arithmetic expression whose value has an absolute valuegreater than or equal to 1.0 x 10¹²⁶, then Oracle returns an error.Each NUMBER value requires from 1 to 22 bytes.

Specify a fixed-pointnumber using the following form:

NUMBER(p,s)

where:

·        p is the precision,or the total number of significant decimal digits, where the most significantdigit is the left-most nonzero digit, and the least significant digit is theright-most known digit. Oracle guarantees the portability of numbers withprecision of up to 20 base-100 digits, which is equivalent to 39 or 40 decimaldigits depending on the position of the decimal point.

·        s is the scale,or the number of digits from the decimal point to the least significant digit.The scale can range from -84 to 127.

o    Positivescale is the number of significant digits to the right of the decimal point toand including the least significant digit.

o    Negativescale is the number of significant digits to the left of the decimal point, tobut not including the least significant digit. For negative scale the leastsignificant digit is on the left side of the decimal point, because the actualdata is rounded to the specified number of places to the left of the decimalpoint. For example, a specification of (10,-2) means to round to hundreds.

Scale can begreater than precision, most commonly when e notation is used. When scale is greater than precision, theprecision specifies the maximum number of significant digits to the right ofthe decimal point. For example, a column defined as NUMBER(4,5) requires a zero for the first digit after the decimal point androunds all values past the fifth digit after the decimal point.

It is goodpractice to specify the scale and precision of a fixed-point number column forextra integrity checking on input. Specifying scale and precision does notforce all values to a fixed length. If a value exceeds the precision, then Oraclereturns an error. If a value exceeds the scale, then Oracle rounds it.

Specify an integerusing the following form:

NUMBER(p)

This represents afixed-point number with precision p and scale 0 and is equivalent to NUMBER(p,0).

Specify afloating-point number using the following form:

NUMBER 

The absence of precisionand scale designators specifies the maximum range and precision for an Oraclenumber.

See Also:

"Floating-Point Numbers"

Table 2-2 showhow Oracle stores data using different precisions and scales.

Table 2-2 Storage of Scale and Precision

Actual Data	Specified As	Stored As
123.89	NUMBER	123.89
123.89	NUMBER(3)	124(exceeds scale)
123.89	NUMBER(3,2)	exceeds precision
123.89	NUMBER(4,2)	exceeds precision
123.89	NUMBER(5,2)	123.89
123.89	NUMBER(6,1)	123.9(exceeds scale)
123.89	NUMBER(6,-2)	100(exceeds scale)
.01234	NUMBER(4,5)	.01234
.00012	NUMBER(4,5)	.00012
.000127	NUMBER(4,5)	.00013(exceeds scale)
.0000012	NUMBER(2,7)	.0000012
.00000123	NUMBER(2,7)	.0000012(exceeds scale)
1.2e-4	NUMBER(2,5)	0.00012
1.2e-5	NUMBER(2,5)	0.00001(exceeds scale)

 

 

 

 

关于varchar2的说明：

VARCHAR2 Datatype

The VARCHAR2 datatype specifies a variable-length character string. When youcreate a VARCHAR2 column, you supply the maximum number of bytes or characters ofdata that it can hold. Oracle subsequently stores each value in the columnexactly as you specify it, provided the value does not exceed the column'smaximum length of the column. If you try to insert a value that exceeds thespecified length, then Oracle returns an error.

You must specify amaximum length for a VARCHAR2 column. This maximum must be at least 1 byte, although theactual string stored is permitted to be a zero-length string (''). You canuse the CHAR qualifier, for example VARCHAR2(10 CHAR), to givethe maximum length in characters instead of bytes. A character is technically acode point of the database character set. CHAR and BYTE qualifiers override the setting of the NLS_LENGTH_SEMANTICS parameter, which has a default of bytes. For performancereasons, Oracle recommends that you use the NLS_LENGTH_SEMANTICS parameter to set length semantics and that you use the BYTE and CHAR qualifiers only when necessary to override the parameter. Themaximum length of VARCHAR2 data is 4000 bytes(无论设置为‘BYTE’或‘CHAR’，最大长度都是4000). Oracle compares VARCHAR2 values using nonpadded(非填充) comparisonsemantics.

To ensure proper dataconversion between databases with different character sets, you must ensurethat VARCHAR2 data consists of well-formed strings. SeeOracle Database GlobalizationSupport Guide formore information on character set support.

See Also:

"Datatype Comparison Rules" for information on comparisonsemantics