1、INTERNATIONAL STANDARD ISOIIEC 9075-1 9075-2 9075-5 First edition 1999-1 2-01 AMENDMENT 1 2001 -03-1 5 Information technology - Database languages - SQL - Part 1: Framework (SQLIFramework) Part 2: Foundation (SQLIFoundation) Part 5: Host Language Bindings (SQLIBindings) AMENDMENT 1 : On-Line Analyti
2、cal P rocess i n g (S Q L/O LAP) Technologies de Iinformafion - Langages de base de donnes - SQL - Partie 1: Charpenfe (SQLICharpenfe) Partie 2: Fondafions (SQLIFondafions) Partie 5: Lianfs de langage dhfe (SQLILianfs) AMENDEMENT 1: Traifemenf analyfique en ligne (SQLIOLAP) Adopted by INCITS (InterN
3、ational Committee for Information Technology Standards) as an American National Standard. Date of ANSI Approval: 10/22/01 Published by American National Standards Institute, 25 West 43rd Street, New York, New York 10036 Copyright 2002 by Information Technology Industry Council (ITI). All rights rese
4、rved. These materials are subject to copyright claims of International Standardization Organization (ISO), International Eledrotechnical Commission (IEC), American National Standards Institute (ANSI), and Information Technology Industry Council (ITI). Not for resale. No part of this publication may
5、be reproduced in any form, including an electronic retrieval system, without the prior written permission of ITI. All requests pertaining to this standard should be submitted to ITI, 1250 Eye Street NW, Washington, DC 20005. Printed in the United States of America Reference number ISO/IEC 9075 (part
6、s 1, 2 and 5):1999/Amdl:2001(E) ISO/IEC 2001 ISO/IEC 9075 (parts 1,2 and 5):1999/Amd.l:2001(E) PDF disclaimer This PDF file may contain embedded typefaces. In accordance with Adobes licensing policy, this file may be printed or viewed but shall not be edited unless the typefaces which are embedded a
7、re licensed to and installed on the computer performing the editing. In downloading this file, parties accept therein the responsibility of not infringing Adobes licensing policy. The IS0 Central Secretariat accepts no liability in this area. Adobe is a trademark of Adobe Systems Incorporated Detail
8、s 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 IS0 member bodies. In the unlikely event that a problem rel
9、ating to it is found, please inform the Central Secretariat at the address given below. O ISOAEC 2001 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
10、, without permission in writing from either IS0 at the address below or ISOs member body in the country of the requester. IS0 copyright office Case postale 56 CH-I211 Geneva 20 Tel. + 41 22 749 O1 11 Fax +41227490947 E-mail copyrightiso.ch Web www.iso.ch Printed in Switzerland II O ISOAEC 2001 -All
11、rights reserved ISO/IEC 9075 (parts 1. 2 and 5):1999/Amd.l:2001 (E) Contents Page Foreword vii Introduction . viii 1 Scope 1 2 Normativereferences . 3 3 Definitions. notations. and conventions . 5 3.1 Definitions . 5 3.2 Notation . 5 3.3 Conventions 5 3.3.1 Use ofterms 5 3.3.1.1 Syntactic containmen
12、t 5 3.3.2 Relationships to other parts of ISOLEC 9075 5 3.3.2.1 Clause. Subclause. and Table relationships 5 4 Concepts 9 4.1 Numbers 9 4.1.1 Operations involving numbers 9 4.2 Tables . 10 4.2.1 Windowed tables 10 4.3 Data analysis operations (involving tables) 11 4.3.1 Group functions . 11 4.3.2 Wi
13、ndow functions 12 4.3.3 Aggregate functions 13 5 Lexical elements . 17 5.1 and 17 5.2 Names and identifiers 19 6 Scalarexpressions . 21 6.1 21 6.2 . 23 6.3 . 27 6.4 . 31 O ISOAEC 2001 . All rights reserved Contents iii ISO/IEC 9075 (parts 1. 2 and 5):1999/Amdl:2001 (E) 7 Query expressions . 33 7.1 .
14、 33 7.2 34 7.3 . 35 7.4 . 36 7.5 37 7.6 . 47 8 Additional common elements 51 8.1 . 51 8.2 . 61 9 Schema definition and manipulation 63 9.1 . 63 9.2 64 10 SQL-client modules 65 10.1 Calls to an cexternally-invoked procedure 65 11 Data manipulation . 67 11.1 . 67 11.2 . 68 12 Dynamic SQL 69 12.1 . 69
15、13 Information Schema 71 13.1 Definition of SQL built-in functions 71 14 Statuscodes . 73 14.1 SQLSTATE . 73 15 Conformance 75 15.1 General conformance requirements 75 Annex A SQL conformance summary 77 Annex B Implementation-defined elements . 81 Annex C Implementation-dependent elements 85 Annex D
16、 SQL feature and package taxonomy . 87 Annex E SQL Packages 89 E.l OLAP . 89 Index 91 iv On-Line Analytical Processing (SQL/OLAP) O ISOAEC 2001 . All rights reserved ISO/IEC 9075 (parts 1,2 and 5):1999/Amd.l:2001 (E) FIGURES Figure Page 1 9 Illustration of WIDTHBUCmT Semantics . . . . . . . . . . .
17、. . . . . . . . . . . . . . . . . . . . . . . . O ISOAEC 2001 - All rights reserved Contents v ISO/IEC 9075 (parts 1. 2 and 5):1999/Amd.l:2001(E) TABLES Tables Page 1 Clause. Subclause. and Table relationships 5 SQLSTATE class and subclass values 73 Implied feature relationships . 75 2 3 4 SQL/OLAP
18、feature taxonomy for features outside Core SQL . 87 5 SQLPackages 89 vi On-Line Analytical Processing (SQL/OLAP) O ISOAEC 2001 . All rights reserved ISO/IEC 9075 (parts 1, 2 and 5):1999/Amd.l:2001 (E) Foreword IS0 (the International Organization for Standardization) and IEC (the International Electr
19、otechni- cal Commission) form the specialized system for worldwide standardization. National bodies that are members of IS0 or IEC participate in the development of International Standards through technical committees established by the respective organization to deal with particular fields of techn
20、ical activity. IS0 and IEC technical committees collaborate in fields of mutual interest. Other international organizations, governmental and non-governmental, in liaison with IS0 and IEC, also take part in the work. International Standards are drafted in accordance with the rules given in the ISOLE
21、C Directives, Part 3. In the field of information technology, IS0 and IEC have established a joint technical committee, ISOIEC JTC 1. Draft International Standards adopted by the joint technical committee are circu- lated to national bodies for voting. Publication as an International Standard requir
22、es approval by at least 75% of the national bodies casting a vote. Attention is drawn to the possibility that some of the elements of this Amendment may be the subject of patent rights. IS0 and IEC shall not be held responsible for identifying any or all such patent rights. Amendment 1 to parts 1, 2
23、 and 5 of ISOIEC 9075:1999 was prepared by Joint Technical Committee ISOIEC JTC 1, Information technology, Subcommittee SC 32, Data management and interchange. O ISOAEC 2001 - All rights reserved Foreword vii ISO/IEC 9075 (parts 1, 2 and 5):1999/Amd.l:2001(E) Introduction The organization of this Am
24、endment is as follows: Clause 1, “Scope”, specifies the scope of this Amendment. Clause 2, “Normative references”, identifies additional standards that, through reference in this Amendment, constitute provisions of this Amendment. Clause 3, “Definitions, notations, and conventions”, defines the nota
25、tions and conventions used in this Amendment. Clause 4, “Concepts”, presents concepts used in the definition of On-Line Analytical Processing facilities. Clause 5, “Lexical elements”, defines a number of lexical elements used in the definition of On-Line Analytical Processing facilities. Clause 6, “
26、Scalar expressions”, defines a number of scalar expressions used in the definition of On-Line Analytical Processing facilities. Clause 7, “Query expressions”, defines the elements of the language that produce rows and tables of data as used in On-Line Analytical Processing facilities. Clause 8, “Add
27、itional common elements”, defines additional common elements used in the definition of On-Line Analytical Processing facilities. Clause 9, “Schema definition and manipulation”, defines the schema definition and manipulation statements associated with the definition of On-Line Analytical Processing f
28、acilities. Clause 10, “SQL-client modules”, defines SQL-client modules and externally-invoked procedures. Clause 11, “Data manipulation”, defines data manipulation operations associated with On-Line Analytical Processing facilities. Clause 12, “Dynamic SQL”, defines the SQL dynamic statements. Claus
29、e 13, “Information Schema”, defines viewed tables that contain schema information related to On-Line Analytical Processing facilities. Clause 14, “Status codes”, defines SQLSTATE values related to On-Line Analytical Processing facilities. Clause 15, “Conformance”, defines the criteria for conformanc
30、e to this Amendment. Annex A, “SQL conformance summary”, is an informative Annex. It summarizes the confor- mance requirements of the SQL language. Annex B, “Implementation-defined elements”, is an informative Annex. It lists those features for which the body of this Amendment states that the syntax
31、, the meaning, the returned results, the effect on SQL-data andor Schemas, or any other behavior is partly or wholly implementation-defined. viii On-Line Analytical Processing (SQUOLAP) O ISOAEC 2001 - All rights reserved ISO/IEC 9075 (parts 1, 2 and 5):1999/Amd.l:2001 (E) Annex C, “Implementation-d
32、ependent elements”, is an informative Annex. It lists those features for which the body of this Amendment states that the syntax, the meaning, the returned results, the effect on SQL-data andor Schemas, or any other behavior is partly or wholly implementation-dependent. Annex D, “SQL feature and pac
33、kage taxonomy”, is an informative Annex. It identifies features of the SQL language specified in this Amendment by a numeric identifier and a short descriptive name. This taxonomy is used to specify conformance to Core SQL and may be used to develop other profiles involving the SQL language. Annex E
34、, “SQL Packages”, is an informative Annex. It specifies a package of SQL language features. In the text of this Amendment, Clauses begin a new odd-numbered page, and in Clause 5, “Lexical elements”, through Clause 15, “Conformance”, Subclauses begin a new page. Any resulting blank space is not signi
35、ficant. O ISOAEC 2001 - All rights reserved Introduction ix ISOIIEC 9075-1 :1999/Amd.l:2001(E) ISOIIEC 9075-2:1999/Amd.l:2001(E) ISOIIEC 9075-5:l 999/Amd.l:2001(E) Information technology - Database languages - SQL - Part 1: Framework (SQL/Framework) Part 2: Foundation (SQL/Foundation) Part 5: Host L
36、anguage Bindings (SQL/Bindings) AMENDMENT 1: On-Line Analytical Processing (SQL/OLAP) 1 Scope This Amendment specifies the syntax and semantics of database language facilities that support on-line analytical processing. The database language facilities that support on-line analytical processing incl
37、ude: - Rank functions. - Distribution functions. - Inverse distribution functions (percentiles). - Hypothetical set functions. - Cumulative and other forms of moving aggregates. - Variance, standard deviation, covariance, correlation, and linear regression functions. This amendment also incidentally
38、 defines several new numeric functions. NOTE 1 - The context for this Amendment is described by the Reference Model of Data Management (ISO/IEC 10032:1993). O ISOAEC 2001 - All rights reserved Scope 1 ISO/IEC 9075 (parts 1, 2 and 5):1999/Amd.l:2001(E) (Blank page) 2 On-Line Analytical Processing (SQ
39、WOLAP) O ISOAEC 2001 - All rights reserved ISO/IEC 9075 (parts 1, 2 and 5):1999/Amd.l:2001 (E) 2 Normative references The following normative documents contain provisions which, through reference in this text, constitute provisions of this Amendment. For dated references, subsequent amendments to, o
40、r revisions of, any of these publications do not apply. However, parties to agreements based on this Amendment are encouraged to investigate the possibility of applying the most recent editions of the normative documents indicated below. For undated references, the latest edition of the normative do
41、cument referred to applies. Members of IS0 and IEC maintain registers of currently valid International Standards. ISOIEC 9075-1:1999, Information technology - Database languages - SQL - Part 1: Frame- work (SQLIFramework). ISOIEC 9075-2:1999, Information technology - Database languages - SQL - Part
42、2: Founda- tion (SQL /Foundation). ISOIEC 9075-5:1999, Information technology -Database languages - SQL - Part 5: Host Language Bindings (SQL / Bindings). O ISOAEC 2001 - All rights reserved Normative references 3 ISO/IEC 9075 (parts 1, 2 and 5):1999/Amd.l:2001(E) (Blank page) 4 On-Line Analytical P
43、rocessing (SQWOLAP) O ISOAEC 2001 - All rights reserved ISO/IEC 9075 (parts 1, 2 and 5):1999/Amd.l:2001 (E) 3 Definitions, notations, and conventions 3.1 Definitions I insert this paragraph I For the purposes of this Amendment, the definitions given in ISOIEC 9075-i and ISOIEC 9075-2 apply. 3.2 Nota
44、tion I insert this paragraph I The syntax notation used in this Amendment is an extended version of BNF (“Backus Normal Form“ or “Backus Naur Form“). This version of BNF is fully described in Sub- clause 3.2, “Notation“, of ISOIEC 9075-1. 3.3 Conventions I insert this paragraph I Except as otherwise
45、 specified in this Amendment the conventions used in this Amendment are identical to those described in ISOIEC 9075-1 and ISOIEC 9075-2. 3.3.1 Use of terms 3.3.1.1 Syntactic containment I Replace 2nd paragraph AI directly contains BI ifAI contains BI without an intervening , , or that is not an . 3.
46、3.2 Relationships to other parts of ISOIEC 9075 3.3.2.1 Clause, Subclause, and Table relationships Table I-Clause, Subclause, and Table relationships Clause, Subclause, or Table in this part of ISO/IEC 9075 Corresponding Clause, Sub- clause, or Table from another Part Part containing corre- spondenc
47、e Clause 1, “Scope“ Clause 2, “Normative references“ Clause 3, “Definitions, notations, and conventions“ Subclause 3.1, “Definitions“ Subclause 3.2, “Notation“ Clause 1, “Scope“ Clause 2, “Normative references“ Clause 3, “Definitions, notations, and conventions“ Subclause 3.1, “Definitions“ Subclaus
48、e 3.2, “Notation“ ISO/IEC 9075-2 ISO/IEC 9075-2 ISO/IEC 9075-2 ISO/IEC 9075-2 ISO/IEC 9075-2 O ISOAEC 2001 - All rights reserved Definitions, notations, and conventions 5 ISO/IEC 9075 (parts 1, 2 and 5):1999/Amd.l:2001(E) 3.3 Conventions Table I-Clause, Subclause, and Table relationships (Cont.) Cla
49、use, Subclause, or Table in this part of ISO/IEC 9075 Subclause 3.3, “Conventions” Subclause 3.3.1, Vse of terms” Subclause 3.3.1.1, “Syntactic con- tainment” Subclause 3.3.2, “Relationships to other parts of ISO/IEC 9075” Subclause 3.3.2.1, “Clause, Sub- clause, and Table relationships” Clause 4, “Concepts” Subclause 4.1, “Numbers” Subclause 4.1.1, “Operations involv- ing numbers” Subclause 4.2, “Tables” Subclause 4.2.1, Vindowed tables” Subclause 4.3, “Data analysis opera- tions (involving tables)” Subclause 4.3.1, “Group functions” Subclause 4.3.2, VJindow fun
