Oracle NUMBER 的比例如何大于精度？

How can an Oracle NUMBER have a Scale larger than the Precision?

提问by aiGuru

The documentation states: "Precision can range from 1 to 38. Scale can range from -84 to 127".

文档指出：“精度范围可以从 1 到 38。比例范围可以从 -84 到 127”。

How can the scale be larger than the precision? Shouldn't the Scale range from -38 to 38?

规模怎么可能大于精度？比例范围不应该从 -38 到 38 吗？

回答by Gary Myers

The question could be why not ? Try the following SQL.

问题可能是为什么不呢？尝试以下 SQL。

select cast(0.0001 as number(2,5)) num, 
       to_char(cast(0.0001 as number(2,5))) cnum,
       dump(cast(0.0001 as number(2,5))) dmp
  from dual

What you see is that you can hold small numbers is that sort of structure It might not be required very often, but I'm sure somewhere there is someone who is storing very precise but very small numbers.

你看到的是你可以保存小数字是那种结构它可能不需要经常使用，但我敢肯定有人在某个地方存储非常精确但非常小的数字。

回答by BaSsGaz

According to Oracle Documentation:

根据 Oracle 文档：

Scale can be greater than precision, most commonly when ex notation is used (wherein decimal part can be so great). 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.

比例可以大于精度，最常见的是使用 ex 表示法时（其中小数部分可以很大）。当 scale 大于 precision 时，precision 指定小数点右侧的最大有效位数。例如，定义为 的列NUMBER(4,5)要求小数点后的第一位数字为零，并将所有值四舍五入到小数点后的第五位数字之后。

Here's how I see it :

这是我的看法：

• When Precisionis greater than Scale (e.g NUMBER(8,5)), no problem, this is straightforward. Precisionmeans the number will have a total of 8 digits, 5 of which are in the fractional part (.→), so the integer part (←.) will have 3 digits. This is easy.

• When you see that Precisionis smaller than Scale(e.g NUMBER(2, 5)), this means 3 things :

  • The number will not have any integer part, only fractional part. So the 0 in the integer part is not counted in the calculations, you say .12345 not 0.12345. In fact, if you specify just 1 digit in the integer part, it will always return an error.
  • The Scalerepresents the total number of digits in the fractional part that the number will have. 5 in this case. So it can be .12345 or .00098 but no more than 5 digits in total.
  • The fractional part is divided into 2 parts, significant numbers and zeros. Significant numbers are specified by Precision, and minimum number of zeros equals (Scale- Precision). Example :

  

  here The number will must have a minimum of 3 zeros in the fractional part. followed by 2 significant numbers (could have a zero as well). So 3 zeros + 2 significant numbers = 5 which is the Scalenumber.

• 当Precision大于 Scale（例如NUMBER(8,5)）时，没问题，这很简单。Precision表示数字一共有8位，其中5位在小数部分(.→)，所以整数部分(←.)有3位。这很简单。

• 当您看到它Precision小于Scale(eg NUMBER(2, 5)) 时，这意味着 3 件事：

  • 该数字不会有任何整数部分，只有小数部分。所以整数部分中的 0 不计入计算中，你说 0.12345 不是 0.12345。事实上，如果在整数部分只指定 1 位数字，它总是会返回错误。
  • 的Scale表示的，所述数量将有小数部分的总位数。5 在这种情况下。所以它可以是 .12345 或 .00098 但总共不超过 5 位数字。
  • 小数部分分为 2 部分，有效数字和零。有效数字由 指定Precision，并且最小零数等于 ( Scale- Precision)。例子 ：

  

  这里的数字在小数部分必须至少有 3 个零。后跟 2 个有效数字（也可以是零）。所以 3 个零 + 2 个有效数字 = 5，这是Scale数字。

In brief, when you see for example NUMBER(6,9), this tells us that the fractional part will have 9 digits in total, starting by an obligatory 3 zeros and followed by 6 digits.

