Number____oracle of Oracle data types

Quote: http://www.blogjava.net/caojianhua/archive/2011/01/24/343461.html

Oracle's number type is one of Oracle's built-in types and is the most basic numeric data type for Oracle. Only one intrinsic data type suitable for storing numeric data was supported in 9IR2 and its previous versions, and two new numeric types emerged after 10g, that is, the introduction of local floating-point data types (Native floating-point data Types): Binary_float ( Single-precision 32-bit) and binary_double (double 64-bit). These new data types are based on the IEEE binary floating-point operations standard, ansi/ieee Std 754-1985 [IEEE 754], using these types with literal f (binary_float) or D (binary_double), such as 2.07f, 3.000094d.

Number data type

The syntax for the number type is simple: Number (p,s):
P: Precision bit, precision, is the total number of valid data digits, the value range is 38, the default is 38, you can use the character * to represent 38.
S: decimal place, scale, is the number of digits to the right of the decimal point, the value range is -84~127, the default value depends on p, if p is not specified, then S is the maximum range, if p is specified, then s=0.
P:is the Precision,or the total number of digits. Oracle guarantees the portability of numbers with precision ranging from 1 to 38.
S:is the scale, or the number of digits to the right of the decimal point. The scale can range from-84 to 127.

The number type P and S, which have nothing to do with the underlying storage, do not affect how data is stored on disk, it affects only what values are allowed and how the value is rounded, and you can think of it as "edit" the data. In simple terms, the precision bit p represents the maximum number of valid digits for a number, while the decimal s indicates the maximum number of decimal digits. In other words, p indicates how many digits are available (that is, the maximum number of p-s digits to the left of the decimal point), and S indicates the s-bit valid digits to the right of the decimal point. such as number (5,2) type of data, there are up to 3 digits to the left of the decimal point, with a maximum of 2 digits on the right, plus a maximum of 5 digits, which cannot be stored correctly, noting that this is not stored correctly, but it is not impossible to store.

Maximum number of integer digits =p-s
s positive, start rounding at specified position to right of decimal point
s minus, start rounding at specified position to the left of decimal
S is 0 or unspecified, rounded to the nearest integer
When P is less than S, the number is a number with an absolute value of less than 1, and the first s-p bit from the right of the decimal point must be 0, preserving the S-decimal.

P>0, the S is divided into 2 kinds of situations:
1. s>0
Accurate to the right of the decimal point s bit, and rounded. Then verify that the effective digits <=p; if s>p, there are at least s-p 0 padding to the right of the decimal point.
2. s<0
Accurate to the left of the decimal point, and rounded. Then verify that the effective digit is <=p+|s|

Specific data can refer to the table below

Value	Datatype	Stored Value
123.2564	Number	123.2564
1234.9876	Number (6,2)	1234.99
12345.12345	Number (6,2)	Error
1234.9876	Number (6)	1235
12345.345	Number (5,-2)	12300
1234567	Number (5,-2)	1234600
12345678	Number (5,-2)	Error
123456789	Number (5,-4)	123460000
1234567890	Number (5,-4)	Error
12345.58	Number (*, 1)	12345.6
0.1	Number (4,5)	Error
0.01234567	Number (4,5)	0.01235
0.09999	Number (4,5)	0.09999
0.099996	Number (4,5)	Error

There are rows where the error occurs because the source data exceeds the range that can be represented, or because the decimal rounding exceeds the range that can be represented.

Here are some examples

1. s>0

Accurate to the right of the decimal point s bit, and rounded. Then verify that the effective digits are <=p;

ZWF. Yudong>create table T_n (ID number (5,2));

Table created.

ZWF. Yudong>insert into t_n values (123.45);

1 row created.

ZWF. Yudong>insert into t_n values (123.455);

1 row created.

ZWF. Yudong>select * from T_n;

Id
----------
123.45
123.46

2 rows selected.

ZWF. Yudong>insert into t_n values (1.234);

1 row created.

ZWF. Yudong>select * from T_n;

Id
----------
123.45
123.46
1.23

3 Rows selected.

ZWF. Yudong>insert into T_n values (. 001);

1 row created.

ZWF. Yudong>select * from T_n;

Id
----------
123.45
123.46
1.23
0

4 rows selected.

ZWF. Yudong>insert into t_n values (1234.56);
INSERT into t_n values (1234.56)
*
ERROR at line 1:
Ora-01438:value larger than specified precision for this column


If s>p, there are at least s-p 0 padding to the right of the decimal point.

ZWF. Yudong>create table T_n (ID number (4,5));

Table created.

ZWF. Yudong>insert into t_n values (1);
INSERT into t_n values (1)
*
ERROR at line 1:
Ora-01438:value larger than specified precision for this column


