ANSI INCITS 135-1992 Information Technology - Database Languages - SQL.pdf

1、ANSI INCITS 135-1992 (R1998) (formerly ANSI X3.135-1992 (R1998)for Information Systems -Database Language -SQLANSI x3.1 35-1992 Revision and consolidation of ANSI X3.1 35-l 989, and ANSI X3.168-1 989 American National Standard for Information Systems - Database Language - SQL Secretariat Computer an

2、d Business Equipment Manufacturers Association Approved October 16, 1992 American National Standards Institute, Inc. AmericanNationalStandardApproval of an American National Standard requires review by ANSI that therequirements for due process, consensus, and other criteria for approval havebeen met

3、 by the standards developer.Consensus is established when, in the judgment of the ANSI Board of StandardsReview, substantial agreement has been reached by directly and materiallyaffected interests. Substantial agreement means much more than a simplemajority, but not necessarily unanimity. Consensus

4、requires that all views andobjections be considered, and that a concerted effort be made toward theirresolution.The use of American National Standards is completely voluntary; their existencedoes not in any respect preclude anyone, whether he has approved the standardsor not, from manufacturing, mar

5、keting, purchasing, or using products, processes,or procedures not conforming to the standards.The American National Standards Institute does not develop standards and will inno circumstances give an interpretation of any American National Standard.Moreover, no person shall have the right or authori

6、ty to issue an interpretation ofan American National Standard in the name of the American National StandardsInstitute. Requests for interpretations should be addressed to the secretariat orsponsor whose name appears on the title page of this standard.CAUTION NOTICE: This American National Standard m

7、ay be revised orwithdrawn at any time. The procedures of the American National StandardsInstitute require that action be taken periodically to reaffirm, revise, or withdrawthis standard. Purchasers of American National Standards may receive currentinformation on all standards by calling or writing t

8、he American National StandardsInstitute.Published byAmerican National Standards Institute11 West 42nd Street, New York, New York 10036Copyright 1992 by Information Technology Industry Council (ITI)All rights reserved.No part of this publication may be reproduced in anyform, in an electronic retrieva

9、l system or otherwise,without prior written permission of ITI, 1250 Eye Street NW,Washington, DC 20005.Printed in the United States of AmericaContents Page Foreword . xi Introduction . xvii 1 Scope .l 2 Normative references .3 3 Definitions, notations, and conventions .5 3.1 Definitions .5 3.1.1 Def

10、initions taken from ISO/IEC 10646 . .5 3.1.2 Definitions taken from IS0 8601 . .5 3.1.3 Definitions provided in this American National Standard . 3.2 Notation . .7 3.3 Conventions 8 3.3.1 Informative elements .8 3.3.2 Specification of syntactic elements . .8 3.3.3 Specification of the Information Sc

11、hema . .9 3.3.4 Use of terms .9 3.3.4.1 Exceptions .9 3.3.4.2 Syntactic containment .9 3.3.4.3 Terms denoting rule requirements .10 3.3.4.4 Rule evaluation order . .10 3.3.4.5 Conditional rules .ll 3.3.4.6 Syntactic substitution . .l 1 3.3.4.7 Other terms . .12 3.3.5 Descriptors .12 3.3.6 Index typo

12、graphy . .13 3.4 Object identifier for Database Language SQL .13 4 Concepts .15 4.1 Data types .15 4.2 Character strings .16 4.2.1 Character strings and collating sequences .16 4.2.2 Operations involving character strings .17 4.2.2.1 Operators that operate on character strings and return character s

13、trings . .17 4.2.2.2 Other operators involving bit strings . .18 4.2.3 Rules determining collating sequence usage . .18 4.3 Bit strings . .21 4.3.1 Bit string comparison and assignment . .21 4.3.2 Operations involving bit strings . .21 4.3.2.1 Operators that operate on bit strings and return bit str

14、ings .21 4.3.2.2 Other operators involving bit strings . .21 4.4 Numbers .21 4.4.1 Characteristics of numbers . .22 4.4.2 Operations involving numbers .23 4.5 Datetimes and intervals .23 4.5.1 Datetimes .23 4.5.2 Intervals .25 4.5.3 Operations involving datetimes and intervals . .26 4.6 Type convers

15、ions and mixing of data types . .27 4.7 Domains . .28 i 4.8 Columns . .28 4.9 Tables . 29 4.10 Integrity constraints .31 4.10.1 Checking of constraints .32 4.10.2 Table constraints . .32 4.10.3 Domain constraints . 33 4.10.4 Assertions . .33 4.11 SQL-schemas . .34 4.12 Catalogs 34 4.13 Clusters of c

16、atalogs .35 4.14 SQL-data .35 4.15 SQL-environment . .35 4.16 Modules 36 4.17 Procedures . .36 4.18 Parameters . .36 4.18.1 Status parameters .36 4.18.2 Data parameters .37 4.18.3 Indicator parameters . 3 7 4.19 Diagnostics area . .37 4.20 Standard programming languages . .38 4.21 Cursors . 38 4.22

17、SQL-statements .39 4.22.1 Classes of SQL-statements .39 4.22.2 SQL-statements classified by function .40 4.22.3 Embeddable SQL-statements . .43 4.22.4 Preparable and immediately executable SQL-statements . .44 4.22.5 Directly executable SQL-statements 46 4.22.6 SQL-statements and transaction states

18、. .47 4.23 Embedded syntax . .48 4.24 SQL dynamic statements .49 4.25 Direct invocation of SQL . .51 4.26 Privileges . .51 4.27 SQL-agents .53 4.28 SQL-transactions . 53 4.29 SQL-connections .56 4.30 SQL-sessions 5 7 4.31 Client-server operation . .59 4.32 information Schema .59 4.33 Leveling . .60

19、4.34 SQL Flagger . .60 5 Lexical elements 63 5.1 . .63 5.2 and . 66 5.3 . . .71 5.4 Names and identifiers . .78 6 Scalar expressions .85 6.1 . . .85 6.2 and . . .91 6.3 . .94 6.4 . . .96 6.5 - Annex B: Implementation-defined elements: - Annex C: Implementation-dependent elements; - Annex D: Deprecat

20、ed features; - Annex E: Incompatibilities with ANSI X3.135-1989; - Annex F: Maintenance and interpretation of SQL. Introduction This American National Standard was approved in 1992. This American National Standard was developed from ANSI X3.135-1989, information Systems, Database Language SQL with I

21、ntegrity Enhance- ments, and replaces that American National Standard. It adds significant new features and capabilities to the specifications. It is generally compati- ble with ANSI X3.135-1989 in the sense that, with very few exceptions, SQL language that conforms to ANSI X3.135-1989 also conforms

22、 to this American National Standard, and will be treated in the same way by an implementation of this American National Standard as it would by an imple- mentation of ANSI X3.135-1989. The known incompatibilities between ANSI X3.135-1989 and this American National Standard are stated in infor- mativ

23、e Annex E, Incompatibilities with ANSI X3.1 351989. xi Technical changes between ANSI X3.135-1989 and this American National Standard include both improvements or enhancements to existing features and the definition of new features. Significant improvements in existing features include: - A better d

24、efinition of direct invocation of SQL language; - Improved diagnostic capabilities, especially a new status parameter (SQLSTATE), a diagnostics area, and supporting statements. Significant new features are: 1) Support for additional data types (DATE, TIME, TIMESTAMP, INTER- VAL, BIT string, variable

25、-length character and bit strings, and NATIONAL CHARACTER strings), 2) Support for character sets beyond that required to express SQL lan- guage itself and support for additional collations, 3) Support for additional scalar operations, such as string operations for concatenate and substring, date an

