ISO/IEC 9075-4:1999
(Main)Information technology — Database languages — SQL — Part 4: Persistent Stored Modules (SQL/PSM)
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 stockés persistants (SQL/PSM)
General Information
Relations
Standards Content (Sample)
INTERNATIONAL ISO/IEC
STANDARD 9075-4
Second edition
1999-12-01
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 stockés persistants (SQL/PSM)
Reference number
ISO/IEC 9075-4:1999(E)
©
ISO/IEC 1999
---------------------- Page: 1 ----------------------
ISO/IEC 9075-4:1999(E)
PDF disclaimer
This PDF file may contain embedded typefaces. In accordance with Adobe's licensing policy, this file may be printed or viewed but shall not
be edited unless the typefaces which are embedded are licensed to and installed on the computer performing the editing. In downloading this
file, parties accept therein the responsibility of not infringing Adobe's licensing policy. The ISO Central Secretariat accepts no liability in this
area.
Adobe is a trademark of Adobe Systems Incorporated.
Details 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 ISO member bodies. In the unlikely event
that a problem relating to it is found, please inform the Central Secretariat at the address given below.
© ISO/IEC 1999
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, without permission in writing from either ISO at the address below or ISO's member body
in the country of the requester.
ISO copyright office
Case postale 56 � CH-1211 Geneva 20
Tel. + 41 22 749 01 11
Fax + 41 22 734 10 79
E-mail copyright@iso.ch
Web www.iso.ch
Printed in Switzerland
i-2 © ISO/IEC 1999 – All rights reserved
---------------------- Page: 2 ----------------------
ISO/IEC 9075-4:1999(E)
© ISO/IEC 1999 – All rights reserved i-3
---------------------- Page: 3 ----------------------
ISO/IEC 9075-4:1999 (E)
Contents Page
Foreword . vii
Introduction . . . . ix
1 Scope . 1
2 Normative references. 3
3 Definitions, notations, and conventions . 5
3.1 Definitions . 5
3.1.1 Definitions provided in Part 4 . 5
3.2 Notations . 5
3.3 Conventions . 5
3.3.1 Use of terms. 5
3.3.1.1 Exceptions . . . 5
3.3.1.2 Other terms . . 6
3.3.2 Relationships to other parts of ISO/IEC 9075 . 6
3.3.2.1 Clause, Subclause, and Table relationships . . . 6
3.4 Object identifier for Database Language SQL . 12
4 Concepts. 13
4.1 SQL-server modules. 13
4.2 SQL-invoked routines . 14
4.3 SQL-paths . 14
4.4 Tables . . . 14
4.5 SQL-schemas . 14
4.6 Host parameters . . 15
4.6.1 Status parameters . 15
4.7 Diagnostics area . . . 15
4.8 Cursors . 15
4.9 Condition handling . 15
4.10 SQL-statements . . . 17
4.10.1 SQL-statements classified by function . . . 17
4.10.2 Embeddable SQL-statements . . 18
4.10.3 Directly executable SQL-statements . . . . 18
4.10.4 Iterated SQL-statements . 18
4.10.5 SQL-statements and transaction states . . 18
ii Persistent Stored Modules (SQL/PSM)
---------------------- Page: 4 ----------------------
©ISO/IEC ISO/IEC 9075-4:1999 (E)
4.10.6 Compound statements . 19
4.10.7 SQL-statement atomicity . 19
4.11 Basic security model . 19
4.11.1 Privileges . 19
5 Lexical elements . 21
5.1 and . 21
5.2 Names and identifiers . 23
6 Scalar expressions . 27
6.1 and . . . 27
6.2 . . 28
6.3 . . . . 30
7 Query expressions . 31
7.1 . 31
8 Additional common elements . 33
8.1 . 33
8.2 . 35
8.3 . . . 36
9 Schema definition and manipulation. 37
9.1 . 37
9.2 . . . . 38
9.3 . . . 39
9.4 . . . 40
9.5 . 41
9.6 . 43
9.7 . 44
9.8 . . 45
9.9 . 46
9.10 . . . . 47
9.11 . 48
9.12 . . . 49
9.13 . 50
9.14 . 51
9.15 . . . 52
9.16 . 53
9.17 . 54
9.18 . 55
9.19 . . . . 58
9.20 . . . 59
9.21 . 60
9.22 . . . . 62
9.23 . 63
Contents iii
---------------------- Page: 5 ----------------------
ISO/IEC 9075-4:1999 (E) ©ISO/IEC
10 Access control . 65
10.1 . 65
10.2 . 66
11 SQL-client modules . 69
11.1 Calls to an . 69
11.2 . . 70
12 Data manipulation . 73
12.1 . 73
12.2 . 74
12.3 . 75
12.4 . 76
12.5 . 77
12.6 . 78
12.7 . 79
13 Control statements . 81
13.1 . 81
13.2 . 85
13.3 . 88
13.4 . . . 89
13.5 . 90
13.6 . . 93
13.7 . . . . 95
13.8 . 97
13.9 . 98
13.10 . . 99
13.11 . 100
13.12 . 101
13.13 . . . 102
14 Dynamic SQL. 105
14.1 . 105
15 Embedded SQL . 107
15.1 . 107
16 Diagnostics management. 109
16.1 . . . 109
16.2 . 111
16.3 . 113
17 Information Schema. 115
17.1 MODULE_COLUMN_USAGE view . 115
17.2 MODULE_PRIVILEGES view . 116
17.3 MODULE_TABLE_USAGE view . 117
iv Persistent Stored Modules (SQL/PSM)
---------------------- Page: 6 ----------------------
©ISO/IEC ISO/IEC 9075-4:1999 (E)
17.4 MODULES view . . . 118
17.5 ROLE_MODULE_GRANTS view . 119
17.6 Short name views . . 120
18 Definition Schema . 121
18.1 MODULE_COLUMN_USAGE base table. 121
18.2 MODULE_PRIVILEGES base table. 123
18.3 MODULE_TABLE_USAGE base table . . . 125
18.4 MODULES base table . 126
19 Status codes. 129
19.1 SQLSTATE. 129
20 Conformance . 131
20.1 Claims of conformance . 131
Annex A SQL Conformance Summary . 133
Annex B Implementation-defined elements . 135
Annex C Implementation-dependent elements . 137
Annex D Deprecated features . 139
Annex E Incompatibilities with ISO/IEC 9075:1992 . 141
Annex F SQL Feature Taxonomy. 143
Index . 145
Contents v
---------------------- Page: 7 ----------------------
ISO/IEC 9075-4:1999 (E) ©ISO/IEC
TABLES
Tables Page
1 Clause, Subclause, and Table relationships . 6
2 s for use with . 109
3 SQL-statement codes for use in the diagnostics area . 110
4 SQLSTATE class and subclass values . . . 129
5 SQL/PSM feature taxonomy for features outside Core SQL . . . 143
vi Persistent Stored Modules (SQL/PSM)
---------------------- Page: 8 ----------------------
©ISO/IEC ISO/IEC 9075-4:1999 (E)
Foreword
ISO (the International Organization for Standardization) and IEC (the International Electrotechnical
Commission) form the specialized system for worldwide standardization. National bodies that are
members of ISO 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. ISO and IEC technical committees collaborate in fields of mutual interest. Other interna-
tional organizations, governmental and non-governmental, in liaison with ISO and IEC, also take
part in the work.
In the field of information technology, ISO 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 SC 32 , Data management and interchange.
This second edition of this part of ISO/IEC 9075 cancels and replaces the first edition, ISO/IEC
9075-4:1996.
ISO/IEC 9075 consists of the following parts, under the general title Information technology —
Database languages — SQL:
— Part 1: Framework (SQL/Framework)
— Part 2: Foundation (SQL/Foundation)
— Part 3: Call-Level Interface (SQL/CLI)
— Part 4: Persistent Stored Modules (SQL/PSM)
— Part 5: Host Language Bindings (SQL/Bindings)
Annexes A, B, C, D, E, and F of this part of ISO/IEC 9075 are for information only.
Foreword vii
---------------------- Page: 9 ----------------------
©ISO/IEC ISO/IEC 9075-4:1999 (E)
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.
2) Clause 2, ‘‘Normative references’’, identifies additional standards that, through reference in this
part of ISO/IEC 9075, constitute provisions of this part of ISO/IEC 9075.
3) Clause 3, ‘‘Definitions, notations, and conventions’’, defines the notations and conventions used
in this part of ISO/IEC 9075.
4) Clause 4, ‘‘Concepts’’, presents concepts used in the definition of persistent stored modules.
5) Clause 5, ‘‘Lexical elements’’, defines a number of lexical elements used in the definition of
persistent stored modules.
6) Clause 6, ‘‘Scalar expressions’’, defines a number of scalar expressions used in the definition of
persistent stored modules.
7) Clause 7, ‘‘Query expressions’’, defines the elements of the language that produce rows and
tables of data as used in persistent stored modules.
8) Clause 8, ‘‘Additional common elements’’, defines additional common elements used in the
definition of persistent stored modules.
9) Clause 9, ‘‘Schema definition and manipulation’’, defines the schema definition and manipulation
statements associated with the definition of persistent stored modules.
10) Clause 10, ‘‘Access control’’, defines facilities for controlling access to SQL-data.
11) Clause 11, ‘‘SQL-client 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.
14) Clause 14, ‘‘Dynamic SQL’’, defines the facilities for executing SQL-statements dynamically in
the context of persistent stored modules.
15) Clause 15, ‘‘Embedded SQL’’, defines the host language embeddings.
16) Clause 16, ‘‘Diagnostics management’’, defines enhancements to the facilities used with persis-
tent stored modules.
17) Clause 17, ‘‘Information Schema’’, defines the Information and Definition Schema objects associ-
ated with persistent stored modules.
18) Clause 18, ‘‘Definition Schema’’, defines base tables on which the viewed tables containing
schema information depend.
Introduction ix
---------------------- Page: 10 ----------------------
ISO/IEC 9075-4:1999 (E) ©ISO/IEC
19) Clause 19, ‘‘Status codes’’, defines SQLSTATE values related to persistent stored modules.
20) Clause 20, ‘‘Conformance’’, defines the criteria for conformance to this part of ISO/IEC 9075.
21) Annex A, ‘‘SQL Conformance Summary’’, is an informative Annex. It summarizes the confor-
mance requirements of the SQL language.
22) Annex B, ‘‘Implementation-defined elements’’, is an informative Annex. It lists those features
for which the body of this part of ISO/IEC 9075 states that the syntax, the meaning, the
returned results, the effect on SQL-data and/or schemas, or any other behavior is partly or
wholly implementation-defined.
23) Annex C, ‘‘Implementation-dependent elements’’, is an informative Annex. It lists those features
for which the body of this part of ISO/IEC 9075 states that the syntax, the meaning, the
returned results, the effect on SQL-data and/or schemas, or any other behavior is partly or
wholly implementation-dependent.
24) Annex D, ‘‘Deprecated features’’, is an informative Annex. It lists features that the responsible
Technical Committee intends will not appear in a future revised version of ISO/IEC 9075.
25) Annex E, ‘‘Incompatibilities with ISO/IEC 9075:1992’’, is an informative Annex. It lists the
incompatibilities between this edition of this part of ISO/IEC 9075 and ISO/IEC 9075-4:1996.
26) Annex F, ‘‘SQL Feature Taxonomy’’, is an informative Annex. It identifies features of the SQL
language specified in this part of ISO/IEC 9075 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.
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 20, ‘‘Conformance’’, Subclauses begin a new page. Any resulting
blank space is not significant.
x Persistent Stored Modules (SQL/PSM)
---------------------- Page: 11 ----------------------
INTERNATIONAL STANDARD ©ISO/IEC ISO/IEC 9075-4:1999 (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 in SQL-server
modules.
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 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 this part of ISO/IEC 9075 is described by the Reference Model of Data
Management (ISO/IEC 10032:1993).
Scope 1
---------------------- Page: 12 ----------------------
ISO/IEC 9075-4:1999 (E) ©ISO/IEC
2 Persistent Stored Modules (SQL/PSM)
---------------------- Page: 13 ----------------------
©ISO/IEC ISO/IEC 9075-4:1999 (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 ISO maintain registers of currently valid International
Standards.
ISO/IEC 8652:1995, Information technology — Programming languages — Ada.
ISO/IEC 9075-1, Information technology — Database languages — SQL — Part 1: Framework
(SQL/Framework).
ISO/IEC 9075-2, Information technology — Database languages — SQL — Part 2: Foundation
(SQL/Foundation).
ISO/IEC 9075-3:1996, Information technology — Database languages — SQL — Part 3: Call-
Level Interface (SQL/CLI).
ISO/IEC 9075-5, Information technology — Database languages — SQL — Part 5: Host
Language Bindings (SQL/Bindings).
Normative references 3
---------------------- Page: 14 ----------------------
ISO/IEC 9075-4:1999 (E) ©ISO/IEC
4 Persistent Stored Modules (SQL/PSM)
---------------------- Page: 15 ----------------------
©ISO/IEC ISO/IEC 9075-4:1999 (E)
3 Definitions, notations, and conventions
3.1 Definitions
3.1.1 Definitions provided in Part 4
Insert this paragraph For the purposes of this part of ISO/IEC 9075, the definitions given in ISO/IEC
9075-1 and ISO/IEC 9075-2 apply.
3.2 Notations
Insert this paragraph The syntax notation used in this part of ISO/IEC 9075 is an extended version
of BNF ("Backus Normal Form" or "Backus Naur Form"). This version of BNF is fully described in
Subclause 6.1, "Notation", of ISO/IEC 9075-1.
3.3 Conventions
Insert this paragraph
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 ISO/IEC 9075-1 and ISO/IEC
9075-2.
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-
tion, is used in General Rules and elsewhere to indicate that:
— The execution of a statement is unsuccessful.
— The application of General Rules, other than those of Subclause 10.4, "", in
ISO/IEC 9075-2, Subclause 11.2, ‘‘’’, Subclause 17.1, "
statement>", in ISO/IEC 9075-5, Subclause 13.1, ‘‘’’, and Subclause 13.2,
‘‘’’, may be terminated.
— Diagnostic information is to be made available.
— Execution of the statement is to have no effect on SQL-data or schemas.
Insert this paragraph
The phrase ‘‘C is re-raised by S’’ is used in General Rules and elsewhere to
indicate that C, a condition raised by an SQL-statement executed during execution of S, is raised
again by S.
Definitions, notations, and conventions 5
---------------------- Page: 16 ----------------------
ISO/IEC 9075-4:1999 (E) ©ISO/IEC
3.3 Conventions
3.3.1.2 Other terms
Insert this paragraph An SQL-statement S1 may be said to be executed as a direct result of executing
an SQL-statement if S1 is the SQL-statement contained in an or
that has been executed.
Insert this paragraph An SQL-statement S1 may be said to be executed as a direct result of executing
an S2 if S2 contains S1.
Insert this paragraph The phrase ‘‘The scope of a contained in a/an Y is that Y,
excluding every contained in that Y’’ means that the scope of the
declaration> does not extend to SQL-statements contained in such an ; it
does, however, extend to the itself.
3.3.2 Relationships to other parts of ISO/IEC 9075
3.3.2.1 Clause, Subclause, and Table relationships
Table 1—Clause, Subclause, and Table relationships
Corresponding Clause,
Clause, Subclause, or Table in Subclause, or Table from an- Part containing corre-
this part of ISO/IEC 9075 other part spondence
Clause 1, ‘‘Scope’’ Clause 1, "Scope" ISO/IEC 9075-2
Clause 2, ‘‘Normative references’’ Clause 2, "Normative references" ISO/IEC 9075-2
Clause 3, ‘‘Definitions, notations, Clause 3, "Definitions, notations, ISO/IEC 9075-2
and conventions’’ and conventions"
Subclause 3.1, ‘‘Definitions’’ Subclause 3.1, "Definitions" ISO/IEC 9075-2
Subclause 3.1.1, ‘‘Definitions pro- (none) (none)
vided in Part 4’’
Subclause 3.3, ‘‘Conventions’’ Subclause 3.3, "Conventions" ISO/IEC 9075-2
Subclause 3.3.1, ‘‘Use of terms’’ Subclause 3.3.1, "Use of terms" ISO/IEC 9075-2
Subclause 3.3.1.1, ‘‘Exceptions’’ Subclause 6.2.3.1, "Exceptions" ISO/IEC 9075-1
Subclause 3.3.1.2, ‘‘Other terms’’ Subclause 6.2.3.7, "Other terms" ISO/IEC 9075-1
Subclause 3.3.2, ‘‘Relationships to (none) (none)
other parts of ISO/IEC 9075’’
Subclause 3.3.2.1, ‘‘Clause, (none) (none)
Subclause, and Table relationships’’
Subclause 3.4, ‘‘Object identifier for Subclause 6.3, "Object identifier for ISO/IEC 9075-1
Database Language SQL’’ Database Language SQL"
Clause 4, ‘‘Concepts’’ Clause 4, "Concepts" ISO/IEC 9075-2
Subclause 4.1, ‘‘SQL-server mod- (none) (none)
ules’’
6 Persistent Stored Modules (SQL/PSM)
---------------------- Page: 17 ----------------------
©ISO/IEC ISO/IEC 9075-4:1999 (E)
3.3 Conventions
Table 1—Clause, Subclause, and Table relationships (Cont.)
Corresponding Clause,
Clause, Subclause, or Table in Subclause, or Table from an- Part containing corre-
this part of ISO/IEC 9075 other part spondence
Subclause 4.2, ‘‘SQL-invoked rou- Subclause 4.23, "SQL-invoked ISO/IEC 9075-2
tines’’ routines"
Subclause 4.3, ‘‘SQL-paths’’ Subclause 4.25, "SQL-paths" ISO/IEC 9075-2
Subclause 4.4, ‘‘Tables’’ Subclause 4.16, "Tables" ISO/IEC 9075-2
Subclause 4.5, ‘‘SQL-schemas’’ Subclause 4.20, "SQL-schemas" ISO/IEC 9075-2
Subclause 4.6, ‘‘Host parameters’’ Subclause 4.26, "Host parameters" ISO/IEC 9075-2
Subclause 4.6.1, ‘‘Status parame- Subclause 4.26.1, "Status parame- ISO/IEC 9075-2
ters’’ ters"
Subclause 4.7, ‘‘Diagnostics area’’ Subclause 4.27, "Diagnostics area" ISO/IEC 9075-2
Subclause 4.8, ‘‘Cursors’’ Subclause 4.29, "Cursors" ISO/IEC 9075-2
Subclause 4.9, ‘‘Condition handling’’ (none) (none)
Subclause 4.10, ‘‘SQL-statements’’ Subclause 4.30, "SQL-statements" ISO/IEC 9075-2
Subclause 4.10.1, ‘‘SQL-statements Subclause 4.30.2, "SQL-statements ISO/IEC 9075-2
classified by function’’ classified by function"
Subclause 4.10.2, ‘‘Embeddable Subclause 4.6.4, "Embeddable SQL- ISO/IEC 9075-5
SQL-statements’’ statements"
Subclause 4.10.3, ‘‘Directly exe- Subclause 4.6.6, "Directly exe- ISO/IEC 9075-5
cutable SQL-statements’’ cutable SQL-statements"
Subclause 4.10.4, ‘‘Iterated SQL- (none) (none)
statements’’
Subclause 4.10.5, ‘‘SQL-statements Subclause 4.30.3, "SQL-statements ISO/IEC 9075-2
and transaction states’’ and transaction states"
Subclause 4.10.6, ‘‘Compound state- (none) (none)
ments’’
Subclause 4.10.7, ‘‘SQL-statement (none) (none)
atomicity’’
Subclause 4.11, ‘‘Basic security Subclause 4.31, "Basic security ISO/IEC 9075-2
model’’ model"
Subclause 4.11.1, ‘‘Privileges’’ Subclause 4.31, "Basic security ISO/IEC 9075-2
model"
Clause 5, ‘‘Lexical elements’’ Clause 5, "Lexical elements" ISO/IEC 9075-2
Subclause 5.1, ‘‘ and and
rator>’’ rator>"
Subclause 5.2, ‘‘Names and identi- Subclause 5.4, "Names and identi- ISO/IEC 9075-2
fiers’’ fiers"
Clause 6, ‘‘Scalar expressions’’ Clause 6, "Scalar expressions" ISO/IEC 9075-2
Definitions, notations, and conventions 7
---------------------- Page: 18 ----------------------
ISO/IEC 9075-4:1999 (E) ©ISO/IEC
3.3 Conventions
Table 1—Clause, Subclause, and Table relationships (Cont.)
Corresponding Clause,
Clause, Subclause, or Table in Subclause, or Table from an- Part containing corre-
this part of ISO/IEC 9075 other part spondence
Subclause 6.1, ‘‘
tion> and ’’ tion> and "
Subclause 6.2, ‘‘’’ Subclause 6.5, "" ISO/IEC 9075-2
Subclause 6.3, ‘‘
reference>’’
Clause 7, ‘‘Query expressions’’ Clause 7, "Query expressions" ISO/IEC 9075-2
Subclause 7.1, ‘‘
tion>’’ tion>"
Clause 8, ‘‘Additional common Clause 10, "Additional common ISO/IEC 9075-2
elements’’ elements"
Subclause 8.1, ‘‘
...
Questions, Comments and Discussion
Ask us and Technical Secretary will try to provide an answer. You can facilitate discussion about the standard in here.