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.


Related articles: