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

Status
Withdrawn
Publication Date
15-Dec-1999
Withdrawal Date
15-Dec-1999
Current Stage
9599 - Withdrawal of International Standard
Completion Date
15-Dec-2003
Ref Project

Relations

Buy Standard

Standard
ISO/IEC 9075-4:1999 - Information technology -- Database languages -- SQL
English language
152 pages
sale 15% off
Preview
sale 15% off
Preview

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.