Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
Basic Elements of Oracle SQL, 2 of 10
The terms literal and constant value are synonymous and refer to a fixed data value. For example, 'JACK', 'BLUE ISLAND', and '101' are all character literals; 5001 is a numeric literal. Note that character literals are enclosed in single quotation marks, which enable Oracle to distinguish them from schema object names.
Many SQL statements and functions require you to specify character and numeric literal values. You can also specify literals as part of expressions and conditions. You can specify character literals with the 'text' notation, national character literals with the N'text' notation, and numeric literals with the integer or number notation, depending on the context of the literal. The syntactic forms of these notations appear in the sections that follow.
Text specifies a text or character literal. You must use this notation to specify values whenever 'text' or char appear in expressions, conditions, SQL functions, and SQL statements in other parts of this reference.
The syntax of text is as follows:
text::=
where
A text literal must be enclosed in single quotation marks. This reference uses the terms text literal and character literal interchangeably.
Text literals have properties of both the CHAR
and VARCHAR2
datatypes:
CHAR
by comparing them using blank-padded comparison semantics. See "Blank-Padded Comparison Semantics".
Here are some valid text literals:
'Hello' 'ORACLE.dbs' 'Jackie''s raincoat' '09-MAR-98' N'nchar literal'
You must use the integer notation to specify an integer whenever integer appears in expressions, conditions, SQL functions, and SQL statements described in other parts of this reference.
The syntax of integer is as follows:
integer::=
where
digit |
is one of 0, 1, 2, 3, 4, 5, 6, 7, 8, 9. |
An integer can store a maximum of 38 digits of precision.
Here are some valid integers:
7 +255
You must use the number notation to specify values whenever number appears in expressions, conditions, SQL functions, and SQL statements in other parts of this reference.
The syntax of number is as follows:
number::=
where
A number can store a maximum of 38 digits of precision.
If you have established a decimal character other than a period (.) with the initialization parameter NLS_NUMERIC_CHARACTERS
, you must specify numeric literals with 'text' notation. In such cases, Oracle automatically converts the text literal to a numeric value.
For example, if the NLS_NUMERIC_CHARACTERS
parameter specifies a decimal character of comma, specify the number 5.123 as follows:
'5,123'
Here are some valid representations of number:
25 +6.34 0.5 25e-03 -1
An interval literal specifies a period of time. You can specify these differences in terms of years and months, or in terms of days, hours, minutes, and seconds. Oracle supports two types of interval literals, YEAR
TO
MONTH
and DAY
TO
SECOND
. Each type contains a leading field and may contain a trailing field. The leading field defines the basic unit of date or time being measured. The trailing field defines the smallest increment of the basic unit being considered. For example, a YEAR
TO
MONTH
interval considers an interval of years to the nearest month. A DAY
TO
MINUTE
interval considers an interval of days to the nearest minute.
If you have date data in numeric form, you can use the NUMTOYMINTERVAL or NUMTODSINTERVAL conversion function to convert the numeric data into interval literals.
Interval literals are used primarily with analytic functions.
Specify YEAR
TO
MONTH
interval literals using the following syntax:
where
YEAR
and the trailing field is MONTH
, the range of integer values for the month field is 0 to 11.
The leading field must be a larger time element than the trailing field. For example, INTERVAL
'0-1'
MONTH
TO
YEAR
is not valid.
The following INTERVAL
YEAR
TO
MONTH
literal indicates an interval of 123 years, 2 months:
INTERVAL '123-2' YEAR(3) TO MONTH
Examples of the other forms of the literal follow, including some abbreviated versions:
You can add or subtract one INTERVAL
YEAR
TO
MONTH
literal to or from another to yield another INTERVAL
YEAR
TO
MONTH
literal. For example:
INTERVAL '5-3' YEAR TO MONTH + INTERVAL '20' MONTH TO MONTH = INTERVAL '6-11' YEAR TO MONTH
Specify DAY
TO
SECOND
interval literals using the following syntax:
where
HH[:MI[:SS[.n]]]
or MI[:SS[.n]]
or SS[.n]
, where n specifies the fractional part of a second. If n contains more digits than the number specified by fractional_seconds_precision, then n is rounded to the number of digits specified by the fractional_seconds_precision value. You can specify time_expr following an integer and a space only if the leading field is DAY
.
DAY
date field. Accepted values are 0 to 9. The default is 2.
SECOND
datetime field. Accepted values are 1 to 9. The default is 6.
The leading field must be a larger time element than the trailing field. For example, INTERVAL
MINUTE
TO
DAY
is not valid. As a result of this restriction, if SECOND
is the leading field, the interval literal cannot have any trailing field.
The valid range of values for the trailing field are as follows:
|
0 to 23 |
|
0 to 59 |
|
0 to 59.999999999 |
Examples of the various forms of INTERVAL
DAY
TO
SECOND
literals follow, including some abbreviated versions:
You can add or subtract one DAY
TO
SECOND
interval literal from another DAY
TO
SECOND
literal. For example.
INTERVAL '20' DAY - INTERVAL '240' HOUR = INTERVAL '10' DAY
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|