文件筆記--Datatypes
閱讀文件時,寫寫筆記而已。一般只寫綜述的那一章。
Oracle® Database SQL Reference
10g Release 2 (10.2)
Part Number B14200-02
Datatypes
Each value manipulated by Oracle Database has a datatype. The datatype of a value associates a fixed set of properties with the value. These properties cause Oracle to treat values of one datatype differently from values of another. For example, you can add values of NUMBER datatype, but not values of RAW datatype.
【oracle使用datatype來區分對待不同的值,有不同的操作】
When you create a table or cluster, you must specify a datatype for each of its columns. When you create a procedure or stored function, you must specify a datatype for each of its arguments. These datatypes define the domain of values that each column can contain or each argument can have. For example, DATE columns cannot accept the value February 29 (except for a leap year) or the values 2 or 'SHOE'. Each value subsequently placed in a column assumes the datatype of the column. For example, if you insert '01-JAN-98' into a DATE column, then Oracle treats the '01-JAN-98' character string as a DATE value after verifying that it translates to a valid date.
【datatype定義了值域】
Oracle Database provides a number of built-in datatypes as well as several categories for user-defined types that can be used as datatypes. The syntax of Oracle datatypes appears in the diagrams that follow. The text of this section is divided into the following sections:
A datatype is either scalar or nonscalar. A scalar type contains an atomic value, whereas a nonscalar (sometimes called a "collection") contains a set of values. A large object (LOB) is a special form. of scalar datatype representing a large scalar value of binary or character data. LOBs are subject to some restrictions that do not affect other scalar types because of their size. Those restrictions are documented in the context of the relevant SQL syntax.
【datatype分為2類:scalar和 nonscalar;LOB是特殊形式的LOB】
The Oracle precompilers recognize other datatypes in embedded SQL programs. These datatypes are called external datatypes and are associated with host variables. Do not confuse built-in datatypes and user-defined types with external datatypes. For information on external datatypes, including how Oracle converts between them and built-in datatypes or user-defined types, see Pro*COBOL Programmer's Guide, and Pro*C/C++ Programmer's Guide.
datatypes::=
- Oracle_built_in_datatypes
- character_datatypes
- { CHAR [ (size [ BYTE | CHAR ]) ]
- | VARCHAR2 (size [ BYTE | CHAR ])
- | NCHAR [ (size) ]
- | NVARCHAR2 (size)
- }
- number_datatypes
- { NUMBER [ (precision [, scale ]) ]
- | BINARY_FLOAT
- | BINARY_DOUBLE
- }
- long_and_raw_datatypes
- { LONG | LONG RAW | RAW (size) }
- datetime_datatypes
- { DATE
- | TIMESTAMP [ (fractional_seconds_precision) ]
- [ WITH [ LOCAL ] TIME ZONE ])
- | INTERVAL YEAR [ (year_precision) ] TO MONTH
- | INTERVAL DAY [ (day_precision) ] TO SECOND
- [ (fractional_seconds_precision) ]
- }
- large_object_datatypes
- { BLOB | CLOB | NCLOB | BFILE }
- rowid_datatypes
- { ROWID | UROWID [ (size) ] }
- character_datatypes
- ANSI_supported_datatypes
- { CHARACTER [VARYING] (size)
- | { CHAR | NCHAR } VARYING (size)
- | VARCHAR (size)
- | NATIONAL { CHARACTER | CHAR }
- [VARYING] (size)
- | { NUMERIC | DECIMAL | DEC }
- [ (precision [, scale ]) ]
- | { INTEGER | INT | SMALLINT }
- | FLOAT [ (size) ]
- | DOUBLE PRECISION
- | REAL
- }
- user_defined_types
- Oracle_supplied_types
- any_types
- { SYS.AnyData | SYS.AnyType | SYS.AnyDataSet }
- | XML_types
- { XMLType | URIType }
- spatial_types
- { SDO_Geometry | SDO_Topo_Geometry |SDO_GeoRaster }
- media_types
- { ORDAudio
- | ORDImage
- | ORDVideo
- | ORDDoc
- | OrdImageSignature
- | still_image_object_types
- }
- expression_filter_type
- { SI_StillImage
- | SI_AverageColor
- | SI_PositionalColor
- | SI_ColorHistogram
- | SI_Texture
- | SI_FeatureList
- | SI_Color
- }
- any_types
For descriptions of the Oracle built-in datatypes, please refer to .
The ANSI-supported datatypes appear in the figure that follows. discusses the mapping of ANSI-supported datatypes to Oracle built-in datatypes.
For a description of the expression_filter_type, please refer to . Other Oracle-supplied types follow:
For descriptions of the Any types, please refer to .
For descriptions of the XML types, please refer to .
For descriptions of the media types, please refer to .
Oracle Built-in Datatypes
The table that follows summarizes Oracle built-in datatypes. Please refer to the syntax in the preceding sections for the syntactic elements. The codes listed for the datatypes are used internally by Oracle Database. The datatype code of a column or object attribute is returned by the DUMP function.
Table 2-1 Built-in Datatype Summary
Oracle Built-in Datatypes
The table that follows summarizes Oracle built-in datatypes. Please refer to the syntax in the preceding sections for the syntactic elements. The codes listed for the datatypes are used internally by Oracle Database. The datatype code of a column or object attribute is returned by the DUMP function.
Table 2-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 sizefor 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 size for 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 theNLS_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, where fractional_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_TERRITORYparameter. 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)] WITH TIME 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 the NLS_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)] WITH LOCAL 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 the NLS_TERRITORY parameter. The sizes varies from 7 to 11 bytes, depending on the precision.
182
INTERVAL YEAR[(year_precision)] TOMONTH
Stores a period of time in years and months, where year_precision is the number of digits in the YEAR datetime field. Accepted values are 0 to 9. The default is 2. The size is fixed at 5 bytes.
183
INTERVAL DAY[(day_precision)] TOSECOND[(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 ROWIDpseudocolumn.
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 typeUROWID. 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 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 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.
The sections that follow describe the Oracle datatypes as they are stored in Oracle Database. For information on specifying these datatypes as literals, please refer to.
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 sizefor 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 size for 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 theNLS_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, where fractional_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_TERRITORYparameter. 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)] WITH TIME 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 the NLS_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)] WITH LOCAL TIME ZONE |
All values of TIMESTAMP WITH TIME ZONE, with the following exceptions:
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. |
182 |
INTERVAL YEAR[(year_precision)] TOMONTH |
Stores a period of time in years and months, where year_precision is the number of digits in the YEAR datetime field. Accepted values are 0 to 9. The default is 2. The size is fixed at 5 bytes. |
183 |
INTERVAL DAY[(day_precision)] TOSECOND[(fractional_seconds)] |
Stores a period of time in days, hours, minutes, and seconds, where
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 ROWIDpseudocolumn. |
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 typeUROWID. 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 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 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. |
The sections that follow describe the Oracle datatypes as they are stored in Oracle Database. For information on specifying these datatypes as literals, please refer to.
Datatype Limits
Datatypes | Limit | Comments |
---|---|---|
BFILE | Maximum size: 4 GB
Maximum size of a file name: 255 characters Maximum size of a directory name: 30 characters Maximum number of open BFILEs: see Comments |
The maximum number of BFILEs is limited by the value of theSESSION_MAX_OPEN_FILES initialization parameter, which is itself limited by the maximum number of open files the operating system will allow. |
BLOB | Maximum size: (4 GB - 1) * DB_BLOCK_SIZEinitialization parameter (8 TB to 128 TB) | The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000Foot 1 ). |
CHAR | Maximum size: 2000 bytes | |
CHAR VARYING | Maximum size: 4000 bytes | |
CLOB | Maximum size: (4 GB - 1) * DB_BLOCK_SIZEinitialization parameter (8 TB to 128 TB) | The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000Footref 1). |
Literals (characters or numbers in SQL or PL/SQL) | Maximum size: 4000 characters | |
LONG | Maximum size: 2 GB - 1 | Only one LONG column is allowed per table. |
NCHAR | Maximum size: 2000 bytes | |
NCHAR VARYING | Maximum size: 4000 bytes | |
NCLOB | Maximum size: (4 GB - 1) * DB_BLOCK_SIZEinitialization parameter (8 TB to 128 TB) | The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000Footref 1). |
NUMBER | 999...(38 9's) x10125 maximum value | Can be represented to full 38-digit precision (the mantissa). |
-999...(38 9's) x10125 minimum value | Can be represented to full 38-digit precision (the mantissa). | |
Precision | 38 significant digits | |
RAW | Maximum size: 2000 bytes | |
VARCHAR | Maximum size: 4000 bytes | |
VARCHAR2 | Maximum size: 4000 bytes |
Footnote 1 The absolute maximum number of columns in a table is 1000. However, when you create an object table (or a relational table with columns of object, nested table, varray, or REF type), Oracle maps the columns of the user-defined types to relational columns, creating in effect hidden columns that count toward the 1000-column limit. For details on how Oracle calculates the total number of columns in such a table, refer to .
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/23650854/viewspace-683061/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- InnoDB文件筆記(一)筆記
- Unity IMGUI 文件筆記UnityGUI筆記
- 《webpack文件》學習筆記Web筆記
- [BI專案記]-文件版本管理筆記筆記
- InnoDB文件筆記(二)—— Redo Log筆記
- InnoDB文件筆記(三)—— Undo Log筆記
- 文件筆記--Oracle Data Pump 2筆記Oracle
- 文件筆記--Oracle Data Pump 1筆記Oracle
- Kafka文件閱讀筆記(一)Kafka筆記
- React Router文件閱讀筆記(上)React筆記
- kafka官方文件學習筆記2–QuickStartKafka筆記UI
- 【筆記】jQuery原始碼(文件處理3)筆記jQuery原始碼
- Data Warehouse Guide文件筆記(四):dimensionGUIIDE筆記
- Datatypes (1) : ROWID and UROWID
- ES 筆記三十二:文件分散式儲存筆記分散式
- 《javascript高階程式設計》筆記:文件模式JavaScript程式設計筆記模式
- XtraBackup官方文件讀書筆記和測試筆記
- Using Byte and Char with Character Datatypes
- ES 筆記四十三:文件的父子關係筆記
- ES 筆記四:文件的基本 CRUD 與批量操作筆記
- 筆記三:基本概念-文件、索引和 REST API筆記索引RESTAPI
- flask文件學習筆記1-快速入門Flask筆記
- oracle12c Performing Backup and Recovery文件筆記OracleORM筆記
- bootstrap 學習筆記之 文件結構不能變boot筆記
- SpringBoot文件之Profiles的閱讀筆記Spring Boot筆記
- SpringBoot文件之IO的閱讀筆記Spring Boot筆記
- SpringBoot文件之Web的閱讀筆記Spring BootWeb筆記
- Libevent 官方文件學習筆記(2. bufferevent部分)筆記
- C#學習筆記——MDI窗體(多文件介面)C#筆記
- 《例項化需求》閱讀筆記(3)-活的文件筆記
- Data Warehouse Guide文件筆記(三):RELY constraintsGUIIDE筆記AI
- 【筆記】9i 文件中的一個問題筆記
- SpringBoot文件之入門的閱讀筆記Spring Boot筆記
- SpringBoot文件之Developing的閱讀筆記Spring Bootdev筆記
- slot 插槽筆記(與文件基本沒差別,僅作學習記錄)筆記
- 預設文件解析--手機web app開發筆記(二)WebAPP筆記
- Libev 官方文件學習筆記 – 03:常用 watcher 介面筆記
- 關於 iOS 集合官方文件的一份小筆記iOS筆記