ISO/IEC 9075:1992
(Main)Information technology — Database languages — SQL
Information technology — Database languages — SQL
Technologies de l'information — Langages de base de données — SQL
General Information
Relations
Standards Content (Sample)
INTERNATIONAL
lSO/IEC
STANDARD 9075
Third edition
1992-11-01
Information technology - Database
languages - SQL
Technologies de I ‘information - Langages de base de donnkes - SQL
Reference number
ISO/IEC 9075:1992(E)
---------------------- Page: 1 ----------------------
1somc 9075:1992 (E)
Page
Contents
Foreword . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi
Introduction.xlii
1
Scope . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1
No~ativereferences. 3
2
5
.....................................
3 Defhitions, notations, and conventions
5
.............................................................
31 . Definitions
5
Definitions taken from ISO/IEC 10646. .
3.1.1
5
Definitions taken from ISO 8601 .
3.1.2
5
.............................
Definitions provided in this International Standard
3.1.3
7
. Notation .
32
8
. Conventions .
33
8
3.3.1 Informative elements .
8
3.32 Specification of syntactic elements .
9
3.3.3 Specification of the Information Schema .
9
3.3.4 Useofterms .
3.3.4.1 Exceptions . 9
9
3.3.4.2 Syntactic containment .
10
...........................................
3.3.4.3 Terms denoting rule requirements
10
3.3.4.4 Rufe evaluation Order. .
11
3.3.4.5 Conditional rules .
11
....................................................
3.3.4.6 Syntactic Substitution
12
............................................................
3.3.4.7 Otherterms
3.3.5 Descriptors.l 2
13
Index ty-pography .
3.3.6
13
..................................
. Object identifier for Database Language SQL
34
4 Concepts.l 5
15
4.1 Datatypes .
16
4.2 Character strings .
16
.....................................
4.2.1 Character strings and collating sequences
0 1s0/1Fc 1992
All rights reserved. No part of this publication may be reproduced or utilized in any form or by any
means, electronie or mechanical, including photocopying and microfilm, without permission in
writing from the publisher.
ISO/IEC Copyright Office l Case postale 56 l CH-121 1 Geneve 20 l Switzerland
Printed in Switzerland
ii Database Language SQL
---------------------- Page: 2 ----------------------
ISOKEC 9075:1992 (E)
....................................... 17
4.2.2 Operations involving Character strings
............. 17
4.2.2.1 Operatcns that operate on Character strings and return Character strings
.................................... 18
4.2.2.2 Other Operators involving Character strings
................................... 18
Rules determining collating sequence usage
4.2.3
..2 1
Bitstrings
4.3 .
........................................
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 retum bit strings
......................................... 21
4.3.2.2 Other Operators involving bit strings
..2 1
4.4 Numbers
............................................................
................................................ 22
4.4.1 Characteristics of numbers
.............................................. 23
4.4.2 Operations involving numbers
................................................... 23
4.5 Datetimes and intervals
4.5.1 Datetimes.Z 3
25
4.5.2 Intervals .
.................................. 26
4.5.3 Operations involving datetimes and intervals
.................................... 27
4.6 Type conversions and mixing of data types
4.7 Domains.2 8
4.8 Columns.2 8
4.9 Tables.2 9
..................................................... 31
4.10 Integrity constraints
................................................... 32
4.10.1 Checking of constraints
........................................................ 32
4.10.2 Table constraints
...................................................... 33
4.10.3
Domain constraints
33
4.10.4 .
Assertions
34
4.11 SQL-Schemas. .
34
4.12 Catalogs .
...................................................... 35
4.13 Clusters of catalogs
35
4.14 SQL-data .
35
4.15 SQL-environment .
36
4.16 Modules .
36
4.17 Procedures .
36
............................................................
4.18 Parameters
....................................................... 36
4.18.1 Status Parameters
........................................................ 37
4.18.2 Data Parameters
..................................................... 37
4.18.3 Indicator Parameters
......................................................... 37
4.19
Diagnostics area
........................................... 38
4.20
Standard programming languages
38
4.21 Cursors .
......................................................... 39
4.22 SQL-Statements
................................................ 39
4.22.1 Classes of SQL-Statements.
........................................ 40
4.22.2 SQL-Statements classified by fwnction
43
...............................................
4.22.3 Embeddable SQL-Statements
......................... 44
4.22.4 Preparable and immediately executable SQL-Statements
......................................... 46
4.22.5 Directly executable SQL-Statements
....................................... 47
4.22.6 SQL-Statements and transaction states
48
4.23 Embedded Syntax .
.................................................. 49
4.24 SQL dynamic Statements
.................................................. 51
4.25 Direct invocation of SQL
Table of Contents iii
---------------------- Page: 3 ----------------------
ISOIIEC 9075:1992 (E)
4.26 Pnvileges.5 1
53
4.27 SQL-agents .
53
4.28 SQL-transactions .
4.29 SQL-connections.5 6
57
4.30 SQL-Sessions .
59
4.31 Client-server Operation. .
59
4.32 Information Schema. .
60
4.33 Leveling .
4.34 SQLFlagger.6 0
5 Lexicalelements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
51 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
52 . and . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66
53 . . 71
54 . Names and identifiers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
6 Scalarexpressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
85
61 . cdatatype> .
91
................................
62 . and
94
........................................................
63 . ctable reference>
96
.
64 .
98
................................................
65 .
101
66 . cnumeric value function> .
105
6.7 ostring value function> .
110
. .
68 cdatetime value function>
112
69 ccase expression> .
114
6’10 . .
124
6.11 .
126
................................................
6.12
128
.................................................
6.13 ostring value expression>
132
...............................................
6.14 cdatetime value expression>
135
................................................
6.15 cinterval value expression>
..13 9
.....................................................
7 Queryexpressions
139
7.1 crow value constructor> .
141
72 . .
142
73 . ctable expression> .
..14 3
74 . cfrom clause> .
145
75 . .
..15 0
76 . .
..15 1
7.7 .
..15 3
78 . chavingclause>. .
155
.....................................................
79 cquery specification>
159
7.10 . .
........................ 165
7.11
cscalar subquery>, , and
8
Predicates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .167
81 .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .167
82 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
83 . cbetween predicate> . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172
iv Database Language SQL
---------------------- Page: 4 ----------------------
ISO/IEC 9075:1992 (E)
cinpredicate> .17 3
84 . .
.17 5
85 . .
178
86 . .
........................................... 180
87 . cquantified comparison predicate>
182
88 . cexists predicate> .
183
.
89 .
184
cmatch predicate>
8.10 .
186
8.11 .
188
8.12 csearch condition> .
191
9 Data assignment rules .
. Retrieval assignment . 191
91
92 . Store assignment . 193
195
93 . Set Operation result data types .
197
10 Additional common elements .
197
10.1 Cinterval qualifier> .
..20 1
10.2 .
..20 3
10.3 cprivileges> .
.205
10.4 .
.207
10.5 .
......................... 208
10.6 cconstraint name definition> and cconstraint attributes>
Schema defknition and manipulation . .211
11
.2ll
11.1 .
.214
11.2 .
11.3 .21 6
..218
11.4 .
11.5 .22 1
.224
11.6 ctable constraint definition> .
.226
11.7 .
.228
11.8 creferential constraint definition> .
11.9 .233
.
11.10 . .235
11.11 .23 6
11.12 .237
.
11.13 . .238
.239
11.14 .
.240
11.15 .
.242
11.16 .
.243
11.17 .
.244
11.18 .
.245
11.19 .
,249
11.20 . .
11.21 .25 0
.252
11.22 .
.253
11.23 . .
............................................. .254
11.24
11.25 . .255
11.26 . .256
Table of Contents v
---------------------- Page: 5 ----------------------
ISO/IEC 9075:1992 (E)
. . . . . 257
11.27 . . . .
-259
11.28
................................................
.261
11.29
............................................
.262
11.30
....................................................
.265
11.31 .
.267
11.32 ctranslation definition>
..................................................
.269
11.33 .
..27 0
11.34
..................................................
.272
11.35
...............................................
.273
11.36 qrant Statement>
......................................................
.276
11.37
.....................................................
12 Module.28 1
12.1 .28 1
.284
12.2 .
..28 5
.
12.3
.........................................................
.290
Calls to a
12.4 .
303
12.5 .
.307
Data manipulation
13 .
307
13.1 .
..310
13.2
.....................................................
312
13.3 .
.315
13.4
......................................................
316
13.5 . 318 13.6 cdelete Statement: positioned> . 320 13.7 . 322 13.8 . ...................................................... 325 13.9 Cupdate Statement: positioned> . .328 Cupdate Statement: searched> 13.10 . 330 13.11 . .333 14 Transaction management . 333 . 14.1 . 335 14.2 . .337 14.3 . 339 14.4 ..................................................... 15 Connectionmanagement.34 1 341 15.1 . 344 15.2 . 346 15.3 cdisconnect Statement> . ..34 9 16 Sessionmanagement . 349 16.1 cset catalog Statement> . 350 .................................................. 16.2 . 351 16.3 . 352 16.4 . 354 16.5 . . vi Database Language SQL ---------------------- Page: 6 ---------------------- ISODEC 9075:1992 (E) 17 DynamicSQL.35 5 17.1 Description of SQL item descriptor areas ...................................... 355 17.2 360 ............................................. 17.3 cdeallocate descriptor Statement> ........................................... 362 17.4 ................................................ 363 17.5 cset descriptor Statement>. ................................................ 366 17.6 369 ..................................................... 17.7 cdeallocate prepared Statement> 375 ............................................ 17.8 cdescribe Statement> 376 ..................................................... 17.9 . . .37 7 17.10 cexecute Statement> 383 ..................................................... 17.11 . 385 ............................................ 17.12 .387 ................................................ 17.13 callocate cursor Statement> 388 ................................................ 17.14 ................................................ 390 17.15 ................................................ 392 394 17.16 cdynamic close Statement> ................................................ 17.17 cdynamic delete Statement: positioned> 395 ...................................... 17.18 ..................................... 396 17.19 cpreparable dynamic delete Statement: positioned> ............................. 398 17.20 cpreparable dynamic update Statement: positioned> 399 ............................. 18 Diagnosticsmanagement.40 1 18.1 . .401 .............................................. 19 EmbeddedSQL ........................................................ ..411 19.1 ............................................ .411 19.2 .......................................... .418 19.3 ............................................ .421 19.4 .............................................. .424 19.5 ......................................... .428 19.6 ......................................... .431 19.7 .434 ........................................ 19.8 .......................................... .436 19.9 . .439 ........................................... 20 Direct invocation of SQL .443 ................................................. 20.1 cdirect SQL Statement>. .443 ................................................. 20.2 cdirect select Statement: multiple rows> 447 ...................................... 21 Information Schema and Definition Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .449 21.1 Introduction.449 21.2 Information Schema. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .450 21.2.1 INFORMATION SCHEMA Schema. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .450 - 21.2.2 INFORMATIONJXHEMA CATALOG NAME base table. . . . . . . . . . . . . . . . . . . . . . . . .451 - - 21.2.3 INFORNATION-SCHEMA-CATALOG NAME-CARDINALITY assertion . . . . . . . . . . . .452 - 21.2.4 SCHEMAII’Aview.453 DOMAINSview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .454 21.2.5 21.2.6 DOMAIN-CONSTRAINTS view . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .455 21.2.7 TABLESview.,.456 Table of Contents vii ---------------------- Page: 7 ---------------------- ISO/IEC 9075:1992 (E) 21.2.8 VIEWSview.45 7 ..45 8 21.2.9 COLUMNSview . .460 21.2.10 TABLE-PRIVILEGES view . ,461 21.2.11 COLUMN_PRIVILEGES view . .462 21.2.12 USAGE-PRIVILEGES view . 21.2.13 TABLE-CONSTRAINTS view . .463 .464 21.2.14 REFERENTIAL CONSTR&NTS view . - 21.2.15 CHECK-CONSTRAINTS view . .465 ............................................. .466 21.2.16 KEY-COLUh!INJJSAGE view ..................................................... .467 21.2.17 ASSER.TIONS view .468 21.2.18 CHARACTER-SETS view . ..................................................... .469 21.2.19 COLLATIONS view ................................................... .470 21.2.20 TRANSLATIONS view .............................................. .471 21.2.21 VIEW-TABLEJJSAGE view 212.22 VIEW-COLUMNJJSAGE view . .472 .473 21.2.23 CONSTRAINT TABLE USAGE view . - - .474 21.2.24 CONSTRJJINT-COLUMNJJSAGE view . .475 21.225 COLUMN-DOMAINJSAGE view . 21.2.26 SQL LANGUAGES view . .476 - .477 21.2.27 SQLJDENTIFIER domain . .477 21.2.28 CHARACTER-DATA domain . ............................................ .478 21.2.29 CARDINAL NUMBER domain - ..47 9 21.3 DefinitionSchema . ........................................................... .479 21.3.1 Introduction .......................................... .480 21.3.2 DEFINITION-SCHEMA Schema. ...................................................... ,481 21.3.3 USERS base table .................................................. .482 21.3.4 SCHEMATA base table ...................................... .483 21.3.5 DATA-TYPE-DESCRIPTOR base table .................................................... .485 21.3.6 DOMAINS base table ....................................... .486 21.3.7 DOMAIN-CONSTRAINTS base table ..................................................... .488 21.3.8 TABLES base table ...................................................... .489 21.3.9 VIEWS base table ................................................... ,491 21.3.10 COLUMNS base table .493 21.3.11 VIEW-TABLEJJSAGE base table . .494 21.3.12 VIEW-COLUMN USAGE base table . - ......................................... .495 21.3.13 TABLE-CONSTRAINTS base table ......................................... .497 21.3.14 KEY COLUMNJJSAGE base table - .................................. .499 21.3.15 REFERENTLAL-CONSTRAINTS base table ......................................... 501 21.3.16 CHECK CONSTRAINTS base table - ........................................ 21.3.17 CHECK TABLE USAGE base table .502 - - 21.3.18 CHECK COLUMN USAGE base table . 503 - - .................................................. 21.3.19 ASSERTIONS base table 504 21.320 TABLE PRIVILEGES base table . 505 - 21,321 COLUMN-PRMLEGES base table. . .5O7 21.322 USAGE PRIVILEGES base table . 509 - ............................................. 21.3.23 CHARACTER SETS base table 511 - 21.3.24COLLATIONSbasetable. . -513 .............................................. 21.3.25 TRANSLATIONS base table .515 ............................................. 21.3.26 SQL LANGUAGES base table. 517 - viii Database Language SQL ---------------------- Page: 8 ---------------------- ISOLIEC 9075:1992 (E) 21.4 Assertions on the base tables ............................................... 520 21.4.1 UNIQUE-CONSTRAINT-NAME assertion ................................... .520 21.4.2 EQUALJXEY-DEGREES assertion ......................................... .521 21.4.3 KEY~DEGREE~GREATER~THAN~OR~EQUAL~TO~l assertion. ................... 522 22 Statuscodes.52 3 22.1 SQLSTATE.52 3 22.2 SQLCODE ........................................................... ..52 7 23 Conformance ......................................................... ...52 9 23.1 , Introduction. ..52 9 ........................................................ 23.2 Claims of conformance ................................................... .529 23.3 Extensions and Options ................................................... 530 23.4 Flagger requirements .................................................... .530 23.5 Processing methods ..................................................... .530 Annex A Leveling the S ...
Questions, Comments and Discussion
Ask us and Technical Secretary will try to provide an answer. You can facilitate discussion about the standard in here.