ISO/IEC 9075-1:1999

INTERNATIONAL ISO/IEC
STANDARD 9075-1
First edition
1999-12-01
Information technology — Database
languages — SQL —
Part 1:
Framework (SQL/Framework)
Technologies de l’information— Langages de base de données— SQL —
Partie 1: Charpente (SQL/Charpente)
Reference number
©
ISO/IEC 1999
PDF disclaimer
This PDF file may contain embedded typefaces. In accordance with Adobe's licensing policy, this file may be printed or viewed but shall not
be edited unless the typefaces which are embedded are licensed to and installed on the computer performing the editing. In downloading this
file, parties accept therein the responsibility of not infringing Adobe's licensing policy. The ISO Central Secretariat accepts no liability in this
area.
Adobe is a trademark of Adobe Systems Incorporated.
Details of the software products used to create this PDF file can be found in the General Info relative to the file; the PDF-creation parameters
were optimized for printing. Every care has been taken to ensure that the file is suitable for use by ISO member bodies. In the unlikely event
that a problem relating to it is found, please inform the Central Secretariat at the address given below.
© ISO/IEC 1999
All rights reserved. Unless otherwise specified, no part of this publication may be reproduced or utilized in any form or by any means, electronic
or mechanical, including photocopying and microfilm, without permission in writing from either ISO at the address below or ISO's member body
in the country of the requester.
ISO copyright office
Case postale 56 � CH-1211 Geneva 20
Tel. + 41 22 749 01 11
Fax + 41 22 734 10 79
E-mail copyright@iso.ch
Web www.iso.ch
Printed in Switzerland
i-2 © ISO/IEC 1999 – All rights reserved

© ISO/IEC 1999 – All rights reserved i-3

