ORACLE numeric types-NUMBER and BINARY

Source: Internet
Author: User
Tags binary to decimal decimal to binary
Before 9i, ORACLE provided the Number type for us. In 10 Gb, ORACLE introduced the BINARY_FLOAT and BINARY_DOUBLE data types. In addition, there are integer, smallint and other data types, but the underlying implementation of these types is still NUMBER, BINARY_FLOAT, BINARY_DOUBLE. Therefore, we can consider ORACLE

Before 9i, ORACLE provided the Number type for us. In 10 Gb, ORACLE introduced the BINARY_FLOAT and BINARY_DOUBLE data types. In addition, there are integer, smallint and other data types, but the underlying implementation of these types is still NUMBER, BINARY_FLOAT, BINARY_DOUBLE. Therefore, we can consider ORACLE

Before 9i, ORACLE provided the Number numeric type for us. In 10 Gb, ORACLE introduced the BINARY_FLOAT and BINARY_DOUBLE data types. In addition, there are integer, smallint and other data types, but the underlying implementation of these types is still NUMBER, BINARY_FLOAT, BINARY_DOUBLE. Therefore, we can assume that ORACLE provides three data types for data storage: NUMBER, BINARY_FLOAT, and BINARY_DOUBLE. You can store positive, negative, 0, infinite, and NAN (not a number) values ).

NUMBER

The number type occupies 0 ~ The 22-byte storage space is a variable-length data type. It adopts oracle internal algorithms and is a soft data type. Therefore, it has good data accuracy, versatility, and portability, however, its operation efficiency is relatively low than that of a floating point type (hardware operation. From 0 ~ Of the 22-byte storage space, one byte is used to store the data length, and 21 bytes are used to store the data (one byte stores the content related to symbols and indexes, 20 bytes for data storage ).

The range of data stored by number is as follows:

Positive number: 1x10-130 to 9. 99... 9x10125. The data precision is 38 valid numbers.

Negative number:-1x10-130 to 9. 99... 99x10125. The data precision is 38 valid numbers.

Zero: 0

Infinity: Only data can be imported from oracle 5.

Number valid number

You can specify COLUMN_NAME number (precision, scale) in the following format. precision indicates the maximum number length that can be stored by number (excluding the 0 values on both sides ), scale refers to the maximum number length (including 0 on the left) on the right of the decimal point ). If p and s are specified, we can use the following algorithm to determine whether the data is valid: If s> = 0, we first count s numbers from the decimal point to the right, if there is still data on the right side, it is truncated, And then we count p numbers on the left side at the current position. If there is still a non-zero number on the left, the number is invalid; otherwise, the data is valid. If s <0, we first calculate the number from the decimal point to the right | s | number, and cut off the data on the right, then we count p numbers on the left at the current position. If there are still non-zero numbers on the left, the number is invalid; otherwise, the data is valid.

If phes is not specified, for example, column_name number, the number is stored in the input format. If p is specified but s is not specified, s is 0 by default, for example, column_name number (p); if s is specified but p is not specified, p is 38 by default, for example, column_name number (*, s ).

The value range of p is 1-38, which is shown in the official document as follows:

Oracle guarantees the portability of numbers with precision of up to 20 base-100 digits, which is equivalent to 39 or 40 decimal digits depending on the position of the decimal point. because 20 bytes are used internally to store data, each byte stores 2 digits, a maximum of 40 digits can be stored, and 40 mentioned in the document, however, if the number on the left of the decimal point is an odd number, add a digit 0 on the left, and the maximum value is 39. In terms of syntax, oracle requires 38 precision, which may be for safety or other reasons.

The value range of s is-84 ~ 127, which is also related to the internal storage format of number.

[SQL]

SQL> create table t6 (c1 number, c2 number (*, 1), c3 number (9), c4 number (9, 2), c5 number (9, 1 ), c6 number (6), c7 number (7,-2 ));

The table has been created.

SQL> insert into t6 values (7456123.89, 7456123.89, 7456123.89, 7456123.89, 7456123.89, 7456123.89, 7456123.89 );

Insert into t6 values (7456123.89, 7456123.89, 7456123.89, 7456123.89, 7456123.89, 7456123.89, 7456123.89)

*

Row 3 has an error:

ORA-01438: value greater than the allowable precision specified for this column

SQL> insert into t6 values (7456123.89, 7456123.89, 7456123.89, 7456123.89, 7456123.89, 0, 7456123.89 );

One row has been created.

SQL> commit;

[SQL]

SQL> select * from t6;

C1 C2 C3 C4 C5 C6 C7

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

7456123.89 7456123.9 7456124 7456123.89 7456123.9 0 7456100

Number Storage Structure

The storage structure of the number data type can be displayed through the dump function. Before exploring the number Storage Structure, let's take a look at the usage of the dump function.

The DUMP function output format is similar:

Type <[length]>, symbol/index digit [number 1, number 2, number 3 ,......, Number 20]

The meanings of parameters are as follows:

· Type indicates the field data Type, which is Number Type and Type = 2 (the Type code can be found in the Oracle document );

· Length refers to the number of bytes stored;

· Symbols/exponent bits are used to represent positive and negative numbers and exponent values;

· Data storage.

Oracle uses 21 bytes to store the number. The first byte is the header information, and the last 20 bytes store valid numbers.

Header information data domain 1 data domain 2... data domain n (n <= 20)

XXXXXXXXXXXXXXXX

|-One byte stores two decimal numbers and can store 0-99. Because 0 is a special character in C, 1-is used to store 0-99, add 1 to the original value. Do not store positive or negative symbols in the data domain.

|-Easy sorting. For negative numbers, we use 100-1-2 to store 0-99 (why not use-1? Please advise ), add a byte with a value of 102 at the end of the negative number to avoid sorting errors.

|-Data index range identifier, when a positive number, the value and 64 do difference (x-64), the result is an index, when a negative number, 63 is different from this value (63-x), and the result is an index. In oracle, 0X80 represents 0. Therefore,

|-Positive number, with an exponential range of-63 ~ 0 (decimal) 1 ~ 63 (non-decimal); for negative numbers, the exponential range is 63 ~ 1 (non-decimal), 0 ~ -64 (decimal)

|-Symbol Information bit. 1 indicates a positive number, and 0 indicates a negative number.

Query: For data stored in the data field, oracle converts each byte to decimal, and then concatenates each byte. If the value of the index field is positive n, the decimal point is located after the nth byte. If it is 0, the decimal point is before the first byte. If it is negative, then, the decimal point is pushed forward to n bytes in the first byte.

Next, let's take an example to see whether the above method is correct:

[SQL]

Desc tab1

Name null type

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

C1 NUMBER

C2 VARCHAR2 (300)

If the precision of the negative number is very large, the 102 comparison bit may be discarded, so 102 has no effect.

[SQL]

SQL> insert into tab1 values (-111111111111111111111111111111111111111111111111111111111111 );

One row has been created.

SQL> select dump (c1) from tab1;

DUMP (C1)

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

Typ = 2 Len = 2: 193,2

Typ = 2 Len = 3: 62,100,102

Typ = 1 Len = 1: 128

Typ = 2 Len = 6: 197,2, 24,46, 68,90

Typ = 2 Len = 7: 58,100, 102

Typ = 2 Len = 12: 58,100, 91,102

Typ = 2 Len = 11: 197,2, 24,46, 68,90, 99,77, 55,33, 11

Typ = 2 Len = 21: 33,90, 90,90, 90,90, 90,90, 90,90, 90,90,

90, 90, 90, 90, 90, 90, 90

Number Variant

In addition to the number type, oracle also provides us with some variant Data Types of number. The internal implementation of these types is still number, which can be understood as a specific alias of number.

NUMERIC (p, s): fully mapped to NUMBER (p, s ). If p is not specified, the default value is 38.

DECIMAL (p, s) or DEC (p, s): fully mapped to NUMBER (p, s ). If p is specified, the default value is 38.

INTEGER or INT: fully mapped to the NUMBER (38) type.

SMALLINT: fully mapped to the NUMBER (38) type.

FLOAT (B): ing to NUMBER type.

Double precision: ing to NUMBER type.

REAL: ing to NUMBER type.

Pay special attention to the float type here. Refer to the official documentation:

The FLOAT data type is a subtype of NUMBER. It can be specified with or without precision, which has the same definition it has for NUMBER and can rangefrom 1

126. Scale cannot be specified, but is interpreted from the data. Each FLOAT value requires from 1 to 22 bytes.

To convert from binary to decimal precision, multiply n by 0.30103. to convert from decimal to binary precision, multiply the decimal precision by 3.32193. the maximum of 126 digits of binary precision is roughly equivalent to 38 digits of decimal precision.

From this we can see that the valid number in float refers to the valid number in binary format, and is converted to a 10th hexadecimal value of B * 0.30103. if the result is not an integer, the top integer is used as the precision of the underlying number storage. For example:

[SQL]

SQL> desc tab1

Is the name empty? Type

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

C1 FLOAT (2)

C2 FLOAT (4)

C3 FLOAT (6)

C4 FLOAT (7)

C5 FLOAT (9)

C6 FLOAT (11)

C7 FLOAT (13)

SQL> insert into tab1 values (1234567890.0987654321, 1234567890.0987654321, 1234567890.0987654321, 1234567890.0987654321, 1234567890.0987654321, 1234567890.0987654321, 1234567890.0987654321 );

One row has been created.

SQL> select * from tab1;

C1 C2 C3 C4 C5 C6 C7

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

1000000000 1200000000 1200000000 1230000000 1230000000 1235000000 1235000000

PLS_INTEGER and BINARY_INTEGER

PLS_INTEGER and BINARY_INTEGER are plsql data types and cannot be directly used in tables. In the official documents of the 11g website, it is pointed out that PLS_INTEGER and BINARY_INTEGER are the same and are signed integers. The value range is-2 ^ 31 ~ 2 ^ 31, occupying 32 bits in total. The internal storage format of dump indicates that it is of the number type through the dump function, which may be incorrect (Supplement: When dump varchar2 and nvarchar2 are of the Data Type, their typ values are 1, dump may use the same typ for similar data types, because the official documents clearly indicate the differences between PLS_INTEGER BINARY_INTEGER and number:

1. The storage space is smaller than the number

2. The computing efficiency is higher than that of number. Therefore, you can directly perform hardware operations, while number is a soft operation.

If the value range exceeds the range of PLS_INTEGER and BINARY_INTEGER, an overflow exception occurs;

[SQL]

DECLARE

P1 PLS_INTEGER: = 2147483647;

P2 PLS_INTEGER: = 1;

N NUMBER;

BEGIN

N: = p1 + p2;

END;

/

Result:

DECLARE

*

ERROR at line 1:

ORA-01426: numeric overflow

ORA-06512: at line 6

In 11g, oracle introduced simple_integer, which is a child type of PLS_INTEGER and has the same value range, however, this type does not perform null and overflow detection (non-null and non-overflow must be manually guaranteed), so it has higher performance than PLS_INTEGER. You can use simple_integer to store a value that is not empty and does not cause overflow. Because no overflow detection is performed, the calculation result of simple_integer may undergo a conversion of positive and negative values, for example:

[SQL]

DECLARE

N SIMPLE_INTEGER: = 2147483645;

BEGIN

FOR j IN 1 .. 4 LOOP

N: = n + 1;

DBMS_OUTPUT.PUT_LINE (TO_CHAR (n, 's999999999999 '));

End loop;

FOR j IN 1 .. 4 LOOP

N: = n-1;

DBMS_OUTPUT.PUT_LINE (TO_CHAR (n, 's999999999999 '));

End loop;

12 END;

13/

+ 2147483646

+ 2147483647

-2147483648

-2147483647

-2147483648

+ 2147483647

+ 2147483646

+ 2147483645

The PL/SQL process is successfully completed.

If simple_integer is converted to pls_integer, it is converted to pls_integer not null.

BINARY_FLOAT and BINARY_DOUBLE

Oracle according to IEEE754 single precision and double precision floating point type, to understand IEEE754, please refer to the http://blog.csdn.net/yidian815/article/details/12912661

Because of the different storage structures, BINARY_FLOAT and BINARY_DOUBLE can store data in a larger range than number, but their precision is not as accurate as number. If you store financial data, we recommend using number. If you perform scientific operations, we recommend using BINARY_FLOAT and BINARY_DOUBLE. Because floating point data uses hardware computing, the computing efficiency is very high.

We can determine whether a floating point is infinite or non-numeric using the following method.

Expr is [not] NAN

Expr is [not] INFINITE

ORACLE can sort NAN. The sorting rule is that NAN is the largest. When NAN is compared with NAN, its value is equal. The value of infinity is equal to that of infinity.

[SQL]

SQL> create table tab1 (id number, c1 binary_float, c2 binary_double );

The table has been created.

SQL> insert into tab1 values (1, 1 );

One row has been created.

SQL> insert into tab1 values (2, nan, nan );

Insert into tab1 values (2, nan, nan)

*

Row 3 has an error:

ORA-00984: The column is not allowed here

SQL> insert into tab1 values (2, binary_float_nan, binary_double_nan );

One row has been created.

SQL> insert into tab1 values (3, binary_float_infinity, binary_double_infinity );

One row has been created.

SQL> insert into tab1 values (4,-binary_float_infinity,-binary_double_infinity );

One row has been created.

SQL> commit;

Submitted.

SQL> select * from tab1;

ID C1 C2

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

1 1.0E + 000 1.0E + 000

2 Nan

3 Inf

4-Inf

SQL> select * from tab1 order by c1;

ID C1 C2

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

4-Inf

1 1.0E + 000 1.0E + 000

3 Inf

2 Nan

SQL> select * from tab1 where c1 is infinite;

ID C1 C2

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

3 Inf

4-Inf

SQL> select * from tab1 where c1 is nan;

ID C1 C2

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

2 Nan

SQL> select * from tab1 where c1 = binary_float_infinity;

ID C1 C2

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

3 Inf

SQL> select * from tab1 where c1! = Binary_float_infinity;

ID C1 C2

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

1 1.0E + 000 1.0E + 000

2 Nan

4-Inf

SQL> select * from tab1 where c1 = (select c1 from tab1 where id = 2 );

ID C1 C2

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

2 Nan

SQL> select * from tab1 where c1 = nan;

Select * from tab1 where c1 = nan

*

Row 3 has an error:

The ORA-00904: "NAN": the identifier is invalid.

SQL> select * from tab1 where c1 = binary_float_nan;

ID C1 C2

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

2 Nan

SQL> select * from tab1 where c1 = (select c1 from tab1 where id = 3 );

ID C1 C2

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

3 Inf

SQL>

Priority: During data conversion, the data type is preferentially converted to the high-level data type.

Date type> binary_double> binary_float> number> char

Related Article

Contact Us

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.