Numeric data types - SQL

We already explained that when creating a table we must choose the appropriate structure, that is, define the fields and their most precise types, as appropriate.

Numeric values ​​are not enclosed in quotes. The point is used as a decimal point.

To store NUMERIC values ​​Oracle has two types of data:

#1. number (t, d): to store integer or decimal values, positive or negative. Its range is from 1.0 x 10-130 to 9,999 ... (38 nines). We define fields of this type when we want to store numerical values ​​with which we will then perform mathematical operations, for example, quantities, prices, etc.

The parameter "t" indicates the total number of digits (counting the decimals) that the maximum number will contain (it is the precision). Its range is from 1 to 38. The parameter "d" indicates the maximum number of decimal digits (scale). The scale can go from -84 to 127. To define integers, you can omit the "d" parameter or put a 0.

A defined field "number (5,2)" can contain any number between -999.99 and 999.99.

To specify integers, we can omit the parameter "d" or put the value 0.

If we try to store a larger value outside the range allowed when defining it, such value is not loaded, a message appears indicating such situation and the sentence is not executed.

For example, if we define a field of type "number (4,2)" and try to save the value 123.45, a message appears indicating that the value is too large for the column. If we enter a value with more decimal places than defined, the value is loaded but with the number of decimal places allowed, the remaining digits are ignored.

#2. binary_float and binary_double: store floating numbers more precisely:

Value BINARY_FLOAT BINARY_DOUBLE

Maximum positive finite value 3.40282E + 38F 1.79769313486231E + 308

Minimum positive finite value 1.17549E-38F 2.22507485850720E-308

For both numeric types:

• if we enter a value with more decimal places than allowed, round to the nearest.
• if we try to enter a value out of range, it does not accept it.
• if we enter a string, Oracle tries to convert it to a numeric value, if said string consists only of digits, the conversion is carried out, then it checks if it is within the range, if so, it enters it, otherwise, it shows an error message and not execute the sentence. If the string contains characters that Oracle cannot convert to a numeric value, it displays an error message and the statement is not executed.

For example, we define a field of type "numberl (5,2)", if we enter the string '12 .22 ', it converts it to the numeric value 12.22 and enters it; if we try to enter the string '1234.56', it converts it to the numeric value 1234.56, but since the maximum allowed value is 999.99, it shows a message indicating that it is out of range. If we try to enter the value '12y.25', Oracle cannot convert and displays an error message.