26、d time operations, and a form for con- ditional expressions, 4) Increased generality and orthogonality in the use of scalar-valued and table-valued query expressions, 5) Additional set operators (for example, union join, natural join, set dif- ference, and set intersection), 6) Capability for domain

27、 definitions in the schema, 7) Support for Schema Manipulation capabilities (especially DROP and ALTER statements), 8) Support for bindings (modules and embedded syntax) in the MUMPS language, 9) Additional privilege capabilities, 10) Additional referential integrity facilities, including referentia

28、l actions, subqueries in CHECK constraints, separate assertions, and user-con- trolled deferral of constraints, 11) Definitions of an Information Schema, 12) Support for dynamic execution of SQL language, 13) Support for certain facilities required for Remote Database Access (especially connection m

29、anagement statements and qualified schema names), 14) Support for temporary tables, 15) Support for transaction consistency levels, 16) Support for data type conversions (CAST expressions among data typW9 17) Support for scrolled cursors, and 18) A requirement for a flagging capability to aid in por

30、tability of applica- tion programs. xii The organization of this American National Standard is as follows: 1) Clause 1, “Scope”, specifies the scope of this American National Standard. 2) Clause 2, “Normative references”, identifies additional standards that, through reference in this American Natio

31、nal Standard, constitute provi- sions of this American National Standard. 3) Clause 3, “Definitions, notations, and conventions”, defines the nota- tions and conventions used in this American National Standard. 4) Clause 4, “Concepts”, presents concepts used in the definition of SQL. 5) Clause 5, “L

