 |
|
EnterpriseDB: NUMERIC Data
Oracle Tips by Burleson Consulting
|
In
EnterpriseDB, we have three primary numeric data types: integer,
number and floating point. OID is a special numeric type.
NUMBER
A
NUMBER stores optionally scaled, optionally precise numbers.
That means that as the programmer, you can define the precision and
scale of the number
Precision is the number of important digits in the entire number.
Scale is the number of important digits to the right of the decimal.
For
example, you can declare a number as:
|
NUMBER TYPE |
DESCRIPTION |
|
v_num_field NUMBER |
This would allow any precision of data, precision and scale are
left open |
|
v_num_field NUMBER(5,2) |
This would allow numbers like 123.45, precision is 5 and scale
is 2 |
|
v_num_field NUMBER(3,0) |
This would allow numbers like 123 or 456, precision is 3 and
scale is 0 |
|
v_num_field NUMBER(3) |
Same as NUMBER(3,0) |
|
v_num_field NUMBER(3,3) |
This would allow numbers like .123 or .456, precision and scale
are both 3 |
There
really is very little difference between Oracle's NUMBER and
EnterpriseDB's NUMBER. EnterpriseDB's NUMBER does allow for
greater precision but it does not allow a negative scale.
Synonyms for NUMBER are:
*
NUMERIC
* DECIMAL
* DEC
INTEGER
An
integer is a whole number between -2 billion and +2 billion.
An integer maps to an Oracle BINARY_INTEGER and a PLS_INTEGER. In
Oracle, these two data types are only available in PL/SQL and not in
SQL (which means you cannot have them as a column data type).
EnterpriseDB supports two additional integers that do not map to any
Oracle data type. A TINYINT (or SMALLINT) can be between -32k
and +32k. A BIGINT can be huge! A TINYINT is 2 bytes, an
INTEGER is 4 bytes and a BIGINT is 8 bytes. That means it can
store whole numbers between -a gazillion and +a gazillion. Or
something like that anyway.
An
integer would be used for columnar data like an ID, a primary key,
an age, or other information that you wish to store as a whole
number. In Oracle, most developers would put this kind of data
in a NUMBER field. For compatibility, I would recommend using
a NUMBER in EnterpriseDB also.
Having
said that, an INTEGER is much faster than a NUMBER. If you
have a performance critical piece of code or table that uses whole
numbers, you may want to use INTEGER instead of NUMBER.
Make a note though that you reduce your cross platform compatibility
by doing so.
Synonyms for INTEGER are:
* INT
* INT4
Synonyms for TINYINT are:
·
SMALLINT
Synonyms for BIGINT are:
·
INT8
BINARY_FLOAT
and BINARY_DOUBLE
Oracle
provides two very efficient floating-point numbers, BINARY_FLOAT and
BINARY_DOUBLE. BINARY_FLOAT is a 32-bit binary number and
BINARY_DOUBLE is a 64-bit floating-point number.
In
EnterpriseDB, we do not have these data types as of yet. What
we do have are REAL and DOUBLE PRECISION. These data types are
not as precise as Oracle's floating-point implementation and are not
nearly as precise as a NUMBER.
REAL
and DOUBLE PRECISION, like Oracle's BINARY_FLOAT and BINARY_DOUBLE,
are much faster than a NUMBER. If you have a performance
critical piece of code or table that uses floating point numbers,
you may want to use one of these instead of NUMBER. The
important thing to remember is that these data types are not as
precise and may use approximations. These are not suitable for
financial applications.
EnterpriseDB and Oracle both offer the FLOAT data type. A
float is declared as FLOAT(n) with n being the number of digits of
precision required or as FLOAT. FLOAT without an (n) is
assumed to mean the maximum allowed. In EnterpriseDB, (n) can
be between (1) and (53). Anything over (53) generates an
error. EnterpriseDB will decide whether to implement the FLOAT
as a REAL
or
a
DOUBLE PRECISION based on the value of (n).
For
maximum compatibility, I would recommend using a FLOAT or FLOAT(n)
for floating point numbers when speed is critical and precision is
not. For all other cases, I would recommend using NUMBER.
Synonyms for FLOAT are:
*
SMALLFLOAT
* REAL
* DOUBLE PRECISION
This
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress.
