Basic Oracle data type, basic oracle Data Type

Basic Oracle data type, basic oracle Data Type

I. Data Types in Oracle

Oracle provides a wide range of data types to store data, including:

　　1. Character Type: Mainly used to store string-type data.　　

Data Type	Length	Description
CHAR (n BYTE/CHAR)	The default value is 1 byte. The maximum value of n is 2000.	Enter spaces at the end to reach the specified length. If the length exceeds the maximum length, an error is returned. The default length is the number of bytes. The length of a character can be 1 to 4 bytes.
NCHAR (n)	The default value is 1 character, and the maximum storage content is 2000 bytes.	Enter spaces at the end of the string to reach the specified length. n is the number of Unicode characters. The default value is 1 byte.
NVARCHAR2 (n)	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. It must be at least 1 byte or 1 character. The maximum n value is 4000.	Variable Length type. If the maximum length is exceeded, an error is returned. The default storage is a string with a length of 0.
VARCHAR	Same as VARCHAR2	Not recommended

Differences between char, nchar, and varchar types

1. The length of char CHAR is fixed. It can contain a maximum of 2000 characters. The length is determined by the declaration. varchar is a variable-length string. The length is determined by the saved value.

2. varchar is a variable string with a maximum length of 2000 and varchar2 has a maximum length of 4000.

 

　　2. digit type: Data in integer and decimal format is stored.

Data Type	Length	Description
NUMBER (p [, s])	1-22 bytes. P value range: 1 to 38 S value range:-84 to 127.	Number of storage points. The absolute value range is 1.0x10-130 to 1.0x10 126. If the value is greater than or equal to 1.0x10 126, an error is returned. P indicates the meaningful decimal digits, and the positive value s indicates the decimal places. The negative value s indicates the number of digits rounded to the left of the decimal point.
BINARY_FLOAT	Five bytes, one of which is a Length byte.	32-Bit Single-precision floating point number type. The symbol is 1-bit, the index is 8-bit, and the tail is 23-bit.
BINARY_DOUBLE	9 bytes, one of which is a Length byte.	64-bit double-precision floating point number type.

　　3. Date and Time type: storage date format type, including Date and Time Interval

Data Type	Length	Description
DATE	7 bytes	The default value is year and month of SYSDATE, and day is 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 is no shard and time zone.
TIMESTAMP [(fractional_seconds_precision)]	7 to 11 bytes	Fractional_seconds_precision is the number of digits in the fractional part of the Oracle storage second value. The default value is 6. The optional values are 0 to 9. No time zone.
TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE	13 bytes	Use UTC, including the YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE _ HOUR, TIMEZONE_MINUTE
TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE	7 to 11 bytes	The Time Zone of the database is used for storage, and the time zone of the session is used for retrieval.
Interval year [(year_precision)] TO MONTH	5 bytes	The Time Interval Type of year and month. Year_precision is the number of digits in the year field. The default value is 2, and the value ranges 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 value is 2, and the value ranges from 0 to 9.

　　4. Large Object Type: stores big data objects, such as audio and video formats.

Data Type	Length	Description
BLOB	Maximum size (4 gb-1) * database block size	Stores unstructured binary files. Supports transaction processing.
CLOB	Maximum size (4 gb-1) * database block size	Stores single-byte or multi-byte character data. Supports transaction processing.
NCLOB	Maximum size (4 gb-1) * database block size	Stores Unicode data. Supports transaction processing.
BFILE	Up to 2 32-1 bytes	The LOB address points to a binary file on the file system and maintains the Directory and file name. Not involved in transaction processing. Only read-only operations are supported.

　　5. Other types:

Data Type	Length	Description
LONG	Up to 2 GB	Variable Length type, storing strings. Do not use this type when creating a table.
RAW (n)	The maximum value is 2000 bytes. n is the number of bytes. n must be specified.	Variable Length type. The value does not change when the character set changes.
LONG RAW	Up to 2 GB	It is not recommended to use the variable length type. We recommend that you convert it to the BLOB type. The value will not change when the character set changes.
ROWID	10 bytes	Indicates the recorded address. It is a string of 18 bits. It is used to locate a unique address value of a record in the database. Generally, this value is determined and unique when this row of data is inserted into the database table.