| Contents |
|
|||||
| Reading Material |
In addition to the data types presented above, SQL-99 offers arrays,
object references and user defined data types. Here, we will,
however, concentrate on the SQL-92 data types.
SQL has six data types to present textual data:
| Data type | SQL-specification syntax |
| fixed length text | character[(size)] char[(size)] |
| varying length text | character varying [(size)] varchar[(size)] |
| long text | character large object (size) CLOB (size) |
| fixed length text based on national character set | national character [(size)] nchar [(size)] |
| varying length text based on national character set | national character varying [(size)] nchar varying [(size)] |
| long text based on national character set | national large character object (size) nclob (size) |
The size tells us either the length of the character string or the maximum
length in characters. If no size is defined, the text data is, by default, one
character
long. The maximum lengths vary according to the database management system. In the Oracle 8 system,
for example, long text has to be used if the character string exceeds
4,000 characters. The maximum length of a fixed length text in Oracle is
2,000 characters. In Oracle 8, the maximum length of a long text is 4 GB. Long text cannot be used in
comparisons in Oracle. In some other systems, this is
possible.
The national character set based strings may be used when special behavior
for characters is needed and the proper character set is available (e.g. Chinese,
Japanese). National character sets may have fixed length or variable length
characters. Use of national character set affects on ordering and comparisons.
'Normal' characters and characters in a national character set are not comparable.
For data of a fixed length, space is reserved according to length, for data
of varying length, according to need
Text literals are closed in apostrophes:
| address | varchar(80) | 'Park Street,5, Helsinki' |
| thesis_text | clob(400K) | 'Introduction\n Databases are...' |
There are three data types for presenting binary information, such as
images.
| Data type | SQL syntax |
| fixed length bit vector | bit[(size)] |
| varying length bit vector | bit varying [(size)] |
| large binary data | binary large object (size) BLOB (size) |
The size of bit vectors are given as the number of bits, the size of large binary data
as 8-bit bytes .The upper limits for the size vary according to implementation.
The bit vector literals can be presented as bits (B) or as hexadecimal
characters (X).
| holidaymap | bit(365) | B'10010000000...' |
| small_gif_image | blob(100K) | X'A09FF00A11FFF....' |
There are four data types to present exact numerical data.
| Data type | SQL syntax |
| small integer (2 bytes) | smallint |
| integer (4 bytes) | integer, int |
| number | numeric [(precision[,scale])] |
| decimal number | decimal [(precision[,scale])]
dec [(precision[,scale])] |
Of the size data to be linked to numerical values, precision expresses the
total number of number positions of the value, and scale expresses the
length of the decimal part. Scale cannot be larger than precision. In the
absence of lengths, implementation specific defaults are used.
For the inexact numeric data , there are three data
types that are all based on presenting the mantissa and the exponent separately.
| Data type | SQL syntax |
| float with definable precision | float (precision) |
| float with implementation specific precision | real |
| double precision | double precision |
The precision of the float is specifiable within the limits determined by the implementation. Double precision numbers are at least as precise as the normal floats.
Numeric literals are presented with the annotation known from many programming
languages.
| small change, less than 1,000 | decimal(5,2) | 320.15 |
| generated identifier | numeric(20) | 123456 |
| oscillation frequency | double precision | 123E2 |
There are four data types to present time data:
| Data type | SQL syntax |
| date | date |
| time (without date) | time[(precision)] |
| date and time | timestamp[(precision)] |
| interval | interval |
The date only contains the date but no time. The timestamp contains both time of day and
date. The precision of the time indicates how many decimals are given for the
seconds. The default for time is no decimals, i.e. the times are given
as seconds. In the timestamp, the default is 6, i.e. the precision
is a millionth of a second.
Times and dates can be used in computations. Let us assume that to_day is today's
date. In that case, to_day+1 would give us tomorrow's date, and
'2003-12-31'-to_day would give us how many days it is from this day until
the end of the year 2003. Let now be the timestamp of the current
instance,
then now+ TIME 6:00 is 6 hours from this instant. The comparison of time data is
based on chronology, so '2002-10-1' is larger than '2002-9-30'.
| Data type date in Oracle corresponds the data type timestamp of the standard with fixed precision. Oracle does not provide separate times and dates. Oracle date may also be used as an interval. Actually it is always an interval where the whole part indicates the number of days since a beginning of the calendar and the decimal part gives the time that exceeds full days. |
The standard presentation of a date literal is DATE 'YYYY-MM-DD'. Time
literals are expressed as TIME 'HH:MI:SS.FFFFFF' and the timestamps as TIMESTAMP
'YYYY-MM-DD HH:MI:SS:FFFFFF'. Many systems make it possible to represent times
without preceding keywords. In Oracle, the date format may be controlled by
configuration parameters.
| date of birth | date | DATE '1998-10-30' |
| F1 circuit time - with three decimals | time(3) | TIME '1:24.301' |
| registration time | timestamp(0) | TIMESTAMP '2001-04-15 13:10:12' |
There is the data type boolean to present the truth values.
| degree_acquired | boolean | TRUE |