Oracle Basic data types

Oracle Basic data types: string, number, date, LOB, LONG raw& RAW, ROWID & Urowid.

The data of string types can be divided into:

According to the library character set (Char/varchar2/clob/long)

International character Set (NCHAR/NVARCHAR2/NCLOB)

String data is stored in a data block after it is converted to a number (binary) by a character set.

A String type

Fixed length type (char/nchar), variable length type (VARCHAR2/NVARCHAR2)

Fixed length:

The field value entered is less than the limit length of the field, and when the data is actually stored, the space is automatically filled to the right and the contents of the field value are stored in the data block. This approach is a waste of space, but storage efficiency is better than variable length types. It also reduces the migration of data rows to occur.

1.1:char type CHAR (size [BYTE | CHAR])

The Char field can store up to 2,000 bytes of information. Does not specify a char length, the default is 1.

The default is to store bytes, and you can view the values of the database parameter nls_length_semantics.

1.2 varchar Type

Although varchar is currently synonymous with VARCHAR2, it is recommended to use VARCHAR2 rather than varchar.

1.4:VARCHAR2 type

VARCHAR2 can store up to 4,000 bytes of information.

Two. Number types

2.1 Number Type

Number (P,s):

P, indicating the number of digits of a valid number, up to a maximum of;

S, which represents the number of digits from the decimal to the lowest significant number, in the range of -84~127. When it is negative, indicates the number of digits from the maximum significant number to the decimal point

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 inserted and updated values have decimals, they are rounded.

2.3 Floating point

Binary_float

The binary_float can support at least 6 bits of precision, and each binary_float value requires 5 bytes, including length bytes.

Binary_double

The value of each binary_double requires 9 bytes, including the length byte.

2.4 Float Type

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.

Three. Date type

3.1 Date Type

Date is the most commonly used type of data, and dates data types store date and time information. 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. The Clob object can store characters up to (4 gigabytes-1) * (Database block size).

4.2 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.3 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

Oracle recommends using CLOB to replace the long type in development. Long columns are supported only for backward compatibility.

5.2 LONG RAW Type

can store 2GB of raw binary data (data that is not character set conversion).

5.3 Raw Type

Similar to Char, declares the way Raw (L), L is length, in bytes.

Oracle Basic data types