oracle数字类型（Oraclenumerictype）

oracle数字类型（Oracle numeric type）
Analysis of Oracle basic data type storage format (two) -- digital type
This article mainly describes the data of type NUMBER and how to store it in the database.
The NUMBER type of Oracle is composed of three parts, the three parts are the highest bit, the data part and the symbol bit. The negative numbers contain the sign bits, and the positive numbers will not include the symbol bits. In addition, the numerical 0 is special, it contains only one numeric value, the highest position is 80, and there is no data part.
The highest position of the positive number is greater than 80, and the highest position of the negative number is less than 80. The highest position of a positive number is a bit, then the highest position is C1, and the hundreds and tens of bits are C2, C3, percentile and extreme order are C0 and BF respectively. The highest position of a negative number is a bit, the highest position is 3E, 100 bits and ten thousand bits are 3D, 3C, percentile and extreme bit are 3F and 40 respectively.
Each bit represents 2 digits in the data section. The two digits may be from 0 to 99, and if the data itself is positive, it is expressed in binary 1 to 64, and if the data itself is negative, use binary 65 to 2.
Sign bits are represented by 66.
The above is my according to the results of DUMP, summed up the relationship between the constants for these mentioned above, Oracle the reason for this choice is reasonable, we can also be deduced according to the example of the back, and will further explain why shown by this way. Here's what it means to get people to have a rough idea of NUMBER type data.
Let's give an example of this in detail:
SQL> CREATE TABLE TEST_NUMBER (NUMBER_COL NUMBER);
Table has been created.
SQL> INSERT INTO TEST_NUMBER VALUES (0);
1 rows have been created.
SQL> INSERT INTO TEST_NUMBER VALUES (1);
1 rows have been created.
SQL> INSERT INTO TEST_NUMBER VALUES (2);
1 rows have been created.
SQL> INSERT INTO TEST_NUMBER VALUES (25);
1 rows have been created.
SQL> INSERT INTO TEST_NUMBER VALUES (123);
1 rows have been created.
SQL> INSERT INTO TEST_NUMBER VALUES (4100);
1 rows have been created.
SQL> INSERT INTO TEST_NUMBER VALUES (132004078);
1 rows have been created.
SQL> INSERT INTO TEST_NUMBER VALUES (2.01);
1 rows have been created.
SQL> INSERT INTO TEST_NUMBER VALUES (0.3);
1 rows have been created.
SQL> INSERT INTO TEST_NUMBER VALUES (0.00000125);
1 rows have been created.
SQL> INSERT INTO TEST_NUMBER VALUES (115.200003);
1 rows have been created.
SQL> INSERT INTO TEST_NUMBER VALUES (-1);
1 rows have been created.
SQL> INSERT INTO TEST_NUMBER VALUES (-2);
1 rows have been created.
SQL> INSERT INTO TEST_NUMBER VALUES (-20032);
1 rows have been created.
SQL> INSERT INTO TEST_NUMBER VALUES (-234.432);
1 rows have been created.
SQL> COMMIT;
Submission completed.
SQL> COL D_NUMBER FORMAT A50
SQL> SELECT NUMBER_COL, DUMP (NUMBER_COL, 16) D_NUMBER FROM TEST_NUMBER;
NUMBER_COL D_NUMBER
------------------------------------------------------------
0 Typ=2 Len=1: 80
1 Typ=
2 Len=2: C1,2
2 Typ=2 Len=2: c1,3
25 Typ=2 Len=2: c1,1a
123 Typ=2 Len=3: c2,2,
18
4100 type = 2 = 2: c2,2a len
132004078 type = 2 len = 6: c5,2,21,1,29,4f
2.01 type = 2 = 3: c1,3,2 len
. 3 type = 2 = 2: c0,1f len
125 00000. type = 2 len = 3: ask, 2,1a
115.200003 type = 2 len = 6: c2,2,10,15,1,4
- 1 type = 2 len = 3: 3e, 64,66
- 5 type = 2 len = 3: 3e, 60,66
- 20032 type = 2 len = 5: 3c, 63,65,45,66
-234.432 type = 2 len = 6: 3d, 63,43,3a, 51,66
已选择15行.
下面根据例子得到的结果, 对每行进行说明.首先说明两点基本的.dump函数返回的type＝2表示dump的数据类型是number, length ＝n表示数值在数据库中存储的长度是n.
1.dump (0) 的结果是0x80, 在前面已经提到, 0只有高位表示位, 没有数据位.由于0的特殊, 既不属于正数, 也不属于负数, 因此使用高位表示位用80表示就足够了, 不会和其它数据冲突, oracle出于节省空间的考虑将后面数据部分省掉了.但是为什么oracle选择
0x80表示0呢? 我们知道正数和负数互为相反数, 每个正数都有一个对应的负数.因此如果我们要使用编码表示数值, 则表示正数和负数的编码应该各占一半, 这样才能保证使oracle表示数据范围是合理的.而0x80的二进制编码是1000 0000, 正好是一个字节编码最大值的一半, 因此, oracle选择0x80来表示0, 是十分有道理的.
2.dump (1) 的结果是0xc102,0xc1表示了最高位个位, 0x2表示数值是1.首先, oracle为什么用c1表示个位呢? 其实, 道理和刚才的差不多.采用科学计数法, 任何一个实数s都可以描述为a.b×10n, a 表示整数部分, b表示小数部分, 而n表示10的指数部分.当s大于1时, n大于等于0, s小于1时, n小于0.也就是说, 采用指数的方式表示, n大于0和n小于0的情况各占一半左右时, oracle所表示的范围最广.因此, oracle选择了c1表示个位是最高位的情况.
sql: select to _ char (round (to _ number ("81", "xxx") + (to _ number ("ff", "xxx") to _ number ("81", "xxx") + 1) / 2), "xx") from dual;
to _
- - -
c1
为什么oracle使用0x2表示1, 而不直接使用0x1表示1呢? oracle 每个字节表示2位数, 因此对于这个2位数, 出现的可能是0～99共
100种可能, 问题出在0这里.oracle底层是用c语言实现的, 我们知道二进制0在c语言中用作字符串终结符, oracle为了避免这个问题, 因此使用了0x1表示0, 并依次类推, 使用0x64表示99.
3.dump (2) 的结果是0xc103.
4.dump (25) 的结果是0xc11a.前面提到, 数据部分是以2位为最小单位保存的.因此对于25来说, 最高位表示位仍然是个位, 个位上的值是25, 根据上面推出的规则, 25在存储为0xc11a.
sql: select to _ char (25 + 1, "xx") from dual;
to _
- - -
1a
5.dump (123) 的结果是0xc20218.由于123最高为是百位, 所以最高位表示位为0xc2, 百位上是1, 用0x02表示, 个位上是23, 用
0x18表示.
6.dump (4100) 的结果是0xc22a.
注意一点, 如果数字最后数位上如果是0, oracle出于节省空间的考虑不会存储.比如: 4100只保存百位上的4112000000只保存百位位上的12,
512000 of them saved 20 of the 51 and hundred on the thousand.
The result of 7.DUMP (132004078) is 0xc5022101294f. The highest
position is a million bits, so it is represented by 0xC5. 1 is represented by 0x02 in 100 bits, and 32 is expressed by 0x21 in a hundred bit. 0 is represented by 0x01 in 100 bits, and 40 by 0x29 on a hundred bit. 78 is represented by 0x4F in a bit.
Note: 0 of the median digit cannot be omitted.
The result of 8.DUMP (2.01) is 0xc10302. The highest bit is a bit represented by 0xC1, 2 on a bit, by 0x03, and 1 on a percentile, represented by 0x02.
Notice: the next bit is a percentile, not a bit.
The result of 9.DUMP (0.3) is 0xc01f. The highest position is percentile, and 0xC0 means that the percentile is 30 and is represented by 0x1F.
The result of 10.DUMP (0.00000125) is 0xbe021a. The highest position is a million bits, represented by 0xBE, and the 1 on the highest position is represented by 0x02, and 25 is represented by 0x1a.
The result of 11.DUMP (115.200003) is 0xc20210150104.
The result of 12.DUMP (-1) is 0x3e6466. The highest bit is represented by 0x3E, 64 denotes 1 on the bit, and 66 is the symbol bit, which means the number is negative.
The negative and positive numbers are opposite numbers, the highest position of the negative number represents the bit, and the sum of the maximum of the corresponding opposite number is
FF. The highest position of 1 is C1, and the highest position of -1 is 3E. 1 of negative numbers is represented by 64. The sum of the numbers in the negative numbers and the numbers of the opposite numbers is 0x66, that is, the symbol bits. Positive number 1 is expressed in 0x02, negative number 1 is expressed in 0x64, and the sum of the two is 0x66. The negative number is more than one identifier, represented by 0x66. Since the range of positive numbers is from 0x01 to 0x64, the range of negative numbers is from 0x65 to 0x02. Therefore, the 0x66 representation does not appear when the number is represented.
The result of 13.DUMP (-5) is 0x3e6066. 0x3e indicates that the MSB is a bit, 0x60 denotes 5 on a bit, and 0x66 is a symbol bit. 0x3E plus 0xC1 is 0xFF. The result of 0x60 plus 0x06 is 0x66.
The result of 14.DUMP (-20032) is 0x3c63654566. The highest position is 10000 bits, and the positive number is 0xC3, so the number of negative numbers is 0x3C. The number is 2, the positive number is expressed by 0x03, the negative number is 0x63, the hundred is 0, the positive number is expressed by 0x01, the negative number is expressed by 0x65, the bit is 32, the positive number is expressed by 0x21, and the negative number is expressed by 0x45. 0x66 is a negative bit.
The result of 15.DUMP (-234.432) is 0x3d63433a5166.
According to the storage characteristics of Oracle, the range of number type of Oracle can also be introduced.
That's what Oracle says on concept:
The following numbers can be stored in a NUMBER column:
Positive numbers in the range 1 x 10^-130 to 9.99... 9 x 10^125 with up to 38 significant digits.
Negative numbers from -1 x 10^-130 to 9.99... 99 x 10^125 with up to 38 significant digits.
Zero.
The range of values is derived.
Look at the symbol bit, 0xC1 denotes a bit.
SQL> select to_number ('ff','xxx') - to_number ('c1','xxx') from dual;
TO_NUMBER ('FF','XXX') -TO_NUMBER ('C1','XXX')
-------------------------------------------
Sixty-two
Since Oracle is stored in two bits and two bits, the MSB is equivalent to 62 x 2=124, and the maximum value at the MSB is 99, so the maximum value of the positive number is 9.999...... X 10^125.
SQL> select to_number ('c1','xxx') - to_number ('80','xxx') from dual;
TO_NUMBER ('C1','XXX') -TO_NUMBER ('80','XXX')
-------------------------------------------
六十五
最高位相当于65×2 = 130，1，10因此正数的最小值为×^ - 130。

负数和正数在各使用了一半的编码，因此具有相同的极值范围。