Constants¶
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.
String Constants¶
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¶
Numeric constants are accepted in these general forms:
digitsdigits.[digits][e[+-]digits][digits].digits[e[+-]digits]digitse[+-]digitswhere 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:
42
3.5
4.
.001
5e2
1.925e-3
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 NUMBER.
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.
Constants of Other Types¶
CAST
A constant of an arbitrary type can be entered using the following notation:
CAST('string' AS type)
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.
CAST (MULTISET)
MULTISET is an extension to CAST that converts subquery results into a
nested table type. The synopsis is:
CAST ( MULTISET ( < subquery > ) AS < datatype > )
Where 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.
Example
The following example demonstrates using MULTISET:
edb=# CREATE OR REPLACE TYPE project_table_t AS TABLE OF VARCHAR2(25);
CREATE TYPE
edb=# CREATE TABLE projects (person_id NUMBER(10), project_name VARCHAR2(20));
CREATE TABLE
edb=# CREATE TABLE pers_short (person_id NUMBER(10), last_name VARCHAR2(25));
CREATE TABLE
edb=# INSERT INTO projects VALUES (1, 'Teach');
INSERT 0 1
edb=# INSERT INTO projects VALUES (1, 'Code');
INSERT 0 1
edb=# INSERT INTO projects VALUES (2, 'Code');
INSERT 0 1
edb=# INSERT INTO pers_short VALUES (1, 'Morgan');
INSERT 0 1
edb=# INSERT INTO pers_short VALUES (2, 'Kolk');
INSERT 0 1
edb=# INSERT INTO pers_short VALUES (3, 'Scott');
INSERT 0 1
edb=# COMMIT;
COMMIT
edb=# SELECT e.last_name, CAST(MULTISET(
edb(# SELECT p.project_name
edb(# FROM projects p
edb(# WHERE p.person_id = e.person_id
edb(# ORDER BY p.project_name) AS project_table_t)
edb-# FROM pers_short e;
last_name | project_table_t
-----------+-----------------
Morgan | {Code,Teach}
Kolk | {Code}
Scott | {}
(3 rows)
