BINARY_FLOAT/BINARY_DOUBLE Type Syntax and Usage-Datatypes
The BINARY_FLOAT and BINARY_DOUBLE are the IEEE standard floating-points many programmers are used to working with. For a full description of what these number types look like and how they are implemented, I suggest reading http://en.wikipedia.org/ wiki/Floating-point. It is interesting to note the following in the basic definition of a floating-point number in that reference (emphasis mine):
A floating-point number is a digital representation for a number in a certain subset of the rational numbers, and is often used to approximate an arbitrary real number on a computer. In particular, it represents an integer or fixed-point number (the significand or, informally, the man-tissa) multiplied by a base (usually 2 in computers) to some integer power (the exponent). When the base is 2, it is the binary analogue of scientific notation (in base 10).
They are used to approximate numbers; they are not nearly as precise as the built-in Oracle NUMBER type described previously. Floating-point numbers are commonly used in scientific applications and are useful in many types of applications due to the fact that they allow arithmetic to be done in hardware (on the CPU, the chip) rather than in Oracle subroutines.
Therefore, the arithmetic is much faster if you are doing real number crunching in a scientific application, but you would not want to use floating-points to store financial information. For example, suppose you wanted to add together 0.3 and 0.1 as floats.
You might think the answer is of course 0.4. You would be wrong (in floating-point arithmetic). The answer is a little bit larger than 0.4:
$ sqlplus eoda/foo@PDB1
SQL> select to_char( 0.3f + 0.1f, ‘0.99999999999999’ ) from dual;TO_CHAR(0.3F+0.1F
This is not a bug, this is the way IEEE floating-point numbers work. As a result, they are useful for a certain domain of problems, but definitely not for problems where dollars and cents count!
The syntax for declaring columns of this type in a table is very straightforward:
BINARY_FLOAT
BINARY_DOUBLE
That is it. There are no options to these types whatsoever.
Non-native Number Types
In addition to the NUMBER, BINARY_FLOAT, and BINARY_DOUBLE types, Oracle syntactically supports the following numeric datatypes:
•\ NUMERIC(p,s): Maps exactly to a NUMBER(p,s). If p is not specified, it defaults to 38.
•\ DECIMAL(p,s) or DEC(p,s): Maps exactly to a NUMBER(p,s). If p is not specified, it defaults to 38.
•\ INTEGER or INT: Maps exactly to the NUMBER(38) type.
•\ SMALLINT: Maps exactly to the NUMBER(38) type.
•\ FLOAT(p): Maps to the NUMBER type.
•\ DOUBLE PRECISION: Maps to the NUMBER type.
•\ REAL: Maps to the NUMBER type.
Note When I say “syntactically supports,” I mean that a CREATE statement may use these datatypes, but under the covers they are all really the NUMBER type. There are precisely three native numeric formats. The use of any other numeric datatype is always mapped to the native Oracle NUMBER type.