From
[email protected] Tue Aug 16 19:47:34 1994
Return-Path: <
[email protected]>
Received: from ECF.NCSL.NIST.GOV by speckle.ncsl.nist.gov (4.1/SMI-3.2-del/cas.6)
id AA10732; Tue, 16 Aug 94 19:46:47 EDT
Message-Id: <
[email protected]>
Date: Tue, 16 Aug 94 16:20:48 PDT
From: Life is just a bowl of queries. 16-Aug-1994 1720 -0600 <
[email protected]>
To:
[email protected]
Apparently-To:
[email protected]
Subject: SC21 N8897 (SQL/PSM CD plain text version)
Status: R
ISO/IEC JTC1/SC21 N8897
ISO Commitee Draft CD 9075-4:199x
Persistent Stored Modules
August 1994
CD 9075-4:199x SQL Part 4: SQL/PSM-August 16, 1994
Digital Equipment Corporation
Maynard, Massachusetts
Contents Page
Foreword.........................................................vii
Introduction.....................................................ix
1 Scope ........................................................ 1
2 Normative references ......................................... 3
3 Definitions, notations, and conventions ...................... 5
3.1 Definitions ................................................ 5
3.2 Notations .................................................. 5
3.3 Conventions ................................................ 5
3.4 Object identifier for Database Language SQL ................ 5
4 Concepts ..................................................... 7
4.1 Introduction ............................................... 7
4.1.1Modules and <routine>s ..................................... 7
4.1.1<routine>s ................................................. 7
4.1.1Handler procedures ......................................... 7
4.2 Tables ..................................................... 9
4.3 Modules .................................................... 9
4.4 Routines ................................................... 9
4.5 Parameters .................................................11
4.5.1Status parameters ..........................................11
4.6 Diagnostics area ...........................................11
4.7 Cursors ....................................................11
4.8 SQL-statements .............................................11
4.8.1SQL-statements classified by function ......................11
4.8.2SQL-statements and transaction states ......................12
4.8.3SQL-statement atomicity ....................................12
5 Lexical elements .............................................13
5.1 <token> and <separator> ....................................13
5.2 Names and identifiers ......................................15
6 Scalar expressions ...........................................17
6.1 <item reference> ...........................................17
6.2 <value specification> and <target specification> ...........21
6.3 <datetime value function> ..................................22
7 Query expressions ............................................23
7.1 <query specification> ......................................23
7.2 <query expression> .........................................25
ii CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
8 Data assignment rules and function determination .............27
8.1 Subject routine determination ..............................27
8.2 Type precedence list determination .........................28
9 Additional common elements ...................................29
9.1 <routine invocation> .......................................29
9.2 <privileges> ...............................................38
9.3 <specific routine designator> ..............................39
10 Schema definition and manipulation ...........................41
10.1 <schema definition> ........................................41
10.2 <drop schema statement> ....................................42
10.3 <default clause> ...........................................43
10.4 <drop routine statement> ...................................44
11 Persistent stored modules ....................................47
11.1 Introduction ...............................................47
11.2 <module> ...................................................48
11.3 <routine> ..................................................50
11.4 <SQL procedure statement> ..................................66
11.5 Data type correspondences ..................................68
12 Persistent SQL module maintenance ............................75
12.1 Introduction ...............................................75
12.2 <SQL-server module definition> .............................75
12.3 <alter module statement> ...................................77
12.4 <drop module statement> ....................................79
13 Data manipulation ............................................81
13.1 <declare cursor> ...........................................81
13.2 <temporary table declaration> ..............................82
14 Control statements ...........................................83
14.1 Introduction ...............................................83
14.2 <call statement> ...........................................83
14.3 <return statement> .........................................84
14.4 <compound statement> .......................................85
14.5 <handler declaration> ......................................88
14.6 <exception declaration> ....................................90
14.7 <SQL variable declaration> .................................91
14.8 <assignment statement> .....................................92
14.9 <case statement> ...........................................94
14.10<if statement> .............................................96
14.11<leave statement> ..........................................98
14.12<loop statement> ...........................................99
14.13<for statement> ............................................100
14.14<signal statement> .........................................103
Table of Contents iii
ISO/IEC JTC1/SC21 N8897
14.15<resignal statement> .......................................105
15 Diagnostics management .......................................107
15.1 <get diagnostics statement> ................................107
16 Information Schema and Definition Schema .....................109
16.1 Information Schema .........................................109
16.1.DOMAINS view ...............................................109
16.1.COLUMNS view ...............................................111
16.1.MODULES view ...............................................114
16.1.ROUTINES view ..............................................115
16.1.PARAMETERS view ............................................117
16.2 Definition Schema ..........................................119
16.2.DATA_TYPE_DESCRIPTOR base table ............................119
16.2.MODULES base table .........................................125
16.2.ROUTINES base table ........................................126
16.2.PARAMETERS base table ......................................129
16.2.MODULE_PRIVILEGES base table ...............................131
16.2.ROUTINE_PRIVILEGES base table ..............................133
17 Status codes .................................................135
17.1 SQLSTATE ...................................................135
18 Conformance ..................................................137
18.1 Claims of conformance ......................................137
18.2 Processing methods .........................................137
18.3 Extensions and options .....................................137
18.4 Flagger requirements .......................................138
Annex A Implementation-defined elements........................139
Annex B Implementation-dependent elements......................141
Annex C Deprecated features....................................143
Annex D Incompatibilities with X3.135-1992 and ISO/IEC
9075:1992..............................................145
Index
iv CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
TABLES
Table Page
1 Data type correspondences for Ada ..........................68
2 Data type correspondences for C ............................69
3 Data type correspondences for COBOL ........................70
4 Data type correspondences for Fortran ......................71
5 Data type correspondences for MUMPS ........................72
6 Data type correspondences for Pascal .......................73
7 Data type correspondences for PL/I .........................74
8 SQL-statement character codes for use in the diagnostics
area........................................................107
9 SQLSTATE class and subclass values .........................135
Table of Contents v
ISO/IEC JTC1/SC21 N8897
Foreword
____________________________________________________________________
A Note From the Editor
It is the intent of ISO/IEC JTC1/SC21/WG3 that This document
be progressed as a new part of ISO/IEC 9075:1992, Information
technology - Database languages - SQL. A future version of the
document will apply to the planned replacement for that standard
(ISO Project 1.21.3.4).
The present state of this document may inadvertently contain
dependencies on the document currently in development under Project
1.21.3.4, but it is the intent of the responsible development group
that those dependencies be removed as this document is progressed.
It is known that additional work is required to address issues
such as subject routine determination, routine invocation
and declaration, the <drop routine statement>, data type
_correspondences,_and_probably_others.______________________________
| |
|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 international 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 circulated 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, Part 4, was prepared by Joint
Technical Committee ISO/IEC JTC 1, Information technology.
ISO/IEC 9075 consists of the following parts, under the general
title Information technology - Database languages - SQL:
- Part 1: Foundation and General Purpose Facilities
- Part 3: Call Level Interface (SQL/CLI)
- Part 4: Persistent SQL Modules (SQL/PSM)
Annexes A, B, C, and D of this Part of this International Standard
are for information only.
Foreword vii
ISO/IEC JTC1/SC21 N8897
Introduction
The organization of this International Standard is as follows:
1) Clause 1, "Scope", specifies the scope of this part of this
International Standard.
2) Clause 2, "Normative references", identifies additional
standards that, through reference in this part of this
International Standard, constitute provisions of this part of
this International Standard.
3) Clause 3, "Definitions, notations, and conventions", defines
the notations and conventions used in this part of this
International Standard.
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 query expressions
used in the definition of persistent stored modules.
8) Clause 8, "Data assignment rules and function determination",
defines the data assignment rules used in the definition of
persistent stored modules.
9) Clause 9, "Additional common elements", defines additional
common elements used in the definition of persistent stored
modules.
10)Clause 10, "Schema definition and manipulation", defines the
schema definition and manipulation statements associated with
the definition of persistent stored modules.
11)Clause 11, "Persistent stored modules", defines the facilities
for using persistent stored modules.
12)Clause 12, "Persistent SQL module maintenance", defines
facilities for managing persistent stored modules.
13)Clause 13, "Data manipulation", defines data manipulation
operations associated with persistent stored modules.
14)Clause 14, "Control statements", defines the control statements
used with persistent stored modules.
Introduction ix
ISO/IEC JTC1/SC21 N8897
15)Clause 15, "Diagnostics management", defines enhancements to the
facilities used with persistent stored modules.
16)Clause 16, "Information Schema and Definition Schema", defines
the Information and Definition Schema objects associated with
persistent stored modules.
17)Clause 17, "Status codes", defines SQLSTATE values related to
persistent stored modules.
18)Clause 18, "Conformance", defines the criteria for conformance
to this Part of this International Standard.
19)Annex A, "Implementation-defined elements", is an informative
Annex. It lists those features 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 implementor shall
provide in each case.
20)Annex B, "Implementation-dependent elements", is an informative
Annex. It lists those features 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.
21)Annex C, "Deprecated features", is an informative Annex.
It lists features that the responsible Technical Committee
intends will not appear in a future revised version of this
International Standard.
22)Annex D, "Incompatibilities with X3.135-1992 and ISO
/IEC 9075:1992", is an informative Annex. It lists the
incompatibilities between this version of this International
Standard and ISO/IEC 9075:1992.
In the text of this International Standard, Clauses begin a new
odd-numbered page, and in Clause 12, "Persistent SQL module
maintenance", through Clause 18, "Conformance", Subclauses begin
a new page. Any resulting blank space is not significant.
x CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
Information Technology - Database Languages - SQL
Part 4 - SQL Persistent Stored Modules (SQL/PSM)
1 Scope
This part of International Standard ISO/IEC 9075 specifies the
syntax, semantics of a database language for declaring and
maintaining persistent database language procedures and invoking
them from programs written in a standard programming langauge.
The database language for procedures includes:
- the specification of statements to direct the flow of control
within a procedure;
- the declaration of local cursors;
- the declaration of local variables;
- the declaration of local temporary tables;
It also includes the definition of tables in the Information Schema
which describes the structure and content of the persistent SQL
modules.
Note: The framework for this International Standard is described by
the Reference Model of Data Management (ISO/IEC 10032:1993).
<To be supplied>
Scope 1
ISO/IEC JTC1/SC21 N8897
2 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
2 Normative references
The following standards contain provisions that, through reference
in this text, constitute provisions of this Part of this National
Standard. At the time of publication, the editions indicated
were valid. All standards are subject to revision, and parties
to agreements based on this National Standard are encouraged to
investigate the possibility of applying the most recent editions
of the standards listed below. Members of IEC and ISO maintain
registers of currently valid International Standards.
- ISO/IEC 1539:1991, Information technology - Programming
languages - Fortran.
- ISO 1989:1985, Programming languages - COBOL.
- ISO 6160:1979, Programming languages - PL/I.
- ISO 7185:1990, Information technology - Programming languages -
Pascal.
- ISO 8652:1987, Programming languages - Ada.
- ISO/IEC 9075:1992, Information Technology - Database Languages -
SQL.
- ISO/IEC 9899:1990, Information technology - Programming
languages - C.
- ISO/IEC 10206:1991, Information technology - Programming
languages - Extended Pascal.
- ISO/IEC 11756:1992, Information technology-Programming
languages-MUMPS.
Normative references 3
ISO/IEC JTC1/SC21 N8897
4 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
3 Definitions, notations, and conventions
3.1 Definitions
For the purposes of this International Standard, the following
definitions apply.
All definitions in ISO/IEC 9075:1992 apply to this Part.
This Part of this International Standard defines the following
terms:
<To be supplied>
<More to be supplied as required.>
3.2 Notations
All notations in ISO/IEC 9075:1992 apply to this Part.
The syntax notation used in this part of this International
Standard is an extended version of BNF ("Backus Normal Form" or
"Backus Naur Form").
This version of BNF is fully described in ISO/IEC 9075:1992.
<More to be supplied as required.>
3.3 Conventions
The conventions used in this part of this International Standard
are identical to those described in ISO/IEC 9075-1.
<More to be supplied as required.>
3.4 Object identifier for Database Language SQL
Note: It is possible that the object identifier for SQL may have to
be adjusted to account for the new structure of SQL3.
Definitions, notations, and conventions 5
ISO/IEC JTC1/SC21 N8897
6 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
4 Concepts
4.1 Introduction
4.1.1 Modules and <routine>s
4.1.1.1 <routine>s
A <routine> (Subclause 4.4, "Routines") may be contained either
in a schema or in a <module>. It may be a function, which
returns a value, or a procedure, which does not return a value.
A <routine> consists of a <routine name>, a sequence of <parameter
declaration>s, and a <routine body>.
A <routine> is either an SQL-invoked <routine> or an externally-
invoked <routine>, depending on its <caller language clause>.
Additionally, it is either an SQL <routine> or an external
<routine>, depending on whether its <routine body> is an SQL-
statement or is an <external body reference>.
A parameter (Subclause 4.18, "Parameters", in ISO/IEC 9075:1992)
is an SQL-supplied parameter if it is declared in an SQL-invoked
<routine>, otherwise it is an externally-supplied parameter.
SQL-supplied parameters can be null (see below), but externally-
supplied parameters cannot be null except through the use of
additional parameters specially designated as indicator parameters.
Other special parameters are the status parameters SQLSTATE and
SQLCODE.
An SQL-statement in an SQL <routine> can be a <compound statement>.
The SQL-statements include assignment statements and flow of
control statements; a <compound statement> may include a <local
declaration list>.
4.1.1.2 Handler procedures
A special routine known as a handler procedure is used in the
handling of exception and completion conditions. A handler
procedure may be one of the following types: redo, undo, or
continue. It can contain SQL-statements in the body of the
procedure. The handler procedure mechanism allows one to either
terminate or resume after an exception or completion condition
occurs.
An undo or redo handler procedure can only be invoked from atomic
compound statements. A continue handler procedure can be invoked
from atomic or non-atomic compound statements and routines.
Concepts 7
ISO/IEC JTC1/SC21 N8897
4.1 Introduction
The last action, prior to exiting, of any handler procedure is
to deactive the active exception or completion condition that was
raised and caused the handler procedure to be invoked.
An undo handler procedure will rollback all of the changes
performed by the compound statement from which it was invoked. It
will then execute the SQL-statement list contained in the body fo
the handler procedure. Next, the diagnostics area will be cleared
of the condition that caused the handler procedure to be invoked.
Control is then returned to the end of the compound statement from
which it was invoked.
A redo handler procedure will rollback all of the changes performed
by the compound statement from which it was invoked. It will
then execute the SQL-statement list contained in the body fo the
handler procedure. Next, the diagnostics stack will be cleared
of the condition that caused the handler procedure to be invoked.
Control is then returned to the beginning of the compound statement
from which it was invoked, causing the compound statement (and
consequently the statement causing the condition to be raised) to
be_re-executed._____________________________________________________
**Editor's Note**
The Editor notes that the notion of "diagnostics stack" is defined
nowhere in SQL. This appears to be an invention based on somebody's
particular implementation technology. Can we either get around this
_phrase_or_define_it,_please?_______________________________________
| |
|A continue handler procedure will execute the SQL-statement |
|list contained in the body fo the handler procedure. Next, the |
|diagnostics stack will be cleared of the condition that caused |
|the handler procedure to be invoked. Control is then returned |
to the SQL-statement following the one that raised the condition
that caused the handler procedure to be invoked in the compound
statement.__________________________________________________________
**Editor's Note**
The Editor notes that the notion of "diagnostics stack" is defined
nowhere in SQL. This appears to be an invention based on somebody's
particular implementation technology. Can we either get around this
_phrase_or_define_it,_please?_______________________________________
| |
|A handler procedure either completes with no active exception or |
|with an active exception. If it completes with an active exception,|
|then an implicit <resignal statement> is executed. The effect of |
|the <resignal statement> is to determine whether there is another |
handler procedure in the current module or chain of active modules
that can resolve the active exception. If there are none, then
control is returned to the SQL-client with this active exception.
This type of exception is called an unhandled exception.
A handler procedure can be declared in either a module or in a
compound statement. If it is specified in a compound statement,
then its scope is that compound statement only. If either a handler
procedure or a handler declaration is contained in a module without
8 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
4.1 Introduction
an intervening compound statement, then its scope is the entire
module.
A handler declaration is used to associate an exception condition
with a handler procedure. A handler declaration can contain
parameters. The name of the handler declaration is the same as
the name of the handler procedure that it references.
A handler procedure can have one or more exception conditions
associated with it.
4.2 Tables
A compound statement local temporary table is a named table defined
by a <temporary table declaration> in a <compound statement>.
A compound statement local temporary table is effectively
materialized the first time it is referenced during an execution
of the <compound statement>, and it persists for that execution.
4.3 Modules
A <module> may also be an SQL-server <module>.
SQL-server <module>s are created by <SQL-server module definition>
and <schema definition> (for schemas that contain <module>s). SQL-
server <module>s are destroyed by the <drop module statement>
and <drop schema statement>. An <SQL-server module definition>
is a <schema element> and has a <module name> that is a <schema
qualified name>. The <routine>s of an SQL-server <module> are
invoked by SQL <call statment>s or <routine invocation>s. The
<language clause> of an SQL-server <module> specifies LANGUAGE
SQL.
A module descriptor also contains the text of the <SQL-server
module definition>.
4.4 Routines
A <routine> is a subprogram. A <routine> consists of a <routine
name>, a sequence of <parameter declarations>s, and a <routine
body>.
A <routine> is either a component of a <module> or an object in a
schema.
A <routine> is either a function, which returns a value, or a
procedure, which does not return a value.
A <routine> is either an SQL-invoked <routine> or an externally-
invoked <routine>, depending on whether the <caller language
clause> of the <routine> specifies "SQL" or some standard
programming language. An SQL-invoked <routine> (function or
procedure) is invoked from within SQL. An externally-invoked
Concepts 9
ISO/IEC JTC1/SC21 N8897
4.4 Routines
<routine> (function or procedure) is invoked by "call" statements
in compilation units of the specified standard programming
language.
A <routine> is either an SQL <routine> or an external <routine>.
An SQL <routine> is a <routine> whose <routine body> is an <SQL
procedure statement>. An external <routine> is a <routine> whose
<routine body> is an <external body reference> that identifies a
routine body provided externally as a program written in a standard
programming language. An SQL-invoked <routine> may be either an SQL
<routine> or an external <routine>. An externally-invoked <routine>
must be an SQL <routine>.
An invocation of a <routine> specifies the <routine name> of the
<routine> and supplies a sequence of argument values corresponding
to the <parameter declaration>s of the <routine>. Such invocation
causes the <routine body> to be executed.
Different <routine>s can have the same <routine name>. This is
referred to an overloading the <routine name>. Overloading is
allowed only for <routine>s that are called from SQL. The parameter
declaration lists of such routines must be sufficiently different
to distinguish which of the routines is to be invoked for a given
<routine invocation>.
If a <routine invocation> is contained in a <query expression>
of a view, a check constraint, or an assertion, then the subject
routines for that invocation are determined at the time the view
is created, the check constraint is created or added to a table,
or the assertion is created. Thus, the same routines are invoked
whenever the view is used or the check constraint or assertion is
evaluated.
A routine is described by a routine descriptor. A routine
descriptor contains:
- The <routine name>;
- If the routine is an SQL-invoked routine, then the specific name
of the routine;
- For each of the routine's parameters, the name, data type,
position, the value of the <default option>, if any, and an
indication of whether the parameter is IN, OUT, or INOUT.
- If the routine is a function, then the data type returned by the
function; and
- The name of the caller language of the routine;
- If the routine is an external routine, then the external name of
the routine;
- If the routine is an external routine, then the parameter
passing style that will be used;
10 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
4.4 Routines
- If the routine is an external routine, then the name of the
language in which the body of the routine is written;
- If the routine is an external routine, then an indication of
whether the routine is a variant routine or not;
4.5 Parameters
4.5.1 Status parameters
If the <SQL procedure statement> is a <compound statement>,
then the active condition is dependent on the exception handler
specified in the <compound statement>.
4.6 Diagnostics area
The diagnostics area is emptied during the execution of a <signal
statement>. Information is added to the diagnostics area during the
execution of a <resignal statement>.
4.7 Cursors
For every <declare cursor> in a <compound statement>, a cursor is
effectively created each time the <compound statement> is executed,
and destroyed when that execution completes.
4.8 SQL-statements
4.8.1 SQL-statements classified by function
The following is another major class of SQL-statements:
- SQL-control statements
The following are also SQL-schema statements:
- <SQL-server module definition>
- <drop module statement>
- <routine>
- <drop routine statement>
The following are the SQL-control statements:
- <call statement>
Concepts 11
ISO/IEC JTC1/SC21 N8897
4.8 SQL-statements
- <return statement>
2 list entries deleted
- <compound statement>
- <case statement>
- <if statement>
- <leave statement>
- <loop statement>
- <for statement>
- <assignment statement>
The following are also SQL-diagnostics statements:
- <signal statement>
- <resignal statement>
4.8.2 SQL-statements and transaction states
The <return statement> is an additional transaction-initiating
SQL-statement.
4.8.3 SQL-statement atomicity
The execution of all SQL-statements other than SQL-control
statements is atomic. The execution of <compound statement>s that
specify ATOMIC is atomic.
If an <SQL control statement> causes the evaluation of a <subquery>
and there is no current SQL-transaction, then an SQL-transaction is
initiated before evaluation of the <subquery>.
12 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
5 Lexical elements
5.1 <token> and <separator>
Function
Specify lexical units (tokens and separators) that participate in
SQL language.
Format
<key word> ::=
<reserved word>
| <non-reserved word>
<non-reserved word> ::=
ATOMIC
<reserved word> ::=
CALL
| DO
| ELSEIF | EXCEPTION
| HANDLER
| IF
| LEAVE | LOOP
| OTHERS
| REDO | RESIGNAL | RETURN | RETURNS
| SIGNAL | SQLEXEPTION | SQLWARNING
| TUPLE
| UNDO
| WHILE
Lexical elements 13
ISO/IEC JTC1/SC21 N8897
5.1 <token> and <separator>
Syntax Rules
No additional Syntax Rules.
Access Rules
No additional Access Rules.
General Rules
No additional General Rules.
14 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
5.2 Names and identifiers
5.2 Names and identifiers
Function
Specify names.
Format
<local or schema qualifier> ::=
!! All alternatives from ISO/IEC 9075:1992
| <statement label>
<local qualifier> ::=
!! All alternatives from ISO/IEC 9075:1992
| <statement label>
<SQL variable name> ::= <identifier>
<exception name> ::= <identifier>
<routine name> ::= <local or schema qualified name>
<specific name> ::= <schema qualified name>
Syntax Rules
1) If a <table name> TN with a <qualified identifier> QI does not
contain a <local or schema qualifier>, then if TN is simply
contained in a <compound statement> CS whose <local declaration
list> contains a <temporary table declaration> or <temporary
view declaration> TT whose declared local name is equal to
QI, then the <statement label> of CS is the implicit <local
or schema qualifier> of TN.
2) If a <table name> TN with a <qualified identifier> QI contains
a <local or schema qualifier> LSQ, then if TN is contained in
a <compound statement> CS whose <statement label> is equal to
LSQ, then the <local declaration list> of CS shall contain a
<temporary table declaration> or <temporary view declaration> TT
whose declared local name is equal to QI.
3) If a <cursor name> CN with a <qualified identifier> QI does
not contain a <local qualifier>, then if CN is simply contained
in a <compound statement> CS whose <local declaration list>
contains a <declare cursor> whose declared local name is equal
to QI, then the <statement label> of CS is the implicit <local
qualifier> of CN.
Lexical elements 15
ISO/IEC JTC1/SC21 N8897
5.2 Names and identifiers
4) If a <cursor name> CN with a <qualified identifier> QI contains
a <local qualifier> LQ, then if CN is contained in a <compound
statement> CS whose <statement label> is equal to LQ, then the
<local declaration list> of CD shall contain a <declare cursor>
whose declared local name is equal to QI.
5) If an <abstract data type name> ADTN with a <qualified
identifier> QI does not contain a <local or schema qualifier>,
then if ADTN is simply contained in a <compound statement> CS
whose <local declaration list> contains a <temporary abstract
data type declaration> whose declared local name is equal to QI,
or a <new type name clause> in a <temporary table declaration>
whose <qualified identifier> is equal to QI, then the <statement
label> of CS is the implicit <local or schema qualifier> of
ADTN.
6) If an <abstract data type name> ADTN with a <qualified
identifier> QI contains a <local or schema qualifier> LSQ,
then if ADTN is simply contained in a <compound statement>
CS whose <statement label> is equal to LSQ, then the <local
declaration list> of CS shall contain a <temporary abstract data
type declaration> whose declared local name is equal to QI, or a
<new type name clause> in a <temporary table declaration> whose
<qualified identifier> is equal to QI.
Access Rules
No additional Access Rules.
General Rules
1) An <SQL variable name> identifies an SQL variable.
2) An <exception name> identifies an exception and corresponding
<exception value>.
3) If more than one <exception name> uses the same <exception
value>, then the <exception name>s shall be considered
equivalent.
4) A <routine name> identifies a <routine>.
16 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
6 Scalar expressions
6.1 <item reference>
Function
Reference a parameter or an SQL variable.
Format
<item reference> ::=
!! All alternatives from ISO/IEC 9075:1992
| <SQL variable name>
<item qualifier> ::=
!! All alternatives from ISO/IEC 9075:1992
| <statement label>
Syntax Rules
1) Let IR be an <item reference> and let IN be the <item name>
contained in IR.
2) If IN is a <parameter name> that simply contains a <colon>, then
IR shall not contain an <item qualifier>.
3) If IR contains an <item qualifier> IQ, then IR shall appear
within the scope of one or more exposed <table name>s,
<correlation name>s, <routine name>s, or <statement label> that
are equal to IQ. If there is more than one such exposed <table
name>, <correlation name>, <routine name>, or <statement label>,
then the one with the most local scope is specified. Let V be
the table or parameter list associated with IQ.
a) V shall include a column, parameter, or variable whose
<column name>, <parameter name>, or <variable name> is IN.
b) If V is a <table reference> in a <joined table> JT, then IN
shall not be a common column name in JT.
Note: "Common column name" is defined in Subclause 7.5,
"<joined table>", in ISO/IEC 9075:1992.
4) If IR does not contain an <item qualifier>, then IR shall
be contained within the scope of one or more exposed <table
name>s, <correlation name>s, <routine>s or <statement label>s
whose associated tables, <parameter list>s, or <variable
declaration list>s include a column, parameter, or variable
whose <identifier> is IN. Let the phrase possible qualifiers
Scalar expressions 17
ISO/IEC JTC1/SC21 N8897
6.1 <item reference>
denote those exposed <table name>s, <correlation name>s,
<routine name>s, ans <statement label>s.
a) Case:
i) If the most local scope contains exactly one possible
qualifier, then the qualifier IQ equivalent to that unique
exposed <table name>, <correlation name>, <routine name>,
or <statement label> is impliict.
ii) If there is more than one possible qualifier with the most
local scope, then:
1) Each possible qualifier shall be a <table name> or
a <correlation name> of a <table reference> that is
direction contained in a <joined table> JT.
2) CN shall be a common column name in JT.
Note: "Common column name" is defined in Subclause 7.5,
"<joined table>", in ISO/IEC 9075:1992.
3) The implicit qualifier IQ is implementation-dependent.
The scope of IQ is that which IQ would have had if JT
had been replaced by the <table reference>:
(JT) AS IQ
b) Let V be the table, parameter list, or local declaration list
associated with IQ.
5) Case:
a) If V is a <table name> or <correlation name>, then IR is a
column reference. IN shall uniquely identify a column of V.
Let R be that column.
i) R is an underlying column of IR. If R is a <derived
column>, then every underlying column of R is an underlying
column of IR.
Note: The underlying columns of a <derived column> are
defined in Subclause 7.9, "<query specification>", in ISO
/IEC 9075:1992.
ii) If IR is contained in a <table expression> TE or a <there
is predicate> TE or a <quantified predicate>TE immediately
containing an <existential clause> or <universal clause>
and the scope of IQ is some <SQL procedure statement>,
<recursive union>, <trigger definition>, <quantified
predicate>, or <table reference> that contains TE, then
CR is an outer reference to the table associated with Q.
b) If V is a <routine name>, then IR is a parameter reference.
Let R be the parameter of V identified by IN. If R is an
externally-supplied parameter, then IR is a host parameter
18 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
6.1 <item reference>
reference. If R is an SQL-supplied parameter, then IR is an
SQL parameter reference.
c) If V is a <statement label>, then IR is an SQL variable
reference. Let R be the variable of V identified by IN.
6) An <item name> that is a <parameter name> shall simply contain a
<colon> if and only if the <item reference is a host parameter
reference.
7) An <indicator parameter> shall be specified only if the <item
reference> is a host parameter reference. The <parameter name>
of the <indicator parameter> shall identify a parameter of V.
The data type of that parameter shall be exact numeric with a
scale of 0.
8) If the data type of R is character string, then R has the
Implicit coercibility attribute and its collating sequence is
the default collating sequence for the column, parameter, or
variable R.
9) If the data type fo R is TIME or TIMESTAMP, then the implicit
time zone of the data is the current default time zone for the
SQL-session.
10)
11)If the data type fo R is TIME WITH TIME ZONE or TIMESTAMP WITH
TIME ZONE, then the time zone of the data is the time zone
represented in the value of IR.
12)Case:
a) If IR is contained in a <declare cursor>, then let OS be the
<open statement>s in the containing <module> that specify
the <cursor name> of the <declare cursor>. For each <open
statement> O in OS:
Case:
i) If O is contained in one or more <compound statement>s
that contain an <SQL variable declaration> whose <SQL
variable name> is IR, then IR denotes the SQL variable
in the innermost such <compound statement>.
ii) Otherwise, O shall be contained in a <routine> that
contains a <parameter declaration> whose <parameter name>
is IR, and IR denotes that parameter.
b) Otherwise, IR shall be contained in one or more <compound
statement>s or <routine>s that contain an <SQL variable
declaration> or <parameter declaration> whose <parameter
name> or <SQL variable name> is IR. IR denotes that parameter
or SQL variable in the innermost such <routine> or <compound
statement>.
Scalar expressions 19
ISO/IEC JTC1/SC21 N8897
6.1 <item reference>
____________________________________________________________________
**Editor's Note**
The preceding Rule representes the Editor's best effort to merge
the effects of papers X3H2-93-096/MUN-069R and X3H2-93-140/YOK-
101/MUN-067. It seems quite likely that additional work will
be required to rationalize the Editor's Notes of Part 2 (SQL
/Foundation)._______________________________________________________
| |
Ac|ess Rules |
| |
|No additional Access Rules. |
| |
General Rules
1) Depending on whether IR is a column reference, parameter
reference, or SQL variable reference, IQ.IN references column
IN in a given row of V, parameter IN of a given call of V, or
variable IN of a given execution of V.
1 Subclause deleted
20 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
6.2 <value specification> and <target specification>
6.2 <value specification> and <target specification>
Function
Specify one or more values, parameters, or variables.
Format
<general value specification> ::=
!! All alternatives from ISO/IEC 9075:1992
| <routine invocation>
<function invocation> ::= <routine invocation>
Syntax Rules
1) If a <routine invocation> is specified, then the subject routine
shall be a function.
Note: The candidate <routine>s of a <routine invocation> are
defined in Subclause 9.1, "<routine invocation>".
Access Rules
None.
General Rules
None.
Scalar expressions 21
ISO/IEC JTC1/SC21 N8897
6.3 <datetime value function>
6.3 <datetime value function>
Function
Specify a function yielding a value of type datetime.
Format
No additional Format items.
Syntax Rules
No additional Syntax Rules.
Access Rules
No additional Access Rules.
General Rules
1) If an SQL-statement other than a <compound statement> causes the
evaluation of one or more <datetime value function>s, then all
such evaluations are effectively performed simultaneously. The
time of evaluation of the <datetime value function> during the
execution of the SQL-statement is implementation-dependent.
22 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
7 Query expressions
7.1 <query specification>
Function
Specify a table derived from the result of a <table expression>.
Format
<user-defined updatability method> ::=
!! All alternatives from ISO/IEC 9075:1992
<routine invocation>
| <compound statement>
Syntax Rules
1) If a <routine invocation> is specified, then the subject routine
shall be a function.
Access Rules
No additional Access Rules.
General Rules
1) If a <user-defined insert clause> is specified, then for each
row inserted into TQS by an <insert statement>,
Case:
a) If a <function invocation> is specified in the <user-defined
insert clause>, then the function identified by <function
invocation> is invoked.
b) Otherwise, the <delete statement: searched>, <update
statement: searched>, <insert statement>, or <compound
statement> specified in the <user-defined insert clause> is
executed.
2) If a <user-defined delete clause> is specified, then for each
row deleted from TQS by a <delete statement: positioned> or a
<delete statement: searched>,
Case:
a) If a <function invocation> is specified in the <user-defined
delete clause>, then the function identified by <function
invocation> is invoked.
Query expressions 23
ISO/IEC JTC1/SC21 N8897
7.1 <query specification>
b) Otherwise, the <delete statement: searched>, <update
statement: searched>, <insert statement>, or <compound
statement> specified in the <user-defined insert clause> is
executed.
3) If a <user-defined update clause> is specified, then for each
row updated in TQS by an <update statement: positioned> or an
<update statement: searched>,
Case:
a) If a <function invocation> is specified in the <user-defined
update clause>, then the function identified by <function
invocation> is invoked.
b) Otherwise, the <delete statement: searched>, <update
statement: searched>, <insert statement>, or <compound
statement> specified in the <user-defined insert clause> is
executed.
24 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
7.2 <query expression>
7.2 <query expression>
Function
Specify a table.
Format
No additional Format items.
Syntax Rules
No additional Syntax Rules.
Access Rules
No additional Access Rules.
General Rules
Query expressions 25
ISO/IEC JTC1/SC21 N8897
26 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
8 Data assignment rules and function determination
8.1 Subject routine determination
Function
Determine the set of subject routines applicable to a given
invocation.
Syntax Rules
1) Let CR and CA be a set of candidate routines and arguments
specified in an application of this Subclause.
2) Let CRi be the i-th candidate routine.
3) Let m be the number of arguments in the <argument list>. Let
n be the number of candidate routines. Let CAj be the j-th
<argument> in the <argument list>. Let TCAj be its most specific
type.
4) Let TPDij be the most specifric type of the j-th parameter
declaration of the i-th candidate routine.
5) For every CAj, eliminate from the set of candidate routines
every CRi for which TPDij is not in the type precedence list
of TCAj. The remaining set of candidate routines shall not be
empty.
6) For r varying from 1 to m, if there is more than one routine
in the remaining set of candidate routines, then for each
pair of candidate routines { CRp, CRq } in the remaining set
of candidate routines, if TPDpj precedes TPDqj in the type
precedence list of CAr, then eliminate CRq from the remaining
set of candidate routines.
7) The set of subject <routine>s is the remaining set of candidate
routines.
____________________________________________________________________
**Editor's Note**
When two functions are declared with the same name, same number
of arguments, and the same names/types of those arguments but in
a different order, a keyword-syntax invocation using that function
name makes it problematic to identify which function to actually
_invoke._See_Possible_Problem_<321>.________________________________
| |
| |
| |
| |
Data assignment rules and function determination 27
ISO/IEC JTC1/SC21 N8897
8.2 Type precedence list determination
8.2 Type precedence list determination
Function
Determine the type precedence list of a given type.
Syntax Rules
1) Let DT be the data type specified in an application of this
Subclause.
2) Let TPL be the type precedence list of DT.
3) If DT is fixed-length character string, then TPL is CHARACTER,
CHARACTER VARYING.
4) If DT is variable-length character string, then TPL is CHARACTER
VARYING.
5) If DT is fixed-length bit string, then TPL is BIT, BIT VARYING.
6) If DT is variable-length bit character string, then TPL is BIT
VARYING.
7) If DT is numeric, then
Case:
a) If DT is SMALLINT, then TPL is SMALLINT, INTEGER, NUMERIC,
DECIMAL, REAL, FLOAT, DOUBLE PRECISION.
b) If DT is INTEGER, then TPL is INTEGER, NUMERIC, DECIMAL,
REAL, FLOAT, DOUBLE PRECISION.
c) If DT is NUMERIC, then TPL is NUMERIC, DECIMAL, REAL, FLOAT,
DOUBLE PRECISION.
d) If DT is DECIMAL, then TPL is DECIMAL, REAL, FLOAT, DOUBLE
PRECISION.
e) If DT is REAL, then TPL is REAL, FLOAT, DOUBLE PRECISION.
f) If DT is FLOAT, then TPL is FLOAT, DOUBLE PRECISION.
g) If DT is DOUBLE PRECISION, then TPL is DOUBLE PRECISION.
8) If DT is INTERVAL, then TPL is DT or a comparable interval type.
9) If DT is a datetime type, then TPL is DT.
28 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
9 Additional common elements
9.1 <routine invocation>
Function
Invoke a routine.
Format
<routine invocation> ::=
<routine name> <argument list>
<argument list> ::=
<left paren> <positional arguments> <right paren>
| <left paren> <right paren>
<positional arguments> ::=
<argument> [ { <comma> <argument> }... ]
<argument> ::=
<value expression>
Syntax Rules
1) If an <argument> is a <value expression> or <status parameter>
and the <data type> of the <argument> is not part of a subtype
family, or if the <argument> does not correspond with an input
parameter, then the typeset for the <argument> contains a single
member that is the data type of the <argument>.
2) Let RN be the <routine name> of a <routine invocation> RI, let
QI be the <qualified identifier> of RN, and let NA be the number
of <argument>s in the <argument list> of RI.
3) The scope of the <routine name> of a <routine> R is the <routine
body> of R. If R is simply contained in a <module> whose <module
contents> contain a <declare cursor> C and R contains an <open
statement> that references the <cursor name> of C, then the
scope_of_the_<routine_name>_of_R_includes_the_<declare_cursor>._____
**Editor's Note**
The preceding Rule is based on the presumption that only a single
<routine> in a <module> can contain an <open statement> for any
given cursor. However, that restriction no longer applies. See
Possible_Problem_<319>._____________________________________________
| |
| |
| Additional common elements 29 |
ISO/IEC JTC1/SC21 N8897
9.1 <routine invocation>
4) A <routine> R is a possibly candidate routine if the <qualified
identifier> of the <routine name> of R is equal to RN and
Case:
a) If R is contained in a <module> M, then the applicable
privileges include EXECUTE on M.
b) If R is an external routine, then the applicable privileges
include EXECUTE on R.
5) Let Ai be the i-th <argument> of RI and let Pi be the i-th
parameter of a possibly candidate routine. A callable candidate
routine of RI is a routine CCR that is a possibly candidate
routine such that for each Ai whose type is an abstract data
type, if Pi is an output parameter or both an output parameter
and an input parameter, then Ai and Pi have the same data type;
otherwise, the type of Ai is a subtype of the type of Pi.
6) A <routine> R is an effectively candidate routine if there is
an effective <argument list> for the <parameter list> of R and
the <argument list> of RI, as determined by the Syntax Rules of
<REFERENCE>(psm_effarglist\FULL).___________________________________
**Editor's Note**
When paper X3H2-94-102/SOU-063 moved this Subclause into the SQL
/PSM document, it marked the Subclause dealing with Effective
argument list determination as "SQL3 Only". However, the preceding
reference to that Subclause is relevant for PSM-92 (as well as
PSM3)._The_Editor_requests_help_in_resolving_this_problem.__________
| |
7)|If RN does not contain a <local or schema qualifier>, then |
| |
|Case: |
| |
|a) If RI is contained in a <module> whose <module contents> MC |
| contains a possibly candidate routine, then the candidate |
<routine>s of RI are the effectively candidate routines (if
any) contained in MC.
b) Otherwise:
i) If RI is contained in a <module> without an intervening
<schema definition>, then let DP be the default SQL-path of
the <module>. If RI is contained in a <schema definition>
without an intervening <module>, then let DP be the default
path of the <schema definition>.
ii) Let Si be the i-th <schema name> in DP. For every Si that
contains at least one possibly candidate routine R2i, let
RFi be the routine family of R2i. The candidate <routine>s
of RI are the set of all routines resulting from the set
union of all RFi.
30 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
9.1 <routine invocation>
____________________________________________________________________
**Editor's Note**
The preceding Rule from X3H2-93-342R uses the concept of "routine
family", which X3H2-93-305 completely eliminated from the document.
What_should_the_resolution_of_this_conundrum_be?____________________
| |
| |
8) If R| contains a <local or schema qualifier> LSQ, then |
| |
Case:
a) If LSQ is "MODULE", then the candidate <routine>s of R are
the effectively candidate routines (if any) contained in M.
b) Otherwise, LSQ shall be the <schema name> of a schema S.
The candidate <routine>s of R are the effectively candidate
routines (if any) contained in S.
9) There shall be at least one candidate routine.
10)Let AL be the <argument list> of a <routine invocation>. Let
XAL be the set of all <argument list>s XALi derived from AL
by forming the extended Cartesian product of the <argument>'s
typesets. The set XAL represents all possible combinations of
most-specific data types present in AL when the <argument>s
are evaluated. For each such XALi, the Syntax Rules of
Subclause 8.1, "Subject routine determination", are applied
to the candidate <routine>s and operands of XALi, yielding a set
of candidate subject <routine>s.
Case:
a) If RN contains a <local or schema qualifier> or RI is
contained in a <module> whose <module contents> contains a
possibly condidate routine R2, then for each XALi, there
shall be at most one candidate subject <routine> in the set.
The set of subject <routine>s is the set of candidate subject
<routine>s.
b) Otherwise, for each XAi ,
Case:
i) If there is at most one candidate subject <routine> in the
set, then select that routine (if any).
ii) If there are more than one candidate subject <routine>
in the set, then select from the set of candidate subject
<routine>s a routine SR such that there is no other routine
SR2 for which the <schema name> of the schema that contains
SR2 precedes in DP the <schema name> of the schema that
contains SR.
Let the set of subject <routine>s be the set of all selected
candidate subject <routine>s.
Additional common elements 31
ISO/IEC JTC1/SC21 N8897
9.1 <routine invocation>
11)For each subject <routine> SR, let SRPL be the <parameter list>
of SR and let SREAL be EAL(SR). For each <parameter declaration>
PDi in SRPL that is an output parameter, if SREALi is supplied,
then SREALi shall be a <target specification>.
Note: "Supplied" arguments are defined in <REFERENCE>(psm_
effarglist\FULL).___________________________________________________
**Editor's Note**
When paper X3H2-94-102/SOU-063 moved this Subclause into the SQL
/PSM document, it marked the Subclause dealing with Effective
argument list determination as "SQL3 Only". However, the preceding
reference to that Subclause is relevant for PSM-92 (as well as
PSM3)._The_Editor_requests_help_in_resolving_this_problem.__________
| |
12|All of the identified subject <routine>s shall be functions. |
| |
13|For any subject <routine> that is not an iterable subject |
|routine, its effective returns data type is its <returns data |
|type>. |
14)Case:
a) If there is a single identified subject <routine>, then
the data type of the <routine invocation> is the effective
returns data type of that <routine>.
b) Otherwise, let Ri be the effective returns data type for the
identified subject <routine> for each XALi. All data types
RTi shall be members of the same subtype family. The data
type of the <routine invocation> is the most-specific single
data type that is a supertype of all of the RTi.
15)If any subject <routine> is a function, then:
a) All of the identified subject <routine>s shall be functions.
b) For any subject <routine> that is not an iterable subject
routine, its effective returns data type is its <returns data
type>.
c) Case:
i) If there is a single identified subject <routine>, then
the data type of the <routine invocation> is the effective
returns data type of that <routine>.
ii) Otherwise, let RTi be the effective returns data type for
the identified subject <routine> for each XALi. All data
types RTi shall be members of the same subtype family. The
data type of the <routine invocation> is the most-specific
single data type that is a supertype of all of the RTi.
32 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
9.1 <routine invocation>
16)If the routine is a handler procedure, then it may not be
explicitly invoked.
Note: Handler procedures mayu only be invoked by the raising
of an exception condition or completion condition within a
<routine>.__________________________________________________________
**Editor's Note**
The preceding Rule does not seem proper. Are we trying to inform
somebody that it is impossible for such a procedure to be
implicitly invoked, or or we saying that it is an error for that
to occur. If the former, then that is hardly a normative statement,
but might be subject for a Note. If the latter, then the only
appropriate wording is to raise an exception if it occurs.
Jeff Richey has volunteered to write a proposal replacing this
Rule with a General Rule raising an exception when explicit
invocation is attempted. This proposal would be processed as
___part_of_the_anticipated_comments_on_this_CD_For_Comment._________
| |
Ac|ess Rules |
| |
|None. |
| |
Ge|eral Rules |
| |
1)|Evaluate, in any order, each <argument> contained in the |
|<argument list> of the <routine invocation> to obtain a value. |
| |
2) Let PL be the evaluated <argument>s, and let XALj be the member
of XAL whose <argument>s have the same data types as PL in the
same order. The subject <routine> to be invoked is the one
yielded for XALj by the Syntax Rules of this Subclause, and
it has effective <returns data type> RTj.
3) If the <routine> is not an iterable subject <routine>, then
let N be the number of <argument>s of the <argument list>,
and for i ranging from 1 to N, let PVi be the value of the
i-th <argument>, Ti be the data type of the i-th <parameter
declaration> of the <parameter list> of the subject <routine>,
and let CPVi be the result of the assignment of PVi to a target
of type Ti according to the rules of Subclause 9.2, "Store
assignment", in ISO/IEC 9075:1992.
4) If the <routine> is an SQL routine, then:
Case:
a) If the <routine> is not an iterable subject <routine>, then
invoke the subject <routine> and supply the values CPVi as an
argument value list. If the subject <routine> is a function,
then let R be the returned value.
Additional common elements 33
ISO/IEC JTC1/SC21 N8897
9.1 <routine invocation>
b) Otherwise,
i) Let AV be the value of the single <argument>.
ii) Let :ARGUMENT and :RESULT be implementation-dependent <SQL
variable name>s different from each other and from the
<SQL variable name> of any other variable with overlapping
scope.______________________________________________________________
The Editor does not understand why the preceding Rule is required
in a form that depends on <SQL variable name>s. Help is requested
in_"splitting"_this_Rule_for_SQL/PSM_versus_SQL3.___________________
| |
5) If t|e subject <routine> is an external routine that specified |
PARAMETER STYLE SQL, then:
a) Let N be the number of <argument>s. Supply N + 1 indicator
arguments whose data type is exact numeric with a scale of 0.
Set the value of indicator argument N + 1 to 0. If CPi is a
null value, then set the value of the i-th indicator argument
to -1; otherwise, set the value of the i-th indicator
argument to 0.
b) Let PN be the number of data types i that are predefined
types. Let UN be the number of base items in the data types
of Ti that are abstract data types. If the subject <routine>
is a procedure, then let RN be 0. If the subject <routine>
is a function, then let RT be the <returns data type>. If RT
is an abstract data type, then let RN be the number of base
items in that data type; otherwise, let RN be 1.
c) The exception data item is set to '00000'.
d) Supply an argument value list of PN+UN+RN+N+5 entries:
i) Argument value list entries 1 to PN + UN are N groups of
entries.
ii) Argument value list entry (PN+UN+RN+N+1)+2 is the name
of the external routine being invoked.
iii) Argument value list entry (PN+UN+RN+N+1)+3 is the specific
name of the external routine being invoked.
iv) Argument value list entry (PN+UN+RN+N+1)+4 is the message
text item.
v) For i ranging from 1 to N: if Ti is a predefined type, then
the i-th group of argument value list entries consists of
the single value CPVi; if Ti is an abstract data type, then
the i-th group of argument value list entries consists of
the base items of value CPVi.
34 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
9.1 <routine invocation>
vi) If the subject <routine> is a function, then argument value
list entries (PN+UN)+1 to (PN+UN)+RN are as follows:
1) If RT is a predefined type, then argument value list
entry (PN+UN)+1 is the single result data item.
2) If RT is an abstract data type, then argument value list
entries (PN+UN)+1 to (PN+UN)+RN are the RN base items
of the result data item.
vii) Argument value list entries (PN+UN+RN)+1 to (PN+UN+RN)+N+1
are the N+1 indicator arguments.
viii)Argument value list entry (PN + UN + RN + N + 1) + 1
exception data item.
e) The value of the implicit or explicit <external routine
name> identifies an external routine. The method and time of
binding of the designated external routine to the schema or
<module> containing the <routine> is implementation-defined.
f) If the subject <routine> specifies NOT VARIANT and if
different invocations of the external routine with identical
argument value lists do not produce identical results, then
the results are implementation-dependent.
g) Invoke the external routine of the subject <routine> with the
argument value list.
h) If the <external routine language clause> specifies ADA
(respectively C, COBOL, FORTRAN, MUMPS, PASCAL, PLI) and
the external routine designated by the <routine name> is
not a standard Ada program (respectively C, COBOL, Fortran,
MUMPS, Pascal, PL/I program), then the effect of the <routine
invocation> is implementation-dependent.
i) After execution of the external routine completes,
Case:
i) If the exception data item is '00000', then the external
routine was successful.
ii) If the first two characters of the exception data item
are equal to the SQLSTATE exception code class value for
external function exception, then an exception condition is
raised: external function exception, using a subclass code
equal to the final three characters of the exception data
item.
iii) If the first two characters of the exception data item
are equal to the SQLSTATE exception code class value for
warning and the third character of the exception data item
is 'H', then a completion condition is raised: warning,
using a subclass code equal to the final three characters
of the exception data item.
Additional common elements 35
ISO/IEC JTC1/SC21 N8897
9.1 <routine invocation>
iv) Otherwise, an exception condition is raised: external
function call exception-invalid SQLSTATE returned.
j) If the exception data item is not '00000', then the message
text item is stored in the diagnostics area.
k) If the subject <routine> is a function, then: If the value
of indicator argument N + 1 is non-negative, then let R1 be
the value of the result data item. Otherwise, let R1 be the
general null value.
Case:
i) If the subject <external function declaration> contains a
<result cast>, then let RT be the <returns data type> of
the subject <routine>. Let R be the result of
CAST ( R1 AS RT )
ii) Otherwise, let R be R1.
l) If the i-th <parameter declaration> of the subject <routine>
is an output parameter, then: If the i-th indicator argument
value is negative, then the value of the i-th argument is the
general null value; otherwise, the value of the i-th argument
is the corresonding group of argument values.
6) If the subject <routine> is an external routine that specified
PARAMETER STYLE GENERAL, then:
a) Let N be the number of <argument>s.
b) If any of the <argument>s is a null value, then an exception
condition is raised: external function call exception-null
value not allowed.
c) Let PN be the number of data types i that are pre-defined
types. Let UN be the number of base items in the data types
of Ti that are abstract data types.
d) Supply an argument value list of PN+UN entries:
i) Argument value list entries 1 to PN + UN are N groups of
entries.
ii) For i ranging from 1 to N: if Ti is a pre-defined type,
then the i-th group of argument value list entries consists
of the single value CPVi; if Ti is an abstract data type,
then the i-th group of argument value list entries consists
of the base items of value CPVi.
e) If the subject <routine> specifies NOT VARIANT and if
different invocations of the external routine with identical
argument value lists do not produce identical results, then
the results are implementation-dependent.
36 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
9.1 <routine invocation>
f) Invoke the external routine of the subject <routine> with the
argument value list.
g) If the <external routine language clause> specifies ADA
(respectively C, COBOL, FORTRAN, MUMPS, PASCAL, PLI) and
the external routine designated by the <routine name> is
not a standard Ada program (respectively C, COBOL, Fortran,
MUMPS, Pascal, PL/I program), then the effect of the <routine
invocation> is implementation-dependent.
h) After execution of the external routine completes, if the
implementation determines that the external routine did not
complete successfully, then an exception condition is raised:
external function call exception.
i) If the subject <routine> is a function, then let R1 be the
returned value of the external routine.
Case:
i) If the subject <external function declaration> contains a
<result cast>, then let RT be the <returns data type> of
the subject <routine>. Let R be the result of:
CAST ( R1 AS RT )
ii) Otherwise, let R be R1.
j) If the i-th <parameter declaration> of the subject <routine>
is an output parameter, then the value of the i-th argument
is the corresponding group of argument values.
7) For each <parameter declaration> of the subject <routine>
that is an output parameter, let AV be the corresponding
argument value and let TS be the <target specification> of
the corresponding <argument>; the value of TS is set to the
result of the assignment of AV to TS according to the rules of
Subclause 9.1, "Retrieval assignment", in ISO/IEC 9075:1992.
8) If the subject <routine> is a function, then R is the result of
the_<routine_invocation>.___________________________________________
**Editor's Note**
I expected to find Rules in this Subclause analogous to those in
Subclause 12.3, "<procedure>", and Subclause 20.1, "<direct SQL
statement>", and analogous to those formerly found in the now
defunct Subclause 13.whatever, "<procedure>". The Rules that I
expected to find define the behavior of the <routine> whenever
it is invoked, including making implicit connections, starting
transactions, etc.
What am I missing here? All I find here are Rules for setting
up the parameters and, if the <routine> is a function, returning
___the_value._______________________________________________________
| |
| Additional common elements 37 |
ISO/IEC JTC1/SC21 N8897
9.2 <privileges>
9.2 <privileges>
Function
Specify privileges.
Format
<object name> ::=
!! All alternatives from ISO/IEC 9075:1992
| EXTERNAL ROUTINE <specific routine designator>
Syntax Rules
1) If <object name> specifies EXTERNAL ROUTINE or MODULE, then
<privileges> shall specify EXECUTE; otherwise, EXECUTE shall not
be specified.
2) If EXTERNAL ROUTINE is specified, then the routines identified
by the <specific routine designator> shall be an external
routine.
Access Rules
None.
General Rules
None.
38 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
9.3 <specific routine designator>
9.3 <specific routine designator>
Function
Specify a routine.
Format
<specific routine designator> ::=
SPECIFIC <specific name>
| <member name>
<member name> ::= <routine name> [ <data type list> ]
<data type list> ::=
<left paren> <data type> [ { <comma> <data type> }... ] <right paren>
Syntax Rules
1) If a <specific name> SN is specified, then the <specific routine
designator> identifies the routine whose <specific name> is SN.
2) If a <member name> MN is specified, then:
a) Let RN be the <routine name> of MN and let RF be the routine
family of RN.
b) Case:
i) If MN contains a <data type list>, then for all i there
shall be exactly one member routine in RF whose i-th
<parameter declaration> specifies a <data type> that is
identical to the i-th <data type> in the <data type list>
of MN. The <specific routine designator> identifies that
routine.
ii) Otherwise, there shall be exactly one routine in RF. The
<specific routine designator> identifies that routine.
Access Rules
None.
General Rules
None.
Additional common elements 39
ISO/IEC JTC1/SC21 N8897
40 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
10 Schema definition and manipulation
10.1 <schema definition>
Function
Define a schema.
Format
<schema element> ::=
!! All alternatives from ISO/IEC 9075:1992
| <SQL-server module definition>
| <routine>
Syntax Rules
No additional Syntax Rules.
Access Rules
No additional Access Rules.
General Rules
No additional General Rules.
Schema definition and manipulation 41
ISO/IEC JTC1/SC21 N8897
10.2 <drop schema statement>
10.2 <drop schema statement>
Function
Destroy a schema.
Format
No additional Format items.
Syntax Rules
No additional Syntax Rules.
Access Rules
No additional Access Rules.
General Rules
1) Let M be the <module name> of any module contained in S. The
following <drop module statement> is effectively executed:
DROP MODULE M CASCADE
42 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
10.3 <default clause>
10.3 <default clause>
Function
Specify the default for a column or domain.
Format
<default option> ::=
!! All alternatives from ISO/IEC 9075:1992
| <routine invocation>
Syntax Rules
1) The subject data type of a <default clause> is the data type
specified in the descriptor identified by the containing <SQL
variable declaration>.
Access Rules
No additional Access Rules.
General Rules
No additional General Rules.
Schema definition and manipulation 43
ISO/IEC JTC1/SC21 N8897
10.4 <drop routine statement>
10.4 <drop routine statement>
Function
Destroy an SQL routine.
Format
<drop routine statement> ::=
DROP { PROCEDURE | FUNCTION } <specific routine designator>
<drop behavior>
Syntax Rules
1) Let SR be the routine identified by the <specific routine
designator> and let RF be the routine family of SR.
2) Case:
a) If FUNCTION is specified, then SR shall be a function.
b) Otherwise, SR shall be a procedure.
3) If RESTRICT is specified, then SR shall not be referenced in
the <query expression> of any view descriptor, the <search
condition> of any constraint descriptor, the <default option>
of any column descriptor, or in any trigger descriptor, domain
descriptor,_assertion_descriptor.___________________________________
**Editor's Note**
The preceding Rule fails to address routines or modules that
reference the routine being dropped. See Possible Problem <340>
in_the_Editor's_Notes.______________________________________________
| |
Ac|ess Rules |
| |
1)|The <authorization identifier> of the current SQL-session shall |
be equal to the <authorization identifier> that owns the schema
identified by the implicit or explicit <schema name> of SR.
General Rules
1) If RF identifies a routine family with exactly one member, then
for every privilege descriptor with a grantor of "_SYSTEM" that
specifies some FUNCTION PRIVILEGES on SR, the following <revoke
statement> is effectively executed as though the <authorization
identifier> of the current SQL-session were "_SYSTEM" and
without further Access Rule checking:
REVOKE FUNCTION PRIVILEGES ON EN FROM GRANTEE CASCADE
where GRANTEE is the grantee in the privilege descriptor.
44 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
10.4 <drop routine statement>
____________________________________________________________________
**Editor's Note**
The preceding Rule refers to the FUNCTION privilege, but there
is no such privilege. See Possible Problem <339> in the Editor's
Notes.______________________________________________________________
| |
2)|The identified routine and its descriptor are destroyed. |
| |
| |
Schema definition and manipulation 45
ISO/IEC JTC1/SC21 N8897
46 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
11 Persistent stored modules
11.1 Introduction
<To be supplied.>
Persistent stored modules 47
ISO/IEC JTC1/SC21 N8897
11.2 <module>
11.2 <module>
Function
Define a module.
Format
<module contents> ::=
!! All alternatives from ISO/IEC 9075:1992
| <routine>
| DECLARE <SQL variable declaration>
<global declaration> ::=
!! All alternatives from ISO/IEC 9075:1992
| <exception declaration>
| <handler declaration>
| <SQL variable declaration>
Syntax Rules
1) An <exception declaration> shall precede in the text of the
module any <handler declaration> that references the <exception
name> of the <exception declaration>.
2) An <SQL variable declaration> specified in <module contents>
shall precede in the text of the <module> any reference to the
<SQL variable name> of the <SQL variable declaration>.
3) An <SQL variable name> contained in an <SQL variable
declaration> that is specified in <module contents> shall be
different from any other <SQL variable name> contained in any
other <SQL variable declaration> that is specified in <module
contents>.
4) A variable declared by an <SQL variable declaration> that
is specified in <module contents> is referred to as a module
variable. A module variable is associated with the module that
is specified by a <module> that specifies its <SQL variable
declaration>.
5) If the <module> does not specify a <module contents> that is
a <handler declaration> specifying an <exception value> of
SQLEXCEPTION, then:
a) Let EP be a <routine name> different from the <routine name>
of any other <routine> that is a <module contents> of the
containing <module>.
b) The <module> implicitly contains, preceding its first <module
contents>:
HANDLER PROCEDURE EP() RESIGNAL ;
48 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
11.2 <module>
DECLARE EP HANDLER FOR SQLEXCEPTION ;
6) If a <handler declaration> specifies an <exception name>,
then an <exception declaration> containing that <exception
name> shall precede in the text of the <module> that <handler
declaration>.
7) A <handler declaration> shall precede in the text of the
<module> any handler procedure trhat references the <procedure
name> of the <handler declaration>.
Access Rules
None.
General Rules
1) Before the first time that an SQL-agent performs a call of a
<procedure> that references the <SQL variable name> of a module
variable, that variable is created.
2) After the last time that an SQL-agent performs a call of a
<routine>:
a) All module variables associated with this module that have
been created are destroyed.
Persistent stored modules 49
ISO/IEC JTC1/SC21 N8897
11.3 <routine>
11.3 <routine>
Function
Define a routine.
Format
<routine> ::=
[ CREATE | DECLARE ] <routine specification>
<routine specification> ::=
<routine header> <routine name> <parameter list>
[ <returns clause> ]
[ <caller language clause> ]
[ SPECIFIC <specific name> ] [ <routine header terminator> ]
<routine body>
<routine header> ::=
[ <procedure type> ] PROCEDURE
| FUNCTION
<procedure type> ::=
HANDLER [ REDO | UNDO ]
<parameter list> ::=
<left paren>
[ <parameter declaration> [ { <comma> <parameter declaration> }... ] ]
<right paren>
<parameter declaration> ::=
[ <parameter mode> ] [ <parameter name> ] <data type>
| <status parameter>
<status parameter> ::=
SQLSTATE
| SQLCODE
<parameter mode> ::=
IN
| OUT
| INOUT
<returns clause> ::= RETURNS <returns data type> [ <result cast> ]
<result cast> ::= CAST FROM <data type>
<returns data type> ::= <data type>
<caller language clause> ::= <language clause>
<routine header terminator> ::= <semicolon>
50 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
11.3 <routine>
<routine body> ::=
<SQL routine body>
| <external body reference>
<SQL routine body> ::= <SQL procedure statement>
<external body reference> ::=
EXTERNAL [ NAME <external routine name> ]
<external routine language clause>
[ <parameter style> ]
[ <variant attribute> ]
<parameter style> ::=
PARAMETER STYLE { SQL | GENERAL }
<variant attribute> ::=
VARIANT
| NOT VARIANT
<external routine language clause> ::= <language clause>
Note: <parenthesized style list> is the preferred form of
<parameter list>. The <unparenthesized style list> is a deprecated
feature that is supported for compatibility with earlier versions
of this International Standard. See Appendix D, "Deprecated
features".
Syntax Rules
1) A <routine> shall contain a <returns clause> if and only if the
<routine header> specifies FUNCTION.
2) Case:
a) If <routine> is immediately contained in a <schema element>
or an <SQL schema definition statement>, then CREATE shall be
specified.
b) If <routine> is immediately contained in a <module contents>,
then CREATE shall not be specified.
c) Otherwise, neither CREATE nor DECLARE shall be specified.
3) A <routine> that specifies PROCEDURE is called a procedure.
A <routine> that specifies FUNCTION is called a function.
A <routine> (procedure or function) whose whose <caller
language clause> specifies SQL is called an SQL-invoked routine
(procedure or function). A <routine> (procedure or function)
whose <caller language clause> does not specify SQL is called
an externally-invoked routine (procedure or function). A
<routine> (procedure or function) that specifies an <SQL routine
body> is called an SQL routine (procedure or function). A
<routine> (procedure or function) that specifies an <external
Persistent stored modules 51
ISO/IEC JTC1/SC21 N8897
11.3 <routine>
body reference> is called an external routine (procedure or
function).
4) Case:
a) If a <routine> is contained in a <module>, then it shall
not specify a <caller language clause>. A <caller language
clause> that is the same as the <language clause> of the
<module> is implicit.
b) If a <routine> is not contained in a <module>, then if a
<caller language clause> is not specified, then LANGUAGE SQL
is implicit.
5) In a <parameter list> PL, the <parameter name> of each
<parameter declaration> that contains a <parameter name> shall
be different from the <parameter name> of any other <parameter
declaration> in PL that contains a <parameter name>.
6) If a <routine> R is an SQL-invoked routine that is simply
contained in a <schema definition> or <module>, then:
a) Case:
i) If R is simply contained in a <schema definition>, then
let the subject schema name be the <schema name> of that
<schema definition>.
ii) If R is simply contained in a <module> that is not
contained in a schema, then let the subject schema name
be the implicit or explicit <schema name> of the <module
name> of that <module> .
b) If RN contains a <local or schema qualifier>, then it shall
be the same as the subject <schema name>. If RN does not
contain a <local or schema qualifier>, then the subject
<schema name> is implicit.
c) If <specific name> is not specified, then an implementation-
dependent_<specific_name>_is_implicit.______________________________
**Editor's Note**
Rules like the preceding Rule that require the implementation to
"invent" a name for something don't specify what the catalog and
schema components of that name should be. This has been identified
as a Possible Problem. See Possible Problem <270> in the Editor's
_Notes._____________________________________________________________
| |
d| If <specific name> contains a <schema name>, then that |
|<schema name> shall be the same as the subject <schema name>. |
| |
e| If <specific name> does not contain a <schema name>, then the |
|subject <schema name> is implicit. |
52 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
11.3 <routine>
f) The schema identified by the explicit or implicit <schema
name> of the <specific name> shall not include a routine
descriptor whose specific name is <specific name>.
g) The <parameter list> shall not contain a <status parameter>.
7) If a <routine> is an externally-invoked routine, then:
a) The <routine name> shall be an <identifier>.
b) <specific name> shall not be specified.
c) If the <routine> is contained in a <module>, then the
<identifier> of the <routine name> of the <routine> shall
be different from the <identifier> of the <routine name> of
any other <routine> in the containing <module>.
d) If the <routine> is contained in a schema, then the
<identifier> of the <routine name> of the routine shall be
different from the <identifier> of the <routine name> of any
other <routine> in that schema.
e) The <parameter list> shall contain at least one <status
parameter>, at most one <status parameter> that specifies
SQLCODE, and at most one <status parameter> that specifies
SQLSTATE. A parameter that corresponds with SQLCODE is
referred to as an SQLCODE parameter. A parameter than
corresponds with SQLSTATE is referred to as an SQLSTATE
parameter. The SQLCODE and SQLSTATE parameters are referred
to as status parameters.
Note: SQLSTATE is the preferred status parameter. The SQLCODE
status parameter is a deprecated feature that is supported
for compatibility with earlier versions of this International
Standard. See Appendix D, "Deprecated features".
f) When the <external routine language clause> specifies
ADA, C, COBOL, FORTRAN, MUMPS, PASCAL, or PLI, then let
the operative data type correspondences table be Table 1,
"Data type correspondences for Ada", Table 2, "Data type
correspondences for C", Table 3, "Data type correspondences
for COBOL", Table 4, "Data type correspondences for Fortran",
Table 5, "Data type correspondences for MUMPS", Table 6,
"Data type correspondences for Pascal", or Table 7, "Data
type correspondences for PL/I", respectively. Refer to
the two columns of the operative data type correspondences
table as the "SQL data type" column and the "host data type
column".
g) If the <routine> is a function, then let the externally
returned data type be the <data type> specified by the
<returns data type>.
Persistent stored modules 53
ISO/IEC JTC1/SC21 N8897
11.3 <routine>
h) Any <data type> in a <parameter declaration> or externally
returned data type (if any) shall specify a data type listed
in the SQL data type column for which the corresponding row
in the host data type column is not "none". If the <data
type> in a <parameter declaration> or externally returned
data type (if any) specifies the data type listed in the i-th
row of the SQL data type column, then the type of the i-th
parameter or the externally returned data type shall be the
type listed in the i-th row of the host data type column.
i) If the <caller language clause> specifies ADA, then:
i) The <routine>s of the <module> are identified by the
<procedure name> as if they were declared immediately
within an Ada library unit package specification that has
a name identical to the <module name> of the containing
<module>.
ii) The Ada base type of any parameter shall be an Ada data
type declared in an Ada package named SQL_STANDARD of the
following form:
package SQL_STANDARD is
package CHARACTER_SET renames csp;
subtype CHARACTER_TYPE is CHARACTER_SET.cst;
type CHAR is array (POSITIVE range <>) of CHARACTER_
TYPE;
type BIT is array (NATURAL range <>) of BOOLEAN;
type SMALLINT is range bs .. ts;
type INT is range bi .. ti;
type REAL is digits dr;
type DOUBLE_PRECISION is digits dd;
subtype INDICATOR_TYPE is t;
type SQLCODE_TYPE is range bsc .. tsc;
subtype SQL_ERROR is SQLCODE_TYPE range SQL_
TYPE'FIRST .. -1;
subtype NOT_FOUND is SQLCODE_TYPE range 100 .. 100;
type SQLSTATE_TYPE is new CHAR (1 .. 5);
package SQLSTATE_CODES is
AMBIGUOUS_CURSOR_NAME_NO_SUBCLASS:
constant SQLSTATE_TYPE := "3C000";
CASE_NOT_FOUND_FOR_CASE_STATEMENT:
constant SQLSTATE_TYPE := "20000";
CARDINALITY_VIOLATION_NO_SUBCLASS:
constant SQLSTATE_TYPE := "21000";
CONNECTION_EXCEPTION_NO_SUBCLASS:
constant SQLSTATE_TYPE := "08000";
CONNECTION_EXCEPTION_CONNECTION_DOES_NOT_
EXIST:
constant SQLSTATE_TYPE := "08003";
CONNECTION_EXCEPTION_CONNECTION_FAILURE:
constant SQLSTATE_TYPE := "08006";
CONNECTION_EXCEPTION_CONNECTION_NAME_IN_USE:
54 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
11.3 <routine>
constant SQLSTATE_TYPE := "08002";
CONNECTION_EXCEPTION_SQLCLIENT_UNABLE_TO_
ESTABLISH_SQLCONNECTION:
constant SQLSTATE_TYPE := "08001";
CONNECTION_EXCEPTION_SQLSERVER_REJECTED_
ESTABLISHMENT_OF_SQLCONNECTION:
constant SQLSTATE_TYPE := "08004";
CONNECTION_EXCEPTION_UNKNOWN_STATEMENT_
COMPLETION:
constant SQLSTATE_TYPE := "08005";
CURSOR_OPERATION_CONFLICT_NO_SUBCLASS:
constant SQLSTATE_TYPE := "09000";
DATA_EXCEPTION_NO_SUBCLASS:
constant SQLSTATE_TYPE := "22000";
DATA_EXCEPTION_CHARACTER_NOT_IN_REPERTOIRE:
constant SQLSTATE_TYPE := "22021";
DATA_EXCEPTION_DATETIME_FIELD_OVERFLOW:
constant SQLSTATE_TYPE := "22008";
DATA_EXCEPTION_DIVISION_BY_ZERO:
constant SQLSTATE_TYPE := "22012";
DATA_EXCEPTION_ERROR_IN_ASSIGNMENT:
constant SQLSTATE_TYPE := "22005";
DATA_EXCEPTION_INDICATOR_OVERFLOW:
constant SQLSTATE_TYPE := "22022";
DATA_EXCEPTION_INTERVAL_FIELD_OVERFLOW:
constant SQLSTATE_TYPE := "22015";
DATA_EXCEPTION_INVALID_CHARACTER_VALUE_FOR_
CAST:
constant SQLSTATE_TYPE := "22018";
DATA_EXCEPTION_INVALID_DATETIME_FORMAT:
constant SQLSTATE_TYPE := "22007";
DATA_EXCEPTION_INVALID_ESCAPE_CHARACTER:
constant SQLSTATE_TYPE := "22019";
DATA_EXCEPTION_INVALID_ESCAPE_SEQUENCE:
constant SQLSTATE_TYPE := "22025";
DATA_EXCEPTION_INVALID_FETCH_SEQUENCE:
constant SQLSTATE_TYPE := "22006";
DATA_EXCEPTION_INVALID_PARAMETER_VALUE:
constant SQLSTATE_TYPE := "22023";
DATA_EXCEPTION_INVALID_REGULAR_EXPRESSION:
constant SQLSTATE_TYPE := "2201B";
DATA_EXCEPTION_INVALID_TIME_ZONE_DISPLACEMENT_
VALUE:
constant SQLSTATE_TYPE := "22009";
DATA_EXCEPTION_NULL_VALUE_NO_INDICATOR_
PARAMETER:
constant SQLSTATE_TYPE := "22002";
DATA_EXCEPTION_NUMERIC_VALUE_OUT_OF_RANGE:
constant SQLSTATE_TYPE := "22003";
DATA_EXCEPTION_STRING_DATA_LENGTH_MISMATCH:
constant SQLSTATE_TYPE := "22026";
DATA_EXCEPTION_STRING_DATA_RIGHT_TRUNCATION:
constant SQLSTATE_TYPE := "22001";
Persistent stored modules 55
ISO/IEC JTC1/SC21 N8897
11.3 <routine>
DATA_EXCEPTION_SUBSTRING_ERROR:
constant SQLSTATE_TYPE := "22011";
DATA_EXCEPTION_TRIM_ERROR:
constant SQLSTATE_TYPE := "22027";
DATA_EXCEPTION_UNTERMINATED_C_STRING:
constant SQLSTATE_TYPE := "22024";
DEPENDENT_PRIVILEGE_DESCRIPTORS_STILL_EXIST_NO_
SUBCLASS:
constant SQLSTATE_TYPE := "2B000";
EXTERNAL FUNCTION CALL EXCEPTION:
constant SQLSTATE_TYPE := "39000";
EXTERNAL FUNCTION CALL EXCEPTION_INVALID_
SQLSTATE_RETURNED:
constant SQLSTATE_TYPE := "39001";
EXTERNAL FUNCTION EXCEPTION:
constant SQLSTATE_TYPE := "38000";
EXTERNAL FUNCTION EXCEPTION_INVALID_RETURN_
VALUE_FOR_PREDICATE:
constant SQLSTATE_TYPE := "39002";
EXTERNAL FUNCTION EXCEPTION_NOT_AVAILABLE:
constant SQLSTATE_TYPE := "39001";
FEATURE_NOT_SUPPORTED_NO_SUBCLASS:
constant SQLSTATE_TYPE := "0A000";
FEATURE_NOT_SUPPORTED_MULTIPLE_ENVIRONMENT_
TRANSACTIONS:
constant SQLSTATE_TYPE := "0A001";
INTEGRITY_CONSTRAINT_VIOLATION_NO_SUBCLASS:
constant SQLSTATE_TYPE := "23000";
INVALID_AUTHORIZATION_SPECIFICATION_NO_
SUBCLASS:
constant SQLSTATE_TYPE := "28000";
INVALID_CATALOG_NAME_NO_SUBCLASS:
constant SQLSTATE_TYPE := "3D000";
INVALID_CHARACTER_SET_NAME_NO_SUBCLASS:
constant SQLSTATE_TYPE := "2C000";
INVALID_CONDITION_NUMBER_NO_SUBCLASS:
constant SQLSTATE_TYPE := "35000";
INVALID_CONNECTION_NAME_NO_SUBCLASS:
constant SQLSTATE_TYPE := "2E000";
INVALID_CURSOR_NAME_NO_SUBCLASS:
constant SQLSTATE_TYPE := "34000";
INVALID_CURSOR_STATE_NO_SUBCLASS:
constant SQLSTATE_TYPE := "24000";
INVALID_SCHEMA_NAME_NO_SUBCLASS:
constant SQLSTATE_TYPE := "3F000";
INVALID_SQL_DESCRIPTOR_NAME_NO_SUBCLASS:
constant SQLSTATE_TYPE := "33000";
INVALID_SQL_STATEMENT_NAME_NO_SUBCLASS:
constant SQLSTATE_TYPE := "26000";
INVALID_TRANSACTION_STATE_NO_SUBCLASS:
constant SQLSTATE_TYPE := "25000";
INVALID_TRANSACTION_TERMINATION_NO_SUBCLASS:
constant SQLSTATE_TYPE := "2D000";
56 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
11.3 <routine>
NO_DATA_NO_SUBCLASS:
constant SQLSTATE_TYPE := "02000";
REMOTE_DATABASE_ACCESS_NO_SUBCLASS:
constant SQLSTATE_TYPE := "HZ000";
SUCCESSFUL_COMPLETION_NO_SUBCLASS:
constant SQLSTATE_TYPE := "00000";
SYNTAX_ERROR_OR_ACCESS_RULE_VIOLATION_NO_
SUBCLASS:
constant SQLSTATE_TYPE := "42000";
SYNTAX_ERROR_OR_ACCESS_RULE_VIOLATION_IN_
DIRECT_STATEMENT_NO_SUBCLASS:
constant SQLSTATE_TYPE := "2A000";
SYNTAX_ERROR_OR_ACCESS_RULE_VIOLATION_IN_
DYNAMIC_STATEMENT_NO_SUBCLASS:
constant SQLSTATE_TYPE := "37000";
TRANSACTION_ROLLBACK_NO_SUBCLASS:
constant SQLSTATE_TYPE := "40000";
TRANSACTION_ROLLBACK_INTEGRITY_CONSTRAINT_
VIOLATION:
constant SQLSTATE_TYPE := "40002";
TRANSACTION_ROLLBACK_SERIALIZATION_FAILURE:
constant SQLSTATE_TYPE := "40001";
TRIGGERED_DATA_CHANGE_VIOLATION_NO_SUBCLASS:
constant SQLSTATE_TYPE := "27000";
WARNING_NO_SUBCLASS:
constant SQLSTATE_TYPE := "01000";
WARNING_CONSTRAINT_CURSOR_OPERATION_CONFLICT:
constant SQLSTATE_TYPE := "01001";
WARNING_DISCONNECT_ERROR:
constant SQLSTATE_TYPE := "01002";
WARNING_EXTERNAL_ROUTINE_WARNING:
constant SQLSTATE_TYPE := "01H00";
WARNING_IMPLICIT_ZERO_BIT_PADDING:
constant SQLSTATE_TYPE := "01008";
WARNING_NULL_VALUE_ELIMINATED_IN_SET_FUNCTION:
constant SQLSTATE_TYPE := "01003";
WARNING_PRIVILEGE_NOT_GRANTED:
constant SQLSTATE_TYPE := "01007";
WARNING_PRIVILEGE_NOT_REVOKED:
constant SQLSTATE_TYPE := "01006";
WARNING_SEARCH_CONDITION_TOO_LONG_FOR_
INFORMATION_SCHEMA:
constant SQLSTATE_TYPE := "01009";
WARNING_STRING_DATA_RIGHT_TRUNCATION_WARNING:
constant SQLSTATE_TYPE := "01004";
WARNING_QUERY_EXPRESSION_TOO_LONG_FOR_
INFORMATION_SCHEMA:
constant SQLSTATE_TYPE := "0100A";
WITH_CHECK_OPTION_VIOLATION_NO_SUBCLASS:
constant SQLSTATE_TYPE := "44000";
end SQLSTATE_CODES;
end SQL_STANDARD;
Persistent stored modules 57
ISO/IEC JTC1/SC21 N8897
11.3 <routine>
where csp is an implementation-defined package and cst is
an implementation-defined character type such that within
the scope of an Ada use clause for
SQL_STANDARD.CHARACTER_SET, string literals can be of type
SQL_STANDARD.CHAR. bs, ts, bi, ti, dr, dd, bsc, and tsc
are implementation-defined integer values. t is INT or
SMALLINT, corresponding with an implementation-defined
<exact numeric type> of indicator parameters.
SQL_STANDARD shall contain no other declarations.
8) If the <routine> is a function, then <parameter mode> shall not
be specified.
9) If a <routine> is an SQL routine, then:
a) Each <parameter declaration> in the <parameter list> shall
contain a <parameter name>.
b) Whether a <parameter declaration> is for an input parameter,
an output parameter, or both is determined as follows:
Case:
i) A <status parameter> is an output parameter.
ii) For every <parameter declaration> that is not a <status
parameter> and for which a <parameter mode> is not
specified, if the <parameter declaration> is contained
in a <routine> that is a function, then the parameter is an
input parameter; otherwise,
Case:
1) If the <parameter name> of a parameter is contained in a
<value specification> or a <simple value specification>
that is contained in <SQL procedure statement>, but
it is not contained in a <target specification> or a
<simple target specification> that is contained in <SQL
procedure statement>, then the parameter is an input
parameter.
2) If the <parameter name> of a parameter is contained
in a <target specification> or a <simple target
specification> that is contained in <SQL procedure
statement>, but it is not contained in a <value
specification> or a <simple value specification> that
is contained in <SQL procedure statement>, then the
parameter is an output parameter.
3) If the <parameter name> of a parameter is contained in a
<value specification> or a <simple value specification>
that is contained in <SQL procedure statement> and
it is contained in a <target specification> or a
<simple target specification> that is contained in <SQL
58 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
11.3 <routine>
procedure statement>, then the parameter is both an
input parameter and an output parameter.
4) Otherwise, the parameter is neither an input parameter
nor an output parameter.
iii) For every <parameter declaration> for which a <parameter
mode> is specified,
Case:
1) If the <parameter mode> is IN, then the parameter
is an input parameter. The <parameter name> shall
not be contained in a <target specification> or a
<simple target specification> that is contained in <SQL
procedure statement>.
2) If the <parameter mode> is OUT, then the parameter is
an output parameter. The <parameter name> shall not
be contained in a <value specification> or a <simple
value specification> that is contained in <SQL procedure
statement>.
3) If the <parameter mode> is INOUT, then the parameter is
both an input parameter and an output parameter.
10)If a <routine> is an external routine, then:
a) The <parameter list> shall not contain a <status parameter>.
b) The implicit or explicit <caller language clause> shall
specify SQL.
c) If an <external routine name> is not specified, then the
<qualified identifier> of the <routine name> is implicit.
d) If <parameter style> is not specified, then PARAMETER STYLE
SQL is implicit.
e) If the <returns data type> is an abstract data type, then
a <parameter style> of PARAMETER STYLE GENERAL shall not be
specified.
f) If a <variant attribute> is not specified, then NOT VARIANT
is implicit.
g) The <external routine language clause> shall not specify SQL.
h) If a <result cast> is specified, then let R be some value
of the <data type> specified in the <result cast> and let RT
be the <returns data type>. The following shall be valid
according to the Syntax Rules of Subclause 6.10, "<cast
specification>", in ISO/IEC 9075:1992.
CAST ( R AS RT )
Persistent stored modules 59
ISO/IEC JTC1/SC21 N8897
11.3 <routine>
i) If <parameter mode> is not specified, then IN is implicit.
j) If <parameter mode> is IN or INOUT, then the parameter is
an input parameter. If the <parameter mode> is OUT or INOUT,
then the parameter is an output parameter.
k) If PARAMETER STYLE SQL is specified, then let N be the
number of <parameter declaration>s. Let PN be the number
of <parameter declaration>s that are predefined types. Let UN
be the number of actual base types of the operand data types
that are abstract data types. If the <returns data type> is
an abstract data type, then let RN be the number of actual
base types of that data type; otherwise, let RN be 1. Let the
effective parameter type list be a list of PN + UN + RN + N + 5
data types, as follows:
i) Effective parameter type list entries 1 to PN + UN are N
groups of entries. For i ranging from 1 to N,
Case:
1) If the i-th <parameter declaration> is a predefined
type, then the i-th group of effective parameter
type list entries consists of the i-th <parameter
declaration>.
2) If the i-th <parameter declaration> is some abstract
data type ADTi, then the i-th group of effective
parameter type list entries consists of the <parameter
mode> of the <parameter declaration> and the actual base
types of the <abstract data type definition> of ADTi.
ii) Effective parameter type list entries (PN + UN) + 1 to
(PN+UN)+RN are as follows:
Case:
1) If the <returns data type> is a predefined type, then
effective parameter list entry (PN + UN) + 1 is the
<parameter mode> OUT and the <returns data type>.
2) If the <returns data type> is some abstract data type
ADTr, then effective parameter list entries (PN+UN)+1 to
(PN+UN)+RN consist of the <parameter mode> OUT and the
actual base types of the <abstract data type definition>
of ADTr.
iii) Effective external parameter list entries (PN+UN+RN)+1 to
(PN+UN+RN)+N+1 are N+1 occurrences of an implementor-
defined <data type> that is an exact numeric type with
scale 0. The <parameter mode> for the i-th such effective
parameter is the same as that of the i - RN - PN - UN-th
effective parameter.
60 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
11.3 <routine>
iv) Effective parameter type list entry (PN + UN + RN + N + 1) +
is a <data type> that is character string of length 5 and
character set SQL_TEXT with <parameter mode> INOUT. This
parameter is used to pass the exception data item.
v) Effective parameter type list entry (PN+UN+RN+N+1)+2 is
a <data type> that is character string of implementation-
defined length and character set SQL_TEXT with <parameter
mode> IN. This parameter is used to pass the name of the
routine being invoked.
vi) Effective parameter type list entry (PN+UN+RN+N+1)+3 is
a <data type> that is character string of implementation-
defined length and character set SQL_TEXT with <parameter
mode> IN. This parameter is used to pass the specific name
of the routine being invoked.
vii) Effective parameter type list entry (PN+UN+RN+N+1)+4 is
a <data type> that is character string of implementation-
defined length and character set SQL_TEXT with <parameter
mode> INOUT. This parameter is used to pass the message
text item.
l) If PARAMETER STYLE GENERAL is specified, then let N be the
number of <parameter declaration>s. Let PN be the number
of <parameter declaration>s that are pre-defined types.
Let UN be the number of actual base types of the operand
data types that are abstract data types. Let the effective
external parameter type list be a list of PN + UN data types,
as follows:
i) Effective parameter type list entries 1 to PN + UN are N
groups of entries. For I ranging from 1 to N,
Case:
1) If the i-th <external parameter> is a predefined type,
then the i-th group of effective parameter type list
entries consists of the i-th <external parameter>.
2) If the i-th <external parameter> is an abstract data
type ADT, then the i-th group of effective parameter
type list entries consists of the <parameter mode> of
the <external parameter> and the actual base types of
the <abstract data type definition> of ADTi.
ii) If the external routine is a function, then the external
routine will have an effective return type of <returns data
type>.
m) Depending on whether the <caller language clause> specifies
ADA, C, COBOL, FORTRAN, MUMPS, PASCAL, or PLI, let the
operative data type correspondences table be Table 1,
"Data type correspondences for Ada", Table 2, "Data type
correspondences for C", Table 3, "Data type correspondences
Persistent stored modules 61
ISO/IEC JTC1/SC21 N8897
11.3 <routine>
for COBOL", Table 4, "Data type correspondences for Fortran",
Table 5, "Data type correspondences for MUMPS", Table 6,
"Data type correspondences for Pascal", or Table 7, "Data
type correspondences for PL/I", respectively. Refer to
the two columns of the operative data type correspondences
table as the "SQL data type" column and the "host data type
column".
n) If the <routine> is a function, then if the <routine>
contains a <result cast>, let the externally returned data
type be the <data type> contained in that <result cast>;
otherwise, let the externally returned data type be the <data
type> contained in the <returns clause>.
o) Any <data type> in a <parameter declaration> or externally
returned data type (if any) shall specify a data type listed
in the SQL data type column for which the corresponding row
in the host data type column is not "none". If the <data
type> in a <parameter declaration> or externally returned
data type (if any) specifies the data type listed in the i-th
row of the SQL data type column, then the type of the i-th
parameter or the externally returned data type shall be the
type listed in the i-th row of the host data type column.
11)A <routine> that is an SQL-invoked routine shall not contain an
<SQL routine body> that is an <SQL connection statement>.
12)If <routine> is not immediately contained in a <module
contents>, then <routine header terminator> shall not be
specified.
13)If a <procedure type> is specified, then:
a) The routine is a handler procedure.
b) The routine shall be an SQL-invoked routine.
c) The routine shall not generally contain an <SQL schema
statement>, an <SQL transaction statement>, an
d) SQL connection statement>, or an <SQL session statement>.
Access Rules
1) If an external routine is contained in a <module>, then the
<user authorization identifier> of the current SQL-session shall
be equal to the <user authorization identifier> that owns the
schema identified by the implicit or explicit <schema name> of
the <routine>.
62 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
11.3 <routine>
General Rules
1) If the <routine> is an external routine, then a privilege
descriptor is created that defines the EXTERNAL PRIVILEGES
privilege on the <routine> to the <authorization identifier>
of the schema or <module> in which the <routine> appears. This
privilege is grantable. The grantor for the privilege descriptor
is set to the special grantor value "_SYSTEM".
2) If the <routine> is an external routine, then the method and
time of binding of that external routine to the schema or
<module> containing the <routine> is implementation-defined. If
the designated external routine is not a program that conforms
to the programming language standard specified by the <external
routine language clause>, then the results are implementation-
dependent.
3) Case:
a) If a <routine> is an SQL-invoked routine, then the rules for
invocation of the <routine> are specified in Subclause 9.1,
"<routine invocation>".
b) If a <routine> is an externally-invoked routine, then the
rules for invocation of the <routine> are specified in
Subclause 12.3, "<procedure>", in ISO/IEC 9075:1992.
4) A routine descriptor is created that describes the routine being
defined:
a) The routine name included in the routine descriptor is
<routine name>.
b) If the routine is an SQL-invoked routine, then the routine
descriptor includes the specific name.
c) The routine descriptor includes, for each parameter in
<parameter list>, the name, data type, position, and an
indication of whether the parameter is input, output, both,
or neither.
d) If the routine is a function, then the routine descriptor
includes the data type in the <returns data type>.
e) The name of the caller language of the routine is the
<language name> in the <language clause> directly contained
in <caller language clause>.
f) If the routine is an external routine, then the external name
of the routine is <external routine name>.
g) If the routine is an external routine, then the name of the
language in which the body of the routine was written is the
<language name> in the <language clause> directly contained
in <external routine language clause>.
Persistent stored modules 63
ISO/IEC JTC1/SC21 N8897
11.3 <routine>
h) If the routine is an external routine, then whether the
parameter passing style is PARAMETER STYLE SQL or PARAMETER
STYLE GENERAL.
i) If the routine is an external routine, then the routine
descriptor includes an indication of whether the routine is
VARIANT or NOT VARIANT.
5) If a <routine> is a handler procedure, then:
a) Let C be the <compound statement. from which the handler
procedure was invoked.
b) If REDO is specified and if the handler procedure was not
invoked from a <compound statement> that specifies ATOMIC,
then:
i) An exception condition is raised: handler exception-cannot
redo a non-atomic compound statement.
ii) An implicit <resignal statement> is executed to resolve the
active condition that caused the handler procedure to be
called.
c) If UNDO is specified and if the handler procedure was not
invoked from a <compound statement> that specifies ATOMIC,
then:
i) An exception condition is raised: handler exception-cannot
undo a non-atomic compound statement.
ii) An implicit <resignal statement> is executed to resolve the
active condition that caused the handler procedure to be
called.
d) If REDO is specified, then:
i) All changes made to SQL-data or schemas by the execution of
every SQL-statement contained in the <SQL statement list>
of C and any <SQL procedure statement>s triggered by the
execution of any such statement are cancelled.
ii) The active condition that caused the handler procedure to
be invoked is deactivated.
iii) All of the SQL-statements contained in the handler routine
are executed.
iv) Case:
1) If there is no active condition, then control is
returned to the beginning of C.
2) Otherwise, an implicit <resignal statement> is executed.
64 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
11.3 <routine>
e) If UNDO is specified, then:
i) All changes made to SQL-data or schemas by the execution of
every SQL-statement contained in the <SQL statement list>
of C and any <SQL procedure statement>s triggered by the
execution of any such statement are cancelled.
ii) The active condition that caused the handler procedure to
be invoked is deactivated.
iii) All of the SQL-statements contained in the handler routine
are executed.
iv) Case:
1) If there is no active condition, then control is
returned to the end of C.
2) Otherwise, an implicit <resignal statement> is executed.
f) If neither UNDO nor REDO is specified, then:
i) The active condition that caused the handler procedure to
be invoked is deactivated.
ii) All of the SQL-statements contained in the handler routine
are executed.
iii) Case:
1) If there is no active condition, then control is
returned to the SQL-statement following the one that
raised the condition in C.
2) Otherwise, an implicit <resignal statement> is executed.
Persistent stored modules 65
ISO/IEC JTC1/SC21 N8897
11.4 <SQL procedure statement>
11.4 <SQL procedure statement>
Function
Define all of the SQL-statements that are <SQL procedure
statement>s.
Format
<SQL executable statement> ::=
!! All alternatives from ISO/IEC 9075:1992
| <SQL control statement>
<SQL schema definition statement> ::=
!! All alternatives from ISO/IEC 9075:1992
| <SQL-server module definition>
| <routine>
<SQL schema manipulation statement> ::=
!! All alternatives from ISO/IEC 9075:1992
| <alter module statement>
| <drop module statement>
| <drop routine statement>
<SQL control statement> ::=
<call statement>
| <return statement>
| <assignment statement>
| <compound statement>
| <case statement>
| <if statement>
| <leave statement>
| <loop statement>
| <for statement>
<SQL diagnostics statement> ::=
!! All alternatives from ISO/IEC 9075:1992
| <signal statement>
| <resignal statement>
| <undo statement>
| <redo statement>
Syntax Rules
1) An <SQL connection statement> shall not be contained in an <SQL
control statement>.
Access Rules
No additional Access Rules.
66 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
11.4 <SQL procedure statement>
General Rules
1) An atomic execution context is active during execution of an
<SQL procedure statement> that is not a <SQL control statement>.
2) If <SQL procedure statement> is not a <compound statement>
and there is an active exception condition at the completion
of an <SQL procedure statement> S, raised either by S or any
triggered <SQL procedure statement>, then:
a) One of the exception conditions is the active condition.
b) All changes made to SQL-data or schemas by the execution of S
and any <SQL procedure statement>s triggered by the execution
of S are canceled.
c) Diagnostics information resulting from the execution of S is
placed into the diagnostics area as specified in Subclause
18.1, "<get diagnostics statement>".
Persistent stored modules 67
ISO/IEC JTC1/SC21 N8897
11.5 Data type correspondences
11.5 Data type correspondences
Function
Specify the data type correspondences for SQL data types and host
language types.
Note: These tables are referenced in Subclause 11.3, "<routine>",
for the definitions of externally-invoked routines and external
routines.
In the following tables, let P be <precision>, S be <scale>, L
be <length>, T be <time fractional seconds precision>, and Q be
<interval qualifier>.
Tables
_____________Table_1-Data_type_correspondences_for_Ada_____________
_SQL_Data_Type__________Ada_Data_Type______________________________
| SQLSTATE | SQL_STANDARD.SQLSTATE_TYPE |
| | |
| SQLCODE | SQL_STANDARD.SQLCODE_TYPE |
| | |
| CHARACTER (L) | SQL_STANDARD.CHAR, with P'LENGTH of L |
| | |
| CHARACTER VARYING | None |
(L)
| BIT (L) | SQL_STANDARD.BIT, with P'LENGTH of L |
| | |
| BIT VARYING (L) | None |
| | |
| SMALLINT | SQL_STANDARD.SMALLINT |
| | |
| INTEGER | SQL_STANDARD.INT |
| | |
| DECIMAL(P,S) | None |
| | |
| NUMERIC(P,S) | None |
| | |
| REAL | SQL_STANDARD.REAL |
| | |
| DOUBLE PRECISION | SQL_STANDARD.DOUBLE_PRECISION |
| | |
| FLOAT(P) | None |
| | |
| DATE | None |
| | |
| TIME(T) | None |
| | |
| TIMESTAMP(T) | None |
| | |
|_INTERVAL(Q)__________|_None______________________________________|
| | |
68 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
11.5 Data type correspondences
______________Table_2-Data_type_correspondences_for_C______________
_SQL_Data_Type__________C_Data_Type________________________________
| SQLCODE | pointer to long |
| | |
| SQLSTATE | char, with length 6 |
| | |
| CHARACTER (L) | char, with length L+k[2] |
| | |
| CHARACTER VARYING | char, with length L+k[2] |
(L)
| BIT (L) | char, with length X[1] |
| | |
| BIT VARYING (L) | None |
| | |
| SMALLINT | pointer to short |
| | |
| INTEGER | pointer to long |
| | |
| DECIMAL(P,S) | None |
| | |
| NUMERIC(P,S) | None |
| | |
| REAL | pointer to float |
| | |
| DOUBLE PRECISION | pointer to double |
| | |
| FLOAT(P) | None |
| | |
| DATE | None |
| | |
| TIME(T) | None |
| | |
| TIMESTAMP(T) | None |
| | |
|_INTERVAL(Q)__________|_None______________________________________|
| | |
| [1]The length X of th| character data type corresponding with |
SQL data type BIT(L) is the smallest integer not less than the
quotient of the division L/B, where B is the implementation-
defined number of bits contained in character of the host
language.
| |
| [2]k is the length in units of C char of the largest character |
| in the character set associated with the SQL data type. |
|__________________________________________________________________|
| |
| |
| |
Persistent stored modules 69
ISO/IEC JTC1/SC21 N8897
11.5 Data type correspondences
____________Table_3-Data_type_correspondences_for_COBOL____________
_SQL_Data_Type__________COBOL_Data_Type____________________________
| SQLSTATE | PICTURE X(5) |
| | |
| SQLCODE | PICTURE S9(PC) USAGE COMPUTATIONAL, where |
PC is implementation-defined between 4
and 18, inclusive
| CHARACTER (L) | alphanumeric, with length L |
| | |
| CHARACTER VARYING | None |
| (L) | |
| BIT (L) | alphanumeric, with length X[1] |
| | |
| BIT VARYING (L) | None |
| | |
| SMALLINT | PICTURE S9(SPI) USAGE BINARY, where SPI |
is implementation-defined
| INTEGER | PICTURE S9(PI) USAGE BINARY, where PI is |
| | implementation-defined |
| | |
| DECIMAL(P,S) | None |
| | |
| NUMERIC(P,S) | USAGE DISPLAY SIGN LEADING SEPARATE, with |
PICTURE as specified[2]
| REAL | None |
| | |
| DOUBLE PRECISION | None |
| | |
| FLOAT(P) | None |
| | |
| DATE | None |
| | |
| TIME(T) | None |
| | |
| TIMESTAMP(T) | None |
| | |
|_INTERVAL(Q)__________|_None______________________________________|
| | |
| [1]The length of a ch|racter type corresponding with SQL BIT(L) |
is one more than the smallest integer not less than the quotient
of the division L/B, where B is the implementation-defined
number of bits contained in one character of the host language.
| |
| [2]Case: |
| |
| aIf S=P, then a PICTURE with an 'S' followed by a 'V' followed |
| by P '9's. |
| bIf P>S>0, then a PICTURE with an 'S' followed by P-S '9's |
followed by a 'V' followed by S '9's.
cIf S=0, then a PICTURE with an 'S' followed by P '9's
___optionally_followed_by_a_'V'.___________________________________
| |
70 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
11.5 Data type correspondences
___________Table_4-Data_type_correspondences_for_Fortran___________
_SQL_Data_Type__________Fortran_Data_Type__________________________
| SQLSTATE | CHARACTER, with length 5 |
| | |
| SQLCODE | INTEGER |
| | |
| CHARACTER (L) | CHARACTER, with length L |
| | |
| CHARACTER VARYING | None |
(L)
| BIT (L) | CHARACTER, with length X[1] |
| | |
| BIT VARYING (L) | None |
| | |
| SMALLINT | None |
| | |
| INTEGER | INTEGER |
| | |
| DECIMAL(P,S) | None |
| | |
| NUMERIC(P,S) | None |
| | |
| REAL | REAL |
| | |
| DOUBLE PRECISION | DOUBLE PRECISION |
| | |
| FLOAT(P) | None |
| | |
| DATE | None |
| | |
| TIME(T) | None |
| | |
| TIMESTAMP(T) | None |
| | |
|_INTERVAL(Q)__________|_None______________________________________|
| | |
| [1]The length X of th| character data type corresponding with |
SQL data type BIT(L) is the smallest integer not less than the
quotient of the division L/B, where B is the implementation-
defined number of bits contained in character of the host
language.
|__________________________________________________________________|
| |
| |
| |
| |
| |
Persistent stored modules 71
ISO/IEC JTC1/SC21 N8897
11.5 Data type correspondences
____________Table_5-Data_type_correspondences_for_MUMPS____________
_SQL_Data_Type__________MUMPS_Data_Type____________________________
| SQLSTATE | character, with maximum length at least 5 |
| | |
| SQLCODE | None |
| | |
| CHARACTER (L) | None |
| | |
| CHARACTER VARYING | character with maximum length L |
(L)
| BIT (L) | None |
| | |
| BIT VARYING (L) | None |
| | |
| SMALLINT | None |
| | |
| INTEGER | character |
| | |
| DECIMAL(P,S) | character |
| | |
| NUMERIC(P,S) | character |
| | |
| REAL | character |
| | |
| DOUBLE PRECISION | None |
| | |
| FLOAT(P) | None |
| | |
| DATE | None |
| | |
| TIME(T) | None |
| | |
| TIMESTAMP(T) | None |
| | |
|_INTERVAL(Q)__________|_None______________________________________|
| | |
| | |
72 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
11.5 Data type correspondences
____________Table_6-Data_type_correspondences_for_Pascal___________
_SQL_Data_Type__________Pascal_Data_Type___________________________
| SQLSTATE | PACKED ARRAY[1..5] OF CHAR |
| | |
| SQLCODE | INTEGER |
| | |
| CHARACTER(1) | CHAR |
| | |
| CHARACTER (L), L>1 | PACKED ARRAY[1..L] OF CHAR |
| | |
| CHARACTER VARYING | None |
(L)
| BIT (L), 1
| B[1] | |
| | |
| BIT (L), B[1] < L | PACKED ARRAY[LB[1]] OF CHAR |
| | |
| BIT VARYING (L) | None |
| | |
| SMALLINT | None |
| | |
| INTEGER | INTEGER |
| | |
| DECIMAL(P,S) | None |
| | |
| NUMERIC(P,S) | None |
| | |
| REAL | REAL |
| | |
| DOUBLE PRECISION | None |
| | |
| FLOAT(P) | None |
| | |
| DATE | None |
| | |
| TIME(T) | None |
| | |
| TIMESTAMP(T) | None |
| | |
|_INTERVAL(Q)__________|_None______________________________________|
| | |
| [1]The length LB of t|e character data type corresponding with |
SQL data type BIT(L) is the smallest integer not less than the
quotient of the division L/B, where B is the implementation-
defined number of bits contained in a character of the host
language.
|__________________________________________________________________|
| |
| |
| |
| |
Persistent stored modules 73
ISO/IEC JTC1/SC21 N8897
11.5 Data type correspondences
_____________Table_7-Data_type_correspondences_for_PL/I____________
_SQL_Data_Type__________PL/I_Data_Type_____________________________
| SQLSTATE | CHARACTER(5) |
| | |
| SQLCODE | FIXED BINARY(PP), where PP is an |
implementation-defined precision at least
15.
| CHARACTER (L) | CHARACTER(L) |
| | |
| CHARACTER VARYING | CHARACTER VARYING(L) |
| (L) | |
| BIT (L) | BIT(L) |
| | |
| BIT VARYING (L) | BIT VARYING (L) |
| | |
| SMALLINT | FIXED BINARY(SPI), where SPI is |
implementation-defined
| INTEGER | FIXED BINARY(PI), where PI is |
| | implementation-defined |
| | |
| DECIMAL(P,S) | FIXED DECIMAL(P,S) |
| | |
| NUMERIC(P,S) | None |
| | |
| REAL | None |
| | |
| DOUBLE PRECISION | None |
| | |
| FLOAT(P) | FLOAT BINARY (P) |
| | |
| DATE | None |
| | |
| TIME(T) | None |
| | |
| TIMESTAMP(T) | None |
| | |
|_INTERVAL(Q)__________|_None______________________________________|
| | |
| | |
74 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
12 Persistent SQL module maintenance
12.1 Introduction
<To be supplied.>
12.2 <SQL-server module definition>
Function
Define an SQL-server module.
Format
<SQL-server module definition> ::=
CREATE <module>
Syntax Rules
1) The <module> shall simply contain a <module name clause> that
specifies an <SQL-server module name>.
2) Case:
a) If an <SQL-server module definition> is contained in a
<schema definition> SD with no intervening <module> and the
<module name> of the <SQL-server module definition> contains
a <schema name>, then that <schema name> shall be the same as
the specified or implicit <schema name> of SD.
b) If an <SQL-server module definition> is contained in a
<module> M with no intervening <schema definition> and the
<module name> of the <SQL-server module definition> contains
a <schema name>, then that <schema name> shall be the same as
the specified or implicit <schema name> of M.
3) The schema identified by the explicit or implicit <schema name>
of the <SQL-server module name> shall not include a module
descriptor whose <module name> is equal to the <SQL-server
module name> of the containing <SQL-server module definition>.
4) The <language clause> of the <module> simply contained in an
<SQL-server module definition> shall specify SQL.
Persistent SQL module maintenance 75
ISO/IEC JTC1/SC21 N8897
12.2 <SQL-server module definition>
Access Rules
1) If an <SQL-server module definition> is contained in a
<module> M with no intervening <schema definition>, then the
<authorization identifier> of the current SQL-session shall be
equal to the <authorization identifier> that owns the schema
identified by the implicit or explicit <schema name> of the
<module name> of M.
General Rules
1) An <SQL-server module definition> defines an SQL-server module.
2) A privilege descriptor is created that defines the EXECUTE
privilege on this module to the <authorization identifier> of
the innermost <schema> or <module> that contains the <SQL-server
module definition>. This privilege is grantable. The grantor of
the privilege descriptor is set to the special grantor value "_
SYSTEM".
76 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
12.3 <alter module statement>
12.3 <alter module statement>
Function
Alter a persistent module.
Format
<alter module statement> ::=
ALTER MODULE <module name> <alter module action>...
<alter module action> ::=
ADD <module contents>
| ALTER <module contents designator> <module contents>
| DROP <module contents designator>
<module contents designator> ::=
| CURSOR <cursor name>
| PROCEDURE <routine name> [ <parameter list> ]
| FUNCTION <routine name> [ <parameter list> ]
| TABLE <table name>
| VIEW <table name>
| EXCEPTION <exception name>
Syntax Rules
1) Let M be the module identified by <module name> and let MN be
the <module name>.
2) Let R be the module contents identified by <module contents
designator> and let RN be the <cursor name>, <routine name>,
<table name>, or <exception name> of that module contents.
3) If R is a routine belonging to a routine family, then <parameter
list> shall be specified. A unique routine in that routine
family shall be identified.
Access Rules
1) The <authorization identifier> of the current SQL-session shall
be equal to the <authorization identifier> that owns the schema
identified by the <schema name> of the module identified by MN.
General Rules
1) If the <alter module action> contains ADD, then the <module
contents> is added at the end of the text of the <module
definition> in the descriptor of M.
2) If the <alter module action> contains ALTER, then the module
contents R replaced in the descriptor of M by the new <module
contents> specified.
Persistent SQL module maintenance 77
ISO/IEC JTC1/SC21 N8897
12.3 <alter module statement>
3) If the <alter module action> contains DROP, then the module
contents_R_is_dropped_from_the_descriptor_of_M._____________________
**Editor's Note**
The ALTER MODULE statement introduced by YOK-190 doesn't specify
the effect when a DROP leaves the module in an invalid state. See
Possible_Problem_<PSM-001>_in_the_Editor's_Notes.___________________
|___________________________________________________________________|
| **Editor's Note** |
|The ALTER MODULE statement doesn't specify consistency requriements|
|for the ALTER suboption. See Possible Problem <PSM-002> in the |
Editor's_Notes._____________________________________________________
| |
| |
| |
| |
78 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
12.4 <drop module statement>
12.4 <drop module statement>
Function
Destroy a module.
Format
<drop module statement> ::=
DROP MODULE <module name> <drop behavior>
Syntax Rules
1) Let M be the module identified by <module name> and let MN be
the <module name>.
Access Rules
1) The <authorization identifier> of the current SQL-session shall
be equal to the <authorization identifier> that owns the schema
identified by the <schema name> of the module identified by MN.
General Rules
1) Let A be the current <authorization identifier>. The following
<revoke statement> is effectively executed with a current
<authorization identifier> of "_SYSTEM" and without further
Access Rule checking:
REVOKE EXECUTE ON MODULE M FROM A
2) The descriptor of M is destroyed.
Persistent SQL module maintenance 79
ISO/IEC JTC1/SC21 N8897
80 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
13 Data manipulation
13.1 <declare cursor>
Function
Define a cursor.
Format
No additional Format items.
Syntax Rules
1) If a <declare cursor> is contained in a <module> M and is not
contained in a <compound statement>, then:
a) For each <open statement> O in the <module> that specifies
the <cursor name> declared in the <declare cursor>, any
<item reference> contained in the <cursor specification>
shall be defined in either an <SQL variable declaration> of
a <compound statement> that contains O or in a <parameter
declaration> in the <routine> that contains O.
b) For each <item reference> in the <declare cursor> the data
types of the variables and parameters associated with each of
the <open statement>s that specify the <cursor name> shall be
the same.
Note: See the Syntax Rules of Subclause 11.2, "<module>".
Access Rules
No additional Access Rules.
General Rules
No additional General Rules.
Data manipulation 81
ISO/IEC JTC1/SC21 N8897
13.2 <temporary table declaration>
13.2 <temporary table declaration>
Function
Declare a declared local temporary table.
Format
No additional Format items.
Syntax Rules
1) Let TN be the <table name> of a <temporary table declaration>
TTD, and let T be the <qualified identifier> of TN.
Case:
a) If TN contains a <local or schema qualifier> LSQ and TTD is
contained in a <compound statement>, then LSQ shall be the
same as the <beginning label> of the innermost <compound
statement> that contains TTD.
b) If TN does not contain a <local or schema qualifier> and TTD
is contained in a <compound statement>, then the <beginning
label> of the innermost <compound statement> that contains
TTD is implicit.
2) If a <temporary table declaration> is contained in a <module> M
with no intervening <compound statement>, then
a) If TN contains a <local or schema qualifier>, then it shall
be "MODULE".
b) The <qualified identifier> of TN shall be different from
the <qualified identifier> of the <table name> of any
other <temporary table declaration> or <temporary view
declaration> that is contained in M with no intervening
<compound statement>.
Access Rules
No additional Access Rules.
General Rules
No additional General Rules.
82 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
14 Control statements
14.1 Introduction
<To be supplied.>
14.2 <call statement>
Function
Invoke a procedure.
Format
<call statement> ::=
CALL <routine invocation>
Syntax Rules
1) A <call statement> shall contain a <routine invocation> whose
candidate <routine>s are procedures.
Note: The candidate <routine>s of a <routine invocation> are
defined in Subclause 9.1, "<routine invocation>".
Access Rules
1) The applicable privileges shall include the EXECUTE privilege on
the <routine> identified by <routine name>.
General Rules
1) The <routine invocation> is executed.
Control statements 83
ISO/IEC JTC1/SC21 N8897
14.3 <return statement>
14.3 <return statement>
Function
Return a value from an SQL function.
Format
<return statement> ::=
RETURN <return value>
<return value> ::=
<value expression>
Syntax Rules
1) <return statement> shall be contained in an <SQL routine body>,
and the <routine> shall be an SQL routine that is a function.
2) A <return statement> shall be contained in a <routine> that is
an SQL function. Let F be that <routine>.
3) The data type of the <value expression> shall be assignable to
an item of the data type specified with RETURNS in F.
Access Rules
None.
General Rules
1) The return value is set to the value of the <value expression>.
84 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
14.4 <compound statement>
14.4 <compound statement>
Function
Specify a statement that groups other statements together.
Format
<compound statement> ::=
[ <beginning label> <colon> ]
BEGIN [ [ NOT ] ATOMIC ]
[ <local declaration list> ]
[ <SQL statement list> ]
1 option deleted
END [ <ending label> ]
<beginning label> ::= <statement label>
<ending label> ::= <statement label>
<statement label> ::= <identifier>
<SQL statement list> ::= <terminated SQL statement>...
<local declaration list> ::= <terminated local declaration>...
<terminated local declaration> ::= <local declaration> <semicolon>
<local declaration> ::=
<SQL variable declaration>
| <routine>
| <declare cursor>
| <temporary table declaration>
| <temporary view declaration>
| <exception declaration>
| <handler declaration>
<terminated SQL statement> ::=
<SQL procedure statement> <semicolon>
Syntax Rules
1) If ATOMIC is specified, then the <SQL procedure statement>
shall not contain a <commit statement>, nor shall it contain a
<rollback statement> that does not specify a <savepoint clause>.
2) If a <compound statement> has a <beginning label>, it is called
a labeled <compound statement>.
3) If an <ending label> is specified, then an identical <beginning
label> shall be specified.
Control statements 85
ISO/IEC JTC1/SC21 N8897
14.4 <compound statement>
4) If a <beginning label> is specified, then it shall be different
from all other <statement label>s immediately contained in a
<compound statement>, a <loop statement>, or a <for statement>
in the containing <routine>.
5) If neither ATOMIC nor NOT ATOMIC is specified, then NOT ATOMIC
is implicit.
1 Rule deleted
6) If an explicit <beginning label> is not specified and the
<compound statement> is the outermost <statement> of a <routine>
then the <procedure name> of that <routine> is the implicit
<beginning label>.
7) The scope of the <statement label> is the <compound statement>.
Note: The "declared local name" of an <SQL variable declaration>
VD, a <routine> R, a <declare cursor> DC, a <temporary abstract
data type declaration> TADTD, a <temporary table declaration>
TTD, a <temporary view declaration> TVD, or an <exception
declaration> ED, in a <local declaration list> is the <SQL
variable name>, <routine name>, <cursor name>, <abstract data
type name>, or <table name> immediately contained in VD, R, DC,
TADTD, TTD, TVD, or ED.
8) If two <local declaration>s in a <local declaration list>
have the same declared local name, then both of those <local
declaration>s shall be <routine>s.
9) If a <compound statement> CS is simply contained in a <routine>,
then no <local declaration> in the <local declaration list>
of CS shall have a declared local name that is equal to
the <parameter name> of any <parameter declaration> in the
<parameter declaration list> of the <routine>.
Access Rules
None.
General Rules
1) An atomic execution context is active during the execution of a
<compound statement> that specifies ATOMIC. When this statement
completes, all savepoints that have been established during its
execution are destroyed.
2) The variables, cursors, routines, and temporary tables and view
specified in the <local declaration list> are created in an
implementation-dependent order.
3) If an <SQL variable declaration> specifies <default clause>,
then the value of that variable is set to the default value
according to the General Rules of Subclause 11.5, "<default
86 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
14.4 <compound statement>
clause>" in ISO/IEC 9075:1992; otherwise, the value of the
variable is implementation-dependent.
4) The <SQL procedure statement>s of the <SQL statement list> are
executed in the order in which they are specified.
5) If there is an active exception or completion condition E at the
completion of any of these <SQL procedure statement>s, then:
a) Execution of all of the <SQL procedure statement>s is
terminated immediately. If a handler procedure has been
associated with E, then control is passed to the handler
procedure associated with E in the closest encompassing
scope.______________________________________________________________
**Editor's Note**
The term "closest encompassing scope" has not been defined. See
_Possible_Problem_<PSM-006>_in_the_Editor's_Notes.__________________
| |
b| If ATOMIC has been specified and the handler procedure |
|completes with an active exception condition, then all |
changes made to SQL-data or schemas by the execution of every
SQL-statement contained in the <SQL statement list> and any
<SQL procedure statement>s triggered by the execution of any
such statement are canceled.
6) The variables, cursors, routines, temporary tables, and
temporary views specified in <local declaration list> are
destroyed.
Control statements 87
ISO/IEC JTC1/SC21 N8897
14.5 <handler declaration>
14.5 <handler declaration>
Function
Associate a handler procedure with an exception or completion
condition for a module or compound statement.
Format
<handler declaration> ::=
DECLARE <specific routine designator> HANDLER
FOR <exception value list>
<exception value list> ::=
<exception value> [ { <comma> <exception value> }... ]
<exception value> ::=
SQLSTATE [ VALUE ] <sqlstate list>
| <exception name>
| SQLEXCEPTION
| SQLWARNING
| NOT FOUND
<sqlstate list> ::=
<character string literal> [ { <comma> <character string literal> }... ]
Syntax Rules
1) If the <handler declaration> is contained in a <module> or
<compound statement> M with no intervening <compound statement>,
then any <exception value> shall be different from thew
<exception value> of any other <handler declaration> contained
in M with no intervening <compound statement>.
2) Let R be the routine identified by <specific routine
designator>.
3) The <character string literal> specified in an <exception
value> shall be an SQLSTATE value that conforms to the rules
of Subclause 22.1, "SQLSTATE", in ISO/IEC 9075:1992.
4) The <exception name> specified in an <exception value> shall
be defined by an <exception declaration> in the scope of this
declaration or in the same <local declaration list>.
5) SQLEXCEPTION, SQLWARNING, and NOT FOUND correspond to
SQLSTATE class values corresponding to categories X, W, and N,
respectively, in Table 23, "SQLSTATE class and subclass values",
in ISO/IEC 9075:1992.
88 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
14.5 <handler declaration>
Access Rules
None.
General Rules
1) Case:
a) If the <handler declaration> is contained in a <module> M
with no intervening <compound statement>, then its scope is
all <routine>s that are <module element>s of M.
b) Otherwise, the <handler declaration> is immediately contained
in a <local declaration> and its scope is the innermost
<compound statement> in which that <local declaration> is
contained.
2) The <handler declaration> associates R with the SQLSTATE values,
<exception name>, or SQLSTATE class value of each <exception
value>.
3) If R is associated with an <exception name> and that <exception
name> was defined for an SQLSTATE value, then R is also
associated with that SQLSTATE value.
4) If R is associated with an SQLSTATE class value, then it is
associated with each SQLSTATE value that has that class value.
Control statements 89
ISO/IEC JTC1/SC21 N8897
14.6 <exception declaration>
14.6 <exception declaration>
Function
Declare an exception name and a corresponding SQLSTATE value.
Format
<exception declaration> ::=
DECLARE <exception name> EXCEPTION
[ FOR <exception value> ]
<exception value> ::=
SQLSTATE [ VALUE ] <character string literal>
Syntax Rules
1) If the <exception declaration> is contained in a <module> M
with no intervening <compound statement>, then the <exception
name> shall be different from the <exception name> of any other
<exception declaration> in M.
2) The <character string literal> provided in an <exception
value> shall be an SQLSTATE value that conforms to the rules
of Subclause 22.1, "SQLSTATE" in ISO/IEC 9075:1992.
Access Rules
None.
General Rules
None.
90 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
14.7 <SQL variable declaration>
14.7 <SQL variable declaration>
Function
Declare a variable.
Format
<SQL variable declaration> ::=
DECLARE <SQL variable name list> [ <constant or updatable> ]
{ <data type> | <domain name> } [ <default clause> ]
<SQL variable name list> ::=
<SQL variable name> [ { <comma> <SQL variable name> }... ]
Syntax Rules
1) If neither CONSTANT nor UPDATABLE is specified, then UPDATABLE
is implicit.
2) For each <SQL variable name> in the <SQL variable name list>:
a) If CONSTANT is specified, then a <default clause> shall be
specified.
b) The effective data type of the variable declared with a
<domain name> is the base data type of the domain and is not
subject to any constraints that may apply to the domain.
Access Rules
None.
General Rules
1) When the variable associated with the <SQL variable declaration>
is created, if the <SQL variable declaration> specifies <default
clause>, then the value of that variable is set to the default
value according to the General Rules of Subclause 11.5,
"<default clause>" in ISO/IEC 9075:1992; otherwise, the value
of the variable is implementation-dependent.
Control statements 91
ISO/IEC JTC1/SC21 N8897
14.8 <assignment statement>
14.8 <assignment statement>
Function
Assign a value to a attribute of an abstract data type or to a
local variable.
Format
<assignment statement> ::=
SET <assignment target> <equals operator> <assignment source>
<assignment target> ::=
<component reference>
| <target specification>
<assignment source> ::=
<value expression>
| <null specification>
Syntax Rules
1 rule deleted
1) The <assignment target> shall not be read-only.
2) If the <assignment target> is constant, then it shall identify
an attribute and the <assignment statement> shall be contained
in a <routine> that is a constructor for the abstract data type
instance containing the attribute identified by <assignment
target>.
3) If the <assignment target> is a <component reference> that
identifies an attribute or is an <SQL variable name>, and
<assignment source> is a <value expression>, then the data type
of the <value expression> shall be assignable to the data type
of the abstract data type attribute identified by <assignment
target>.
4) The <value specification> contained in the <component
reference> shall be a <target specification> or <simple target
specification>.
Access Rules
None.
92 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
14.8 <assignment statement>
General Rules
1) Case:
a) If <assignment target> immediately contains an <component
reference>, then the result of the <assignment
statement> is given by applying the General Rules of
<REFERENCE>(componentref\FULL), in ISO/IEC 9075:1992, with
the_value_of_<assignment_source>_as_VALUE.__________________________
**Editor's Note**
The preceding Rule refers to the SQL3 Subclause named "<component
reference>", which is clearly inapplicable to SQL-92 and therefore
_to_this_document._This_must_be_corrected.__________________________
| |
b| Otherwise, let TV be the item identified by the <assignment |
|target> and SV be the value of the <assignment source>. The |
|General Rules of Subclause 9.1, "Retrieval assignment" in ISO |
/IEC 9075:1992, are applied to TV and SV as TARGET and VALUE
respectively.
Control statements 93
ISO/IEC JTC1/SC21 N8897
14.9 <case statement>
14.9 <case statement>
Function
Select an execution path based on multiple cases.
Format
<case statement> ::=
<simple case statement>
| <searched cast statement>
<simple case statement> ::=
CASE <simple case operand 1>
<simple case statement when clause>...
[ <case statement else clause> ]
END CASE
<searched case statement> ::=
CASE
<searched case statement when clause>...
[ <case statement else clause> ]
END CASE
<simple case statement when clause> ::=
WHEN <simple case operand 2> THEN <SQL statement list>
<searchede case statement when clause> ::=
WHEN <search condition> THEN <SQL statement list>
<case statement else clause> ::=
ELSE <SQL statement list>
<simple case operand 1> ::= <value expression>
<simple case operand 2> ::= <value expression>
Syntax Rules
1) If a <case statement> specifies a <simple case statement>, then
let SCO1 be the <simple case operand 1>:
a) The data type of each <simple case operand 2> SCO2 shall be
comparable with the data type of SCO1.
b) The <simple case statement> is equivalent to a <searched case
statement> in which each <searched statement when clause>
specifies a <search condition> of the form:
SCO1 = SCO2
94 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
14.9 <case statement>
Access Rules
None.
General Rules
1) Case:
a) If the <search condition> of some <searched case statement
when clause> in a <case statement> is true, then the <SQL
statement list> of the first (leftmost) <searched case
statement when clause> whose <search condition> is true is
executed.
b) If the <case statement> simply contains a <case statement
else clause>, then the <SQL statement lsit> of that <case
statement else clause> is executed.
c) Otherwise, an exception condition is raised: case not found
for case statement.
Control statements 95
ISO/IEC JTC1/SC21 N8897
14.10 <if statement>
14.10 <if statement>
Function
Provide conditional execution based on the truth value of a
condition.
Format
<if statement> ::=
IF <search condition>
<if statement then clause>
[ <if statement elseif clause>... ]
[ <if statement else clause> ]
END IF
<if statement then clause> ::=
THEN <SQL statement list>
<if statement elseif clause> ::=
ELSEIF <search condition> THEN <SQL statement list>
<if statement else clause> ::=
ELSE <SQL statement list>
Syntax Rules
1) If one or more <if statement elseif clause>s are specified, then
the <if statement> is equivalent to an <if statement> that does
not contain ELSEIF by performing the following transformation
recursively:
IF <search condition>
<if statement then clause>
<if statement elseif clause 1>
[ <if statement elseif clause> . . . ]
[ <if statement else clause> ]
END IF
is equivalent to
IF <search condition>
<if statement then clause>
ELSE
IF <search condition 1>
THEN <statement list 1>
[ <if statement elseif clause>... ]
[ <if statement else clause> ]
END IF
END IF
96 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
14.10 <if statement>
where <search condition 1> is the <search condition> directly
contained in <if statement elseif clause 1> and <statement
list 1> is the <SQL statement list> directly contained in <if
statement elseif clause 1>.
Access Rules
None.
General Rules
1) If the <search condition> immediately contained in the <if
statement> evaluates to true, then the <SQL statement list>
immediately contained in the <if statement then clause> is
executed and execution of the <if statement> is terminated.
2) If an <if statement else clause> is specified, then the <SQL
statement list> immediately contained in the <if statement
else clause> is executed and execution of the <if statement> is
terminated.
Control statements 97
ISO/IEC JTC1/SC21 N8897
14.11 <leave statement>
14.11 <leave statement>
Function
Continue execution by leaving a block or loop statement.
Format
<leave statement> ::=
LEAVE <statement label>
Syntax Rules
1) A <leave statement> L shall be contained within a labeled <SQL
procedure statement> S that specifies a <label> identical to
the <statement label> of L.
2) The <statement label> of L is called the corresponding
<statement label>.
Access Rules
None.
General Rules
1) Let L be the <leave statement> that is encountered.
2) Let S be the <SQL procedure statement> containing L with a
corresponding <statement label>.
3) No further action is performed by S.
98 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
14.12 <loop statement>
14.12 <loop statement>
Function
Repeat the execution of a statement.
Format
<loop statement> ::=
[ <beginning label> <colon> ]
[ <loop iteration scheme> ]
LOOP
<SQL statement list>
END LOOP [ <ending label> ]
<loop iteration scheme> ::=
WHILE <search condition>
Syntax Rules
1) If a <loop statement> has a <beginning label>, then it is called
a labeled <loop statement>.
2) If <ending label> is specified, then it shall be identical to
<beginning label>.
Access Rules
None.
General Rules
1) Let LS be the <loop statement>.
2) If a <loop iteration scheme> I is provided for LS, then:
a) The <search condition> contained in I is evaluated.
b) If the <search condition> is true, then the <SQL statement
list> is executed, and if each statement in the <SQL
statement list> executes without raising an exception, then
execution of LS is repeated.
c) If the <search condition> is false or unknown, then the
execution of LS is terminated.
3) If a <loop iteration scheme> is not provided for LS, then
the <SQL statement list> is executed repeatedly until an
unhandled exception condition is raised or execution of a
<leave statement> terminates execution of LS.
Control statements 99
ISO/IEC JTC1/SC21 N8897
14.13 <for statement>
14.13 <for statement>
Function
Execute a statement for each row of a query expression.
Format
<for statement> ::=
[ <beginning label> <colon> ]
FOR <for loop variable name> AS
[ <cursor name> [ <cursor sensitivity> ] CURSOR FOR ]
<cursor specification>
DO <SQL statement list>
END FOR [ <ending label> ]
<for loop variable name> ::= <identifier>
Syntax Rules
1) Let FCS be the <cursor specification> of the <for statement> FS.
2) If <cursor name> is specified, then let CN be that <cursor
name>. Otherwise, let CN be an implementation-dependent <cursor
name> that is different from any other <cursor name> in the
outermost containing <module> or <routine>.
3) Let QE be the <query expression> of FCS. Each column of the
table specified by QE shall have a <column name>. Let V1,
V2, . . . , VN be those <column name>s. Let DT1, DT2, . . . , DTN
be the data types of the respective columns.
4) Let BL, FLVN, and SLL be the <beginning label>, <for loop
variable name>, and <SQL statement list> of FS. Let S be an
implementation-dependent <SQL variable name> that is different
from any <SQL variable name> in the outermost containing
<module> or <routine>. Let CS be the explicit or implicit
<cursor sensitivity>. The <for statement> is equivalent to:
BL:
BEGIN
DECLARE CN CS CURSOR FOR FCS;
1 declaration deleted
DECLARE V1 DT1;
DECLARE V2 DT2;
100 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
14.13 <for statement>
.
.
.
DECLARE VN DTN;
DECLARE AT_END BOOLEAN DEFAULT FALSE;
DECLARE HANDLER PROCEDURE DO_NOTHING()
BEGIN
END;
DECLARE HANDLER PROCEDURE AT_END()
SET AT_END = TRUE;
BEGIN
DECLARE DO_NOTHING HANDLER FOR SQLSTATE '00000';
DECLARE DO_NOTHING HANDLER FOR SQLWARNING;
DECLARE AT_END HANDLER FOR NOT FOUND;
OPEN CN;
FETCH CN INTO V1, V2, . . . , VN;
1 line deleted
END;
FLVN:
WHILE NOT AT_END LOOP
SLL;
BEGIN
DECLARE DO_NOTHING HANDLER FOR SQLSTATE '00000';
DECLARE AT_END HANDLER FOR NOT FOUND;
FETCH CN INTO V1, V2, . . . , VN;
END;
END LOOP FLVN;
CLOSE CN;
END BL;
5) <SQL statement list> shall not generally contain a <fetch
statement>_that_specifies_CN._______________________________________
**Editor's Note**
Termination of a transaction within a FOR loop has been identified
as a potential problem area that requires serious additional
thought._See_Possible_Problem_<PSM-004>_in_the_Editor's_Notes.______
| |
| |
Ac|ess Rules |
| |
None.
Control statements 101
ISO/IEC JTC1/SC21 N8897
14.13 <for statement>
General Rules
None.
102 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
14.14 <signal statement>
14.14 <signal statement>
Function
Signal an exception condition.
Format
<signal statement> ::=
SIGNAL <exception name>
[ <signal argument list> ]
<signal argument list> ::=
<left paren> <argument> [ { <comma> <argument> }... ] <right paren>
Syntax Rules
1) <exception name> shall be defined in the scope of this statement
by an <exception declaration>.
2) Let SAL be the <signal argument list>, let HDPL be the list
of parameters for a <handler declaration>, and let EN be the
<exception name>.
3) The appropriate handler declaration HD is determined as
follows:
a) EN shall be the exception name of HD.
b) If SAL is specified, then for all i that shall be exactly one
HD whose i-th HDPL specifies a <data type> that is identical
to the i-th <data type> in the SAL of EN.
4) Determine the handler procedure HD by applying the Syntax Rules
of_<REFERENCE>(psm_handler_proc\FULL).______________________________
**Editor's Note**
Change proposal X3H2-94-201/SOU-184 said "...by applying the Syntax
Rules of Subclause 13.5, "<handler procedure>"...", but there is
no such Subclause. I considered using Subclause 13.5, "<handler
declaration>", which was provided by the proposal, but the Syntax
Rules there don't seem to help determine a handler procedure.
Advice is urgently requested.
Jeff Richey has indicated interest in this area and has
suggested an approach for a possible change proposal that may
___be_part_of_the_comments_on_this_CD_For_Comment.__________________
| |
Ac|ess Rules |
| |
|None. |
| |
| Control statements 103 |
ISO/IEC JTC1/SC21 N8897
14.14 <signal statement>
General Rules
1) The <signal statement> effectively invokes the handler procedure
HP using the argument list SAL, based on the Rules specified in
Subclause 9.1.
2) Let N be the value of the statement information field NUMBER
in the Diagnostics Area before the execution of the <signal
statement>. The existing exception information areas 1 through N
in the Diagnostics Area are cleared. The value of the statement
information field NUMBER in the Diagnostics Area is set to 1 and
the MORE field is set to 'N'.
The statement information field COMMAND_FUNCTION is set to
"<signal statement>" and the DYNAMIC_FUNCTION field is cleared.
In the first exception information area in the Diagnostics Area,
the exception information field RETURNED_SQLSTATE is set to
the value associated with the specified <exception name> and
the field EXCEPTION_IDENTIFIER is set to contain the <exception
name>.
104 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
14.15 <resignal statement>
14.15 <resignal statement>
Function
Resignal an exception condition.
Format
<resignal statement> ::=
RESIGNAL [ <exception name> ]
Syntax Rules
1) A <resignal statement> shall be contained in a handler
procedure.
2) If <exception name> is specified, then it shall be defined in
the scope of this statement by an <exception declaration>.
Access Rules
None.
General Rules
1) Let N be the value of the statement information field NUMBER
in the Diagnostics Area before the execution of the <resignal
statement>.
Case:
a) If <exception name> is not specified, then the Diagnostics
Area remains unchanged and the exception information 1
through N is propagated (resignaled).
b) If <exception name> is specified, then the statement
information NUMBER field is incremented. All existing
exception information is stacked such that the i-th exception
information area is placed at the position of the i+1-
st exception information area in the Diagnostics Area.
If the maximum number of exception information areas
for the Diagnostics Area is exceeded, then the statement
information fields NUMBER and MORE are modified as specified
in <REFERENCE>(psm_exception_handler\FULL), and the last
exception information area is lost.
Control statements 105
ISO/IEC JTC1/SC21 N8897
14.15 <resignal statement>
____________________________________________________________________
**Editor's Note**
Change proposal X3H2-94-201/SOU-184 failed to instruct the Editor
what action to take regarding the preceding invalid reference to
the now-removed Subclause for <exception handler>.
Advice is urgently requested.
Jeff Richey has volunteered to produce a change proposal
rewriting this Rule as part of the comments on this CD For
______Comment.______________________________________________________
| |
|In the first exception information area in the Diagnostics |
|Area, the exception information field RETURNED_SQLSTATE is |
|set to the value associated with the specified <exception |
|name> and the field EXCEPTION_IDENTIFIER is set to contain |
|the <exception name>. |
| |
| |
| |
106 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
15 Diagnostics management
15.1 <get diagnostics statement>
Function
Get exception or completion condition information from the
diagnostics area.
Format
No additional Format items.
Syntax Rules
No additional Syntax Rules.
Access Rules
No additional Access Rules.
General Rules
1) Table 8, "SQL-statement character codes for use in the
diagnostics area", provides additional SQL-statement character
codes.
Table 8-SQL-statement character codes
__________________for_use_in_the_diagnostics_area__________________
_SQL-statement____________________Identifier_______________________
| <SQL-server module definition> | DEFINE MODULE |
| | |
| <assignment statement> | ASSIGNMENT |
| | |
| <call statement> | CALL |
| | |
| <compound statement> | BEGIN END |
| | |
| <create routine> | CREATE ROUTINE |
| | |
| <drop module statement> | DROP MODULE |
| | |
| <drop routine statement> | DROP ROUTINE |
| | |
| <for statement> | FOR |
| | |
|_<return_statement>_____________|_RETURN__________________________|
| | |
Diagnostics management 107
ISO/IEC JTC1/SC21 N8897
108 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
16 Information Schema and Definition Schema
16.1 Information Schema
16.1.1 DOMAINS view
Function
Identify the domains defined in this catalog that are accessible to
a given user.
Definition
CREATE VIEW DOMAINS
AS SELECT DISTINCT
DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME,
DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_
LENGTH,
COLLATION_CATALOG, COLLATION_SCHEMA,COLLATION_NAME,
CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_
NAME,
NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,
DATETIME_PRECISION, INTERVAL_CODE, INTERVAL_
PRECISION, DOMAIN_DEFAULT
ABSTRACT_DATA_TYPE_CATALOG, ABSTRACT_DATA_TYPE_SCHEMA,
ABSTRACT_DATA_TYPE_NAME,
NULL_CLASS_CATALOG, NULL_CLASS_SCHEMA, NULL_CLASS_NAME
FROM DEFINITION_SCHEMA.DOMAINS
JOIN
DEFINITION_SCHEMA.DATA_TYPE_DESCRIPTOR AS D
LEFT JOIN
DEFINITION_SCHEMA.COLLATIONS AS S
USING ( COLLATION_CATALOG, COLLATION_
SCHEMA, COLLATION_NAME )
ON
( ( DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_
NAME, '' ,0 )
= ( TABLE_OR_DOMAIN_CATALOG, TABLE_OR_DOMAIN_SCHEMA,
TABLE_OR_DOMAIN_NAME, COLUMN_NAME , ORDINAL_
POSITION ) )
WHERE
( ( DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_
NAME, 'DOMAIN' )
IN
( SELECT OBJECT_CATALOG, OBJECT_SCHEMA, OBJECT_
NAME, OBJECT_TYPE
FROM DEFINITION_SCHEMA.USAGE_PRIVILEGES
WHERE GRANTEE IN ( 'PUBLIC', CURRENT_USER ) )
Information Schema and Definition Schema 109
ISO/IEC JTC1/SC21 N8897
16.1 Information Schema
OR
( DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME ) IN
( SELECT DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME
FROM COLUMNS ) )
AND DOMAIN_CATALOG
= ( SELECT CATALOG_NAME FROM INFORMATION_SCHEMA_
CATALOG_NAME )
110 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
16.1 Information Schema
16.1.2 COLUMNS view
Function
Identify the columns of tables defined in this catalog that are
accessible to a given user.
Definition
CREATE VIEW COLUMNS
AS SELECT DISTINCT
TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,
C.COLUMN_NAME, ORDINAL_POSITION,
CASEWHEN EXISTS ( SELECT *
FROM DEFINITION_SCHEMA.SCHEMATA AS S
WHERE ( TABLE_CATALOG, TABLE_SCHEMA )
= (S.CATALOG_NAME, S.SCHEMA_NAME )
AND SCHEMA_OWNER = USER )
THEN COLUMN_DEFAULT
ELSE NULL
END AS COLUMN_DEFAULT,
IS_NULLABLE,
COALESCE (D1.DATA_TYPE, D2.DATA_TYPE) AS DATA_TYPE,
COALESCE (D1.CHARACTER_MAXIMUM_LENGTH, D2.CHARACTER_
MAXIMUM_LENGTH)
AS CHARACTER_MAXIMUM_LENGTH,
COALESCE (D1.CHARACTER_OCTET_LENGTH, D2.CHARACTER_OCTET_
LENGTH)
AS CHARACTER_OCTET_LENGTH,
COALESCE (D1.NUMERIC_PRECISION, D2.NUMERIC_PRECISION)
AS NUMERIC_PRECISION,
COALESCE (D1.NUMERIC_PRECISION_RADIX, D2.NUMERIC_PRECISION_
RADIX)
AS NUMERIC_PRECISION_RADIX,
COALESCE (D1.NUMERIC_SCALE, D2.NUMERIC_SCALE) AS NUMERIC_
SCALE,
COALESCE (D1.DATETIME_PRECISION, D2.DATETIME_PRECISION) AS
DATETIME_PRECISION,
COALESCE (D1.INTERVAL_CODE, D3.INTERVAL_CODE) AS INTERVAL_
CODE,
COALESCE (D1.INTERVAL_PRECISION, D3.INTERVAL_PRECISION) AS
INTERVAL_PRECISION,
COALESCE (C1.CHARACTER_SET_CATALOG, C2.CHARACTER_SET_
CATALOG)
AS CHARACTER_SET_CATALOG,
COALESCE (C1.CHARACTER_SET_SCHEMA, C2.CHARACTER_SET_SCHEMA)
AS CHARACTER_SET_SCHEMA,
COALESCE (C1.CHARACTER_SET_NAME, C2.CHARACTER_SET_NAME) AS
CHARACTER_SET_NAME,
COALESCE (D1.COLLATION_CATALOG, D2.COLLATION_CATALOG) AS
COLLATION_CATALOG,
Information Schema and Definition Schema 111
ISO/IEC JTC1/SC21 N8897
16.1 Information Schema
COALESCE (D1.COLLATION_SCHEMA, D2.COLLATION_SCHEMA) AS
COLLATION_SCHEMA,
COALESCE (D1.COLLATION_NAME, D2.COLLATION_NAME) AS
COLLATION_NAME,
DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME,
COALESCE (D1.ABSTRACT_DATA_TYPE_CATALOG, D2.ABSTRACT_DATA_
TYPE_CATALOG)
AS ABSTRACT_DATA_TYPE_CATALOG,
COALESCE (D1.ABSTRACT_DATA_TYPE_SCHEMA, D2.ABSTRACT_DATA_
TYPE_SCHEMA)
AS ABSTRACT_DATA_TYPE_SCHEMA,
COALESCE (D1.ABSTRACT_DATA_TYPE_NAME, D2.ABSTRACT_DATA_
TYPE_NAME)
AS ABSTRACT_DATA_TYPE_NAME,
COALESCE (D1.NULL_CLASS_CATALOG, D2.NULL_CLASS_CATALOG)
AS NULL_CLASS_CATALOG,
COALESCE (D1.NULL_CLASS_SCHEMA, D2.NULL_CLASS_SCHEMA)
AS NULL_CLASS_SCHEMA,
COALESCE (D1.NULL_CLASS_NAME, D2.NULL_CLASS_NAME) AS NULL_
CLASS_NAME
FROM DEFINITION_SCHEMA.COLUMNS AS C
LEFT JOIN
DEFINITION_SCHEMA.DATA_TYPE_DESCRIPTOR AS D1
LEFT JOIN
DEFINITION_SCHEMA.COLLATIONS AS C1
ON
( ( C1.COLLATION_CATALOG, C1.COLLATION_
SCHEMA, C1.COLLATION_NAME )
= ( D1.COLLATION_CATALOG, D1.COLLATION_
SCHEMA, D1.COLLATION_NAME ) )
ON
( ( C.TABLE_CATALOG, C.TABLE_SCHEMA, C.TABLE_
NAME, 'COLUMN',
C.COLUMN_NAME, 0 )
= ( D1.OBJECT_CATALOG, D1.OBJECT_SCHEMA, D1.OBJECT_
NAME,
D1.OBJECT_TYPE, D1.COLUMN_NAME, D1.ORDINAL_
POSITION ) )
LEFT JOIN
DEFINITION_SCHEMA.DATA_TYPE_DESCRIPTOR AS D2
LEFT JOIN
DEFINITION_SCHEMA.COLLATIONS AS C2
ON
( ( C2.COLLATION_CATALOG, C2.COLLATION_
SCHEMA, C2.COLLATION_NAME )
= ( D2.COLLATION_CATALOG, D2.COLLATION_
SCHEMA, D2.COLLATION_NAME ) )
ON
( ( C.DOMAIN_CATALOG, C.DOMAIN_SCHEMA, C.DOMAIN_
NAME, 'DOMAIN',
'', 0 )
= ( D2.OBJECT_CATALOG, D2.OBJECT_SCHEMA, D2.OBJECT_
NAME,
112 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
16.1 Information Schema
D2.OBJECT_TYPE, D2.COLUMN_NAME, D2.ORDINAL_
POSITION ) )
WHERE ( C.TABLE_CATALOG, C.TABLE_SCHEMA, C.TABLE_
NAME, C.COLUMN_NAME )
IN
( SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_
NAME, COLUMN_NAME
FROM DEFINITION_SCHEMA.COLUMN_PRIVILEGES
WHERE GRANTEE IN ( 'PUBLIC', CURRENT_USER ) )
AND C.TABLE_CATALOG
= ( SELECT CATALOG_NAME FROM INFORMATION_SCHEMA_
CATALOG_NAME )
Information Schema and Definition Schema 113
ISO/IEC JTC1/SC21 N8897
16.1 Information Schema
16.1.3 MODULES view
Function
Identify the modules in this catalog that are accessible to a given
user.
Definition
CREATE VIEW MODULES AS
SELECT
MODULE_CATALOG, MODULE_SCHEMA, MODULE_NAME,
CASE WHEN ( MODULE_CATALOG, MODULE_SCHEMA, CURRENT_USER )
IN ( SELECT CATALOG_NAME, SCHEMA_NAME, SCHEMA_OWNER
FROM DEFINITION_SCHEMA.SCHEMATA )
THEN MODULE_DEFINITION
ELSE NULL
END AS MODULE_DEFINITION,
MODULE_AUTHORIZATION
FROM DEFINITION_SCHEMA.MODULES
WHERE ( MODULE_CATALOG, MODULE_SCHEMA, MODULE_NAME )
IN ( SELECT MODULE_CATALOG, MODULE_SCHEMA, MODULE_
NAME
FROM DEFINITION_SCHEMA.MODULE_PRIVILEGES
WHERE GRANTEE IN ( 'PUBLIC', CURRENT_USER )
)
AND MODULE_CATALOG
= ( SELECT CATALOG_NAME FROM INFORMATION_SCHEMA_
CATALOG_NAME )
114 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
16.1 Information Schema
16.1.4 ROUTINES view
Function
Identify the routines in this catalog that are accessible to a
given user.
Definition
CREATE VIEW ROUTINES AS
SELECT
SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME,
ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME,
MODULE_CATALOG, MODULE_SCHEMA, MODULE_NAME,
R.ABSTRACT_DATA_TYPE_CATALOG, R.ABSTRACT_DATA_TYPE_SCHEMA,
R.ABSTRACT_DATA_TYPE_NAME,
ROUTINE_TYPE,
DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_
LENGTH,
COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_NAME,
NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,
DATETIME_PRECISION, INTERVAL_CODE, INTERVAL_PRECISION,
D.ABSTRACT_DATA_TYPE_CATALOG AS TYPE_ABSTRACT_DATA_TYPE_
CATALOG,
D.ABSTRACT_DATA_TYPE_SCHEMA AS TYPE_ABSTRACT_DATA_TYPE_
SCHEMA,
D.ABSTRACT_DATA_TYPE_NAME AS TYPE_ABSTRACT_DATA_TYPE_NAME,
ROUTINE_BODY,
CASE WHEN ( SPECIFIC_CATALOG, SPECIFIC_SCHEMA, CURRENT_
USER )
IN ( SELECT CATALOG_NAME, SCHEMA_NAME, SCHEMA_
OWNER
FROM DEFINITION_SCHEMA.SCHEMATA )
THEN ROUTINE_DEFINITION
ELSE NULL
END AS ROUTINE_DEFINITION,
EXTERNAL_NAME, EXTERNAL_LANGUAGE, IS_VARIANT
FROM DEFINITION_SCHEMA.ROUTINES R
LEFT JOIN DEFINITION_SCHEMA.DATA_TYPE_DESCRIPTOR D
ON
( SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME,
'FUNCTION_RESULT', '', 0 )
= ( OBJECT_CATALOG, OBJECT_SCHEMA, OBJECT_NAME,
OBJECT_TYPE, COLUMN_NAME, ORDINAL_POSITION )
WHERE
( ( ( MODULE_CATALOG, MODULE_SCHEMA, MODULE_NAME) IS NULL
AND
( SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME )
IN ( SELECT SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_
NAME
FROM DEFINITION_SCHEMA.ROUTINE_PRIVILEGES
WHERE GRANTEE IN ( 'PUBLIC', CURRENT_USER )
)
Information Schema and Definition Schema 115
ISO/IEC JTC1/SC21 N8897
16.1 Information Schema
)
OR
( ( MODULE_CATALOG, MODULE_SCHEMA, MODULE_NAME) IS NOT NULL
AND
( MODULE_CATALOG, MODULE_SCHEMA, MODULE_NAME )
IN ( SELECT MODULE_CATALOG, MODULE_SCHEMA, MODULE_NAME
FROM DEFINITION_SCHEMA.MODULE_PRIVILEGES
WHERE GRANTEE IN ( 'PUBLIC', CURRENT_USER )
)
)
)
AND SPECIFIC_CATALOG
= ( SELECT CATALOG_NAME FROM INFORMATION_SCHEMA_CATALOG_
NAME )
116 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
16.1 Information Schema
16.1.5 PARAMETERS view
Function
Identify the parameters of routines defined in this catalog.
Definition
CREATE VIEW PARAMETERS AS
SELECT
P1.SPECIFIC_CATALOG, P1.SPECIFIC_SCHEMA, P1.SPECIFIC_NAME,
P1.ORDINAL_POSITION, PARAMETER_MODE, PARAMETER_NAME,
DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_
LENGTH,
D1.COLLATION_CATALOG, D1.COLLATION_SCHEMA, D1.COLLATION_
NAME,
C1.CHARACTER_SET_CATALOG, C1.CHARACTER_SET_SCHEMA,
C1.CHARACTER_SET_NAME,
NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,
DATETIME_PRECISION,INTERVAL_CODE, INTERVAL_PRECISION,
D1.ABSTRACT_DATA_TYPE_CATALOG, D1.ABSTRACT_DATA_TYPE_
SCHEMA,
D1.ABSTRACT_DATA_TYPE_NAME,
PARAMETER_DEFAULT
FROM DEFINITION_SCHEMA.PARAMETERS P1
LEFT JOIN
DEFINITION_SCHEMA.DATA_TYPE_DESCRIPTOR D1
LEFT JOIN DEFINITION_SCHEMA.COLLATIONS C1
ON ( ( C1.COLLATION_CATALOG, C1.COLLATION_SCHEMA,
C1.COLLATION_NAME )
= ( D1.COLLATION_CATALOG, D1.COLLATION_SCHEMA,
D1.COLLATION_NAME ) )
ON
(SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME,
'PARAMETER', '', P1.ORDINAL_POSITION)
= (OBJECT_CATALOG, OBJECT_SCHEMA, OBJECT_NAME,
OBJECT_TYPE, COLUMN_NAME, D1.ORDINAL_POSITION)
INNER JOIN DEFINITION_SCHEMA.ROUTINES R1
ON ( ( P1.SPECIFIC_CATALOG, P1.SPECIFIC_
SCHEMA, P1.SPECIFIC_NAME )
= ( R1.SPECIFIC_CATALOG, R1.SPECIFIC_
SCHEMA, R1.SPECIFIC_NAME )
)
WHERE
( ( ( MODULE_CATALOG, MODULE_SCHEMA, MODULE_NAME) IS NULL
AND
( P1.SPECIFIC_CATALOG, P1.SPECIFIC_SCHEMA, P1.SPECIFIC_
NAME )
IN (
SELECT SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME
FROM DEFINITION_SCHEMA.ROUTINE_PRIVILEGES
WHERE GRANTEE IN ( 'PUBLIC', CURRENT_USER )
)
Information Schema and Definition Schema 117
ISO/IEC JTC1/SC21 N8897
16.1 Information Schema
)
OR
( ( MODULE_CATALOG, MODULE_SCHEMA, MODULE_NAME) IS NOT NULL
AND
( MODULE_CATALOG, MODULE_SCHEMA, MODULE_NAME )
IN (
SELECT MODULE_CATALOG, MODULE_SCHEMA, MODULE_NAME
FROM DEFINITION_SCHEMA.MODULE_PRIVILEGES
WHERE GRANTEE IN ( 'PUBLIC', CURRENT_USER )
)
)
)
AND P1.SPECIFIC_CATALOG
= ( SELECT CATALOG_NAME FROM INFORMATION_SCHEMA_CATALOG_
NAME )
118 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
16.2 Definition Schema
16.2 Definition Schema
16.2.1 DATA_TYPE_DESCRIPTOR base table
Function
The DATA_TYPE_DESCRIPTOR table has one row for each domain, one
row for each column (in each table) that is defined as having a
data type rather than a domain, one row for each function, and one
row for each parameter of each routine. It effectively contains a
representation of the data type descriptors.
Definition
CREATE TABLE DATA_TYPE_DESCRIPTOR
(
OBJECT_CATALOG INFORMATION_SCHEMA.SQL_
IDENTIFIER,
OBJECT_SCHEMA INFORMATION_SCHEMA.SQL_
IDENTIFIER,
OBJECT_NAME INFORMATION_SCHEMA.SQL_
IDENTIFIER,
OBJECT_TYPE INFORMATION_
SCHEMA.CHARACTER_DATA,
COLUMN_NAME INFORMATION_SCHEMA.SQL_
IDENTIFIER,
ORDINAL_POSITION INFORMATION_
SCHEMA.CARDINAL_NUMBER,
DATA_TYPE INFORMATION_
SCHEMA.CHARACTER_DATA
CONSTRAINT TABLE_OR_DOMAIN_DATA_TYPE_NOT_NULL NOT NULL,
CHARACTER_MAXIMUM_LENGTH INFORMATION_
SCHEMA.CARDINAL_NUMBER,
CHARACTER_OCTET_LENGTH INFORMATION_
SCHEMA.CARDINAL_NUMBER,
COLLATION_CATALOG INFORMATION_SCHEMA.SQL_
IDENTIFIER,
COLLATION_SCHEMA INFORMATION_SCHEMA.SQL_
IDENTIFIER,
COLLATION_NAME INFORMATION_SCHEMA.SQL_
IDENTIFIER,
NUMERIC_PRECISION INFORMATION_
SCHEMA.CARDINAL_NUMBER,
NUMERIC_PRECISION_RADIX INFORMATION_
SCHEMA.CARDINAL_NUMBER,
NUMERIC_SCALE INFORMATION_
SCHEMA.CARDINAL_NUMBER,
DATETIME_PRECISION INFORMATION_
SCHEMA.CARDINAL_NUMBER,
INTERVAL_CODE INFORMATION_
SCHEMA.CHARACTER_DATA,
INTERVAL_PRECISION INFORMATION_
SCHEMA.CARDINAL_NUMBER,
Information Schema and Definition Schema 119
ISO/IEC JTC1/SC21 N8897
16.2 Definition Schema
ABSTRACT_DATA_TYPE_CATALOG INFORMATION_SCHEMA.SQL_
IDENTIFIER,
ABSTRACT_DATA_TYPE_SCHEMA INFORMATION_SCHEMA.SQL_
IDENTIFIER,
ABSTRACT_DATA_TYPE_NAME INFORMATION_SCHEMA.SQL_
IDENTIFIER,
CONSTRAINT OBJECT_CHECK_COMBINATIONS
CHECK ( OBJECT_TYPE = 'COLUMN'
AND CHARACTER_LENGTH (COLUMN_NAME) > 0
AND ORDINAL_POSITION = 0
OR
OBJECT_TYPE IN ( 'DOMAIN', 'FUNCTION_RESULT')
AND CHARACTER_LENGTH (COLUMN_NAME) = 0
AND ORDINAL_POSITION = 0
OR
OBJECT_TYPE = 'PARAMETER'
AND CHARACTER_LENGTH (COLUMN_NAME) = 0
AND ORDINAL_POSITION > 0
) ,
CONSTRAINT DATA_TYPE_CHECK_COMBINATIONS
CHECK ( DATA_TYPE IN ( 'CHARACTER', 'CHARACTER VARYING',
'BIT', 'BIT VARYING' )
AND ( CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_
LENGTH,
COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_
NAME )
IS NOT NULL
AND ( NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX,
NUMERIC_SCALE, DATETIME_PRECISION,
ABSTRACT_DATA_TYPE_CATALOG, ABSTRACT_DATA_TYPE_
SCHEMA,
ABSTRACT_DATA_TYPE_NAME ) IS NULL
AND ( INTERVAL_CODE, INTERVAL_PRECISION )
IS NULL
OR
DATA_TYPE IN ( 'REAL', 'DOUBLE PRECISION', 'FLOAT' )
AND ( CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_
LENGTH,
COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_
NAME )
IS NULL
AND NUMERIC_PRECISION IS NOT NULL
AND NUMERIC_PRECISION_RADIX = 2
AND NUMERIC_SCALE IS NULL
AND DATETIME_PRECISION IS NULL
AND ( ABSTRACT_DATA_TYPE_CATALOG, ABSTRACT_DATA_TYPE_
SCHEMA,
ABSTRACT_DATA_TYPE_NAME ) IS
NULL
AND ( INTERVAL_CODE, INTERVAL_PRECISION )
IS NULL
120 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
16.2 Definition Schema
OR
DATA_TYPE IN ( 'INTEGER', 'SMALLINT' )
AND ( CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_
LENGTH,
COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_
NAME )
IS NULL
AND NUMERIC_PRECISION_RADIX IN ( 2, 10 ) )
AND NUMERIC_PRECISION IS NOT NULL
AND NUMERIC_SCALE = 0
AND DATETIME_PRECISION IS NULL
AND ( INTERVAL_CODE, INTERVAL_PRECISION ) IS NULL
OR
DATA_TYPE IN ( 'NUMERIC', 'DECIMAL' )
AND ( CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_
LENGTH,
COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_
NAME )
IS NULL
AND NUMERIC_PRECISION_RADIX = 10
AND ( NUMERIC_PRECISION, NUMERIC_SCALE ) IS NOT NULL
AND DATETIME_PRECISION IS NULL
AND ( INTERVAL_CODE, INTERVAL_PRECISION ) IS NULL
AND ( INTERVAL_CODE, INTERVAL_PRECISION )
IS NULL
OR
DATA_TYPE IN ( 'DATE', 'TIME', 'TIMESTAMP',
'TIME WITH TIME ZONE', 'TIMESTAMP
WITH TIME ZONE' )
AND ( CHARACTER_MAXIMUM_LENGTH,
CHARACTER_OCTET_LENGTH,
COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_
NAME )
IS NULL
AND ( NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX )
IS NOT NULL
AND NUMERIC_SCALE IS NULL
AND DATETIME_PRECISION IS NOT NULL
AND ( ABSTRACT_DATA_TYPE_CATALOG, ABSTRACT_DATA_TYPE_
SCHEMA,
ABSTRACT_DATA_TYPE_NAME )
IS NULL
AND ( INTERVAL_CODE, INTERVAL_PRECISION )
IS NULL
OR
DATA_TYPE = 'INTERVAL'
AND ( CHARACTER_MAXIMUM_LENGTH,
CHARACTER_OCTET_LENGTH,
COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_
NAME )
IS NULL
AND ( NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX )
IS NOT NULL
Information Schema and Definition Schema 121
ISO/IEC JTC1/SC21 N8897
16.2 Definition Schema
AND NUMERIC_SCALE IS NULL
AND DATETIME_PRECISION IS NOT NULL
AND ( ABSTRACT_DATA_TYPE_CATALOG, ABSTRACT_DATA_TYPE_
SCHEMA,
ABSTRACT_DATA_TYPE_NAME )
IS NULL
AND INTERVAL_CODE IN
( 'YEAR', 'MONTH', 'DAY', 'HOUR',
'MINUTE', 'SECOND', 'YEAR TO MONTH',
'DAY TO HOUR', 'DAY TO MINUTE',
'DAY TO SECOND', 'HOUR TO MINUTE',
'HOUR TO SECOND', 'MINUTE TO SECOND' )
AND INTERVAL_PRECISION
IS NOT NULL
OR
DATA_TYPE = 'BOOLEAN'
AND ( CHARACTER_MAXIMUM_LENGTH,
CHARACTER_OCTET_LENGTH,
COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_
NAME )
IS NULL
AND ( NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX )
IS NULL
AND NUMERIC_SCALE IS NULL
AND DATETIME_PRECISION IS NULL
AND ( ABSTRACT_DATA_TYPE_CATALOG, ABSTRACT_DATA_TYPE_
SCHEMA,
ABSTRACT_DATA_TYPE_NAME )
IS NULL
AND ( INTERVAL_CODE, INTERVAL_PRECISION )
IS NULL
OR
DATA_TYPE = 'USER_DEFINED'
AND ( NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX )
NUMERIC_SCALE, DATETIME_PRECISION,
CHARACTER_OCTET_LENGTH,
CHARACTER_MAXIMUM_LENGTH ) IS NULL
AND ( INTERVAL_CODE, INTERVAL_PRECISION )
IS NULL
),
CONSTRAINT DATA_TYPE_DESCRIPTOR_PRIMARY_KEY
PRIMARY KEY ( OBJECT_CATALOG, OBJECT_SCHEMA, OBJECT_NAME,
OBJECT_TYPE, COLUMN_NAME, ORDINAL_
POSITION ),
CONSTRAINT DATA_TYPE_CHECK_REFERENCES_COLLATION
CHECK ( COLLATION_CATALOG
<> ANY ( SELECT CATALOG_NAME FROM SCHEMATA )
OR
( COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_
NAME ) IN
122 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
16.2 Definition Schema
( SELECT COLLATION_CATALOG, COLLATION_
SCHEMA, COLLATION_NAME
FROM COLLATIONS ) ),
CONSTRAINT DATA_TYPE_DESCRIPTOR_FOREIGN_KEY_SCHEMATA
FOREIGN KEY ( ABSTRACT_DATA_TYPE_CATALOG, ABSTRACT_DATA_
TYPE_SCHEMA )
REFERENCES SCHEMATA,
CONSTRAINT DATA_TYPE_DESCRIPTOR_CHECK_USED
CHECK ( ( OBJECT_CATALOG, OBJECT_SCHEMA, OBJECT_NAME,
OBJECT_TYPE, COLUMN_NAME, ORDINAL_POSITION )
IN (
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,
'COLUMN', COLUMN_NAME, 0
FROM COLUMNS
UNION
SELECT DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME,
'DOMAIN', '', 0
FROM DOMAINS
UNION
SELECT SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_
NAME,
'FUNCTION_RESULT', '', 0
FROM ROUTINES
WHERE ROUTINE_TYPE = 'FUNCTION'
UNION
SELECT SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_
NAME,
'PARAMETER', '', ORDINAL_POSITION
FROM PARAMETERS )
)
Description
1) The values of OBJECT_CATALOG and OBJECT_SCHEMA are the catalog
name and the unqualified schema name, respectively, of the
schema that contains the object (domain, column, function, or
parameter) to which the data type descriptor belongs.
2) Case:
a) If OBJECT_TYPE is 'COLUMN', then the value of OBJECT_NAME is
the name of the table, COLUMN_NAME is the name of the column
in that table to which the data type descriptor belongs, and
ORDINAL_POSITION is 0.
b) If OBJECT_TYPE is 'DOMAIN' then the value of OBJECT_NAME
is the name of the domain to which the data type descriptor
belongs, the length of COLUMN_NAME is 0, and ORDINAL_POSITION
is 0.
Information Schema and Definition Schema 123
ISO/IEC JTC1/SC21 N8897
16.2 Definition Schema
c) If OBJECT_TYPE is 'FUNCTION_RESULT' then the value of OBJECT_
NAME is the specific name of the function to which the data
type descriptor belongs, the length of COLUMN_NAME is 0, and
ORDINAL_POSITION is 0.
d) If OBJECT_TYPE is 'PARAMETER' then the value of OBJECT_NAME
is the specific name of the routine to which the data type
descriptor belongs, the length of COLUMN_NAME is 0, and
ORDINAL_POSITION is the ordinal position of the parameter
in the routine.
3) The values of DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_
OCTET_LENGTH, COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_
NAME, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,
DATETIME_PRECISION, ABSTRACT_DATA_TYPE_CATALOG, ABSTRACT_DATA_
TYPE_SCHEMA, and ABSTRACT_DATA_TYPE_NAME contain the data type
of the domain or column being defined, the maximum length in
characters or bits of the column if it is a character or bit
type respectively, maximum length in octets of the column if
it is a character type, the qualified name of the applicable
collation if it is a character type, the precision and radix
of the precision if it is a numeric type, the scale if it
is a numeric type, the fractional seconds precision if it is
a datetime or interval type, and the qualified name of the
abstract data type, if specified.
4) If DATA_TYPE is 'INTERVAL', then the values of INTERVAL_CODE are
the value for <interval qualifier> (as specified in Table 20,
"Codes used for <interval qualifier>s in Dynamic SQL", in ISO
/IEC 9075:1992 ) for the data type being described; otherwise,
INTERVAL_CODE is the null value.
5) If DATA_TYPE is 'INTERVAL', then the values of INTERVAL_
PRECISION are the interval leading field precision of the data
type being described; otherwise, INTERVAL_PRECISION is the null
value.
6) The values of ABSTRACT_DATA_TYPE_CATALOG, ABSTRACT_DATA_TYPE_
SCHEMA, and ABSTRACT_DATA_TYPE_NAME are the null value if
the column being described is not defined as an abstract data
type. Otherwise, the values of ABSTRACT_DATA_TYPE_CATALOG,
ABSTRACT_DATA_TYPE_SCHEMA, and ABSTRACT_DATA_TYPE_NAME are the
qualified name of the abstract data type used by the column
being described.
124 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
16.2 Definition Schema
16.2.2 MODULES base table
Function
The MODULES base table has one row for each SQL-server module.
Definition
CREATE TABLE MODULES
(
MODULE_CATALOG INFORMATION_SCHEMA.SQL_IDENTIFIER,
MODULE_SCHEMA INFORMATION_SCHEMA.SQL_IDENTIFIER,
MODULE_NAME INFORMATION_SCHEMA.SQL_IDENTIFIER,
MODULE_DEFINITION INFORMATION_SCHEMA.CHARACTER_DATA,
MODULE_AUTHORIZATION INFORMATION_SCHEMA.SQL_IDENTIFIER
CONSTRAINT AUTHORIZATION_FOREIGN_KEY_
USERS REFERENCES USERS,
CONSTRAINT MODULES_PRIMARY_KEY
PRIMARY KEY ( MODULE_CATALOG, MODULE_SCHEMA, MODULE_
NAME ),
CONSTRAINT MODULES_FOREIGN_KEY_SCHEMATA
FOREIGN KEY ( MODULE_CATALOG, MODULE_SCHEMA )
REFERENCES SCHEMATA
)
Description
1) The values of MODULE_CATALOG, MODULE_SCHEMA, and MODULE_NAME
are the catalog name, unqualified schema name, and qualified
identifier of the module name of the module being described.
2) Case:
a) If the character representation of the <SQL-Server module
definition> that defined the module being described can be
represented without truncation, then the value of MODULE_
DEFINITION is that character representation.
b) Otherwise, the value of MODULE_DEFINITION is the null value.
Note: Any implicit <column reference>s that were contained in
the <module> are replaced by explicit <column reference>s in
MODULE_DEFINITION.
3) Case:
a) If AUTHORIZATION was specified in <module authorization
clause> in the module being described, then the value of
MODULE_AUTHORIZATION is <module authorization identifier>.
b) Otherwise, the value of MODULE_AUTHORIZATION is the null
value.
Information Schema and Definition Schema 125
ISO/IEC JTC1/SC21 N8897
16.2 Definition Schema
16.2.3 ROUTINES base table
Function
The ROUTINES base table has one row for each routine that has been
created in a module, or created in a schema not within a module.
Definition
CREATE TABLE ROUTINES
(
SPECIFIC_CATALOG INFORMATION_SCHEMA.SQL_IDENTIFIER,
SPECIFIC_SCHEMA INFORMATION_SCHEMA.SQL_IDENTIFIER,
SPECIFIC_NAME INFORMATION_SCHEMA.SQL_IDENTIFIER,
ROUTINE_CATALOG INFORMATION_SCHEMA.SQL_IDENTIFIER,
ROUTINE_SCHEMA INFORMATION_SCHEMA.SQL_IDENTIFIER,
ROUTINE_NAME INFORMATION_SCHEMA.SQL_IDENTIFIER,
MODULE_CATALOG INFORMATION_SCHEMA.SQL_IDENTIFIER,
MODULE_SCHEMA INFORMATION_SCHEMA.SQL_IDENTIFIER,
MODULE_NAME INFORMATION_SCHEMA.SQL_IDENTIFIER,
ABSTRACT_DATA_TYPE_CATALOG INFORMATION_SCHEMA.SQL_
IDENTIFIER,
ABSTRACT_DATA_TYPE_SCHEMA INFORMATION_SCHEMA.SQL_
IDENTIFIER,
ABSTRACT_DATA_TYPE_NAME INFORMATION_SCHEMA.SQL_
IDENTIFIER,
ROUTINE_TYPE INFORMATION_SCHEMA.CHARACTER_DATA
CONSTRAINT ROUTINE_TYPE_NOT_NULL NOT NULL
CONSTRAINT ROUTINE_TYPE_CHECK
CHECK ( ROUTINE_TYPE IN ( 'PROCEDURE', 'FUNCTION' ) ),
ROUTINE_BODY INFORMATION_SCHEMA.CHARACTER_DATA
CONSTRAINT ROUTINE_BODY_NOT_NULL NOT NULL
CONSTRAINT ROUTINE_BODY_CHECK
CHECK ( ROUTINE_BODY IN ( 'SQL', 'EXTERNAL' ) ),
ROUTINE_DEFINITION INFORMATION_SCHEMA.CHARACTER_DATA,
EXTERNAL_NAME INFORMATION_SCHEMA.SQL_IDENTIFIER,
EXTERNAL_LANGUAGE INFORMATION_SCHEMA.CHARACTER_DATA
CONSTRAINT EXTERNAL_LANGUAGE_CHECK
CHECK ( EXTERNAL_LANGUAGE IN ( 'ADA', 'C', 'COBOL',
'FORTRAN', 'MUMPS', 'PASCAL',
'PLI' ) ),
IS_VARIANT INFORMATION_SCHEMA.CHARACTER_DATA
CONSTRAINT IS_VARIANT_CHECK
CHECK ( IS_VARIANT IN ( 'YES', 'NO' ) ),
CONSTRAINT ROUTINES_PRIMARY_KEY
PRIMARY KEY ( SPECIFIC_CATALOG, SPECIFIC_
SCHEMA, SPECIFIC_NAME ),
CONSTRAINT ROUTINES_FOREIGN_KEY_SCHEMATA
FOREIGN KEY ( ROUTINE_CATALOG, ROUTINE_SCHEMA )
REFERENCES SCHEMATA,
CONSTRAINT ROUTINES_FOREIGN_KEY_MODULES
126 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
16.2 Definition Schema
FOREIGN KEY ( MODULE_CATALOG, MODULE_SCHEMA, MODULE_
NAME )
REFERENCES MODULES
MATCH FULL,
CONSTRAINT ROUTINES_FOREIGN_KEY_ABSTRACT_DATA_TYPES
FOREIGN KEY ( ABSTRACT_DATA_TYPE_CATALOG, ABSTRACT_DATA_
TYPE_SCHEMA,
ABSTRACT_DATA_TYPE_NAME )
REFERENCES ABSTRACT_DATA_TYPES
MATCH FULL,
CONSTRAINT ROUTINES_COMBINATIONS
CHECK ( ( ROUTINE_BODY = 'SQL' AND
( EXTERNAL_NAME, EXTERNAL_LANGUAGE, IS_
VARIANT ) IS NULL )
OR
( ROUTINE_BODY = 'EXTERNAL' AND
( EXTERNAL_NAME, EXTERNAL_LANGUAGE, IS_
VARIANT ) IS NOT NULL ) ),
CONSTRAINT ROUTINES_SAME_SCHEMA
CHECK ( (SPECIFIC_CATALOG, SPECIFIC_SCHEMA)
= (ROUTINE_CATALOG, ROUTINE_SCHEMA)
OR (SPECIFIC_CATALOG, SPECIFIC_SCHEMA)
= (MODULE_CATALOG, MODULE_SCHEMA)
OR (SPECIFIC_CATALOG, SPECIFIC_SCHEMA)
= (ABSTRACT_DATA_TYPE_CATALOG, ABSTRACT_DATA_TYPE_
SCHEMA) )
)
Description
1) The values of SPECIFIC_CATALOG, SPECIFIC_SCHEMA, and SPECIFIC_
NAME are the catalog name, unqualified schema name, and
qualified identifier of the specific name of the routine being
described.
2) The values of ROUTINE_CATALOG, ROUTINE_SCHEMA, and ROUTINE_NAME
are the catalog name, unqualified schema name, and qualified
identifier of the routine name of the routine being described.
3) Case:
a) If the routine being described was defined in a module, then
the values of MODULE_CATALOG, MODULE_SCHEMA, and MODULE_NAME
are the catalog name, unqualified schema name, and qualified
identifier of the module name of this module.
b) Otherwise, the values of MODULE_CATALOG, MODULE_SCHEMA, and
MODULE_NAME are the null value.
4) Case:
a) If the routine being described was defined in a abstract
data type, then the values of ABSTRACT_DATA_TYPE_CATALOG,
Information Schema and Definition Schema 127
ISO/IEC JTC1/SC21 N8897
16.2 Definition Schema
ABSTRACT_DATA_TYPE_SCHEMA, and ABSTRACT_DATA_TYPE_NAME are
the catalog name, unqualified schema name, and qualified
identifier of the abstract data type name of this abstract
data type.
b) Otherwise, the values of ABSTRACT_DATA_TYPE_CATALOG,
ABSTRACT_DATA_TYPE_SCHEMA, and ABSTRACT_DATA_TYPE_NAME are
the null value.
5) The values of ROUTINE_TYPE have the following meanings:
PROCEDURE The routine being described is a procedure.
FUNCTION The routine being described is a function.
6) The values of ROUTINE_BODY have the following meanings:
SQL The routine being described is an SQL routine.
EXTERNAL The routine being described is an external routine.
7) Case:
a) If the routine being described is a SQL routine, and the
routine is not contained in a module, and the character
representation of the <routine body> that defined the routine
can be represented without truncation, then the value of
ROUTINE_DEFINITION is that character representation.
b) Otherwise, the value of ROUTINE_DEFINITION is the null
value.
8) Case:
a) If the routine being described is an external routine, then:
i) The value of EXTERNAL_NAME is the external name of the
routine.
ii) The value of EXTERNAL_LANGUAGE is the language of the
external routine.
iii) The value of IS_VARIANT indicates whether VARIANT was
specified when the external routine was defined.
b) Otherwise, the values of EXTERNAL_NAME, EXTERNAL_LANGUAGE,
and IS_VARIANT are the null value.
128 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
16.2 Definition Schema
16.2.4 PARAMETERS base table
Function
The PARAMETERS table has one row for each parameter of each routine
described in the ROUTINES base table.
Definition
CREATE TABLE PARAMETERS
(
SPECIFIC_CATALOG INFORMATION_SCHEMA.SQL_IDENTIFIER,
SPECIFIC_SCHEMA INFORMATION_SCHEMA.SQL_IDENTIFIER,
SPECIFIC_NAME INFORMATION_SCHEMA.SQL_IDENTIFIER,
ORDINAL_POSITION INFORMATION_SCHEMA.CARDINAL_NUMBER
CONSTRAINT PARAMETER_POSITION_NOT_NULL NOT NULL,
PARAMETER_MODE INFORMATION_SCHEMA.CHARACTER_DATA
CONSTRAINT PARAMETER_MODE_NOT_NULL NOT NULL
CONSTRAINT PARAMETER_MODE_CHECK
CHECK ( PARAMETER_
MODE IN ( 'IN', 'OUT', 'INOUT', 'NONE' ) ),
PARAMETER_NAME INFORMATION_SCHEMA.SQL_IDENTIFIER,
PARAMETER_DEFAULT INFORMATION_SCHEMA.CHARACTER_DATA,
CONSTRAINT PARAMETERS_PRIMARY_KEY
PRIMARY KEY ( SPECIFIC_CATALOG, SPECIFIC_SCHEMA,
SPECIFIC_NAME, ORDINAL_POSITION ),
CONSTRAINT PARAMETERS_FOREIGN_KEY_SCHEMATA
FOREIGN KEY ( SPECIFIC_CATALOG, SPECIFIC_SCHEMA )
REFERENCES SCHEMATA
)
Description
1) The values of SPECIFIC_CATALOG, SPECIFIC_SCHEMA, and SPECIFIC_
NAME are the catalog name, unqualified schema name, and
qualified identifier of the specific name of the routine that
contains the parameter being described.
2) The value of ORDINAL_POSITION is the ordinal position of the
parameter in the routine.
3) The values of PARAMETER_MODE have the following meanings:
IN The parameter being described is an input
parameter.
OUT The parameter being described is an output
parameter.
INOUT The parameter being described is an input parameter
and an output parameter.
Information Schema and Definition Schema 129
ISO/IEC JTC1/SC21 N8897
16.2 Definition Schema
NONE The parameter being described is neither an input
parameter nor an output parameter.
4) Case:
a) If <parameter name> was specified when the routine was
created, then the value of PARAMETER_NAME is that <parameter
name>.
b) Otherwise, the value of PARAMETER_NAME is the null value.
5) The value of PARAMETER_DEFAULT is null if the parameter
being described has no default value. Otherwise, the value of
PARAMETER_DEFAULT is a character representation of the default
value for the parameter that obeys the rules specified for
<default option> in Subclause 11.5, "<default clause>", in ISO
/IEC 9075:1992.
130 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
16.2 Definition Schema
16.2.5 MODULE_PRIVILEGES base table
Function
The MODULE_PRIVILEGES module has one row for each module privilege
descriptor. It effectively contains a representation of the module
privilege descriptors.
Definition
CREATE TABLE MODULE_PRIVILEGES
(
GRANTOR INFORMATION_SCHEMA.SQL_IDENTIFIER,
GRANTEE INFORMATION_SCHEMA.SQL_IDENTIFIER,
MODULE_CATALOG INFORMATION_SCHEMA.SQL_IDENTIFIER,
MODULE_SCHEMA INFORMATION_SCHEMA.SQL_IDENTIFIER,
MODULE_NAME INFORMATION_SCHEMA.SQL_IDENTIFIER,
PRIVILEGE_TYPE INFORMATION_SCHEMA.CHARACTER_DATA
CONSTRAINT MODULE_PRIVILEGES_TYPE_CHECK
CHECK ( PRIVILEGE_TYPE = 'EXECUTE' ),
IS_GRANTABLE INFORMATION_SCHEMA.CHARACTER_DATA
CONSTRAINT MODULE_PRIVILEGES_GRANTABLE_NOT_NULL NOT NULL
CONSTRAINT MODULE_PRIVILEGES_GRANTABLE_CHECK
CHECK ( IS_GRANTABLE IN ( 'YES', 'NO' ) ),
CONSTRAINT MODULE_PRIVILEGES_PRIMARY_KEY
PRIMARY KEY ( GRANTOR, GRANTEE, MODULE_CATALOG, MODULE_
SCHEMA,
MODULE_NAME, PRIVILEGE_TYPE ),
CONSTRAINT MODULE_PRIVILEGES_FOREIGN_KEY_TABLES
FOREIGN KEY ( MODULE_CATALOG, MODULE_SCHEMA, MODULE_
NAME )
REFERENCES MODULES,
CONSTRAINT MODULE_PRIVILEGES_GRANTOR_FOREIGN_KEY_USERS
FOREIGN KEY ( GRANTOR )
REFERENCES USERS,
CONSTRAINT MODULE_PRIVILEGES_GRANTEE_FOREIGN_KEY_USERS
FOREIGN KEY ( GRANTEE )
REFERENCES USERS
)
Description
1) The value of GRANTOR is the <authorization identifier> of the
user who granted module privileges, on the module identified
by MODULE_CATALOG, MODULE_SCHEMA, and MODULE_NAME, to the user
identified by the value of GRANTEE for the module privilege
being described.
Information Schema and Definition Schema 131
ISO/IEC JTC1/SC21 N8897
16.2 Definition Schema
2) The value of GRANTEE is the <authorization identifier> of some
user, or "PUBLIC" to indicate all users, to whom the module
privilege being described is granted.
3) The values of MODULE_CATALOG, MODULE_SCHEMA, and MODULE_NAME
are the catalog name, unqualified schema name, and qualified
identifier, respectively, of the module on which the privilege
being described has been granted.
4) The values of PRIVILEGE_TYPE have the following meanings:
EXECUTE The user has EXECUTE privilege on the module
identified by MODULE_CATALOG, MODULE_SCHEMA, and
MODULE_NAME.
5) The values of IS_GRANTABLE have the following meanings:
YES The privilege being described was granted WITH
GRANT OPTION and is thus grantable.
NO The privilege being described was not granted WITH
GRANT OPTION and is thus not grantable.
132 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
16.2 Definition Schema
16.2.6 ROUTINE_PRIVILEGES base table
Function
The ROUTINE_PRIVILEGES table has one row for each routine privilege
descriptor. It effectively contains a representation of the routine
privilege descriptors.
Definition
CREATE TABLE ROUTINE_PRIVILEGES
(
GRANTOR INFORMATION_SCHEMA.SQL_IDENTIFIER,
GRANTEE INFORMATION_SCHEMA.SQL_IDENTIFIER,
SPECIFIC_CATALOG INFORMATION_SCHEMA.SQL_IDENTIFIER,
SPECIFIC_SCHEMA INFORMATION_SCHEMA.SQL_IDENTIFIER,
SPECIFIC_NAME INFORMATION_SCHEMA.SQL_IDENTIFIER,
PRIVILEGE_TYPE INFORMATION_SCHEMA.CHARACTER_DATA
CONSTRAINT ROUTINE_PRIVILEGES_TYPE_CHECK
CHECK ( PRIVILEGE_TYPE IN ( 'EXECUTE' ) ),
IS_GRANTABLE INFORMATION_SCHEMA.CHARACTER_DATA
CONSTRAINT ROUTINE_PRIVILEGES_GRANTABLE_NOT_NULL NOT NULL
CONSTRAINT ROUTINE_PRIVILEGES_GRANTABLE_CHECK
CHECK ( IS_GRANTABLE IN ( 'YES', 'NO' ) ),
CONSTRAINT ROUTINE_PRIVILEGES_PRIMARY_KEY
PRIMARY KEY ( GRANTOR, GRANTEE, SPECIFIC_
CATALOG, SPECIFIC_SCHEMA,
SPECIFIC_NAME, PRIVILEGE_TYPE ),
CONSTRAINT ROUTINE_PRIVILEGES_FOREIGN_KEY_TABLES
FOREIGN KEY ( SPECIFIC_CATALOG, SPECIFIC_
SCHEMA, SPECIFIC_NAME )
REFERENCES ROUTINES,
CONSTRAINT ROUTINE_PRIVILEGES_GRANTOR_FOREIGN_KEY_USERS
FOREIGN KEY ( GRANTOR )
REFERENCES USERS,
CONSTRAINT ROUTINE_PRIVILEGES_GRANTEE_FOREIGN_KEY_USERS
FOREIGN KEY ( GRANTEE )
REFERENCES USERS
)
Description
1) The value of GRANTOR is the <authorization identifier> of the
user who granted routine privileges, on the routine identified
by SPECIFIC_CATALOG, SPECIFIC_SCHEMA, and SPECIFIC_NAME, to
the user identified by the value of GRANTEE for the routine
privilege being described.
2) The value of GRANTEE is the <authorization identifier> of some
user, or "PUBLIC" to indicate all users, to whom the routine
privilege being described is granted.
Information Schema and Definition Schema 133
ISO/IEC JTC1/SC21 N8897
16.2 Definition Schema
3) The values of SPECIFIC_CATALOG, SPECIFIC_SCHEMA, and SPECIFIC_
NAME are the catalog name, unqualified schema name, and
qualified identifier, respectively, of the routine on which
the privilege being described has been granted.
4) The values of PRIVILEGE_TYPE have the following meanings:
EXECUTE The user has EXECUTE privilege on the routine
identified by SPECIFIC_CATALOG, SPECIFIC_SCHEMA,
and SPECIFIC_NAME.
5) The values of IS_GRANTABLE have the following meanings:
YES The privilege being described was granted WITH
GRANT OPTION and is thus grantable.
NO The privilege being described was not granted WITH
GRANT OPTION and is thus not grantable.
134 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
17 Status codes
17.1 SQLSTATE
_____________Table_9-SQLSTATE_class_and_subclass_values____________
_CategoryCondition_______________Class__Subcondition_____________Subclass
| X | case not found for | 20 | (no subclass) | 000 |
case statement
| X | handler exception | 0C | (no subclass) | 000 |
| | | | | |
| | | | cannot undo a non- | 001 |
atomic compound
statement
| | | | cannot redo a non- | 002 |
| | | | atomic compound | |
|_______|_______________________|______|_statement_____________|____ |
| | | | | |
| | | | | |
| | | | | |
| | | | | |
Status codes 135
ISO/IEC JTC1/SC21 N8897
136 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
18 Conformance
18.1 Claims of conformance
Claims of conformance to this International Standard shall state:
1) Which level of conformance is claimed (as specified in ISO/IEC
9075:1992.
2) <More to be supplied as required.>
3) The definitions for all elements and actions that this Standard
specifies as implementation-defined.
18.2 Processing methods
This International Standard does not define the method by which an
<embedded SQL host program> is processed. Although the processing
of <embedded SQL host program> is defined in terms of derivation
of a program compliant with a programming language standard and a
<module>, implementations of SQL are not constrained to follow that
method, provided that effect is achieved.
Although the processing of <direct SQL statement> is defined in
terms of calls to <routine>s in a <module>, implementations of
Direct Invocation are not constrained to follow that method, so
long as the same effect is achieved.
18.3 Extensions and options
A conforming implementation may provide additional facilities or
options not specified by this Part of this International Standard.
This may imply an implementation-defined extension of the list of
reserved words (<reserved word>) and thereby may prevent proper
processing of some programs that otherwise meet the requirements of
this International Standard.
An implementation remains conforming even if it provides user
options to process nonconforming SQL language or to process
conforming SQL language in a nonconforming manner.
Conformance 137
ISO/IEC JTC1/SC21 N8897
18.4 Flagger requirements
18.4 Flagger requirements
Implementations that claim conformance to Entry SQL shall provide
an SQL Flagger (see Subclause 4.34, "SQL Flagger", in ISO/IEC
9075:1992) that supports the following "level of flagging" options:
- Entry SQL Flagging
- Intermediate SQL Flagging
and the following "extent of checking" option:
- Syntax Only
Implementations that claim conformance to Intermediate or Full SQL
shall provide an SQL Flagger (see Subclause 4.34, "SQL Flagger", in
ISO/IEC 9075:1992) that supports the following "level of flagging"
options:
- Entry SQL Flagging
- Intermediate SQL Flagging
- Full SQL Flagging
and the following "extent of checking" options:
- Syntax Only
- Catalog Lookup
138 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
Annex A
(Informative)
Implementation-defined elements
This Annex references those features that are identified in the
body of this International Standard as implementation-defined.
The term implementation-defined is used to identify characteristics
that may differ between implementations, but that shall be defined
for each particular implementation.
1) Subclause 9.1, "<routine invocation>": Whether or not SAI
can invoke <routine>s in a <module> with explicit <module
authorization identifier> MAI is implementation-defined, as
are any restrictions pertaining to such invocation.
2) Subclause 9.1, "<routine invocation>": The method and time of
binding of the designated external routine to the schema or
<module> containing the <routine> is implementation-defined.
3) Subclause 12.1, "<module>": If a <module> that contains a
<routine> is associated with an SQL-agent that is associated
with another <module> that contains a <routine> with the same
<procedure name>, then the effect is implementation-defined.
4) Subclause 11.3, "<routine>": There is an implementation-
defined package and character type for use in Ada bindings.
The precisions and scales of the Ada types for SQL data types
and the <exact numeric type> of indicator parameters are
implementation-defined.
5) Subclause 11.3, "<routine>": The number of bits in a C character
is implementation-defined.
6) Subclause 11.3, "<routine>": The number of bits contained in a
COBOL character is implementation-defined.
7) Subclause 11.3, "<routine>": The precision of a COBOL data type
corresponding to SQL INTEGER or SMALLINT is implementation-
defined.
8) Subclause 11.3, "<routine>": The number of bits contained in a
Fortran character is implementation-defined.
Implementation-defined elements 139
ISO/IEC JTC1/SC21 N8897
9) Subclause 11.3, "<routine>": The maximum length of the MUMPS
variable-length character type is implementation-defined.
10)Subclause 11.3, "<routine>": The number of bits contained in a
Pascal character is implementation-defined.
11)Subclause 11.3, "<routine>": The precision of a PL/I data type
corresponding to SQL INTEGER or SMALLINT is implementation-
defined.
12)Subclause 11.3, "<routine>": If the value of any input parameter
provided by the SQL-agent falls outside the set of allowed
values of the data type of the parameter, or if the value of any
output parameter resulting from the execution of the <routine>
falls outside the set of values supported by the SQL-agent for
that parameter, then the effect is implementation-defined.
13)Subclause 11.3, "<routine>": If the <module> that contains the
<routine> is associated with an SQL-agent that is associated
with another <module> that contains a <routine> with the same
<procedure name>, then the effect is implementation-defined.
14)Subclause 11.3, "<routine>": If no session is active when
the first <routine> in an SQL-session is called, and if the
<SQL procedure statement> of the <routine> is not a <connect
statement>, then an implementation-defined <connect statement>
is effectively executed to create an SQL-session and connect the
user to a default SQL-server.
15)Subclause 11.3, "<routine>": The number of permitted outstanding
asynchronous SQL-statements is implementation-defined.
16)Subclause 11.3, "<routine>": The null character that defines the
end of a C character string is implementation-defined.
17)Subclause 11.3, "<routine>": If the <routine> is an external
routine, then the method and time of binding of that external
routine to the schema or <module> containing the <routine> is
implementation-defined.
140 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
Annex B
(Informative)
Implementation-dependent elements
This Annex references those places where this International
Standard states explicitly that the actions of a conforming
implementation are implementation-dependent.
The term implementation-dependent is used to identify
characteristics that may differ between implementations, but that
are not necessarily specified for any particular implementation.
1) Subclause 9.1, "<routine invocation>": The result data item
supplied has an implementation-dependent value.
2) Subclause 9.1, "<routine invocation>": If a parameter has an
indicator parameter that specifies the parameter to be null,
then the value of the parameter itself is implementation-
dependent.
3) Subclause 9.1, "<routine invocation>": If the external function
is not a standard-conforming program, then the effect of the
<external function invocation> is implementation-dependent.
4) Subclause 12.1, "<module>": If the SQL-agent that performs a
call of a <routine> in a <module> is not a standard program in
hte language specified in the <language clause> of the <module>,
then the results are implementation-dependent.
5) Subclause 12.1, "<module>": If the <language clause> of a
<module> specifies ADA and the SQL-agent performs calls of
<routine>s in that <module> from more than one Ada task, then
the results are implementation-dependent.
6) Subclause 12.1, "<module>": After the execution of the last
<routine>, if an unrecoverable error has not occurred, and the
SQL-agent did not terminate unexpectedly, and no constraint
is not satisfied, then the choice of whether to perform a
<commit statement> or a <rollback statement> is implementation-
dependent. The determination of whether an SQL-agent has
terminated unexpectedly is implementation-dependent.
Implementation-dependent elements 141
ISO/IEC JTC1/SC21 N8897
7) Subclause 11.3, "<routine>": If there are more than one status
parameter, then the order in which values are assigned to these
status parameters is implementation-dependent.
8) Subclause 11.3, "<routine>": If the designated external routine
is not a program that conforms to the programming language
standard specified by the <external routine language clause>,
then the results are implementation-dependent.
9) Subclause 14.4, "<compound statement>": The variables, cursors,
routines, and temporary tables and view specified in a <local
declaration list> are created in an implementation-dependent
order.
10)Subclause 14.7, "<SQL variable declaration>": When a variable
associated with an <SQL variable declaration> is created and the
<SQL variable declaration> does not contain a <default clause>,
then the intial value of the variable is implementation-
dependent.
11)Subclause 14.13, "<for statement>": The <cursor name> used in
the transformation of a <for statement> into a <loop statement>
is implementation-dependent, as is the <SQL variable name> used
in the <loop statement> for getting diagnostics information.
142 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
Annex C
(Informative)
Deprecated features
It is intended that the following features will be removed at a
later date from a revised version of this International Standard:
No additional deprecated items.
Deprecated features 143
144 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
Annex D
(Informative)
Incompatibilities with X3.135-1992 and ISO/IEC 9075:1992
This International Standard introduces some incompatibilities
with the earlier version of Database Language SQL as specified
in ISO/IEC 9075:1992. Unless specified in this Annex, features
and capabilities of Database Language SQL are compatible with the
earlier version of this International Standard.
1) A number of additional <reserved word>s have been added to the
language. These <reserved word>s are:
- CALL
- DO
- ELSEIF
- EXCEPTION
- IF
- LEAVE
- LOOP
- OTHERS
- RESIGNAL
- RETURN
- RETURNS
- SIGNAL
- TUPLE
- WHILE
Incompatibilities with X3.135-1992 and ISO/IEC 9075:1992 145
Index
Index entries appearing in boldface indicate the page
where the word, phrase, or BNF nonterminal was defined;
index entries appearing in italics indicate a page where
the BNF nonterminal was used in a Format; and index
entries appearing in roman type indicate a page where
the word, phrase, or BNF nonterminal was used in a
heading, Function, Syntax Rule, Access Rule, General
Rule, Leveling Rule, Table, or other descriptive text.
- A - - C -
ABSTRACT_DATA_TYPE_CATALOGo CALLo13, 56, 83, 107, 145
109, 112, 115, 117, 120, <caller language clause>o
121, 122, 123, 124, 126, 7, 9, 50, 51, 52, 54,
127, 128 59, 61, 63
ABSTRACT_DATA_TYPE_NAMEo <call statement>o11, 66,
109, 112, 115, 117, 120, 83, 107
121, 122, 124, 126, 127, cannot redo a non-atomic
128 compound statemento64,
ABSTRACT_DATA_TYPE_SCHEMAo 135
109, 112, 115, 117, 120, cannot undo a non-atomic
121, 122, 123, 124, 126, compound statemento64,
127, 128 135
<alter module action>o77, case not found for case
78 statemento95, 135
<alter module statement>o <case statement>o12, 66,
66, 77 94, 95
<argument>o27, 29, 30, 31, <case statement else
33, 34, 36, 37, 103 clause>o94, 95
<argument list>o27, 29, CATALOG_NAMEo56, 110, 111,
30, 31, 33 113, 114, 115, 116, 118,
<assignment source>o92, 93 122
<assignment statement>o12, <character string literal>o
66, 92, 93, 107 88, 90
<assignment target>o92, 93 CHARACTER_MAXIMUM_LENGTHo
ATOMICo12, 13, 64, 85, 86, 109, 111, 115, 117, 119,
87 120, 121, 122, 124
- B - CHARACTER_OCTET_LENGTHo
<beginning label>o82, 85, 109, 111, 115, 117, 119,
86, 99, 100 120, 121, 122, 124
CHARACTER_SET_CATALOGo109,
111, 117
Index 1
ISO/IEC JTC1/SC21 N8897
CHARACTER_SET_NAMEo56, <declare cursor>o11, 15,
109, 111, 117 16, 19, 29, 81, 85, 86
CHARACTER_SET_SCHEMAo109, <default clause>o43, 86,
111, 117 91, 142
COLLATIONSo109, 112, 117, <default option>o10, 43,
123 44, 130
COLLATION_CATALOGo109, DEFINITION_SCHEMAo109,
111, 112, 115, 117, 119, 111, 112, 113, 114, 115,
120, 121, 122, 123, 124 116, 117, 118
COLLATION_NAMEo109, 112, DOo13, 100, 145
115, 117, 119, 120, 121, <domain name>o91
122, 123, 124 DOMAIN_CATALOGo109, 110,
COLLATION_SCHEMAo109, 112, 112, 123
115, 117, 119, 120, 121, DOMAIN_DEFAULTo109
122, 123, 124 DOMAIN_NAMEo109, 110, 112,
<colon>o17, 19, 85, 99, 123
100 DOMAIN_SCHEMAo109, 110,
COLUMNSo110, 111, 112, 123 112, 123
COLUMN_DEFAULTo111 <drop behavior>o44, 79
COLUMN_NAMEo109, 111, 112, <drop module statement>o9,
113, 115, 117, 119, 120, 11, 42, 66, 79, 107
122, 123, 124 <drop routine statement>o
COLUMN_PRIVILEGESo113 11, 44, 66, 107
<comma>o29, 39, 50, 88, - E -
91, 103 **Editor's Note**o8, 20,
<component reference>o92, 27, 29, 30, 31, 32, 33,
93 37, 44, 45, 52, 78, 87,
<compound statement>o7, 9, 93, 101, 103, 106
11, 12, 15, 16, 19, 22, ELSEIFo13, 96, 145
23, 24, 64, 66, 67, 81, <ending label>o85, 99, 100
82, 85, 86, 88, 89, 90, <equals operator>o92
107 EXCEPTIONo13, 48, 49, 54,
<constant or updatable>o91 55, 56, 77, 88, 90, 104,
<cursor name>o15, 16, 19, 106, 145
29, 77, 81, 86, 100, 142 <exception declaration>o
<cursor sensitivity>o100 48, 49, 85, 86, 88, 90,
<cursor specification>o81, 103, 105
100 <exception name>o15, 16,
- D - 48, 49, 77, 88, 89, 90,
<data type>o29, 39, 50, 103, 104, 105, 106
53, 54, 59, 60, 61, 62, <exception value>o16, 48,
91, 103 88, 89, 90
<data type list>o39 <exception value list>o88
DATA_TYPEo109, 111, 112, <external body reference>o
115, 117, 119, 120, 121, 7, 10, 51, 52
122, 123, 124, 126, 127, external function call
128 exceptiono36, 37
DATA_TYPE_DESCRIPTORo109, external function exception
112, 115, 117, 119, 122, o35
123 <external routine language
DATETIME_PRECISIONo109, clause>o35, 37, 51, 53,
111, 115, 117, 119, 120, 59, 63, 142
121, 122, 124 EXTERNAL_NAMEo115, 126,
127, 128
2 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
- F - <item reference>o17, 19,
<for loop variable name>o 81
100 - K -
<for statement>o12, 66, <key word>o13
86, 100, 107, 142 - L -
<function invocation>o21, LEAVEo13, 98, 145
23, 24 <leave statement>o12, 66,
- G - 98, 99
<general value specifica- <left paren>o29, 39, 50,
tion>o21 103
<global declaration>o48 <local declaration>o48,
GRANTEEo44, 109, 113, 114, 85, 86, 89
115, 116, 117, 118, 131, <local declaration list>o
132, 133 7, 15, 16, 85, 86, 87,
- H - 88, 142
HANDLERo13, 48, 49, 50, <local or schema qualifier>
88, 101 o15, 16, 30, 31, 52, 82
<handler declaration>o48, <local qualifier>o15, 16
49, 85, 88, 89, 103 LOOPo13, 99, 101, 145
handler exceptiono64, 135 <loop iteration scheme>o99
handler procedureo7, 8, 9, <loop statement>o12, 66,
33, 49, 62, 64, 65, 87, 86, 99, 142
88, 103, 104, 105 - M -
<member name>o39
- I - <module>o7, 9, 19, 29, 30,
<identifier>o15, 17, 53, 31, 35, 48, 49, 52, 53,
85, 100 54, 62, 63, 75, 76, 81,
IFo13, 96, 145 82, 88, 89, 90, 100,
<if statement>o12, 66, 96, 125, 137, 139, 140, 141
97 <module contents>o29, 30,
<if statement else clause>o 31, 48, 51, 62, 77
96, 97 <module contents
<if statement elseif designator>o77
clause>o96 <module name>o9, 42, 52,
<if statement then clause>o 54, 75, 76, 77, 79
96, 97 MODULESo114, 125, 126,
INFORMATION_SCHEMAo57, 127, 131
110, 113, 114, 116, 118, MODULE_AUTHORIZATIONo114,
119, 120, 125, 126, 129, 125
131, 133 MODULE_CATALOGo114, 115,
INTERVAL_CODEo109, 111, 116, 117, 118, 125, 126,
115, 117, 119, 120, 121, 127, 131, 132
122, 124 MODULE_DEFINITIONo114, 125
INTERVAL_PRECISIONo109, MODULE_NAMEo114, 115, 116,
111, 115, 117, 119, 120, 117, 118, 125, 126, 127,
121, 122, 124 131, 132
invalid SQLSTATE returnedo MODULE_PRIVILEGESo114,
36 116, 118, 131
IS_NULLABLEo111 MODULE_SCHEMAo114, 115,
IS_VARIANTo115, 126, 127, 116, 117, 118, 125, 126,
128 127, 131, 132
<item qualifier>o17
Index 3
ISO/IEC JTC1/SC21 N8897
- N - - R -
<non-reserved word>o13 REDOo13, 50, 64, 65
<null specification>o92 <reserved word>o13, 137,
null value not allowedo36 145
NULL_CLASS_CATALOGo109, RESIGNALo13, 48, 105, 145
112 <resignal statement>o8,
NULL_CLASS_NAMEo109, 112 11, 12, 64, 65, 66, 105
NULL_CLASS_SCHEMAo109, 112 <result cast>o36, 37, 50,
NUMERIC_PRECISIONo109, 59, 62
111, 115, 117, 119, 120, RETURNo13, 84, 145
121, 122, 124 RETURNSo13, 50, 84, 145
NUMERIC_PRECISION_RADIXo <returns clause>o50, 51,
109, 111, 115, 117, 119, 62
120, 121, 122, 124 <returns data type>o32,
NUMERIC_SCALEo109, 111, 33, 34, 36, 37, 50, 53,
115, 117, 119, 120, 121, 59, 60, 61, 63
122, 124 <return statement>o12, 66,
- O - 84, 107
<object name>o38 <return value>o84
OBJECT_CATALOGo109, 112, <right paren>o29, 39, 50,
115, 117, 119, 122, 123 103
OBJECT_NAMEo109, 112, 115, <routine>o7, 9, 10, 11,
117, 119, 122, 123, 124 16, 17, 19, 21, 27, 29,
OBJECT_SCHEMAo109, 112, 30, 31, 32, 33, 34, 35,
115, 117, 119, 122, 123 36, 37, 41, 48, 49, 50,
OBJECT_TYPEo109, 112, 113, 51, 52, 53, 54, 58, 59,
115, 117, 119, 120, 122, 62, 63, 64, 66, 81, 83,
123, 124 84, 85, 86, 89, 92, 100,
ORDINAL_POSITIONo109, 111, 137, 139, 140, 141
112, 113, 115, 117, 119, <routine body>o7, 9, 10,
120, 122, 123, 124, 129 29, 50, 51, 128
OTHERSo13, 145 <routine header>o50, 51
- P - <routine header terminator>
<parameter declaration>o7, o50, 62
10, 19, 32, 33, 36, 37, <routine invocation>o9,
39, 50, 52, 54, 58, 59, 10, 21, 23, 29, 31, 32,
60, 61, 62, 81, 86 33, 35, 37, 43, 83
<parameter list>o17, 30, <routine name>o7, 9, 10,
32, 33, 50, 51, 52, 53, 15, 16, 17, 18, 29, 30,
58, 59, 63, 77 35, 37, 39, 48, 50, 53,
<parameter mode>o50, 58, 59, 63, 77, 83, 86
59, 60, 61 ROUTINESo115, 117, 123,
PARAMETERSo117, 123, 129 126, 127, 129, 133
<parameter style>o51, 59 <routine specification>o50
PARAMETER_DEFAULTo117, ROUTINE_BODYo115, 126,
129, 130 127, 128
PARAMETER_MODEo117, 129 ROUTINE_CATALOGo115, 126,
PARAMETER_NAMEo117, 129, 127
130 ROUTINE_DEFINITIONo115,
<positional arguments>o29 126, 128
<procedure type>o50, 62 ROUTINE_NAMEo115, 126, 127
ROUTINE_SCHEMAo115, 126,
127
4 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
ISO/IEC JTC1/SC21 N8897
ROUTINE_TYPEo115, 123, <SQL schema manipulation
126, 128 statement>o66
- S - <SQL-server module
<schema element>o9, 41, 51 definition>o9, 11, 41,
SCHEMATAo111, 114, 115, 66, 75, 76, 107
122, 123, 125, 126, 129 <sqlstate list>o88
SCHEMA_NAMEo56, 111, 114, <SQL statement list>o64,
115 65, 85, 87, 94, 95, 96,
SCHEMA_OWNERo111, 114, 115 97, 99, 100, 101
<search condition>o44, 94, <SQL variable declaration>o
95, 96, 97, 99 19, 43, 48, 81, 85, 86,
<searched case statement>o 91, 142
94 <SQL variable name>o15,
<searchede case statement 16, 17, 19, 34, 48, 49,
when clause>o94 86, 91, 92, 100, 142
<semicolon>o50, 85 <SQL variable name list>o
SIGNALo13, 103, 145 91
<signal argument list>o103 SQLWARNINGo13, 88, 101
<signal statement>o11, 12, SQL_IDENTIFIERo119, 120,
66, 103, 104 125, 126, 129, 131, 133
<simple case operand 1>o94 <statement label>o15, 16,
<simple case operand 2>o94 17, 18, 19, 85, 86, 98
<simple case statement>o94 <status parameter>o29, 50,
<simple case statement when 53, 58, 59
clause>o94
<specific name>o15, 39, - T -
50, 52, 53 TABLE_CATALOGo111, 112,
<specific routine 113, 123
designator>o38, 39, TABLE_NAMEo111, 112, 113,
44, 88 123
SPECIFIC_CATALOGo115, 116, TABLE_SCHEMAo111, 112,
117, 118, 123, 126, 127, 113, 123
129, 133, 134 <target specification>o21,
SPECIFIC_NAMEo115, 117, 32, 37, 58, 59, 92
123, 126, 127, 129, 133, <temporary abstract data
134 type declaration>o16,
SPECIFIC_SCHEMAo115, 117, 86
123, 126, 127, 129, 133, <temporary table
134 declaration>o9, 15,
<SQL control statement>o 16, 82, 85, 86
12, 66, 67 <temporary view decla-
<SQL diagnostics statement> ration>o15, 82, 85,
o66 86
<SQL executable statement>o <terminated local
66 declaration>o85
SQLEXEPTIONo13 <terminated SQL statement>o
<SQL procedure statement>o 85
10, 11, 18, 51, 58, 59, TUPLEo13, 145
64, 65, 66, 67, 85, 87, - U -
98, 140 UNDOo13, 50, 64, 65
<SQL routine body>o51, 62, USERo109, 111, 113, 114,
84 115, 116, 117, 118, 122,
<SQL schema definition 125, 131, 133
statement>o51, 66
Index 5
ISO/IEC JTC1/SC21 N8897
<user-defined updatability
method>o23
- V -
<value expression>o29, 84,
92, 94
<variant attribute>o51, 59
- W -
WHILEo13, 99, 101, 145
6 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM)
