Oracle number type precision, scale range ____oracle

 oracle   number  type precision, scale range  

Number (p,s)

P:1---38
S: -84---127

valid digits   : From the left of the first number not  0 , to the end of all numbers, the decimal point and minus sign are not counted in the number of significant digits. &NBSP

p>0 ,  s   3  case:  
1.  S>0&NBSP

to the right  s  bit of the decimal point and rounded   . Then verify that the valid digits are  <= p  ;  

ZWF. Yudong>create table T_n (ID number (5,2));  
table created. 
  --  before the decimal point, you can only have  3   bit, the number of digits after the decimal point can be as many   as possible;

ZWF. Yudong>insert into t_n values (123.45);
1 row created.

ZWF. Yudong>insert into t_n values (123.455);
1 row created.

ZWF. Yudong>insert into t_n values (12.345);
1 row created.

ZWF. Yudong>insert into t_n values (1.234);
1 row created.

ZWF. Yudong>insert into T_n values (. 001);
1 row created.

ZWF. Yudong>select * from T_n;
Id
----------
123.45
123.46

12.35

1.23

0.00

5 rows selected.

ZWF.   Yudong>insert into t_n values (1234.5678); --Effective bit 4 + 2 > 5
INSERT into t_n values (1234.5678)
*
ERROR at line 1:
Ora-01438:value larger than specified precision for this column

ZWF.   Yudong>insert into t_n values (12345); --Effective bit 5 + 2 > 5
INSERT into t_n values (12345)
*
ERROR at line 1:
Ora-01438:value larger than specified precision for this column

if s > P has at least s-p 0 padding to the right of the decimal point。

ZWF. Yudong>create table T_n (ID number (4,5));
Table created.

ZWF. Yudong>insert into t_n values (1);
INSERT into t_n values (1)
*
ERROR at line 1:
Ora-01438:value larger than specified precision for this column


ZWF.   Yudong>insert into T_n values (. 1); --0.10000, the effective bit is 5 > 4
INSERT into T_n values (. 1)
*
ERROR at line 1:
Ora-01438:value larger than specified precision for this column


ZWF.   Yudong>insert into t_n values (1.01); --1.01000, the effective bit is 6 > 4
INSERT into t_n values (1.01)
*
ERROR at line 1:
Ora-01438:value larger than specified precision for this column


ZWF. Yudong>insert into t_n values (. 01);
1 row created.

ZWF. Yudong>insert into T_n values (. 001);
1 row created.
ZWF. Yudong>insert into T_n values (. 0001);
1 row created.

ZWF. Yudong>insert into T_n values (. 00001);
1 row created.

ZWF. Yudong>insert into T_n values (. 00000 6);
1 row created.

ZWF.   Yudong>insert into T_n values (. 000000 1); --over scale storage 0
1 row created.

ZWF. Yudong>select * from T_n;
Id
-------------
0.01000
0.00100

0.00010
0.00001
0.00001
0.00000

6 rows selected.

2.s<0

Accurate to the left of the decimal point and rounded。 And thenverify that the effective digit is <= p + |s|

ZWF. Yudong>create table T_n (ID number (5,-2));
Table created.

ZWF. Yudong>insert into t_n values (123);
1 row created.

ZWF. Yudong>insert into t_n values (1234);
1 row created.

ZWF. Yudong>insert into t_n values (12345);
1 row created.

ZWF. Yudong>insert into t_n values (123456);
1 row created.

ZWF. Yudong>insert into t_n values (1234567);
1 row created.

ZWF. Yudong>insert into t_n values (12);
1 row created.

ZWF. Yudong>insert into t_n values (1);
1 row created.

ZWF. Yudong>insert into T_n values (. 1);
1 row created.

ZWF. Yudong>insert into t_n values (1234567.6789);
1 row created.

ZWF. Yudong>select * from T_n;
Id
------------
100
1200
12300
123500
1234600
0
0
0
1234600

9 rows selected.

ZWF. Yudong>insert into t_n values (12345678);
INSERT into t_n values (12345678)
*
ERROR at line 1:
Ora-01438:value larger than specified precision for this column

3.s=0 represents an integer

Number (P): equivalent to Number (p,0) for specifying integers

Number: Does not specify the number of P, s, which is used to represent floating-point numbers, whose precision and scale are the maximum values that Oracle can support

Summarize:

In the case of p < s

1. can only be used to store decimals greater than 0 less than 1 .
2. The number of 0 immediately following the decimal point is at least s-p, otherwise it cannot be inserted properly.
3. P is used to specify the maximum number of significant digits after the decimal point. Of course, it does not include the number of 0 immediately following the decimal point.
4. S is used to limit the number of digits after the decimal point "and, of course, it includes the immediately following 0 decimal points."

In the case of p > S

Before the decimal point can be inserted up to: p-s number, but the number after the decimal point can be arbitrary length

Reprint Address: http://blog.csdn.net/a9529lty/article/details/6105269