Data Types In Oracle SQL Part2 - Numeric Data Types
In the last tutorial, we learned about Character Data Types in Oracle SQL. In this tutorial, we are going to learn about Numeric data types that oracle supports.
Numeric Data Types :
The data that can be stored in numeric data types can be zero (0), infinity, positive and negative fixed and floating-point numbers. You can also store values that are results of invalid operations, such as NAN or "not a number".
Numeric data types are categorized into 2 types
1. NUMBER Data Type.
2. FLOAT Data Type.
1. NUMBER Datatype :
1. It stores zero, positive and negative fixed and floating point numbers.
2. The range for positive numbers is from 1 x 10-130 to 9.99...9 x 10125 with up to 38 significant digits.
3. The range for negative numbers is from -1 x 10-130 to 9.99...99 x 10125 with up to 38 significant digits.
Syntax 1:
column_name NUMBER
syntax 2 :
column_name NUMBER(precision,scale)
4. precision specifies the total number of digits before the decimal point i.e 1 to 38.
5. scale specifies the number of digits to the right of the decimal point. It can range from -84 to 127.
syntax 3:
column_name NUMBER(*,scale)
for the above syntax, the oracle gives precision as 38, maintains the specified scale.
Tip : When you are specifying numeric fields, always specify the precision and scale, which provides extra integrity checks on input.
Let's see how Oracle database stores data in NUMBER fields for different scale factors.
1. If the value is 1234567.89 and data type is NUMBER, the value will be stored as "1234567.89".
2. If the value is 1234567.89 and data type is NUMBER(*,1), then the value will be stored as "1234567.9". Here Oracle database rounds the decimal part to nearest value, in this case it has rounded 0.89 to 0.9.
3. If the value is 1234567.89 and data type is NUMBER(9), then the value will be stored as "1234567". Here scale is 0. So no decimal part is stored.
4. If the value is 1234567.89 and data type is NUMBER(9,2), then the value will be stored as "1234567.89. Here scale is 2, so decimal part is preserved.
5. If the value is 1234567.89 and data type is NUMBER(9,1), then the value will be stored as "1234567.9. same as point 2.
6. If the value is 1234567.89 and data type is NUMBER(6), then Oracle returns an error, because the number exceeds the precision.
7. If the value is 1234567.89 and data type is NUMBER(7,-2), then the value will be stored as 1234600. Here Oracle database round the actual data to the specified number of places to the left of the decimal point. Here it rounded to nearest 100ths.
2. Float Data Type :
1. It specifies to have a decimal point anywhere from the first to the last digit, or can have no decimal point at all.
2. The scale value is not applicable to floating point numbers, as the number of digits that can appear after the decimal point is not restricted.
Syntax :
Float : It specifies a floating point number with decimal precision 38 or binary precision of 126. The maximum of 126 digits of binary precision is roughly equivalent to 38 digits of decimal precision.
Float(B) : It specifies a floating point number with binary precision B. The precision can range from 1 to 126. To convert from binary to decimal precision multiply 'B' by 0.30103. To convert from decimal to binary precision multiply the decimal precision by 3.32193.
Numeric Data Types :
The data that can be stored in numeric data types can be zero (0), infinity, positive and negative fixed and floating-point numbers. You can also store values that are results of invalid operations, such as NAN or "not a number".
Numeric data types are categorized into 2 types
1. NUMBER Data Type.
2. FLOAT Data Type.
1. NUMBER Datatype :
1. It stores zero, positive and negative fixed and floating point numbers.
2. The range for positive numbers is from 1 x 10-130 to 9.99...9 x 10125 with up to 38 significant digits.
3. The range for negative numbers is from -1 x 10-130 to 9.99...99 x 10125 with up to 38 significant digits.
Syntax 1:
column_name NUMBER
syntax 2 :
column_name NUMBER(precision,scale)
4. precision specifies the total number of digits before the decimal point i.e 1 to 38.
5. scale specifies the number of digits to the right of the decimal point. It can range from -84 to 127.
syntax 3:
column_name NUMBER(*,scale)
for the above syntax, the oracle gives precision as 38, maintains the specified scale.
Tip : When you are specifying numeric fields, always specify the precision and scale, which provides extra integrity checks on input.
Let's see how Oracle database stores data in NUMBER fields for different scale factors.
1. If the value is 1234567.89 and data type is NUMBER, the value will be stored as "1234567.89".
2. If the value is 1234567.89 and data type is NUMBER(*,1), then the value will be stored as "1234567.9". Here Oracle database rounds the decimal part to nearest value, in this case it has rounded 0.89 to 0.9.
3. If the value is 1234567.89 and data type is NUMBER(9), then the value will be stored as "1234567". Here scale is 0. So no decimal part is stored.
4. If the value is 1234567.89 and data type is NUMBER(9,2), then the value will be stored as "1234567.89. Here scale is 2, so decimal part is preserved.
5. If the value is 1234567.89 and data type is NUMBER(9,1), then the value will be stored as "1234567.9. same as point 2.
6. If the value is 1234567.89 and data type is NUMBER(6), then Oracle returns an error, because the number exceeds the precision.
7. If the value is 1234567.89 and data type is NUMBER(7,-2), then the value will be stored as 1234600. Here Oracle database round the actual data to the specified number of places to the left of the decimal point. Here it rounded to nearest 100ths.
2. Float Data Type :
1. It specifies to have a decimal point anywhere from the first to the last digit, or can have no decimal point at all.
2. The scale value is not applicable to floating point numbers, as the number of digits that can appear after the decimal point is not restricted.
Syntax :
Float : It specifies a floating point number with decimal precision 38 or binary precision of 126. The maximum of 126 digits of binary precision is roughly equivalent to 38 digits of decimal precision.
Float(B) : It specifies a floating point number with binary precision B. The precision can range from 1 to 126. To convert from binary to decimal precision multiply 'B' by 0.30103. To convert from decimal to binary precision multiply the decimal precision by 3.32193.
Comments
Post a Comment