Technical Standard Data Management: Structured Query Language


Download Technical Standard Data Management: Structured Query Language


Preview text

Technical Standard Data Management: Structured Query Language (SQL)
Version 2
HNICAL

TEC

STAND

ARD

[This page intentionally left blank]

X/Open CAE Specification
Data Management: Structured Query Language (SQL), Version 2
X/Open Company Ltd.

© March 1996, X/Open Company Limited
All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic, mechanical, photocopying, recording or otherwise, without the prior permission of the copyright owners.

X/Open CAE Specification Data Management: Structured Query Language (SQL), Version 2
ISBN: 1-85912-151-9 X/Open Document Number: C449
Published by X/Open Company Ltd., U.K.
Any comments relating to the material contained in this document may be submitted to X/Open at:
X/Open Company Limited Apex Plaza Forbury Road Reading Berkshire, RG1 1AX United Kingdom or by Electronic Mail to: [email protected]

ii

X/Open CAE Specification

Contents

Chapter 1
1.1 1.2 1.2.1 1.2.2 1.3 1.3.1 1.3.2 1.3.3 1.3.4 1.4 1.4.1 1.4.2 1.4.3 1.4.4 1.4.5 1.4.6 1.5
Chapter 2
2.1 2.1.1 2.2 2.3 2.3.1 2.3.2 2.3.3 2.3.4 2.3.5 2.4 2.4.1 2.4.2 2.4.3 2.4.4 2.5 2.5.1 2.5.2 2.5.3 2.5.4 2.5.5 2.6 2.6.1

Introduction............................................................................................... 1
Relational Database Standards ................................................................ 1 The X/Open Specification ........................................................................ 2
Audience.................................................................................................... 2 Compliance Terminology....................................................................... 2 Compliance Policy ...................................................................................... 5 Language Embedding............................................................................. 5 Flagging Non-portable Usage ............................................................... 5 Distributed Transaction Delimitation.................................................. 6 Character Set Support............................................................................. 6 This Issue ...................................................................................................... 7 Transitional SQL....................................................................................... 7 Internationalisation ................................................................................. 9 Alignment with SPIRIT SQL ................................................................. 9 Other New Material in this Issue ......................................................... 10 Substantive Changes in this Issue ........................................................ 10 Documentation and Policy Changes in this Issue............................. 12 SQL Registry ................................................................................................ 13
Concepts ...................................................................................................... 15
Introduction ................................................................................................. 15 General Terms........................................................................................... 15
Data Types and Values............................................................................... 16 Tables............................................................................................................. 17
Attributes of Columns ............................................................................ 17 Types of Table ........................................................................................... 17 Indexes ....................................................................................................... 18 System Views............................................................................................ 18 Integrity Constraints ............................................................................... 18 Database System ......................................................................................... 19 Clients and Servers.................................................................................. 19 Database Organisation ........................................................................... 20 Character Sets ........................................................................................... 21 Collations................................................................................................... 23 Using SQL..................................................................................................... 24 Cursors....................................................................................................... 24 Executable SQL Statements ................................................................... 24 Embedded Constructs ............................................................................ 24 Dynamic SQL............................................................................................ 25 Return Status ............................................................................................ 25 Access Control............................................................................................. 26 Users........................................................................................................... 26

Data Management: Structured Query Language (SQL), Version 2

iii

Contents

Chapter

2.6.2 2.6.3 2.6.4 2.7 2.7.1 2.7.2
3
3.1 3.1.1 3.1.2 3.1.3 3.1.4 3.1.5 3.1.6 3.2 3.2.1 3.2.2 3.2.3 3.2.4 3.3 3.4 3.4.1 3.5 3.6 3.7 3.8 3.9 3.9.1 3.9.2 3.9.3 3.9.4 3.9.5 3.9.6 3.9.7 3.10 3.10.1 3.10.2 3.10.3 3.10.4 3.10.5 3.10.6 3.10.7 3.10.8 3.11 3.11.1 3.11.2 3.11.3

