Problem with ORACLE number type scale 0

Today I encountered a very interesting problem with the number type scale, and I use a simple test case to illustrate this case. If there is a test table, there is a field type number, I insert the following two data

CREATE TABLE TEST

(

VARCHAR (12),

QTY number

)

 into TEST

SELECT ' M '  from UNION  All

SELECT ' C '  from DUAL;

COMMIT;

Directly query the table test, found that the Qty field value is 1,

When using the following SQL statement, the value of Sum_qty is also 1

However, if you get the value in the cursor, you will find that the value of the field qty is the original value. 99999999999999999

DECLARE CURSOR C_test

Is

SELECT SUM  as  from TEST

GROUP  by Category;

C_row C_test%rowtype;

begin

 for  in C_test loop

Dbms_output.put_line (' The result is ' | | c_row. Sum_qty);

end Loop;

End;

The result is. 99999999999999999

The result is 12

Why is there such a strange phenomenon? In fact, we encountered this case is reversed in this order, and finally found that the value inserted is 0.999999999. Of course, this process is very tangled. It's far from as easy as it is in our example. To explain this, number (p, s) declares a fixed-point number p (precision) as the precision, s (scale) represents the numbers to the right of the decimal point, and the maximum precision value of 38,scale is 84 to 127. Number (p) means that the declaration of an integer is equivalent to numbers (p, 0), and if the p and S,number types are not specified, its default precision value is 38, and the default scale value is 0. So in the SELECT statement, a value of. 99999999999999999 is displayed as 1, but in the cursor it gets the true value of the field, without conversion. So there was this bizarre problem. Although it was very simple after the fact, but did not understand the situation, I feel very strange, very puzzled!

The accuracy (p) and scale (s) of the attached fixed-point number are also subject to the following rules:
? Oracle will error when the length of the integer part of a number > P-s
? When the length of a number of decimal parts is > S, Oracle rounds.
? When s (scale) is negative, Oracle rounds the s number to the left of the decimal point.
? When s > P, p indicates the number of digits to the left of the S bit after the decimal point, and if the Oracle error is greater than P, the number of s bits to the right after the decimal point is rounded

Problem with ORACLE number type scale 0