The kinds of implicitly-typed constants in Advanced Server are strings and numbers. Constants can also be specified with explicit types, which can enable more accurate representation and more efficient handling by the system. These alternatives are discussed in the following subsections.
A string constant in SQL is an arbitrary sequence of characters bounded by single quotes
('), for example
'This is a string'. To include a single-quote character within a string constant, write two adjacent single quotes, e.g.
'Dianne''s horse'. Note that this is not the same as a double-quote character
Numeric constants are accepted in these general forms:
digits is one or more decimal digits (0 through 9). At least one digit must be before or after the decimal point, if one is used. At least one digit must follow the exponent marker
(e), if one is present. There may not be any spaces or other characters embedded in the constant. Note that any leading plus or minus sign is not actually considered part of the constant; it is an operator applied to the constant.
These are some examples of valid numeric constants:
A numeric constant that contains neither a decimal point nor an exponent is initially presumed to be type
INTEGER if its value fits in type
INTEGER (32 bits); otherwise it is presumed to be type
BIGINT if its value fits in type
BIGINT (64 bits); otherwise it is taken to be type
NUMBER. Constants that contain decimal points and/or exponents are always initially presumed to be type
The initially assigned data type of a numeric constant is just a starting point for the type resolution algorithms. In most cases the constant will be automatically coerced to the most appropriate type depending on context. When necessary, you can force a numeric value to be interpreted as a specific data type by casting it as described in the following section.
A constant of an arbitrary type can be entered using the following notation:
The string constant’s text is passed to the input conversion routine for the type called
type. The result is a constant of the indicated type. The explicit type cast may be omitted if there is no ambiguity as to the type the constant must be (for example, when it is assigned directly to a table column), in which case it is automatically coerced.
CAST can also be used to specify runtime type conversions of arbitrary expressions.
MULTISET is an extension to
CAST that converts subquery results into a nested table type. The synopsis is:
subquery is a query returning one or more rows and
datatype is a nested table type.
CAST(MULTISET) is used to store a collection of data in a table.
The following example demonstrates using