Oracle Number 数据类型及其严格性
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10067945/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle Number data type and its strictness
提问by Justin Cave
I'm very new to oracle but I've been learning about it in class and I've recently come across the numberdata type. I've read the documentation regarding scale and precision (s,p) but I still am not positive regarding the type's proper usage.
我对 oracle 很陌生，但我一直在课堂上学习它，最近我遇到了number数据类型。我已经阅读了有关比例和精度 (s,p) 的文档，但我仍然对类型的正确用法持否定态度。
Let's say I want to store percent values as a decimal. The decimal can be anywhere from 0 to 1 and may have up to 3 numbers following the decimal place.
假设我想将百分比值存储为小数。小数可以是 0 到 1 之间的任何位置，并且小数位后最多可以有 3 个数字。
Some possible decimals to be stored may include:
要存储的一些可能的小数可能包括：
.66
.553
1.00
If I were to make the column NUMBER(4,3)would that work EVEN IF there were only two numbers? See below
如果我要制作该列NUMBER(4,3)，即使只有两个数字也能正常工作吗？见下文
.22
.10
.35
etc...
In short, does the number type require that the EXACT sizes are met? Eg. Would NUMBER(4,3) ABSOLUTELY REQUIRE that the data inserted is 4 numbers, 3 of them falling after the decimal place?
简而言之，数字类型是否要求满足 EXACT 大小？例如。NUMBER(4,3) 是否绝对要求插入的数据是 4 个数字，其中 3 个落在小数点后？
Thanks for the help!
谢谢您的帮助！
回答by Justin Cave
Data types for columns limit the range of values that can be stored. But they don't do anything to force a particular length or precision.
列的数据类型限制了可以存储的值的范围。但是他们不会强制执行特定的长度或精度。
Just like you can store a single character or a NULL in a VARCHAR2(100)column, you can store numbers with less than 3 digits of precision and less than 4 digits of scale. For example, the values 0 and 6.1 are both perfectly valid for a NUMBER(4,3)column. If you insert a value that has too many digits of precision, the value will be silently rounded to the precision specified in the column definition.
就像您可以在VARCHAR2(100)列中存储单个字符或 NULL 一样，您可以存储小于 3 位精度和小于 4 位小数位数的数字。例如，值 0 和 6.1 对于一NUMBER(4,3)列都完全有效。如果您插入的值的精度位数过多，则该值将被自动舍入到列定义中指定的精度。
SQL> create table foo (
2 col1 number(4,3)
3 );
Table created.
SQL> insert into foo values( 9.999 );
1 row created.
SQL> insert into foo values (0);
1 row created.
SQL> insert into foo values( 6.1 );
1 row created.
SQL> insert into foo values( 1.2345 );
1 row created.
SQL> select * from foo;
COL1
----------
9.999
0
6.1
1.235
