From melton@cookie.enet.dec.com Tue Aug 16 19:47:34 1994 Return-Path: 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: <9408162320.AA24398@us3rmc.bb.dec.com> Date: Tue, 16 Aug 94 16:20:48 PDT From: Life is just a bowl of queries. 16-Aug-1994 1720 -0600 To: len@cookie.enet.dec.com Apparently-To: gallagher@ecf.ncsl.nist.gov 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 s ..................................... 7 4.1.1s ................................................. 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 and ....................................13 5.2 Names and identifiers ......................................15 6 Scalar expressions ...........................................17 6.1 ...........................................17 6.2 and ...........21 6.3 ..................................22 7 Query expressions ............................................23 7.1 ......................................23 7.2 .........................................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 .......................................29 9.2 ...............................................38 9.3 ..............................39 10 Schema definition and manipulation ...........................41 10.1 ........................................41 10.2 ....................................42 10.3 ...........................................43 10.4 ...................................44 11 Persistent stored modules ....................................47 11.1 Introduction ...............................................47 11.2 ...................................................48 11.3 ..................................................50 11.4 ..................................66 11.5 Data type correspondences ..................................68 12 Persistent SQL module maintenance ............................75 12.1 Introduction ...............................................75 12.2 .............................75 12.3 ...................................77 12.4 ....................................79 13 Data manipulation ............................................81 13.1 ...........................................81 13.2 ..............................82 14 Control statements ...........................................83 14.1 Introduction ...............................................83 14.2 ...........................................83 14.3 .........................................84 14.4 .......................................85 14.5 ......................................88 14.6 ....................................90 14.7 .................................91 14.8 .....................................92 14.9 ...........................................94 14.10 .............................................96 14.11 ..........................................98 14.12 ...........................................99 14.13 ............................................100 14.14 .........................................103 Table of Contents iii ISO/IEC JTC1/SC21 N8897 14.15 .......................................105 15 Diagnostics management .......................................107 15.1 ................................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 , 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). 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: 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. 3.3 Conventions The conventions used in this part of this International Standard are identical to those described in ISO/IEC 9075-1. 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 s 4.1.1.1 s A (Subclause 4.4, "Routines") may be contained either in a schema or in a . It may be a function, which returns a value, or a procedure, which does not return a value. A consists of a , a sequence of s, and a . A is either an SQL-invoked or an externally- invoked , depending on its . Additionally, it is either an SQL or an external , depending on whether its is an SQL- statement or is an . A parameter (Subclause 4.18, "Parameters", in ISO/IEC 9075:1992) is an SQL-supplied parameter if it is declared in an SQL-invoked , 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 can be a . The SQL-statements include assignment statements and flow of control statements; a may include a . 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 is executed. The effect of | |the 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 in a . A compound statement local temporary table is effectively materialized the first time it is referenced during an execution of the , and it persists for that execution. 4.3 Modules A may also be an SQL-server . SQL-server s are created by and (for schemas that contain s). SQL- server s are destroyed by the and . An is a and has a that is a . The s of an SQL-server are invoked by SQL s or s. The of an SQL-server specifies LANGUAGE SQL. A module descriptor also contains the text of the . 4.4 Routines A is a subprogram. A consists of a , a sequence of s, and a . A is either a component of a or an object in a schema. A is either a function, which returns a value, or a procedure, which does not return a value. A is either an SQL-invoked or an externally- invoked , depending on whether the of the specifies "SQL" or some standard programming language. An SQL-invoked (function or procedure) is invoked from within SQL. An externally-invoked Concepts 9 ISO/IEC JTC1/SC21 N8897 4.4 Routines (function or procedure) is invoked by "call" statements in compilation units of the specified standard programming language. A is either an SQL or an external . An SQL is a whose is an . An external is a whose is an that identifies a routine body provided externally as a program written in a standard programming language. An SQL-invoked may be either an SQL or an external . An externally-invoked must be an SQL . An invocation of a specifies the of the and supplies a sequence of argument values corresponding to the s of the . Such invocation causes the to be executed. Different s can have the same . This is referred to an overloading the . Overloading is allowed only for 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 . If a is contained in a 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 ; - 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 , 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 is a , then the active condition is dependent on the exception handler specified in the . 4.6 Diagnostics area The diagnostics area is emptied during the execution of a . Information is added to the diagnostics area during the execution of a . 4.7 Cursors For every in a , a cursor is effectively created each time the 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: - - - - The following are the SQL-control statements: - Concepts 11 ISO/IEC JTC1/SC21 N8897 4.8 SQL-statements -  2 list entries deleted - - - - - - - The following are also SQL-diagnostics statements: - - 4.8.2 SQL-statements and transaction states The 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 s that specify ATOMIC is atomic. If an causes the evaluation of a and there is no current SQL-transaction, then an SQL-transaction is initiated before evaluation of the . 12 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM) ISO/IEC JTC1/SC21 N8897 5 Lexical elements 5.1 and Function Specify lexical units (tokens and separators) that participate in SQL language. Format ::= | ::= ATOMIC ::= 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 and 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 ::= !! All alternatives from ISO/IEC 9075:1992 | ::= !! All alternatives from ISO/IEC 9075:1992 | ::= ::= ::= ::= Syntax Rules 1) If a TN with a QI does not contain a , then if TN is simply contained in a CS whose contains a or TT whose declared local name is equal to QI, then the of CS is the implicit of TN. 2) If a
TN with a QI contains a LSQ, then if TN is contained in a CS whose is equal to LSQ, then the of CS shall contain a or TT whose declared local name is equal to QI. 3) If a CN with a QI does not contain a , then if CN is simply contained in a CS whose contains a whose declared local name is equal to QI, then the of CS is the implicit of CN. Lexical elements 15 ISO/IEC JTC1/SC21 N8897 5.2 Names and identifiers 4) If a CN with a QI contains a LQ, then if CN is contained in a CS whose is equal to LQ, then the of CD shall contain a whose declared local name is equal to QI. 5) If an ADTN with a QI does not contain a , then if ADTN is simply contained in a CS whose contains a whose declared local name is equal to QI, or a in a whose is equal to QI, then the of CS is the implicit of ADTN. 6) If an ADTN with a QI contains a LSQ, then if ADTN is simply contained in a CS whose is equal to LSQ, then the of CS shall contain a whose declared local name is equal to QI, or a in a whose is equal to QI. Access Rules No additional Access Rules. General Rules 1) An identifies an SQL variable. 2) An identifies an exception and corresponding . 3) If more than one uses the same , then the s shall be considered equivalent. 4) A identifies a . 16 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM) ISO/IEC JTC1/SC21 N8897 6 Scalar expressions 6.1 Function Reference a parameter or an SQL variable. Format ::= !! All alternatives from ISO/IEC 9075:1992 | ::= !! All alternatives from ISO/IEC 9075:1992 | Syntax Rules 1) Let IR be an and let IN be the contained in IR. 2) If IN is a that simply contains a , then IR shall not contain an . 3) If IR contains an IQ, then IR shall appear within the scope of one or more exposed
s, s, s, or that are equal to IQ. If there is more than one such exposed
, , , or , 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 , , or is IN. b) If V is a
in a JT, then IN shall not be a common column name in JT. Note: "Common column name" is defined in Subclause 7.5, "", in ISO/IEC 9075:1992. 4) If IR does not contain an , then IR shall be contained within the scope of one or more exposed
s, s, s or s whose associated tables, s, or s include a column, parameter, or variable whose is IN. Let the phrase possible qualifiers Scalar expressions 17 ISO/IEC JTC1/SC21 N8897 6.1 denote those exposed
s, s, s, ans s. a) Case: i) If the most local scope contains exactly one possible qualifier, then the qualifier IQ equivalent to that unique exposed
, , , or is impliict. ii) If there is more than one possible qualifier with the most local scope, then: 1) Each possible qualifier shall be a
or a of a
that is direction contained in a JT. 2) CN shall be a common column name in JT. Note: "Common column name" is defined in Subclause 7.5, "", 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
: (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
or , 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 , then every underlying column of R is an underlying column of IR. Note: The underlying columns of a are defined in Subclause 7.9, "", in ISO /IEC 9075:1992. ii) If IR is contained in a
TE or a TE or a TE immediately containing an or and the scope of IQ is some , , , , or
that contains TE, then CR is an outer reference to the table associated with Q. b) If V is a , 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 reference. If R is an SQL-supplied parameter, then IR is an SQL parameter reference. c) If V is a , then IR is an SQL variable reference. Let R be the variable of V identified by IN. 6) An that is a shall simply contain a if and only if the shall be specified only if the is a host parameter reference. The of the 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 , then let OS be the s in the containing that specify the of the . For each O in OS: Case: i) If O is contained in one or more s that contain an whose is IR, then IR denotes the SQL variable in the innermost such . ii) Otherwise, O shall be contained in a that contains a whose is IR, and IR denotes that parameter. b) Otherwise, IR shall be contained in one or more s or s that contain an or whose or is IR. IR denotes that parameter or SQL variable in the innermost such or . Scalar expressions 19 ISO/IEC JTC1/SC21 N8897 6.1 ____________________________________________________________________ **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 and 6.2 and Function Specify one or more values, parameters, or variables. Format ::= !! All alternatives from ISO/IEC 9075:1992 | ::= Syntax Rules 1) If a is specified, then the subject routine shall be a function. Note: The candidate s of a are defined in Subclause 9.1, "". Access Rules None. General Rules None. Scalar expressions 21 ISO/IEC JTC1/SC21 N8897 6.3 6.3 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 causes the evaluation of one or more s, then all such evaluations are effectively performed simultaneously. The time of evaluation of the 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 Function Specify a table derived from the result of a
. Format ::= !! All alternatives from ISO/IEC 9075:1992 | Syntax Rules 1) If a is specified, then the subject routine shall be a function. Access Rules No additional Access Rules. General Rules 1) If a is specified, then for each row inserted into TQS by an , Case: a) If a is specified in the , then the function identified by is invoked. b) Otherwise, the , , , or specified in the is executed. 2) If a is specified, then for each row deleted from TQS by a or a , Case: a) If a is specified in the , then the function identified by is invoked. Query expressions 23 ISO/IEC JTC1/SC21 N8897 7.1 b) Otherwise, the , , , or specified in the is executed. 3) If a is specified, then for each row updated in TQS by an or an , Case: a) If a is specified in the , then the function identified by is invoked. b) Otherwise, the , , , or specified in the is executed. 24 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM) ISO/IEC JTC1/SC21 N8897 7.2 7.2 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 . Let n be the number of candidate routines. Let CAj be the j-th in the . 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 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 Function Invoke a routine. Format ::= ::= | ::= [ { }... ] ::= Syntax Rules 1) If an is a or and the of the is not part of a subtype family, or if the does not correspond with an input parameter, then the typeset for the contains a single member that is the data type of the . 2) Let RN be the of a RI, let QI be the of RN, and let NA be the number of s in the of RI. 3) The scope of the of a R is the of R. If R is simply contained in a whose contain a C and R contains an that references the of C, then the scope_of_the__of_R_includes_the_._____ **Editor's Note** The preceding Rule is based on the presumption that only a single in a can contain an 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 4) A R is a possibly candidate routine if the of the of R is equal to RN and Case: a) If R is contained in a 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 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 R is an effectively candidate routine if there is an effective for the of R and the of RI, as determined by the Syntax Rules of (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 , then | | | |Case: | | | |a) If RI is contained in a whose MC | | contains a possibly candidate routine, then the candidate | s of RI are the effectively candidate routines (if any) contained in MC. b) Otherwise: i) If RI is contained in a without an intervening , then let DP be the default SQL-path of the . If RI is contained in a without an intervening , then let DP be the default path of the . ii) Let Si be the i-th in DP. For every Si that contains at least one possibly candidate routine R2i, let RFi be the routine family of R2i. The candidate 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 ____________________________________________________________________ **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 LSQ, then | | | Case: a) If LSQ is "MODULE", then the candidate s of R are the effectively candidate routines (if any) contained in M. b) Otherwise, LSQ shall be the of a schema S. The candidate 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 of a . Let XAL be the set of all s XALi derived from AL by forming the extended Cartesian product of the 's typesets. The set XAL represents all possible combinations of most-specific data types present in AL when the s are evaluated. For each such XALi, the Syntax Rules of Subclause 8.1, "Subject routine determination", are applied to the candidate s and operands of XALi, yielding a set of candidate subject s. Case: a) If RN contains a or RI is contained in a whose contains a possibly condidate routine R2, then for each XALi, there shall be at most one candidate subject in the set. The set of subject s is the set of candidate subject s. b) Otherwise, for each XAi , Case: i) If there is at most one candidate subject in the set, then select that routine (if any). ii) If there are more than one candidate subject in the set, then select from the set of candidate subject s a routine SR such that there is no other routine SR2 for which the of the schema that contains SR2 precedes in DP the of the schema that contains SR. Let the set of subject s be the set of all selected candidate subject s. Additional common elements 31 ISO/IEC JTC1/SC21 N8897 9.1 11)For each subject SR, let SRPL be the of SR and let SREAL be EAL(SR). For each PDi in SRPL that is an output parameter, if SREALi is supplied, then SREALi shall be a . Note: "Supplied" arguments are defined in (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 s shall be functions. | | | 13|For any subject that is not an iterable subject | |routine, its effective returns data type is its . | 14)Case: a) If there is a single identified subject , then the data type of the is the effective returns data type of that . b) Otherwise, let Ri be the effective returns data type for the identified subject for each XALi. All data types RTi shall be members of the same subtype family. The data type of the is the most-specific single data type that is a supertype of all of the RTi. 15)If any subject is a function, then: a) All of the identified subject s shall be functions. b) For any subject that is not an iterable subject routine, its effective returns data type is its . c) Case: i) If there is a single identified subject , then the data type of the is the effective returns data type of that . ii) Otherwise, let RTi be the effective returns data type for the identified subject for each XALi. All data types RTi shall be members of the same subtype family. The data type of the 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 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 .__________________________________________________________ **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 contained in the | | of the to obtain a value. | | | 2) Let PL be the evaluated s, and let XALj be the member of XAL whose s have the same data types as PL in the same order. The subject to be invoked is the one yielded for XALj by the Syntax Rules of this Subclause, and it has effective RTj. 3) If the is not an iterable subject , then let N be the number of s of the , and for i ranging from 1 to N, let PVi be the value of the i-th , Ti be the data type of the i-th of the of the subject , 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 is an SQL routine, then: Case: a) If the is not an iterable subject , then invoke the subject and supply the values CPVi as an argument value list. If the subject is a function, then let R be the returned value. Additional common elements 33 ISO/IEC JTC1/SC21 N8897 9.1 b) Otherwise, i) Let AV be the value of the single . ii) Let :ARGUMENT and :RESULT be implementation-dependent s different from each other and from the of any other variable with overlapping scope.______________________________________________________________ The Editor does not understand why the preceding Rule is required in a form that depends on s. Help is requested in_"splitting"_this_Rule_for_SQL/PSM_versus_SQL3.___________________ | | 5) If t|e subject is an external routine that specified | PARAMETER STYLE SQL, then: a) Let N be the number of 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 is a procedure, then let RN be 0. If the subject is a function, then let RT be the . 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 vi) If the subject 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 identifies an external routine. The method and time of binding of the designated external routine to the schema or containing the is implementation-defined. f) If the subject 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 with the argument value list. h) If the specifies ADA (respectively C, COBOL, FORTRAN, MUMPS, PASCAL, PLI) and the external routine designated by the is not a standard Ada program (respectively C, COBOL, Fortran, MUMPS, Pascal, PL/I program), then the effect of the 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 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 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 contains a , then let RT be the of the subject . Let R be the result of CAST ( R1 AS RT ) ii) Otherwise, let R be R1. l) If the i-th of the subject 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 is an external routine that specified PARAMETER STYLE GENERAL, then: a) Let N be the number of s. b) If any of the 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 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 f) Invoke the external routine of the subject with the argument value list. g) If the specifies ADA (respectively C, COBOL, FORTRAN, MUMPS, PASCAL, PLI) and the external routine designated by the is not a standard Ada program (respectively C, COBOL, Fortran, MUMPS, Pascal, PL/I program), then the effect of the 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 is a function, then let R1 be the returned value of the external routine. Case: i) If the subject contains a , then let RT be the of the subject . Let R be the result of: CAST ( R1 AS RT ) ii) Otherwise, let R be R1. j) If the i-th of the subject is an output parameter, then the value of the i-th argument is the corresponding group of argument values. 7) For each of the subject that is an output parameter, let AV be the corresponding argument value and let TS be the of the corresponding ; 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 is a function, then R is the result of the_.___________________________________________ **Editor's Note** I expected to find Rules in this Subclause analogous to those in Subclause 12.3, "", and Subclause 20.1, "", and analogous to those formerly found in the now defunct Subclause 13.whatever, "". The Rules that I expected to find define the behavior of the 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 is a function, returning ___the_value._______________________________________________________ | | | Additional common elements 37 | ISO/IEC JTC1/SC21 N8897 9.2 9.2 Function Specify privileges. Format ::= !! All alternatives from ISO/IEC 9075:1992 | EXTERNAL ROUTINE Syntax Rules 1) If specifies EXTERNAL ROUTINE or MODULE, then shall specify EXECUTE; otherwise, EXECUTE shall not be specified. 2) If EXTERNAL ROUTINE is specified, then the routines identified by the 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 9.3 Function Specify a routine. Format ::= SPECIFIC | ::= [ ] ::= [ { }... ] Syntax Rules 1) If a SN is specified, then the identifies the routine whose is SN. 2) If a MN is specified, then: a) Let RN be the of MN and let RF be the routine family of RN. b) Case: i) If MN contains a , then for all i there shall be exactly one member routine in RF whose i-th specifies a that is identical to the i-th in the of MN. The identifies that routine. ii) Otherwise, there shall be exactly one routine in RF. The 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 Function Define a schema. Format ::= !! All alternatives from ISO/IEC 9075:1992 | | 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 10.2 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 of any module contained in S. The following 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 10.3 Function Specify the default for a column or domain. Format ::= !! All alternatives from ISO/IEC 9075:1992 | Syntax Rules 1) The subject data type of a is the data type specified in the descriptor identified by the containing . Access Rules No additional Access Rules. General Rules No additional General Rules. Schema definition and manipulation 43 ISO/IEC JTC1/SC21 N8897 10.4 10.4 Function Destroy an SQL routine. Format ::= DROP { PROCEDURE | FUNCTION } Syntax Rules 1) Let SR be the routine identified by the 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 of any view descriptor, the of any constraint descriptor, the 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 of the current SQL-session shall | be equal to the that owns the schema identified by the implicit or explicit 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 is effectively executed as though the 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 ____________________________________________________________________ **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 Persistent stored modules 47 ISO/IEC JTC1/SC21 N8897 11.2 11.2 Function Define a module. Format ::= !! All alternatives from ISO/IEC 9075:1992 | | DECLARE ::= !! All alternatives from ISO/IEC 9075:1992 | | | Syntax Rules 1) An shall precede in the text of the module any that references the of the . 2) An specified in shall precede in the text of the any reference to the of the . 3) An contained in an that is specified in shall be different from any other contained in any other that is specified in . 4) A variable declared by an that is specified in is referred to as a module variable. A module variable is associated with the module that is specified by a that specifies its . 5) If the does not specify a that is a specifying an of SQLEXCEPTION, then: a) Let EP be a different from the of any other that is a of the containing . b) The implicitly contains, preceding its first : HANDLER PROCEDURE EP() RESIGNAL ; 48 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM) ISO/IEC JTC1/SC21 N8897 11.2 DECLARE EP HANDLER FOR SQLEXCEPTION ; 6) If a specifies an , then an containing that shall precede in the text of the that . 7) A shall precede in the text of the any handler procedure trhat references the of the . Access Rules None. General Rules 1) Before the first time that an SQL-agent performs a call of a that references the of a module variable, that variable is created. 2) After the last time that an SQL-agent performs a call of a : 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 11.3 Function Define a routine. Format ::= [ CREATE | DECLARE ] ::= [ ] [ ] [ SPECIFIC ] [ ] ::= [ ] PROCEDURE | FUNCTION ::= HANDLER [ REDO | UNDO ] ::= [ [ { }... ] ] ::= [ ] [ ] | ::= SQLSTATE | SQLCODE ::= IN | OUT | INOUT ::= RETURNS [ ] ::= CAST FROM ::= ::= ::= 50 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM) ISO/IEC JTC1/SC21 N8897 11.3 ::= | ::= ::= EXTERNAL [ NAME ] [ ] [ ] ::= PARAMETER STYLE { SQL | GENERAL } ::= VARIANT | NOT VARIANT ::= Note: is the preferred form of . The 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 shall contain a if and only if the specifies FUNCTION. 2) Case: a) If is immediately contained in a or an , then CREATE shall be specified. b) If is immediately contained in a , then CREATE shall not be specified. c) Otherwise, neither CREATE nor DECLARE shall be specified. 3) A that specifies PROCEDURE is called a procedure. A that specifies FUNCTION is called a function. A (procedure or function) whose whose specifies SQL is called an SQL-invoked routine (procedure or function). A (procedure or function) whose does not specify SQL is called an externally-invoked routine (procedure or function). A (procedure or function) that specifies an is called an SQL routine (procedure or function). A (procedure or function) that specifies an body reference> is called an external routine (procedure or function). 4) Case: a) If a is contained in a , then it shall not specify a . A that is the same as the of the is implicit. b) If a is not contained in a , then if a is not specified, then LANGUAGE SQL is implicit. 5) In a PL, the of each that contains a shall be different from the of any other in PL that contains a . 6) If a R is an SQL-invoked routine that is simply contained in a or , then: a) Case: i) If R is simply contained in a , then let the subject schema name be the of that . ii) If R is simply contained in a that is not contained in a schema, then let the subject schema name be the implicit or explicit of the of that . b) If RN contains a , then it shall be the same as the subject . If RN does not contain a , then the subject is implicit. c) If is not specified, then an implementation- dependent__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 contains a , then that | | shall be the same as the subject . | | | e| If does not contain a , then the | |subject is implicit. | 52 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM) ISO/IEC JTC1/SC21 N8897 11.3 f) The schema identified by the explicit or implicit of the shall not include a routine descriptor whose specific name is . g) The shall not contain a . 7) If a is an externally-invoked routine, then: a) The shall be an . b) shall not be specified. c) If the is contained in a , then the of the of the shall be different from the of the of any other in the containing . d) If the is contained in a schema, then the of the of the routine shall be different from the of the of any other in that schema. e) The shall contain at least one , at most one that specifies SQLCODE, and at most one 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 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 is a function, then let the externally returned data type be the specified by the . Persistent stored modules 53 ISO/IEC JTC1/SC21 N8897 11.3 h) Any in a 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 in a 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 specifies ADA, then: i) The s of the are identified by the as if they were declared immediately within an Ada library unit package specification that has a name identical to the of the containing . 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 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 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 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 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 of indicator parameters. SQL_STANDARD shall contain no other declarations. 8) If the is a function, then shall not be specified. 9) If a is an SQL routine, then: a) Each in the shall contain a . b) Whether a is for an input parameter, an output parameter, or both is determined as follows: Case: i) A is an output parameter. ii) For every that is not a and for which a is not specified, if the is contained in a that is a function, then the parameter is an input parameter; otherwise, Case: 1) If the of a parameter is contained in a or a that is contained in , but it is not contained in a or a that is contained in , then the parameter is an input parameter. 2) If the of a parameter is contained in a or a that is contained in , but it is not contained in a or a that is contained in , then the parameter is an output parameter. 3) If the of a parameter is contained in a or a that is contained in and it is contained in a or a that is contained in 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 for which a is specified, Case: 1) If the is IN, then the parameter is an input parameter. The shall not be contained in a or a that is contained in . 2) If the is OUT, then the parameter is an output parameter. The shall not be contained in a or a that is contained in . 3) If the is INOUT, then the parameter is both an input parameter and an output parameter. 10)If a is an external routine, then: a) The shall not contain a . b) The implicit or explicit shall specify SQL. c) If an is not specified, then the of the is implicit. d) If is not specified, then PARAMETER STYLE SQL is implicit. e) If the is an abstract data type, then a of PARAMETER STYLE GENERAL shall not be specified. f) If a is not specified, then NOT VARIANT is implicit. g) The shall not specify SQL. h) If a is specified, then let R be some value of the specified in the and let RT be the . The following shall be valid according to the Syntax Rules of Subclause 6.10, "", in ISO/IEC 9075:1992. CAST ( R AS RT ) Persistent stored modules 59 ISO/IEC JTC1/SC21 N8897 11.3 i) If is not specified, then IN is implicit. j) If is IN or INOUT, then the parameter is an input parameter. If the 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 s. Let PN be the number of 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 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 is a predefined type, then the i-th group of effective parameter type list entries consists of the i-th . 2) If the i-th is some abstract data type ADTi, then the i-th group of effective parameter type list entries consists of the of the and the actual base types of the of ADTi. ii) Effective parameter type list entries (PN + UN) + 1 to (PN+UN)+RN are as follows: Case: 1) If the is a predefined type, then effective parameter list entry (PN + UN) + 1 is the OUT and the . 2) If the is some abstract data type ADTr, then effective parameter list entries (PN+UN)+1 to (PN+UN)+RN consist of the OUT and the actual base types of the 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 that is an exact numeric type with scale 0. The 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 iv) Effective parameter type list entry (PN + UN + RN + N + 1) + is a that is character string of length 5 and character set SQL_TEXT with 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 that is character string of implementation- defined length and character set SQL_TEXT with 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 that is character string of implementation- defined length and character set SQL_TEXT with 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 that is character string of implementation- defined length and character set SQL_TEXT with 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 s. Let PN be the number of 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 is a predefined type, then the i-th group of effective parameter type list entries consists of the i-th . 2) If the i-th is an abstract data type ADT, then the i-th group of effective parameter type list entries consists of the of the and the actual base types of the of ADTi. ii) If the external routine is a function, then the external routine will have an effective return type of . m) Depending on whether the 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 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 is a function, then if the contains a , let the externally returned data type be the contained in that ; otherwise, let the externally returned data type be the contained in the . o) Any in a 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 in a 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 that is an SQL-invoked routine shall not contain an that is an . 12)If is not immediately contained in a , then shall not be specified. 13)If a 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 , an , an d) SQL connection statement>, or an . Access Rules 1) If an external routine is contained in a , then the of the current SQL-session shall be equal to the that owns the schema identified by the implicit or explicit of the . 62 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM) ISO/IEC JTC1/SC21 N8897 11.3 General Rules 1) If the is an external routine, then a privilege descriptor is created that defines the EXTERNAL PRIVILEGES privilege on the to the of the schema or in which the appears. This privilege is grantable. The grantor for the privilege descriptor is set to the special grantor value "_SYSTEM". 2) If the is an external routine, then the method and time of binding of that external routine to the schema or containing the is implementation-defined. If the designated external routine is not a program that conforms to the programming language standard specified by the , then the results are implementation- dependent. 3) Case: a) If a is an SQL-invoked routine, then the rules for invocation of the are specified in Subclause 9.1, "". b) If a is an externally-invoked routine, then the rules for invocation of the are specified in Subclause 12.3, "", 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 . 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 , 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 . e) The name of the caller language of the routine is the in the directly contained in . f) If the routine is an external routine, then the external name of the routine is . 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 in the directly contained in . Persistent stored modules 63 ISO/IEC JTC1/SC21 N8897 11.3 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 is a handler procedure, then: a) Let C be the that specifies ATOMIC, then: i) An exception condition is raised: handler exception-cannot redo a non-atomic compound statement. ii) An implicit 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 that specifies ATOMIC, then: i) An exception condition is raised: handler exception-cannot undo a non-atomic compound statement. ii) An implicit 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 of C and any 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 is executed. 64 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM) ISO/IEC JTC1/SC21 N8897 11.3 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 of C and any 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 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 is executed. Persistent stored modules 65 ISO/IEC JTC1/SC21 N8897 11.4 11.4 Function Define all of the SQL-statements that are s. Format ::= !! All alternatives from ISO/IEC 9075:1992 | ::= !! All alternatives from ISO/IEC 9075:1992 | | ::= !! All alternatives from ISO/IEC 9075:1992 | | | ::= | | | | | | | | ::= !! All alternatives from ISO/IEC 9075:1992 | | | | Syntax Rules 1) An shall not be contained in an . 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 General Rules 1) An atomic execution context is active during execution of an that is not a . 2) If is not a and there is an active exception condition at the completion of an S, raised either by S or any triggered , 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 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, "". 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, "", for the definitions of externally-invoked routines and external routines. In the following tables, let P be , S be , L be , T be
| VIEW
| EXCEPTION Syntax Rules 1) Let M be the module identified by and let MN be the . 2) Let R be the module contents identified by and let RN be the , ,
, or of that module contents. 3) If R is a routine belonging to a routine family, then shall be specified. A unique routine in that routine family shall be identified. Access Rules 1) The of the current SQL-session shall be equal to the that owns the schema identified by the of the module identified by MN. General Rules 1) If the contains ADD, then the is added at the end of the text of the in the descriptor of M. 2) If the contains ALTER, then the module contents R replaced in the descriptor of M by the new specified. Persistent SQL module maintenance 77 ISO/IEC JTC1/SC21 N8897 12.3 3) If the 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__in_the_Editor's_Notes.___________________ |___________________________________________________________________| | **Editor's Note** | |The ALTER MODULE statement doesn't specify consistency requriements| |for the ALTER suboption. See Possible Problem 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 12.4 Function Destroy a module. Format ::= DROP MODULE Syntax Rules 1) Let M be the module identified by and let MN be the . Access Rules 1) The of the current SQL-session shall be equal to the that owns the schema identified by the of the module identified by MN. General Rules 1) Let A be the current . The following is effectively executed with a current 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 Function Define a cursor. Format No additional Format items. Syntax Rules 1) If a is contained in a M and is not contained in a , then: a) For each O in the that specifies the declared in the , any contained in the shall be defined in either an of a that contains O or in a in the that contains O. b) For each in the the data types of the variables and parameters associated with each of the s that specify the shall be the same. Note: See the Syntax Rules of Subclause 11.2, "". Access Rules No additional Access Rules. General Rules No additional General Rules. Data manipulation 81 ISO/IEC JTC1/SC21 N8897 13.2 13.2 Function Declare a declared local temporary table. Format No additional Format items. Syntax Rules 1) Let TN be the
of a TTD, and let T be the of TN. Case: a) If TN contains a LSQ and TTD is contained in a , then LSQ shall be the same as the of the innermost that contains TTD. b) If TN does not contain a and TTD is contained in a , then the of the innermost that contains TTD is implicit. 2) If a is contained in a M with no intervening , then a) If TN contains a , then it shall be "MODULE". b) The of TN shall be different from the of the
of any other or that is contained in M with no intervening . 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 14.2 Function Invoke a procedure. Format ::= CALL Syntax Rules 1) A shall contain a whose candidate s are procedures. Note: The candidate s of a are defined in Subclause 9.1, "". Access Rules 1) The applicable privileges shall include the EXECUTE privilege on the identified by . General Rules 1) The is executed. Control statements 83 ISO/IEC JTC1/SC21 N8897 14.3 14.3 Function Return a value from an SQL function. Format ::= RETURN ::= Syntax Rules 1) shall be contained in an , and the shall be an SQL routine that is a function. 2) A shall be contained in a that is an SQL function. Let F be that . 3) The data type of the 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 . 84 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM) ISO/IEC JTC1/SC21 N8897 14.4 14.4 Function Specify a statement that groups other statements together. Format ::= [ ] BEGIN [ [ NOT ] ATOMIC ] [ ] [ ]  1 option deleted END [ ] ::= ::= ::= ::= ... ::= ... ::= ::= | | | | | | ::= Syntax Rules 1) If ATOMIC is specified, then the shall not contain a , nor shall it contain a that does not specify a . 2) If a has a , it is called a labeled . 3) If an is specified, then an identical shall be specified. Control statements 85 ISO/IEC JTC1/SC21 N8897 14.4 4) If a is specified, then it shall be different from all other s immediately contained in a , a , or a in the containing . 5) If neither ATOMIC nor NOT ATOMIC is specified, then NOT ATOMIC is implicit.  1 Rule deleted 6) If an explicit is not specified and the is the outermost of a then the of that is the implicit . 7) The scope of the is the . Note: The "declared local name" of an VD, a R, a DC, a TADTD, a TTD, a TVD, or an ED, in a is the , , , , or
immediately contained in VD, R, DC, TADTD, TTD, TVD, or ED. 8) If two s in a have the same declared local name, then both of those s shall be s. 9) If a CS is simply contained in a , then no in the of CS shall have a declared local name that is equal to the of any in the of the . Access Rules None. General Rules 1) An atomic execution context is active during the execution of a 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 are created in an implementation-dependent order. 3) If an specifies , then the value of that variable is set to the default value according to the General Rules of Subclause 11.5, " clause>" in ISO/IEC 9075:1992; otherwise, the value of the variable is implementation-dependent. 4) The s of the 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 s, then: a) Execution of all of the 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__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 and any s triggered by the execution of any such statement are canceled. 6) The variables, cursors, routines, temporary tables, and temporary views specified in are destroyed. Control statements 87 ISO/IEC JTC1/SC21 N8897 14.5 14.5 Function Associate a handler procedure with an exception or completion condition for a module or compound statement. Format ::= DECLARE HANDLER FOR ::= [ { }... ] ::= SQLSTATE [ VALUE ] | | SQLEXCEPTION | SQLWARNING | NOT FOUND ::= [ { }... ] Syntax Rules 1) If the is contained in a or M with no intervening , then any shall be different from thew of any other contained in M with no intervening . 2) Let R be the routine identified by . 3) The specified in an shall be an SQLSTATE value that conforms to the rules of Subclause 22.1, "SQLSTATE", in ISO/IEC 9075:1992. 4) The specified in an shall be defined by an in the scope of this declaration or in the same . 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 Access Rules None. General Rules 1) Case: a) If the is contained in a M with no intervening , then its scope is all s that are s of M. b) Otherwise, the is immediately contained in a and its scope is the innermost in which that is contained. 2) The associates R with the SQLSTATE values, , or SQLSTATE class value of each . 3) If R is associated with an and that 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 14.6 Function Declare an exception name and a corresponding SQLSTATE value. Format ::= DECLARE EXCEPTION [ FOR ] ::= SQLSTATE [ VALUE ] Syntax Rules 1) If the is contained in a M with no intervening , then the shall be different from the of any other in M. 2) The provided in an 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 14.7 Function Declare a variable. Format ::= DECLARE [ ] { | } [ ] ::= [ { }... ] Syntax Rules 1) If neither CONSTANT nor UPDATABLE is specified, then UPDATABLE is implicit. 2) For each in the : a) If CONSTANT is specified, then a shall be specified. b) The effective data type of the variable declared with a 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 is created, if the specifies , then the value of that variable is set to the default value according to the General Rules of Subclause 11.5, "" in ISO/IEC 9075:1992; otherwise, the value of the variable is implementation-dependent. Control statements 91 ISO/IEC JTC1/SC21 N8897 14.8 14.8 Function Assign a value to a attribute of an abstract data type or to a local variable. Format ::= SET ::= | ::= | Syntax Rules  1 rule deleted 1) The shall not be read-only. 2) If the is constant, then it shall identify an attribute and the shall be contained in a that is a constructor for the abstract data type instance containing the attribute identified by . 3) If the is a that identifies an attribute or is an , and is a , then the data type of the shall be assignable to the data type of the abstract data type attribute identified by . 4) The contained in the shall be a or . Access Rules None. 92 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM) ISO/IEC JTC1/SC21 N8897 14.8 General Rules 1) Case: a) If immediately contains an , then the result of the is given by applying the General Rules of (componentref\FULL), in ISO/IEC 9075:1992, with the_value_of__as_VALUE.__________________________ **Editor's Note** The preceding Rule refers to the SQL3 Subclause named "", 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 and SV be the value of the . 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 14.9 Function Select an execution path based on multiple cases. Format ::= | ::= CASE ... [ ] END CASE ::= CASE ... [ ] END CASE ::= WHEN THEN ::= WHEN THEN ::= ELSE ::= ::= Syntax Rules 1) If a specifies a , then let SCO1 be the : a) The data type of each SCO2 shall be comparable with the data type of SCO1. b) The is equivalent to a in which each specifies a 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 Access Rules None. General Rules 1) Case: a) If the of some in a is true, then the of the first (leftmost) whose is true is executed. b) If the simply contains a , then the of that 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 14.10 Function Provide conditional execution based on the truth value of a condition. Format ::= IF [ ... ] [ ] END IF ::= THEN ::= ELSEIF THEN ::= ELSE Syntax Rules 1) If one or more s are specified, then the is equivalent to an that does not contain ELSEIF by performing the following transformation recursively: IF [ . . . ] [ ] END IF is equivalent to IF ELSE IF THEN [ ... ] [ ] END IF END IF 96 CD 9075-4:199x SQL Part 4, Persistent Stored Modules (SQL/PSM) ISO/IEC JTC1/SC21 N8897 14.10 where is the directly contained in and is the directly contained in . Access Rules None. General Rules 1) If the immediately contained in the evaluates to true, then the immediately contained in the is executed and execution of the is terminated. 2) If an is specified, then the immediately contained in the is executed and execution of the is terminated. Control statements 97 ISO/IEC JTC1/SC21 N8897 14.11 14.11 Function Continue execution by leaving a block or loop statement. Format ::= LEAVE Syntax Rules 1) A L shall be contained within a labeled S that specifies a