ISO/IEC 9075-4:1996

Information technology — Database languages — SQL — Part 4: Persistent Stored Modules (SQL/PSM)

Technologies de l'information — Langages de base de données — SQL — Partie 4: Modules mémorisés persistants (SQL/PSM)

General Information

• Status: Withdrawn
• Publication Date: 25-Dec-1996
• Withdrawal Date: 25-Dec-1996
• ICS: 35.060 - Languages used in information technology
• Technical Committee: ISO/IEC JTC 1/SC 32 - Data management and interchange
• Drafting Committee: ISO/IEC JTC 1/SC 32 - Data management and interchange
• Current Stage: 9599 - Withdrawal of International Standard
• Completion Date: 16-Dec-1999

Relations

• Revised: ISO/IEC 9075-4:1999 - Information technology — Database languages — SQL — Part 4: Persistent Stored Modules (SQL/PSM)
• Effective Date: 15-Apr-2008

Standards Content (Sample)

ISO/IEC
INTERNATIONAL
9075-4
STANDARD
First edition
1996-l 2-l 5
Information technology - Database
languages - SQL -
Part 4:
Persistent Stored Modules (SQL/PSM)
- Langages de base de donnbes - SQL -
Technologies de I ‘information
Partie 4: Modules m6moris& persistan ts SQL (SQL/PSM)
Reference number
ISO/I EC 9075-4: 1996(E)

---------------------- Page: 1 ----------------------
ISO/IEC 90754: 1996(E)
Contents
Page
Foreword . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi
1
Scope . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1
2 Normativereferences. 3
5
.....................................
3 Definitions, notations, and conventions
5
31 . Definitions .
5
3.2 Notations .
5
33 Conventions. .
5
313.1 Useofterms .
5
3.3.1.1 Exceptions .
6
....................................................
3.3.1.2 Syntactic containment
6
.....................................................
3.3.1.3 Rule evaluation order
6
.........................................
3.3.2 Relationships to ISO/IEC 9075:1992
6
............................
3.3.2.1 New and modified Clauses, Subclauses, and Annexes
7
............................................
3.3.2.2 New and modified Format items
7
......................................
3.3.2.3 New and modified paragraphs and rules
8
..................................................
3.3.2.4 New and modified tables
8
....................................
3.3.2.5 Clause, Subclause, and Table relationships
15
..................................
3.4 Object identifier for Database Language SQL
4 Concepts.l 7
..................................................... 17
4.1 SQL-server Modules.
18
42 . SQL-invoked routines .
..2 1
4.3 SQL-paths .
21
4.3.1 Type conversions and mixing of data types .
4.4 Tables.2 2
22
4.5 Integrity constraints .
22
SQL-schemas .
4.6
23
Parameters .
4.7
23
4.7.1 Status parameters .
23
Diagnostics area.
4.8 .
23
...............................................................
4.9 Cursors
23
......................................................
4.10 Conditionhandling
0 ISO/IEC 1996
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 micro-
film, without permission in writing from the publisher.
ISO/IEC Copyright Office l Case postale 56 l CH-1211 Geneve 20 l Switzerland
Printed in Switzerland
ii

