What is the difference between precision and scale?

What is the difference between precision and scale in Oracle? In tutorials 开发者_如何转开发they usually leave scale empty and set precision to 6 when creating a primary key.

What do precision and scale stand for?

---

Precision 4, scale 2: 99.99

Precision 10, scale 0: 9999999999

Precision 8, scale 3: 99999.999

Precision 5, scale -3: 99999000

---

Precision is the total number of digits, can be between 1 and 38.
Scale is the number of digits after the decimal point, may also be set as negative for rounding.

Example:
NUMBER(7,5): 12.12345
NUMBER(5,0): 12345

More details on the ORACLE website:
https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1832

---

Precision is the number of significant digits. Oracle guarantees the portability of numbers with precision ranging from 1 to 38.

Scale is the number of digits to the right (positive) or left (negative) of the decimal point. The scale can range from -84 to 127.

In your case, ID with precision 6 means it won't accept a number with 7 or more significant digits.

Reference:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1832

That page also has some examples that will make you understand precision and scale.

---

Maybe more clear:

Note that precision is the total number of digits, scale included

NUMBER(Precision,Scale)

Precision 8, scale 3 : 87654.321

Precision 5, scale 3 : 54.321

Precision 5, scale 1 : 5432.1

Precision 5, scale 0 : 54321

Precision 5, scale -1: 54320

Precision 5, scale -3: 54000

---

Scale is the number of digit after the decimal point (or colon depending your locale)

Precision is the total number of significant digits

---

precision: Its the total number of digits before or after the radix point. EX: 123.456 here precision is 6.

Scale: Its the total number of digits after the radix point. EX: 123.456 here Scaleis 3

---

• Precision: Total length of the data.
• Scale

  • Scale>0

    • Precision=Integer.Length+Decimal.Length
    • The number of digits after the decimal point.

  • Scale<0

    • Precision=Integer.Length

    If you specify a negative scale, Oracle Database rounds the actual data to the specified number of places to the left of the decimal point. Reference to Oracle

---

If value is 9999.988 and Precision 4, scale 2 then it means 9999(it represents precision).99(scale is 2 so .988 is rounded to .99)

If value is 9999.9887 and precision is 4, scale is 2 then it means 9999.99