Ownership................................................................................................. 26 Authorisation Identifiers........................................................................ 26 Privileges ................................................................................................... 27 Transactions ................................................................................................. 28 Transaction Attributes ............................................................................ 28 Concurrent Transactions ........................................................................ 29
Common Elements ................................................................................ 31
Notation and Language Structure........................................................... 31 Notation..................................................................................................... 31 Language Structure ................................................................................. 32 Format of Object Qualification.............................................................. 35 Format of Column Qualification .......................................................... 36 Specifying the Character Set.................................................................. 36 Keywords................................................................................................... 37
Generic Data Types..................................................................................... 39 Character String ....................................................................................... 40 Numeric..................................................................................................... 41 Date/Time................................................................................................. 42 Interval....................................................................................................... 44
Rules for Determining Data Types.......................................................... 47 Literals........................................................................................................... 48
Pseudo-literals .......................................................................................... 50 Assignment .................................................................................................. 51 Comparison.................................................................................................. 53 Null Values................................................................................................... 54 Indicator Variables...................................................................................... 56 Expressions .................................................................................................. 57
Arithmetic Operators .............................................................................. 57 Date/Time and Interval Arithmetic .................................................... 57 String Operations..................................................................................... 59 Set Functions............................................................................................. 63 Dynamic Parameters ............................................................................... 64 CAST Function ......................................................................................... 65 Specifying a Collation ............................................................................. 66 Search Conditions and Predicates ........................................................... 67 Comparison Predicate............................................................................. 67 Quantified Comparison Predicate........................................................ 68 BETWEEN Predicate ............................................................................... 68 IN Predicate............................................................................................... 68 LIKE Predicate .......................................................................................... 69 NULL Predicate........................................................................................ 69 EXISTS Predicate...................................................................................... 70 OVERLAPS Predicate ............................................................................. 70 Queries .......................................................................................................... 71 Query Specifications ............................................................................... 71 Joined Tables ............................................................................................. 73 Query Expressions................................................................................... 75

iv

X/Open CAE Specification

Contents

Chapter Chapter

3.11.4 3.11.5 3.12
4
4.1 4.1.1 4.1.2 4.1.3 4.2 4.3 4.4 4.4.1 4.4.2 4.5 4.5.1 4.5.2 4.5.3 4.5.4 4.5.5 4.5.6 4.6 4.6.1 4.6.2 4.7
5
5.1 5.2 5.2.1 5.2.2 5.2.3 5.2.4 5.2.5 5.2.6 5.3 5.3.1 5.3.2 5.3.3 5.3.4 5.3.5 5.3.6 5.3.7 5.3.8 5.3.9 5.3.10 5.3.11 5.3.12

Sub-queries................................................................................................ 76 Correlation ................................................................................................ 76 Row Value Constructor ............................................................................. 78
Embedded Aspects ................................................................................ 79
Embedded SQL Host Program................................................................. 79 Embedded SQL Constructs ................................................................... 79 Embedded Host Variables and Indicator Variables.......................... 80 Data Types and Embedded Host Variables........................................ 82
SQL Declare Section ................................................................................... 84 DECLARE AUTHORIZATION Statement ............................................ 86 Cursors.......................................................................................................... 87
DECLARE CURSOR Statement ............................................................ 88 Dynamic DECLARE CURSOR Statement .......................................... 89 SQL Statement Outcomes ......................................................................... 90 Outcome Categories................................................................................ 90 SQLSTATE Status Variable..................................................................... 90 Diagnostics Area ...................................................................................... 91 SQLCODE Status Variable..................................................................... 92 Application Usage ................................................................................... 93 Other Effects of Errors............................................................................. 93 WHENEVER Statement ............................................................................ 94 Special Rule for C..................................................................................... 94 Special Rules for COBOL ....................................................................... 94 Multiple Compilation Units ..................................................................... 95
Executable SQL Statements............................................................. 97
Classification of SQL Statements............................................................. 97 General Diagnostics ................................................................................... 99
Syntax Checking ...................................................................................... 99 Expression Errors..................................................................................... 100 Assignment Errors................................................................................... 100 Constraint Checking ............................................................................... 101 Read-only Transaction Violation .......................................................... 101 Connection Errors.................................................................................... 101 Data Definition Statements....................................................................... 102 General Diagnostics ................................................................................ 102 ALTER TABLE .......................................................................................... 102 CREATE CHARACTER SET ................................................................. 103 CREATE COLLATION ........................................................................... 104 CREATE INDEX....................................................................................... 105 CREATE SCHEMA .................................................................................. 106 CREATE TABLE ....................................................................................... 107 CREATE TRANSLATION ...................................................................... 110 CREATE VIEW ......................................................................................... 111 DROP CHARACTER SET...................................................................... 112 DROP COLLATION................................................................................ 113 DROP INDEX ........................................................................................... 113

