Difference between decimal and Number in Oracle

Differences between decimal and Number in Oracle 1. 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 an Oracle database to another database (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 and negative fixed points or floating point numbers. The data range can be 1.0*10 (-130) -- 9. 9... 9*10 (125) {38 9 followed by 88 0} digits. 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: it indicates that Number (p, s) declares that precision is set to p (precision), and s (scale) indicates the Number of digits on the right of the decimal point, the maximum precision is 38. The value range of scale is-84 to 127 Number (p). Declare an integer equivalent to Number (p, 0) Number. Declare a floating point. 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 rules below: when the length of the integer part of A number is greater than p-s, oracle will report an error when the length of a decimal part 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])] Abbreviation: precision --> p scale --> s NUMBER (p, s) range: 1 <= p <= 38,-84 <= s <= 127 stored data range:-1.0e-130 <= number value <1.0e + 126 stored 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 is accurate to the second place on the right of the decimal point, and rounded. Then, check whether the valid bit is <= p. S <0 is accurate to the second place on the left of the decimal point, and is rounded to five. Then, check whether the valid bit is <= p + | s |. S = 0 at this time, NUMBER indicates an integer. Eg: Actual Data Specified As Stored As random 123.89 NUMBER 123.89123.89 NUMBER (3) 124123.89 NUMBER (6, 2) 123.89123.89 NUMBER (6, 1) 123.9123.89 NUMBER (4, 2) exceeds precision (valid bit: 5, 5> 4) 123.89 NUMBER (6,-2) 100.01234 NUMBER (4, 5 ). 01234 (effective bit: 4 ). 00012 NUMBER (4, 5 ). 00012.000127 NUMBER (4, 5 ). 00013.0000012 NUMBER (2, 7 ). 0000012.00000123 NUMBER (2, 7 ). 00000121.2e-4 NUMBER () 0.000121.2e-5 NUMBER () 0.20.00003.2564 NUMBER 123.25641234.9876 NUMBER () Limit NUMBER () Error (valid bit: 5 + 2> 6) 1234.9876 NUMBER (6) 1235 (s does not indicate s = 0) 12345.345 NUMBER (5,-2) 123001234567 NUMBER (5,-2) 123460012345678 NUMBER (5,-2) error (valid bits 8> 7) 123456789 NUMBER (5,-4) 1234600001234567890 NUMBER (5,-4) Error (valid bits 10> 9) 12345.58 NUMBER (*, 1) 12345.60.1 NUMBER (0.10000) Error (0.01234567, valid bit: 5> 4) 0.09999 NUMBER () 0.01235000009999 NUMBER () 3. Oracle statements are separated from statements in the project: [SQL] SELECT (CASE WHEN tt1.statistics _ date is not null THEN tt1.statistics _ date ELSE tt2.statistics _ date END) AS statistics_date, NVL (tt1.actuser, 0) AS actuser, NVL (tt2.new _ user, 0) AS new_user FROM (SELECT t. statistics_date, SUM (t. actuser) AS actuser FROM pdt_stat_act_1133_ I t WHERE t. statistics_date like '2014-2013 'and t. statistics_month = '2014-04 'group by t. statistics_date) tt1 full join (SELECT t2.statistics _ date, SUM (t2.new _ user) OVER (order by t2.statistics _ date) AS new_user FROM (SELECT statistics_date AS statistics_date, SUM (new_user) AS new_user FROM pdt_stat_newuser_1133_ I WHERE statistics_date like '2017-2013 'GROUP BY statistics_date) t2) tt2 ON tt1.statistics _ date = tt2.statistics _ date