---------------------- Page: 2 ----------------------
ISO/IEC9075=4:1996(E)
WSOIIEC
25
4.11 .
SQL-statements
........................................ 25
4.11.1
SQL-statements classified by function
26
...............................................
4.11.2 Embeddable SQL-statements
......................... 26
4.11.3 Preparable and immediately executable SQL-statements
......................................... 26
4.11.4 Directly executable SQL-statements
26 .
.......................................
SQL-statements and transaction states
4.11.5
27
....................................................
4.11.6 Compoundstatements
27
..................................................
4.11.7 SQL-statement atomicity
..2 7
4.12 Privileges. .
28
...........................................................
4.13 SQL-sessions
29
5 Lexicalelements. .
.................................................. 29
51 . and
31
....................................................
52 . Names and identifiers
6 Scalar expressions .O . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
61 . and . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
35
......................................................
62 .
36
........................................................
63 .
38
.................................................
64 .
..3 9
65 . .
40
......................................................
.
66 .
..4 1
7 Query expressions .
41
.................................................
71 . .
72 .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
43
.....................................................
73 .
44
......................................................
1.4
....................... 45
7.5 , , and .
47
............................
8 Data assignment rules and routine determination
47
..............................................
81 . Subject routine determination
48
..........................................
82 . Type precedence list determination
51
..............................................
9 Additional common elements
51
.....................................................
91
64
9:2 .
65
...............................................
93 .
94 . .6 7
..6 8
.t .
95
69
......................................................
96 .
71
.......................................
10 Schema definition and manipulation.
71
......................................................
10.1
73
.................................................
10.2
75
.........................................................
10.3
77
...............................................
10.4
78
..................................................
10.5
79
...........................................
10.6
80
10.7 .
. . .
111

---------------------- Page: 3 ----------------------
...................................................... ..8 1

$0.8
82
....................................................
10.9
83
.................................................
PO‘BO domain statement>
84
.............................................
10.11
................................................ $5
40*f2 .............................................. 86
IO.13 adrop tsansla~tiom statementw
.................................................... 87
10.1.4 ................................................ 88
PO.f5 ............................................. 89
PO.16 -43QL-sewer module definitionw
................................................. 92
10.17 rap module statement>
................................................... 93
10.18 ................................................. 102
IO.19
....................................................... 103
10.20
....................................................... 105

10.21
. . . . . . .O . . . . * . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .A11
11
11.1 . . . . . . . . . . . . . . . . . .e . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .lll
..112

