1. Character types
Data type |
Length |
Description |
CHAR (N) |
Default 1 bytes, maximum n value is 2000 |
Padding at the end to reach the specified length, exceeding the maximum length error. The default length is the number of bytes, and the character length can be from 1 bytes to four bytes. |
NCHAR (N) |
Default 1 characters, maximum storage content 2000 bytes |
Fills the space at the end to the specified length, and n is the number of Unicode characters. The default is 1 bytes. |
NVARCHAR2 (N) |
Maximum length must be specified, maximum storage content 4000 bytes |
Variable length type. n is the number of Unicode characters |
VARCHAR2 (N) |
The maximum length must be specified, at least 1 bytes or 1 characters, and the maximum n value is 4000 |
Variable length type. Error exceeding maximum length. The default is to store a string of length 0. |
VARCHAR |
With VARCHAR2 |
Do not recommend using |
2. Number types
Data type |
Length |
Description |
Number (P[,s]) |
1-22 bytes. P Value range 1 to 38 s range of values-84 to 127 |
Stores the number of fixed-point values with an absolute value range of 1.0 x 10-130 to 1.0 x 10 126. Value greater than or equal to 1.0 x 10 126 times wrong. P is a meaningful 10-digit number, positive s is the number of decimal places, and negative s indicates how many bits are rounded to the left of the decimal point. |
Binary_float |
5 bytes, which have a length byte. |
32-bit single-precision floating-point number type. The sign bit 1 bits, refers to the digit 8 bits, the tail digit 23 bits. |
Binary_double |
9 bytes, which have a length byte. |
64-bit double-precision floating-point number type. |
3. Time, time interval type
The Time field has a range of values:
Time field |
Time type Valid values |
Time interval Type valid values |
Year |
4712 to 9999, including 0 |
Any integer |
MONTH |
01 to 12 |
0 to 11 |
Day |
01 to 31 |
Any integer |
HOUR |
00 to 23 |
0 to 23 |
MINUTE |
00 to 59 |
0 to 59 |
SECOND |
XX to 59.9 (n), 9 (n) Not applicable with date type |
0 to 59.9 (n) |
Timezone_hour |
-1 to 14, not applicable with date and timestamp types |
Not available |
Timezone_minute |
00 to 59, not applicable with date and timestamp types |
Not available |
Timezone_region |
|
Not available |
Timezone_abbr |
|
Not available |
Time, time interval type:
Data type |
Length |
Description |
DATE |
7 bytes |
The default value is Sysdate year, month, and Day 01. Contains a time field, if the insertion value does not have a Time field, the default value is: 00:00:00 or 12:00:00 for 24-hour and 12-hour clock times. There are no seconds and time zones. |
TIMESTAMP [(Fractional_seconds_precision)] |
7 to 11 bytes |
The fractional_seconds_precision stores the fractional bits of seconds for Oracle, which defaults to 6 and an optional value of 0 to 9. There is no time zone. |
TIMESTAMP [(Fractional_seconds_precision)] with time ZONE |
13 bytes |
Using UTC, contains fields year, MONTH, Day, HOUR, MINUTE, SECOND, Timezone_ HOUR, Timezone_minute |
TIMESTAMP [(Fractional_seconds_precision)] with LOCAL time ZONE |
7 to 11 bytes |
When using the database time zone, use the reply time zone. |
INTERVAL [(Year_precision)] to MONTH |
5 bytes |
Contains the time interval type for the year and month. Year_precision is the number of digits in the year field, which defaults to 2 and is preferable to 0 to 9. |
INTERVAL Day [(day_precision)] To SECOND [(fractional_seconds_precision)] |
11 bytes |
Day_precision is the number of digits in the month field, which defaults to 2 and is preferable to 0 to 9. |
The time field values used by To_date () and date are all midnight values. or use the trunc () function to filter to ensure that the time field is a midnight value.
Time and time interval type operation rules:
Adds and subtract number type constants on date and timestamp (which are converted to date type values), which are in days.
All timestamp type operations are based on UTC time. That is, for the timestamp with LOCAL time zone, the conversion is first to UTC, and the calculation is completed before being converted back.
INTERVAL year to Month constants:
INTERVAL ' Year-month ' year/month (precision) to month
An error is returned when the year digit exceeds precision.
Where precision is the maximum number of digits, the default is 2, preferably 0 to 9.
Example: INTERVAL ' 123-2 ' year (3) to MONTH,
INTERVAL ' 123 ' year (3),
INTERVAL ' MONTH (3).
INTERVAL Day to second constants:
INTERVAL ' n/time_expr/n time_expr ' Day/hour/minute (leading_precision) to Hour/minute/second (fractional_second_ Precision
INTERVAL ' n/time_expr ' SECOND (leading_precision, fractional_second_precision)
time_expr format: HH[:MI[:SS[.N]] [or MI[:SS[.N]] or SS[.N] if n is greater than seconds precision, then round N.
You can use n time_expr only if the first field is day.
Leading_precision default is 2, preferably 0 to 9.
4. Large Object Type
Data type |
Length |
Description |
Blob |
Maximum (4gb-1) * Database block size |
Storage of unstructured binaries. Supports transactional processing. |
Clob |
Maximum (4gb-1) * Database block size |
Stores single-byte or multibyte-character data. Supports transactional processing. |
NCLOB |
Maximum (4gb-1) * Database block size |
Stores Unicode data. Supports transactional processing. |
BFILE |
Maximum of 2 32-1 bytes |
The LOB address points to a binary file on the file system, maintaining the directory and file name. Do not participate in transaction processing. Only read-only operations are supported. |
The LOB column contains an LOB address that points to a lob type value within the database or outside the database.
5. Other types
Data type |
Length |
Description |
LONG |
Maximum of 2GB |
Variable-length type, which stores strings. Do not use this type when creating a table. |
RAW (N) |
The maximum is 2000 bytes, n is the number of bytes, and n must be specified |
Variable-length type, which does not change the value when the character set changes. |
LONG RAW |
Maximum of 2GB |
Variable-length types, not recommended, are recommended for conversion to BLOB types, and do not change values when the character set changes. |
ROWID |
10 bytes |
Represents the address of the record. A string that is displayed as a 18-bit. A relative unique address value that is used to locate a record in the database. Typically, this value is determined and unique when the row of data is inserted into a database table. |
Urowid (N) |
|
|
ROWID: Data Object number 32 bits (6 characters), file Number 10 bits (3 characters), block number 22 bit (6 characters), line number 16 bit (3 characters)
Get rowid information using the DQMS_ROWID package:
Select Dbms_rowid.rowid_object (ROWID) object_id, dbms_rowid.rowid_relative_fno (ROWID) file_id, Dbms_rowid.rowid_block_number (ROWID) block_id, Dbms_rowid.rowid_row_number (ROWID) num from Dept;
Oracle Basic Data Type Summary