Data Management: Structured Query Language (SQL), Version 2

v

Contents

5.3.13 5.3.14 5.3.15 5.3.16 5.3.17 5.3.18 5.4 5.4.1 5.4.2 5.4.3 5.4.4 5.4.5 5.4.6 5.4.7 5.4.8 5.4.9 5.5 5.5.1 5.5.2 5.5.3 5.5.4 5.5.5 5.5.6 5.5.7 5.5.8 5.5.9 5.5.10 5.5.11 5.5.12 5.6 5.6.1 5.6.2 5.6.3 5.6.4 5.7 5.7.1 5.7.2 5.7.3 5.7.4 5.7.5 5.7.6 5.7.7 5.7.8 5.8 5.8.1 5.8.2 5.8.3 5.9

DROP SCHEMA ...................................................................................... 113 DROP TABLE............................................................................................ 114 DROP TRANSLATION .......................................................................... 114 DROP VIEW.............................................................................................. 114 GRANT ...................................................................................................... 115 REVOKE .................................................................................................... 116 Data Manipulation Statements ................................................................ 118 CLOSE........................................................................................................ 118 Positioned DELETE ................................................................................. 118 Searched DELETE.................................................................................... 118 FETCH........................................................................................................ 119 INSERT....................................................................................................... 119 OPEN.......................................................................................................... 120 SELECT INTO .......................................................................................... 121 Positioned UPDATE ................................................................................ 121 Searched UPDATE ................................................................................... 122 Dynamic SQL Statements ......................................................................... 124 SQL Descriptor Areas ............................................................................. 124 USING and INTO Clauses..................................................................... 129 ALLOCATE DESCRIPTOR.................................................................... 130 DEALLOCATE DESCRIPTOR .............................................................. 130 DESCRIBE ................................................................................................. 131 EXECUTE .................................................................................................. 132 EXECUTE IMMEDIATE ......................................................................... 133 Dynamic FETCH...................................................................................... 133 GET DESCRIPTOR .................................................................................. 134 Dynamic OPEN........................................................................................ 135 PREPARE................................................................................................... 136 SET DESCRIPTOR ................................................................................... 137 Transaction Control Statements .............................................................. 139 General Diagnostics ................................................................................ 139 COMMIT ................................................................................................... 139 ROLLBACK............................................................................................... 140 SET TRANSACTION .............................................................................. 140 Connection Statements .............................................................................. 142 Current and Dormant Connections ..................................................... 142 Default Connection ................................................................................. 142 State Table.................................................................................................. 142 Connection Context................................................................................. 143 General Diagnostics ................................................................................ 143 CONNECT ................................................................................................ 145 DISCONNECT ......................................................................................... 146 SET CONNECTION................................................................................ 146 Session Statements ..................................................................................... 147 SET CATALOG......................................................................................... 147 SET NAMES.............................................................................................. 147 SET SCHEMA........................................................................................... 148 SET SESSION AUTHORIZATION.......................................................... 149

vi

X/Open CAE Specification

Contents

5.10
Chapter 6
Chapter 7
7.1 7.1.1 7.1.2 7.1.3 7.1.4 7.1.5 7.1.6 7.1.7 7.2 7.2.1 7.3 7.4 7.5 7.6 7.7 7.8
Appendix A
A.1 A.2 A.3
Appendix B Appendix C
C.1 C.2