HI.2 .
....................................................
113
11.3 Calls ts a
...............................................
114
11.4
................................................
121
lB.5 -Data type correspondences.
..................................................... “129
I.2 uktticm
12.1 rsorw.12 9
..13 0
12.2 <~penstatementw .
.......................................................
131
12.3 .......................................................
132
12.4 ahse statementw
.............................................
133
12.5 ............................................. 134 12.6 cdelete statement: positioned> .............................................. 135 12.7 ............................................. 136 12.8 ............................................. 137 . 12.9 .............................................. 138 P2.PO ....................................................... 141 13 ContPsl statemenf;s ........................................................ 141 13.1 ...................................................... 142 13.2 ................................................... 143 13.3 .................................................... 146 13.4 chandler declaratisnw ................................................... 149 93.5 ................................................ 150 13.6 L variable declaration> ‘151 ................................................. 13.7 eassipment statement> ........................................................ 152 13.8 .......................................................... 154 13.9 ....................................................... 156 13.10 cleave statement> ‘157 ....................................................... 113.11 ....................................................... 158 43.12 ...................................................... 159 13.13 ......................................................... 160 13.14 ---------------------- Page: 4 ---------------------- ISO/IEC 90754: 1996(E) @ ISO/IEC ..16 3 14 Transactionmanagement . ..................................................... 163 14.1 ..................................................... 164 14.2 ..16 5 15 Sessionmanagement . ..................................................... 165 15.1 16 DynamicSQL.16 7 167 Description of SQL item descriptor areas. . 16.1 169 . 16.2 ..17 0 . . 16.3 ................................................. 175 17 Diagnostics management. ............................................... 175 17.1 . ...................................................... 178 17.2 ..................................................... 179 17.3 cresignal statement> ..18 1 EmbeddedSQL . 18 ............................................. 181 18.1 183 ................................. 19 Information Schema and Definition Schema 19.1 Information Schema. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183 19.1‘1 SCHEMATA view. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183 19.1‘2 DOMAINSview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .184 19.1‘3 COLUMNSview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .185 19‘1.4 MODULESview.187 19.1.5 ROUTINESview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .188 19.1.6 PARAMETERSview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .189 19.1‘7 MODULE TABLE USAGE view . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190 - - 19.1.8 MODULE COLUMN USAGE view . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191 - - 19.1‘9 MODULE PRIVILEGES view . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192 - 19.1.10 ROUTINE PRIVILEGES view. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193 - 19.1.11 ROUTINE TABLE USAGE view. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194 - - 19.1.12 ROUTINE COLUMN USAGE view. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195 - - 19.1.13 Definition of SQL built-in functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196 19.2 DefinitionSchema ,.201 19.2‘1 SCHEMATA base table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ,201 DATA TYPE DESCRIPTOR base table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ,202 19.2‘2 - - 19.2‘3 MODULES base table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ,206 19‘2.4 ROUTINES base table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ,208 19.2‘5 PARAMETERS base table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .211 19.2.6 MODULE TABLE USAGE base table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ,213 - - 19.2‘7 MODULE COLUMN USAGE base table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ,214 - - 19.2.8 MODULE PRIVILEGES base table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ,215 - 19.2.9 ROUTINE PRIVILEGES base table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ,217 - 19.2.10 ROUTINE TABLE USAGE base table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ,219 - - 19.2.11 ROUTINE COLUMN USAGE base table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ,220 - - 20 Statuscodes.221 20.1 SQLSTATE.221 ---------------------- Page: 5 ---------------------- @ ISOIIEC ISO/IEC 90754:1996(E) ..22 3 21 Conformance. ........................................................ ................................................... ,223 21.1 Claims of conformance ..22 3 21.2 Extensions andoptions ................................................. .......................................... .223 21.2.1 Information Schema requirements ......................................... .224 21.2.2 Schema manipulation requirements ................................................... ,224 Flagger requirements. 21.3 Implementation-defined elements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .225 Annex A Annex B Implementation-dependent elements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ,227 Annex C Deprecatedfeatures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .229 Annex D Incompatibilities with ISO/IEC 9075:1992 . . . . . . . . . . . . . . . . . . . . . . . . . . . ,231 Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .233 Vi ---------------------- Page: 6 ---------------------- ISOnEC 90754: 1996(E) MSOIIEC TABLES Table Page 1 Clause, Subclause, and Table relationships . 8 2 Standard programming languages . 68 3 Data type correspondences for Ada . 121 4 Data type correspondences for C . 122 5 Data type correspondences for COBOL . 123 6 Data type correspondences for Fortran . 124 7 Data type correspondences for MUMPS . 125 8 Data type correspondences for Pascal . 126 9 Data type correspondences for PL/I . 127 . 10 Data types of 11 Codes used for input/output SQL parameter modes in Dynamic SQL . 167 12 . 175 13 SQL-statement character codes for use in the diagnostics area . 176 14 SQLSTATE class and subclass values . ,221 vii ---------------------- Page: 7 ---------------------- ISO/IEC 9075=4:1996(E) @ ISOIIEC Foreword IS0 (the International Organization for Standardization) and IEC (the International Electrotechnical 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 technical activity. IS0 and IEC technical committees collaborate in fields of mutual interest. Other interna- tional organizations, governmental and non-governmental, in liaison with IS0 and IEC, also take part in the work. In the field of information technology, IS0 and IEC have established a joint technical committee, ISO/IEC JTC 1. Draft International Standards adopted by the joint technical committee are circu- lated to national bodies for voting. Publication as an International Standard requires approval by at least 75% of the national bodies casting a vote. International Standard ISO/IEC 9075-4, was prepared by Joint Technical Committee ISO/IEC JTC 1, Information technology, Subcommittee SC21, Open systems interconnection, data management, and open distributed processing. ISO/IEC 9075 consists of the following parts, under the general title Information technology - Database languages - SQL: - Part 3: Call-Level Interface (SQUCLI) - Part 4: Persistent Stored Modules (SQL/PSM) Parts 1, 2, and 5 are currently published as ISO/IEC 9075:1992. Part 3 is currently published as ISO/IEC 9075-3:1995. Annexes A, B, C, and D of this part of ISO/IEC 9075 are for information only. “‘ Vlll ---------------------- Page: 8 ---------------------- ISO/IEC 9075-4: 1.996(E) WSO/IEC Introduction The organization of this part of ISO/IEC 9075 is as follows: 1) Clause 1, “Scope”, specifies the scope of this part of ISO/IEC 9075. identifies additional standards that, through reference in this 2) Clause 2, “Normative references”, part of ISO/IEC 9075, constitute provisions of this part of ISO/IEC 9075. defines the notations and conventions used 3) Clause 3, “Definitions, notations, and conventions”, in this part of ISO/IEC 9075. 4) Clause 4, “Concepts”, presents concepts used in the definition of persistent stored modules. of lexical elements used in the definition of Clause 5, “Lexical elements”, defines a number 5) persistent stored modules. defines a number of scalar expressions used in the definition of Clause 6, “Scalar expressions”, 6) modules. persistent stored 7) Clause 7, “Query expressions”, defines the elements of the language that produce rows and tables of data as used in persistent stored modules. defines the data assignment rules 8) Clause 8, “Data assignment rules and routine determination”, used in the definition of persistent stored modules. defines additional common elements used in the 9) Clause 9, “Additional common elements”, definition of persistent stored modules. defines the schema definition and manipula- 10) Clause 10, “Schema definition and manipulation”, tion statements associated with the definition of persistent stored modules. 11) Clause 11, “Modules”, defines the facilities for using persistent stored modules. 12) Clause 12, “Data manipulation”, defines data manipulation operations associated with persistent stored modules. 13) Clause 13, “Control statements”, defines the control statements used with persistent stored modules. statements Clause 14, “Transaction management”, defines the SQL-transaction management 14) associated with persistent stored modules. Clause 15, “Session management”, defines the SQL-session management statements associated 15) with persistent stored modules. 16) Clause 16, “Dynamic SQL”, defines the facilities for executing SQL-statements dynamically in the context of persistent stored modules. facilities used with persis- Clause 17, “Diagnostics management”, defines enhancements to the 17) tent stored modules. ---------------------- Page: 9 ---------------------- ISO/IEC 9075-4: 1996(E) 0 ISO/IEC Clause 18, “Embedded SQL”, defines host language embeddings related to persistent stored 18) modules. defines the Information and Definition Clause 19, “Information Schema and Definition Schema”, 1% Schema objects associated with persistent stored modules. Clause 20, “Status codes”, defines SQLSTATE values related to persistent stored modules. 20) Clause 21, “Conformance”, defines the criteria for conformance to this part of ISO/IEC 9075. 21) is an informative Annex. It lists those features Annex A, “Implementation-defined elements”, 2% for which the body of this part of the standard states that the syntax or meaning or effect on the database is partly or wholly implementation-defined, and describes the defining information that an implementer shall provide in each case. Annex B, “Implementation-dependent elements”, is an informative Annex. It lists those features 23) for which the body of this part of the standard states that the syntax or meaning or effect on the database is partly or wholly implementation-dependent. \ Annex C, “Deprecated features”, is an informative Annex. It lists features that the responsible 24) Technical Committee intends will not appear in a future revised version of ISO/IEC 9075. Annex D, “Incompatibilities with ISO/IEC 9075:1992”, is an informative Annex. It lists the 25) incompatibilities between this edition of ISO/IEC 9075 and ISO/IEC 9075:1992. In the text of this part of ISO/IEC 9075, Clauses begin a new odd-numbered page, and in Clause 5, “Lexical elements”, through Clause 21, “Conformance”, Subclauses begin a new page. Any resulting blank space is not significant. X ---------------------- Page: 10 ---------------------- INTERNATIONAL STANDARD @ ISO/IEC ISO/IEC 90754: 1996(E) Information technology - Database languages - SQL - Part 4: Persistent Stored Modules (SQL/PSM) 1 Scope This part of International Standard ISO/IEC 9075 specifies the syntax and semantics of a database language for declaring and maintaining persistent database language routines either in SQL-server modules or as standalone schema-level routines, and invoking them from programs written in a standard programming language. The database language for s and s includes: - The specification of statements to direct the flow of control. - The assignment of the result of expressions to variables and parameters. - The specification of condition handlers that allow SQL-invoked routines to deal with various conditions that arise during their execution. - The specification of statements to signal and resignal conditions. - The ability to set an SQL-path for controlling the determination of the subject routine to be invoked. - The declaration of local cursors. - The declaration of local variables. It also includes the definition of the Information Schema tables that contain schema information pertaining to SQL-server modules and SQL-invoked routines. NOTE 1 - The context for ISO/IEC 9075 is described by the Reference Model of Data Management (ISWIEC 10032: 1993). Scope 1 ---------------------- Page: 11 ---------------------- OISOLCEC ISO/IEC 9075-4:1996 (E) 2 Persistent Stored Modules (SQLLPSM) ---------------------- Page: 12 ---------------------- OISO/IEC ISO/IEC 9075-4:1996 (E) 2 Normative references The following standards contain provisions that, through reference in this text, constitute provisions of this part of ISO/IEC 9075. At the time of publication, the editions indicated were valid. All standards are subject to revision, and parties to agreements based on this part of ISO/IEC 9075 are encouraged to investigate the possibility of applying the most recent editions of the standards indicated below. Members of IEC and IS0 maintain registers of currently valid International Standards. ISO/IEC 1539:1991, Information technology - Programming languages - FORTRAN. IS0 1989:1985, Programming languages - COBOL. IS0 6160:1979, Programming languages - PLI I. IS0 \ IEC 7185:1990, Information technology - Programming languages - Pascal. ISOIIEC 865211995, Information technology - Programming languages - Ada. NOTE 2 - IS0 86521987 has been superseded by a new edition (ISO/IEC $6521995). However, when this part of ISO/IEC 9075 was under development, the previous edition was valid and this part of ISO/IEC 9075 is therefore based on that edition, which is listed below. IS0 8652:1987, Programming languages - Ada. ISO/IEC 90751992, Information technology - Database languages - SQL. ISOAEC 9899:1990, Programming languages - C. ISOIIEC 10206: 1991, Information technology - Programming languages - Extended Pascal. ISOIIEC 11756:1992, Information technology - Programming languages - MUMPS. 3 Normative references ---------------------- Page: 13 ---------------------- ISO/IEC 9075-4:1996 (E) OISO/IEC 4 Persistent Stored Modules (SQL/PSM) ---------------------- Page: 14 ---------------------- OISO/IEC ISO/IEC 9075-4:1996 (E) 3 Definitions, notations, and conventions 3.1 Definitions [ Inset-t this paragraph 1 For the purposes of this part of ISO/IEC 9075, the definitions given in ISO/IEC 9075:1992 and the following definitions apply. external routine: An SQL-invoked routine whose routine body is an external body reference a> that identifies a program written in a standard programming language other than SQL. signature (of an SQL-invoked routine): The name of the SQL-invoked routine, the position b) and data types of each of its SQL parameters, and an indication of whether it is an SQL-invoked function or an SQL-invoked procedure. SQL routine: An SQL-invoked routine whose routine body is written in SQL. d SQL. SQL-invoked routine: A routine that is allowed to be invoked only from within d) 3.2 Notations 9075. 1 Insert this paragraph 1 All notations in ISO/IEC 9075:1992 apply to this part of ISO/IEC . 1 1 r 1 Inset-t this paragraph 1 The syntax notation used in this part of ISO/IEC 9075 is an extenaea version or BNF (“Backus Normal Form” or “Backus Naur Form”). [ Insert this paragraph 1 This version of BNF is fully described in ISO/IEC 9075:1992. 3.3 Conventions [ Insert this paragraph J Except as otherwise specified in this part of ISO/IEC 9075, the conventions used in this part of ISO/IEC 9075 are identical to those described in part 1 of ISO/IEC 9075. 3.3.1 Use of terms 3.3.1.1 Exceptions [ Modified paragraph ] The phrase “an exception condition is raised:“, followed by the name of a condi- ...