Summary of Oracle10g data types

the
1. Character type
the
type of data
length
illustrate
CHAR(n BYTE/CHAR)
The default is 1 byte, and the maximum value of n is 2000
Spaces are filled at the end to reach the specified length, and an error will be reported if the maximum length is exceeded. The specified length is the number of bytes by default, and the character length can range from 1 byte to four bytes.
NCHAR(n)
The default is 1 character, and the maximum storage content is 2000 bytes
Spaces are padded at the end to reach the specified length, and n is the number of Unicode characters. The default is 1 byte.
NVARCHAR2(n) www.2cto.com
The maximum length must be specified, and the maximum storage content is 4000 bytes
Variable length type. n is the number of Unicode characters
VARCHAR2(n BYTE/CHAR)
The maximum length must be specified, at least 1 byte or 1 character, and the maximum value of n is 4000
Variable length type. An error is reported when the maximum length is exceeded. By default, a string of length 0 is stored.
VARCHAR
Same as VARCHAR2
Not recommended for use
the
2. Number type
the
type of data
length
illustrate
NUMBER(p[,s])
1-22 bytes.
P ranges from 1 to 38
S ranges from -84 to 127
Stores fixed-point numbers with values ​​ranging in absolute value from 1.0 x 10 -130 to 1.0 x 10 126. An error is reported when the value is greater than or equal to 1.0 x 10 126.
p is a significant decimal digit, positive value s is the number of decimal places, and negative value s indicates how many digits are rounded to the left of the decimal point.
BINARY_FLOAT
5 bytes, including a length byte.
32-bit single-precision floating-point number type.
1 sign bit, 8 exponent bits, and 23 mantissa bits.
BINARY_DOUBLE
9 bytes, including a length byte.
64-bit double-precision floating-point number type.
the
3. Time, time interval type
www.2cto.com
The range of possible values ​​for the time field:
time field
Valid values ​​for time type
Valid values ​​for time interval type
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
00 to 59.9(n), 9(n) does not apply to the DATE type
0 to 59.9(n)
TIMEZONE_HOUR
-1 to 14, not applicable to DATE and TIMESTAMP types
unavailable
TIMEZONE_MINUTE
00 to 59, not applicable to DATE and TIMESTAMP types
unavailable
TIMEZONE_REGION
the
unavailable
TIMEZONE_ABBR
www.2cto.com
unavailable
the
Time, time interval type:
type of data
length
illustrate
DATE
7 bytes
The default value is the year, month and day of SYSDATE, and 01. Contains a time field. If the inserted 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 time. There are no minutes, seconds and time zone.
TIMESTAMP [(fractional_seconds_precision)]
7 to 11 bytes
fractional_seconds_precision is the digit of the fractional part of the second value stored by Oracle, the default is 6, and the optional value is 0 to 9. There is no time zone.
TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE
13 bytes
Use UTC with fields YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_
HOUR, TIMEZONE_MINUTE
TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE
7 to 11 bytes
Use the database time zone when saving, and use the session time zone when fetching.
INTERVAL YEAR [(year_precision)] TO MONTH
5 bytes
Contains the time interval type of year and month. year_precision is the number of digits in the year field, the default is 2, and it can range from 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, the default is 2, and it can range from 0 to 9.
The time field values ​​used by TO_DATE() and DATE are midnight values. Or use the TRUNC() function to filter to ensure that the time field has a midnight value.
the
Time and interval type operation rules:
Add or subtract a NUMBER type constant to DATE and TIMESTAMP (which will be converted to a DATE type value), and the constant unit is the number of days.
All TIMESTAMP operations are based on UTC time. That is, for TIMESTAMP WITH LOCAL TIME ZONE, it is first converted to UTC time, and then converted back after the calculation is completed.
INTERVAL YEAR TO MONTH constant:
INTERVAL’year-month’YEAR/MONTH(precision) TO MONTH
When the number of digits of year exceeds precision, an error is returned.
Among them, precision is the maximum number of digits, the default is 2, and it can be 0 to 9.
Example: INTERVAL '123-2' YEAR(3) TO MONTH ,
INTERVAL & # 39; 123 & # 39; YEAR (3), www.2cto.com
INTERVAL & # 39; 300 & # 39; 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 the precision of minutes and seconds, round up n.
n time_expr can only be used if the first field is DAY.
leading_precision is 2 by default, and can range from 0 to 9.
4. Large object type
the
type of data
length
illustrate
BLOB
The maximum is (4GB-1)*database block size
Store unstructured binary files. Support transaction processing.
CLOB
The maximum is (4GB-1)*database block size
Store single-byte or multi-byte character data. Support transaction processing.
NCLOB
The maximum is (4GB-1)*database block size
Store Unicode data. Support transaction processing.
BFILE
Up to 2 32-1 bytes
The LOB address points to a binary file on the file system, maintaining directory and file names. Does not participate in transaction processing. Only read-only operations are supported. www.2cto.com
The LOB column contains a LOB address, pointing to a LOB type value inside or outside the database.
5.other types
the
type of data
length
illustrate
long
up to 2GB
A variable-length type that stores strings. Do not use this type when creating tables.
RAW(n)
Maximum 2000 bytes, n is the number of bytes, must specify n
Variable length type, the value will not change when the character set changes.
LONG RAW
up to 2GB
Variable-length type, not recommended to use, it is recommended to convert to BLOB type, the value will not change when the character set changes.
ROWID
10 bytes
Represents the address of record. Displayed as an 18-bit string. A relatively unique address value used to locate a record in the database. Normally, this value is determined and unique when the row of data is inserted into the database table.
UROWID(n) www.2cto.com
the
ROWID: data object number 32 bits (6 characters), file number 10 bits (3 characters), block number 22 bits (6 characters), row number 16 bits (3 characters)
Use the dqms_rowid package to get ROWID information:
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;

the
Author ?R?