简而言之，当您看到例如 时NUMBER(6,9)，这告诉我们小数部分总共有 9 位数字，从强制性的 3 个零开始，然后是 6 位数字。

Here are some examples :

这里有些例子 ：

SELECT CAST(.0000123 AS NUMBER(6,9)) FROM dual;   -- prints: 0.0000123; .000|012300
SELECT CAST(.000012345 AS NUMBER(6,9)) FROM dual; -- prints: 0.0000123; .000|012345
SELECT CAST(.123456 AS NUMBER(3,4)) FROM dual;    -- ERROR! must have a 1 zero (4-3=1)
SELECT CAST(.013579 AS NUMBER(3,4)) FROM dual;    -- prints: 0.0136; max 4 digits, .013579 rounded to .0136

回答by aiGuru

Thanks to everyone for the answers. It looks like the precision is the number of significant digits.

感谢大家的回答。看起来精度是有效数字的数量。

 select cast(0.000123 as number(2,5)) from dual

results in:

结果是：

.00012

Where

在哪里

 select cast(0.00123 as number(2,5)) from dual

and

和

 select cast(0.000999 as number(2,5)) from dual

both result in:

两者都导致：

ORA-01438: value larger than specified precision allowed for this column

the 2nd one due to rounding.

由于四舍五入，第二个。

回答by KPB

According to Oracle Documentation:

根据 Oracle 文档：

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.

比例可以大于精度，最常见的是使用 e 符号时。当 scale 大于 precision 时，precision 指定小数点右侧的最大有效位数。例如，定义为 NUMBER(4,5) 的列要求小数点后的第一个数字为零，并将所有值四舍五入到小数点后的第五个数字之后。

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.

指定定点数列的小数位数和精度以对输入进行额外的完整性检查是一种很好的做法。指定小数位数和精度不会将所有值强制为固定长度。如果值超过精度，则 Oracle 返回错误。如果某个值超过了比例，则 Oracle 将对其进行四舍五入。

回答by Lorenzo

The case where Scale is larger than Precision could be summarized this way:

Scale 大于 Precision 的情况可以这样总结：

Number of digits on the right of decimal point = Scale

小数点右边的位数=比例

Minimum number of zeroes right of decimal = Scale - Precision

小数点右边的最小零数 = 比例 - 精度

--this will work 
select cast(0.123456 as number(5,5)) from dual;

returns 0.12346

返回 0.12346

-- but this
select cast(0.123456 as number(2,5)) from dual;
--will return "ORA-1438 value too large".  
--It will not return err with at least 5-2 = 3 zeroes:
select cast(0.000123456 as number(2,5)) from dual;

returns 0.00012

返回 0.00012

-- and of course this will work too
select cast(0.0000123456 as number(2,5)) from dual;

returning 0.00001

返回 0.00001

回答by Tobias

Hmm as I understand the reference the precision is the count of digits.
maximum precision of 126 binary digits, which is roughly equivalent to 38 decimal digits

嗯，据我所知，参考的精度是位数。
maximum precision of 126 binary digits, which is roughly equivalent to 38 decimal digits

In oracle you have type NUMBER(precision,scale) where precision is total number of digits and scale is number of digits right of decimal point. Scale can be omitted, but it means zero. Precision can be unspecified (use i.e. NUMBER(*,10)) - this means total number of digits is as needed, but there are 10 digits right

在 oracle 中，您输入 NUMBER(precision,scale) ，其中 precision 是总位数，scale 是小数点右侧的位数。比例可以省略，但它意味着零。精度可以是未指定的（使用即 NUMBER(*,10)） - 这意味着总位数是需要的，但有 10 位是正确的

If the scale is less than zero, the value will be rounded to scaledigits left the decimal point.
I think that if you reserve more numbers right of the decimal point than there can be in the whole number, this means something like 0.00000000123456 but I am not 100% sure.

如果比例小于零，则该值将四舍五入为scale小数点左侧的数字。
我认为，如果您保留的小数点右边的数字比整数中的数字多，这意味着 0.00000000123456 之类的东西，但我不是 100% 确定。