Common Data Types for Oracle table Creation

In Oracle, varchar has been used as a reserved word. If you use the varchar type, Oracle will not report an error, but after creating the table, you will find that it has automatically changed to varch.

In Oracle, varchar has been used as a reserved word. If you use the varchar type, Oracle will not report an error, but after creating the table, you will find that it has automatically changed to varch.

Preface:
It is not difficult to specify a data type for a column. It is difficult to specify a suitable data type. For the same string type, choose a fixed length or a variable length, among which there are many articles. Therefore, you need to patiently and carefully learn the following content.

When creating a table, you must specify the data type for each column in the table. If the actual data does not match the data type of the column, the database will refuse to save the data. For example, specify the birth date as "1980-13-31" for the student ".

In Oracle, common data types include:
String: the string is divided into fixed-length char and variable-length varchar2.
Number: integer number (integer), decimal number (total length, decimal place), write only number, indicating no limit.
Date: date type, which can be saved as year, month, day, hour, minute, and second.

Q: Why is the string type varchar2 in Oracle related to varchar?
Answer:
In Oracle, varchar has been used as a reserved word. If you use the varchar type, Oracle will not report an error, but after creating the table, you will find that it has automatically changed to the varchar2 type.

Q: Do I use number or integer )?
In addition, Oracle also has other types such as integer, but the disadvantage of using integer is that it does not know its length, and may even be on different operating systems (such as 32-bit CPU and 64-bit CPU) the allocated length is different. Using number to specify the number is always the number, and everything is in your own hands.

Q: How do I specify the length for the string and number types?
If the char type does not specify the length, the default value is 1, while varchar2 must specify the length.
The maximum length of the char and varchar types is more than 4000 characters.
Number indicates an unlimited integer or decimal number. It can store very large numbers or numbers with many decimal places. To save disk space, specify the length as much as possible.
Date: The length is not required.

Problem: the maximum length of a string is about 4000. What if I want to save more content?
Answer:
There are two solutions. The first is to use a large object type, that is, CLOB or BLOB type, but programming is troublesome. The second is to use a one-to-many parent and child tables.

A large object refers to a large amount of data. If char or varchar2 is used, the maximum length of the column is about 4000; if more content is used, one of the methods is to set the column to the CLOB type, but only to save character data, such as novels. For binary data, slice, sound, and office documents, you must set the BLOB type for the column. CLOB or BLOB can contain up to 4 GB of content. For a movie, the most common practice is to store the name, path, and other information of the movie in the table. The movie is saved directly on the disk, rather than directly stored in the database, it does not use the BFile type.
Example: Create a novel table
Create table xiao_shuo (
Xs_id number (10) primary key, -- Novel number
Xs_name varchar2 (5), -- Novel name
Xs_contenct clob, -- Novel content
Xs_fen_mian blob -- novel cover (image)
);
The second type is implemented using the Parent and Child tables, such as the novel table (novel ID, novel title), content table (novel ID, number of rows, row content ), the row content is varchar (4000 ). This indicates that each row can contain a maximum of 4000 characters.

Key question: What is the difference between fixed length type and variable length type?
Answer:
The main difference between the two lies in the storage and query efficiency.
First, let's talk about char -- fixed length type.
For example, specify the name column as char (8 ). When "Zhang San" is saved, the database automatically saves four spaces. When "Zhang Sanfeng" is saved, the database automatically saves two spaces so that each person's name can be 8 characters long, the length is fixed, so it is called "fixed length ". Obviously, when saving information, the fixed length will occupy much disk space because space is saved.
What is the role of the database to store these "redundant" spaces?
That is, after obtaining the length of a field, you can retrieve the data without judging the actual length of each name. This greatly improves the query efficiency.

Next we will talk about varchar2 -- variable length type.
For example, specify the name column as varchar2 (8 ). When "Zhang San" and "Zhang Sanfeng" are saved, the database only saves the data and does not automatically add spaces. The names of two people are 4 and 6, respectively, and the length is changed, so it is called "getting longer ". In this way, no disk space is occupied.
However, when querying, the length of each person's name is different. You must first judge and then retrieve the data. Therefore, the query efficiency is lower than that of the char type.

Conclusion: The relationship between char and varchar is the relationship between space and time. char changes the space for time, sacrifices the disk space, but returns the query time.

For Oracle, there is a deeper difference.
For example, if you use varchar (4000) for employee "Remarks ). Some employees have very few remarks, and may add a lot of remarks later. Because the varchar type does not store excessive free space between records, it will reference the record's "Row migration", resulting in disk fragmentation, thus reducing query efficiency. The char type is occupied by spaces and does not reference the redistribution of disk space. Fragments are not referenced during project usage. For example, users often modify QQ signatures, group profiles, and other information. Using char is better than using varchar.

The following is the definition of data types in Oracle system files:

Subtype FLOAT is NUMBER; -- NUMBER (126)
Subtype REAL is FLOAT; -- FLOAT (63)
Subtype "double precision" is FLOAT;
Subtype INTEGER is NUMBER (38,0 );
Subtype INT is INTEGER;
Subtype SMALLINT is NUMBER (38,0 );
Subtype DECIMAL is NUMBER (38,0 );
Subtype NUMERIC is DECIMAL;
Subtype DEC is DECIMAL;

Subtype BINARY_INTEGER is INTEGER range '-2147483647'. 2147483647;
Subtype NATURAL is BINARY_INTEGER range 0 .. 2147483647;
Subtype natura1is NATURAL not null;
Subtype POSITIVE is BINARY_INTEGER range 2147483647;
Subtype POSITIVEN is POSITIVE not null;
Subtype SIGNTYPE is BINARY_INTEGER range '-1'... 1; -- for SIGN functions

Type VARCHAR2 is NEW CHAR_BASE;

Subtype VARCHAR is VARCHAR2;
Subtype STRING is VARCHAR2;

Subtype LONG is VARCHAR2 (32760 );

Subtype RAW is VARCHAR2;
Subtype "long raw" is RAW (32760 );

Subtype ROWID is VARCHAR2 (256 );

-- Ansi fixed-length char
-- Define synonyms for CHAR and CHARN.
Subtype CHAR is VARCHAR2;
Subtype CHARACTER is CHAR;

Type MLSLABEL is new CHAR_BASE;