[20150503]關於oracle的number型別.txt

[20150503]關於oracle的number型別.txt

--節前的事情,別人建表使用number型別,本來想定義成number(10,2),結果少輸入0,變成number(1,2).
--在我的記憶裡,好像前面的數值應該大於後面的精度的,沒想到這樣竟然可以透過,自己感到很奇怪!
--測試下來,才知道自己oracle基本的東西都不是很清楚.

1.首先提到我以前寫的一篇blog:
[20140823]在sqlplus使用copy注意.txt
http://blog.itpub.net/267265/viewspace-1257036/

在這篇blog中我提到定義number型別最好包含精度和小數點位數.

2.接著看看上面的定義會如何?

--看看oracle 文件:

NUMBER Data Type
The NUMBER data type stores zero as well as positive and negative fixed numbers with
absolute values from 1.0 x 10^-130 to but not including 1.0 x 10^126
. If you specify an arithmetic expression whose value has an absolute value greater than or equal to 1.0 x 10^126
, then Oracle returns an error. Each NUMBER value requires from 1 to 22 bytes.
Specify a fixed-point number using the following form:
NUMBER(p,s)

■ p is the precision, or the maximum number of significant decimal digits, where the
most significant digit is the left-most nonzero digit, and the least significant digit is
the right-most known digit. Oracle guarantees the portability of numbers with
precision of up to 20 base-100 digits, which is equivalent to 39 or 40 decimal digits
depending on the position of the decimal point.

■ s is the scale, or the number of digits from the decimal point to the least significant
digit. The scale can range from -84 to 127.

– Positive scale is the number of significant digits to the right of the decimal
point to and including the least significant digit.

– Negative scale is the number of significant digits to the left of the decimal
point, to but not including the least significant digit. For negative scale the
least significant digit is on the left side of the decimal point, because the actual
data is rounded to the specified number of places to the left of the decimal
point. For example, a specification of (10,-2) means to round to hundreds.

Scale can be greater than precision, most commonly when e notation is used. When
scale is greater than precision, the precision specifies the maximum number of
significant digits to the right of the decimal point. For example, a column defined as
NUMBER(4,5) requires a zero for the first digit after the decimal point and rounds all
values past the fifth digit after the decimal point.

--文件明確提到Scale can be greater than precision.

It is good practice to specify the scale and precision of a fixed-point number column
for extra integrity checking on input. Specifying scale and precision does not force all
values to a fixed length. If a value exceeds the precision, then Oracle returns an error. If
a value exceeds the scale, then Oracle rounds it.
Specify an integer using the following form:
NUMBER(p)

This represents a fixed-point number with precision p and scale 0 and is equivalent to
NUMBER(p,0).
Specify a floating-point number using the following form:
NUMBER
The absence of precision and scale designators specifies the maximum range and
precision for an Oracle number.

--測試:
SCOTT@test01p> create table t1 ( a number(1,2));
Table created.

SCOTT@test01p> insert into t1 values(0.10);
insert into t1 values(0.10)
                      *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SCOTT@test01p> insert into t1 values(0.01);
1 row created.

SCOTT@test01p> insert into t1 values(0.091);
1 row created.

SCOTT@test01p> select * from t1;
         A
----------
       .01
       .09

--很明顯這樣定義僅僅能插入0.01-0.09(當然沒有包括負數),再過來看就很容易裡面前面的定義.精度1表示僅僅最後1位有數值(把值*100).
--假如我定義number(2,2)表示的範圍就是0.01-0.99 (不包括負數).

SCOTT@test01p> create table t2 ( a number(2,2));
Table created.

SCOTT@test01p> insert into t2 values(0.99);
1 row created.

SCOTT@test01p> insert into t2 values(0.01);
1 row created.

SCOTT@test01p> insert into t2 values(1.00);
insert into t2 values(1.00)
                      *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SCOTT@test01p> insert into t2 values(-0.99);
1 row created.

SCOTT@test01p> select * from t2;
         A
----------
       .99
       .01
      -.99