ISO/IEC 9075-1:1999
(Main)Information technology — Database languages — SQL — Part 1: Framework (SQL/Framework)
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)
General Information
Relations
Standards Content (Sample)
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 9075-1:1999(E)
©
ISO/IEC 1999
---------------------- Page: 1 ----------------------
ISO/IEC 9075-1:1999(E)
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
---------------------- Page: 2 ----------------------
ISO/IEC 9075-1:1999(E)
© ISO/IEC 1999 – All rights reserved i-3
---------------------- Page: 3 ----------------------
ISO/IEC 9075-1:1999 (E)
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)
---------------------- Page: 4 ----------------------
©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
---------------------- Page: 5 ----------------------
ISO/IEC 9075-1:1999 (E) ©ISO/IEC
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)
---------------------- Page: 6 ----------------------
©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
---------------------- Page: 7 ----------------------
ISO/IEC 9075-1:1999 (E) ©ISO/IEC
TABLES
Tables Page
1 Relationships of routine characteristics . . 25
2 SQL Packages . . . . 55
vi Framework (SQL/Framework)
---------------------- Page: 8 ----------------------
©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
---------------------- Page: 9 ----------------------
©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
---------------------- Page: 10 ----------------------
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
---------------------- Page: 11 ----------------------
ISO/IEC 9075-1:1999 (E) ©ISO/IEC
2 Framework (SQL/Framework)
---------------------- Page: 12 ----------------------
©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
---------------------- Page: 13 ----------------------
ISO/IEC 9075-1:1999 (E) ©ISO/IEC
4 Framework (SQL/Framework)
---------------------- Page: 14 ----------------------
©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
---------------------- Page: 15 ----------------------
ISO/IEC 9075-1:1999 (E) ©ISO/IEC
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)
---------------------- Page: 16 ----------------------
©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
---------------------- Page: 17 ----------------------
ISO/IEC 9075-1:1999 (E) ©ISO/IEC
8 Framework (SQL/Framework)
---------------------- Page: 18 ----------------------
©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
---------------------- Page: 19 ----------------------
ISO/IEC 9075-1:1999 (E) ©ISO/IEC
4.2 SQL-environments an
...
Questions, Comments and Discussion
Ask us and Technical Secretary will try to provide an answer. You can facilitate discussion about the standard in here.