ISO ANSI

962 Pages·2000·2.56 MB·English

X3H2-94-329
DBL:RIO-004
August, 1994
ISO
International Organization for Standardization
ANSI
American National Standards Institute
ANSI TC X3H2
ISO/IEC JTC 1/SC 21/WG 3
Database
Title: (ISO-ANSI Working Draft) Database Language SQL (SQL3)
Author: Jim Melton (Editor)
References:
1) ANSI X3H2-94-079/SOU-003, (ISO Working Draft) Framework for SQL (SQL/Framework),
March, 1994
2) ANSI X3H2-94-080/SOU-004, (ISO-ANSI Working Draft) Database Language SQL, March, 1994
3) ANSI X3H2-94-081/SOU-005, (ISO Working Draft) SQL Call-Level Interface (SQL/CLI), March,
1994
4) ANSI X3H2-94-082/SOU-006, (ISO Working Draft) SQL Persistent Stored Modules (SQL/PSM),
March, 1994
5) ANSI X3H2-94-083/SOU-007, (ISO Working Draft) SQL Host Language Bindings (SQL
/Bindings), March, 1994
6) X3H2-94-077, Minutes from ANSI X3H2 meeting, February, 1994
7) X3H2-94-185, Minutes from ANSI X3H2 meeting, April, 1994
8) X3H2-94-306, Minutes from ANSI X3H2 meeting, June, 1994
9) RIO-001, Minutes from ISO DBL RG meeting, July, 1994

