Difference between decimal and Number in Oracle

Source: Internet
Author: User
Oracle only supports the decimal type in syntax, but it is actually the number type at the underlying level. The decimal type is supported to move data from the Oracle database to other

Oracle only supports the decimal type in syntax, but it is actually the number type at the underlying level. The decimal type is supported to move data from the Oracle database to other

I. DECIMAL Type Details

Oracle only supports the decimal type in syntax, but it is actually the number type at the underlying layer. The decimal type is supported to move data from the Oracle database to other databases (such as DB2 ).

Since decimal is of the number type at the bottom layer of Oracle, it is enough to use the number type. If you need to convert this field type to the char type, you can use the to_char function to convert it.

The decimal type should basically be a number, because the data type in oracle only has the number type, which can be processed as the number type. Decimal (8, 2) indicates the total length of a number of 8 digits, and the decimal part is two digits. The value range is 8 digits. It is precise to the second decimal point and rounded to the nearest decimal point. That is, there are 6 integers and two decimal places. That is, the maximum value can be 999999.99, which can be 2 decimal places. In Oracle, you can use the to_char function to convert numbers to character types.

Ii. NUMBER Type Details

In Oracle, the Number type can be used to store 0, positive, negative, or floating point numbers. The data range that can be expressed is

1.0*10 (-130) -- 9. 9... 9*10 (125) {38 9 s with 88 0}

When the value of the mathematical expression in Oracle is greater than or equal to 1.0*10 (126), Oracle Reports an error.

The data declaration of Number is as follows:

Indicates

Function

Description

Number (p, s)

Declare a fixed number of points

P (precision) is the precision, s (scale) indicates the number of digits on the right of the decimal point, the maximum precision is 38, the range of scale is-84 to 127

Number (p)

Declare an integer

Equivalent to Number (p, 0)

Number

Declare a floating point number

The precision is 38. Note that the scale value is not applied. That is to say, scale cannot be simply understood as 0 or another number.

The precision (p) and Scale (s) of the number of points follow the following rules:

When the length of an integer is greater than p-s, Oracle Reports an error.

When the length of the fractional part of A number is greater than s, Oracle will round.

When s (scale) is a negative number, Oracle rounds the s number on the left of the decimal point.

When s> p, p indicates the maximum number of digits (s) to the left after the decimal point. If p is greater than p, Oracle Reports an error. The number (s) to the right after the decimal point is rounded.

NUMBER Type Details

Oracle number datatype Syntax: NUMBER [(precision [, scale])]
Short for: precision --> p
Scale --> s

NUMBER (p, s)
Range: 1 <= p <= 38,-84 <= s <= 127
Data storage range:-1.0e-130 <= number value <1.0e + 126
Saved in the machine range: 1 ~ 22 bytes

Valid bits: the number of digits from the first digit on the left that is not 0.
S:
S> 0
Accurate to the second place to the right of the decimal point, and rounded. Then, check whether the valid bit is <= p.
S <0
It is accurate to the second place on the left of the decimal point, and rounded up. Then, check whether the valid bit is <= p + | s |.
S = 0
In this case, NUMBER indicates an integer.

Eg:
Actual Data Specified As Stored
----------------------------------------
123.89 NUMBER 123.89
123.89 NUMBER (3) 124
123.89 NUMBER (123.89)
123.89 NUMBER (6, 1) 123.9
123.89 NUMBER () exceeds precision (valid bits: 5, 5> 4)
123.89 NUMBER (6,-2) 100
. 01234 NUMBER (01234). (effective bit: 4)
. 00012 NUMBER (00012 ).
. 000127 NUMBER (00013 ).
. 0000012 NUMBER (0000012 ).
. 00000123 NUMBER (0000012 ).
1.2e-4 NUMBER (2, 5) 0.00012
1.2e-5 NUMBER (0.00001)
123.2564 NUMBER 123.2564
1234.9876 NUMBER (1234.99)
12345.12345 NUMBER () Error (valid bit: 5 + 2> 6)
1234.9876 NUMBER (6) 1235 (s does not indicate s = 0)
12345.345 NUMBER (5,-2) 12300
1234567 NUMBER (5,-2) 1234600
12345678 NUMBER (5,-2) Error (valid 8> 7)
123456789 NUMBER (5,-4) 123460000
1234567890 NUMBER (5,-4) Error (valid value: 10> 9)
12345.58 NUMBER (*, 1) 12345.6
0.1 NUMBER (0.10000) Error (, valid bit: 5> 4)
0.01234567 NUMBER (0.01235)

0.09999 NUMBER (0.09999)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.