Oracle Built-in Data Types(Oracle内置数据类型)

Oracle Built-in(嵌入的，内置) Data Types

The table that follows(跟随,遵照) summarizes(总结,概述) Oracle built-in data types. Refer(参考,查阅) to the syntax(语法,句法)in the preceding(在...之前,前) sections(截面,节段,部分) for the syntactic(句法的,语法结构) elements(元素,成份,原理). The codes(编码,代码) listed(列出) for the data types are used internally(内部的) by Oracle Database. The data type code of a column or object attribute is returned by the<wbr><code style="font-family:monospace; font-size:12px">DUMP(倾倒)</code><wbr>function.</wbr></wbr>

Table 3-1 Built-in Data Type Summary(概要,简要)

Code	Data Type	Description
1	VARCHAR2(size<wbr>[<code style="font-family:monospace; font-size:12px">BYTE(字节)</code><wbr>|<wbr><code style="font-family:monospace; font-size:12px">CHAR(把数值,字符)</code>])</wbr></wbr></wbr>	Variable-length(变长,可变长度) character(字符,特性) string having(所有,具有) maximum length<wbr><code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">size</span></code><wbr>bytes or characters. Maximum<wbr><code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">size</span></code><wbr>is 4000 bytes or characters, and minimum is 1 byte or 1 character. You must specify(指定,列举)<wbr><code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">size</span></code><wbr>for<wbr><code style="font-family:monospace; font-size:12px">VARCHAR2</code>.</wbr></wbr></wbr></wbr></wbr></wbr></wbr> BYTE<wbr>indicates(指示,表明) that the column will have(将有) byte length semantics(语义).<wbr><code style="font-family:monospace; font-size:12px">CHAR</code><wbr>indicates that the column will have character semantics.</wbr></wbr></wbr>
1	NVARCHAR2(size)	Variable-length Unicode character string having maximum length<wbr><code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">size</span></code><wbr>characters. The number of bytes can be up to two times<wbr><code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">size(可达两倍大小)<wbr></wbr></span></code>for(为)<wbr><code style="font-family:monospace; font-size:12px">AL16UTF16</code><wbr>encoding and three times<wbr><code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">size</span></code><wbr>for<wbr><code style="font-family:monospace; font-size:12px">UTF8</code><wbr>encoding(3次UTF8编码的大小). Maximum<wbr><code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">size</span></code><wbr>is determined(决定了) by the national character set(集合) definition(定义,解说), with(支持) an upper(上面的,较高的) limit(限制) of 4000 bytes. You must specify<wbr><code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">size</span></code><wbr>for<wbr><code style="font-family:monospace; font-size:12px">NVARCHAR2</code>.</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
2	NUMBER<wbr>[ (<code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">p</span></code><wbr>[,<wbr><code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">s</span></code>]) ]</wbr></wbr></wbr>	Number having precision(精度,精确的)<wbr><code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">p</span></code><wbr>and scale(刻度)<wbr><code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">s</span></code>. The precision<wbr><code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">p</span></code><wbr>can range(范围) from 1 to 38. The scale<wbr><code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">s</span></code><wbr>can range from -84 to 127. Both(两者) precision and scale are in decimal digits(小数位数). A<wbr><code style="font-family:monospace; font-size:12px">NUMBER</code><wbr>value requires(需要) from 1 to 22 bytes.</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
2	FLOAT<wbr>[(<code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">p</span></code>)]</wbr>	A subtype(图表类型) of the<wbr><code style="font-family:monospace; font-size:12px">NUMBER</code><wbr>data type having precision<wbr><code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">p</span></code>. A<wbr><code style="font-family:monospace; font-size:12px">FLOAT</code><wbr>value is represented(代表,表现) internally(内部) as<wbr><code style="font-family:monospace; font-size:12px">NUMBER</code>. The precision<wbr><code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">p</span></code><wbr>can range from 1 to 126 binary digits(二进制数字). A<wbr><code style="font-family:monospace; font-size:12px">FLOAT</code><wbr>value requires(需要) from 1 to 22 bytes.</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
8	LONG	Character data of variable length up to 2 gigabytes(十亿字节), or 231<wbr>-1 bytes. Provided(假如,提供) for backward(向后的) compatibility(兼容性).</wbr>
12	DATE	Valid(有效的,正确的) date range from January(1月) 1, 4712 BC(公元前4712), to December(12月) 31, 9999 AD. The default format is determined(决定) explicitly(明确的) by(由) theNLS_DATE_FORMAT<wbr>parameter or implicitly(含蓄的,暗中的) by the<wbr><code style="font-family:monospace; font-size:12px">NLS_TERRITORY</code><wbr>parameter. The size is fixed(固定的) at 7 bytes. This data type contains(包含) the datetime fields(域)<wbr><code style="font-family:monospace; font-size:12px">YEAR</code>,<wbr><code style="font-family:monospace; font-size:12px">MONTH</code>,<wbr><code style="font-family:monospace; font-size:12px">DAY</code>,<wbr><code style="font-family:monospace; font-size:12px">HOUR</code>,<wbr><code style="font-family:monospace; font-size:12px">MINUTE</code>, and<wbr><code style="font-family:monospace; font-size:12px">SECOND</code>. It does not have(没有)<wbr>fractional(小数的) seconds or a time zone(时区).</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
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<wbr>[(<code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">fractional_seconds_precision</span></code>)]</wbr>	Year, month, and day values of date, as well as hour, minute, and second values of time, where<wbr><code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">fractional_seconds_precision</span></code><wbr>is the number of digits(数字) in the fractional(小数) part(部分) of the<wbr><code style="font-family:monospace; font-size:12px">SECOND</code><wbr>datetime(日期时间字段域)<wbr>field. Accepted(接受) values of<wbr><span style="word-wrap:normal; word-break:normal; line-height:18px; font-size:12px"><em>ractional_seconds_precision</em></span><code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">f</span></code><wbr>are 0 to 9. The default is 6. The default format is determined(决定,确定) explicitly(明确的) by the<wbr><code style="font-family:monospace; font-size:12px">NLS_TIMESTAMP_FORMAT</code><wbr>parameter or implicitly by the<wbr><code style="font-family:monospace; font-size:12px">NLS_TERRITORY</code>parameter. The size is 7 or 11 bytes, depending on(取决于,根据) the precision(不同的精度). This data type contains the datetime fields<wbr><code style="font-family:monospace; font-size:12px">YEAR</code>,<wbr><code style="font-family:monospace; font-size:12px">MONTH</code>,<wbr><code style="font-family:monospace; font-size:12px">DAY</code>,<wbr><code style="font-family:monospace; font-size:12px">HOUR</code>,<code style="font-family:monospace; font-size:12px">MINUTE</code>, and<wbr><code style="font-family:monospace; font-size:12px">SECOND</code>. It contains fractional seconds(分数秒) but does not have a time zone.</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
181	TIMESTAMP<wbr>[(<code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">fractional_seconds</span></code>)]<wbr><code style="font-family:monospace; font-size:12px">WITH</code><wbr><code style="font-family:monospace; font-size:12px">TIME</code><wbr><code style="font-family:monospace; font-size:12px">ZONE</code></wbr></wbr></wbr></wbr>	All values of<wbr><code style="font-family:monospace; font-size:12px">TIMESTAMP</code><wbr>as well as(也,和...一样) time zone displacement(取代,位移) value, where<wbr><code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">fractional_seconds_precision</span></code><wbr>is the number of digits in the fractional part(数字的小数部分的位数) of the<wbr><code style="font-family:monospace; font-size:12px">SECOND</code><wbr>datetime field(日期时间字段). Accepted values are 0 to 9. The default is 6. The default format is determined explicitly by the<wbr><code style="font-family:monospace; font-size:12px">NLS_TIMESTAMP_FORMAT</code><wbr>parameter or implicitly by the<wbr><code style="font-family:monospace; font-size:12px">NLS_TERRITORY</code><wbr>parameter. The size is fixed at 13 bytes. This data type contains the datetime fields()<wbr><code style="font-family:monospace; font-size:12px">YEAR</code>,<wbr><code style="font-family:monospace; font-size:12px">MONTH</code>,<wbr><code style="font-family:monospace; font-size:12px">DAY</code>,<wbr><code style="font-family:monospace; font-size:12px">HOUR</code>,<wbr><code style="font-family:monospace; font-size:12px">MINUTE</code>,<wbr><code style="font-family:monospace; font-size:12px">SECOND</code>,<wbr><code style="font-family:monospace; font-size:12px">TIMEZONE_HOUR</code>, and<wbr><code style="font-family:monospace; font-size:12px">TIMEZONE_MINUTE</code>. It has fractional seconds and an explicit(明确的) time zone.</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
231	TIMESTAMP<wbr>[(<code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">fractional_seconds</span></code>)]<wbr><code style="font-family:monospace; font-size:12px">WITH</code><wbr><code style="font-family:monospace; font-size:12px">LOCAL</code><wbr><code style="font-family:monospace; font-size:12px">TIME</code><wbr><code style="font-family:monospace; font-size:12px">ZONE</code></wbr></wbr></wbr></wbr></wbr>	All values of<wbr><code style="font-family:monospace; font-size:12px">TIMESTAMP(时间戳</code><code style="font-family:monospace; font-size:12px">)</code><wbr><code style="font-family:monospace; font-size:12px">WITH</code><wbr><code style="font-family:monospace; font-size:12px">TIME</code><wbr><code style="font-family:monospace; font-size:12px">ZONE</code>, with the following exceptions(以下例外):</wbr></wbr></wbr></wbr> 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(由)<wbr><code style="font-family:monospace; font-size:12px">NLS_TIMESTAMP_FORMAT</code><wbr>parameter or implicitly by the<wbr><code style="font-family:monospace; font-size:12px">NLS_TERRITORY</code><wbr>parameter. The size is 7 or 11 bytes, depending on the precision.</wbr></wbr></wbr></wbr>
182	INTERVAL<wbr><code style="font-family:monospace; font-size:12px">YEAR</code><wbr>[(<code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">year_precision</span></code>)]<wbr><code style="font-family:monospace; font-size:12px">TO</code><wbr><code style="font-family:monospace; font-size:12px">MONTH</code></wbr></wbr></wbr></wbr>	Stores(存储) a period(周期,期间) of time in years and months, where<wbr><code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">year_precision</span></code><wbr>is the number of digits in the<wbr><code style="font-family:monospace; font-size:12px">YEAR</code><wbr>datetime field. Accepted values are 0 to 9. The default is 2. The size is fixed(固定) at 5 bytes.</wbr></wbr></wbr></wbr>
183	INTERVAL<wbr><code style="font-family:monospace; font-size:12px">DAY</code><wbr>[(<code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">day_precision</span></code>)]<wbr><code style="font-family:monospace; font-size:12px">TO</code><wbr><code style="font-family:monospace; font-size:12px">SECOND</code><wbr>[(<code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">fractional_seconds</span></code>)]</wbr></wbr></wbr></wbr></wbr>	Stores a period of time in days, hours, minutes, and seconds, where day_precision<wbr>is the maximum number of digits in the<wbr><code style="font-family:monospace; font-size:12px">DAY</code><wbr>datetime field. Accepted values are 0 to 9. The default is 2.</wbr></wbr></wbr> fractional_seconds_precision<wbr>is the number of digits(在...位数) in the fractional part(小数部位) of the<wbr><code style="font-family:monospace; font-size:12px">SECOND</code><wbr>field(第二个字段). Accepted values are 0 to 9. The default is 6.</wbr></wbr></wbr> The size is fixed at 11 bytes.
23	RAW(size)	Raw(生的，原始) binary data of length<wbr><code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">size</span></code><wbr>bytes. Maximum<wbr><code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">size</span></code><wbr>is 2000 bytes. You must specify<wbr><code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">size</span></code><wbr>for a<wbr><code style="font-family:monospace; font-size:12px">RAW</code><wbr>value(原始值).</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
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 the<wbr><code style="font-family:monospace; font-size:12px">ROWID</code>pseudocolumn.</wbr>
208	UROWID<wbr>[(<code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">size</span></code>)]</wbr>	Base 64 string representing the logical(合乎逻辑的) address of a row of an index-organized(索引组织) table. The optional(可选)<wbr><code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">size</span></code><wbr>is the size of a column of type<code style="font-family:monospace; font-size:12px">UROWID(是typeUROWID列的大小)</code>. The maximum size and default is 4000 bytes.</wbr></wbr>
96	CHAR<wbr>[(<code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">size</span></code><wbr>[<code style="font-family:monospace; font-size:12px">BYTE</code><wbr>|<wbr><code style="font-family:monospace; font-size:12px">CHAR</code>])]</wbr></wbr></wbr></wbr>	Fixed-length(固定长度) character data of length<wbr><code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">size</span></code><wbr>bytes or characters. Maximum<wbr><code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">size</span></code><wbr>is 2000 bytes or characters. Default and minimum<wbr><code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">size</span></code>is 1 byte.</wbr></wbr></wbr></wbr></wbr> BYTE<wbr>and<wbr><code style="font-family:monospace; font-size:12px">CHAR</code><wbr>have the same(相同) semantics(语义) as for<wbr><code style="font-family:monospace; font-size:12px">VARCHAR2</code>.</wbr></wbr></wbr></wbr>
96	NCHAR[(size)]	Fixed-length character data of length<wbr><code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">size</span></code><wbr>characters. The number of bytes can be up to(最多可以有) two times<wbr><code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">size</span></code><wbr>for<wbr><code style="font-family:monospace; font-size:12px">AL16UTF16</code><wbr>encoding and three times<wbr><code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">size</span></code><wbr>for<wbr><code style="font-family:monospace; font-size:12px">UTF8</code><wbr>encoding. Maximum<wbr><code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">size</span></code><wbr>is determined(取决于,决定) by the national(国家) character set(字符集) definition(定义), with an upper limit(上限) of 2000 bytes. Default and minimum<wbr><code style="font-family:monospace; font-size:12px"><span style="word-wrap:normal; word-break:normal; line-height:18px; font-style:italic">size</span></code><wbr>is 1 character.</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
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 sections(部分,截面) that follow describe the Oracle data types as they are stored in Oracle Database. For information(信息) on specifying these data types as literals(文字), refer(参考) to<wbr><a href="file:///F:/hzy/help/E11882_01/server.112/e26088/sql_elements003.htm#i11223" style="text-decoration:none; color:rgb(65,100,111)">"Literals"</a>.</wbr>