Oracle also provides a number of numeric subtypes. Most of the time, these subtypes are simply alternate names for the three basic types we've just discussed. These alternate names offer compatibility with ANSI SQL, SQL/DS, and DB2 datatypes, and usually have the same range of legal values as their base type. Sometimes, subtypes offer additional functionality by restricting values to a subset of those supported by their base type. These subtypes are described in Table 9-4.
| Table 9-4. Predefined numeric subtypes | ||
| Subtype | Compatibility | Corresponding Oracle datatype/notes |
| DEC (precision, scale) | ANSI | NUMBER (precision, scale) |
| DECIMAL (precision, scale) | IBM | NUMBER (precision, scale) |
| DOUBLE PRECISION | ANSI | NUMBER |
| FLOAT | ANSI, IBM | NUMBER |
| FLOAT (binary_precision) | ANSI, IBM | NUMBER, but NUMBERs can't be declared using binary precision |
| INT | ANSI | NUMBER |
| INTEGER | ANSI, IBM | NUMBER |
| NATURAL | N/A | BINARY_INTEGER, but allows only non-negative values (0 and higher) |
| NATURALN | N/A | Same as NATURAL, but with the additional restriction of never being NULL |
| NUMERIC (precision, scale) | ANSI | NUMBER (precision, scale) |
| POSITIVE | N/A | BINARY_INTEGER, but allows only positive values (1 and higher) |
| POSITIVEN | N/A | Same as POSITIVE, but with the additional restriction of never being NULL |
| REAL | ANSI | NUMBER |
| SIGNTYPE | N/A | BINARY_INTEGER, limited to the values -1, 0, and 1 |
| SMALLINT | ANSI, IBM | NUMBER (38) |
The NUMERIC, DECIMAL, and DEC datatypes can declare only fixed-point numbers. DOUBLE PRECISION and REAL are equivalent to NUMBER. FLOAT allows floating decimal points with binary precisions that range from 63 to 126 bits. We don't find it all that useful to define a number's precision in terms of bits rather than digits. We also don't find much use for the ANSI/IBM compatible subtypes, and we don't believe you will either.
The subtypes that we do sometimes find useful are the BINARY_INTEGER subtypes. NATURAL and POSITIVE are both subtypes of BINARY_INTEGER. These subtypes constrain the values you can store in a variable, and their use can make a program more self-documenting. For example, if you have a variable whose values must always be non-negative, you could declare that variable to be NATURAL (0 and higher) or POSITIVE (1 and higher), improving the self-documenting aspect of your code. Bear in mind, though, that you may get better performance out of PLS_INTEGER than out of BINARY_INTEGER and its subtypes.
9.2 Number Conversions
Computers work with numbers best when those numbers are in some sort of binary format. We humans, on the other hand, prefer to see our numbers in the form of character strings containing digits, commas, and other punctuation. PL/SQL allows you to convert numbers back and forth between human- and machine-readable form. Most commonly, you'll perform such conversions using the TO_CHAR and TO_NUMBER functions. In order to use these functions to their fullest extent, you need to understand number format models.
