# Implementation of Oracle Number-type numerical storage and conversion

Source: Internet
Author: User

Oracle stores data through corresponding algorithm conversion in the database. This article briefly introduces the Number-type data storage and conversion of Oracle. This article aims to answer question No. 2119 on the message board.
We can use the DUMP function to convert the storage format of numbers. A simple output is similar to the following format:
Copy codeThe Code is as follows:
SQL> select dump (1) from dual; DUMP (1)
------------------
Typ = 2 Len = 2: 193,2

The DUMP function output format is similar:
Type <[length]>, symbol/exponent digit [number 1, number 2, number 3, ......, number 20]
The meanings are as follows:
1. Type: Number Type, Type = 2 (Type code can be found in the Oracle document)
2. Length: refers to the number of bytes stored
3. symbol/index bit
On storage, Oracle stores and converts positive and negative numbers respectively:
Positive: Add 1 storage (to avoid Null)
Negative number: It is reduced by 101. If the total length is smaller than 21 bytes, the last 102 is added (required for sorting)
Exponential conversion:
Positive number: exponent = symbol/exponent bit-193 (1 indicates positive number)
Negative: Index = 62-first byte
The value is the highest valid bit starting from <number 1>. The stored numerical calculation method is as follows:
Each <digit> multiplied by 100 ^ (exponential-N) (N is the order of the valid digits, N = 0 for the first valid digit)
5. Examples
Copy codeThe Code is as follows:
SQL & gt; select dump (123456.789) from dual; DUMP (123456.789)
-------------------------------
Typ = 2 Len = 6: 195, 13, 35, 57, 79,91

<Index>: 195-193 = 2
<Number 1> 13-1 = 12*100 ^ (2-0) 120000
<Number 2> 35-1 = 34*100 ^ (2-1) 3400
<Number 3> 57-1 = 56*100 ^ (2-2) 56
<Number 4> 79-1 = 78*100 ^ (2-3). 78
<Number 5> 91-1 = 90*100 ^ (2-4). 009
123456.789
Copy codeThe Code is as follows:
SQL> select dump (-123456.789) from dual; DUMP (-123456.789)
----------------------------------
Typ = 2 Len = 7: 60, 89, 67,45, 102

<Index> 62-60 = 2 (the highest digit is 0, indicating a negative number)
<Number 1> 101-89 = 12*100 ^ (2-0) 120000
<Number 2> 101-67 = 34*100 ^ (2-1) 3400
<Number 3> 101-45 = 56*100 ^ (2-2) 56
<Number 4> 101-23 = 78*100 ^ (2-3). 78
<Number 5> 101-11 = 90*100 ^ (2-4 ).
123456.789 (-)
Now let's take another look at why adding 102 in the last step is required for sorting.-123456.789 is actually stored in the database

-123456.78901 the actual storage in the database is
, 91
It can be seen that if 102 is not added at the end,-123456.789 <-123456.78901 may occur during sorting.
For question No. 2119, the first question is:
1. Why does 193 and 2 mean?
You can see the answer from above.
2. Why are there two bytes of length for the NUMBER type?
For this problem, I think we should know that all data types are ultimately stored in binary in the computer. In fact, the so-called data types are defined. Therefore, the storage is only determined by the algorithm.
Therefore, this problem is not true. For example:
Copy codeThe Code is as follows:
SQL & gt; select dump (110) from dual; DUMP (110)
---------------------
Typ = 2 Len = 3: 194,2, 11SQL> select dump (1100) from dual; DUMP (1100)
-------------------
Typ = 2 Len = 2: 194,12

We can see that although 1100> 110, the storage of 1100 occupies only 2 bytes, while 110 occupies 3 bytes.

Related Keywords:
Related Article

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

## A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

• #### Sales Support

1 on 1 presale consultation

• #### After-Sales Support

24/7 Technical Support 6 Free Tickets per Quarter Faster Response

• Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.