PL and SQL number type data
- 2020-05-27 07:25:45
- OfStack
number( < p > , < s > )
Precision p ranges from 1 to 38
The significant bit s ranges from -84 to 127
Highest integer number = p-s
s positive Numbers, starting at 4 rounds 5 to the right of the decimal point
s negative number, starting at 4 rounds 5 to the left of the decimal point
s is 0 or not specified, 4 rounds 5 to the nearest integer
When p is less than s, it means that the number is less than 1 in absolute value, and the first s-p bits starting from the right of the decimal point must be 0, leaving the s decimal.
--num_test start------------------------------------------------
Connected to Oracle9i Enterprise Edition Release 9.0.1.1.1
Connected as aspire
SQL
>
SQL
>
SET linesize 1000;
SQL
>
CREATE TABLE hjm_num_test
2 (a NUMBER,
3 b NUMBER(5,2),
4 c NUMBER(5,-2),
5 d NUMBER(5,0),
6 e NUMBER(5),
7 f NUMBER(2,5));
Table created
SQL
>
INSERT INTO hjm_num_test
2 (a,b,c,d,e,f) VALUES(123.3333,123.3333,123.3333,123.3333,123.3333,-0.0003);
1 row inserted
SQL
>
INSERT INTO hjm_num_test
2 (a,b,c,d,e,f) VALUES(197.9333,197.9333,197.9333,197.9333,197.9333,0.00012567);
1 row inserted
SQL > COMMIT;
Commit complete
SQL > SELECT * FROM hjm_num_test;
A B C D E F 1 123.3333 123.33 100 123 123 -0.00030 2 197.9333 197.93 200 198 198 0.00013
(shown in pl/sql dev sql window)
--num_test end--------------------------------------------------------
It seems that the result is correct, but now there is a problem, when I don't run pl/sql dev window, I run in isqlplus, the result is a little bit different, notice, the F column in line 1:
A B C D E F 123.3333 123.33 100 123 123 -.0003 197.9333 197.93 200 198 198 .00013
(shown in isqlplus)
It leaves out the zero at the end! The same effect was tested under sqlplus.
But my intention is to keep the 0, because number(2,5), after all, specifies that the significant bit is 5 bits.