In order to write proper SQL
statements, familiarity with database objects (tables, views, constraints) are
essential.
One general rule to follow when
you are writing SQL statements is that data types cannot be mixed. Conversion
functions are available to convert from one type to another.
Numeric
The NUMBER data type is used
to store zero, negative, positive, fixed and floating point numbers with up to
38 digits of precision. Numbers range between 1.0x10 -130 and 1.0x10
126.
Numbers can be defined in
one of three ways:
NUMBER(p,s)
where p is the
precision up to 38 digits and s is the scale (number of digits to the
right of the decimal point). The scale can range between -84 to 127.
NUMBER (p)
This is a fixed-point number
with a scale of zero and a precision of p.
NUMBER
This is a floating-point
number with a precision of 38.
The following table shows
how Oracle stores different scales and precisions:
|
Actual Data
|
Defined as
|
Stored as
|
|
123456.789
|
NUMBER(6,2)
|
123456.79
|
|
123456.789
|
NUMBER(6)
|
123457
|
|
123456.789
|
NUMBER(6,-2)
|
123400
|
|
123456.789
|
NUMBER
|
123456.789
|
Date
Instead of storing date and
time information in a character or numeric format, IBM created a separate data
type. For each DATE data type, the following information is stored:
Century, Year, Month, Day, Hour,
Minute, Seconds
You can easily retrieve the
current date and time by using the function SYSDATE.
Date arithmetic is possible
using number constants or other dates. Only addition and subtraction are
supported. For example, SYSDATE + 7 will return one week from today.
Every database system has a
default date format that is usually set to DD-MON-YY, where DD is the day of
the month (the first day of the month is 01), MON is the abbreviated month
name, and YY is a two-digit year designation.
Character
There are four character
types available:
The CHAR data type is used
where fixed-length fields are necessary. Any length up to 255 characters can be
specified. The default length is 1. When data is entered, any space left over
will be filled with blanks. All alpha-numeric characters are allowed.
The VARCHAR2 is used for
variable-length fields. A length component must be supplied when you use this
data type. The maximum length is 2000 characters. All alpha-numeric characters
are allowed.
The LONG data type is used
to store large amounts of variable-length text. Any length up to 2 GB can be
specified. Be aware that there are some restrictions to using this data type,
such as:
Only one column per table
can be defined as LONG.
A LONG column cannot be
indexed.
A LONG column cannot be
passed as an argument to a procedure.
A function cannot be used to
return a LONG column.
A LONG column cannot be used
in where, order by or group by clauses.
The VARCHAR data type is
synonymous with VARCHAR2. Oracle Corporation is reserving this for future use.
Binary
Two data types, RAW and
LONGRAW, are available for storing binary type data such as digitized sound and
images. These data types take on similar characteristics as the VARCHAR2 and
LONG data types already mentioned.
Use the RAW data type to
store binary data up to 2000 characters and use the LONGRAW data type to store
binary data up to 2 GB.
Oracle only stores and
retrieves binary data, no string manipulations are allowed. Data is retrieved
as hexadecimal character values.
No comments:
Post a Comment