1 Editorial Notes
The attached SQL3 base document incorporates the changes arising from the ISO DBL RG meeting
held January, 1994, in Munich, Germany, and the ANSI X3H2 meeting in February, 1994, in
Tiburon, CA.
The following conventions are used in the base document:
1) Words or short phrases that have been approved by ANSI but not by ISO are indicated by:
ANSI sample phrase
while words or short phrases approved by ISO and not by ANSI are indicated by:
ISO example.
Short differences between ANSI and ISO are indicated by:
ANSI ANSI version.
ISO ISO version.
2) Longer sequences of text that have been approved by ANSI, later changed by ISO, and not yet
reconsidered by ANSI are distinguished by:
ANSI Only—caused by ISO changes not yet considered by ANSI
separating the ANSI-specific text from the preceding text by the convention you see here, and
from the following text by the single heavy rule below. This separator is used to identify infor-
mation that may change when ANSI has the opportunity to consider changes accepted by ISO
that affect the area indicated.
Similarly, longer sequences approved by ISO, changed by ANSI, and not yet reconsidered by
ISO are distinguished by:
ISO Only—caused by ANSI changes not yet considered by ISO
separating the ISO-specific text from the preceding text by the convention you see here, and
from the following text by the single heavy rule below as before. This separator is used to iden-
tify information that may change when ISO has the opportunity to consider changes accepted
by ANSI that affect the area indicated.
It is the intent of these conventions to clearly highlight those portions of the SQL3 base docu-
ment that are not yet approved by both bodies. One hopes that this highlighting will make it
easier for the two bodies to move closer, so that a single standard can result from their efforts.
3) I have created a number of additional separators. While recognizing that the proliferation of
these separators makes the document somewhat complex to examine, I believe it to be necessary
in order that we may know the precise state of each part of the document. In particular, you
may find in this document separators as follows:
ISO Only–SQL3
This separator indicates that ISO has approved the text following the separator for SQL3. ANSI
has not approved the text at all.
Editorial Notes Notes–1
ANSI Only–SQL3
This separator indicates that ANSI has approved the text following the separator for SQL3. ISO
has not approved the text at all.
4) I am usually able to produce a document that has changebars to indicate changes between the
present document and the preceding version. New (inserted) and changed material is marked
with a thin vertical bar like that next to this paragraph.
•
Example of deletion bullet
Additionally, places in the current document where material from the preceding version has
been deleted are marked with a bullet like the one preceding this paragraph. The note beside
the bullet, if any, indicates the number of "entities" that have been deleted. Those entities are
typically sentences, although they are sometimes text processor commands.
In Clause 2, "Changes and Notes", I have made a number of notes related to the changes that I
have made to Reference 1 to derive this document.
In Clause 3, "Possible problems with the SQL3 language", I have listed a number of possible
problems in the SQL3 language. This list should be viewed as a sort of "work item list", helping
identify areas that should (must?) be resolved before the document is ready for public review. This
material is divided into genuine problems versus those problems that simply should be addressed
or for which "wordsmithing" is the likely answer; the second category is not viewed as sufficiently
urgent to cause the document to fail a ballot for public review or advancement. Finally, there is a
new section for "Language Opportunities" that lists those items in which some interest has been
expressed, but that are not felt to be sufficiently important to justify delaying the standard in their
absence.
In Clause 4, "SQL3 Language Features Not Approved by Both ANSI and ISO", I have listed what I
believe to be the differences between ANSI-approved SQL3 features and ISO-approved features at
the time of publication of these Editor’s Notes.
In Clause 5, "Guidelines for writing "user-friendly" change proposals", I have outlined some sug-
gested guidelines that I encourage you to use when writing change proposals. Following those
guidelines will markedly help improve the quality of the document.
2
2 Changes and Notes
ISO DBL RG changes
In its July, 1994, meeting in Southampton, United Kingdom, the ISO/IEC JTC1/SC21/WG3
Database Languages Rapporteur Group (DBL RG) Meeting considered most of the papers that had
been put before it. All those papers that were accepted have been incorporated into this document.
The results of those papers that were approved are reflected in this document; this portion of the
Editor’s Notes indicates the papers whose changes are reflected.
1) SOU-020/X3H2-93-481, Privileges: correct definition of "abandoned"
2) SOU-021/X3H2-93-433, Large Object Strings
3) SOU-027/X3H2-93-531R1, Correction to VIEW_TABLE_USAGE base table
4) SOU-028/X3H2-93-523R, Further clarification to COLUMN_DEFAULT
5) SOU-029/X3H2-93-556, Null-elimination warning for cursors
6) SOU-030/X3H2-94-019, Correction to definition of outer reference
7) SOU-031/X3H2-94-015, Casting between datetimes and character strings
8) SOU-034/X3H2-93-493, Make column-level SELECT privileges work
9) SOU-035/X3H2-93-500, Different types of external routines
10) SOU-036/X3H2-93-503, Consistency of descriptor area and diagnostics area
11) SOU-037/X3H2-93-534, Fix on Leveling Rule of <referential constraint definition>
12) SOU-038/X3H2-93-560, Consistent SQLSTATEs for Referential Integrity constraint violation
13) SOU-040/X3H2-94-026, Triggers on views
14) SOU-041/X3H2-94-027, Schema paths for data types
15) SOU-042/X3H2-94-028, Improving encapsulation I
16) SOU-045/X3H2-94-031R, Interval qualifier
17) SOU-046/X3H2-94-060, Null class specification
18) SOU-051/X3H2-94-239, Common subexpression in queries
19) SOU-054/X3H2-94-242, MIscellaneous editorial corrections
20) SOU-055/X3H2-94-243, Erratum in Subclause 5.2
21) SOU-059/X3H2-94-111, Remove <SQL statement>
22) SOU-060/X3H2-94-126, Clean up <query specification>
Changes and Notes Notes–3
23) SOU-062/X3H2-94-158, PL/I specification of FLOAT and DECIMAL
24) SOU-063/X3H2-94-102, Adding functions, routines, overloading to SQL/PSM
25) SOU-065/X3H2-94-106, Miscellaneous PSM changes
26) SOU-069/X3H2-94-148, Clarify <leave statement> Syntax Rules
27) SOU-070/X3H2-94-159, Exception handling in <for statement>
28) SOU-073/X3H2-94-096, Changing the type of an <abstract data type value expression>
29) SOU-074/X3H2-94-098, Remove semicolon in CREATE TRIGGER
30) SOU-075/X3H2-94-099, Fixes to Large Object Strings
31) SOU-076/X3H2-94-103, ROW types—base proposal
32) SOU-077/X3H2-94-150, Using Row Types in <for statement>
33) SOU-080/X3H2-94-109, Restrict use of ’empty’ ADT constructor
34) SOU-081/X3H2-94-178, Miscellaneous cleanup
35) SOU-082/X3H2-94-124, Replace "<column reference>" with "column reference"
36) SOU-083/X3H2-94-125, Alignment of ADT descriptor
37) SOU-084/X3H2-94-130, Destructors for object ADTs
38) SOU-085/X3H2-94-131, Subject routine determination in views/constraints
39) SOU-086/X3H2-94-143, SQL-session attributes
40) SOU-087/X3H2-94-149, Miscellaneous routine cleanup
41) SOU-088/X3H2-94-151, Inner-most atomic execution context
42) SOU-090/X3H2-94-169R, Bit strings and <position expression>
43) SOU-097/X3H2-94-244, Miscellaneous Possible Problems
44) SOU-104/X3H2-94-252, A proposal to fix Possible Problems 261, 298, and 300
45) SOU-105R1/X3H2-94-253, Removal of redundant <there is predicate>
46) SOU-106/X3H2-94-254, A proposal to fix Possible Problem 262
47) SOU-107/X3H2-94-255, A proposal to fix Possible Problem 277 and Language Opportunity 276
(but not Possible Problem 153)
48) SOU-108/X3H2-94-256, Fix to Possible Problem 299
49) SOU-109/X3H2-94-257, A proposal to fix Possible Problem 313
50) SOU-112/X3H2-94-260, Fix to Possible Problem 344
51) SOU-118/X3H2-94-264, A proposal to standardize Session Management statement syntax
4
52) SOU-130/X3H2-94-275, Cursor correction
53) SOU-131/X3H2-94-282, Ada OUT parameters
54) SOU-138/X3H2-94-216, CASE
55) SOU-139/X3H2-94-289, Schema information tables for SQL/PSM
56) SOU-141/X3H2-94-217, Single-root supertype rule
57) SOU-142/X3H2-94-231, Removal of virtual attributes
58) SOU-145/X3H2-94-233R, Change "handle" to "locator"
59) SOU-146/X3H2-94-293/X3H2-94-204, Improving encapsulation: changes and fixes
60) SOU-150/X3H2-94-268, OID Association
61) SOU-151/X3H2-94-269, Nullability with INTERSECT and EXCEPT
62) SOU-154/X3H2-94-284, Need for <specific type template designator
63) SOU-184/X3H2-94-201, Flow of control, exception handling, and transactions
64) SOU-186/X3H2-94-387, Response to SOU-071, "Specialize Generalize rows in subtable"
65) SOU-188/X3H2-94-387, Comments on Final Text of Corrigendum 1
66) SOU-???/X3H2-94-116, ELSEIF usage in the <if statement>
ANSI X3H2 changes
In its April, 1994, meeting in Pittsburgh, PA, and its June, 1994, meeting in Fargo, ND, the ANSI
X3H2 body considered most of the papers that had been put before it in a timely manner.
The results of those papers that were approved are reflected in this document; this portion of the
Editor’s Notes indicates the papers whose changes are reflected.
1) SOU-020/X3H2-93-481, Privileges: correct definition of "abandoned"
2) SOU-021/X3H2-93-433, Large Object Strings
3) SOU-027/X3H2-93-531R1, Correction to VIEW_TABLE_USAGE base table
4) SOU-028/X3H2-93-523R, Further clarification to COLUMN_DEFAULT
5) SOU-029/X3H2-93-556, Null-elimination warning for cursors
6) SOU-030/X3H2-94-019, Correction to definition of outer reference
7) SOU-031/X3H2-94-015, Casting between datetimes and character strings
8) SOU-033/X3H2-93-465, A Backward-Compatible extension to the FROM clause
9) SOU-034/X3H2-93-493, Make column-level SELECT privileges work
10) SOU-035/X3H2-93-500, Different types of external routines
11) SOU-036/X3H2-93-503, Consistency of descriptor area and diagnostics area
Changes and Notes Notes–5
12) SOU-037/X3H2-93-534, Fix on Leveling Rule of <referential constraint definition>
13) SOU-038/X3H2-93-560, Consistent SQLSTATEs for Referential Integrity constraint violation
14) SOU-039/X3H2-94-004, Row identifiers simplify applications
15) SOU-040/X3H2-94-026, Triggers on views
16) SOU-041/X3H2-94-027, Schema paths for data types
17) SOU-042/X3H2-94-028, Improving encapsulation I
18) SOU-044/X3H2-94-030R1, Roles module
19) SOU-045/X3H2-94-031R, Interval qualifier
20) SOU-046/X3H2-94-060, Null class specification
21) SOU-059/X3H2-94-111, Remove <SQL statement>
22) SOU-060/X3H2-94-126, Clean up <query specification>
23) SOU-062/X3H2-94-158, PL/I specification of FLOAT and DECIMAL
24) SOU-063/X3H2-94-102, Adding functions, routines, overloading to SQL/PSM
25) SOU-065/X3H2-94-106, Miscellaneous PSM changes
26) SOU-069/X3H2-94-148, Clarify <leave statement> Syntax Rules
27) SOU-070/X3H2-94-159, Exception handling in <for statement>
28) SOU-072/X3H2-94-092, Specifying triggers
29) SOU-073/X3H2-94-096, Changing the type of an <abstract data type value expression>
30) SOU-074/X3H2-94-098, Remove semicolon in CREATE TRIGGER
31) SOU-075/X3H2-94-099, Fixes to Large Object Strings
32) SOU-076/X3H2-94-103, ROW types—base proposal
33) SOU-077/X3H2-94-150, Using Row Types in <for statement>
34) SOU-080/X3H2-94-109, Restrict use of ’empty’ ADT constructor
35) SOU-081/X3H2-94-178, Miscellaneous cleanup
36) SOU-082/X3H2-94-124, Replace "<column reference>" with "column reference"
37) SOU-083/X3H2-94-125, Alignment of ADT descriptor
38) SOU-084/X3H2-94-130, Destructors for object ADTs
39) SOU-085/X3H2-94-131, Subject routine determination in views/constraints
40) SOU-086/X3H2-94-143, SQL-session attributes
41) SOU-087/X3H2-94-149, Miscellaneous routine cleanup
6
42) SOU-088/X3H2-94-151, Inner-most atomic execution context
43) SOU-089/X3H2-94-162, Default Roles
44) SOU-090/X3H2-94-169R, Bit strings and <position expression>
45) SOU-130/X3H2-94-275, Cursor correction
46) SOU-131/X3H2-94-282, Ada OUT parameters
47) SOU-138/X3H2-94-216, CASE
48) SOU-139/X3H2-94-289, Schema information tables for SQL/PSM
49) SOU-141/X3H2-94-217, Single-root supertype rule
50) SOU-142/X3H2-94-231, Removal of virtual attributes
51) SOU-145/X3H2-94-233R, Change "handle" to "locator"
52) SOU-146/X3H2-94-293/X3H2-94-204, Improving encapsulation: changes and fixes
53) SOU-150/X3H2-94-268, OID Association
54) SOU-151/X3H2-94-269, Nullability with INTERSECT and EXCEPT
55) SOU-154/X3H2-94-284, Need for <specific type template designator
56) SOU-???/X3H2-94-116, ELSEIF usage in the <if statement>
Other changes
In addition, the following changes appear in this document.
1) A number of minor editorial changes intended to clarify vague wording, incorrect grammar,
inconsistent phraseology, and so forth.
Changes and Notes Notes–7
8