oracle number长度转换,oracle中dump函数及oracle NUMBER类型内部存储机制

oracle中dump函数

DUMP函数的输出格式类似:

类型 ，符号/指数位 [数字1，数字2，数字3，......，数字20]

各位的含义如下:

1.类型: Number型,Type=2 (类型代码可以从Oracle的文档上查到)

2.长度:指存储的字节数

3.符号/指数位

在存储上,Oracle对正数和负数分别进行存储转换:

正数：加1存储(为了避免Null)

负数：被101减,如果总长度小于21个字节，最后加一个102(是为了排序的需要)

指数位换算:

正数：指数=符号/指数位 - 193 (最高位为1是代表正数)

负数：指数=62 - 第一字节

4.从开始是有效的数据位

从开始是最高有效位,所存储的数值计算方法为：

将下面计算的结果加起来：

每个乘以100^(指数-N) (N是有效位数的顺序位，第一个有效位的N=0)

5、举例说明

SQL> select dump(123456.789) from dual;DUMP(123456.789)

-------------------------------

Typ=2 Len=6: 195,13,35,57,79,91

:     195 - 193 = 2

13 - 1      = 12 *100^(2-0) 120000

35 - 1      = 34 *100^(2-1) 3400

57 - 1      = 56 *100^(2-2) 56

79 - 1      = 78 *100^(2-3) .78

91 - 1      = 90 *100^(2-4) .009

123456.789

SQL> select dump(-123456.789) from dual;DUMP(-123456.789)

----------------------------------

Typ=2 Len=7: 60,89,67,45,23,11,102

62 - 60 = 2(最高位是0，代表为负数)

101 - 89 = 12 *100^(2-0) 120000

101 - 67 = 34 *100^(2-1) 3400

101 - 45 = 56 *100^(2-2) 56

101 - 23 = 78 *100^(2-3) .78

101 - 11 = 90 *100^(2-4) .009

123456.789(-)

现在再考虑一下为什么在最后加102是为了排序的需要，-123456.789在数据库中实际存储为

60,89,67,45,23,11

而-123456.78901在数据库中实际存储为

60,89,67,45,23,11,91

可见，如果不在最后加上102，在排序时会出现-123456.789

转自

Internal representation of the NUMBER datatype

As with other datatypes, stored numbers are preceded by a length byte which stores the size of the datum in bytes, or 0xFF for NULLs. The actual data bytes for non-null numbers represent the value in scientific notation. For example, the number 12.3 is represented as +0.123 * 10². The high order bit of the first byte represents the sign. The sign bit is set for positive numbers; and clear for negative numbers. The remainder of the first byte represents the exponent, and then up to 20 bytes may be used to represent the significant digits excluding trailing zeros. This is sometimes called the mantissa.

Each byte of the mantissa normally represents two decimal digits. For positive numbers an offset of 1 is added to avoid null bytes, while for negative numbers an offset of 101 is added to the negated digit pair. Thus a mantissa byte with the decimal value of 100 might represent the digit pair "99" in a positive number, or the digit pair "01" in a negative number. The interpretation must be based on the sign bit. Negative numbers with less than 20 mantissa bytes also have a byte with the (impossible) decimal value 102 appended. I don't know what purpose this serves.

If there are an odd number of significant digits before the decimal point, the first mantissa byte can only represent 1 digit because the decimal exponent must be even. In this case, the 20-byte mantissa can represent at most 39 decimal digits. However, the last digit may not be accurate if a more precise value has been truncated for storage. This is why the maximum guaranteed precision for Oracle numbers is 38 decimal digits, even though 40 digits can be represented.

The decimal exponent is guaranteed to be even by the alignment of the mantissa. Thus the value stored for the exponent is always halved and is expressed such that the decimal point falls before the first digit of the mantissa. It again represents a pair of decimal digits, this time with an offset of 64 for positive numbers, and 63 for the negated exponent of negative numbers. Thus a set of exponent bits with the decimal value of 65 might represent the exponent +2 in a positive number, or the exponent -4 in a negative number. Please note that the encoding of the exponent is based on the sign of the number, and not on the sign of the exponent itself.

Finally, there are special encodings for zero, and . Zero is represented by the single byte 0x80. Negative infinity is represented by 0x00, and positive infinity is represented by the two bytes 0xFF65. These are illustrated in the listing below.SQL> select n, dump(n,16) from special_numbers;

N DUMP(N,16)

--- ------------------------------------------

0 Typ=2 Len=1: 80

-~ Typ=2 Len=1: 0

~ Typ=2 Len=2: ff,65

For the rest, the best way to familiarize yourself further with the internal representation of numbers is to use the dump function to examine the representation of some sample values. This is simulated below. Just type a number and then press "Enter" to check out its representation. For example, try to find out why 110 takes one more byte of storage than 1100 despite being a smaller number.

Number:

Representation:

Normalized number: +0.1230 * 10^2

Sign bit: 1

Exponent bits: 65 = (64 + 2/2)

First byte: 193

Mantissa digits: 12,30

Mantissa bytes: 13,31