Oracle --- number data type, oraclenumber type

Oracle --- number data type, oraclenumber type

NUMBER(Precision, scale)

Actual value	Data Type	Storage Value
1234567.89	Number	1234567.89
1234567.89	Number (8)	1234567
1234567.89	Number (6)	Error
1234567.89	Number (9, 1)	1234567.9
1234567.89	Number (9, 3)	Error
1234567.89	Number (7,2)	Error
1234567.89	Number (5,-2)	1234600
1234511.89	Number (5,-2)	1234500
1234567.89	Number (5,-4)	1230000
1234567.89	Number (*, 1)	1234567.9
0.012	Number (2, 3)	0.012
0.23	Number (2, 3)	Error

8.Scale can also be described as follows about precision.

The precision (p) and Scale (s) of the number of points follow the following rules:

1) when the length of an integer is greater than p-s, Oracle Reports an error.

2) when the length of the fractional part of A number is greater than s, Oracle will round.

3) When s (scale) is negative, Oracle rounds the s number on the left of the decimal point.

4) When s> p, p indicates the maximum number of digits (s) to the left after the decimal point. If p is greater than p, Oracle Reports an error. The number (s) to the right after the decimal point is rounded

Difference from int

Oracle does not have the int type. To be compatible with other databases, the int type is added as a subset of the number type.
Int type can only store integers;
Number can be a floating point or an integer;
Number (8, 1) stores a floating point number with a decimal point of 1 and a total length of 8. If the number of decimal places is insufficient, use 0 to complete it;
Number (8) refers to the integer whose total length is 8;
Int is equivalent to number (22) and the total storage length is an integer of 22.

NUMBER subclass

A) oracle does not have the int type. To be compatible with other databases, the int type is added as a subset of the number type.

B) The int type can only store integers. The number can store floating-point numbers or integers.

C) when creating a table in an oracle database, decimal and numeric do not contain precision. oracle will automatically process the table as an INTEGER. oracle will automatically process the table as a number with precision.

D) Oracle uses only NUMBER (m, n) to represent any complex digital data.

E) decimal, numeric, int and so on are all data types of SQL, DB2, and other databases. ORACLE introduces them for compatibility. But in fact, it is stored in ORACLE as NUMBER.

Number auto-Increment

Different from mysql, you can set auto-increment directly. Sequence and trigger must be created. Example:

First, create a table:

CREATE TABLE example(ID Number(4) NOT NULL PRIMARY KEY,NAME VARCHAR(25));

To customize a sequence ):

Create sequence example_sequenceINCREMENT BY 1 -- add several start with 1 each time -- count NOMAXVALUE from 1 -- do not set the maximum value NOCYCLE -- accumulate all the time, do not cycle NOCACHE -- do not CREATE a buffer

Create another trigger:

CREATE OR REPLACE TRIGGER example_tBEFORE INSERT ON exampleFOR EACH ROWBEGINSELECT example_sequence.nextval INTO :new.id FROM dual; END;

Test, insert data

Insert into example (name) values ('zhang san'); insert into example (id, name) values (111, 'Li si ');

Enter the query statement:

select t.* from EXAMPLE t

The result is:

Id	Name
1	Zhang San
111	Li Si