Diagnostic Statement ................................................................................. 150
Information Schema............................................................................. 155
CHARACTER_SETS ...................................................................................... 156 COLLATIONS ................................................................................................. 157 COLUMN_PRIVILEGES............................................................................... 158 COLUMNS ...................................................................................................... 160 INDEXES ......................................................................................................... 164 SCHEMATA..................................................................................................... 165 SERVER_INFO ............................................................................................... 166 SQL_LANGUAGES........................................................................................ 168 TABLE_PRIVILEGES ..................................................................................... 169 TABLES ............................................................................................................ 171 TRANSLATIONS ............................................................................................ 172 USAGE_PRIVILEGES ................................................................................... 173 VIEWS .............................................................................................................. 174
Implementation-specific Issues .................................................... 175
Limits............................................................................................................. 175 Supplementary Definitions.................................................................... 175 Additional Limits..................................................................................... 177 Storage Capacity ...................................................................................... 178 Statement Complexity ............................................................................ 179 Embedded Aspects (SPIRIT Only) ....................................................... 179 Derived Limits (SPIRIT Only)............................................................... 179 Language-specific Limits (SPIRIT Only) ............................................ 180
Vendor-specific SQL................................................................................... 181 Vendor Escape Clause............................................................................. 181
Restrictions on Names ............................................................................... 183 Data Definition Statements in Transactions.......................................... 183 Commitment of Transactions................................................................... 184 Error Treatment ........................................................................................... 184 Textual Sequencing..................................................................................... 184 SELECT ......................................................................................................... 185
Syntax Summary..................................................................................... 187
Common Elements ..................................................................................... 187 Embedded Aspects ..................................................................................... 195 Executable SQL Statements ...................................................................... 198
SQLSTATE Values ................................................................................. 203
ISO Database Language SQL ......................................................... 209
Included Features from Intermediate and Full SQL............................ 210 Extensions to the International Standard .............................................. 212

Data Management: Structured Query Language (SQL), Version 2

vii

Contents

Appendix

D
D.1 D.2 D.3 D.3.1 D.4 D.5 D.6 D.7 D.8

Appendix E
E.1 E.2 E.3 E.4 E.5 E.6

Appendix F
F.1 F.2 F.3

List of Figures
5-1
List of Tables
2-1
3-1 3-2 3-3 3-4 3-5 3-6 3-7 3-8 3-9 5-1 5-2 5-3

SPIRIT SQL, Issue 2 ............................................................................. 213
Introduction to SPIRIT............................................................................... 213 Conformance Requirements..................................................................... 215 INCLUDE SQLCA ...................................................................................... 225
Contents of the SQLCA .......................................................................... 225 Sizing for Database Constructs................................................................ 227 Resolution of Implementation-defined Items....................................... 228 Interpretation of the International Standard......................................... 231 Differences from X/Open SQL ................................................................ 231 Conformance Claim in SQL_LANGUAGES......................................... 231
SPIRIT SQL, Issue 3 ............................................................................. 233
Conformance Requirements..................................................................... 233 Sizing for Database Constructs................................................................ 237 Resolution of Additional Implementation-defined Items.................. 238 Enhanced Internationalisation ................................................................. 239 Differences from X/Open SQL ................................................................ 241 Conformance Claim in SQL_LANGUAGES......................................... 242
Future Extensions................................................................................... 243
Security ......................................................................................................... 243 Adoption of Additional Material from SQL Standards...................... 243 Host Language Issues ................................................................................ 243
Glossary ....................................................................................................... 245
Index............................................................................................................... 251
Reporting of Connection Errors (Flowchart) ........................................... 144
Database Phenomena Possible at Various Transaction Isolation Levels............................................................................................... 29 Classification of Data Types ........................................................................ 39 Named Character String Data Types......................................................... 40 Named Numeric Data Types....................................................................... 41 Named Date/Time Data Types .................................................................. 43 Named INTERVAL Data Types.................................................................. 45 Lengths of Named INTERVAL Data Types ............................................. 46 Treatment of Null Values in Various Contexts ........................................ 55 String Operations........................................................................................... 59 Set Functions................................................................................................... 63 Object Types with General Diagnostics for Existence Checking......... 100 Assumed Data Type of Dynamic Parameters Based on Context......... 136 Implicit Setting of Item Descriptor Area Fields....................................... 138

viii

X/Open CAE Specification

Preparing to load PDF file. please wait...

0 of 0
100%
Technical Standard Data Management: Structured Query Language