32、exical elements”, defines the lexical elements of the lan- guage. 6) Clause 6, “Scalar expressions”, defines the elements of the language that produce scalar values. 7) Clause 7, “Query expressions”, defines the elements of the language that produce rows and tables of data. 8) Clause 8, “Predicates”

33、, defines the predicates of the language. 9) Clause 9, “data assignment rules”, specifies the rules for assignments that retrieve data from or store data into the database, and formation rules for set operations. 10) Clause 10, “Additional common elements”, defines additional lan- guage elements tha

34、t are used in various parts of the language. 11) Clause 11, “Schema definition and manipulation”, defines facilities for creating and managing a schema. 12) Clause 12, “Module”, defines modules and procedures. 13) Clause 13, “Data manipulation”, defines the data manipulation state- ments. 14) Clause

35、 14, “Transaction management”, defines the SQL-transaction management statements. 15) Clause 15, “Connection management”, defines the SQL-connection management statements. 16) Clause 16, “Session management”, defines the SQL-session manage- ment statements. 17) Clause 17, “Dynamic SQL”, defines the

36、facilities for executing SQL- statements dynamically. 18) Clause 18, “Diagnostics management”, defines the diagnostics man- agement facilities. 19) Clause 19, “Embedded SQL”, defines syntax for embedding SQL in certain standard programming languages. 20) Clause 20, “Direct invocation of SQL”, define

37、s the direct invocation of SQL language. 21) Clause 21, “Information Schema and Definition Schema”, defines viewed tables that contain schema information. . . . XIII 22) Clause 22, “Status codes”, defines values that identify the status of the execution of SQL-statements and the mechanisms by which

38、those val- ues are returned. 23) Clause 23, “Conformance”, defines the criteria for conformance to this American National Standard. 24) Annex A, “Leveling the SQL Language”, is an informative Annex. It lists the leveling rules defining the Entry SQL and Intermediate SQL sub- set levels of the SQL la

39、nguage. 25) Annex B, “Implementation-defined elements”, is an informative Annex. It lists those features for which the body of the American National Standard states that the syntax or meaning or effect on the database is partly or wholly implementation-defined, and describes the defining infor- mati

40、on that an implementor shall provide in each case. 26) Annex C, “Implementation-dependent elements”, is an informative Annex. It lists those features for which the body of the American National Standard states explicitly that the meaning or effect on the database is implementation-dependent. 27) Ann

41、ex D, “Deprecated features”, is an informative Annex. It lists fea- tures that the responsible Technical Committee intends will not appear in a future revised version of this American National Standard. 28) Annex E, “Incompatibilities with ANSI X3.135-1989”, is an informative Annex. It lists the inc

42、ompatibilities between this version of this American National Standard and ANSI X3.135-1989. 29) Annex F, “Maintenance and interpretation of SQL”, is an informative Annex. It identifies SQL interpretations and corrections that have been processed by ANSI Accredited Committee X3 since adoption of ANS

43、I X3.135-1989. In the text of this American National Standard, clauses begin a new odd- numbered page, and in clause 5, Lexical elements, through clause 22, Status codes, subclauses begin a new page. Any resulting blank space is not significant. xiv AMERICAN NATIONAL STANDARD ANSI X3.1 35-1992 Ameri

44、can National Standard for Information Systems - Database Language - SQL 1 Scope This American Standard defines the data structures and basic operations on SQL-data. It provides functional capabilities for creating, accessing, maintaining, controlling, and protecting SQL-data. Note: The framework for

45、 this American Standard is described by the Reference Model of Data Management (ISO/IEC DIS 10032:1991). This American Standard specifies the syntax and semantics of a database language - for specifying and modifying the structure and the integrity constraints of SQL-data, - for declaring and invoki

46、ng operations on SQL-data and cursors, and - for declaring database language procedures and embedding them into a standard programming language. It also specifies an Information Schema that describes the structure and the integrity constraints of SQL-data. This American Standard - provides a vehicle

47、 for portability of data definitions and compilation units between SQL- implementations, - provides a vehicle for interconnection of SQL-implementations, - specifies syntax for embedding SQL-statements in a compilation unit that otherwise conforms to the standard for a particular programming languag

48、e. It defines how an equivalent compilation unit may be derived that conforms to the particular programming language standard. In that equivalent compilation unit, each embedded SQL-statement has been replaced by statements that invoke a database language procedure that contains the SQL-statement, a

49、nd - specifies syntax for direct invocation of SQL-statements. This American Standard does not define the method or time of binding between any of: - database management system components, - SQL data definition declarations, - SQL procedures, or - compilation units, including those containing embedded SQL. American National Standard X3.135-1992 Implementations of this American Standard may exist in environments that also support application programming languages, end-user query languages, report generator systems, data dictionary systems, program library systems, and distributed communicat