Oracle data types

1.NUMBER
Number indicates a numeric type
is often defined as number (p,s) Form, where
P represents the total number of digits
s represents the number of digits after the decimal point
2.CHAR
Represents a fixed-length character type
is often defined as a char (n) Form, n indicates the number of bytes occupied
The maximum length is 2000 bytes
3.varchar2
A character type that represents a variable length
The definition format is VARCHAR2 (n) and n indicates the maximum number of bytes that can be consumed
The maximum length is 4000 bytes
4.CHAR and VARCHAR2 types
Represents a string data type used to hold string information in a table, such as name, occupation, address, etc.
Char holds the fixed-length character, that is, the amount of VARCHAR2, which is stored in a variable-length character.
Sort by natural order of characters
Storage encoding for 5.CHAR and VARCHAR2
Default units are bytes and can be specified as characters
char (10) equals char (ten bytes)
Specify the unit as a character: char (20 char), byte
VARCHAR2 (10) is equivalent to VARCHAR2 (ten bytes)
Specify the unit as a character: VARCHAR2 (20 CHAR)
Each English character occupies one byte, and each Chinese character is encoded differently, occupying 2-4 bytes
Zhs16gbk:2 bytes
Utf-8:2-4 bytes
Maximum length of 6.CHAR and VARCHAR2
Cahr Maximum length is 2000 bytes
Save up to 2000 English characters, 1000 characters (GBK)
VARCHAR2 Maximum value is 4000 bytes
Save up to 4,000 English characters, 2000 characters (GBK)
Char can not specify length, default is 1,VARCHAR2 must specify length
7.LONG and CLOB types
LONG:VARCHAR2 long version, storing variable length strings up to 2GB of string data
Long has many limitations:
Each table can have only one long column, cannot be a primary key, cannot be indexed, cannot appear in query criteria ...
CLOB: Storing fixed-length or variable-length strings up to 4GB of string data
Oracle recommends using CLOB instead of long type in development
8.NUMBER (P) denotes integers
Full syntax: Number (Precision,scale)
If scale is not set, the default value is 0, that is, number (p) represents an integer
P represents the total number of digits, with a value of 1-38
Used to store data in a table, such as encoding, age, number of times, etc., recorded in integers
9.NUMBER (p,s) indicates floating-point numbers
Number (Precision,scale)
Precision:number Maximum number length that can be stored (excluding left and right sides of 0)
Scale: The maximum number length to the right of the decimal point, including the 0 on the left
If s is specified but no p is specified, p defaults to 38, for example: column name Number (*,s)
It is often used to make data with decimal place in the amount, grade, etc. of the table.
Variant data type of number: The internal implementation is the name, which can be interpreted as an alias for numbers, and is intended to be compatible with multiple databases and programming languages
NUMERIC (p,s): Fully mapped to number (P,s)
DECIMAL (p,s) or Dec (p,s): Fully mapped to number (P,s)
Integer or int: fully mapped to number (38) type
FLOAT (b): Map to Number type
DOUBLE PRECISION: Map to Number type
REAL: Map to Number type
10.DATE
Data used to define the date time
Length is 7 bytes
The default format is: DD-MON-RR, for example: 11-apr-71
Date types commonly used in Oracle to save dates and times
Date range may be from January 1, 4712 to December 31, 9999 A.D.
The date type is stored in the database in a fixed 7-byte format:
1th byte: Century +100
2nd byte: Year
3rd Byte: Month
4th Byte: Day
5th byte: Hours +1
6th byte: Minute +1
7th byte: Seconds +1
11.TIMESTAMP
Oracle Common Date Types
The difference from date is that not only can the date and time be saved, but also the fractional seconds can be saved, with the highest precision up to NS (nanosecond)
The database is stored internally with 7 or 11 bytes, with a precision greater than 0 with 11 bytes of storage
The format is:
1th byte-7th byte: Same as Date
第8-11 bytes: nanoseconds, with 4 bytes of storage, internal operation type is integral type
12.SYSDATE
Its essence is an Oracle intrinsic function that returns the current system time, accurate to the second
The default display format is DD-MON-RR
13.SYSTIMESTAMP
An intrinsic function that returns the current system date and time, accurate to milliseconds.

Oracle data types