SQL data types in Oracle

SQL data types in Oracle

Data types are the basis of data storage for each programming language. They are also a benchmark for the implementation of programming languages. Some programming languages may have a wide range of data types, such as java, c, the support for data computing is comprehensive, and some programming is relatively simple. It focuses more on implementing specific functions, and the data type is much simpler. For example, shell.

For SQL in Oracle, data type support is comprehensive. Whether it is required for data computing or complex business logic encapsulation, data type support is both built-in and scalable. You can select as needed.

Currently, Oracle data types have the following structure.

User-Defined types are basically implemented by type and can be flexibly customized as needed.

The built-in data type is the main application direction. Generally divided into scalar, set, relational

For more information about custom data types.

Oracle PL/SQL Composite data types

Oracle Data Type learning notes

Oracle-defined data Type

Oracle DB implicit and explicit data type conversion

Common Oracle Data Types and integrity constraints

Oracle 4 lobs Data Types

A simple example is as follows. We can create a test table test_datatype to briefly describe it.

Create table test_datatype (id number, name varchar2 (100), memo varchar2 (1000 ));

Create or replace type t_test_datatype
Object
(
Id number,
Name varchar2 (100)
)
/

Create or replace type tt_test_datatype as table of t_test_datatype
/

Create table new_test_datatype
(
Object_id number,
Other_columns tt_test_datatype
)
Nested table other_columns store as other_columns_nt
/

For built-in data types, a simple summary and description are summarized into tables.

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
NUMBER (p [, s])	1-22 bytes.	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.
	P value range: 1 to 38
	S value range:-84 to 127.
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.

Some data samples are summarized as follows:

Input data	Data Type	Storage results
7,456,123.89	NUMBER	7456123.89
7,456,123.89	NUMBER (*, 1)	7456123.9
7,456,123.89	NUMBER (9)	7456124
7,456,123.89	NUMBER (9, 2)	7456123.89
7,456,123.89	NUMBER (9, 1)	7456123.9
7,456,123.89	NUMBER (6)	(Not accepted, exceeds precision)
7,456,123.89	NUMBER (7,-2)	7456100