Databases Reference
In-Depth Information
Oracle supports three numeric data types:
•
NUMBER
•
BINARY_DOUBLE
BINARY_FLOAT
For most situations, you'll use the
NUMBER
data type for any type of number data. Its syntax is
•
NUMBER(scale, precision)
where
scale
is the total number of digits, and
precision
is the number of digits to the right of the decimal point.
So, with a number defined as
NUMBER(5, 2)
you can store values +/-999.99. That's a total of five digits, with two used
for precision to the right of the decimal point.
Ti
■
Oracle allows a maximum of 38 digits for a
NUMBER
data type. this is almost always sufficient for any type of
numeric application.
What sometimes confuses DBAs is that you can create a table with columns defined as
INT
,
INTEGER
,
REAL
,
DECIMAL
, and so on. These data types are all implemented by Oracle with a
NUMBER
data type. For example, a column
specified as
INTEGER
is implemented as a
NUMBER(38)
.
The
BINARY_DOUBLE
and
BINARY_FLOAT
data types are used for scientific calculations. These map to the
DOUBLE
and
FLOAT
Java data types. Unless your application is performing rocket science calculations, then use the
NUMBER
data
type for all your numeric requirements.
Date/Time
When capturing and reporting on date-related information, you should always use a
DATE
or
TIMESTAMP
data type
(and not
VARCHAR2
). Using the correct date-related data type allows you to perform accurate Oracle date calculations
and aggregations and dependable sorting for reporting. If you use a
VARCHAR2
for a field that contains date
information, you are guaranteeing future reporting inconsistencies and needless conversion functions (such as
TO_DATE
and
TO_CHAR
).
Oracle supports three date-related data types:
DATE
•
TIMESTAMP
•
INTERVAL
The
DATE
data type contains a date component as well as a time component that is granular to the second. By default,
if you don't specify a time component when inserting data, then the time value defaults to midnight (0 hour at the 0 second).
•
If you need to track time at a more granular level than the second, then use
TIMESTAMP
; otherwise, feel free to use
DATE
.
The
TIMESTAMP
data type contains a date component and a time component that is granular to fractions of a
second. When you define a
TIMESTAMP
, you can specify the fractional second precision component. For instance,
if you wanted five digits of fractional precision to the right of the decimal point, you would specify that as
TIMESTAMP(5)
The maximum fractional precision is 9; the default is 6. If you specify 0 fractional precision, then you have the
equivalent of the
DATE
data type.