ZWF. Yudong>insert into T_n values (. 1);
INSERT into T_n values (. 1)
*
ERROR at line 1:
Ora-01438:value larger than specified precision for this column


ZWF. Yudong>insert into t_n values (. 01);

1 row created.

ZWF. yudong>commit;

Commit complete.

ZWF. Yudong>select * from T_n;

Id
----------
.01

1 row selected.

ZWF. Yudong>insert into T_n values (. 001);

1 row created.

ZWF. Yudong>insert into T_n values (. 0001);

1 row created.

ZWF. Yudong>insert into T_n values (. 00001);

1 row created.

ZWF.   Yudong>insert into T_n values (. 000001); --Over scale storage 0

1 row created.

ZWF. Yudong>select * from T_n;

Id
----------
.01
.001
.0001
.00001
0


Ten rows selected.

ZWF. Yudong>col DP for A50
ZWF.  Yudong>select id,dump (ID) dp,length (ID), vsize (ID) from T_n; --vsize and dump are bytes, length is the actual number of digits (including decimal points)

ID DP LENGTH (ID) vsize (ID)
---------- -------------------------------------------------- ---------- ----------
. typ=2 len=2:192,2 3 2
.001 typ=2 len=2:191,11 4 2
.0001 typ=2 len=2:191,2 5 2
.00001 typ=2 len=2:190,11 6 2
0 typ=2 len=1:128 1 1

5 rows selected.


2. s<0

Accurate to the left of the decimal point, and rounded. Then verify that the effective digit is <=p+|s|

ZWF. Yudong>create table T_n (ID number (5,-2));

Table created.

ZWF. Yudong>insert into t_n values (12345);

1 row created.

ZWF. Yudong>select * from T_n;

Id
----------
12300

1 row selected.

ZWF. Yudong>insert into t_n values (123456);

1 row created.

ZWF. Yudong>insert into t_n values (1234567);

1 row created.

ZWF. Yudong>select * from T_n;

Id
----------
12300
123500
1234600

3 Rows selected.

ZWF. Yudong>insert into t_n values (12345678);
INSERT into t_n values (12345678)
*
ERROR at line 1:
Ora-01438:value larger than specified precision for this column

Number type storage structure of Oracle

Oracle uses a variable-length storage Number data type (converted to a 2-encoded format to store in a certain rule).

The number type stored in the Oracle database contains 3 parts: The head section, the data section, and the symbol bit.

For positive numbers, the symbol bit is omitted, and for 0, Oracle stores X80 (128).

ZWF. Yudong>select Dump (0) from dual;

DUMP (0)
----------------
typ=2 len=1:128

1 row selected.

ZWF. Yudong>select dump (1) from dual;

DUMP (1)
------------------
typ=2 len=2:193,2

1 row selected.

ZWF. Yudong>select dump ( -1) from dual;

DUMP (-1)
-----------------------
typ=2 len=3:62,100,102

1 row selected.

The head section is a byte 8 bits, which is the 128, 193, 62 that you saw earlier. From this section we can see the basic information of the number type, because when you design this storage format, Oracle wants to represent all the 00-ff in hexadecimal
Number, so in order to encode the symmetry, first will be divided into positive and negative, so the middle position of 00-FF 80, that is, the decimal 128来 0, the head part is less than 80, that is negative, greater than 80 is a positive number. Oracle again to
00-80, 80-ff to divide:

00-3E said: Number <=-1
3f-7f said:-1 < number < 0
81-c0 said: 0 < number < 1
C1-ff said: Number >= 1

From the head section we can also see the number of bits of data information, whether it contains decimals, you can judge the position of the decimal point according to the head information. Because the data part of the low 2 N-time azimuth 0 is not stored, when the data is presented Oracle
According to the head of the information to add the lowest 0.

ZWF. Yudong>select dump (123456789) from dual;

DUMP (123456789)
------------------------------
typ=2 len=6:197,2,24,46,68,90--197 (C5) means that the number 123456789 is greater than 1,197-193 (the number 1 occupies 2 bytes for 193) = 4, so this number occupies 6 (2+4) bytes.

1 row selected.


Then we'll look at the data section, where Oracle's decimal digits (the exact opposite of the integer part, the decimal part) are stored in two-bit pairs (from the right to the left), for example, for 1234, where Oracle stores 12, 34, respectively.
So you just need to encode (+-) 1-99.

1---99, respectively, in hexadecimal 2-64, is 2-100,

-1----99, in hexadecimal 64-2, is 100-2.

ZWF. Yudong>select dump (12345) from dual;

DUMP (12345)
------------------------
typ=2 len=4:195,2,24,46--The Data section 2,24,46 representation (2-1=1,24-1=23,46-1=45); The head section represents 12345 >= 1, consuming 195-193+2=4 bytes.

1 row selected.