Contents Page
Foreword . vii
Introduction . . . . ix
1 Scope . 1
2 Normative references. 3
3 Definitions and use of terms . 5
3.1 Definitions . 5
3.1.1 Definitions provided in this standard . . . . 5
3.2 Use of terms. 6
3.3 Informative elements . 7
4 Concepts. 9
4.1 Caveat . . 9
4.2 SQL-environments and their components . 9
4.2.1 SQL-environments . 9
4.2.2 SQL-agents . 9
4.2.3 SQL-implementations . 9
4.2.3.1 SQL-clients . . 10
4.2.3.2 SQL-servers . . 10
4.2.4 SQL-client modules . 10
4.2.5 User identifiers . . . 10
4.2.6 Catalogs and schemas . 10
4.2.6.1 Catalogs . 11
4.2.6.2 SQL-schemas . 11
4.2.6.3 The Information Schema . 11
4.2.6.4 The Definition Schema . . . 11
4.2.7 SQL-data . 11
4.3 Tables . . . 11
4.4 SQL data types . . . 12
4.4.1 General data type information . 12
4.4.2 The null value . . . . 13
4.4.3 Predefined types . . . 13
4.4.3.1 Numeric types . 13
4.4.3.2 String types . . 13
ii Framework (SQL/Framework)
©ISO/IEC ISO/IEC 9075-1:1999 (E)
4.4.3.3 Boolean type . 14
4.4.3.4 Datetime types. 14
4.4.3.5 Interval types. 14
4.4.4 Constructed atomic types. 14
4.4.4.1 Reference types . 14
4.4.5 Constructed composite types . . 14
4.4.5.1 Collection types . 15
4.4.5.2 Row types . . . . 15
4.4.5.3 Fields . 15
4.5 Sites and operations on sites . . 15
4.5.1 Sites . . . . 15
4.5.2 Assignment . 15
4.5.3 Nullability . 15
4.6 SQL-schema objects . 16
4.6.1 General SQL-schema object information . 16
4.6.2 Descriptors relating to character sets . . . 16
4.6.2.1 Character sets . 16
4.6.2.2 Collations . . . . 17
4.6.2.3 Translations . . 17
4.6.3 Domains and their components . 17
4.6.3.1 Domains. 17
4.6.3.2 Domain constraints . 17
4.6.4 User-defined types . 18
4.6.4.1 Structured types . 18
4.6.4.2 Attributes . . . . 18
4.6.5 Distinct types. 18
4.6.6 Base tables and their components . 18
4.6.6.1 Base tables . . . 18
4.6.6.2 Columns. 18
4.6.6.3 Table constraints . 19
4.6.6.4 Triggers . 19
4.6.7 View definitions . . . 19
4.6.8 Assertions . 20
4.6.9 SQL-server modules (defined in ISO/IEC 9075-4, SQL/PSM) . . 20
4.6.10 Schema routines . . . 20
4.6.11 Privileges . 20
4.6.12 Roles . . . 20
4.7 Integrity constraints and constraint checking . 20
4.7.1 Constraint checking . 21
4.7.2 Determinism and constraints . . 21
4.8 Communication between an SQL-agent and an SQL-implementation . . . . 21
4.8.1 Host languages . . . 21
4.8.2 Parameter passing and data type correspondences . 22
4.8.2.1 General parameter passing and data type correspondence information . 22
4.8.2.2 Data type correspondences. 22
Contents iii
4.8.2.3 Locators . 22
4.8.2.4 Status parameters . 23
4.8.2.5 Indicator parameters . . . . 23
4.8.3 Descriptor areas (defined in ISO/IEC 9075-5) . 24
4.8.4 Diagnostic information. 24
4.8.5 SQL-transactions . . 24
4.9 Modules . 25
4.10 Routines. 25
4.10.1 General routine information . . 25
4.10.2 Type preserving functions . . . . 26
4.10.3 Built-in functions . . 26
4.11 SQL-statements . . . 26
4.11.1 Classes of SQL-statements . . . . 26
4.11.2 SQL-statements classified by function . . . 26
5 The parts of ISO/IEC 9075 . 29
5.1 Overview . 29
5.2 ISO/IEC 9075-1: Framework (SQL/Framework) . . . 29
5.3 ISO/IEC 9075-2: Foundation (SQL/Foundation) . . . . 29
5.3.1 Data types specified in ISO/IEC 9075-2 . . 29
5.3.2 Tables . . . 30
5.3.3 SQL-statements specified in ISO/IEC 9075-2 . 30
5.4 ISO/IEC 9075-3: Call Level Interface (SQL/CLI) . . . 30
5.5 ISO/IEC 9075-4: Persistent Stored Modules (SQL/PSM) . 31
5.5.1 SQL-statements specified in ISO/IEC 9075-4 . 31
5.6 ISO/IEC 9075-5: Host Language Bindings (SQL/Bindings) . . . 31
5.6.1 SQL-session facilities . 32
5.6.2 Dynamic SQL. 32
5.6.3 Embedded SQL . . . 32
5.6.4 Direct invocation of SQL . 32
5.6.5 SQL-statements specified in ISO/IEC 9075-5 . 32
5.6.5.1 Additional functional classes of SQL-statements . 32
6 Notation and conventions used in other parts of ISO/IEC 9075. 35
6.1 Notation. 35
6.2 Conventions . 36
6.2.1 Specification of syntactic elements. 36
6.2.2 Specification of the Information Schema . 37
6.2.3 Use of terms. 37
6.2.3.1 Exceptions . . . 37
6.2.3.2 Syntactic containment . . . 37
6.2.3.3 Terms denoting rule requirements . . 38
6.2.3.4 Rule evaluation order . . . . 39
6.2.3.5 Conditional rules . 39
6.2.3.6 Syntactic substitution . . . 40
6.2.3.7 Other terms . . 40
iv Framework (SQL/Framework)
©ISO/IEC ISO/IEC 9075-1:1999 (E)
6.2.4 Descriptors. 41
6.2.5 Relationships of incremental parts to ISO/IEC 9075-2, Foundation . 42
6.2.5.1 New and modified Clauses, Subclauses, and Annexes . . . 42
6.2.5.2 New and modified Format items . . . 43
6.2.5.3 New and modified paragraphs and rules . 43
6.2.5.4 New and modified tables . 44
6.2.6 Index typography . . 44
6.3 Object identifier for Database Language SQL . 44
7 Annexes to the parts of ISO/IEC 9075 . 47
7.1 Implementation-defined elements . 47
7.2 Implementation-dependent elements . . . . 47
7.3 Deprecated features . 47
7.4 Incompatibilities with previous versions . 47
8 Conformance . 49
8.1 Requirements for SQL-implementations . 49
8.1.1 Parts and packages . 49
8.1.2 Functionality . 49
8.1.3 Additional features . 49
8.1.4 SQL flagger . 50
8.1.5 Claims of conformance . 51
8.2 Requirements for SQL applications . 51
8.2.1 Introduction . 51
8.2.2 Requirements. 51
8.2.3 Claims of conformance . 52
Annex A Maintenance and interpretation of SQL . 53
Annex B SQL Packages. 55
B.1 Enhanced datetime facilities . . 55
B.2 Enhanced integrity management . 56
B.3 OLAP facilities . . . . 56
B.4 PSM . 56
B.5 CLI. 57
B.6 Basic object support . 57
B.7 Enhanced object support . 57
B.8 Active database . . . 58
B.9 SQL/MM support . . 58
Annex C Implementation-defined elements . 59
Annex D Implementation-dependent elements . 61
Index . 63
Contents v
TABLES
Tables Page
1 Relationships of routine characteristics . . 25
2 SQL Packages . . . . 55
vi Framework (SQL/Framework)
©ISO/IEC ISO/IEC 9075-1:1999 (E)
Foreword
ISO (the International Organization for Standardization) and IEC (the International Electrotechnical
Commission) form the specialized system for worldwide standardization. National bodies that are
members of ISO or IEC participate in the development of International Standards through technical
committees established by the respective organization to deal with particular fields of technical
activity. ISO and IEC technical committees collaborate in fields of mutual interest. Other interna-
tional organizations, governmental and non-governmental, in liaison with ISO and IEC, also take
part in the work.
In the field of information technology, ISO and IEC have established a joint technical committee,
ISO/IEC JTC 1. Draft International Standards adopted by the joint technical committee are circu-
lated to national bodies for voting. Publication as an International Standard requires approval by
at least 75% of the national bodies casting a vote.
International Standard ISO/IEC 9075-1 was prepared by Joint Technical Committee ISO/IEC JTC
1, Information technology, Subcommittee SC 32, Data management and interchange.
ISO/IEC 9075 consists of the following parts, under the general title Information technology —
Database languages — SQL:
— Part 1: Framework (SQL/Framework)
— Part 2: Foundation (SQL/Foundation)
— Part 3: Call-Level Interface (SQL/CLI)
— Part 4: Persistent Stored Modules (SQL/PSM)
— Part 5: Host Language Bindings (SQL/Bindings)
Annexes A, B, C, and D of this part of ISO/IEC 9075 are for information only.
Foreword vii
©ISO/IEC ISO/IEC 9075-1:1999 (E)
Introduction
The organization of this part of ISO/IEC 9075 is as follows:
1) Clause 1, ‘‘Scope’’, specifies the scope of this part of ISO/IEC 9075.
2) Clause 2, ‘‘Normative references’’, identifies additional standards that, through reference in this
part of International Standard, constitute provisions of ISO/IEC 9075.
3) Clause 3, ‘‘Definitions and use of terms’’, defines terms used in this and other parts of ISO/IEC
9075.
4) Clause 4, ‘‘Concepts’’, describes the concepts used in ISO/IEC 9075.
5) Clause 5, summarises the content of each of the parts of ISO/IEC 9075, in terms of the concepts
described in Clause 4, ‘‘Concepts’’.
6) Clause 6, defines notation and conventions used in other parts of ISO/IEC 9075.
7) Clause 7, describes the content of annexes of other parts of ISO/IEC 9075.
8) Clause 8, specifies requirements that apply to claims of conformance to all or some of the parts
of ISO/IEC 9075.
9) Annex A, is an informative Annex. It describes the formal procedures for maintenance and
interpretation of ISO/IEC 9075.
10) Annex B, ‘‘SQL Packages’’, is an informative Annex. It specifies several packages of SQL
language features as identified in:
— Appendix F, "SQL feature and package taxonomy", in ISO/IEC 9075-2
— Appendix F, "SQL Feature Taxonomy", in ISO/IEC 9075-4
— Appendix F, "SQL feature and package taxonomy", in ISO/IEC 9075-5
to which SQL-implementations may claim conformance.
11) Annex C, ‘‘Implementation-defined elements’’, is an informative Annex. It lists those features
for which the body of this part of ISO/IEC 9075 states that the syntax, the meaning, the
returned results, the effect on SQL-data and/or schemas, or any other behavior is partly or
wholly implementation-defined.
12) Annex D, ‘‘Implementation-dependent elements’’, is an informative Annex. It lists those features
for which the body of this part of ISO/IEC 9075 states that the syntax, the meaning, the
returned results, the effect on SQL-data and/or schemas, or any other behavior is partly or
wholly implementation-dependent.
In the text of this part of ISO/IEC 9075, Clauses begin a new odd-numbered page. Any resulting
blank space is not significant.
Introduction ix
INTERNATIONAL STANDARD ©ISO/IEC ISO/IEC 9075-1:1999 (E)
Information technology — Database languages — SQL —
Part 1:
Framework (SQL/Framework)
1 Scope
This part of ISO/IEC 9075 describes the conceptual framework used in other parts of ISO/IEC 9075
to specify the grammar of SQL and the result of processing statements in that language by an
SQL-implementation.
This part of ISO/IEC 9075 also defines terms and notation used in the other parts of ISO/IEC 9075.
NOTE 1 – The coordination of the development of existing and future standards for the management of
persistent data in information systems is described by the Reference Model of Data Management (ISO/IEC
10032:1995).
Scope 1
2 Framework (SQL/Framework)
©ISO/IEC ISO/IEC 9075-1:1999 (E)
2 Normative references
The following standards contain provisions that, through reference in this text, constitute provisions
of this part of ISO/IEC 9075. At the time of publication, the editions indicated were valid. All
standards are subject to revision, and parties to agreements based on this part of ISO/IEC 9075
are encouraged to investigate the possibility of applying the most recent editions of the standards
indicated below. Members of IEC and ISO maintain registers of currently valid International
Standards.
1) ISO 8824-1:1995, Information technology — Specification of Abstract Syntax Notation One
(ASN.1) — Part 1: Specification of basic notation
2) ISO/IEC 9075-2:1999, Information technology — Database languages — SQL — Part 2:
Foundation (SQL/Foundation).
3) ISO/IEC FDIS 9075-3:1999, Information technology — Database languages — SQL — Part
3: Call-Level Interface (SQL/CLI).
4) ISO/IEC 9075-4:1999, Information technology — Database languages — SQL — Part 4:
Persistent Stored Modules (SQL/PSM).
5) ISO/IEC 9075-5:1999, Information technology — Database languages — SQL — Part 5: Host
Language Bindings (SQL/Bindings).
6) ISO/IEC 10646-1:1993, Information technology — Universal Multi-Octet Coded Character
Set (UCS) — Part 1: Architecture and Multilingual Plane.
7) ISO/IEC CD 14651, Information technology — International String Ordering — Method for
comparing Character Strings.
8) The Unicode Consortium, The Unicode Standard, Version 2.0, 1996. ISBN 0-201-48345-9.
Normative references 3
4 Framework (SQL/Framework)
©ISO/IEC ISO/IEC 9075-1:1999 (E)
3 Definitions and use of terms
3.1 Definitions
For the purposes of this part of ISO/IEC 9075, the following definitions apply.
3.1.1 Definitions provided in this standard
In this part of ISO/IEC 9075, the definition of a verb defines every voice, mood, and tense of that
verb.
This part of ISO/IEC 9075 defines the following terms, which are also used in other parts of ISO/IEC
9075:
a) atomic: Incapable of being subdivided.
b) compilation unit: A segment of executable code, possibly consisting of one or more subpro-
grams.
c) data type: A set of representable values.
d) descriptor: A coded description of an SQL object. It includes all of the information about the
object that a conforming SQL-implementation requires.
e) identifier: A means by which something is identified.
f) identify: To properly reference something without ambiguity.
g) implementation-defined: Possibly differing between SQL-implementations, but specified by
the implementor for each particular SQL-implementation.
h) implementation-dependent: Possibly differing between SQL-implementations, but not spec-
ified by ISO/IEC 9075, and not required to be specified by the implementor for any particular
SQL-implementations.
i) instance (of a value): A physical representation of a value. Each instance is at exactly one
site. An instance has a data type that is the data type of its value.
j) null value: A special value that is used to indicate the absence of any data value.
k) object (as in ‘‘x object’’): Any thing.An x object is a component of, or is otherwise associated
with, some x, and cannot exist independently of that x. For example, an SQL object is an object
that exists only in the context of SQL; an SQL-schema object is an object that exists in some
SQL-schema.
l) persistent: Continuing to exist indefinitely, until destroyed deliberately. Referential and
cascaded actions are regarded as deliberate. Actions incidental to the termination of an SQL-
transaction or an SQL-session are not regarded as deliberate.
m) property (of an object): An attribute, quality, or characteristic of the object.
Definitions and use of terms 5
3.1 Definitions
n) row: A sequence of (field name, value) pairs, the data type of each value being specified by the
row type.
o) scope (of a standard): The clause in the standard that defines the subject of the standard and
the aspects covered, thereby indicating the limits of applicability of the standard or of particular
parts of it.
p) scope (of a declaration): That part of an SQL-client module, SQL-server module, invoked procedure>, SQL routine, or SQL-statement in which the object declared can be
referenced.
q) sequence: An ordered collection of objects that are not necessarily distinct.
r) site: A place occupied by an instance of a value of some specified data type (or subtype of it).
s) SQL-connection: An association between an SQL-client and an SQL-server.
t) SQL-environment: The context in which SQL-data exists and SQL-statements are executed.
u) SQL-implementation: A processor that processes SQL-statements. A conforming SQL-
implementation is an SQL-implementation that satisfies the requirements for SQL-implementations
as defined in Clause 8, ‘‘Conformance’’.
v) SQL-session: The context within which a single user, from a single SQL-agent, executes a
sequence of consecutive SQL-statements over a single SQL-connection.
w) SQL-statement: A string of characters that conforms, or purports to conform, to the Format
and Syntax Rules specified in the parts of ISO/IEC 9075.
x) table: A table has an ordered collection of one or more columns and an unordered collection of
zero or more rows. Each column has a name and a data type. Each row has, for each column,
exactly one value in the data type of that column.
3.2 Use of terms
The concepts on which ISO/IEC 9075 is based are described in terms of objects, in the usual sense
of the word.
Every object has properties, in the usual sense of the word (sometimes called characteristics or
attributes), usually including a name that is unique within some class of object. Some objects are
dependent on other objects. If x is an object, then the objects dependent on it are known as ‘‘x
objects’’. Thus the term ‘‘SQL object’’ denotes some object that exists only in the context of SQL.
Many x objects might be considered to be components of the x on which they depend.
If an x ceases to exist, then every x object dependent on that x also ceases to exist.
The representation of an x is known as an x descriptor or an x state, depending on the nature of x’s.
The descriptor or state of an x represents everything that needs to be known about the x. See also
Subclause 6.2.4, ‘‘Descriptors’’.
6 Framework (SQL/Framework)
©ISO/IEC ISO/IEC 9075-1:1999 (E)
3.3 Informative elements
3.3 Informative elements
In several places in the body of ISO/IEC 9075, informative notes appear. For example:
NOTE 2 – This is an example of a note.
Those notes do not belong to the normative part ISO/IEC 9075 and conformance to material speci-
fied in those notes shall not be claimed.
Definitions and use of terms 7
8 Framework (SQL/Framework)
©ISO/IEC ISO/IEC 9075-1:1999 (E)
4 Concepts
4.1 Caveat
This Clause describes concepts that are, for the most part, specified precisely in other parts of
ISO/IEC 9075. In any case of discrepancy, the specification in the other part is to be presumed
correct.
4.2 SQL-environments and their components
4.2.1 SQL-environments
An SQL-environment comprises:
— One SQL-agent.
— One SQL-implementation.
— Zero or more SQL-client modules, containing externally-invoked procedures available to the
SQL-agent.
— Zero or more authorization identifiers.
— Zero or more catalogs, each of which contains one or more SQL-schemas.
— The sites, principally base tables, that contain SQL-data, as described by the contents of the
schemas. This data may be thought of as ‘‘the database’’, but the term is not used in ISO/IEC
9075, because it has different meanings in the general context.
4.2.2 SQL-agents
An SQL-agent is that which causes the execution of SQL-statements. In the case of the di-
rect invocation of SQL (see Subclause 5.6.4, ‘‘Direct invocation of SQL’’), it is implementation-
defined. Alternatively, it may consist of one or more compilation units that, when executed, invoke
externally-invoked procedures in an SQL-client module.
4.2.3 SQL-implementations
An SQL-implementation is a processor that executes SQL-statements, as required by the SQL-
agent. An SQL-implementation, as perceived by the SQL-agent, includes one SQL-client, to which
that SQL-agent is bound, and one or more SQL-servers. An SQL-implementation can conform to
ISO/IEC 9075 without allowing more than one SQL-server to exist in an SQL-environment.
Because an SQL-implementation can be specified only in terms of how it executes SQL-statements,
the concept denotes an installed instance of some software (database management system). ISO/IEC
9075 does not distinguish between features of the SQL-implementation that are determined by the
software vendor and those determined by the installer.
Concepts 9
4.2 SQL-environments and their components
ISO/IEC 9075 recognizes that SQL-client and SQL-server software may have been obtained from
different vendors; it does not specify the method of communication between SQL-client and SQL-
server.
4.2.3.1 SQL-clients
An SQL-client is a processor, perceived by the SQL-agent as part of the SQL-implementation, that
establishes SQL-connections between itself and SQL-servers and maintains a diagnostics area and
other state data relating to interactions between itself, the SQL-agent, and the SQL-servers.
4.2.3.2 SQL-servers
Each SQL-server is a processor, perceived by the SQL-agent as part of the SQL-implementation,
that manages SQL-data.
Each SQL-server:
— Manages the SQL-session taking place over the SQL-connection between itself and the SQL-
client.
— Executes SQL-statements received from the SQL-client, receiving and sending data as required.
— Maintains the state of the SQL-session, including the authorization identifier and certain
session defaults.
4.2.4 SQL-client modules
An SQL-client module is a module that is explicitly created and dropped by implementation-defined
mechanisms.
An SQL-client module does not necessarily have a name; if it does, the permitted names are
implementation-defined.
An SQL-client module contains zero or more externally-invoked procedures.
Exactly one SQL-client module is associated with an SQL-agent at any time. However, in the case
of either direct binding style or SQL/CLI, this may be a default SQL-client module whose existence
is not apparent to the user.
4.2.5 User identifiers
A user identifier represents a user. The means of creating and destroying user identifiers, and their
mapping to real users, is not specified by ISO/IEC 9075.
4.2.6 Catalogs and schemas
10 Framework (SQL/Framework)
©ISO/IEC ISO/IEC 9075-1:1999 (E)
4.2 SQL-environments and their components
4.2.6.1 Catalogs
A catalog is a named collection of SQL-schemas in an SQL-environment. The mechanisms for
creating and destroying catalogs are implementation-defined.
4.2.6.2 SQL-schemas
An SQL-schema, often referred to simply as a schema, is a persistent, named collection of descrip-
tors that describe SQL-data. Any object whose descriptor is in some SQL-schema is known as an
SQL-schema object.
A schema, the schema objects in it, and the SQL-data described by them are said to be owned by
the authorization identifier associated with the schema.
SQL-schemas are created and destroyed by execution of SQL-schema statements (or by implementation-
defined mechanisms).
4.2.6.3 The Information Schema
Every catalog contains an SQL-schema with the name INFORMATION_SCHEMA that includes
the descriptors of a number of schema objects, mostly view definitions, that together allow every
descriptor in that catalog to be accessed, but not changed, as though it was SQL-data.
The data available through the views in an Information Schema includes the descriptors of the
Information Schema itself. It does not include the schema objects or base tables of the Definition
Schema (see Subclause 4.2.6.4, ‘‘The Definition Schema’’).
Each Information Schema view is so specified that a given user can access only those rows of the
view that represent descriptors on which that user has privileges.
4.2.6.4 The Definition Schema
The definition schema is a fictitious schema with the name DEFINITION_SCHEMA; if it were to
exist, the SQL-data in its base tables would describe all the SQL-data available to an SQL-server.
ISO/IEC 9075 defines it only in order to use it as the basis for the views of the Information Schema.
The structure of the Definition Schema is a representation of the data model of SQL.
4.2.7 SQL-data
SQL-data is data described by SQL-schemas — data that is under the control of an SQL-
implementation in an SQL-environment.
4.3 Tables
A table has an ordered collection of one or more columns and an unordered collection of zero or more
rows. Each column has a name and a data type. Each row has, for each column, exactly one value
in the data type of that column.
Concepts 11
4.3 Tables
SQL-data consists entirely of table variables, called base tables. An operation that references zero
or more base tables and returns a table is called a query. The result of a query is called a derived
table.
The rows of a table have a type, called ‘‘the row type’’; every row of a table has the same row type,
which is also the row type of the table. A table that is declared to be based on some structured
type is called a ‘‘typed table’’; its columns correspond in name and declared type to the attributes of
the structured type. Typed tables have one additional column, called the ‘‘self-referencing column’’
whose type is a reference type associated with the structured type of the table.
If a typed table TB1 has an associated structured type TP1 that is a subtype of some other struc-
tured type TP2, then TB1 can be defined to be a ‘‘subtable’’ of a typed table TB2 whose associated
type is TP2; TB2 is, in this case, a ‘‘supertable’’ of TB1.
A view is a named query, which can be invoked by use of this name. The result of such an invocation
is called a viewed table.
Some queries, and hence some views, are updatable, meaning they can appear as targets of state-
ments that change SQL-data. The results of changes expressed in this way are defined in terms of
corresponding changes to base tables.
No two columns of a base table or a viewed table can have the same name. Derived tables, other
than viewed tables, may contain more than one column with the same name.
A base table is either a schema object (its descriptor is in a schema; see Subclause 4.6.6, ‘‘Base
tables and their components’’) or a module object (its descriptor is in a module; see Subclause 4.9,
‘‘Modules’’). A base table whose descriptor is in a schema is called a created base table, and may be
either persistent or temporary (though its descriptor is persistent in either case). A persistent base
table contains 0 (zero) or more rows of persistent SQL-data. A base table declared in a module may
only be temporary, and is called a declared temporary table.
A temporary table is an SQL-session object that cannot be accessed from any other SQL-session. A
global temporary table can be accessed from any associated SQL-client module. A local temporary
table can be accessed only from the module to which it is local.
A temporary table is empty when an SQL-session is initiated and it is emptied (that is, all its rows
are deleted) either when an SQL-transaction is terminated or when an SQL-session is terminated,
depending on its descriptor.
4.4 SQL data types
4.4.1 General data type information
Every data value belongs to some data type.
Every data type is either predefined, constructed,or user-defined. Every data type has a name. The
name of a predefined or constructed data type is a reserved word specified by that part of ISO/IEC
9075 that specifies the data type. The name of a user-defined type is provided in its definition. A
user-defined data type is a schema object; see Subclause 4.6.4, ‘‘User-defined types’’.
A predefined data type is a data type specified by ISO/IEC 9075, and is therefore provided by the
SQL-implementation. A data type is predefined even though the user is required (or allowed) to
provide certain parameters when specifying it (for example the precision of a number).
A predefined data type is atomic. An atomic type is a data type whose values are not composed of
values of other data types. The existence of an operation (SUBSTRING, EXTRACT) that is capable
of selecting part of a string or datetime value does not imply that a string or datetime is not atomic.
12 Framework (SQL/Framework)
©ISO/IEC ISO/IEC 9075-1:1999 (E)
4.4 SQL data types
A constructed type is either atomic or composite. A composite type is a data type each of whose
values is composed of zero or more values, each of a declared data type.
4.4.2 The null value
Every data type includes a special value, called the null value, sometimes denoted by the keyword
NULL. This value differs from other values in the following respects:
— Since the null value is in every data type, the data type of the null value implied by the
keyword NULL cannot be inferred; hence NULL can be used to denote the null value only in
certain contexts, rather than everywhere that a literal is permitted.
— Although the null value is neither equal to any other value nor not equal to any other value —
it is unknown whether or not it is equal to any given value — in some contexts, multiple null
values are treated together; for example, the treats all null values together.
4.4.3 Predefined types
4.4.3.1 Numeric types
There are two classes of numeric type: exact numeric, which includes integer types and types with
specified precision and scale; and approximate numeric, which is essentially floating point, and for
which a precision may optionally be specified.
Every number has a precision (number of digits), and exact numeric types also have a scale (digits
after the radix point). Arithmetic operations may be performed on operands of different or the same
numeric type, and the result is of a numeric type that depends only on the numeric type of the
operands. If the result cannot be represented exactly in the result type, then whether it is rounded
or truncated is implementation-defined. An exception condition is raised if the result is outside
the range of numeric values of the result type, or if the arithmetic operation is not defined for the
operands.
4.4.3.2 String types
A value of character type is a string (sequence) of characters drawn from some character repertoire.
The characters in a character string S are all drawn from the same character set CS.If S is the
value of some expression E, then CS is the character set specified for the declared type of E.A
character string type is either of fixed length, or of variable length up to some implementation-
defined maximum. A value of character large object (CLOB) type is a string of characters from some
character repertoire and is always associated with exactly one character set. A character large
object is of variable length, up to some implementation-defined maximum that is probably greater
than that of other character strings.
Either a character string or character large object may be specified as being based on a spe-
cific character set by specifying CHARACTER SET in the data type; a particular character set
chosen by the implementation to be the national character set may be specified by specifying
NATIONAL CHARACTER, NATIONAL CHARACTER VARYING, or NATIONAL CHARACTER
LARGE OBJECT (or one of several syntactic equivalents) as the data type.
A value of bit string type is a string of bits (binary digits). A bit string type is either of fixed length,
or of variable length up to some implementation-defined maximum.
Concepts 13
4.4 SQL data types
A value of binary string type (known as a binary large object, or BLOB) is a variable length sequence
of octets, up to an implementation-defined maximum.
4.4.3.3 Boolean type
A value of the Boolean data type is either true or false. The truth value of unknown is sometimes
represented by the null value.
4.4.3.4 Datetime types
There are three datetime types, each of which specifies values comprising datetime fields.
A value of data type TIMESTAMP comprises values of the datetime fields YEAR (between 0001 and
9999), MONTH, DAY, HOUR, MINUTE and SECOND.
A value of data type TIME comprises values of the datetime fields HOUR, MINUTE and SECOND.
A value of data type DATE comprises values of the datetime fields YEAR (between 0001 and 9999),
MONTH and DAY.
A value of DATE is a valid Gregorian date. A value of TIME is a valid time of day.
TIMESTAMP and TIME may be specified with a number of (decimal) digits of fractional seconds
precision.
TIMESTAMP and TIME may also be specified as being WITH TIME ZONE, in which case every
value has associated with it a time zone displacement. In comparing values of a data type WITH
TIME ZONE, the value of the time zone displacement is disregarded.
4.4.3.5 Interval types
A value of an interval type represents the duration of a period of time. There are two classes of
intervals. One class, called year-month intervals, has a datetime precision that includes a YEAR
field or a MONTH field, or both. The other class, called day-time intervals, has an express or
implied interval precision that can include any set of contiguous fields other than YEAR or MONTH.
4.4.4 Constructed atomic types
4.4.4.1 Reference types
A reference type is a predefined data type, a value of which references (or points to) some site
holding a value of the referenced type. The only sites that may be so referenced are the rows of
typed tables. It follows that every referenced type is a structured type.
4.4.5 Constructed composite types
14 Framework (SQL/Framework)
©ISO/IEC ISO/IEC 9075-1:1999 (E)
4.4 SQL data types
4.4.5.1 Collection types
A collection comprises zero or more elements of a specified data type known as the element type.
An array is an ordered collection of not necessarily distinct values, whose elements may be refer-
enced by their ordinal position in the array.
An array type is specified by an array type constructor.
4.4.5.2 Row types
A row type is a sequence of one or more (field name, data type) pairs, known as fields. A value of a
row type consists of one value for each of its fields.
4.4.5.3 Fields
A field is a (field name, data type) pair. A value of the field is a value of its data type.
4.5 Sites and operations on sites
4.5.1 Sites
A site is a place that holds an instance of a value of a specified data type. Every site has a de-
fined degree of persistence, independent of its data type. A site that exists until deliberately
destroyed is said to be persistent. A site that necessarily ceases to exist on completion of a com-
pound SQL-statement, at the end of an SQL-transaction, or at the end of an SQL-session is said to
be temporary. A site that exists only for as long as necessary to hold an argument or returned value
is said to be transient.
As indicated above, the principal kind of persistent or temporary site is the base table. A base
table is a special kind of site, in that constraints can be specified on its values, which the SQL-
implementation is required to enforce (see Subclause 4.6.6.3, ‘‘Table constraints’’).
Some sites may be referenced by their names — for example, base tables and SQL variables (see
ISO/IEC 9075-4). Some sites may be referenced by a REF value. A site occupied by an element of
an array may be referenced by its element number.
4.5.2 Assignment
The instance at a site can be changed by the operation of assignment. Assignment replace
...