Summary of Oracle database types

Racle Basic data types (also called built-in data types built-in datatypes) can be categorized by type: String type, Number type, date type, LOB type, LONG raw& RAW type, ROWID & urowid type.
A String type
Char type char (size [BYTE | CHAR])

Char type, fixed-length string, is padded with spaces to reach its maximum length. A non-null char (12) always contains 12 bytes of information. The Char field can store up to 2,000 bytes of information. If you do not specify a char length when you create a table, the default is 1. In addition you can specify that it stores bytes or characters, such as char (Bytye) char (three char). In general, the default is to store bytes, you can view the database parameters

1.2:nchar type

This is a fixed-length string that contains data in Unicode format. The nchar field can store up to 2,000 bytes of information. Its maximum length depends on the national character set. In addition, if the field is a nchar type, you need to write the following

SELECT translated_description from Product_descriptions

WHERE translated_name = N ' LCD Monitor 11/pm ';

1.3 varchar Type

Do not use the varchar data type. Use the VARCHAR2 data type. Although the varchar data type is currently synonymous with VARCHAR2, the varchar data type has different comparison semantics compared to a single data type used for variable-length strings.

1.4:VARCHAR2 type

Variable length string, unlike the char type, it does not use spaces to fill to the maximum length. VARCHAR2 can store up to 4,000 bytes of information.

1.5:NVARCHAR2 type

This is a variable-length string that contains data in Unicode format. NVARCHAR2 can store up to 4,000 bytes of information.

Two. Number types

2.1 Number Type

Number (P,s) is the most common numeric type that can hold a data range of 10^130~10^126 (without this value) and requires a 1~22 byte (byte) of storage space.

P is the abbreviation for Precison, the abbreviation of precision, indicating the number of digits of a valid number, up to 38 significant digits

S is the English abbreviation for scale and can be used in -84~127. When scale is positive, the number of digits from the decimal point to the lowest significant number, which is negative, indicates the number of digits from the maximum significant number to the

Here is an example of an official document

2.2 Integer type

Integer is the subtype of number, which is equivalent to number (38,0), which is used to store integers. If the value inserted and updated has a decimal number, it will be rounded

2.3 Floating point

Floating-point numbers can have a decimal point anywhere from the first to the last number, or you can have no decimal points at all. The exponent may (optionally) be used for the following number of increased ranges (for example, 1.777e-20). Scale values do not apply to floating-point numbers, because the number of digits that can be displayed after the decimal point is unrestricted. Binary_float

The Binary_float is a 32-bit, single-precision floating-point numeric data type. can support at least 6 bits of precision, each binary_float value requires 5 bytes, including length bytes.

Binary_double

The binary_double is a 64-bit, double-precision floating-point numeric data type. The value of each binary_double requires 9 bytes, including the length byte.

In a column of numbers, floating-point numbers have decimal precision. In Binary_float or binary_double columns, the floating-point number has a binary precision. The binary floating-point numbers support a special value of infinity and NaN (not a number).

You can specify a floating-point number that is listed in the range of table 2-4. The format used to specify floating-point numbers is defined in "Numeric text".
2.5 Float Type

The float type is also a subtype of number.

Float (n), number n indicates the precision of the bit, the number of values that can be stored. The value of N can range from 1 to 126. To convert from binary to decimal precision, multiply n by 0.30103. To convert from decimal to binary precision, use 3.32193 times decimal precision. The maximum value for 126-bit binary precision is approximately equivalent to 38 decimal digits precision.

Three. Date type

Date types are used to store date data, but are not stored directly in the database using a generic format (2012-08-08).

3.1 Date Type

Date is the most commonly used type of data, and dates data types store date and time information. Although date and time information can be represented by a character or number type, the date data type has special associated properties. For each date value, Oracle stores the following information: Century, year, month, date, hour, minute, and second. Typically consumes 7 bytes of storage space.

3.2 Timestamp type

This is a 7-byte or 12-byte fixed-width date/time data type. It differs from the date data type because timestamp can contain fractional seconds, and timestamp with fractional seconds can hold up to 9 digits to the right of the decimal point

3.3 TIMESTAMP with time zone type

This is a variant of the timestamp type, which contains the value of the time zone offset

3.4 TIMESTAMP with LOCAL time zone type

3.5 INTERVAL year to Moth

3.6 INTERVAL Day to SECOND

Four. LOB type

The built-in LOB data types include large and unstructured data such as blobs, CLOB, NCLOB, BFILE (external storage), such as text, images, video screens, and spatial data storage. BLOB, CLOB, NCLOB type

4.1 CLOB Data type

It stores single-byte and multibyte-character data. Supports a fixed-width and variable-width character set. CLOB objects can store up to (4 gigabytes-1) * (database block size) characters

4.2 NCLOB Data type

It stores data of the Unicode type, supports fixed-width and variable-width character sets, and Nclob objects can store text data up to (4 gigabytes-1) * (Database block size).

4.3 BLOB data type

It stores unstructured binary data large objects, which can be thought of as a bit stream without character set semantics, typically images, sounds, video, and other files. BLOB objects store binary data of up to (4 gigabytes-1) * (Database block size).

4.4 BFILE Data type

binary files, which are stored in a system file outside the database, are read-only, and the database will treat the file as a binary file

Five. Raw & LONG Raw type

5.1 Long type

It stores variable-length strings up to 2G of character data (2GB refers to 2000 megabytes instead of 2000 megabytes), and as with VARCHAR2 or char types, text stored in a long type is converted to character set. Oracle recommends using CLOB to replace the long type in development. Long columns are supported only for backward compatibility. The CLOB type is much less restrictive than the long type. The limit for the long type is as follows:

1. Only one column in a table can be a long type. (Why? some unclear white)

A 2.LONG column cannot be defined as a primary key or a unique constraint.

3. Cannot build an index

4.LONG data cannot specify a regular expression.

5. A function or stored procedure cannot accept parameters of the Long data type.

6.LONG columns cannot appear in a WHERE clause or integrity constraint (except that NULL and NOT NULL constraints may occur)

Six. ROWID & Urowid Type

Each row in the database has an address. However, some table row addresses are not physical or permanent, or are not generated by the Oracle database.

For example, the Index organization table row address is stored in the index of the leaf and can be moved.

For example, the rowid of an external table (such as accessing the DB2 table through a gateway) is not?? Standard Oracle's ROWID.

Oracle uses a common ROWID (urowids) storage address index to organize tables and appearances. The Index organization table has logical urowids, and foreign urowids of the table. Urowid these two types are stored in ROWID pseudo (the physical row ID of the heap Organization's table).

Creates a logical-based ROWID primary key in a table. The rowid of logic does not change, as long as the primary key does not change. The ROWID pseudo-urowid data type of the index organization table. You can access this pseudo-column, and you will heap the ROWID pseudo (i.e. use a SELECT ...) to organize the table. ROWID statement). If you want to store the ROWID index of the Organization table, then you can define a column of table-type UROWID to the column to retrieve the ROWID pseudo value.

Summary of Oracle database types