I've been trying to understand Oracle database's use of data type = number. I ran across the following link:
我一直在试图理解Oracle数据库对data type = number的使用。我遇到了以下链接:
问汤姆
and I'm having trouble interpreting it. I should mention I don't have Oracle background, but approaching this from Matlab experience. I understand number data type has a precision and scale. What I don't understand is how the exponent is treated here. Is the exponent a separate consideration from precision and scale? Or, is the exponent limited by precision and scale?
我很难解释它。我应该说我没有Oracle的背景，但是从Matlab的经验来看。我知道数字数据类型有精度和规模。我不理解的是指数如何在这里处理。指数是与精度和尺度分开考虑的吗?或者，指数是否受精度和尺度的限制?
Here's my confusion -- the above link starts with:
这是我的困惑——上面的链接以:
Q: I declared the datatype of a column of a table as NUMBER & Presumed it to be Number(38) to be specific. But What I found to be strange that, it is accepting digits beyong 38 digits.ie. 1.111E+125. If the Datatype's precision is 38 and scale can range from -84 to 127 how this value can insert into the column.
问:我将表中某一列的数据类型声明为NUMBER，并假定为NUMBER(38)。但我发现奇怪的是，它接受的数字超过38位。1.111 e + 125。如果数据类型的精度为38，并且比例可以从-84到127，那么该值如何插入到列中。
A: and we said... 1.111e+125 only has 4 digits of precision (number of digits needed to represent the number -- in this case 1111).
和我们说…1.111e+125只有4位精度(表示数字所需的位数——在这种情况下是1111)。
Which seems to indicate the exponent is treated separate from precision and scale. But a few messages down someone else asks:
这似乎表明指数被区别于精度和规模。但也有一些人会问:
Q: When I create a table like: create table test(no number(7)) ; insert into test values(1.00e+25) ; it gives the following error message: ORA-01438: value larger than specified precision allows for this column. Can u pls. explain this.
问:当我创建一个表时，比如:create table test(no number(7));插入测试值(1.00e+25);它给出以下错误消息:ORA-01438:值大于指定的精度，允许此列。你能解释一下吗?
A: you said 7 digits, you gave it 25
A:你说7位，你说25位
which seems to indicate the exponent is limited by the precision. Hoping someone can help me understand how both answers can be correct.
这似乎表明指数受到精度的限制。希望有人能帮助我理解这两个答案是如何正确的。
1 个解决方案
#1
3
number(7) is an integer with (at most) seven digits (in the "normal" decimal representation). Note that fractions are not allowed. Numbers larger than 9,999,999 will not be accepted. This has nothing to do with how many significant digits they have. This is a "business constraint" put in place by you.
number(7)是一个具有(最多)7位数字的整数(在“正常”的十进制表示法中)。注意，分数是不允许的。大于99999999999的号码将不被接受。这与他们有多少位有效数字无关。这是您设置的“业务约束”。
number(7,2) would be a number with seven digits, including two after the decimal point
数字(7,2)是一个有7个数字的数字，包括小数点后的两个数字
number is a floating point number with 38 digits of precision (i.e. 38 significant digits, no matter where they are in relation to the decimal point). The exponent can be anything between -84 and 125. There are no "business contraints" here. You can store as much as Oracle can store (which is 38 digits and the exponent between -84 and 125).
数字是一个浮点数，有38位精度(即38位有效数字，无论它们与小数点的关系在哪里)。指数可以是-84到125之间的任何数。这里没有“商业限制”。您可以存储和Oracle可以存储的一样多(这是38位数字和-84和125之间的指数)。
Note that all these numbers are stored as decimal data (to avoid rounding errors when converting into binary), so "digits" really mean decimal digits (not binary bits).
注意，所有这些数字都存储为十进制数据(以避免在转换为二进制时出现四舍五入错误)，因此“数字”实际上是十进制数字(而不是二进制位)。
#1
3
number(7) is an integer with (at most) seven digits (in the "normal" decimal representation). Note that fractions are not allowed. Numbers larger than 9,999,999 will not be accepted. This has nothing to do with how many significant digits they have. This is a "business constraint" put in place by you.
number(7)是一个具有(最多)7位数字的整数(在“正常”的十进制表示法中)。注意，分数是不允许的。大于99999999999的号码将不被接受。这与他们有多少位有效数字无关。这是您设置的“业务约束”。
number(7,2) would be a number with seven digits, including two after the decimal point
数字(7,2)是一个有7个数字的数字，包括小数点后的两个数字
number is a floating point number with 38 digits of precision (i.e. 38 significant digits, no matter where they are in relation to the decimal point). The exponent can be anything between -84 and 125. There are no "business contraints" here. You can store as much as Oracle can store (which is 38 digits and the exponent between -84 and 125).
数字是一个浮点数，有38位精度(即38位有效数字，无论它们与小数点的关系在哪里)。指数可以是-84到125之间的任何数。这里没有“商业限制”。您可以存储和Oracle可以存储的一样多(这是38位数字和-84和125之间的指数)。
Note that all these numbers are stored as decimal data (to avoid rounding errors when converting into binary), so "digits" really mean decimal digits (not binary bits).
注意，所有这些数字都存储为十进制数据(以避免在转换为二进制时出现四舍五入错误)，因此“数字”实际上是十进制数字(而不是二进制位)。