String type
tea
The char data type stores fixed-length character values. The CHAR data type can contain 1 to 2000 characters. If the length of CHAR is not specified explicitly, its default length will be set to 1. If you assign a value to the CHAR variable and its length is less than the specified length, Oracle will automatically fill it with spaces.
Note: If a value longer than the fixed length is given and a space is added at the end, the space will be deleted to the fixed length. If the value is too large, Oracle will return an error.
Variable length string
Used to save variable-length string data. Where the maximum byte length is specified by (size). The length of each line is variable, and the maximum length of each line is 4000 bytes. Before setting the size, consider whether the character set is single byte or multi-byte.
Note: varchar is an obsolete type, and oracle currently recommends using varchar2. Although varchar can also be used, I am not sure whether it will be supported in future versions.
Varchar2
Used to save variable-length string data. Where the maximum byte length is specified by (size). The length of each line is variable, and the maximum length of each line is 4000 bytes. Before setting the size, consider whether the character set is single byte or multi-byte. Because the VARCHAR2 data type only stores the CHARacters assigned to the column (no spaces), the storage space required by VARCHAR2 is smaller than that of the char data type.
Difference: varchar2 treats all characters as two bytes (in general), varchar only takes two bytes for Chinese characters and full-width characters, and both numbers and English characters are one byte; VARCHAR2 treats an empty string as null, while VARCHAR still treats an empty string as null. In most cases, it is recommended to use the varchar2 type to ensure better compatibility.
Enchar
Unicode data type, a fixed-length string depending on the character set, with a maximum length of 2000 bytes.
Nvarchar2
Unicode data type, a variable-length string depending on the character set, with a maximum length of 4000 bytes.
long
The maximum length of a variable-length character column is 2GB, which is used for long data strings without string search.
Description: This type is a legacy data type, which is no longer supported in the future and is gradually replaced by big data types such as BLOB, CLOB and NCLOB.
Digital type
figure
The precision of the # data type can be as high as 38 bits, and it has two qualifiers, such as the column NUMBER (precision, decimal places). Precision indicates the significant bit in a number. If no precision is specified, Oracle will use 38 as the precision. Scale represents the number of digits to the right of the decimal point. By default, scale is set to 0. If scale is set to a negative number, Oracle rounds the number to the number of digits specified to the left of the decimal point.
decimal
Oracle only supports decimal types grammatically, but at the bottom, it is actually a numeric type. In order to move data from Oracle database to other databases, decimal type is supported.
float
The FLOAT type is also a subtype of NUMBER. Its format is Float(n), and the number n represents the precision of bits and the number of values that can be stored. The value of n can be from 1 to 126. To convert the precision from binary to decimal, multiply n by 0.30 103. To convert decimal precision to binary precision, multiply decimal precision by 3.32 193. The maximum value of 126 bit binary precision is approximately equivalent to 38 bit decimal precision.
Binary _ floating point
BINARY_FLOAT is a 32-bit, single-precision floating-point digital data type. Each BINARY_FLOAT value needs 5 bytes of storage space, of which 1 byte is used to store the length of the data value. Supports basic functions provided by numeric data types. But it' s binary precision and decimal precision is $ NUMBER. Therefore, it can provide faster mathematical operation speed and reduce the occupied storage space.
BINARY_DOUBLE
BINARY_DOUBLE is a 64-bit double-precision floating-point digital data type. Each BINARY_DOUBLE value requires 9 bytes of storage space, of which 1 byte is used to store the length of the data value. Supports basic functions provided by numeric data types. But it' s binary precision and decimal precision is $ NUMBER. Therefore, it can provide faster mathematical operation speed and reduce the occupied storage space.
Date data type
date
ORACLE's most commonly used date type, which can save date and time and can be used for common date processing. The date ranges from 47 BC121to AD1nine thousand nine hundred and ninety-nine12.
The storage of date type in the database is fixed at 7 bytes, the first byte: century+1 00, the second byte: year, the third byte: month, the fourth byte: day, the fifth byte: hour+1, and the sixth byte: minute+/kloc-.
time stamp
ORACLE commonly used date type, different from date, it can save not only date and time, but also decimal seconds. The number of decimal places can be specified as 0-9, and the default is 6 digits, so the highest precision can be ns (nanosecond). The database is stored in 7 or 1 1 byte. If the precision is 0, it is stored in 7 bytes, which is the same as the function of date type. If the precision is greater than 0, it is stored in 655438+0 bytes. Byte 1: century+100, byte 2: year, byte 3: month, byte 4: day, byte 5: hour+1, byte 6: minute+1, byte 7: second+1.
Note: If the timestamp date type is added or subtracted from the numerical value, it will be automatically converted to the date type, that is, decimal seconds will be automatically removed.
Time stamp with time zone
The timestamp is extended to store the time zone. Time stamp and time zone offset values, where fractional_seconds_precision is all the values of the fractional part of the number in the second date-time field. Acceptable values are 0 to 9. The default value is 6. The default format is to determine explicit NLS date format parameters or implicit NLS region parameters. The size is fixed to 13 bytes. This data type contains date and time fields Year, Month, Day, Hour, Minute, Second Time Zone Hour and Time Zone Minute. It has a definite fractional second and time zone.
Time stamp with local time zone
All timestamps with time zone values, except the following cases: when data standardizes the database time zone, it is stored in the database. When retrieving data, users can see the data in the session time zone.
The default format is to determine explicit NLS date format parameters or implicit NLS region parameters. The size of varies from 7 to 1 1 byte, depending on the precision.
The interval between years and months
The number of years and months of the storage period, where year_precision is the number of years, date and time fields. Acceptable values are 0 to 9. The default value is 2. The size is fixed at 5 bytes.
Day-second interval
Its format is: interval day [(DAY _ precision)] to second [(fractional _ seconds)], that is, a period of time is stored in days, hours, minutes and seconds, where day _ precision is the maximum number of digits in the day, date and time field. Acceptable values are 0 to 9. The default value is 2. Fractional_seconds_precision is the fractional part of the second field in the number. Acceptable values are 0 to 9. The default value is 6. The size is fixed to 1 1 byte.
The interval between years and months
Oracle syntax: interval year [(year _ precision)] to month, used to indicate the time difference, accurate only to the year and month. Year _ precision is a period of digital years, and the acceptable values are 0 to 9. The default value is 2. The size is fixed at 5 bytes.
Large object (LOB) data type
Peat field
Maximum 4G, storing single-byte character data. Suitable for storing long text.
Nclob
Maximum 4G, storing multi-byte national character data. Suitable for storing long text.
a drop of
Maximum 4G, storing binary data. Suitable for storing images, videos, audio, etc.
BFile
The maximum length is 4GB, and the maximum length of a large binary object file stored outside the database is 4GB. This external LOB type records changes through the database, but the specific storage of data is carried out outside the database. Oracle can read and query BFILE, but cannot write or participate in transactions.
Raw and long raw data types
raw
Raw(n) format, where n= 1 to 2000, variable-length binary data, and the maximum length n must be specified when defining fields. Oracle uses this format to save smaller graphics files or formatted text files, such as Miceosoft Word documents. Raw is an old data type, which will be gradually replaced by large object data types such as BLOB, CLOB and NCLOB in the future.
Long-term unprocessed
The maximum length is 2GB. Variable length binary data with a maximum length of 2GB. Oracle uses this format to save large graphics files or formatted text files (such as Miceosoft Word documents) and non-text files (such as audio and video). You cannot have both long and long raw types in the same table. Long raw is also an older data type, which will be gradually replaced by large object data types such as BLOB, CLOB and NCLOB in the future.
ROWID and UROWID data types
Rowid
ROWID is the unique identifier of a table row, and it is a pseudo-column, which can be used in SELECT, but cannot be modified by INSERT or UPDATE. ROWID column, which Oracle uses to build internal indexes. You can reference the value of ROWID, but ROWID is not stored in the database. You can create tables that contain ROWID data types. However, Oracle does not guarantee that this value is a legal rowids. The user must ensure that the rowid value is true and legal.
Urine wid
UROWID supports logical and physical ROWIDs, and the column UROWID type can store various ROWIDs. The UROWID type only exists in Oracle 8. 1 and can also be used to store the previous ROWID type data information.
Special data type
Xmltype
XmlType is a unique data type of Oracle since 9i. Is the powerful existence of inherited Blob. It can be used to store xml, and provide quite a few functions of directly reading XML documents and managing nodes. Theoretically, it can save 2G data. As a special storage type of XML data. Xmltype provides suitable support for saving, retrieval and operation.
Xmlindex
XMLIndex can be used for SQL/XML functions XMLExists (), XMLTable () and XMLQuery (), and it is valid in any part of the query; It is not limited to use in the WHERE clause. No other index used for XML data can do this.
Therefore, XMLIndex can improve the access speed of SELECT list data and FROM list data, which is especially useful for XML fragment extraction. Function-based index and CTXXPath index. You don't need to know in advance the XPath expression that will be used in the query. XMLIndex is completely universal, but function-based indexes are different. XMLIndex index can be used for XML schema-based or schema-based data. It can be used for binary XML and unstructured storage model. B-tree index is only applicable to schema-based data (structured storage) stored in object-relational way; This is inefficient for XML schema-based data stored in binary XML or CLOB instances. You can use the XMLIndex index to search for XPath expressions that target collections (that is, nodes that appear multiple times in a document). Function index is not.