(This is stale and you may wish to find a more up-to-date copy, but it is preserved here for posterity. Enjoy -- Daria 24 Nov 2017) Information Technology - Database Language SQL (Proposed revised text of DIS 9075) July 1992 (Second Informal Review Draft) ISO/IEC 9075:1992, Database Language SQL- July 30, 1992 Digital Equipment Corporation Maynard, Massachusetts Contents Page Foreword.........................................................xi Introduction.....................................................xiii 1 Scope ........................................................ 1 2 Normative references ......................................... 3 3 Definitions, notations, and conventions ...................... 5 3.1 Definitions ................................................ 5 3.1.1Definitions taken from ISO/IEC 10646 ....................... 5 3.1.2Definitions taken from ISO 8601 ............................ 5 3.1.3Definitions provided in this International Standard ........ 5 3.2 Notation ................................................... 7 3.3 Conventions ................................................ 9 3.3.1Informative elements ....................................... 9 3.3.2Specification of syntactic elements ........................ 9 3.3.3Specification of the Information Schema ....................10 3.3.4Use of terms ...............................................10 3.3.4Exceptions .................................................10 3.3.4Syntactic containment ......................................11 3.3.4Terms denoting rule requirements ...........................12 3.3.4Rule evaluation order ......................................12 3.3.4Conditional rules ..........................................13 3.3.4Syntactic substitution .....................................13 3.3.4Other terms ................................................14 3.3.5Descriptors ................................................14 3.3.6Index typography ...........................................15 3.4 Object identifier for Database Language SQL ................16 4 Concepts .....................................................19 4.1 Data types .................................................19 4.2 Character strings ..........................................20 4.2.1Character strings and collating sequences ..................20 4.2.2Operations involving character strings .....................22 4.2.2Operators that operate on character strings and return char- acter strings...............................................22 4.2.2Other operators involving character strings ................23 4.2.3Rules determining collating sequence usage .................23 4.3 Bit strings ................................................26 4.3.1Bit string comparison and assignment .......................27 4.3.2Operations involving bit strings ...........................27 4.3.2Operators that operate on bit strings and return bit strings ............................................................27 4.3.2Other operators involving bit strings ......................27 ii Database Language SQL 4.4 Numbers ....................................................27 4.4.1Characteristics of numbers .................................28 4.4.2Operations involving numbers ...............................29 4.5 Datetimes and intervals ....................................29 4.5.1Datetimes ..................................................30 4.5.2Intervals ..................................................32 4.5.3Operations involving datetimes and intervals ...............34 4.6 Type conversions and mixing of data types ..................34 4.7 Domains ....................................................35 4.8 Columns ....................................................36 4.9 Tables .....................................................37 4.10 Integrity constraints ......................................40 4.10.Checking of constraints ....................................41 4.10.Table constraints ..........................................41 4.10.Domain constraints .........................................43 4.10.Assertions .................................................43 4.11 SQL-schemas ................................................44 4.12 Catalogs ...................................................45 4.13 Clusters of catalogs .......................................45 4.14 SQL-data ...................................................45 4.15 SQL-environment ............................................46 4.16 Modules ....................................................46 4.17 Procedures .................................................47 4.18 Parameters .................................................47 4.18.Status parameters ..........................................47 4.18.Data parameters ............................................48 4.18.Indicator parameters .......................................48 4.19 Diagnostics area ...........................................48 4.20 Standard programming languages .............................49 4.21 Cursors ....................................................49 4.22 SQL-statements .............................................51 4.22.Classes of SQL-statements ..................................51 4.22.SQL-statements classified by function ......................52 4.22.Embeddable SQL-statements ..................................55 4.22.Preparable and immediately executable SQL-statements .......56 4.22.Directly executable SQL-statements .........................58 4.22.SQL-statements and transaction states ......................59 4.23 Embedded syntax ............................................61 4.24 SQL dynamic statements .....................................61 4.25 Direct invocation of SQL ...................................64 4.26 Privileges .................................................64 4.27 SQL-agents .................................................66 4.28 SQL-transactions ...........................................67 4.29 SQL-connections ............................................70 4.30 SQL-sessions ...............................................72 Table of Contents iii 4.31 Client-server operation ....................................74 4.32 Information Schema .........................................75 4.33 Leveling ...................................................75 4.34 SQL Flagger ................................................76 5 Lexical elements .............................................79 5.1 ...................................79 5.2 and ....................................82 5.3 ..................................................89 5.4 Names and identifiers ......................................98 6 Scalar expressions ...........................................107 6.1 ................................................107 6.2 and ...........114 6.3 ..........................................118 6.4 .........................................121 6.5 ...............................124 6.6 ...................................128 6.7 ....................................132 6.8 ..................................139 6.9 ..........................................141 6.10 .......................................144 6.11 .........................................155 6.12 .................................157 6.13 ..................................160 6.14 ................................165 6.15 ................................168 7 Query expressions ............................................173 7.1 ....................................173 7.2
..................................176 7.3
.........................................177 7.4 ..............................................178 7.5 .............................................180 7.6 .............................................185 7.7 ..........................................187 7.8 ............................................189 7.9 ......................................191 7.10 .........................................196 7.11 , , and
....203 8 Predicates ...................................................205 8.1 ................................................205 8.2 .....................................207 8.3 ........................................211 8.4 .............................................212 iv Database Language SQL 8.5 ...........................................214 8.6 ...........................................218 8.7 ..........................220 8.8 .........................................222 8.9 .........................................223 8.10 ..........................................224 8.11 .......................................227 8.12 .........................................229 9 Data assignment rules ........................................231 9.1 Retrieval assignment .......................................231 9.2 Store assignment ...........................................234 9.3 Set operation result data types ............................237 10 Additional common elements ...................................239 10.1 .......................................239 10.2 ..........................................243 10.3 ...............................................245 10.4 ..............................248 10.5 ...........................................251 10.6 and ...252 11 Schema definition and manipulation ...........................255 11.1 ........................................255 11.2 ....................................258 11.3
.........................................260 11.4 ........................................262 11.5 ...........................................266 11.6
..............................270 11.7 .............................272 11.8 ........................274 11.9 ..............................281 11.10 ....................................283 11.11 ....................................284 11.12 ..................................286 11.13 ................................287 11.14 ...............................288 11.15 ...................................289 11.16 ..........................291 11.17 .........................292 11.18 .....................................294 11.19 ..........................................296 11.20 ......................................300 11.21 ........................................301 11.22 ...................................304 11.23 ................................305 Table of Contents v 11.24 ...............................306 11.25 .........................307 11.26 ........................308 11.27 ....................................309 11.28 .................................311 11.29 .............................313 11.30 .....................................314 11.31 .................................318 11.32 ...................................320 11.33 ...............................323 11.34 .....................................325 11.35 .................................328 11.36 ..........................................329 11.37 .........................................333 12 Module .......................................................341 12.1 ...................................................341 12.2 .......................................344 12.3 ................................................346 12.4 Calls to a .....................................352 12.5 ..................................368 13 Data manipulation ............................................371 13.1 ...........................................371 13.2 ...........................................375 13.3 ..........................................377 13.4 ..........................................381 13.5
that identifies a view that is defined by a V, then is said to generally contain the contained in V. If contains , then generally contains . If generally contains and generally contains , then generally contains . An instance A1 of directly contains an instance B1 of if A1 contains B1 without an intervening or . Definitions, notations, and conventions 11 X3H2-92-154/DBL CBR-002 3.3 Conventions 3.3.4.3 Terms denoting rule requirements In the Syntax Rules, the term shall defines conditions that are required to be true of syntactically conforming SQL language. When such conditions depend on the contents of the schema, then they are required to be true just before the actions specified by the General Rules are performed. The treatment of language that does not conform to the SQL Formats and Syntax Rules is implementation- dependent. If any condition required by Syntax Rules is not sat- isfied when the evaluation of Access or General Rules is attempted and the implementation is neither processing non-conforming SQL language nor processing conforming SQL language in a non-conforming manner, then an exception condition is raised: syntax error or access rule violation (if this situation occurs during dynamic ex- ecution of an SQL-statement, then the exception that is raised is syntax error or access rule violation in dynamic SQL statement; if the situation occurs during direct invocation of an SQL-statement, then the exception that is raised is syntax error or access rule violation in direct SQL statement). In the Access Rules, the term shall defines conditions that are required to be satisfied for the successful application of the General Rules. If any such condition is not satisfied when the General Rules are applied, then an exception condition is raised: syntax error or access rule violation (if this situation occurs during dynamic execution of an SQL-statement, then the exception that is raised is syntax error or access rule violation in dynamic SQL statement; if the situation occurs during direct invocation of an SQL-statement, then the exception that is raised is syntax error or access rule violation in direct SQL statement). In the Leveling Rules, the term shall defines conditions that are required to be true of SQL language for it to syntactically conform to the specified level of conformance. 3.3.4.4 Rule evaluation order A conforming implementation is not required to perform the exact sequence of actions defined in the General Rules, but shall achieve the same effect on SQL-data and schemas as that sequence. The term effectively is used to emphasize actions whose effect might be achieved in other ways by an implementation. The Syntax Rules and Access Rules for contained syntactic elements are effectively applied at the same time as the Syntax Rules and Access Rules for the containing syntactic elements. The General Rules for contained syntactic elements are effectively applied be- fore the General Rules for the containing syntactic elements. Where the precedence of operators is determined by the Formats of this International Standard or by parentheses, those operators are ef- fectively applied in the order specified by that precedence. Where the precedence is not determined by the Formats or by parentheses, effective evaluation of expressions is generally performed from 12 Database Language SQL X3H2-92-154/DBL CBR-002 3.3 Conventions left to right. However, it is implementation-dependent whether ex- pressions are actually evaluated left to right, particularly when operands or operators might cause conditions to be raised or if the results of the expressions can be determined without completely evaluating all parts of the expression. In general, if some syn- tactic element contains more than one other syntactic element, then the General Rules for contained elements that appear earlier in the production for the containing syntactic element are applied before the General Rules for contained elements that appear later. For example, in the production: ::= the Syntax Rules and Access Rules for , , and are ef- fectively applied simultaneously. The General Rules for are applied before the General Rules for , and the General Rules for are applied after the General Rules for both and . If the result of an expression or search condition can be deter- mined without completely evaluating all parts of the expression or search condition, then the parts of the expression or search condi- tion whose evaluation is not necessary are called the inessential parts. If the Access Rules pertaining to inessential parts are not satisfied, then the syntax error or access rule violation exception condition is raised regardless of whether or not the inessential parts are actually evaluated. If evaluation of the inessential parts would cause an exception condition to be raised, then it is implementation-dependent whether or not that exception condition is raised. 3.3.4.5 Conditional rules Conditional rules are specified with "If" or "Case" conventions. Rules specified with "Case" conventions include a list of con- ditional sub-rules using "If" conventions. The first such "If" sub-rule whose condition is true is the effective sub-rule of the "Case" rule. The last sub-rule of a "Case" rule may specify "Otherwise". Such a sub-rule is the effective sub-rule of the "Case" rule if no preceding "If" sub-rule in the "Case" rule has a true condition. 3.3.4.6 Syntactic substitution In the Syntax and General Rules, the phrase "X is implicit" indi- cates that the Syntax and General Rules are to be interpreted as if the element X had actually been specified. In the Syntax and General Rules, the phrase "the following is implicit: Y" indicates that the Syntax and General Rules are to be interpreted as if a syntactic element containing Y had actually been specified. Definitions, notations, and conventions 13 X3H2-92-154/DBL CBR-002 3.3 Conventions In the Syntax Rules and General Rules, the phrase "former is equiv- alent to latter" indicates that the Syntax Rules and General Rules are to be interpreted as if all instances of former in the element had been instances of latter. If a BNF nonterminal is referenced in a Subclause without speci- fying how it is contained in a BNF production that the Subclause defines, then Case: - If the BNF nonterminal is itself defined in the Subclause, then the reference shall be assumed to be the occurrence of that BNF nonterminal on the left side of the defining production. - Otherwise, the reference shall be assumed to be to a BNF pro- duction in which the particular BNF nonterminal is immediately contained. 3.3.4.7 Other terms Some Syntax Rules define terms, such as T1, to denote named or unnamed tables. Such terms are used as table names or correlation names. Where such a term is used as a correlation name, it does not imply that any new correlation name is actually defined for the denoted table, nor does it affect the scopes of any actual correlation names. An SQL-statement S1 is said to be executed as a direct result of executing an SQL-statement if S1 is the SQL-statement contained in a that has been executed, or if S1 is the value of an referenced by an contained in a that has been executed, or if S1 was the value of the that was associ- ated with an by a and that same is referenced by an contained in a that has been executed. 3.3.5 Descriptors A descriptor is a conceptual structured collection of data that defines the attributes of an instance of an object of a specified type. The concept of descriptor is used in specifying the seman- tics of SQL. It is not necessary that any descriptor exist in any particular form in any database or environment. Some SQL objects cannot exist except in the context of other SQL objects. For example, columns cannot exist except in tables. Those objects are independently described by descriptors, and the de- scriptors of enabling objects (e.g., tables) are said to include the descriptors of enabled objects (e.g., columns or table con- straints). Conversely, the descriptor of an enabled object is said to be included in the descriptor of an enabling object. 14 Database Language SQL X3H2-92-154/DBL CBR-002 3.3 Conventions In other cases, certain SQL objects cannot exist unless some other SQL object exists, even though there is not an inclusion relation- ship. For example, SQL does not permit an assertion to exist if the tables referenced by the assertion do not exist. Therefore, an as- sertion descriptor is dependent on or depends on zero or more table descriptors (equivalently, an assertion is dependent on or depends on zero or more tables). In general, a descriptor D1 can be said to depend on, or be dependent on, some descriptor D2. There are two ways of indicating dependency of one construct on another. In many cases, the descriptor of the dependent construct is said to "include the name of" the construct on which it is de- pendent. In this case "the name of" is to be understood as meaning "sufficient information to identify the descriptor of"; thus an implementor might choose to use a pointer or a concatenation of , , etc. Alternatively, the descrip- tor may be said to include text (e.g., , ). In such cases, whether the implementation includes ac- tual text (with defaults and implications made explicit) or its own style of parse tree is irrelevant; the validity of the descriptor is clearly "dependent on" the existence of descriptors for objects that are referred to in it. The statement that a column "is based on" a domain, is equivalent to a statement that a column "is dependent on" that domain. An attempt to destroy a descriptor may fail if other descriptors are dependent on it, depending on how the destruction is specified. Such an attempt may also fail if the descriptor to be destroyed is included in some other descriptor. Destruction of a descriptor results in the destruction of all descriptors included in it, but has no effect on descriptors on which it is dependent. 3.3.6 Index typography In the Index to this International Standard, the following conven- tions are used: - Index entries appearing in boldface indicate the page where the word, phrase, or BNF nonterminal was defined; - Index entries appearing in italics indicate a page where the BNF nonterminal was used in a Format; and - Index entries appearing in roman type indicate a page where the word, phrase, or BNF nonterminal was used in a heading, Function, Syntax Rule, Access Rule, General Rule, Leveling Rule, Table, or other descriptive text. Definitions, notations, and conventions 15 X3H2-92-154/DBL CBR-002 3.4 Object identifier for Database Language SQL 3.4 Object identifier for Database Language SQL Function The object identifier for Database Language SQL identifies the characteristics of an SQL-implementation to other entities in an open systems environment. Format ::= ::= ::= iso | 1 | iso 1 ::= standard | 0 | standard 0 ::= 9075 ::= ::= <1987> | <1989> | <1992> <1987> ::= 0 | edition1987 0 <1989> ::= <1989 base> <1989 package> <1989 base> ::= 1 | edition1989 1 <1989 package> ::= | ::= 0 | IntegrityNo 0 ::= 1 | IntegrityYes 1 <1992> ::= 2 | edition1992 2 ::= | | ::= 0 | Low 0 ::= 1 | Intermediate 1 ::= 2 | High 2 16 Database Language SQL X3H2-92-154/DBL CBR-002 3.4 Object identifier for Database Language SQL Syntax Rules 1) An of shall not be specified unless the is specified as <1992>. 2) The value of identifies the level at which conformance is claimed as follows: a) If specifies <1992>, then Case: i) , then Entry SQL level. ii) , then Intermediate SQL level. iii) , then Full SQL level. b) Otherwise: i) , then level 1. ii) , then level 2. 3) A specification of <1989 package> as implies that the integrity enhancement feature is not implemented. A specification of <1989 package> as implies that the integrity enhancement feature is implemented. Definitions, notations, and conventions 17 X3H2-92-154/DBL CBR-002 18 Database Language SQL X3H2-92-154/DBL CBR-002 4 Concepts 4.1 Data types A data type is a set of representable values. The logical represen- tation of a value is a . The physical representation of a value is implementation-dependent. A value is primitive in that it has no logical subdivision within this International Standard. A value is a null value or a non-null value. A null value is an implementation-dependent special value that is distinct from all non-null values of the associated data type. There is effectively only one null value and that value is a member of every SQL data type. There is no for a null value, although the keyword NULL is used in some places to indicate that a null value is desired. SQL defines distinct data types named by the following s: CHARACTER, CHARACTER VARYING, BIT, BIT VARYING, NUMERIC, DECIMAL, INTEGER, SMALLINT, FLOAT, REAL, DOUBLE PRECISION, DATE, TIME, TIMESTAMP, and INTERVAL. Subclause 6.1, "", describes the semantic properties of each data type. For reference purposes, the data types CHARACTER and CHARACTER VARYING are collectively referred to as character string types. The data types BIT and BIT VARYING are collectively referred to as bit string types. Character string types and bit string types are collectively referred to as string types and values of string types are referred to as strings. The data types NUMERIC, DECIMAL, INTEGER, and SMALLINT are collectively referred to as exact numeric types. The data types FLOAT, REAL, and DOUBLE PRECISION are col- lectively referred to as approximate numeric types. Exact numeric types and approximate numeric types are collectively referred to as numeric types. Values of numeric type are referred to as numbers. The data types DATE, TIME, and TIMESTAMP are collectively referred to as datetime types. Values of datetime types are referred to as datetimes. The data type INTERVAL is referred to as an interval type. Values of interval types are called intervals. Each data type has an associated data type descriptor. The contents of a data type descriptor are determined by the specific data type that it describes. A data type descriptor includes an identifica- tion of the data type and all information needed to characterize an instance of that data type. Concepts 19 X3H2-92-154/DBL CBR-002 4.1 Data types Each host language has its own data types, which are separate and distinct from SQL data types, even though similar names may be used to describe the data types. Mappings of SQL data types to data types in host languages are described in Subclause 12.3, "", and Subclause 19.1, "". Not every SQL data type has a corresponding data type in every host language. 4.2 Character strings A character string data type is described by a character string data type descriptor. A character string data type descriptor con- tains: - the name of the specific character string data type (CHARACTER or CHARACTER VARYING; NATIONAL CHARACTER and NATIONAL CHARACTER VARYING are represented as CHARACTER and CHARACTER VARYING, respectively); - the length or maximum length in characters of the character string data type; - the catalog name, schema name, and character set name of the character set of the character string data type; and - the catalog name, schema name, and collation name of the colla- tion of the character string data type. Character sets fall into three categories: those defined by na- tional or international standards, those provided by implemen- tations, and those defined by applications. All character sets, however defined, always contain the character. Character sets defined by applications can be defined to "reside" in any schema chosen by the application. Character sets defined by stan- dards or by implementations reside in the Information Schema (named INFORMATION_SCHEMA) in each catalog, as do collations defined by standards and collations and form-of-use conversions defined by implementations. The SQL_TEXT specifies the name of a character repertoire and implied form-of- use that can represent every character that is in and all other characters that are in character sets supported by the implementation. 4.2.1 Character strings and collating sequences A character string is a sequence of characters chosen from the same character repertoire. The character repertoire from which the characters of a particular string are chosen may be specified explicitly or implicitly. A character string has a length, which is the number of characters in the sequence. The length is 0 or a positive integer. 20 Database Language SQL X3H2-92-154/DBL CBR-002 4.2 Character strings All character strings of a given character repertoire are mutu- ally comparable, subject to the restrictions specified in Table 3, "Collating sequence usage for comparisons". A collating sequence, also known as a collation, is a set of rules determining comparison of character strings in a particular char- acter repertoire. There is a default collating sequence for each character repertoire, but additional collating sequences can be defined for any character repertoire. Note: A column may be defined as having a default collating se- quence. This default collating sequence for the column may be different from the default collating sequence for its character repertoire, e.g., if the is specified in the . It will be clear from context when the term "default collating sequence" is used whether it is meant for a column or for a character repertoire. Given a collating sequence, two character strings are identical if and only if they are equal in accordance with the comparison rules specified in Subclause 8.2, "". The collat- ing sequence used for a particular comparison is determined as in Subclause 4.2.3, "Rules determining collating sequence usage". The s NATIONAL CHARACTER are used to specify a character string data type with a particular implementation-defined character repertoire. Special syntax (N'string') is provided for representing literals in that character repertoire. A character set is described by a character set descriptor. A char- acter set descriptor includes: - the name of the character set or character repertoire, - if the character set is a character repertoire, then the name of the form-of-use, - an indication of what characters are in the character set, and - the name of the default collation of the character set. For every character set, there is at least one collation. A colla- tion is described by a collation descriptor. A collation descriptor includes: - the name of the collation, - the name of the character set on which the collation operates, - whether the collation has the NO PAD or the PAD SPACE attribute, and - an indication of how the collation is performed. Concepts 21 X3H2-92-154/DBL CBR-002 4.2 Character strings 4.2.2 Operations involving character strings 4.2.2.1 Operators that operate on character strings and return character strings is an operator, |, that returns the char- acter string made by joining its character string operands in the order given. is a triadic function, SUBSTRING, that returns a string extracted from a given string according to a given numeric starting position and a given numeric length. Truncation occurs when the implied starting and ending positions are not both within the given string. is a pair of functions for converting all the lower case characters in a given string to upper case (UPPER) or all the upper case ones to lower case (LOWER), useful only in connection with strings that may contain s. is a function that invokes an installation- supplied form-of-use conversion to return a character string S2 derived from a given character string S1. It is intended, though not enforced by this International Standard, that S2 be exactly the same sequence of characters as S1, but encoded according some dif- ferent form-of-use. A typical use might be to convert a character string from two-octet UCS to one-octet Latin1 or vice versa. is a function that returns its first string ar- gument with leading and/or trailing pad characters removed. The second argument indicates whether leading, or trailing, or both leading and trailing pad characters should be removed. The third argument specifies the pad character that is to be removed. is a function for changing each charac- ter of a given string according to some many-to-one or one-to-one mapping between two not necessarily distinct character sets. The mapping, rather than being specified as part of the function, is some external function identified by a . For any pair of character sets, there are zero or more translations that may be invoked by a . A translation is described by a translation descriptor. A translation descriptor includes: - the name of the translation, - the name of the character set from which it translates, - the name of the character set to which it translates, and - an indication of how the translation is performed. 22 Database Language SQL X3H2-92-154/DBL CBR-002 4.2 Character strings 4.2.2.2 Other operators involving character strings returns the length of a given character string, as an integer, in characters, octets, or bits according to the choice of function. determines the first position, if any, at which one string, S1, occurs within another, S2. If S1 is of length zero, then it occurs at position 1 for any value of S2. If S1 does not occur in S2, then zero is returned. uses the triadic operator LIKE (or the inverse, NOT LIKE), operating on three character strings and returning a Boolean. LIKE determines whether or not a character string "matches" a given "pattern" (also a character string). The char- acters '%' (percent) and '_' (underscore) have special meaning when they occur in the pattern. The optional third argument is a charac- ter string containing exactly one character, known as the "escape character", for use when a percent or underscore is required in the pattern without its special meaning. 4.2.3 Rules determining collating sequence usage The rules determining collating sequence usage for character strings are based on the following: - Expressions where no columns are involved (e.g., literals, host variables) are by default compared using the default collating sequence for their character repertoire. Note: The default collating sequence for a character repertoire is defined in Subclause 10.4, "", and Subclause 11.28, "". - When columns are involved (e.g., comparing two columns, or com- paring a column to a literal), by default the default collating sequence of the columns involved is used so long as the columns have the same default collating sequence. - When columns are involved having different default collating sequences, explicit specification of the collating sequence in the expression is required via the when the expression participates in a comparison. - Any explicit specification of collating sequence in an expres- sion overrides any default collating sequence. To formalize this, s effectively have a coercibility attribute. This attribute has the values Coercible, Implicit, No collating sequence, and Explicit. s with the Coercible, Implicit, or Explicit attributes have a collating sequence. Concepts 23 X3H2-92-154/DBL CBR-002 4.2 Character strings A consisting of a column reference has the Implicit attribute, with collating sequence as defined when the column was created. A consisting of a value other than a column (e.g., a host variable or a literal) has the Coercible attribute, with the default collation for its char- acter repertoire. A simply containing a has the Explicit attribute, with the collating sequence specified in the . Note: When the coercibility attribute is Coercible, the collating sequence is uniquely determined as specified in Subclause 8.2, "". The tables below define how the collating sequence and the co- ercibility attribute is determined for the result of any monadic or dyadic operation. Table 1, "Collating coercibility rules for monadic operators", shows the collating sequence and coercibility rules for monadic operators, and Table 2, "Collating coercibil- ity rules for dyadic operators", shows the collating sequence and coercibility rules for dyadic operators. Table 3, "Collating se- quence usage for comparisons", shows how the collating sequence is determined for a particular comparison. _____Table_1-Collating_coercibility_rules_for_monadic_operators____ Operand Coercibility Result Coercibility _____and_Collating_Sequence_____ _____and_Collating_Sequence___ | Collating | Collating | |_Coercibility______Sequence______|_Coercibility______Sequence_____| | | | | Coercible | default | Coercible | default | | | | | | | Implicit | X | Implicit | X | | | | | | | Explicit | X | Explicit | X | | | | | | |_______No_collati|g_sequence_____|______No_collatin|_sequence_____| | | | | | _____Table_2-Collating_coercibility_rules_for_dyadic_operators_____ Result Coercibility Operand 1 Coercibility Operand 2 Coercibility and Collating _and_Collating_Sequence _and_Collating_Sequence ___Sequence___ | Collating | Collating | Col|ating |_Coercibility_Sequence__|_Coercibility_Sequence__|__CoercibilitySe|uence | | | | | Coercible | default | Coercible | default | Coercible| def|ult | | | | | | | | Coercible | default | Implicit | Y | Implicit | Y | | | | | | | | | Coercible | default | No collati|g sequence | No colla|ing | sequence 24 Database Language SQL X3H2-92-154/DBL CBR-002 4.2 Character strings _Table_2-Collating_coercibility_rules_for_dyadic_operators_(Cont.)_ Result Coercibility Operand 1 Coercibility Operand 2 Coercibility and Collating _and_Collating_Sequence _and_Collating_Sequence ___Sequence___ | Collating | Collating | Col|ating |_Coercibility_Sequence__|_Coercibility_Sequence__|__CoercibilitySe|uence | | | | | Coercible | default | Explicit | Y | Explicit | Y | | | | | | | | | Implicit | X | Coercible | default | Implicit | X | | | | | | | | | Implicit | X | Implicit | X | Implicit | X | | | | | | | | | Implicit | X | Implicit | Y /= X | No colla|ing | sequence | Implicit | X | No collati|g sequence | No collating | | | | | | sequence | | | | | | | | Implicit | X | Explicit Y | Explicit Y | | | | | | | No collati|g sequence | Any, | Any | No colla|ing | except sequence Explicit | No collating sequence | Explicit | X | Explicit X | | | | | | | Explicit X | Coercible | default | Explicit | X | | | | | | | | Explicit | X | Implicit | Y | Explicit | X | | | | | | | | | Explicit | X | No collati|g sequence | Explicit | X | | | | | | | | | Explicit | X | Explicit X | Explicit | X | | | | | | | | Explicit | X | Explicit | Y /= X | Not permi|ted:| ____________________________________________________invalid_syntax_ |__________Ta|le_3-Collat|ng_sequence_|sage_for_co|parisons________| Comparand 1 Comparand 2 Coercibility and Coercibility and _Collating_Sequence _Collating_Sequence | | | Collating Sequence | | Collatin| Collatin| Used For The | |_CoercibilitSequence|_CoercibilitSequence|__Comparison____________| | | | | | Coercible| default | Coercible| default | default | | | | | | | | Coercible| default | Implicit | Y | Y | | | | | | | | Coercible| default | No co|lating | Not permitted: invalid| sequence syntax | Coercible| default | Explicit Y | Y | | | | | | Concepts 25 X3H2-92-154/DBL CBR-002 4.2 Character strings ______Table_3-Collating_sequence_usage_for_comparisons_(Cont.)_____ Comparand 1 Comparand 2 Coercibility and Coercibility and _Collating_Sequence _Collating_Sequence | | | Collating Sequence | | Collatin| Collatin| Used For The | |_CoercibilitSequence|_CoercibilitSequence|__Comparison____________| | | | | | Implicit | X | Coercible| default | X | | | | | | | | Implicit | X | Implicit | X | X | | | | | | | | Implicit | X | Implicit | Y /= X | Not permitted: invalid| syntax | Implicit | X | No co|lating | Not permitted: invalid| | | | seq|ence | syntax | | | | | | | | Implicit | X | Explicit Y | Y | | | | | | | No co|lating | Any | Any | Not permitted: invalid| sequence except syntax Explicit | No collating | Explicit | X | X | | sequence | | | | | | | | | | Explicit X | Coercible| default | X | | | | | | | Explicit | X | Implicit | Y | X | | | | | | | | Explicit | X | No co|lating | X | sequence | Explicit | X | Explicit X | X | | | | | | | Explicit | X | Explicit | Y /= X | Not permitted: invalid| ____________________________________________syntax_________________ |For n-adic|operation| (e.g., ) with operands X1, | X2, . . . , n , the collating sequence is effectively determined by considering X1 and X2, then combining this result with X3, and so on. 4.3 Bit strings A bit string is a sequence of bits, each having the value of 0 or 1. A bit string has a length, which is the number of bits in the string. The length is 0 or a positive integer. A bit string data type is described by a bit string data type de- scriptor. A bit string data type descriptor contains: - the name of the specific bit string data type (BIT or BIT VARYING); and 26 Database Language SQL X3H2-92-154/DBL CBR-002 4.3 Bit strings - the length of the bit string data type (in bits). 4.3.1 Bit string comparison and assignment All bit strings are mutually comparable. A bit string is identical to another bit string if and only if it is equal to that bit string in accordance with the comparison rules specified in Subclause 8.2, "". Assignment of a bit string to a bit string variable is performed from the most significant bit to the least significant bit in the source string to the most significant bit in the target string, one bit at a time. 4.3.2 Operations involving bit strings 4.3.2.1 Operators that operate on bit strings and return bit strings is an operator, |, that returns the bit string made by concatenating the two bit string operands in the order given. is a triadic function identical in syntax and semantics to except that the first argument and the returned value are both bit strings. 4.3.2.2 Other operators involving bit strings returns the length (as an integer number of octets or bits according to the choice of function) of a given bit string. determines the first position, if any, at which one string, S1, occurs within another, S2. If S1 is of length zero, then it occurs at position 1 for any value of S2. If S1 does not occur in S2, then zero is returned. 4.4 Numbers A number is either an exact numeric value or an approximate numeric value. Any two numbers are mutually comparable to each other. A numeric data type is described by a numeric data type descriptor. A numeric data type descriptor contains: - the name of the specific numeric data type (NUMERIC, DECIMAL, INTEGER, SMALLINT, FLOAT, REAL, or DOUBLE PRECISION); - the precision of the numeric data type; Concepts 27 X3H2-92-154/DBL CBR-002 4.4 Numbers - the scale of the numeric data type, if it is an exact numeric data type; and - an indication of whether the precision (and scale) are expressed in decimal or binary terms. 4.4.1 Characteristics of numbers An exact numeric value has a precision and a scale. The precision is a positive integer that determines the number of significant digits in a particular radix (binary or decimal). The scale is a non-negative integer. A scale of 0 indicates that the number is an integer. For a scale of S, the exact numeric value is the integer value of the significant digits multiplied by 10-S. An approximate numeric value consists of a mantissa and an expo- nent. The mantissa is a signed numeric value, and the exponent is a signed integer that specifies the magnitude of the mantissa. An approximate numeric value has a precision. The precision is a posi- tive integer that specifies the number of significant binary digits in the mantissa. The value of an approximate numeric value is the mantissa multiplied by 10exponent. Whenever an exact or approximate numeric value is assigned to a data item or parameter representing an exact numeric value, an approximation of its value that preserves leading significant dig- its after rounding or truncating is represented in the data type of the target. The value is converted to have the precision and scale of the target. The choice of whether to truncate or round is implementation-defined. An approximation obtained by truncation of a numerical value N for an T is a value V representable in T such that N is not closer to zero than the numerical value of V and such that the absolute value of the difference between N and the numer- ical value of V is less than the absolute value of the difference between two successive numerical values representable in T. An approximation obtained by rounding of a numerical value N for an T is a value V representable in T such that the absolute value of the difference between N and the nu- merical value of V is not greater than half the absolute value of the difference between two successive numerical values repre- sentable in T. If there are more than one such values V, then it is implementation-defined which one is taken. All numerical values between the smallest and the largest value, inclusive, representable in a given exact numeric type have an approximation obtained by rounding or truncation for that type; it is implementation-defined which other numerical values have such approximations. 28 Database Language SQL X3H2-92-154/DBL CBR-002 4.4 Numbers An approximation obtained by truncation or rounding of a numerical value N for an T is a value V repre- sentable in T such that there is no numerical value representable in T and distinct from that of V that lies between the numerical value of V and N, inclusive. If there are more than one such values V then it is implementation- defined which one is taken. It is implementation-defined which numerical values have approximations obtained by rounding or trun- cation for a given approximate numeric type. Whenever an exact or approximate numeric value is assigned to a data item or parameter representing an approximate numeric value, an approximation of its value is represented in the data type of the target. The value is converted to have the precision of the target. Operations on numbers are performed according to the normal rules of arithmetic, within implementation-defined limits, except as provided for in Subclause 6.12, "". 4.4.2 Operations involving numbers As well as the usual arithmetic operators, plus, minus, times, divide, unary plus, and unary minus, there are the following func- tions that return numbers: - (see Subclause 4.2.2, "Operations involv- ing character strings", and Subclause 4.3.2, "Operations involv- ing bit strings") takes two strings as arguments and returns an integer; - (see Subclause 4.2.2, "Operations involving character strings", and Subclause 4.3.2, "Operations involv- ing bit strings") operates on a string argument and returns an integer; - (see Subclause 4.5.3, "Operations involving datetimes and intervals") operates on a datetime or interval argument and returns an integer. 4.5 Datetimes and intervals A datetime data type is described by a datetime data type descrip- tor. An interval data type is described by an interval data type descriptor. A datetime data type descriptor contains: - the name of the specific datetime data type (DATE, TIME, TIMESTAMP, TIME WITH TIME ZONE, or TIMESTAMP WITH TIME ZONE); and Concepts 29 X3H2-92-154/DBL CBR-002 4.5 Datetimes and intervals - the value of the
that does not specify TEMPORARY. A derived table is a table derived directly or indirectly from one or more other tables by the evaluation of a . The values of a derived table are derived from the values of the underlying tables when the is evaluated. A viewed table is a named derived table defined by a . A viewed table is sometimes called a view. Concepts 37 X3H2-92-154/DBL CBR-002 4.9 Tables The terms simply underlying table, underlying table, leaf underly- ing table, generally underlying table, and leaf generally underly- ing table define a relationship between a derived table or cursor and other tables. The simply underlying tables of derived tables and cursors are defined in Subclause 7.9, "", Subclause 7.10, "", and Subclause 13.1, "". A viewed table has no simply underlying tables. The underlying tables of a derived table or cursor are the simply underlying tables of the derived table or cursor and the underlying tables of the simply underlying tables of the derived table or cursor. The leaf underlying tables of a derived table or cursor are the underlying tables of the derived table or cursor that do not them- selves have any underlying tables. The generally underlying tables of a derived table or cursor are the underlying tables of the derived table or cursor and, for those underlying tables of the derived table or cursor that are viewed tables, the of each viewed table and the gen- erally underlying tables of the of each viewed table. The leaf generally underlying tables of a derived table or cursor are the generally underlying tables of the derived table or cursor that do not themselves have any generally underlying tables. All base tables are updatable. Derived tables are either updatable or read-only. The operations of insert, update, and delete are permitted for updatable tables, subject to constraining Access Rules. The operations of insert, update, and delete are not allowed for read-only tables. A grouped table is a set of groups derived during the evaluation of a or a . A group is a multiset of rows in which all values of the grouping column or columns are equal if a is specified, or the group is the entire table if no is specified. A grouped table may be considered as a collection of tables. Set functions may operate on the individual tables within the grouped table. A global temporary table is a named table defined by a
that specifies GLOBAL TEMPORARY. A created local temporary table is a named table defined by a
that speci- fies LOCAL TEMPORARY. Global and created local temporary tables are effectively materialized only when referenced in an SQL-session. Every in every SQL-session that references a created local temporary table causes a distinct instance of that created local temporary table to be materialized. That is, the contents of a global temporary table or a created local temporary table cannot be shared between SQL-sessions. In addition, the contents of a cre- ated local temporary table cannot be shared between s of a single SQL-session. The definition of a global temporary table or a created local temporary table appears in a schema. In SQL language, 38 Database Language SQL X3H2-92-154/DBL CBR-002 4.9 Tables the name and the scope of the name of a global temporary table or a created local temporary table are indistinguishable from those of a persistent base table. However, because global temporary ta- ble contents are distinct within SQL-sessions, and created local temporary tables are distinct within s within SQL-sessions, the effective of the schema in which the global tem- porary table or the created local temporary table is instantiated is an implementation-dependent that may be thought of as having been effectively derived from the of the schema in which the global temporary table or created local temporary table is defined and the implementation-dependent SQL- session identifier associated with the SQL-session. In addition, the effective of the schema in which the created local temporary table is instantiated may be thought of as being further qualified by a unique implementation-dependent name associ- ated with the in which the created local temporary table is referenced. A declared local temporary table is a named table defined by a that is effectively materialized the first time any in the that contains the is executed. A declared local tem- porary table is accessible only by s in the that contains the . The effective of the of the declared local tem- porary table may be thought of as the implementation-dependent SQL-session identifier associated with the SQL-session and a unique implementation-dependent name associated with the that contains the . All references to a declared local temporary table are prefixed by "MODULE.". The materialization of a temporary table does not persist beyond the end of the SQL-session in which the table was materialized. Temporary tables are effectively empty at the start of an SQL- session. A table is described by a table descriptor. A table descriptor is either a base table descriptor, a view descriptor, or a derived table descriptor (for a derived table that is not a view). Every table descriptor includes: - the degree of the table (the number of column descriptors); and - the column descriptor of each column in the table. A base table descriptor describes a base table. In addition to the components of every table descriptor, a base table descriptor includes: - the name of the base table; - an indication of whether the table is a persistent base table, a global temporary table, a created local temporary table, or a declared local temporary table; and Concepts 39 X3H2-92-154/DBL CBR-002 4.9 Tables - the descriptor of each table constraint specified for the table. A derived table descriptor describes a derived table. In addi- tion to the components of every table descriptor, a derived table descriptor includes: - if the table is named, then the name of the table; - the that defines how the table is to be de- rived; and - an indication of whether the derived table is updatable or read- only (this is derived from the ); A view descriptor describes a view. In addition to the components of a derived table descriptor, a view descriptor includes: - an indication of whether the view has the CHECK OPTION; if so, whether it is to be applied as CASCADED or LOCAL. 4.10 Integrity constraints Integrity constraints, generally referred to simply as constraints, define the valid states of SQL-data by constraining the values in the base tables. A constraint is either a table constraint, a domain constraint or an assertion. A constraint is described by a constraint descriptor. A constraint descriptor is either a table constraint descriptor, a domain constraint descriptor or an assertion descriptor. Every constraint descriptor includes: - the name of the constraint; - an indication of whether or not the constraint is deferrable; - an indication of whether the initial constraint mode is deferred or immediate; A or is possibly non- deterministic if an implementation might, at two different times where the state of the SQL-data is the same, produce results that differ by more than the order of the rows due to General Rules that specify implementation-dependent behavior. No integrity constraint shall be defined using a or a that is possibly non-deterministic. 40 Database Language SQL X3H2-92-154/DBL CBR-002 4.10 Integrity constraints 4.10.1 Checking of constraints Every constraint is either deferrable or non-deferrable. Within a transaction, every constraint has a constraint mode; if a con- straint is non-deferrable, then its constraint mode is always im- mediate, otherwise it is either or immediate or deferred. Every constraint has an initial constraint mode that specifies the constraint mode for that constraint at the start of each SQL- transaction and immediately after definition of that constraint. If a constraint is deferrable, then its constraint mode may be changed (from immediate to deferred, or from deferred to immediate) by execution of a . The checking of a constraint depends on its constraint mode within the current SQL-transaction. If the constraint mode is immedi- ate, then the constraint is effectively checked at the end of each SQL-statement. If the constraint mode is deferred, then the constraint is effectively checked when the constraint mode is changed to immediate either explicitly by execution of a , or implicitly at the end of the current SQL-transaction. When a constraint is checked other than at the end of an SQL- transaction, if it is not satisfied, then an exception condition is raised and the SQL-statement that caused the constraint to be checked has no effect other than entering the exception information into the diagnostics area. When a is executed, all constraints are effectively checked and, if any constraint is not satisfied, then an exception condition is raised and the transaction is terminated by an implicit . 4.10.2 Table constraints A table constraint is either a unique constraint, a referential constraint or a table check constraint. A table constraint is de- scribed by a table constraint descriptor which is either a unique constraint descriptor, a referential constraint descriptor or a table check constraint descriptor. A unique constraint is described by a unique constraint descriptor. In addition to the components of every table constraint descriptor, a unique constraint descriptor includes: - an indication of whether it was defined with PRIMARY KEY or UNIQUE, and - the names and positions of the unique columns specified in the ; A referential constraint is described by a referential constraint descriptor. In addition to the components of every table constraint descriptor, a referential constraint descriptor includes: Concepts 41 X3H2-92-154/DBL CBR-002 4.10 Integrity constraints - the names of the referencing columns specified in the , - the names of the referenced columns and referenced table speci- fied in the , and - the value of the , if specified, and the , if specified. Note: If MATCH FULL or MATCH PARTIAL is specified for a referential constraint and if the referencing table has only one column spec- ified in for that referential constraint, or if the referencing table has more than one specified column for that , but none of those columns is nullable, then the effect is the same as if no were specified. A table check constraint is described by a table check constraint descriptor. In addition to the components of every table constraint descriptor, a table check constraint descriptor includes: - the . A unique constraint is satisfied if and only if no two rows in a table have the same non-null values in the unique columns. In addition, if the unique constraint was defined with PRIMARY KEY, then it requires that none of the values in the specified column or columns be the null value. In the case that a table constraint is a referential constraint, the table is referred to as the referencing table. The referenced columns of a referential constraint shall be the unique columns of some unique constraint of the referenced table. A referential constraint is satisfied if one of the following con- ditions is true, depending on the specified in the : - If no was specified then, for each row R1 of the referencing table, either at least one of the values of the referencing columns in R1 shall be a null value, or the value of each referencing column in R1 shall be equal to the value of the corresponding referenced column in some row of the referenced table. - If MATCH FULL was specified then, for each row R1 of the refer- encing table, either the value of every referencing column in R1 shall be a null value, or the value of every referencing column in R1 shall not be null and there shall be some row R2 of the referenced table such that the value of each referencing col- umn in R1 is equal to the value of the corresponding referenced column in R2. 42 Database Language SQL X3H2-92-154/DBL CBR-002 4.10 Integrity constraints - If MATCH PARTIAL was specified then, for each row R1 of the referencing table, there shall be some row R2 of the refer- enced table such that the value of each referencing column in R1 is either null or is equal to the value of the corresponding referenced column in R2. The referencing table may be the same table as the referenced ta- ble. A table check constraint is satisfied if and only if the specified is not false for any row of a table. 4.10.3 Domain constraints A domain constraint is a constraint that is specified for a domain. It is applied to all columns that are based on that domain, and to all values cast to that domain. A domain constraint is described by a domain constraint descriptor. In addition to the components of every constraint descriptor a domain constraint descriptor includes: - the . A domain constraint is satisfied by SQL-data if and only if, for any table T that has a column named C based on that domain, the specified , with each occurrence of VALUE re- placed by C, is not false for any row of T. A domain constraint is satisfied by the result of a if and only if the specified , with each occurrence of VALUE replaced by that result, is not false. 4.10.4 Assertions An assertion is a named constraint that may relate to the content of individual rows of a table, to the entire contents of a table, or to a state required to exist among a number of tables. An assertion is described by an assertion descriptor. In addi- tion to the components of every constraint descriptor an assertion descriptor includes: - the . An assertion is satisfied if and only if the specified is not false. Concepts 43 X3H2-92-154/DBL CBR-002 4.11 SQL-schemas 4.11 SQL-schemas An SQL-schema is a persistent descriptor that includes: - the of the SQL-schema; - the of the owner of the SQL-schema; - The of the default character set for the SQL-schema; and - the descriptor of every component of the SQL-schema. In this International Standard, the term "schema" is used only in the sense of SQL-schema. Each component descriptor is either a domain descriptor, a base table descriptor, a view descriptor, an assertion descriptor, a privilege descriptor, a character set descriptor, a collation descriptor, or a translation descriptor. The persistent objects described by the descriptors are said to be owned by or to have been created by the of the schema. A schema is created initially using a and may be subsequently modified incrementally over time by the execution of s. s are unique within a catalog. A is explicitly or implicitly qualified by a that identifies a catalog. Base tables and views are identified by
s. A
consists of a and an . For a per- sistent table, the identifies the schema in which the base table or view identified by the
was de- fined. Base tables and views defined in different schemas can have s that are equal according to the General Rules of Subclause 8.2, "". If a reference to a
does not explicitly contain a , then a specific is implied. The par- ticular associated with such a
depends on the context in which the
appears and is governed by the rules for . The default schema for s that are dynamically prepared in the current SQL- session through the execution of s and s is initially implementation-defined but may be changed by the use of s. 44 Database Language SQL X3H2-92-154/DBL CBR-002 4.12 Catalogs 4.12 Catalogs Catalogs are named collections of schemas in an SQL-environment. An SQL-environment contains zero or more catalogs. A catalog con- tains one or more schemas, but always contains a schema named INFORMATION_SCHEMA that contains the views and domains of the Information Schema. The method of creation and destruction of catalogs is implementation-defined. The set of catalogs that can be referenced in any SQL-statement, during any particular SQL-transaction, or during the course of an SQL-session is also implementation-defined. The default catalog for a whose does not specify an explicit to qualify the is implementation-defined. The default catalog for s that are dynami- cally prepared in the current SQL-session through the execution of s and s is ini- tially implementation-defined but may be changed by the use of s. 4.13 Clusters of catalogs A cluster is an implementation-defined collection of catalogs. Exactly one cluster is associated with an SQL-session and it defines the totality of the SQL-data that is available to that SQL-session. An instance of a cluster is described by an instance of a defi- nition schema. Given some SQL-data object, such as a view, a con- straint, a domain, or a base table, the definition of that object, and of all the objects that it directly or indirectly references, are in the same cluster of catalogs. For example, no and no can "cross" a cluster boundary. Whether or not any catalog can occur simultaneously in more than one cluster is implementation-defined. Within a cluster, no two catalogs have the same name. 4.14 SQL-data SQL-data is any data described by schemas that is under the control of an SQL-implementation in an SQL-environment. Concepts 45 X3H2-92-154/DBL CBR-002 4.15 SQL-environment 4.15 SQL-environment An SQL-environment comprises the following: - an SQL-implementation capable of processing some Level (Entry SQL, Intermediate SQL, or Full SQL) of this International Standard and at least one binding style; see Clause 23, "Conformance" for further information about binding styles; - zero or more catalogs; - zero or more s; - zero or more s; and - the SQL-data described by the schemas in the catalogs. An SQL-environment may have other implementation-defined contents. The rules determining which s are considered to be within an SQL-environment are implementation-defined. 4.16 Modules A is an object specified in the module language. A is either a persistent or an SQL-session . The mechanisms by which s are created or destroyed are implementation-defined. A consists of an optional , a , a with either or both of a and a , an optional that iden- tifies the character repertoire used for expressing the names of schema objects used in the , zero or more s, zero or more cursors specified by s, and one or more s. All s contained in the are expressed in either or the character repertoire indicated by unless they are specified with "". A compilation unit is a segment of executable code, possibly con- sisting of one or more subprograms. A is associated with a compilation unit during its execution. A single may be associated with multiple compilation units and multiple s may be associated with a single compilation unit. The manner in which this association is specified, including the possible re- quirement for execution of some implementation-defined statement, is implementation-defined. Whether a compilation unit may invoke or transfer control to other compilation units, written in the same or a different programming language, is implementation-defined. 46 Database Language SQL X3H2-92-154/DBL CBR-002 4.17 Procedures 4.17 Procedures A consists of a , a sequence of s, and a single . A in a is invoked by a compilation unit as- sociated with the by means of a host language "call" statement that specifies the of the and supplies a sequence of parameter values corresponding in number and in to the s of the . A call of a causes the that it contains to be executed. 4.18 Parameters A parameter is declared in a by a . The specifies the of its value. A parameter either assumes or supplies the value of the corresponding argument in the call of that . These s map to host language types and are not nullable except through the use of additional indicator variables. 4.18.1 Status parameters The SQLSTATE and SQLCODE parameters are status parameters. They are set to status codes that indicate either that a call of the completed successfully or that an exception condition was raised during execution of the . Note: The SQLSTATE parameter is the preferred status parameter. The SQLCODE parameter is a deprecated feature that is supported for compatibility with earlier versions of this International Standard. See Annex D, "Deprecated features". A shall specify either the SQLSTATE parameter or the SQLCODE parameter or both. The SQLSTATE parameter is a charac- ter string parameter for which exception values are defined in Clause 22, "Status codes". The SQLCODE parameter is an integer pa- rameter for which the negative exception values are implementation- defined. If a condition is raised that causes a statement to have no effect other than that associated with raising the condition (that is, not a completion condition), then the condition is said to be an exception condition or exception. If a condition is raised that permits a statement to have an effect other than that associated with raising the condition (corresponding to an SQLSTATE class value of successful completion, warning, or no data), then the condition is said to be a completion condition. Concepts 47 X3H2-92-154/DBL CBR-002 4.18 Parameters 4.18.2 Data parameters A data parameter is a parameter that is used to either assume or supply the value of data exchanged between a host program and an SQL-implementation. 4.18.3 Indicator parameters An indicator parameter is an integer parameter that is specified immediately following another parameter. Its primary use is to indicate whether the value that the other parameter assumes or supplies is a null value. An indicator parameter cannot immediately follow another indicator parameter. The other use for indicator parameters is to indicate whether string data truncation occurred during a transfer between a host program and an SQL-implementation in parameters or host variables. If a non-null string value is transferred and the length of the target data item is sufficient to accept the entire source data item, then the indicator parameter or variable is set to 0 to in- dicate that truncation did not occur. However, if the length of the target data item is insufficient, then the indicator parame- ter or variable is set to the length of the source data item (in characters or bits, as appropriate) to indicate that truncation occurred and to indicate the original length in characters or bits, as appropriate, of the source. 4.19 Diagnostics area The diagnostics area is a place where completion and exception con- dition information is stored when an SQL-statement is executed. There is one diagnostics area associated with an SQL-agent, regard- less of the number of s that the SQL-agent includes or the number of connections in use. At the beginning of the execution of any statement that is not an , the diagnostics area is emptied. An implementation shall place information about a completion condition or an exception condition reported by SQLCODE or SQLSTATE into this area. If other conditions are raised, an implementation may place information about them into this area. s containing s return a code indicating completion or exception conditions for that statement via SQLCODE or SQLSTATE, but do not modify the diagnostics area. An SQL-agent may choose the size of the diagnostics area with the ; if an SQL-agent does not specify the size of the diagnostics area, then the size of the diagnostics area is implementation-dependent, but shall always be able to hold information about at least one condition. An implementation may place information into this area about fewer conditions than are specified. The ordering of the information about conditions placed 48 Database Language SQL X3H2-92-154/DBL CBR-002 4.19 Diagnostics area into the diagnostics area is implementation-dependent, except that the first condition in the diagnostics area always corresponds to the condition specified by the SQLSTATE or SQLCODE value. 4.20 Standard programming languages This International Standard specifies the actions of s in s when those s are called by programs that conform to certain specified programming language standards. The term "standard PLN program", where PLN is the name of a program- ming language, refers to a program that conforms to the standard for that programming language as specified in Clause 2, "Normative references". This International Standard also specifies a mechanism whereby SQL language may be embedded in programs that otherwise conform to any of the same specified programming language stan- dards. Note: In this International Standard, for the purposes of inter- facing with programming languages, the data types DATE, TIME, TIMESTAMP, and INTERVAL shall be converted to or from character strings in those programming languages by means of a . It is anticipated that future evolution of programming language standards will support data types corresponding to these four SQL data types; this standard will then be amended to reflect the availability of those corresponding data types. The data type CHARACTER is also mapped to character strings in the programming languages. However, because the facilities available in the pro- gramming languages do not provide the same capabilities as those available in SQL, there shall be agreement between the host pro- gram and SQL regarding the specific format of the character data being exchanged. Specific syntax for this agreement is provided in this International standard. For standard programming lan- guages, C, COBOL, Fortran, and Pascal, bit strings are mapped to character variables in the host language in a manner described in Subclause 19.1, "". For standard pro- gramming languages Ada and PL/I, bit string variables are directly supported. 4.21 Cursors A cursor is specified by a , , or . For every or in a , a cursor is effectively created when an SQL-transaction (see Subclause 4.28, "SQL-transactions") referencing the is initiated, and destroyed when that SQL-transaction is terminated. A cursor is also effectively created when an is executed within a SQL-transaction and destroyed when that SQL-transaction is terminated. In addition, an extended dynamic Concepts 49 X3H2-92-154/DBL CBR-002 4.21 Cursors cursor is destroyed when a is exe- cuted that deallocates the prepared statement on which the extended dynamic cursor is based. A cursor is in either the open state or the closed state. The ini- tial state of a cursor is the closed state. A cursor is placed in the open state by an or and returned to the closed state by a or , a , or a . A cursor in the open state identifies a table, an ordering of the rows of that table, and a position relative to that ordering. If the does not include an , or includes an that does not specify the order of the rows completely, then the rows of the table have an order that is defined only to the extent that the specifies an order and is otherwise implementation-dependent. When the ordering of a cursor is not defined by an , the relative positions of two rows is implementation- dependent. When the ordering of a cursor is partially determined by an , then the relative positions of two rows are determined only by the ; if the two rows have equal values for the purpose of evaluating the , then their relative positions are implementation-dependent. A cursor is either read-only or updatable. If the table identified by a cursor is not updatable or if INSENSITIVE is specified for the cursor, then the cursor is read-only; otherwise, the cursor is updatable. The operations of update and delete are not allowed for read-only cursors. The position of a cursor in the open state is either before a cer- tain row, on a certain row, or after the last row. If a cursor is on a row, then that row is the current row of the cursor. A cursor may be before the first row or after the last row of a table even though the table is empty. When a cursor is initially opened, the position of the cursor is before the first row. A or positions an open cursor on a specified row of the cursor's ordering and retrieves the values of the columns of that row. An or updates the current row of the cursor. A or deletes the current row of the cursor. If an error occurs during the execution of an SQL-statement that identifies an open cursor, then, except where otherwise explic- itly defined, the effect, if any, on the position or state of that cursor is implementation-dependent. If a cursor is open, and the current SQL-transaction makes a change to SQL-data other than through that cursor, and the for that cursor specified INSENSITIVE, then the effect of that change will not be visible through that cursor before it is closed. Otherwise, whether the effect of such a change will be 50 Database Language SQL X3H2-92-154/DBL CBR-002 4.21 Cursors visible through that cursor before it is closed is implementation- dependent. 4.22 SQL-statements 4.22.1 Classes of SQL-statements An SQL-statement is a string of characters that conforms to the format and syntax rules specified in this international standard. Most SQL-statements can be prepared for execution and executed in one of a number of ways. These are: - in a , in which case it is prepared when the is created (see Subclause 4.16, "Modules") and executed when the containing procedure is called. - in an embedded SQL host program, in which case it is pre- pared when the embedded SQL host program is preprocessed (see Subclause 4.23, "Embedded syntax"). - being prepared and executed by the use of SQL-dynamic statements (which are themselves executed in one of the foregoing two ways- see Subclause 4.24, "SQL dynamic statements"). - direct invocation, in which case it is effectively prepared immediately prior to execution (see Subclause 4.25, "Direct invocation of SQL"). There are at least five ways of classifying SQL-statements: - According to their effect on SQL objects, whether persistent objects, i.e., SQL-data and schemas, or transient objects, such as SQL-sessions and other SQL-statements. - According to whether or not they start a transaction, or can, or must, be executed when no transaction is active. - According to whether or not they may be embedded. - According to whether they may be dynamically prepared and exe- cuted. - According to whether or not they may be directly executed. This International Standard permits implementations to provide ad- ditional, implementation-defined, statements that may fall into any of these categories. This Subclause will not mention those state- ments again, as their classification is entirely implementation- defined. Concepts 51 X3H2-92-154/DBL CBR-002 4.22 SQL-statements 4.22.2 SQL-statements classified by function The following are the main classes of SQL-statements: - SQL-schema statements; these may have a persistent effect on schemas - SQL-data statements; some of these, the SQL-data change state- ments, may have a persistent effect on SQL-data - SQL-transaction statements; except for the , these, and the following classes, have no effects that persist when a session is terminated - SQL-connection statements - SQL-session statements - SQL-dynamic statements - SQL-diagnostics statements - SQL embedded exception declaration The following are the SQL-schema statements: - - - - -
- - - - - - - - - - - 52 Database Language SQL X3H2-92-154/DBL CBR-002 4.22 SQL-statements - - - - The following are the SQL-data statements: - - - - - - - - - - - - o o o o o o o Concepts 55 X3H2-92-154/DBL CBR-002 4.22 SQL-statements The following SQL-statements are embeddable in an embedded SQL host program, and may occur in a , though not in a : - - - The following SQL-statements are embeddable in an embedded SQL host program, but may not occur in a : - SQL embedded exception declarations Consequently, the following SQL-data statements are not embeddable in an embedded SQL host program, nor may they occur in a , nor be the in a in a : - - - - - 4.22.4 Preparable and immediately executable SQL-statements The following SQL-statements are preparable: - All SQL-schema statements - All SQL-transaction statements - All SQL-session statements - The following SQL-data statements: o o o o o o o o 56 Database Language SQL X3H2-92-154/DBL CBR-002 4.22 SQL-statements Consequently, the following SQL-statements are not preparable: - All SQL-connection statements - All SQL-dynamic statements - All SQL-diagnostics statements - SQL embedded exception declarations - The following SQL-data statements: o o o o o o o o o o 58 Database Language SQL X3H2-92-154/DBL CBR-002 4.22 SQL-statements o o o o o o 4.22.6 SQL-statements and transaction states Whether an starts a transaction de- pends on what SQL-statement is the value of . Whether an starts a transaction depends on what SQL-statement was the value of when the prepared statement identified by was prepared. The following SQL-statements are transaction initiating SQL- statements, i.e., if there is no current transaction, and a state- ment of this class is executed, a transaction is initiated: - All SQL-schema statements - The following SQL-data statements: o o o o o o o o o
::= | ::= MODULE ::= ::= ::= [ ] ::= ::= ::= [ ] ::= ::= 98 Database Language SQL X3H2-92-154/DBL CBR-002 5.4 Names and identifiers ::= ::= ::= ::= ::= | ::= ::= [ ] ::= | ::= [ ] ::= [ ] ::= GLOBAL | LOCAL ::= ::= ::= ::= [ ] ::= ::= ::= ::= ::= Lexical elements 99 X3H2-92-154/DBL CBR-002 5.4 Names and identifiers Syntax Rules 1) If a is not specified in an , then the set of characters contained in the shall be wholly contained in either or the character repertoire identified by: Case: a) If the is contained in a , then the , b) If the is contained in a that is not contained in a , then the , c) If the is contained in a that is prepared in the current SQL-session by an or a or in a that is invoked directly, then the default character set name for the SQL-session. 2) If a is specified in an , then: a) There shall be no between the and the . b) The set of characters contained in the or shall be wholly contained in the character repertoire indicated by the . 3) The sum of the number of s and the number of s in an shall not be greater than 128. 4) An is equivalent to an in which every letter that is a lower-case letter is replaced by the equivalent upper-case letter or letters. This treatment includes determination of equivalence, representation in the Information and Definition Schemas, representation in the diagnostics area, and similar uses. 5) An (with every letter that is a lower- case letter replaced by the equivalent upper-case letter), treated as the repetition of a that specifies a of SQL_TEXT, shall not be equal, according to the comparison rules in Subclause 8.2, "", to any (with every letter that is a lower-case letter replaced by the equivalent upper-case letter), treated as the repetition of a that specifies a of SQL_TEXT. 100 Database Language SQL X3H2-92-154/DBL CBR-002 5.4 Names and identifiers Note: It is the intention that no specified in this International standard or revisions thereto shall end with an . 6) If
is not a , then the table identified by
shall not be a declared local temporary table. 7) No shall specify DEFINITION_SCHEMA. 8) If a does not contain a , then Case: a) If the is contained in a , then the that is specified or implicit in the is implicit. b) If the is contained in a that is prepared in the current SQL-session by an or a or in a that is invoked directly, then the default for the SQL-session is implicit. c) Otherwise, the that is specified or implicit for the is implicit. 9) If a does not contain a , then Case: a) If the is contained in a , then an implementation-defined is implicit. b) If the is contained in a other than in a , then the that is specified or implicit in the is implicit. c) If the is contained in a that is prepared in the current SQL-session by an or a or in a that is invoked directly, then the default catalog name for the SQL-session is implicit. d) If the is contained in a , then Case: i) If the is contained in a , then the explicit or implicit contained in the is implicit. Lexical elements 101 X3H2-92-154/DBL CBR-002 5.4 Names and identifiers ii) Otherwise, an implementation-defined is implicit. e) Otherwise, the explicit or implicit contained in the is implicit. 10)Two s are equal if and only if they have the same and the same , regard- less of whether the s are implicit or explicit. 11)Two s are equal if and only if they have the same and the same , regard- less of whether the s are implicit or explicit. 12)An that is a is associated with a table within a particular scope. The scope of a is either a
"). Scopes may be nested. In different scopes, the same may be associated with different tables or with the same table. 13)The of or shall not be a . 14)The data type of the of shall be character string with an implementation-defined character set and shall have an octet length of 128 octets or less. 15)The data type of the of shall be character string with an implementation- defined character set and shall have an octet length of 128 octets or less. 16)The data type of the of shall be character string with an implementation-defined character set and shall have an octet length of 128 octets or less. 17)In a , , or , if a is not specified, then a of LOCAL is implicit. 18)No shall specify "PUBLIC". 19)Those s that are valid s are implementation-defined. 20)Those s that are valid s are implementation- defined. 102 Database Language SQL X3H2-92-154/DBL CBR-002 5.4 Names and identifiers 21)If a does not specify a , then INFORMATION_SCHEMA is implicit. 22)If a does not specify a , then INFORMATION_SCHEMA is implicit. 23)If a does not specify a , then INFORMATION_SCHEMA is implicit. 24)The of , , and shall be character string with an implementation- defined character set and shall have an octet length of 128 octets or less. 25)If a does not specify a , then INFORMATION_SCHEMA is implicit; otherwise, INFORMATION_ SCHEMA shall be specified. Access Rules None. General Rules 1) A
identifies a table. 2) Within its scope, a identifies a table. 3) A identifies a declared local temporary ta- ble. 4) A identifies a column. 5) A identifies a domain. 6) An represents an authorization iden- tifier and identifies a set of privileges. 7) A identifies a . 8) A identifies a cursor. 9) A identifies a . 10)A identifies a parameter. 11)A identifies a table constraint, a domain constraint, or an assertion. 12)A identifies a statement prepared by the execu- tion of a . The scope of a is the in which it appears and the current SQL-session. Lexical elements 103 X3H2-92-154/DBL CBR-002 5.4 Names and identifiers 13)The value of an identifies a statement prepared by the execution of a . If a of GLOBAL is specified, then the scope of the is the current SQL-session. If a of LOCAL is specified or implicit, then the scope of the state- ment name is further restricted to the in which the appears. 14)A identifies a cursor in an . 15)The value of an identifies a cursor cre- ated by the execution of an . If a of GLOBAL is specified, then the scope of the is the current SQL-session. If a of LOCAL is specified of implicit, then the scope of the cursor name is further restricted to the in which the appears. 16)A identifies an SQL descriptor area created by the execution of an . If a of GLOBAL is specified, then the scope of the is the current SQL-session. If a of LOCAL is specified or implicit, then the scope of the is further restricted to the in which the appears. 17)A identifies a catalog. 18)A identifies a schema. 19)A identifies a collating sequence. 20)A identifies a character set. 21)A identifies a character translation. 22)A identifies a form-of-use con- version. All s are implementation- defined. 23)A identifies an SQL-connection. Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall not contain any or . b) Conforming Intermediate SQL language shall not contain any explicit , , , , , or . 104 Database Language SQL X3H2-92-154/DBL CBR-002 5.4 Names and identifiers 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain any , , , , , or . b) An shall not specify a . Lexical elements 105 X3H2-92-154/DBL CBR-002 106 Database Language SQL X3H2-92-154/DBL CBR-002 6 Scalar expressions 6.1 Function Specify a data type. Format ::= [ CHARACTER SET ] | | | | | ::= CHARACTER [ ] | CHAR [ ] | CHARACTER VARYING | CHAR VARYING | VARCHAR ::= NATIONAL CHARACTER [ ] | NATIONAL CHAR [ ] | NCHAR [ ] | NATIONAL CHARACTER VARYING | NATIONAL CHAR VARYING | NCHAR VARYING ::= BIT [ ] | BIT VARYING ::= | ::= NUMERIC [ [ ] ] | DECIMAL [ [ ] ] Scalar expressions 107 X3H2-92-154/DBL CBR-002 6.1 | DEC [ [ ] ] | INTEGER | INT | SMALLINT ::= FLOAT [ ] | REAL | DOUBLE PRECISION ::= ::= ::= ::= DATE | TIME [
6.3
Function Reference a table. Format
::=
[ [ AS ] [ ] ] | [ AS ] [ ] | ::=
::= ::= [ { }... ] Syntax Rules 1) A immediately contained in a
TR is exposed by TR. A
immediately contained in a
TR is exposed by TR if and only if TR does not specify a . 2) Case: a) If a
TR is contained in a FC with no intervening , then the scope clause SC of TR is the
of TR is the
of TR is the of SC and of all s contained in SC that contain TR. 3) A
that is exposed by a
TR shall not be the same as any other
that is exposed by a
with the same scope clause as TR. 118 Database Language SQL X3H2-92-154/DBL CBR-002 6.3
4) A that is exposed by a
TR shall not be the same as any other that is exposed by a
with the same scope clause as TR and shall not be the same as the of any
that is exposed by a
with the same scope clause as TR. 5) A
immediately contained in a
TR has a scope clause and scope defined by that
if and only if the
is exposed by TR. 6) The same shall not be specified more than once in a . 7) If a is specified in a
, then the number of s in the shall be the same as the degree of the table specified by the or the
of that
, and the name of the i-th column of that or the effective name of the i-th column of that
is the i-th in that . 8) A is an updatable derived table if and only if the simply contained in the of the
of the is updatable. Access Rules 1) Let T be the table identified by the
immediately contained in
. If the
is contained in any of: a) a simply contained in a , a , a , or an ; or b) a
or ; or c) a immediately contained in a or an ; or d) a immediately contained in an , then the applicable privileges shall include SELECT for T. General Rules 1) The or exposed
contained in a
defines that or
to be an identifier of the table identified by the
or of that
. Scalar expressions 119 X3H2-92-154/DBL CBR-002 6.3
Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) A
shall not be a . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) A
shall not be a . b) The optional AS shall not be specified. c) shall not be specified. 120 Database Language SQL X3H2-92-154/DBL CBR-002 6.4 6.4 Function Reference a column. Format ::= [ ] ::=
| Syntax Rules 1) Let CR be the , let CN be the contained in CR, and let C be the column identified by CN. 2) If CR contains a Q, then CR shall appear within the scope of one or more
s or s that are equal to Q. If there is more than one such
or , then the one with the most local scope is specified. Let T be the table associated with Q. a) T shall include a column whose is CN. b) If T is a
in a J, then CN shall not be a common column name in J. Note: Common column name is defined in Subclause 7.5, "". 3) If CR does not contain a , then CR shall be contained within the scope of one or more
s or s whose associated tables include a column whose is CN. Let the phrase possible qualifiers denote those
s and s. a) Case: i) If the most local scope contains exactly one possible qualifier, then the qualifier Q equivalent to that unique
or is implicit. ii) If there is more than one possible qualifier with most local scope, then: 1) Each possible qualifier shall be a
or a of a
that is di- rectly contained in a J. Scalar expressions 121 X3H2-92-154/DBL CBR-002 6.4 2) CN shall be a common column name in J. Note: Common column name is defined in Subclause 7.5, "". 3) The implicit qualifier Q is implementation-dependent. The scope of Q is that which Q would have had if J had been replaced by the
: ( J ) AS Q b) Let T be the table associated with Q. 4) The data type of CR is the data type of column C of T. CN shall uniquely identify a column of T. 5) If the data type of CR is character string, then CR has the Implicit coercibility attribute and its collating sequence is the default collating sequence for column C of T. 6) If the data type of CR is TIME or TIMESTAMP, then the implicit time zone of the data is the current default time zone for the SQL-session. 7) If the data type of CR is TIME WITH TIME ZONE or TIMESTAMP WITH TIME ZONE, then the time zone of the data is the time zone rep- resented in the value of CR. 8) If CR is contained in a
TE and the scope clause of the
immediately containing the or Q also contains TE, then CR is an outer reference to the table associated with Q. 9) Let CR be the and let C be the column identi- fied by CR. C is an underlying column of CR. If C is a , then every underlying column of C is an underlying column of CR. Note: The underlying columns of a are defined in Subclause 7.9, "". Access Rules 1) The applicable privileges shall include SELECT for T if CR is contained in any of: a) a immediately contained in a or an ; or b) a immediately contained in an . 122 Database Language SQL X3H2-92-154/DBL CBR-002 6.4 General Rules 1) The Q.CN references column C in a given row of T. 2) If the data type of CR is TIME, TIMESTAMP, TIME WITH TIME ZONE or TIMESTAMP WITH TIME ZONE, then let TZ be an INTERVAL HOUR TO MINUTE containing the value of the time zone displacement associated with CR. The value of CR, normalized to UTC, is ef- fectively computed as CR + TZ. Leveling Rules 1) The following restrictions apply for Intermediate SQL; None. 2) The following restrictions apply for Entry SQL; None. Scalar expressions 123 X3H2-92-154/DBL CBR-002 6.5 6.5 Function Specify a value derived by the application of a function to an argument. Format ::= COUNT | ::= [ ] ::= AVG | MAX | MIN | SUM | COUNT ::= DISTINCT | ALL Syntax Rules 1) If is not specified, then ALL is implicit. 2) The argument of COUNT(*) and the argument source of a is a table or a group of a grouped table as spec- ified in Subclause 7.8, "", and Subclause 7.9, "". Note: argument source is defined in Subclause 7.8, "". 3) Let T be the argument or argument source of a . 4) The simply contained in shall not contain a or a . If the contains a that is an outer reference, then that outer reference shall be the only contained in the . Note: Outer reference is defined in Subclause 6.4, "". 5) If a contains a that is an outer reference, then the shall be contained in either: a) a
that is directly contained in the
that directly contains the . Note: Outer reference is defined in Subclause 6.4, "". 6) Let DT be the data type of the . 7) If COUNT is specified, then the data type of the result is exact numeric with implementation-defined precision and scale of 0. 8) If MAX or MIN is specified, then the data type of the result is DT. 9) If SUM or AVG is specified, then: a) DT shall not be character string, bit string, or datetime. b) If SUM is specified and DT is exact numeric with scale S, then the data type of the result is exact numeric with implementation-defined precision and scale S. c) If AVG is specified and DT is exact numeric, then the data type of the result is exact numeric with implementation- defined precision not less than the precision of DT and implementation-defined scale not less than the scale of DT. d) If DT is approximate numeric, then the data type of the result is approximate numeric with implementation-defined precision not less than the precision of DT. e) If DT is interval, then the data type of the result is inter- val with the same precision as DT. 10)If the data type of the result is character string, then the collating sequence and the coercibility attribute are determined as in Subclause 4.2.3, "Rules determining collating sequence usage". Access Rules None. General Rules 1) Case: a) If COUNT(*) is specified, then the result is the cardinality of T. Scalar expressions 125 X3H2-92-154/DBL CBR-002 6.5 b) Otherwise, let TX be the single-column table that is the result of applying the to each row of T and eliminating null values. If one or more null values are eliminated, then a completion condition is raised: warning- null value eliminated in set function. 2) If DISTINCT is specified, then let TXA be the result of elimi- nating redundant duplicate values from TX. Otherwise, let TXA be TX. Case: a) If the COUNT is specified, then the result is the cardinality of TXA. b) If AVG, MAX, MIN, or SUM is specified, then Case: i) If TXA is empty, then the result is the null value. ii) If AVG is specified, then the result is the average of the values in TXA. iii) If MAX or MIN is specified, then the result is respec- tively the maximum or minimum value in TXA. These results are determined using the comparison rules specified in Subclause 8.2, "". iv) If SUM is specified, then the result is the sum of the values in TXA. If the sum is not within the range of the data type of the result, then an exception condition is raised: data exception-numeric value out of range. Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) If a specifies DISTINCT, then the shall be a . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) If a specifies or implies ALL, then COUNT shall not be specified. b) If a specifies or implies ALL, then the shall include a that references a column of T. c) If the contains a that is an outer reference, then the shall be a . 126 Database Language SQL X3H2-92-154/DBL CBR-002 6.5 d) No contained in a shall reference a column derived from a that generally contains a . Scalar expressions 127 X3H2-92-154/DBL CBR-002 6.6 6.6 Function Specify a function yielding a value of type numeric. Format ::= | | ::= POSITION IN ::= | | ::= { CHAR_LENGTH | CHARACTER_LENGTH } ::= OCTET_LENGTH ::= BIT_LENGTH ::= EXTRACT FROM ::= |
shall not contain more than one . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) A shall not specify DEFAULT. Query expressions 175 X3H2-92-154/DBL CBR-002 7.2
7.2
Function Specify a set of s to be constructed into a table. Format
::= VALUES
::= [ { }... ] Syntax Rules 1) All s shall be of the same degree. Access Rules None. General Rules 1) Let Ti be a table whose j-th column has the same data type as the j-th in the i-th and let Ti contain one row whose j-th column has the same value as the j-th in the i-th . 2) The result of the
is the same as the result of T1 [ UNION ALL T2 [ . . . UNION ALL n ] . . . ] Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) A
shall contain exactly one that shall be of the form "()". b) A
shall be the of an . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. 176 Database Language SQL X3H2-92-154/DBL CBR-002 7.3
7.3
Function Specify a table or a grouped table. Format
::= [ ] [ ] [ ] Syntax Rules 1) The result of a
is a derived table in which the descriptor of the i-th column is the same as the descriptor of the i-th column of the table specified by the . 2) Let C be some column. Let TE be the
. C is an underlying column of TE if and only if C is an underlying column of some contained in TE. Access Rules None. General Rules 1) If all optional clauses are omitted, then the result of the is the same as the result of the . Otherwise, each specified clause is applied to the result of the previously specified clause and the result of the
is the result of the application of the last specified clause. Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) If the table identified in the is a grouped view, then the
shall not contain a , , or . Query expressions 177 X3H2-92-154/DBL CBR-002 7.4 7.4 Function Specify a table derived from one or more named tables. Format ::= FROM
[ {
}... ] Syntax Rules 1) Case: a) If the contains a single
with no intervening or , then the descriptor of the result of the is the same as the descriptor of the table identified by that
. b) If the contains more than one
with no intervening or , then the descriptors of the columns of the result of the are the descriptors of the columns of the tables identified by the
s, in the order in which the
s appear in the and in the order in which the columns are defined within each table. Access Rules None. General Rules 1) Case: a) If the contains a single
with no intervening or , then the result of the is the table identified by that
. b) If the contains more than one
with no intervening or , then the result of the is the extended Cartesian product of the tables identified by those
s. The extended Cartesian product, CP, is the multiset of all rows R such that R is the concatenation of a row from each of the identified tables in the order in which they are iden- tified. The cardinality of CP is the product of the cardi- nalities of the identified tables. The ordinal position of a 178 Database Language SQL X3H2-92-154/DBL CBR-002 7.4 column in CP is N+S, where N is the ordinal position of that column in the identified table T from which it is derived and S is the sum of the degrees of the tables identified before T in the . Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) If the table identified by
is a grouped view, then the shall contain exactly one
. Query expressions 179 X3H2-92-154/DBL CBR-002 7.5 7.5 Function Specify a table derived from a Cartesian product, inner or outer join, or union join. Format ::= | | ::=
CROSS JOIN
::=
[ NATURAL ] [ ] JOIN
[ ] ::= | ::= ON ::= USING ::= INNER | [ OUTER ] | UNION ::= LEFT | RIGHT | FULL ::= Syntax Rules 1) Let TR1 and TR2 be the first and second
s of the , respectively. Let T1 and T2 be the tables identified by TR1 and TR2, respectively. Let TA and TB be the correlation names of TR1 and TR2, respectively. Let CP be: SELECT * FROM TR1, TR2 2) If a is specified, then 180 Database Language SQL X3H2-92-154/DBL CBR-002 7.5 Case: a) If NATURAL is specified, then a shall not be specified. b) If UNION is specified, then neither NATURAL nor a shall be specified. c) Otherwise, a shall be specified. 3) If a is specified and a is not specified, then INNER is implicit. 4) If a containing a is speci- fied, then; a) Each directly contained in the shall unambiguously reference a column of T1 or T2 or be an outer reference. b) If a directly contained in the is a , then the shall be contained in a or of s of the form COALESCE ( TA.C, TB.C ) AS C for every column C that is a corresponding join column, taken in order of their ordinal positions in T1. e) Let SL1 be a of those s of T2 that are not correspond- ing join columns, taken in order of their ordinal positions in T2. f) The descriptors of the columns of the result of the are the same as the descriptors of the columns of the result of SELECT SLCC, SLT1, SLT2 FROM TR1, TR2 7) For every column CR of the result of the that is not a corresponding join column and that corresponds to a column C1 of T1, CR is possibly nullable if any of the following conditions are true: a) RIGHT, FULL, or UNION is specified, or b) INNER, LEFT, or CROSS JOIN is specified or implicit and 1 is possibly nullable. 8) For every column CR of the result of the that is not a corresponding join column and that corresponds to a column C2 of T2, CR is possibly nullable if any of the following conditions are true: a) LEFT, FULL, or UNION is specified, or b) INNER, RIGHT, or CROSS JOIN is specified or implicit and C is possibly nullable. 9) For every column CR of the result of the that is a corresponding join column and that corresponds to a column C1 of T1 and C2 of T2, CR is possibly nullable if any of the following conditions are true: a) RIGHT, FULL, or UNION is specified and 1 is possibly nul- lable, or b) LEFT, FULL, or UNION is specified and 2 is possibly nul- lable. 10)The is a read-only table. 182 Database Language SQL X3H2-92-154/DBL CBR-002 7.5 Access Rules None. General Rules 1) Case: a) If is UNION, then let T be the empty set. b) If a is specified, then let T be the multiset of rows of CP. c) If a is specified, then let T be the multi- set of rows of CP for which the specified is true. d) If NATURAL is specified or is specified, then Case: i) If there are corresponding join columns, then let T be the multiset of rows of CP for which the corresponding join columns have equal values. ii) Otherwise, let T be the multiset of rows of CP. 2) Let P1 be the multiset of rows of T1 for which there exists in T some row that is the concatenation of some row R1 of T1 and some row R2 of T2. Let P2 be the multiset of rows of T2 for which there exists in T some row that is the concatenation of some row R1 of T1 and some row R2 of T2. 3) Let U1 be those rows of T1 that are not in P1 and let U2 be those rows of T2 that are not in P2. 4) Let D1 and D2 be the degree of T1 and T2, respectively. Let X1 be U1 extended on the right with D2 columns containing the null value. Let X2 be U2 extended on the left with D1 columns containing the null value. 5) Let XN1 and XN2 be effective distinct names for X1 and X2, re- spectively. Let TN be an effective name for T. Case: a) If INNER or is specified, then let S be the multiset of rows of T. b) If LEFT is specified, then let S be the multiset of rows resulting from: SELECT * FROM TN UNION ALL SELECT * FROM XN1 Query expressions 183 X3H2-92-154/DBL CBR-002 7.5 c) If RIGHT is specified, then let S be the multiset of rows resulting from: SELECT * FROM TN UNION ALL SELECT * FROM XN2 d) If FULL is specified, then let S be the multiset of rows resulting from: SELECT * FROM TN UNION ALL SELECT * FROM XN1 UNION ALL SELECT * FROM XN2 e) If UNION is specified, then let S be the multiset of rows resulting from: SELECT * FROM XN1 UNION ALL SELECT * FROM XN2 6) Let SN be an effective name of S. Case: a) If NATURAL is specified or a is speci- fied, then the result of the is the multiset of rows resulting from: SELECT SLCC, SLT1, SLT2 FROM SN b) Otherwise, the result of the is S. Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall contain no . b) Conforming Intermediate SQL language shall not specify UNION JOIN. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain any . 184 Database Language SQL X3H2-92-154/DBL CBR-002 7.6 7.6 Function Specify a table derived by the application of a to the result of the preceding . Format ::= WHERE Syntax Rules 1) Let T be the result of the preceding . Each directly contained in the shall unambiguously reference a column of T or be an outer reference. Note: Outer reference is defined in Subclause 6.4, "". 2) If a directly contained in the is a , then the shall be contained in a or
. Format ::= SELECT [ ]
[ { ::= | ::= [ ] ::= [ AS ] Syntax Rules 1) Let T be the result of the
. 2) The degree of the table specified by a is equal to the cardinality of the "*" is simply contained in a that is immediately contained in an , then the "*" is equivalent to a sequence in which each is a that references a column of T and each column of T is referenced exactly once. The columns are ref- erenced in the ascending sequence of their ordinal position within T. 4) The of QS. For all i, C is an underlying column of DCi, and of any that identifies DCi, if and only if C is an underlying column of the of DCi, or C is an underlying column of the
immediately contained in QS. 6) Each directly contained in each and each contained in a directly contained in each shall unambiguously reference a column of T. 7) If T is a grouped table, then each in each that references a column of T shall refer- ence a grouping column or be specified within a . If T is not a grouped table and any contains a that contains a reference to a column of T or any directly contains a that does not contain an outer reference, then every in every that references a column of T shall be specified within a . 8) Each column of the table that is the result of a has a column descriptor that includes a data type descriptor that is the same as the data type descriptor of the from which the column was derived. 9) Case: a) If the i-th in the does not specify an and the of that is a single , then the of the i-th column of the result is C. c) Otherwise, the of the i-th column of the is implementation-dependent and different from the of any column, other than itself, of a table referenced by any
contained in the SQL-statement. 10)A column of the table that is the result of a is possibly nullable if and only if it contains a for a column C that is possibly nullable, an , an , a , CAST NULL AS X (X represents a or a ), SYSTEM_ USER, or a that does not contain COUNT. 192 Database Language SQL X3H2-92-154/DBL CBR-002 7.9 11)Let TREF be the
s that are simply contained in the of the
. The simply un- derlying tables of the are the tables identified by the
s and s contained in TREF without an intervening . 12)A QS is updatable if and only if the fol- lowing conditions hold: a) QS does not specify DISTINCT. b) Every contained in the
immediately contained in QS specifies exactly one
and that
refers either to a base table or to an updatable derived table. Note: updatable derived table is defined in Subclause 6.3, "
". d) If the
immediately contained in QS imme- diately contains a WC, then no leaf generally underlying table of QS shall be a generally underlying table of any contained in WC. e) The
immediately contained in QS does not include a or a . 13)A is possibly non-deterministic if any of the following conditions are true: a) The DISTINCT is specified and one of the columns of T has a data type of character string; or b) The directly contains a that is possibly non-deterministic; or c) The contains a that contains a reference to a column of T or di- rectly contains a that does not contain an outer reference, then T is the argument or argument source of each such and the result of the is a table con- sisting of 1 row. The i-th value of the row is the value specified by the i-th . ii) If the
contains a . b) A
of the is a grouped view, then the
| ::= TABLE
::= CORRESPONDING [ BY ] ::= 196 Database Language SQL X3H2-92-154/DBL CBR-002 7.10 Syntax Rules 1) Let T be the table specified by the . 2) The TABLE
is equivalent to the ( SELECT * FROM
) 3) Let set operator be UNION [ALL], EXCEPT [ALL], or INTERSECT [ALL]. 4) T is an updatable table and the is updatable if and only if it simply contains a QE or a QS and: a) the contains QE or QS without an inter- vening that specified UNION or EXCEPT; b) the contains QE or QS without an interven- ing that specifies INTERSECT; and c) QE or QS is updatable. 5) Case: a) If a is a , then the column descriptor of the i-th column of the is the same as the column descriptor of the i-th column of the . b) If a is an , then the column descriptor of the i-th column of the is the same as the column descriptor of the i-th column of the table identified by the
contained in the . c) Otherwise, the column descriptor of the i-th column of the is same as the column descriptor of the i- th column of the
, except that the is implementation-dependent and different from the of any column, other than itself, of a table referenced by any
contained in the SQL-statement. 6) Case: a) If a is a , then the column descriptor of the i-th column of the is the same as the column descriptor of the i-th column of the . Query expressions 197 X3H2-92-154/DBL CBR-002 7.10 b) Otherwise, the column descriptor of the i-th column of the is the same as the column descriptor of the i-th column of the . 7) Case: a) If a is a , then the column descriptor of the i-th column of the is the same as the column descriptor of the i-th column of the . b) Otherwise, the column descriptor of the i-th column of the is the same as the column descriptor of the i-th column of the . 8) If a set operator is specified in a or a , then let T1, T2, and TR be respec- tively the first operand, the second operand, and the result of the or . Let TN1 and TN2 be the effective names for T1 and T2, respectively. 9) If a set operator is specified in a or a , then let OP be the set operator. Case: a) If CORRESPONDING is specified, then: i) Within the columns of T1, the same shall not be specified more than once and within the columns of T2, the same shall not be specified more than once. ii) At least one column of T1 shall have a that is the of some column of T2. iii) Case: 1) If is not specified, then let SL be a of those s explic- itly appearing in the in the order that these s appear in the . Every in the shall be a of both T1 and T2. iv) The or is equivalent to: ( SELECT SL FROM TN1 ) OP ( SELECT SL FROM TN2 ) 198 Database Language SQL X3H2-92-154/DBL CBR-002 7.10 b) If CORRESPONDING is not specified, then T1 and T2 shall be of the same degree. 10)Case: a) If the is a , then the column descriptor of the i-th column of the is same as the column descriptor of the i-th column of the . b) Otherwise, i) Case: 1) Let C be the of the i-th column of T1. If the of the i-th column of T2 is C, then the of the i-th column of TR is C. 2) Otherwise, the of the i-th column of TR is implementation-dependent and different from the of any column, other than itself, of any table referenced by any
contained in the SQL-statement. ii) The data type of the i-th column of TR is determined by applying Subclause 9.3, "Set operation result data types", to the data types of the i-th column of T1 and the i-th column of T2. If the i-th column of both T1 and T2 are known not nullable, then the i-th column of TR is known not nullable; otherwise, the i-th column of T is possibly nullable. 11)Case: a) If a is a , then the column descriptor of the i-th column of the is the same as the column descriptor of the i-th column of the . b) Otherwise, the column descriptor of the i-th column of the is the same as the column descriptor of the i-th column of the . 12)Case: a) If a is a , then the column descriptor of the i-th column of the is the same as the column descriptor of the i-th column of the . Query expressions 199 X3H2-92-154/DBL CBR-002 7.10 b) Otherwise, i) Case: 1) Let C be the of the i-th column of T1. If the of the i-th column of T2 is C, then the of the i-th column of TR is C. 2) Otherwise, the of the i-th column of TR is implementation-dependent and different from the of any column, other than itself, of any table referenced by any
contained in the SQL-statement. ii) The data type of the i-th column of TR is determined by applying Subclause 9.3, "Set operation result data types", to the data types of the i-th column of T1 and the i-th column of T2. If the i-th column of both T1 and T2 are known not nullable, then the i-th column of TR is known not nullable; otherwise, the i-th column of T is possibly nullable. 13)Case: a) If a is a , then the column descriptor of the i-th column of the is the same as the column descriptor of the i-th column of the . b) Otherwise, the column descriptor of the i-th column of the is the same as the column descriptor of the i-th column of the . 14)The simply underlying tables of a are the tables identified by those
s, s, and s contained in the without an intervening , an intervening , or an intervening . 15)A is possibly non-deterministic if a) it contains a set operator UNION and ALL is not specified, or if it contains EXCEPT or INTERSECT; and b) the first or second operand contains a column that has a data type of character string. 16)The underlying columns of each column of QE and of QE itself are defined as follows: a) A column of a
has no underlying columns. b) The underlying columns of every i-th column of a ST are the underlying columns of the i-th column of the table immediately contained in ST. 200 Database Language SQL X3H2-92-154/DBL CBR-002 7.10 c) If no set operator is specified, then the underlying columns of every i-th column of QE are the underlying columns of the i-th column of the simply contained in QE. d) If a set operator is specified, then the underlying columns of every i-th column of QE are the underlying columns of the i-th column of T1 and those of the i-th column of T2. e) Let C be some column. C is an underlying column of QE if and only if C is an underlying column of some column of QE. Access Rules None. General Rules 1) Case: a) If no set operator is specified, then T is the result of the specified or . b) If a set operator is specified, then the result of applying the set operator is a table containing the following rows: i) Let R be a row that is a duplicate of some row in T1 or of some row in T2 or both. Let m be the number of duplicates of R in T1 and let n be the number of duplicates of R in T2, where m 0 and n 0. ii) If ALL is not specified, then Case: 1) If UNION is specified, then Case: A) If m > 0 or n > 0, then T contains exactly one dupli- cate of R. B) Otherwise, T contains no duplicate of R. 2) If EXCEPT is specified, then Case: A) If m > 0 and n = 0, then T contains exactly one dupli- cate of R. B) Otherwise, T contains no duplicate of R. 3) If INTERSECT is specified, then Case: A) If m > 0 and n > 0, then T contains exactly one dupli- cate of R. Query expressions 201 X3H2-92-154/DBL CBR-002 7.10 B) Otherwise, T contains no duplicates of R. iii) If ALL is specified, then Case: 1) If UNION is specified, then the number of duplicates of R that T contains is (m + n). 2) If EXCEPT is specified, then the number of duplicates of R that T contains is the maximum of (m - n) and 0. 3) If INTERSECT is specified, then the number of duplicates of R that T contains is the minimum of m and n. Note: See the General Rules of Subclause 8.2, "". 2) If a set operator is specified, then for each column whose data type is interval, let UDT be in turn the data type of the cor- responding column of T and let SV be the value of the column in each row of the first and second operands. The value of the corresponding column of T in the corresponding row of T is CAST (SV AS UDT) Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) A shall not be a
except in an . b) Conforming Intermediate SQL shall contain no . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) A shall not specify EXCEPT. b) A shall not specify INTERSECT. c) A shall not contain a . d) A shall not specify CORRESPONDING. e) If UNION is specified, then except for column names, the descriptors of the first and second operands shall be iden- tical and the descriptor of the result is identical to the descriptor of the operands. 202 Database Language SQL X3H2-92-154/DBL CBR-002 7.11 , , and
7.11 , , and
Function Specify a scalar value, a row, or a table derived from a . Format ::= ::=
::= ::= Syntax Rules 1) The degree of a shall be 1. 2) The degree of a shall be greater than 1. 3) The data type of a is the data type of the column of the immediately contained in the . 4) The data types of the columns of a or
are the data types of the respective columns of the immediately contained in the or
. Access Rules None. General Rules 1) If the cardinality of a or a is greater than 1, then an exception condition is raised: cardinal- ity violation. Leveling Rules 1) The following restrictions apply for Intermediate SQL: 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) If a is contained in a , then the
in the shall not contain a or a and shall not identify a grouped view. Query expressions 203 X3H2-92-154/DBL CBR-002 7.11 , , and
b) The contained in a shall be a . 204 Database Language SQL X3H2-92-154/DBL CBR-002 8 Predicates 8.1 Function Specify a condition that can be evaluated to give a truth value of true, false, or unknown. Format ::= | | | | | | | | | Syntax Rules None. Access Rules None. General Rules 1) The result of a is a truth value derived according to the General Rules of Subclause 8.2, "", Subclause 8.3, "", Subclause 8.4, "", Subclause 8.5, "", Subclause 8.6, "", Subclause 8.7, "", Subclause 8.8, "", Subclause 8.9, "", Subclause 8.10, "", or Subclause 8.11, "", as appropriate. Predicates 205 X3H2-92-154/DBL CBR-002 8.1 Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) A shall not be a . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain any . b) Conforming Entry SQL language shall not contain any . 206 Database Language SQL X3H2-92-154/DBL CBR-002 8.2 8.2 Function Specify a comparison of two row values. Format ::= ::= | | | | | Syntax Rules 1) The two s shall be of the same degree. 2) Let corresponding values be values with the same ordinal posi- tion in the two s. 3) The data types of the corresponding values of the two s shall be comparable. 4) Let X be a value in the first and Y be the corresponding value in the second . If X and Y have data type character string, then the pair-wise comparison collating sequence used to compare X and Y is de- termined by the table for collating sequences for comparisons (Subclause 4.2.3, "Rules determining collating sequence usage"). For any pair of corresponding character strings, let CS be the identified collating sequence. Access Rules None. General Rules 1) Let X and Y be any two corresponding s. Let XV and YV be the values represented by X and Y, respectively. Case: a) If XV or YV is the null value, then "X Y" is un- known. Predicates 207 X3H2-92-154/DBL CBR-002 8.2 b) If XV and YV are non-null values, then "X Y" is true or false as follows: i) "X = Y" is true if and only if XV and YV are equal. ii) "X <> Y" is true if and only if XV and YV are not equal. iii) "X < Y" is true if and only if XV is less than YV. iv) "X > Y" is true if and only if XV is greater than YV. v) "X <= Y" is true if and only if XV is not greater than YV. vi) "X >= Y" is true if and only if XV is not less than YV. vii) "X Y" is false if and only if "X Y" is not true. 2) Numbers are compared with respect to their algebraic value. 3) The comparison of two character strings is determined as fol- lows: a) If the length in characters of X is not equal to the length in characters of Y, then the shorter string is effectively replaced, for the purposes of comparison, with a copy of itself that has been extended to the length of the longer string by concatenation on the right of one or more pad char- acters, where the pad character is chosen based on CS. If CS has the NO PAD attribute, then the pad character is an implementation-dependent character different from any char- acter in the character set of X and Y that collates less than any string under CS. Otherwise, the pad character is a . b) The result of the comparison of X and Y is given by the col- lating sequence CS. c) Depending on the collating sequence, two strings may com- pare as equal even if they are of different lengths or con- tain different sequences of characters. When the operations MAX, MIN, DISTINCT, references to a grouping column, and the UNION, EXCEPT, and INTERSECT operators refer to character strings, the specific value selected by these operations from a set of such equal values is implementation-dependent. Note: If the coercibility attribute of the comparison is Coercible, then the collating sequence used is the default de- fined for the character repertoire. See also other Syntax Rules in this Subclause, Subclause 10.4, "", and Subclause 11.28, "". 208 Database Language SQL X3H2-92-154/DBL CBR-002 8.2 4) The comparison of two bit string values, X and Y, is determined by comparison of their bits with the same ordinal position. If Xi and Yi are the values of the i-th bits of X and Y, re- spectively, and if LX is the length in bits of X and LY is the length in bits of Y, then: a) X is equal to Y if and only if X = LY and Xi = Yi for all i. b) X is less than Y if and only if: i) LX < LY and Xi = Yi for all i less than or equal to LX; or ii) Xi = Yi for all i < n and Xn = 0 and Yn = 1 for some n less than or equal to the minimum of LX and LY. 5) The comparison of two datetimes is determined according to the interval resulting from their subtraction. Let X and Y be the two values to be compared and let H be the least significant of X and Y. The result of X Y is defined as: ( X - Y ) H INTERVAL (0) H Note: Two datetimes are comparable only if they have the same s; see Subclause 4.5.1, "Datetimes". 6) The comparison of two intervals is determined by the compari- son of their corresponding values after conversion to integers in some common base unit. Let X and Y be the two intervals to be compared. Let A TO B be the specified or implied datetime qualifier of X and C TO D be the specified or implied datetime qualifier of Y. Let T be the least significant of B and D and let U be a datetime qualifier of the form T(N), where N is an large enough so that significance is not lost in the CAST operation. X is effectively replaced by CAST (X AS INTERVAL U). Y is effectively replaced by CAST (Y AS INTERVAL U). The result of the comparison is effectively computed as: CAST ( X AS INTEGER ) CAST ( Y AS INTEGER ) 7) Let Rx and Ry be the two s of the and let RXi and RYi be the i-th s of Rx and Ry, respectively. "Rx Ry" is true, false, or unknown as follows: a) "x = Ry" is true if and only if RXi = RYi for all i. b) "x <> Ry" is true if and only if RXi <> RYi for some i. c) "x < Ry" is true if and only if RXi = RYi for all i < n and RXn < RYn for some n. d) "x > Ry" is true if and only if RXi = RYi for all i < n and RXn > RYn for some n. Predicates 209 X3H2-92-154/DBL CBR-002 8.2 e) "x <= Ry" is true if and only if Rx = Ry or Rx < Ry. f) "x >= Ry" is true if and only if Rx = Ry or Rx > Ry. g) "x = Ry" is false if and only if "Rx <> Ry" is true. h) "x <> Ry" is false if and only if "Rx = Ry" is true. i) "x < Ry" is false if and only if "Rx >= Ry" is true. j) "x > Ry" is false if and only if "Rx <= Ry" is true. k) "x <= Ry" is false if and only if "Rx > Ry" is true. l) "x >= Ry" is false if and only if "Rx < Ry" is true. m) "x Ry" is unknown if and only if "Rx Ry" is neither true nor false. Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. 210 Database Language SQL X3H2-92-154/DBL CBR-002 8.3 8.3 Function Specify a range comparison. Format ::= [ NOT ] BETWEEN AND Syntax Rules 1) The three s shall be of the same degree. 2) Let respective values be values with the same ordinal position in the two s. 3) The data types of the respective values of the three s shall be comparable. 4) Let X, Y, and Z be the first, second, and third s, respectively. 5) "X NOT BETWEEN Y AND Z" is equivalent to "NOT ( X BETWEEN Y AND Z )". 6) "X BETWEEN Y AND Z" is equivalent to "X>=Y AND X<=Z". Access Rules None. General Rules None. Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. Predicates 211 X3H2-92-154/DBL CBR-002 8.4 8.4 Function Specify a quantified comparison. Format ::= [ NOT ] IN ::=
| ::= { }... Syntax Rules 1) Let IVL be an . ( IVL ) is equivalent to the
: ( VALUES IVL ) 2) Let RVC be the and let IPV be the . 3) The expression RVC NOT IN IPV is equivalent to NOT ( RVC IN IPV ) 4) The expression RVC IN IPV is equivalent to RVC = ANY IPV Access Rules None. General Rules None. 212 Database Language SQL X3H2-92-154/DBL CBR-002 8.4 Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall not contain a in an that is not a . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. Predicates 213 X3H2-92-154/DBL CBR-002 8.5 8.5 Function Specify a pattern-match comparison. Format ::= [ NOT ] LIKE [ ESCAPE ] ::= ::= ::= Syntax Rules 1) The data types of , , and shall be character string. , , and shall be comparable. 2) Let M be the result of the of the , let P be the result of the of the , and let E be the result of the of the if one is specified. 3) "M NOT LIKE P" is equivalent to "NOT (M LIKE P)". 4) Case: a) If is not specified, then the collat- ing sequence used for the is determined by Table 3, "Collating sequence usage for comparisons", taking as comparand 1 and as comparand 2. b) Otherwise, let C1 be the coercibility attribute and collat- ing sequence of the , and C2 be the coercibility attribute and collating sequence of the . Let C3 be the resulting coercibility attribute and collating sequence as determined by Table 2, "Collating coercibility rules for dyadic operators", taking C1 as the operand 1 coercibility and C2 as the operand 2 coercibility. The collating sequence used for the is determined by Table 3, "Collating sequence usage for comparisons", taking C3 as the coercibility attribute and collating sequence of comparand 1 and as comparand 2. 214 Database Language SQL X3H2-92-154/DBL CBR-002 8.5 Access Rules None. General Rules 1) If an is specified and M, P, or E is the null value, then M LIKE P ESCAPE E is unknown. 2) If an is not specified and M or P is the null value, then M LIKE P is unknown. 3) Case: a) If an is specified, then: i) If the length in characters of E is not equal to 1, then an exception condition is raised: data exception-invalid escape character. ii) If there is not a partitioning of the string P into sub- strings such that each substring has length 1 or 2, no substring of length 1 is the escape character E, and each substring of length 2 is the escape character E followed by either the escape character E, an character, or the character, then an exception condition is raised: data exception-invalid escape sequence. If there is such a partitioning of P, then in that parti- tioning, each substring with length 2 represents a single occurrence of the second character of that substring. Each substring with length 1 that is the character represents an arbitrary character specifier. Each substring with length 1 that is the character represents an arbitrary string specifier. Each substring with length 1 that is neither the character nor the character represents the character that it contains. b) If an is not specified, then each character in P represents an arbitrary character spec- ifier, each character in P represents an arbitrary string specifier, and each character in P that is neither the character nor the character represents itself. Predicates 215 X3H2-92-154/DBL CBR-002 8.5 4) The string P is a sequence of the minimum number of substring specifiers such that each of P is part of exactly one substring specifier. A substring specifier is an arbitrary character specifier, an arbitrary string spec- ifier, or any sequence of s other than an arbitrary character specifier or an arbitrary string specifier. 5) Case: a) If M and P are character strings whose lengths are variable and if the lengths of both M and P are 0, then M LIKE P is true. b) The M LIKE P is true if there exists a partitioning of M into substrings such that: i) A substring of M is a sequence of 0 or more contiguous s of M and each of M is part of exactly one substring. ii) If the i-th substring specifier of P is an arbitrary char- acter specifier, the i-th substring of M is any single . iii) If the i-th substring specifier of P is an arbitrary string specifier, then the i-th substring of M is any sequence of 0 or more s. iv) If the i-th substring specifier of P is neither an arbi- trary character specifier nor an arbitrary string speci- fier, then the i-th substring of M is equal to that sub- string specifier according to the collating sequence of the , without the appending of characters to M, and has the same length as that substring specifier. v) The number of substrings of M is equal to the number of substring specifiers of P. c) Otherwise, M LIKE P is false. 216 Database Language SQL X3H2-92-154/DBL CBR-002 8.5 Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) The shall be a . b) A shall be a . c) An shall be a . Predicates 217 X3H2-92-154/DBL CBR-002 8.6 8.6 Function Specify a test for a null value. Format ::= IS [ NOT ] NULL Syntax Rules None. Access Rules None. General Rules 1) Let R be the value of the . 2) If all the values in R are the null value, then "R IS NULL" is true; otherwise, it is false. 3) If none of the values in R are the null value, then "R IS NOT NULL" is true; otherwise, it is false. Note: For all R, "R IS NOT NULL" has the same result as "NOT R IS NULL" if and only if R is of degree 1. Table 12, " semantics", specifies this behavior. ________________Table_12-_semantics________________ R IS R IS NOT NOT R IS NOT R IS NOT _Expression_______NULL____NULL__________NULL__________NULL_________ | degree 1: null | true | false | false | true | | | | | | | | degree 1: not | false | true | true | false | null | degree > 1: | true | false | false | true | | all null | | | | | | | | | | | | degree > 1: | false | false | true | true | | some null | | | | | | | | | | | | degree > 1: | false | true | true | false | |_none_null______|_______|_____________|____________|______________| | | | | | | |Leveling Rules | | | | | | | | | | | 218 Database Language SQL X3H2-92-154/DBL CBR-002 8.6 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) A shall be a . Predicates 219 X3H2-92-154/DBL CBR-002 8.7 8.7 Function Specify a quantified comparison. Format ::=
::= | ::= ALL ::= SOME | ANY Syntax Rules 1) The shall be of the same degree as the result of the
. 2) The data types of the values of the shall be respectively comparable to those of the columns of the
. 3) The collating sequence for each pair of respective values in the is determined in the same manner as described in Subclause 8.2, "". Access Rules None. General Rules 1) Let R be the result of the and let T be the result of the
. 2) The result of "R T" is derived by the application of the implied "R RT" to every row RT in T: Case: a) If T is empty or if the implied is true for every row RT in T, then "R T" is true. b) If the implied is false for at least one row RT in T, then "R T" is false. 220 Database Language SQL X3H2-92-154/DBL CBR-002 8.7 c) If the implied is true for at least one row RT in T, then "R T" is true. d) If T is empty or if the implied is false for every row RT in T, then "R T" is false. e) If "R T" is neither true nor false, then it is unknown. Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. Predicates 221 X3H2-92-154/DBL CBR-002 8.8 8.8 Function Specify a test for a non-empty set. Format ::= EXISTS
Syntax Rules None. Access Rules None. General Rules 1) Let T be the result of the
. 2) If the cardinality of T is greater than 0, then the result of the is true; otherwise, the result of the is false. Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. 222 Database Language SQL X3H2-92-154/DBL CBR-002 8.9 8.9 Function Specify a test for the absence of duplicate rows. Format ::= UNIQUE
Syntax Rules None. Access Rules None. General Rules 1) Let T be the result of the
. 2) If there are no two rows in T such that the value of each column in one row is non-null and is equal to the value of the cor- responding column in the other row according to Subclause 8.2, "", then the result of the is true; otherwise, the result of the is false. Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain any . Predicates 223 X3H2-92-154/DBL CBR-002 8.10 8.10 Function Specify a test for matching rows. Format ::= MATCH [ UNIQUE ] [ PARTIAL | FULL ]
Syntax Rules 1) The shall be of the same degree as the
. 2) The data types of the values of the shall be respectively comparable to those of the corresponding columns of the
. 3) The collating sequence for each pair of respective values in the is determined in the same manner as described in Subclause 8.2, "". Access Rules None. General Rules 1) Let R be the . 2) If neither PARTIAL nor FULL is specified, then Case: a) If some value in R is the null value, then the is true. b) If no value in R is the null value, then Case: i) If UNIQUE is not specified and there exists a (possibly non-unique) row RTi of the
such that R = RTi then the is true. ii) If UNIQUE is specified and there is a unique row RTi of the
such that R = RTi 224 Database Language SQL X3H2-92-154/DBL CBR-002 8.10 then the is true. iii) Otherwise, the is false. 3) If PARTIAL is specified, then Case: a) If all values in R are the null value, then the is true. b) Otherwise, Case: i) If UNIQUE is not specified and there exists a (possibly non-unique) row RTi of the
such that each non-null value of R equals its corresponding value in RTi, then the is true. ii) If UNIQUE is specified and there is a unique row RTi of the
such that each non-null value of R equals its corresponding value in RTi, then the is true. iii) Otherwise, the is false. 4) If FULL is specified, then Case: a) If all values in R are the null value, then the is true. b) If no values in R are the null value, then Case: i) If UNIQUE is not specified and there exists a (possibly non-unique) row RTi of the
such that R = RTi then the is true. ii) If UNIQUE is specified and there exists a unique row RTi of the
such that R = RTi then the is true. iii) Otherwise, the is false. c) Otherwise, the is false. Predicates 225 X3H2-92-154/DBL CBR-002 8.10 Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall not contain any . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. 226 Database Language SQL X3H2-92-154/DBL CBR-002 8.11 8.11 Function Specify a test for an overlap between two events. Format ::= OVERLAPS ::= ::= Syntax Rules 1) The degree of and shall both be 2. 2) The data type of the first column of each shall be a datetime data type and the first column of each shall be comparable. Note: Two datetimes are comparable only if they have the same s; see Subclause 4.5.1, "Datetimes". 3) The data type of the second column of each shall be a datetime data type or INTERVAL. Case: a) If the data type is INTERVAL, then the precision of the data type shall be such that the interval can be added to the datetime data type contained in the first column of the . b) If the data type is a datetime data type, then it shall be comparable with the datetime data type contained in the first column of the . Access Rules None. General Rules 1) Let D1 be the value of the first column of and D2 be the value of the first column of . Predicates 227 X3H2-92-154/DBL CBR-002 8.11 2) Case: a) If the data type of the second column of is a datetime data type, then let E1 be the value of the second column of . b) If the data type of the second column of is INTERVAL, then let I1 be the value of the second column of . Let E1 = D1 + I1. 3) If D1 is the null value or if E1 < D1, then let S1 = E1 and let T1 = D1. Otherwise, let S1 = D1 and let T1 = E1. 4) Case: a) If the data type of the second column of is a datetime data type, then let E2 be the value of the second column of . b) If the data type of the second column of is INTERVAL, then let I2 be the value of the second column of . Let E2 = D2 + I2. 5) If D2 is the null value or if E2 < D2, then let S2 = E2 and let T2 = D2. Otherwise, let S2 = D2 and let T2 = E2. 6) The result of the is the result of the following expression: ( S1 > S2 AND NOT ( S1 >= T2 AND T1 >= T2 ) ) OR ( S2 > S1 AND NOT ( S2 >= T1 AND T2 >= T1 ) ) OR ( S1 = S2 AND ( T1 <> T2 OR T1 = T2 ) ) Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain any . 228 Database Language SQL X3H2-92-154/DBL CBR-002 8.12 8.12 Function Specify a condition that has the truth value true, false, or unknown, depending on the result of applying boolean operators to specified conditions. Format ::= | OR ::= | AND ::= [ NOT ] ::= [ IS [ NOT ] ] ::= TRUE | FALSE | UNKNOWN ::= | Syntax Rules 1) If NOT is specified in a , then let BP be the contained and let TV be the contained . The is equivalent to: ( NOT ( BP IS TV ) ) Access Rules None. General Rules 1) The result is derived by the application of the specified boolean operators ("AND", "OR", "IS", and "NOT") to the results derived from each evaluation. If boolean operators are not specified, then the result of the is the result of the specified . Predicates 229 X3H2-92-154/DBL CBR-002 8.12 2) NOT(true) is false, NOT(false) is true, and NOT(unknown) is unknown. Table 13, "Truth table for the AND boolean", Table 14, "Truth table for the OR boolean", and Table 15, "Truth table for the IS boolean" specify the semantics of AND, OR, and IS, respectively. ______________Table_13-Truth_table_for_the_AND_boolean_____________ ___________________________________________________________________ | true | true | false | unknown | | | | | | | false | false | false | false | | | | | | |__________|_________|__________|__________________________________| | | | | | ______________Table_14-Truth_table_for_the_OR_boolean______________ ___________________________________________________________________ | true | true | true | true | | | | | | | false | true | false | unknown | | | | | | |__________|_________|__________|__________________________________| | | | | | ______________Table_15-Truth_table_for_the_IS_boolean______________ _IS_________TRUE______FALSE______UNKNOWN___________________________ | true | true | false | false | | | | | | | false | false | true | false | | | | | | |__________|_________|__________|__________________________________| | | | | | 3) When a S is evaluated against a row of a table, each reference to a column of that table by a directly contained in S is a reference to the value of that column in that row. Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) A shall not specify a . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. 230 Database Language SQL X3H2-92-154/DBL CBR-002 9 Data assignment rules 9.1 Retrieval assignment Function Specify rules for value assignments that retrieve SQL-data. Syntax Rules 1) Let T and V be a TARGET and VALUE specified in an application of this Subclause. 2) If the data type of T is character string, bit string, numeric, datetime, or interval, then the data type of V shall be a mu- tually assignable character string type, a bit string type, a numeric type, the same datetime type, or a comparable interval type, respectively. General Rules 1) If V is the null value, then Case: a) If an indicator is specified for T, then that indicator is set to -1. b) If no indicator is specified for T, then an exception con- dition is raised: data exception-null value, no indicator parameter. 2) If V is not the null value and T has an indicator, then Case: a) If the data type of T is character string or bit string and the length in characters or bits, respectively, M of V is greater than the length in characters or bits, respectively, of T, then the indicator is set to M. If M exceeds the maxi- mum value that the indicator can contain, then an exception condition is raised: data exception-indicator overflow. b) Otherwise, the indicator is set to 0. 3) If V is not the null value, then Case: a) If the data type of T is fixed-length character string with length in characters L and the length in characters of V is equal to L, then the value of T is set to V. Data assignment rules 231 X3H2-92-154/DBL CBR-002 9.1 Retrieval assignment b) If the data type of T is fixed-length character string with length in characters L, and the length in characters of V is greater than L, then the value of T is set to the first L characters of V and a completion condition is raised: warning-string data, right truncation. c) If the data type of T is fixed-length character string with length in characters L, and the length in characters M of V is smaller than L, then the first M characters of T are set to V, and the last L-M characters of T are set to s. d) If the data type of T is variable-length character string and the length in characters M of V is not greater than the maximum length in characters of T, then the value of T is set to V and the length in characters of T is set to M. e) If the data type of T is variable-length character string and the length in characters of V is greater than the maximum length in characters L of T, then the value of T is set to the first L characters of V, the length in characters of T becomes L, and a completion condition is raised: warning- string data, right truncation. f) If the data type of T is fixed-length bit string with length in bits L and the length in bits of V is equal to L, then the value of T is set to V. g) If the data type of T is fixed-length bit string with length in bits L and the length in bits of V is greater than L, then the value of T is set to the first L bits of V and a completion condition is raised: warning-string data, right truncation. h) If the data type of T is fixed-length bit string with length in bits L and the length in bits M of V is smaller than L, then the first M bits of T are set to V, the remaining bits of T are set to bits each with the value of 0, and a comple- tion condition is raised: warning-implicit zero-bit padding. i) If the data type of T is variable-length bit string and the length in bits M of V is not greater than the maximum length in bits of T, then the value of T is set to V and the length in bits of T is set to M. j) If the data type of T is variable-length bit string, and the length in bits of V is greater than the maximum length in bits L of T, then the value of T is set to the first L bits of V, the length in bits of T is set to L, and a completion condition is raised: warning-string data, right truncation. 232 Database Language SQL X3H2-92-154/DBL CBR-002 9.1 Retrieval assignment k) If the data type of T is numeric and there is an approxi- mation obtained by rounding or truncation of the numerical value of V for the data type of T, then the value of T is set to such an approximation. If there is no such approximation, then an exception condi- tion is raised: data exception-numeric value out of range. If the data type of T is exact numeric, then it is implementation- defined whether the approximation is obtained by rounding or by truncation. l) If the data type of T is datetime and there is a representa- tion of the value of V in the data type of T, then the value of T is set to that representation. m) If the data type of T is interval and there is a representa- tion of the value of V in the data type of T, then the value of T is set to that representation. Otherwise, an exception condition is raised: data exception-interval field overflow. Data assignment rules 233 X3H2-92-154/DBL CBR-002 9.2 Store assignment 9.2 Store assignment Function Specify rules for value assignments that store SQL-data. Syntax Rules 1) Let T and V be a TARGET and VALUE specified in an application of this Subclause. 2) If the data type of T is character string, bit string, numeric, datetime, or interval, then the data type of V shall be char- acter string, bit string, numeric, the same datetime type, or a comparable interval type, respectively. General Rules 1) Let T be an object column. 2) If the value of V is the null value, then the value of T is set to the null value. 3) Otherwise, let V denote a non-null value of T. Case: a) If the data type of T is fixed-length character string with length in characters L and the length in characters of V is equal to L, then the value of T is set to V. b) If the data type of T is fixed-length character string with length in characters L and the length in characters M of V is larger than L, then Case: i) If the rightmost M-L characters of V are all s, then the value of T is set to the first L characters of V. ii) If one or more of the rightmost M-L characters of V are not s, then an exception condition is raised: data exception-string data, right truncation. c) If the data type of T is fixed-length character string with length in characters L and the length in characters M of V is less than L, then the first M characters of T are set to V and the last L-M characters of T are set to s. d) If the data type of T is variable-length character string and the length in characters M of V is not greater than the maximum length in characters of T, then the value of T is set to V and the length in characters of T is set to M. e) If the data type of T is variable-length character string and the length in characters M of V is greater than the maximum length in characters L of T, then, 234 Database Language SQL X3H2-92-154/DBL CBR-002 9.2 Store assignment Case: i) If the rightmost M-L characters of V are all s, then the value of T is set to the first L characters of V and the length in characters of T is set to L. ii) If one or more of the rightmost M-L characters of V are not s, then an exception condition is raised: data exception-string data, right truncation. f) If the data type of T is fixed-length bit string with length in bits L and the length in bits of V is equal to L, then the value of T is set to V. g) If the data type of T is fixed-length bit string with length in bits L and the length in bits M of V is greater than L, then an exception condition is raised: data exception-string data, right truncation. h) If the data type of T is fixed-length bit string with length in bits L and the length in bits M of V is less than L, then an exception condition is raised: data exception-string data, length mismatch. i) If the data type of T is variable-length bit string and the length in bits M of V is not greater than the maximum length in bits of T, then the value of T is set to V and the length in bits of T is set to M. j) If the data type of T is variable-length bit string, and the length in bits M of V is greater than the maximum length in bits L of T, then an exception condition is raised: data exception-string data, right truncation. k) If the data type of T is numeric and there is an approxi- mation obtained by rounding or truncation of the numerical value of V for the data type of T, then the value of T is set to such an approximation. If there is no such approximation, then an exception condi- tion is raised: data exception-numeric value out of range. If the data type of T is exact numeric, then it is implementation- defined whether the approximation is obtained by rounding or by truncation. l) If the data type of T is datetime and there is a representa- tion of the value of V in the data type of T, then the value of T is set to that representation. m) If the data type of T is interval and there is a representa- tion of the value of V in the data type of T, then the value of T is set to that representation. Otherwise, an exception condition is raised: data exception-interval field overflow. Data assignment rules 235 X3H2-92-154/DBL CBR-002 9.2 Store assignment 4) If the column definition of T includes the name of a domain whose domain descriptor includes a domain constraint D, then D is effectively checked. If D is not satisfied, then an exception condition is raised: integrity constraint violation. 236 Database Language SQL X3H2-92-154/DBL CBR-002 9.3 Set operation result data types 9.3 Set operation result data types Function Specify the Syntax Rules and result data types for s and s having set operators. Syntax Rules 1) Let DTS be a set of data types specified in an application of this Subclause. 2) All of the data types in DTS shall be comparable. 3) Case: a) If any of the data types in DTS is character string, then all data types in DTS shall be character string, and all of them shall have the same character repertoire. That charac- ter repertoire is the character repertoire of the result. The character set of the result is the character set of one of the data types in DTS. The specific character set chosen is implementation-dependent. The collating sequence and the co- ercibility attribute are determined as specified in Table 2, "Collating coercibility rules for dyadic operators". Case: i) If any of the data types in DTS is variable-length char- acter string, then the result data type is variable-length character string with maximum length in characters equal to the maximum of the lengths in characters and maximum lengths in characters of the data types in DTS. ii) Otherwise, the result data type is fixed-length character string with length in characters equal to the maximum of the lengths in characters of the data types in DTS. b) If any of the data types in DTS is bit string, then all data types in DTS shall be bit string. Case: i) If any of the data types in DTS is variable-length bit string, then the result data type is variable-length bit string with maximum length in bits equal to the maximum of the lengths in bits and maximum lengths in bits of the data types in DTS. ii) Otherwise, the result data type is fixed-length bit string with length in bits equal to the maximum of the lengths in bits of the data types in DTS. Data assignment rules 237 X3H2-92-154/DBL CBR-002 9.3 Set operation result data types c) If all of the data types in DTS are exact numeric, then the result data type is exact numeric with implementation-defined precision and with scale equal to the maximum of the scales of the data types in DTS. d) If any data type in DTS is approximate numeric, then each data type in DTS shall be numeric and the result data type is approximate numeric with implementation-defined precision. e) If any data type in DTS is a datetime data type, then each data type in DTS shall be the same datetime data type. The result data type is the same datetime data type. f) If any data type in DTS is interval, then each data type in DTS shall be interval. If the precision of any data type in DTS specifies YEAR or MONTH, then the precision of each data type shall specify only YEAR or MONTH. If the preci- sion of any data type in DTS specifies DAY, HOUR, MINUTE, or SECOND(N), then the precision of no data type of DTS shall specify the s YEAR and MONTH. The result data type is interval with precision "S TO E", where S and E are the most significant of the s and the least significant of the s of the data types in DTS, respectively. General Rules None. 238 Database Language SQL X3H2-92-154/DBL CBR-002 10 Additional common elements 10.1 Function Specify the precision of an interval data type. Format ::= TO | ::= [ ] ::= | SECOND [ ] ::= [ ] | SECOND [ [ ] ] ::= | SECOND ::= YEAR | MONTH | DAY | HOUR | MINUTE ::= ::= Additional common elements 239 X3H2-92-154/DBL CBR-002 10.1 Syntax rules 1) There is a significance of ordering of s. In order from most significant to least significant, the ordering is: YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. A or with an i is more significant than a or with an j if i>j. An or with an i is more significant than an or with an j if i>j. 2) If TO is specified, then: a) shall be more significant than , b) shall not specify MONTH, and c) if specified YEAR, then shall spec- ify MONTH. 3) The maximum value of is implementation-defined, but shall not be less than 2. 4) The maximum value of is implementation-defined, but shall not be less than 6. 5) An , if specified, shall be greater than 0 and shall not be greater than the implementation- defined maximum. If is not specified, then an of 2 is implicit. 6) An , if specified, shall be greater than or equal to 0 and shall not be greater than the implementation-defined maximum. If SECOND is specified and is not specified, then an of 6 is implicit. Access Rules None. General Rules 1) An item qualified by an contains the date- time fields identified by the . Case: a) If the specifies a , then the identifies a single . Any reference to the most significant or 240 Database Language SQL X3H2-92-154/DBL CBR-002 10.1 least significant of the item refers to that . b) Otherwise, the identifies those datetime fields from to , inclusive. 2) An specifies Case: a) If the is SECOND, then the number of decimal digits of precision before the specified or implied decimal point of the seconds . b) Otherwise, the number of decimal digits of precision of the first . 3) An specifies the num- ber of decimal digits of precision following the specified or implied decimal point in the SECOND. 4) If is not specified and and are the same , then the is equivalent to a that is that . 5) The length in positions of an item of type interval is computed as follows. Case: a) If the item is a year-month interval, then Case: i) If the is a , then the length in positions of the item is the implicit or explicit of the . ii) Otherwise, the length in positions of the item is the im- plicit or explicit of the plus 2 (the length of the that is the ) plus 1 (the length of the between the and the in a ). b) Otherwise, Case: i) If the is a that does not specify SECOND, then the length in positions of the item is the implicit or explicit of the . Additional common elements 241 X3H2-92-154/DBL CBR-002 10.1 ii) If the is a that specifies SECOND, then the length in positions of the item is the implicit or explicit of the plus the implicit or explicit . If is greater than zero, then the length in positions of the item is increased by 1 (the length in positions of the between the and the ). iii) Otherwise, let participating datetime fields mean the date- time fields that are less significant than the and more significant than the of the . The length in positions of each par- ticipating datetime field is 2. Case: 1) If is SECOND, then the length in positions of the item is the implicit or explicit , plus 3 times the number of par- ticipating datetime fields (each participating datetime field has length 2 positions , plus the s or s that precede them have length 1 position), plus the implicit or explicit , plus 1 (the length in positions of the preceding the ). If is greater than zero, then the length in positions of the item is increased by 1 (the length in positions of the within the field identified by the ). 2) Otherwise, the length in positions of the item is the implicit or explicit , plus 3 times the number of participating datetime fields (each participating datetime field has length 2 positions, plus the s or s that pre- cede them have length 1 position), plus 2 (the length in positions of the ), plus 1 (the length in positions of the preceding the ). Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain any . 242 Database Language SQL X3H2-92-154/DBL CBR-002 10.2 10.2 Function Specify a standard programming language. Format ::= LANGUAGE ::= ADA | C | COBOL | FORTRAN | MUMPS | PASCAL | PLI Syntax Rules None. Access Rules None. General Rules 1) The standard programming language specified by the clause is defined in the International Standard identified by the keyword. Table 16, "Standard programming languages", specifies the relationship. ______________Table_16-Standard_programming_languages______________ Language _keyword______Relevant_standard____________________________________ | ADA | ISO/IEC 8652 | | | | | C | ISO/IEC 9899 | | | | | COBOL | ISO 1989 | | | | | FORTRAN | ISO/IEC 1539 | | | | | MUMPS | ISO/IEC 11756 | | | | | PASCAL | ISO 7185 and ISO/IEC 10206 | | | | |_PLI________|_ISO_6160____________________________________________| | | | Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. Additional common elements 243 X3H2-92-154/DBL CBR-002 10.2 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) A shall not specify MUMPS. 244 Database Language SQL X3H2-92-154/DBL CBR-002 10.3 10.3 Function Specify privileges. Format ::= ALL PRIVILEGES | ::= [ { }... ] ::= SELECT | DELETE | INSERT [ ] | UPDATE [ ] | REFERENCES [ ] | USAGE ::= ::= PUBLIC | Syntax Rules 1) If the of the or specifying specifies
, then let T be the table identified by that
. T shall not be a declared local temporary table. 2) If T is a temporary table, then shall specify ALL PRIVILEGES. 3) Each in a shall identify a column of T. 4) UPDATE () is equivalent to the spec- ification of UPDATE () for each in . INSERT () is equivalent to the specification of INSERT () for each in . REFERENCES () is equivalent to the specification of Additional common elements 245 X3H2-92-154/DBL CBR-002 10.3 REFERENCES () for each in . 5) ALL PRIVILEGES is equivalent to the specification of all of the privileges on for which the current has grantable privilege descriptors. Access Rules None. General Rules 1) A of PUBLIC denotes at all times a list of s containing all of the s in the SQL environment. 2) The set of applicable privileges for an includes those privileges defined by privilege descriptors associated with that , together with those defined by privilege descriptors associated with PUBLIC. 3) UPDATE () specifies the UPDATE privilege on the in- dicated column and implies one or more column privilege descrip- tors. If the is omitted, then UPDATE specifies the UPDATE privilege on all columns of T including any column subsequently added to T and implies a table privilege descriptor and one or more column privilege descriptors. 4) INSERT () specifies the INSERT privilege on the in- dicated column and implies one or more column privilege descrip- tors. If the is omitted, then INSERT specifies the INSERT privilege on all columns of T including any column subsequently added to T and implies a table privilege descriptor and one or more column privilege descriptors. 5) REFERENCES () specifies the REFERENCES privilege on the indicated column and implies one or more column privilege descriptors. If the is omitted, then REFERENCES specifies the REFERENCES privilege on all columns of T including any column subsequently added to T and implies a table privilege descriptor and one or more column privilege descriptors. Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) An that specifies INSERT shall not contain a . 246 Database Language SQL X3H2-92-154/DBL CBR-002 10.3 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. Additional common elements 247 X3H2-92-154/DBL CBR-002 10.4 10.4 Function Identify a character set. Format ::= | | | | ::= ::= ::= ::= ::= Syntax Rules 1) The s, s, s, and s that are supported are implementation-defined. 2) A character set identified by a , by an , by a , or by an has associated with it a privilege descriptor that was effec- tively defined by the GRANT USAGE ON CHARACTER SET CS TO PUBLIC WITH GRANT OPTION where CS is the contained in the . The grantor of the privilege descriptor is set to the special grantor value "_SYSTEM". 3) The s shall include SQL_TEXT. 248 Database Language SQL X3H2-92-154/DBL CBR-002 10.4 Access Rules 1) Let C be the contained in the . The applicable privileges shall include USAGE on C. General Rules 1) A identifies a character set. Let the identified character set be CS. Note: A character set comprises the characters in the character set's repertoire together with a form-of-use that specifies the convention for arranging those characters into character strings. 2) A specifies the name of a character repertoire that is defined by a national or interna- tional standard. The character repertoire and form-of-use of CS, implied by the , are defined by the standard that defined that . The default collating sequence of the character reper- toire is defined by the order of the characters in the standard and has the PAD SPACE attribute. 3) An speci- fies the name of a character repertoire that is implementation- defined. The character repertoire and form-of-use of CS, implied by the , are implementation-defined. The default collating sequence of the character repertoire and whether the collating sequence has the NO PAD attribute or the PAD SPACE attribute is implementation- defined. 4) A identifies a charac- ter set whose descriptor is in some schema whose is not INFORMATION_SCHEMA. Note: The default collating sequence and form-of-use of CS are as defined in Subclause 11.28, "". 5) A identifies form-of-use that is defined by some national or international standard. That form-of-use is the form-of-use of CS. The charac- ter repertoire of CS is as defined in that standard. The default collating sequence of the character repertoire is defined by the order of the characters in ISO/IEC 10646 and has the PAD SPACE attribute. Note: Specific forms-of-use implied by this rule include ISO 2022 code extension techniques. 6) An identifies an implementation-defined form-of-use that shall be the form-of-use of CS. The implied character repertoire and Additional common elements 249 X3H2-92-154/DBL CBR-002 10.4 default collating sequence of CS and whether the collating se- quence has the NO PAD attribute or the PAD SPACE attribute are implementation-defined. Note: Specific forms-of-use implied by this rule include implementation-defined techniques such as mixed one-octet/two- octet Latin/Kanji or Compound String. 7) There is a character set descriptor for every character set that can be specified by a . Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain a . 250 Database Language SQL X3H2-92-154/DBL CBR-002 10.5 10.5 Function Specify a collating sequence. Format ::= COLLATE Syntax Rules None. Access Rules 1) Let C be the contained in the . The applicable privileges shall include USAGE on C. General Rules None. Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall not contain any . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. Additional common elements 251 X3H2-92-154/DBL CBR-002 10.6 and 10.6 and Function Specify the name of a constraint and its attributes. Format ::= CONSTRAINT ::= [ [ NOT ] DEFERRABLE ] | [ NOT ] DEFERRABLE [ ] ::= INITIALLY DEFERRED | INITIALLY IMMEDIATE Syntax Rules 1) If a is contained in a , and if the contains a , then that shall be the same as the specified or implicit of the containing . 2) The of shall be differ- ent from the of the of any other constraint defined in the same schema. 3) If is not specified, then INITIALLY IMMEDIATE is implicit. 4) Case: a) If INITIALLY DEFERRED is specified, then: i) NOT DEFERRABLE shall not be specified. ii) If DEFERRABLE is not specified, then DEFERRABLE is im- plicit. b) If INITIALLY IMMEDIATE is specified or implicit and nei- ther DEFERRABLE nor NOT DEFERRABLE is specified, then NOT DEFERRABLE is implicit. Access Rules None. 252 Database Language SQL X3H2-92-154/DBL CBR-002 10.6 and General Rules 1) If NOT DEFERRABLE is specified, then the constraint is not de- ferrable; otherwise it is deferrable. 2) If is INITIALLY DEFERRED, then the ini- tial constraint mode for the constraint is deferred; otherwise, the initial constraint mode for the constraint is immediate. 3) If, on completion of any SQL-statement, the constraint mode of any constraint is immediate, then that constraint is effectively checked. Note: This includes the cases where is a , a , or the statement that causes a constraint with a constraint mode of initially immediate to be created. 4) When a constraint is effectively checked, if the constraint is not satisfied, then an exception condition is raised: integrity constraint violation. If this exception condition is raised as a result of executing a , then SQLSTATE is not set to integrity constraint violation, but is set to transaction rollback-integrity constraint violation (see the General Rules of Subclause 14.3, ""). Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall contain no ex- plicit . Note: This means that INITIALLY IMMEDIATE NOT DEFERRABLE is implicit. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Intermediate SQL language shall contain no . Additional common elements 253 X3H2-92-154/DBL CBR-002 254 Database Language SQL X3H2-92-154/DBL CBR-002 11 Schema definition and manipulation 11.1 Function Define a schema. Format ::= CREATE SCHEMA [ ] [ ... ] ::= | AUTHORIZATION | AUTHORIZATION ::= ::= DEFAULT CHARACTER SET ::= |
| | | | | | Syntax Rules 1) If is not specified, then a equal to is implicit. 2) If AUTHORIZATION is not speci- fied, then Schema definition and manipulation 255 X3H2-92-154/DBL CBR-002 11.1 Case: a) If the is contained in a that has a specified, then an equal to that is implicit for the . b) Otherwise, an equal to the SQL- session is implicit. 3) The of the explicit or implicit shall be different from the of the of any other schema in the catalog identified by the of . 4) If a appears in a in a , then the effective and during processing of the is the and specified or implicit in the . Other SQL-statements executed in s in the have the and specified or implicit for the . 5) If is not specified, then a containing an implementation-defined is im- plicit. Access Rules 1) The privileges necessary to execute the are implementation-defined. General Rules 1) A schema S is created with a name equal to the explicit or im- plicit and a default character set name equal to the of the explicit or implicit . 2) The is the current for privilege determination for S. 3) Those objects defined by s (base tables, views, constraints, domains, assertions, character sets, translations, collations, privileges) and their associated descriptors are effectively created. 4) The explicit or implicit is used as the default character set used for all s and s that do not specify an explicit charac- ter set. 256 Database Language SQL X3H2-92-154/DBL CBR-002 11.1 Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall not contain any . b) Conforming Intermediate SQL language shall not contain any . c) Conforming Intermediate SQL language shall not contain any . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Intermediate SQL language shall not contain any . b) A shall specify AUTHORIZATION and shall not specify a . c) A shall not be speci- fied. d) Conforming Entry SQL language shall not contain any . Schema definition and manipulation 257 X3H2-92-154/DBL CBR-002 11.2 11.2 Function Destroy a schema. Format ::= DROP SCHEMA ::= CASCADE | RESTRICT Syntax Rules 1) Let S be the schema identified by . 2) S shall identify a schema in the catalog identified by the ex- plicit or implicit . 3) If RESTRICT is specified, then S shall not contain any per- sistent base tables, global temporary tables, created local temporary tables, views, domains, assertions, character sets, collations, or translations. Note: If CASCADE is specified, then such objects will be dropped by the effective execution of the SQL schema manipulation state- ments specified in the General Rules of this Subclause. Access Rules 1) The current shall be equal to the that owns the schema identified by the . General Rules 1) Let T be the
of any base table or temporary ta- ble contained in S. The following is effectively executed: DROP TABLE T CASCADE 2) Let V be the
of any view contained in S. The fol- lowing is effectively executed: DROP VIEW V CASCADE 3) Let D be the of any domain contained in S. The following is effectively executed: DROP DOMAIN D CASCADE 258 Database Language SQL X3H2-92-154/DBL CBR-002 11.2 4) Let A be the of any assertion contained in S. The following is effectively exe- cuted: DROP ASSERTION A 5) Let CD be the of any collation definition contained in S. The following is effectively executed: DROP COLLATION CD 6) Let TD be the of any translation contained in S. The following is effectively executed: DROP TRANSLATION TD 7) Let RD be the of any character set con- tained in S. The following is effectively executed: DROP CHARACTER SET RD 8) The identified schema and its description are destroyed. Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain a . Schema definition and manipulation 259 X3H2-92-154/DBL CBR-002 11.3
11.3
Function Define a persistent base table, a created local temporary table, or a global temporary table. Format
::= CREATE [ { GLOBAL | LOCAL } TEMPORARY ] TABLE
[ ON COMMIT { DELETE | PRESERVE } ROWS ]
::=
[ {
}... ]
::= |
Syntax Rules 1) If a
is contained in a , and if the
contains a , then that shall be the same as the specified or implicit of the containing . 2) The schema identified by the explicit or implicit schema name of the
shall not include a table descriptor whose table name is
. 3) If ON COMMIT is specified, then TEMPORARY shall be specified. 4) If TEMPORARY is specified and ON COMMIT is not specified, then ON COMMIT DELETE ROWS is implicit. 5) A
shall contain at least one . 6) The scope of the
is the
. Access Rules 1) If a
is contained in a , then the current shall be equal to the that owns the schema identified by the implicit or explicit of the
. 260 Database Language SQL X3H2-92-154/DBL CBR-002 11.3
General Rules 1) A
defines either a persistent base table, a global temporary table or a created local temporary table. If GLOBAL is specified, then a global temporary table is defined. If LOCAL is specified, then a created local temporary table is defined. Otherwise, a persistent base table is defined. 2) The degree of the table being created is initially set to 0; the General Rules of Subclause 11.4, "" specify the degree of the table being created during the definition of columns in that table. 3) A table descriptor is created that describes the table being defined. a) The name included in the table descriptor is
. b) The table descriptor includes the degree of the table, which is the number of
s in the
that are s. 4) A set of privilege descriptors is created that define the priv- ileges INSERT, SELECT, UPDATE, DELETE, and REFERENCES on this table and INSERT, SELECT, UPDATE, and REFERENCES for every in the table definition to the of the or in which the
appears. These privileges are grantable. The grantor for each of these privilege descriptors is set to the special grantor value "_SYSTEM". Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall not specify TEMPORARY and shall not reference any global or local tem- porary table. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. Schema definition and manipulation 261 X3H2-92-154/DBL CBR-002 11.4 11.4 Function Define a column of a table. Format ::= { | } [ ] [ ... ] [ ] ::= [ ] [ ] ::= NOT NULL | | | Syntax Rules 1) Case: a) If the is contained in a
, then let T be the table defined by that
. b) If the is contained in a , then let T be the table declared by that . c) If the is contained in an , then let T be the table identified in the containing . The in the shall be different from the of any column of T. 2) The i-th column of the table is described by the i-th in the
. The name and the data type or domain of the column are specified by the and or , respectively. 3) Let C be the of a . 4) If is specified, then let D be the domain identi- fied by the . 262 Database Language SQL X3H2-92-154/DBL CBR-002 11.4 5) The data type of the column is Case: a) If is specified, then that data type. b) Otherwise, the data type of D. 6) If the data type of the column is character string, then the collation of the column is Case: a) If is specified, then the collation speci- fied by that . b) If is specified, then the collation of D, if any. c) Otherwise, the default collation of the character set of the column. Note: The character set of a column is determined by its data type. 7) If a is specified, then: a) Let DT be the . b) The data type of the column is DT. c) If DT is CHARACTER or CHARACTER VARYING and does not spec- ify a , then the specified or implicit in the of the that created the schema identified by the immedi- ately contained in the
of the containing
or is implicit. d) If DT is a that identifies a char- acter set that specifies a and the does not contain a , then the of the is implicit in the . 8) If is specified, then data type shall be a character string type. 9) If a is specified, then let CND be the if one is specified and let CND be a zero-length string otherwise; let CA be the if specified and let CA be a zero-length string otherwise. The is equivalent to a
as follows: Schema definition and manipulation 263 X3H2-92-154/DBL CBR-002 11.4 Case: a) If a is specified that con- tains the NOT NULL, then it is equivalent to a
that contains the follow- ing
: CND CHECK ( C IS NOT NULL ) CA b) If a is specified that con- tains a , then it is equivalent to a
that contains the following
: CND (C) CA Note: The is defined in Subclause 11.7, "". c) If a is specified that con- tains a , then it is equivalent to a
that contains the following
: CND FOREIGN KEY (C) CA Note: The is defined in Subclause 11.8, "". d) If a is specified that con- tains a , then it is equivalent to a
that contains the follow- ing
: CND CHECK ( ) CA Each directly contained in the shall reference column C. Access Rules 1) If is specified, then the applicable privileges shall include USAGE on D.. General Rules 1) A defines a column in a table. 2) The specifies the default collating sequence for the column. If is not specified, then the default collating sequence is that used for comparisons of Coercible coercibility attribute, as defined in Subclause 8.2, "". 3) If the specifies , then a data type descriptor is created that describes the data type of the column being defined. 264 Database Language SQL X3H2-92-154/DBL CBR-002 11.4 4) The degree of the table T being defined in the containing
or or altered by the containing is increased by 1. 5) A column descriptor is created that describes the column being defined. The name included in the column descriptor is . If the specifies , then the column descriptor includes the data type descriptor of the column; otherwise, the column descriptor includes the name of the domain of the column. The ordinal position included in the column descriptor is equal to the degree of T. If the contains a , then the of the is included in the column descriptor. The column descriptor includes the nullability char- acteristic of the column, determined according to the rules in Subclause 4.8, "Columns". The column descriptor is included in the table descriptor for T. Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall not contain any . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) A shall not contain a . b) A shall not contain a . c) Conforming Intermediate SQL language shall contain no . Schema definition and manipulation 265 X3H2-92-154/DBL CBR-002 11.5 11.5 Function Specify the default for a column or domain. Format ::= DEFAULT ::= | | USER | CURRENT_USER | SESSION_USER | SYSTEM_USER | NULL Syntax Rules 1) The subject data type of a is the data type specified in the descriptor identified by the containing , , , or . 2) If USER is specified, then CURRENT_USER is implicit. 3) Case: a) If a is specified, then: Case: i) If the subject data type is character string, then the shall be a . If the length of the subject data type is fixed, then the length in characters of the shall not be greater than the length of the subject data type. If the length of the subject data type is variable, then the length in characters of the shall not be greater than the maximum length of the subject data type. The shall have the same character repertoire as the subject data type. ii) If the subject data type is bit string, then the shall be a or a . If the length of the subject data type is fixed, then the length in bits of the or shall not be greater than the length of the sub- ject data type. If the length of the subject data type is 266 Database Language SQL X3H2-92-154/DBL CBR-002 11.5 variable, then the length in bits of the or shall not be greater than the maximum length of the subject data type. iii) If the subject data type is exact numeric, then the shall be a that simply contains an . There shall be a rep- resentation of the value of the in the subject data type that does not lose any significant digits. iv) If the subject data type is approximate numeric, then the shall be a . v) If the subject data type is datetime, then the shall be a and shall contain the same s as the subject data type. vi) If the subject data type is interval, then the shall be an and shall contain the same as the subject data type. b) If CURRENT_USER, SESSION_USER, or SYSTEM_USER is specified, then the subject data type shall be character string with character set SQL_TEXT. If the length of the subject data type is fixed, then its length shall not be less than 128 characters. If the length of the subject data type is vari- able, then its maximum length shall not be less than 128 characters. c) If is specified, then the subject data type shall be datetime with the same datetime type as the datetime data type of the . Access Rules None. General Rules 1) The default value inserted in the column descriptor, if the is to apply to a column, or in the domain de- scriptor, if the is to apply to a domain, is as follows: Case: a) If the contains NULL, then the null value. b) If the contains a , then Case: i) If the subject data type is numeric, then the numeric value of the . Schema definition and manipulation 267 X3H2-92-154/DBL CBR-002 11.5 ii) If the subject data type is character string with variable length, then the value of the . iii) If the subject data type is character string with fixed length, then the value of the , extended as neces- sary on the right with s to the length in characters of the subject data type. iv) If the subject data type is bit string with variable length, then the value of the . v) If the subject data type is bit string with fixed length, then the value of the extended as necessary on the right with 0-valued bits to the length of the subject data type and a completion condition is raised: warning- implicit zero-bit padding. vi) If the subject data type is datetime or interval, then the value of the . c) If the specifies CURRENT_USER, SESSION_USER, or SYSTEM_USER, then Case: i) If the subject data type is character string with variable length, then the value specified by CURRENT_USER, SESSION_ USER, or SYSTEM_USER. ii) If the subject data type is character string with fixed length, then the value specified by CURRENT_USER, SESSION_ USER, or SYSTEM_USER, extended as necessary on the right with s to the length in characters of the subject data type. d) If the contains a , then the value of an implicit reference to the . 2) The default value of a column is Case: a) If the column descriptor of a column includes a default value derived from a , then the value of that . b) If the column descriptor includes a domain name that iden- tifies a domain descriptor that includes a default value derived from a , then the value of that . c) Otherwise, the null value. 268 Database Language SQL X3H2-92-154/DBL CBR-002 11.5 Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) A shall not specify a , SYSTEM_USER, SESSION_USER, or CURRENT_USER. Schema definition and manipulation 269 X3H2-92-154/DBL CBR-002 11.6
11.6
Function Specify an integrity constraint. Format
::= [ ]
[ ]
::= | | Syntax Rules 1) If is not specified, then INITIALLY IMMEDIATE NOT DEFERRABLE is implicit. 2) If is not specified, then a that contains an implementation- dependent is implicit. The assigned shall obey the Syntax Rules of an explicit . Access Rules None. General Rules 1) A
defines a table constraint. 2) A table constraint descriptor is created that describes the table constraint being defined. The table constraint descriptor includes the contained in the explicit or implicit . The table constraint descriptor includes an indication of whether the constraint is deferrable or not deferrable and whether the initial constraint mode of the constraint is de- ferred or immediate. Case: a) If is specified, then the table constraint descriptor is a unique constraint descriptor that includes an indication of whether it was defined with PRIMARY KEY or UNIQUE, and the names of the unique columns specified in the . 270 Database Language SQL X3H2-92-154/DBL CBR-002 11.6
b) If is specified, then the table constraint descriptor is a referential constraint de- scriptor that includes the names of the referencing columns specified in the and the names of the referenced columns and referenced table specified in the , the value of the , if specified, and the , if specified. c) If is specified, then the table constraint descriptor is a table check constraint descriptor that includes the . 3) If the
is a , then let SC be the immediately contained in the and let T be the table name included in the corresponding table constraint descriptor; the table constraint is not satisfied if and only if EXISTS ( SELECT * FROM T WHERE NOT ( SC ) ) is true. Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Intermediate SQL language shall contain no . Schema definition and manipulation 271 X3H2-92-154/DBL CBR-002 11.7 11.7 Function Specify a uniqueness constraint for a table. Format ::= ::= UNIQUE | PRIMARY KEY ::= Syntax Rules 1) Let T be the table identified by the containing
or . Let TN be the
of T. 2) Let UCL be the of the . 3) Case: a) If the specifies PRIMARY KEY, then let SC be the : UNIQUE ( SELECT UCL FROM TN ) AND ( UCL ) IS NOT NULL b) Otherwise, let SC be the : UNIQUE ( SELECT UCL FROM TN ) 4) Each in the shall identify a column of T, and the same column shall not be identified more than once. 5) A
shall specify at most one implicit or ex- plicit that specifies PRIMARY KEY. 6) If a that specifies PRIMARY KEY is contained in an , then the table identified by the
immediately contained in the containing shall not have a unique constraint that was defined by a that specified PRIMARY KEY. 272 Database Language SQL X3H2-92-154/DBL CBR-002 11.7 7) The set of columns in the shall be dis- tinct from the unique columns of any other unique constraint descriptor that is included in the base table descriptor of T. Access Rules None. General Rules 1) A defines a unique constraint. Note: Subclause 10.6, " and ", specifies when a constraint is effectively checked. 2) The unique constraint is not satisfied if and only if EXISTS ( SELECT * FROM TN WHERE NOT ( SC ) ) is true. Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) If PRIMARY KEY or UNIQUE is specified, then the for each column whose is in the shall specify NOT NULL. Schema definition and manipulation 273 X3H2-92-154/DBL CBR-002 11.8 11.8 Function Specify a referential constraint. Format ::= FOREIGN KEY ::= REFERENCES [ MATCH ] [ ] ::= FULL | PARTIAL ::= ::=
[ ] ::= ::= [ ] | [ ] ::= ON UPDATE ::= ON DELETE ::= CASCADE | SET NULL | SET DEFAULT | NO ACTION Syntax Rules 1) Let referencing table be the table identified by the containing
or . Let referenced table be the table identified by the
in the . Let referencing columns be the column or columns identified by the in the 274 Database Language SQL X3H2-92-154/DBL CBR-002 11.8 and let referencing column be one such column. 2) Case: a) If the specifies a , then the set of column names of that shall be equal to the set of column names in the unique columns of a unique constraint of the refer- enced table. Let referenced columns be the column or columns identified by that and let refer- enced column be one such column. Each referenced column shall identify a column of the referenced table and the same column shall not be identified more than once. b) If the does not specify a , then the table descriptor of the referenced table shall include a unique constraint that spec- ifies PRIMARY KEY. Let referenced columns be the column or columns identified by the unique columns in that unique con- straint and let referenced column be one such column. The shall be considered to implic- itly specify a that is identical to that . 3) The table constraint descriptor describing the whose identifies the referenced columns shall indicate that the unique constraint is not deferrable. 4) The referenced table shall be a base table. Case: a) If the referencing table is a persistent base table, then the referenced table shall be a persistent base table. b) If the referencing table is a global temporary table, then the referenced table shall be a global temporary table. c) If the referencing table is a created local temporary table, then the referenced table shall be either a global temporary table or a created local temporary table. d) If the referencing table is a declared local temporary table, then the referenced table shall be either a global temporary table, a created local temporary table or a declared local temporary table. 5) If the referenced table is a temporary table with ON COMMIT DELETE ROWS specified, then the referencing table shall specify ON COMMIT DELETE ROWS. Schema definition and manipulation 275 X3H2-92-154/DBL CBR-002 11.8 6) Each referencing column shall identify a column of the referenc- ing table, and the same column shall not be identified more than once. 7) The shall contain the same number of s as the . The i-th col- umn identified in the corresponds to the i-th column identified in the . The data type of each referencing column shall be the same as the data type of the corresponding referenced column. 8) If a does not specify any , then an with a of NO ACTION is implicit. 9) If a does not specify any , then a with a of NO ACTION is implicit. Access Rules 1) The applicable privileges shall include REFERENCES for each referenced column. General Rules 1) A defines a referential constraint. Note: Subclause 10.6, " and ", specifies when a constraint is effectively checked. 2) Let Rf be the referencing columns and let Rt be the referenced columns in the referenced table T. The referencing table and the referenced table satisfy the referential constraint if and only if: Case: a) A is not specified and for each row of the ref- erencing table, the Rf MATCH (SELECT Rt FROM T) is true. b) PARTIAL is specified and for each row of the referencing table, the Rf MATCH PARTIAL (SELECT Rt FROM T) is true. c) FULL is specified and for each row of the referencing table, the Rf MATCH FULL (SELECT Rt FROM T) 276 Database Language SQL X3H2-92-154/DBL CBR-002 11.8 is true. 3) Case: a) If is not specified or if FULL is specified, then for a given row in the referenced table, let matching rows be all rows in the referencing table whose referenc- ing column values equal the corresponding referenced column values for the referential constraint. b) If PARTIAL is specified, then: i) For a given row in the referenced table, let matching rows be all rows in the referencing table that have at least one non-null referencing column value and whose non-null ref- erencing column values equal the corresponding referenced column values for the referential constraint. ii) For a given row in the referenced table, let unique match- ing rows be all matching rows for that given row that are matching rows only to the given row in the referenced table for the referential constraint. For a given row in the ref- erenced table, let non-unique matching rows be all matching rows for that given row that are not unique matching rows for that given row for the referential constraint. 4) For every row of the referenced table, its matching rows, unique matching rows, and non-unique matching rows are determined imme- diately before the execution of any SQL-statement. No new match- ing rows are added during the execution of that SQL-statement. The association between a referenced row and a non-unique match- ing row is dropped during the execution of that SQL-statement if the referenced row is either marked for deletion or updated to a distinct value on any referenced column that corresponds to a non-null referencing column. This occurs immediately after such a mark for deletion or update of the referenced row. Unique matching rows and non-unique matching rows for a referenced row are evaluated immediately after dropping the association between that referenced row and a non-unique matching row. 5) If a is specified and a row of the referenced table that has not previously been marked for deletion is marked for deletion, then Case: a) If is not specified or if FULL is specified, then Case: i) If the specifies CASCADE, then all matching rows are marked for deletion. Schema definition and manipulation 277 X3H2-92-154/DBL CBR-002 11.8 ii) If the specifies SET NULL, then in all match- ing rows each referencing column is set to the null value. iii) If the specifies SET DEFAULT, then in all matching rows each referencing column is set to the default value specified in the General Rules of Subclause 11.5, "". b) If PARTIAL is specified, then Case: i) If the specifies CASCADE, then all unique matching rows are marked for deletion. ii) If the specifies SET NULL, then in all unique matching rows each referencing column is set to the null value. iii) If the specifies SET DEFAULT, then in all unique matching rows each referencing column is set to the default value specified in the General Rules of Subclause 11.5, "". Note: Otherwise, the is not performed. 6) If an is specified and a non-null value of a ref- erenced column in the referenced table is updated to a value that is distinct from the current value of that column, then Case: a) If is not specified or if FULL is specified, then Case: i) If the specifies CASCADE, then in all match- ing rows the referencing column that corresponds with the referenced column is updated to the new value of the refer- enced column. ii) If the specifies SET NULL, then Case: 1) If is not specified, then in all matching rows the referencing column that corresponds with the referenced column is set to the null value. 2) If specifies FULL, then in all matching rows each referencing column is set to the null value. iii) If the specifies SET DEFAULT, then in all matching rows the referencing column that corresponds with the referenced column is set to the default value specified 278 Database Language SQL X3H2-92-154/DBL CBR-002 11.8 in the General Rules of Subclause 11.5, "". b) If PARTIAL is specified, then Case: i) If the specifies CASCADE, then for each unique matching row that contains a non-null value in the referencing column C1 that corresponds with the updated referenced column C2, C1 is updated to the new value V of C2, provided that, in all updated rows in the referenced table that formerly had, in the same SQL-statement, that unique matching row as a matching row, the values in C2 have all been updated to a value that is not distinct from V. Otherwise, an exception condition is raised: triggered data change violation. Note: Because of the Rules of Subclause 8.2, "", on which the definition of "distinct" re- lies, the values in C2 may have been updated to values that are not distinct, yet are not identical. Which of these non-distinct values is used for the cascade operation is implementation-dependent. ii) If the specifies SET NULL, then in all unique matching rows that contain a non-null value in the ref- erencing column that corresponds with the updated column, that referencing column is set to the null value. iii) If the specifies SET DEFAULT, then in all unique matching rows that contain a non-null value in the referencing column that corresponds with the updated col- umn, that referencing column is set to the default value specified in the General Rules of Subclause 11.5, "". Note: Otherwise, the is not performed. 7) If any attempt is made within an SQL-statement to update some data item to a value that is distinct from the value to which that data item was previously updated within the same SQL- statement, then an exception condition is raised: triggered data change violation. 8) If an attempts to update a row that has been deleted by any that identifies some cursor CR that is still open or updated by any that identifies some cursor CR that is still open or if a attempts to mark for deletion such a row, then a completion condition is raised: warning- cursor operation conflict. Schema definition and manipulation 279 X3H2-92-154/DBL CBR-002 11.8 9) All rows that are marked for deletion are effectively deleted at the end of the SQL-statement, prior to the checking of any integrity constraints. Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) A shall not specify MATCH. b) A shall not contain an . c) The order of the column names in a shall be the same as the order of column names of the corre- sponding unique constraint of the referenced table. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) A shall not contain a . 280 Database Language SQL X3H2-92-154/DBL CBR-002 11.9 11.9 Function Specify a condition for the SQL-data. Format ::= CHECK Syntax Rules 1) The shall not contain a or a . 2) The shall not contain a that is not contained in a . 3) If is contained in a
or , then let T be the table identified by the containing
or . Case: a) If T is a persistent base table, or if the is contained in a or , then no
generally con- tained in the shall reference a temporary table. b) If T is a global temporary table, then no
generally contained in the shall reference a table other than a global temporary table. c) If T is a created local temporary table, then no
generally contained in the shall reference a table other than either a global temporary table or a created local temporary table. d) If T is a declared local temporary table, then no
generally contained in the shall reference a persistent base table. 4) If the is contained in a
that defines a temporary table and specifies ON COMMIT PRESERVE ROWS or a that specifies ON COMMIT PRESERVE ROWS, then no in the shall reference a temporary table defined by a
or a that specifies ON COMMIT DELETE ROWS. Schema definition and manipulation 281 X3H2-92-154/DBL CBR-002 11.9 5) The shall not generally contain a or a that is CURRENT_USER, SESSION_USER, or SYSTEM_USER. 6) The shall not generally contain a or a that is possibly non- deterministic. Access Rules 1) Let TN be any
referenced in the . Case: a) If a is contained in the , then the applicable privileges shall include REFERENCES for each of the table identified by TN contained in the . b) Otherwise, the applicable privileges shall include REFERENCES for at least one column of the table identified by TN. General Rules 1) A defines a check constraint. Note: Subclause 10.6, " and ", specifies when a constraint is effectively checked. The General Rules that control the evaluation of a check constraint can be found in either Subclause 11.6, "
", or Subclause 11.21, "", depending on whether it forms part of a table constraint or a domain constraint. 2) If the character representation of the cannot be represented in the Information Schema without truncation, then a completion condition is raised: warning-search condition too long for information schema. Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) The contained in a shall not contain a . b) The REFERENCES privilege is not required for access. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. 282 Database Language SQL X3H2-92-154/DBL CBR-002 11.10 11.10 Function Change the definition of a table. Format ::= ALTER TABLE
::= | | | | Syntax Rules 1) Let T be the table identified by the
. 2) The schema identified by the explicit or implicit schema name of the
shall include the descriptor of T. 3) The scope of the
is the entire . 4) T shall be a base table. 5) T shall not be a declared local temporary table. Access Rules 1) The current shall be equal to the that owns the schema identified by the of the table identified by
. General Rules 1) The base table descriptor of T is modified as specified by . Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain an . Schema definition and manipulation 283 X3H2-92-154/DBL CBR-002 11.11 11.11 Function Add a column to a table. Format ::= ADD [ COLUMN ] Syntax Rules None. Access Rules None. General Rules 1) The column defined by the is added to T. 2) Let C be the column added to T. Every value in C is the default value for C. Note: The default value of a column is defined in Subclause 11.5, "". Note: The addition of a column to a table has no effect on any existing included in a view descriptor or included in constraint descriptor because any implicit s in these clauses are replaced by explicit s when the clause is originally evaluated. See the Syntax Rules of Subclause 7.10, "". 3) For every table privilege descriptor that specifies T and a privilege of SELECT, UPDATE, INSERT or REFERENCES, a new col- umn privilege descriptor is created that specifies T, the same action, grantor, and grantee, and the same grantability, and specifies the of the . 4) In all other respects, the specification of a in an has the same effect as specification of the in the
for T would have had. In particular, the degree of T is increased by 1 and the ordinal position of that column is equal to the new degree of T as specified in the General Rules of Subclause 11.4, "". 284 Database Language SQL X3H2-92-154/DBL CBR-002 11.11 Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain an . Schema definition and manipulation 285 X3H2-92-154/DBL CBR-002 11.12 11.12 Function Change a column and its definition. Format ::= ALTER [ COLUMN ] ::= | Syntax Rules 1) Let T be the table identified in the containing . 2) Let C be the column identified by the . 3) C shall be a column of T. Access Rules None. General Rules 1) The column descriptor of C is modified as specified by . Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain an . 286 Database Language SQL X3H2-92-154/DBL CBR-002 11.13 11.13 Function Set the default clause for a column. Format ::= SET Syntax Rules None. Access Rules None. General Rules 1) Let C be the column identified by the in the con- taining . 2) The default value specified by the is placed in the column descriptor of C. Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain a . Schema definition and manipulation 287 X3H2-92-154/DBL CBR-002 11.14 11.14 Function Drop the default clause from a column. Format ::= DROP DEFAULT Syntax Rules 1) Let C be the column identified by the in the con- taining . 2) The descriptor of C shall include a default value. Access Rules None. General Rules 1) The default value is removed from the column descriptor of C. Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain a . 288 Database Language SQL X3H2-92-154/DBL CBR-002 11.15 11.15 Function Destroy a column. Format ::= DROP [ COLUMN ] Syntax Rules 1) Let T be the table identified by the
in the con- taining and let TN be the name of T. 2) Let C be the column identified by the CN. 3) C shall be a column of T and C shall not be the only column of T. 4) If RESTRICT is specified, then C shall not be referenced in the of any view descriptor or in the of any constraint descriptor other than a table con- straint descriptor that contains references to no other column and that is included in the table descriptor of T. Note: A that does not specify CASCADE will fail if there are any references to that column resulting from the use of CORRESPONDING, NATURAL, SELECT * (except where contained in an exists predicate>), or REFERENCES without a in its . Note: If CASCADE is specified, then any such dependent object will be dropped by the execution of the spec- ified in the General Rules of this Subclause. Access Rules None. 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 INSERT(CN), UPDATE(CN), REFERENCES(CN) ON TABLE TN FROM A CASCADE Schema definition and manipulation 289 X3H2-92-154/DBL CBR-002 11.15 2) Let VN be the name of any view that contains a reference to column C of table T. The following is effectively executed with a current of "_SYSTEM" and without further Access Rule checking: DROP VIEW VN CASCADE 3) If the column is not based on a domain, then its data type de- scriptor is destroyed. 4) The data associated with C is destroyed and the descriptor of C is removed from the descriptor of T. 5) The identified column and its descriptor are destroyed. 6) The degree of T is reduced by 1. The ordinal position of all columns having an ordinal position greater than the ordinal position of C is reduced by 1. Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain a . 290 Database Language SQL X3H2-92-154/DBL CBR-002 11.16 11.16 Function Add a constraint to a table. Format ::= ADD
Syntax Rules None. Access Rules None. General Rules 1) Let T be the table identified by the
in the con- taining . 2) The table constraint descriptor for the
is included in the table descriptor for T. Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain an . Schema definition and manipulation 291 X3H2-92-154/DBL CBR-002 11.17 11.17 Function Destroy a constraint on a table. Format ::= DROP CONSTRAINT Syntax Rules 1) Let T be the table identified by the
in the con- taining . The schema identified by the explicit or implicit schema name of the
shall in- clude the descriptor of T. 2) The shall identify a table constraint TC of T. 3) If TC is a unique constraint and there exists a referential constraint RC whose referenced table is T and whose referenced columns are the unique columns of TC, then RC is said to be dependent on TC. 4) If RESTRICT is specified, then no table constraint shall be dependent on TC. Note: If CASCADE is specified, then any such dependent object will be dropped by the effective execution of the specified in the General Rules of this Subclause. Access Rules None. General Rules 1) Let TCN2 be the of any table constraint that is dependent on TC and let T2 be the
of the ta- ble descriptor that includes TCN2. The following is effectively executed without further Access Rule checking: ALTER TABLE T2 DROP CONSTRAINT TCN2 CASCADE 2) The descriptor of TC is removed from the descriptor of T. 3) The identified table constraint and its descriptor are de- stroyed. 292 Database Language SQL X3H2-92-154/DBL CBR-002 11.17 Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain a . Schema definition and manipulation 293 X3H2-92-154/DBL CBR-002 11.18 11.18 Function Destroy a table. Format ::= DROP TABLE
Syntax Rules 1) Let T be the table identified by the
and let TN be that
. The schema identified by the explicit or implicit schema name of TN shall include the descriptor of T. 2) T shall be a base table. 3) T shall not be a declared local temporary table. 4) If RESTRICT is specified, then T shall not be referenced in the of any view descriptor or the of any constraint descriptor. Note: If CASCADE is specified, then such referencing objects will be dropped by the execution of the spec- ified in the General Rules of this Subclause. Access Rules 1) The current shall be equal to the that owns the schema identified by the of the table identified by TN. 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 ALL PRIVILEGES ON TN FROM A CASCADE 2) The identified base table and its descriptor are destroyed. Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 294 Database Language SQL X3H2-92-154/DBL CBR-002 11.18 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain any . Schema definition and manipulation 295 X3H2-92-154/DBL CBR-002 11.19 11.19 Function Define a viewed table. Format ::= CREATE VIEW
[ ] AS [ WITH [ ] CHECK OPTION ] ::= CASCADED | LOCAL ::= Syntax Rules 1) The shall not contain a or a . 2) If a is contained in a and the
contains a , then that shall be the same as the specified or implicit of the containing . 3) The schema identified by the explicit or implicit schema name of the
shall not include a table descriptor whose table name is
. 4) The viewed table defined by shall not be identified by any
generally contained in the . 5) Any
that is specified in the shall be different from the
of any . 6) If the is updatable, then the viewed table is an updatable table. Otherwise, it is a read-only table. 7) If the is a that con- tains a or a that is not con- tained in a , then the viewed table defined by the is a grouped view. 8) If any two columns in the table specified by the have the same , or if any column of that table has an implementation-dependent name, then a shall be specified. 296 Database Language SQL X3H2-92-154/DBL CBR-002 11.19 9) The same shall not be specified more than once in the . 10)The number of s in the shall be the same as the degree of the table specified by the . 11)No column in the table specified by shall have a coercibility attribute of No collating sequence. Note: The coercibility attribute is described in Subclause 4.2.3, "Rules determining collating sequence usage". Note: The coercibility attribute for references to the column is defined in Subclause 6.4, "". 12)If WITH CHECK OPTION is specified, then the viewed table shall be updatable. 13)If WITH CHECK OPTION is specified with no , then a of CASCADED is implicit. 14)Let V be the view defined by the . The un- derlying columns of every i-th column of V are the underlying columns of the i-th column of the and the underlying columns of V are the underlying columns of the . Access Rules 1) If a is contained in a , then the current shall be equal to the that owns the schema identified by the implicit or explicit of the
. General Rules 1) A view descriptor VD is created that describes V. The view de- scriptor includes the
, the , column descriptors taken from the table specified by the , and an indication of whether WITH CHECK OPTION was specified. If a is specified, then the of the i-th column of the view is the i-th in that . Otherwise, the s of the view are the s of the table specified by the . 2) Let VN be the
. Let QE be the . If a is specified, then let VCL be the preceded by a and followed by a ; otherwise, let VCL be the empty string. Case: a) When VN is immediately contained in some SQL-schema state- ment, it identifies the view descriptor VD. Schema definition and manipulation 297 X3H2-92-154/DBL CBR-002 11.19 b) Otherwise, VN references the same table as the
: ( QE ) AS VN VCL 3) Let A be the that owns V. 4) A set of privilege descriptors is created that defines the priv- ilege SELECT on this table to A and SELECT for each column of V to A. This privilege is grantable if and only if the appli- cable SELECT privileges on all
s contained in the are grantable. The grantor of this privilege descriptor is set to the special grantor value "_SYSTEM". 5) If V is updatable, then let T be the leaf underlying table of the . 6) For i ranging from 1 to the number of distinct leaf underlying tables of the of V, let RTi be the
s of those tables. For every column CV of V: a) Let CRij, for j ranging from 1 to the number of columns of RTi that are underlying columns of CV, be the s of those columns. b) If A has REFERENCES(CRij) for all i and for all j, and A has REFERENCES on some column of RTi for all i, then a privilege descriptor is created that defines the privilege REFERENCES (CV) on V to A. That privilege is grantable if and only if the REFERENCES privileges on all of the columns CRTij are grantable. The grantor of that privilege descriptor is set to the special grantor value "_SYSTEM". 7) If V is updatable, then: a) A set of privilege descriptors is created that defines the privileges INSERT, UPDATE, and DELETE on V that are appli- cable privileges on T to A. A privilege on V is grantable if and only if the corresponding privilege on T is grantable. b) For every column in V: i) There is a corresponding column in T from which the column of V is derived. Let CV and CT be the s of the corresponding columns of V and T respectively. ii) A set of privilege descriptors is created that defines the privileges INSERT(CV) and UPDATE(CV) on V, where the privileges INSERT(CT) and UPDATE(CT) on T are the appli- cable privileges to A, respectively. A privilege on V is grantable if and only if the corresponding privilege on T is grantable. The grantor of these privilege descriptors is set to the special grantor value "_SYSTEM". 298 Database Language SQL X3H2-92-154/DBL CBR-002 11.19 8) If V is updatable, then let TLEAF be the leaf generally under- lying table of V. For every row in V there is a corresponding row in TLEAF from which the row of V is derived and for each column in V there is a corresponding column in TLEAF from which the column of V is derived. The insertion of a row into V is an insertion of a corresponding row into TLEAF. The deletion of a row from V is a deletion of the corresponding row in TLEAF. The updating of a column of a row in V is an updating of the corresponding column of the corresponding row in TLEAF. 9) Let V1 be a view. V1 spans V1. V1 spans a view V2 if V2 is a generally underlying table of V1. 10)An update operation is an , , , , or . An update operation on a view V is an update operation whose
identifies V. 11)If a view V1 spans a view VA described by a view descriptor that includes WITH CHECK OPTION and an update operation on V1 would result in a row that would not appear in the result of VA, then a) If the view descriptor of VA includes CASCADED, then an ex- ception condition is raised: with check option violation. b) If the view descriptor of VA includes LOCAL and the update operation would result in a row that would appear in the simply underlying table of the simply underlying table of the contained in VA, then an exception condition is raised: with check option violation. 12)Validation of a WITH CHECK OPTION constraint is effectively performed at the end of each update operation. 13)If the character representation of the cannot be represented in the Information Schema without truncation, then a completion condition is raised: warning-query expression too long for information schema. Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall not contain any , but the effect shall be that defined for a of CASCADED. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) The in a shall be a . Schema definition and manipulation 299 X3H2-92-154/DBL CBR-002 11.20 11.20 Function Destroy a view. Format ::= DROP VIEW
Syntax Rules 1) Let V be the table identified by the
and let VN be that
. The schema identified by the explicit or implicit schema name of VN shall include the descriptor of V. 2) V shall be a viewed table. 3) If RESTRICT is specified, then V shall not be referenced in the of any view descriptor or the of any assertion descriptor or constraint descriptor. Note: If CASCADE is specified, then any such dependent object will be dropped by the execution of the spec- ified in the General Rules of this Subclause. Access Rules 1) The current shall be equal to the that owns the schema identified by the of the table identified by VN. 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 ALL PRIVILEGES ON VN FROM A CASCADE 2) The identified view and its descriptor are destroyed. Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain a . 300 Database Language SQL X3H2-92-154/DBL CBR-002 11.21 11.21 Function Define a domain. Format ::= CREATE DOMAIN [ AS ] [ ] [ ... ] [ ] ::= [ ] [ ] Syntax Rules 1) If a is contained in a , and if the contains a , then that shall be the same as the specified or implicit of the containing . The schema identified by the explicit or implicit schema name of the shall not include a domain descriptor whose domain name is . 2) If specifies CHARACTER or CHARACTER VARYING and does not specify , then the character set name of the default character set of the schema identified by the implicit or explicit of is implicit. 3) If specifies a that identi- fies a character set that has a default collation and the does not directly contain a , then the collation of the is the im- plicit collation of the domain. 4) Let D1 be some domain. D1 is in usage by a domain constraint DC if and only if the of DC generally contains the either of D1 or of some domain D2 such that D1 is in usage by some domain constraint of D2. No domain shall be in usage by any of its own constraints. 5) If is specified, then shall be a character string type. 6) for every is specified: a) If is not specified, then INITIALLY IMMEDIATE NOT DEFERRABLE is implicit. Schema definition and manipulation 301 X3H2-92-154/DBL CBR-002 11.21 b) If is not specified, then a that contains an implementation- dependent is implicit. The assigned shall obey the Syntax Rules of an explicit . Access Rules 1) If a is contained in a , then the current shall be equal to the that owns the schema identified by the implicit or explicit of the . General Rules 1) A defines a domain. Note: Subclause 10.6, " and ", specifies when a constraint is effectively checked. 2) A data type descriptor is created that describes the data type of the domain being created. 3) A domain descriptor is created that describes the domain being created. The domain descriptor contains the name of the domain, the data type descriptor of the data type, the of the if the contains a , the value of the if the immediately contains , and a domain constraint descriptor for every immediately contained . 4) A privilege descriptor is created that defines the USAGE priv- ilege on this domain to the of the or in which the appears. This privilege is grantable if and only if the applicable privi- leges include a grantable REFERENCES privilege for each included in the domain descriptor and a grantable USAGE privilege for each , , , and contained in the of any domain constraint descriptor included in the domain descriptor, and a grantable USAGE privilege for the contained in the included in the domain descriptor. The grantor of the privilege descriptor is set to the special grantor value "_SYSTEM". 5) Let DSC be the included in some domain con- straint descriptor DCD. Let D be the name of the domain whose descriptor includes DCD. Let T be the name of some table whose descriptor includes some column descriptor with column name C whose domain name is D. Let CSC be a copy of DSC in which every instance of the VALUE is replaced by C. 302 Database Language SQL X3H2-92-154/DBL CBR-002 11.21 6) The domain constraint specified by DCD for C is not satisfied if and only if EXISTS ( SELECT * FROM T WHERE NOT ( CSC ) ) is true. Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall not contain any . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain any . Schema definition and manipulation 303 X3H2-92-154/DBL CBR-002 11.22 11.22 Function Change a domain and its definition. Format ::= ALTER DOMAIN ::= | | | Syntax Rules 1) Let D be the domain identified by . The schema identified by the explicit or implicit schema name of the shall include the descriptor of D. Access Rules 1) The current shall be equal to the that owns the schema identified by the implicit or explicit of . General Rules 1) The domain descriptor of D is modified as specified by . Note: The changed domain descriptor of D is applicable to every column that is dependent on D. Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall contain no . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. 304 Database Language SQL X3H2-92-154/DBL CBR-002 11.23 11.23 Function Set the default value in a domain. Format ::= SET Syntax Rules None. Access Rules None. General Rules 1) Let D be the domain identified by the in the con- taining . 2) The default value specified by the is placed in the domain descriptor of D. Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall contain no . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. Schema definition and manipulation 305 X3H2-92-154/DBL CBR-002 11.24 11.24 Function Remove the default clause of a domain. Format ::= DROP DEFAULT Syntax Rules 1) Let D be the domain identified by the in the con- taining . 2) The descriptor of D shall contain a default value. Access Rules None. General Rules 1) Let C be the set of columns whose column descriptors contain the domain descriptor of D. 2) For every column belonging to C, if the column descriptor does not already contain a default value, then the default value from the domain descriptor of D is placed in that column descriptor. 3) The default value is removed from the domain descriptor of D. Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall contain no . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. 306 Database Language SQL X3H2-92-154/DBL CBR-002 11.25 11.25 Function Add a constraint to a domain. Format ::= ADD Syntax Rules 1) Let D be the domain identified by the in the con- taining . 2) Let D1 be some domain. D1 is in usage by a domain constraint DC if and only if the of DC generally contains the either of D1 or of some domain D2 such that D1 is in usage by some domain constraint of D2. No domain shall be in usage by any of its own constraints. Access Rules None. General Rules 1) The constraint descriptor of the is added to the domain descriptor of D. Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall contain no . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. Schema definition and manipulation 307 X3H2-92-154/DBL CBR-002 11.26 11.26 Function Destroy a constraint on a domain. Format ::= DROP CONSTRAINT Syntax Rules 1) Let D be the domain identified by the in the con- taining . 2) Let DC be the descriptor of the constraint identified by . 3) DC shall be included in the domain descriptor of D. Access Rules None. General Rules 1) The constraint descriptor of DC is removed from the domain de- scriptor of D. 2) The constraint DC and its descriptor are destroyed. Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall contain no . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. 308 Database Language SQL X3H2-92-154/DBL CBR-002 11.27 11.27 Function Destroy a domain. Format ::= DROP DOMAIN Syntax Rules 1) Let D be the domain identified by and let DN be that . The schema identified by the explicit or implicit schema name of DN shall include the descriptor of D. 2) If RESTRICT is specified, then D shall not be referenced by any column descriptor, in the of any view descriptor, or in the of any constraint de- scriptor. Access Rules 1) The current shall be equal to the that owns the schema identified by the of the domain identified by DN. Let UA be the of the current SQL-session. General Rules 1) Let C be any column descriptor that includes DN, let T be the table described by the table descriptor that includes C, and let TN be the column name of T. C is modified as follows: a) DN is removed from C. A copy of the data type descriptor of D is included in C. b) If C does not include a and the domain de- scriptor of D includes a , then a copy of the of D is included in C. c) For every domain constraint descriptor included in the domain descriptor of D: i) Let TCD be a
consisting of a whose is implementation-dependent, whose
is derived from the of the do- main constraint descriptor by replacing every instance of VALUE by the of C, and whose are the of the domain constraint descriptor. Schema definition and manipulation 309 X3H2-92-154/DBL CBR-002 11.27 ii) If the applicable privileges of UA include all of the priv- ileges necessary for UA to successfully execute the ALTER TABLE TN ADD TCD then the following
is effec- tively executed with a current of UA: ALTER TABLE TN ADD TCD d) If C does not include a collation and the of D includes a collation, then i) Let CCN be the of the collation. ii) If the applicable privileges for UA contain USAGE on CCN, then CCN is added to C as the . 2) Let A be the current . The following is effectively executed with a current of "_SYSTEM" and without further Access Rule checking: REVOKE USAGE ON DOMAIN DN FROM A CASCADE 3) The identified domain is destroyed by destroying its descriptor and its data type descriptor. Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain a . 310 Database Language SQL X3H2-92-154/DBL CBR-002 11.28 11.28 Function Define a character set. Format ::= CREATE CHARACTER SET [ AS ] [ | ] ::= GET ::= | | ::= ::= COLLATION FROM Syntax Rules 1) If a is contained in a and if the immediately contained in the contains a , then that shall be the same as the specified or implicit of the . 2) The schema identified by the explicit or implicit schema name of the shall not include a character set descriptor whose character set name is . 3) A shall identify some character set descriptor. 4) If neither nor is specified, then the following is implicit: COLLATION FROM DEFAULT Schema definition and manipulation 311 X3H2-92-154/DBL CBR-002 11.28 Access Rules 1) If a is contained in a , then the current shall be equal to the that owns the schema identified by the implicit or explicit of the . 2) The applicable privileges for the shall include USAGE. General Rules 1) A defines a character set. 2) A character set descriptor is created for the defined character set. 3) The character set has the same character repertoire as the char- acter set identified by the . 4) A privilege descriptor is created that defines the USAGE privi- lege on this character set to the of the schema or in which the appears. The grantor of the privilege descriptor is set to the special grantor value "_SYSTEM". This privilege is grantable. Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) In conforming Intermediate SQL language, shall specify DEFAULT. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not specify a . 312 Database Language SQL X3H2-92-154/DBL CBR-002 11.29 11.29 Function Destroy a character set. Format ::= DROP CHARACTER SET Syntax Rules 1) Let C be the character set identified by the and let CN be the name of C. 2) The schema identified by the explicit or implicit schema name of CN shall include the descriptor of C. 3) C shall not be referenced in the of any view descriptor or in the of any constraint de- scriptor, or be included in any collation descriptor or transla- tion descriptor. Access Rules 1) The current shall be equal to the that owns the schema identified by the of the character set identified by C. 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 USAGE ON CHARACTER SET CN FROM A CASCADE 2) The descriptor of C is destroyed. Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall contain no . Schema definition and manipulation 313 X3H2-92-154/DBL CBR-002 11.30 11.30 Function Define a collating sequence. Format ::= CREATE COLLATION FOR FROM [ ] ::= NO PAD | PAD SPACE ::= | ::= | | DESC | DEFAULT ::= TRANSLATION [ THEN COLLATION ] ::= EXTERNAL ::= ::= | ::= ::= 314 Database Language SQL X3H2-92-154/DBL CBR-002 11.30 Syntax Rules 1) If a is contained in a and if the immediately contained in the contains a , then that shall be the same as the specified or implicit of the . 2) The schema identified by the explicit or implicit schema name of the shall not include a collation descriptor whose collation name is . 3) A shall be the name of a colla- tion defined by a national or international standard. An shall be the name of a collation that is implementation-defined. 4) The s and s that are supported are implementation-defined. Each collation identified by a or by a shall have associated with it a privilege descriptor that was effectively defined by the GRANT USAGE ON COLLATION COLL TO PUBLIC where COLL is the or . 5) A collating sequence specified by or shall be a collating sequence that is defined for the character repertoire of the character set with which the is associated. 6) A shall be the name of a collating se- quence that is defined in the schema identified by the explicit or implicit . 7) If a does not specify , then Case: a) If a is specified that con- tains a that identifies a collation for which the specifies NO PAD, then NO PAD is implicit. b) Otherwise, PAD SPACE is implicit. 8) If NO PAD is specified, then the collation is said to have the NO PAD attribute. If PAD SPACE is specified, then the collation is said to have the PAD SPACE attribute. Schema definition and manipulation 315 X3H2-92-154/DBL CBR-002 11.30 9) If is specified, then let T be the translation named by . Let C1 be the colla- tion being defined by the . The source character set of T shall be the same as the character set of C1. 10)If THEN COLLATION is specified, then let C2 be the collation named by in THEN COLLATION . The target character set of T shall be identical to the character set of C2. Access Rules 1) If a is contained in a , then the current shall be equal to the that owns the schema identified by the implicit or explicit of the . 2) Let C be a collation identified by any con- tained in . The applicable privileges shall include USAGE on C. 3) If is specified, then the applicable privi- leges shall include USAGE. General Rules 1) A defines a collating sequence. 2) DEFAULT specifies that the collation is to be performed us- ing the order of characters as they appear in the character repertoire. 3) If DESC is specified, then the collation is the reverse of that specified by . 4) A privilege descriptor is created that defines the USAGE priv- ilege on this collation to the current . The grantor of the privilege descriptor is set to the special grantor value "_SYSTEM". 5) This privilege descriptor is grantable if and only if the USAGE privilege for the current on the contained in the is also grantable and if the USAGE privilege for the current on the contained in the , if present, is also grantable. 6) If is specified, then Case: a) If THEN COLLATION is specified, then let C2 be the collating sequence named by the in THEN COLLATION . The collating sequence defined is obtained by effectively translating a character 316 Database Language SQL X3H2-92-154/DBL CBR-002 11.30 string using T, then applying the collating sequence of C2 to the result. b) Otherwise, the collating sequence defined is obtained by ef- fectively translating a character string using T, then apply- ing the default collating sequence for the target character set of T. 7) If is specified, then the collating se- quence defined is that given by: a) If is specified, then the national or international standard collation. b) Otherwise, the implementation-defined collation. 8) A collation descriptor is created for the defined collation. Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall not contain any . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. Schema definition and manipulation 317 X3H2-92-154/DBL CBR-002 11.31 11.31 Function Destroy a collating sequence. Format ::= DROP COLLATION Syntax Rules 1) Let C be the collating sequence identified by the and let CN be the name of C. 2) The schema identified by the explicit or implicit schema name of CN shall include the descriptor of C. Access Rules 1) The current shall be equal to the that owns the schema identified by the of the collating sequence identified by C. 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 USAGE ON COLLATION CN FROM A CASCADE 2) Let CD be any collation descriptor that includes CN. CD is modi- fied by deleting any occurrences of "THEN COLLATION CN" or "DESC (CN)" 3) Let CSD be any character set descriptor that includes CN. CSD is modified by deleting any occurrences of "COLLATION FROM CN" or "DESC (CN)". 4) Let DD be any column descriptor or domain descriptor that includes CN. DD is modified by deleting any occurrences of "COLLATE CN". 5) Let VD be any view descriptor whose includes "COLLATE CN" or any constraint descriptor whose includes "COLLATE CN". VD is modified by deleting any occurrences of "COLLATE CN". 6) The descriptor of C is destroyed. 318 Database Language SQL X3H2-92-154/DBL CBR-002 11.31 Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall not contain any . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. Schema definition and manipulation 319 X3H2-92-154/DBL CBR-002 11.32 11.32 Function Define a character translation. Format ::= CREATE TRANSLATION FOR TO FROM ::= ::= ::= ::= | IDENTITY | ::= EXTERNAL ::= | ::= ::= ::= Syntax Rules 1) If a is contained in a and if the immediately contained in the contains a , then that shall be the same as the specified or implicit of the . 320 Database Language SQL X3H2-92-154/DBL CBR-002 11.32 2) The schema identified by the explicit or implicit schema name of the shall not include a translation descrip- tor whose translation name is . 3) A shall be the name of a trans- lation defined by a national or international standard. An shall be the name of a translation that is implementation-defined. 4) The s and s that are supported are implementation- defined. Each translation identified by a or by a shall have associated with it a privilege descriptor that was effectively defined by the GRANT USAGE ON TRANSLATION TRANS TO PUBLIC where TRANS is the or . 5) A shall identify a translation de- scriptor. Access Rules 1) If a is contained in a , then the current shall be equal to the that owns the schema identified by the implicit or explicit of the . 2) If is specified, then the applicable privileges shall include USAGE. General Rules 1) A defines a translation. 2) IDENTITY specifies a translation that makes no changes to the characters. 3) A translation descriptor is created for the defined translation. 4) A privilege descriptor PD is created that defines the USAGE privilege on this translation to the of the schema or in which the appears. The grantor of the privilege descriptor is set to the special grantor value "_SYSTEM". 5) PD is grantable if and only if the USAGE privilege for the of the schema or in which the appears is also grantable on every contained in the . Schema definition and manipulation 321 X3H2-92-154/DBL CBR-002 11.32 Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall contain no . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. 322 Database Language SQL X3H2-92-154/DBL CBR-002 11.33 11.33 Function Destroy a character translation. Format ::= DROP TRANSLATION Syntax Rules 1) Let T be the translation identified by the and let TN be the name of T. 2) The schema identified by the explicit or implicit schema name of TN shall include the descriptor of T. 3) T shall not be referenced in the included in any view descriptor or in the included in any constraint descriptor or be included in any collation descriptor. Access Rules 1) The current shall be equal to the that owns the schema identified by the of the translation identified by T. General Rules 1) Let CD be any collation descriptor that includes a TRANSLATION TN. CD is modified by deleting that . 2) Let CSD be any that references T. CSD is modified by deleting any occurrences of a that contains TN. 3) Let A be the current . The following is effectively executed with a current of "_SYSTEM" and without further Access Rule checking: REVOKE USAGE ON TRANSLATION TN FROM A CASCADE 4) The descriptor of T is destroyed. Schema definition and manipulation 323 X3H2-92-154/DBL CBR-002 11.33 Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall contain no . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. 324 Database Language SQL X3H2-92-154/DBL CBR-002 11.34 11.34 Function Specify an integrity constraint by means of an assertion and spec- ify the initial default time for checking the assertion. Format ::= CREATE ASSERTION [ ] ::= CHECK Syntax Rules 1) If an is contained in a and if the contains a , then that shall be the same as the explicit or implicit of the containing . 2) The schema identified by the explicit or implicit schema name of the shall not include a constraint descriptor whose constraint name is . 3) If is not specified, then INITIALLY IMMEDIATE NOT DEFERRABLE is implicit. 4) The shall not contain a or a . 5) No in the shall reference a temporary table. 6) The shall not generally contain a or a that is CURRENT_USER, SESSION_USER, or SYSTEM_USER. 7) The of shall be differ- ent from the of the of any other constraint defined in the same schema. 8) The shall not generally contain a or a that is possibly non- deterministic. Schema definition and manipulation 325 X3H2-92-154/DBL CBR-002 11.34 Access Rules 1) If an is contained in a , then the current shall be equal to the that owns the schema identified by the implicit or explicit of the of the . 2) Let TN be any
referenced in the of the . If TN identifies a table described by a base table descriptor or a view descriptor, then Case: a) If a is contained in the , then the applicable privileges shall include REFERENCES for each CN of the table identified by TN, where CN is contained in the . b) Otherwise, the applicable privileges shall include REFERENCES for at least one column of the table identified by TN. General Rules 1) An defines an assertion. Note: Subclause 10.6, " and ", specifies when a constraint is effectively checked. 2) The assertion is not satisfied if and only if the result of evaluating the is false. 3) An assertion descriptor is created that describes the assertion being defined. The name included in the assertion descriptor is . The assertion descriptor includes an indication of whether the constraint is deferrable or not deferrable and whether the ini- tial constraint mode is deferred or immediate. The assertion descriptor includes the of the . 4) If the character representation of the cannot be represented in the Information Schema without truncation, then a completion condition is raised: warning-search condition too long for information schema. Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall not contain any . 326 Database Language SQL X3H2-92-154/DBL CBR-002 11.34 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. Schema definition and manipulation 327 X3H2-92-154/DBL CBR-002 11.35 11.35 Function Destroy an assertion. Format ::= DROP ASSERTION Syntax Rules 1) Let A be the assertion identified by and let AN be the name of A. 2) The schema identified by the explicit or implicit schema name of AN shall include the descriptor of A. Access Rules 1) The current shall be equal to the that owns the schema identified by the of the assertion identified by AN. General Rules 1) The descriptor of A is destroyed. Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall not contain any . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. 328 Database Language SQL X3H2-92-154/DBL CBR-002 11.36 11.36 Function Define privileges. Format ::= GRANT ON TO [ { }... ] [ WITH GRANT OPTION ] ::= [ TABLE ]
| DOMAIN | COLLATION | CHARACTER SET | TRANSLATION Syntax Rules 1) If specifies a , , , or , then shall specify USAGE; otherwise, USAGE shall not be specified. 2) Let O be the object identified by the . 3) Let A be the current . For each specified, a set of privilege descriptors is iden- tified. The privilege descriptors identified are those defining, for each explicitly or implicitly in , that on O held by A with grant option. Access Rules 1) The applicable privileges shall include a privilege identifying O. General Rules 1) The specify one or more privileges on the object identified by the . 2) For every identified privilege descriptor, a privilege descrip- tor is created that specifies the identical , , object O, and grantor A. Let CPD be the set of privilege de- scriptors created. 3) For every identified privilege descriptor whose action is SELECT, INSERT, UPDATE, or REFERENCES without a column name, privilege descriptors are also created for each column C in O Schema definition and manipulation 329 X3H2-92-154/DBL CBR-002 11.36 for which A holds the corresponding privilege with grant op- tion. For each such column, a privilege descriptor is created that specifies the identical , the identical , object C, and grantor A. 4) If WITH GRANT OPTION was specified, each privilege descriptor also indicates that the privilege is grantable. 5) If
is specified, then let T be the table identified by the
. 6) For every updatable view V owned by some grantee G such that T is some leaf underlying table of the of V: a) Let VN be the
of V. b) If WITH GRANT OPTION is specified, then let WGO be "WITH GRANT OPTION"; otherwise, let WGO be a zero-length string. c) For every privilege descriptor PD in CPD, let PA be the ac- tion included in PD. i) If PA is INSERT, UPDATE, or DELETE, then the following is effectively executed as though the current were "_SYSTEM" and with- out further Access Rule checking: GRANT PA ON VN TO G WGO ii) If PA is A(CT), where A is INSERT or UPDATE and CT is the name of some column of T such that there is a correspond- ing column in V, named CVN, that is derived from CT, then the following is effectively executed as though the current were "_ SYSTEM" and without further Access Rule checking: GRANT A(CVN) ON VN TO G WGO 7) For every G and for every view V1 owned by G, if G has been granted SELECT privilege WITH GRANT OPTION on all tables identified by a
contained in the of V1, then for every privilege descriptor with a P that contains SELECT, a of "_SYSTEM", of V1, and G that is not grantable, the following is effectively executed with a current of "_SYSTEM" and without further Access Rule checking: GRANT P ON V1 TO G WITH GRANT OPTION 8) For every G and for every domain D1 owned by G, if G has been granted REFERENCES privilege WITH GRANT OPTION on every column referenced in the included in a domain constraint descriptor included in the domain descriptor of D1 and a grantable USAGE privilege on all domains, character sets, collations, and translations whose s, set name>s, s, and s, respec- tively, are included in the domain descriptor, and a grantable USAGE privilege for the contained in the included in the domain descriptor, then for every privilege descriptor with USAGE, a of "_ SYSTEM", D1, and G that is not grantable, the following is effectively executed with a cur- rent of "_SYSTEM" and without further Access Rule checking: GRANT USAGE ON DOMAIN D1 TO G WITH GRANT OPTION 9) For every G and for every collation C1 owned by G, if the USAGE privilege of G for the character set identified by a contained in the of C1 is grantable, then for every privilege descriptor with a P, a of "_SYSTEM", of C1, and G that is not grantable, the following is effectively executed with a current of "_SYSTEM" and without further Access Rule checking: GRANT P ON COLLATION C1 TO G WITH GRANT OPTION 10)For every G and for every translation T1 owned by G, if the USAGE privilege of G for every character set identified by a contained in the of T1 is grantable, then for every privilege descriptor with a P, a of "_SYSTEM", of T1, and G that is not grantable, the fol- lowing is effectively executed as though the current were "_SYSTEM" and without further Access Rule checking: GRANT P ON TRANSLATION T1 TO G WITH GRANT OPTION 11)If
is specified, then for each view V owned by some G such that T or some column CT of T, let RTi, for i ranging from 1 to the number of tables identified by the
s contained in the of V, be the
s of those tables. For every column CV of V: a) Let CRij, for j ranging from 1 to the number of columns of RTi that are underlying columns of CV, be the s of those columns. b) If WITH GRANT OPTION was specified, then let WGO be "WITH GRANT OPTION"; otherwise, let WGO be a zero-length string. c) If, following successful execution of the , G will have REFERENCES(CRTij) for all i and for all j, and A has REFERENCES on some column of RTi for all i, the the following is effectively executed as though Schema definition and manipulation 331 X3H2-92-154/DBL CBR-002 11.36 the current were "_SYSTEM" and without further Access Rule checking: GRANT REFERENCES CV ON V TO G WGO 12)If two privilege descriptors are identical except that one in- dicates that the privilege is grantable and the other indicates that the privilege is not grantable, then both privilege de- scriptors are set to indicate that the privilege is grantable. 13)Redundant duplicate privilege descriptors are removed from the multiset of all privilege descriptors. 14)For every combination of and on O specified in , if there is no corresponding privilege de- scriptor in the set of identified privilege descriptors, then a completion condition is raised: warning-privilege not granted. 15)If ALL PRIVILEGES was specified, then for each grantee, if no privilege descriptors were identified, then a completion condi- tion is raised: warning-privilege not granted. Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) In Conforming Intermediate SQL language, an shall not specify COLLATION or TRANSLATION. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) In Conforming Entry SQL language, an shall not specify TABLE. b) In Conforming Entry SQL language, an shall not specify CHARACTER SET or DOMAIN. 332 Database Language SQL X3H2-92-154/DBL CBR-002 11.37 11.37 Function Destroy privileges. Format ::= REVOKE [ GRANT OPTION FOR ] ON FROM [ { }... ] Syntax Rules 1) If specifies a , , , or , then shall specify USAGE; otherwise, USAGE shall not be specified. 2) INSERT is equivalent to specifying both the INSERT table priv- ilege and INSERT () for all columns of
. 3) UPDATE is equivalent to specifying both the UPDATE table priv- ilege and UPDATE () for all columns of
. 4) REFERENCES is equivalent to specifying both the REFERENCES ta- ble privilege and REFERENCES () for all columns of
. 5) Let O be the object identified by the . 6) Let A be the current . For every specified, a set of privilege descriptors is iden- tified. A privilege descriptor is said to be identified if it belongs to the set of privilege descriptors that define, for any explicitly or implicitly in , that on O granted by A to . Note: Column privilege descriptors become identified when explicitly or implicitly contains a . 7) A privilege descriptor D is allowed to be created by a grant permitted by P if either: a) The following conditions hold: i) P indicates that the privilege that it represents is grantable, and Schema definition and manipulation 333 X3H2-92-154/DBL CBR-002 11.37 ii) The grantee of P is the same as the grantor of D or the grantee of P is PUBLIC, and iii) Case: 1) P and D are both column privilege descriptors. The ac- tion and the identified column of P are the same as the action and identified column of D, respectively. 2) P is a table privilege descriptor and D is a column privilege descriptor. The identified table of P is the same as the identified table of D and the action of P is the same as the action of D and the action of P is SELECT. 3) Neither P nor D are column privilege descriptors. The action and the identified table, domain, character set, collation, or translation of P are the same as the ac- tion and the identified table, domain, character set, collation, or translation of D, respectively. b) The following conditions hold: i) The privilege descriptor for D indicates that its grantor is the special grantor value "_SYSTEM", and ii) The action of P is the same as the action of D, and iii) The grantee of P is the owner of the table, collation, or translation identified by D, or the grantee of P is PUBLIC, and iv) One of the following conditions hold: 1) P and D are both table privilege descriptors, the priv- ilege descriptor for D identifies the
of a V and either: A) The action of P is SELECT and the identified table of P is contained in the of V, or B) V is an updatable view and the identified table of P is the underlying table of the . 2) P and D are both column privilege descriptors, the priv- ilege descriptor D identifies a CVN ex- plicitly or implicitly contained in the of a V and V is an updatable view. For every column CV identified by a CVN, there is a corresponding column in the underly- ing table of the TN. Let CTN be the of the column of the from which CV is derived. The action for P is UPDATE or INSERT and the identified column of P is TN.CTN. 334 Database Language SQL X3H2-92-154/DBL CBR-002 11.37 3) P is a table privilege descriptor and the column privi- lege descriptor D identifies a CV explic- itly or implicitly contained in the of a V. Let TN be a
con- tained in the of the view. The action for P is SELECT and the identified table of P is TN. 4) The privilege descriptor D identifies the of a CO and the identified character set name of P is contained in the immediately contained in CO. 5) The privilege descriptor D identifies the of a TD and the identi- fied character set name of P is contained in the or the immediately contained in TD. 8) A privilege descriptor D is said to be directly dependent on an- other privilege descriptor P if D represents a privilege allowed to be created by a grant permitted by P. 9) The privilege dependency graph is a directed graph such that: a) Each node represents a privilege descriptor, and b) Each arc from node P1 to node P2 represents the fact that P2 directly depends on P1. An independent node is one that has no incoming arcs. 10)A privilege descriptor P is said to be modified if either P is a SELECT column privilege descriptor and a SELECT table privilege descriptor with the same grantee, grantor, catalog name, schema name, and table name is a modified privilege descriptor, or: a) P indicates that the privilege that it represents is grantable, and b) P directly depends on an identified privilege descriptor or a modified privilege descriptor, and c) Let XO and XA respectively be the identifier of the object identified by a privilege descriptor X and the action of X. Within the set of privilege descriptors upon which P directly depends, there exists some XO and XA for which the set of identified privilege descriptors unioned with the set of mod- ified privilege descriptors include all privilege descriptors specifying the grant of XA on XO with grant option, and d) At least one of the following is true: i) GRANT OPTION FOR is specified and the grantor of P is the special grantor value "_SYSTEM". Schema definition and manipulation 335 X3H2-92-154/DBL CBR-002 11.37 ii) There exists a path to P from an independent node that includes no identified or modified privilege descriptors. P is said to be a marked modified privilege descriptor. iii) P directly depends on a marked modified privilege descrip- tor, and the grantor of P is the special grantor value "_SYSTEM". P is said to be a marked modified privilege descriptor. 11)A privilege descriptor P is abandoned if: a) It is not an independent node, and Case: i) GRANT OPTION FOR is not specified, P is not itself a mod- ified privilege descriptor, and there exists no path to P from any independent node other than paths that include an identified privilege descriptor or a modified privilege descriptor. ii) GRANT OPTION FOR is specified, P is not itself a modi- fied privilege descriptor, and there exists no path to P from any independent node other than paths that include a modified privilege descriptor. b) P is a SELECT column privilege descriptor and there exists a SELECT table privilege descriptor X with the same grantee, grantor, catalog name, schema name, and table name and Case: i) GRANT OPTION FOR is not specified and X is an identified privilege descriptor or an abandoned privilege descriptor. ii) GRANT OPTION FOR is specified and X is an abandoned privi- lege descriptor. 12)Let S1 be the name of any schema and let A1 be the that owns the schema identified by S1. 13)Let V be any view descriptor included in S1. V is said to be abandoned if the destruction of all abandoned privilege descrip- tors and, if GRANT OPTION FOR is not specified, all identified privilege descriptors would result in A1 no longer having SELECT privilege on one or more tables or USAGE privilege on one or more domains, collations, character sets, or translations whose names are contained in the of V. 14)Let TC be any table constraint descriptor included in S1. TC is said to be abandoned if the destruction of all abandoned privi- lege descriptors and, if GRANT OPTION FOR is not specified, all identified privilege descriptors would result in A1 no longer having REFERENCES privilege on one or more referenced columns of TC or USAGE privilege on one or more domains, collations, 336 Database Language SQL X3H2-92-154/DBL CBR-002 11.37 character sets, or translations whose names are contained in any of TC. 15)Let AX be any assertion descriptor included in S1. AX is said to be abandoned if the destruction of all abandoned privilege descriptors and, if GRANT OPTION FOR is not specified, all iden- tified privilege descriptors would result in A1 no longer having REFERENCES privilege on one or more referenced columns of AX or USAGE privilege on one or more domains, collations, character sets, or translations whose names are contained in any of AX. 16)Let DC be any domain constraint descriptor included in S1. DC is said to be abandoned if the destruction of all abandoned privi- lege descriptors and, if GRANT OPTION FOR is not specified, all identified privilege descriptors would result in A1 no longer having REFERENCES privilege on one or more referenced columns of DC or USAGE privilege on one or more domains, collations, character sets, or translations whose names are contained in any of DC. 17)Let DO be any domain descriptor included in S1. DO is said to be abandoned if the destruction of all abandoned privilege descrip- tors and, if GRANT OPTION FOR is not specified, all identified privilege descriptors would result in A1 no longer having USAGE privilege on the collation whose name is contained in the of DO, if any. 18)If RESTRICT is specified, then there shall be no abandoned priv- ilege descriptors, abandoned views, abandoned table constraints, abandoned assertions, abandoned domain constraints, or abandoned domains. Access Rules 1) The applicable privileges shall include a privilege identifying O. General Rules 1) If GRANT OPTION FOR is not specified, then: a) All abandoned privilege descriptors are destroyed, and b) The identified privilege descriptors are destroyed, and c) The modified privilege descriptors are set to indicate that they are not grantable. 2) If GRANT OPTION FOR is specified, then Case: a) If CASCADE is specified, then all abandoned privilege de- scriptors are destroyed. Schema definition and manipulation 337 X3H2-92-154/DBL CBR-002 11.37 b) Otherwise, if there are any privilege descriptors directly dependent on an identified privilege descriptor that are not modified privilege descriptors, then an exception condition is raised: dependent privilege descriptors still exist. The identified privilege descriptors and the modified privilege descriptors are set to indicate that they are not grantable. 3) For every abandoned view descriptor V, let S1.VN be the
of V. The following is effectively executed without further Access Rule checking: DROP VIEW S1.VN CASCADE 4) For every abandoned table constraint descriptor TC, let S1.TCN be the of TC and let S2.T2 be the
of the table that contains TC (S1 and S2 not necessarily dif- ferent). The following is effectively executed without further Access Rule checking: ALTER TABLE S2.T2 DROP CONSTRAINT S1.TCN CASCADE 5) For every abandoned assertion descriptor AX, let S1.AXN be the of AX. The following is effectively executed without further Access Rule check- ing: DROP ASSERTION S1.AXN 6) For every abandoned domain constraint descriptor DC, let S1.DCN be the of DC and let S2.DN be the of the domain that contains DC. The following is effectively executed without further Access Rule checking: ALTER DOMAIN S2.DN DROP CONSTRAINT S1.DCN 7) For every abandoned domain descriptor DO, let S1.DN be the of DO. The following is effectively executed without further Access Rule checking: DROP DOMAIN S1.DN CASCADE 8) For every combination of and on O specified in , if there is no corresponding privilege de- scriptor in the set of identified privilege descriptors, then a completion condition is raised: warning-privilege not revoked. 9) If ALL PRIVILEGES was specified, then for each , if no privilege descriptors were identified, then a completion condition is raised: warning-privilege not revoked. 338 Database Language SQL X3H2-92-154/DBL CBR-002 11.37 Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain a . Schema definition and manipulation 339 X3H2-92-154/DBL CBR-002 340 Database Language SQL X3H2-92-154/DBL CBR-002 12 Module 12.1 Function Define a module. Format ::= [ ... ] ... ::= SCHEMA | AUTHORIZATION | SCHEMA AUTHORIZATION ::= ::= | | Syntax Rules 1) If SCHEMA is not specified, then a equal to is implicit. 2) If the explicit or implicit does not specify a , then an implementation-defined is implicit. 3) The implicit or explicit is the implicit for all unqualified s in the . 4) A or shall precede in the text of the any that references the of the or . Module 341 X3H2-92-154/DBL CBR-002 12.1 5) For every in a , there shall be exactly one in that that contains an that specifies the declared in the . Note: See the Syntax Rules of Subclause 13.1, "". Access Rules None. General Rules 1) If the SQL-agent that performs a call of a in a is not a program that conforms to the programming language standard specified by the of that , then the effect is implementation-dependent. 2) If the SQL-agent performs calls of s from more than one Ada task, then the results are implementation-dependent. 3) Case: a) If a is specified, then it is the current for privilege determination for the execution of each in the . b) Otherwise, the current for privi- lege determination for the execution of each in the is the SQL-session . 4) After the last time that an SQL-agent performs a call of a : a) A or a is effec- tively executed. If an unrecoverable error has occurred, or if the SQL-agent terminated unexpectedly, or if any con- straint is not satisfied, then a is performed. Otherwise, the choice of which of these SQL- statements to perform is implementation-dependent. The deter- mination of whether an SQL-agent has terminated unexpectedly is implementation-dependent. b) Let D be the of any system descriptor area that is currently allocated within an SQL-session associated with the SQL-agent. A that specifies DEALLOCATE DESCRIPTOR D is effectively executed. 342 Database Language SQL X3H2-92-154/DBL CBR-002 12.1 c) All SQL-sessions associated with the SQL-agent are termi- nated. Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) A shall not contain a . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) A shall be associated with an SQL-agent during its execution. An SQL-agent shall be associated with at most one . b) A shall not be a . c) A shall specify AUTHORIZATION and shall not specify SCHEMA. Module 343 X3H2-92-154/DBL CBR-002 12.2 12.2 Function Name a . Format ::= MODULE [ ] [ ] ::= NAMES ARE Syntax Rules 1) If a does not specify a , then the is unnamed. 2) The shall be different from the of any other in the same SQL-environment. Note: An SQL-environment may have multiple s that are unnamed. 3) If the of the containing specifies ADA, then a shall be specified, and that shall be a valid Ada library unit name. 4) If a is not specified, then a that specifies an implementation-defined character set that contains at least every character that is in is implicit. Access Rules None. General Rules 1) If a is specified, then in the SQL-environment the containing has the name given by . Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 344 Database Language SQL X3H2-92-154/DBL CBR-002 12.2 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) A shall not be speci- fied. Module 345 X3H2-92-154/DBL CBR-002 12.3 12.3 Function Define a procedure. Format ::= PROCEDURE ::= [ { }... ] | ... ::= | ::= SQLCODE | SQLSTATE Syntax Rules 1) The shall be different from the of any other in the containing . Note: The should be a standard-conforming pro- cedure, function, or routine name of the language specified by the subject . Failure to observe this recommen- dation will have implementation-dependent effects. 2) The of each in a shall be different from the of any other in that . 3) Any contained in the of a shall be specified in a in that . Note: s in a without enclos- ing parentheses and without commas separating multiple s is a deprecated feature that is supported for compatibility with earlier versions of this International Standard. See Annex D, "Deprecated features". 4) A call of a shall supply n parameters, where n is the number of s in the . 346 Database Language SQL X3H2-92-154/DBL CBR-002 12.3 5) A 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 that 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 Annex D, "Deprecated features". 6) Whether a is for an input parameter, an output parameter, or both is determined as follows: Case: a) A is an output parameter. b) For every that is not a , Case: i) 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. ii) 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. iii) 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 , then the parameter is both an input parameter and an output parameter. iv) Otherwise, the parameter is neither an input parameter nor an output parameter. 7) The Syntax Rules of Subclause 12.4, "Calls to a ", shall be true. Module 347 X3H2-92-154/DBL CBR-002 12.3 Access Rules None. General Rules 1) A defines a procedure that may be called by an SQL- agent. 2) If the that contains the is associated with an SQL-agent that is associated with another that contains a with the same , then the effect is implementation-defined. 3) If the that contains the has an explicit MAI that is different from the SQL-session SAI, then: a) Whether or not SAI can invoke s in a with explicit MAI is implementation-defined, as are any restrictions pertaining to such invocation. b) If SAI is restricted from invoking a in a with explicit MAI, then an exception condition is raised: invalid authorization specification. 4) If the value of any input parameter provided by the SQL-agent falls outside the set of allowed values of the data type of the parameter, or if the value of any output parameter resulting from the execution of the falls outside the set of values supported by the SQL-agent for that parameter, then the effect is implementation-defined. If the implementation- defined effect is the raising of an exception condition, then an exception condition is raised: data exception-invalid parameter value. 5) Let S be the of the . 6) When the is called by an SQL-agent: Case: a) If S is an , then: i) The that contains S is associated with the SQL- agent. ii) The diagnostics area is emptied. iii) S is executed. 348 Database Language SQL X3H2-92-154/DBL CBR-002 12.3 iv) If S successfully initiated or resumed an SQL-session, then subsequent calls to a by the SQL-agent are associated with that SQL-session until the SQL-agent terminates the SQL-session or makes it dormant. b) If S is an , then: i) The that contains S is associated with the SQL- agent. ii) S is executed. c) Otherwise: i) If no SQL-session is current for the SQL-agent, then Case: 1) If the SQL-agent has not executed an and there is no default SQL-session asso- ciated with the SQL-agent, then the following is effectively executed: CONNECT TO DEFAULT 2) If the SQL-agent has not executed an and there is a default SQL-session associated with the SQL-agent, then the following is effectively executed: SET CONNECTION DEFAULT 3) Otherwise, an exception condition is raised: connection exception-connection does not exist. Subsequent calls to a or invocations of s by the SQL-agent are associated with the SQL-session until the SQL-agent terminates the SQL-session or makes it dormant. ii) If an SQL-transaction is active for the SQL-agent, then S is associated with that SQL-transaction. iii) If no SQL-transaction is active for the SQL-agent and S is a transaction-initiating SQL-statement, then 1) An SQL-transaction is effectively initiated and asso- ciated with this call and with subsequent calls of any or invocations of s by that SQL-agent until the SQL-agent terminates that SQL-transaction. 2) Case: A) If a has been executed since the termination of the last SQL-transaction in the SQL-session, then the access mode, constraint Module 349 X3H2-92-154/DBL CBR-002 12.3 mode, and isolation level of the SQL-transaction are set as specified by the . B) Otherwise, the access mode of that SQL-transaction is read-write, the constraint mode for all constraints in that SQL-transaction is immediate, and the isolation level of that SQL-transaction is SERIALIZABLE. 3) The SQL-transaction is associated with the SQL-session. 4) The that contains S is associated with the SQL-transaction. iv) The that contains S is associated with the SQL- agent. v) If S contains an and the access mode of the current SQL-transaction is read-only, then an exception condition is raised: invalid transaction state. vi) The diagnostics area is emptied. vii) The values of all input parameters to the are established. viii)S is executed. 7) If the non-dynamic or dynamic execution of an or the execution of an , , or occurs within the same SQL-transaction as the non-dynamic or dynamic execution of an SQL-schema statement and this is not allowed by the SQL-implementation, then an exception condition is raised: invalid transaction state. 8) When a is called by an SQL-agent, let PDi be the of the i-th parameter and let DTi and PNi be the and the specified in PDi, respectively. Let PIi be the i-th parameter in the proce- dure call. 9) If S is a
| | | | | | | ::= | | | | | | | | | | ::= | | | 13.5 ::= SELECT [ ]
shall be the same as the number of elements in the corresponds with the i-th element of the and
are those specified in the . S shall be a valid . Access Rules None. General Rules 1) Let Q be the result of S. 2) Case: a) If the cardinality of Q is greater than 1, then an ex- ception condition is raised: cardinality violation. It is implementation-dependent whether or not SQL-data values are assigned to the targets identified by the , and a comple- tion condition is raised: no data. 382 Database Language SQL X3H2-92-154/DBL CBR-002 13.5 is in an implementation-dependent order. 5) If an exception condition is raised during the assignment of a value to a target, then the values of targets are implementation- dependent. 6) The target identified by the i-th of the is an exact numeric type, then the data type of the i-th column of the table T shall be an exact numeric type. b) The
shall not include a or a and shall not identify a grouped view. Data manipulation 383 X3H2-92-154/DBL CBR-002 13.6 13.6 Function Delete a row of a table. Format ::= DELETE FROM
WHERE CURRENT OF Syntax Rules 1) The containing shall contain a whose is the same as the in the . Let CR be the cursor specified by . 2) CR shall be an updatable cursor. Note: updatable cursor is defined in Subclause 13.1, "". 3) Let T be the table identified by the
. Let QS be the that is the simply underlying table of the simply underlying table of CR. The simply underlying table of QS shall be T. Note: The simply underlying table of a is defined in Subclause 13.1, "". Access Rules 1) The applicable privileges shall include DELETE for the
. Note: The applicable privileges for a
are defined in Subclause 10.3, "". General Rules 1) If the access mode of the current SQL-transaction is read-only and T is not a temporary table, then an exception condition is raised: invalid transaction state. 2) If cursor CR is not positioned on a row, then an exception con- dition is raised: invalid cursor state. 3) The row from which the current row of CR is derived is marked for deletion. 4) If, while CR is open, the row from which the current row of CR is derived has been marked for deletion by any , marked for deletion by any that identifies any cursor other than CR, updated 384 Database Language SQL X3H2-92-154/DBL CBR-002 13.6 by any , or updated by any that identifies any cursor other than CR, then a completion condition is raised: warning-cursor operation conflict. 5) All rows that are marked for deletion are effectively deleted at the end of the prior to the checking of any integrity constraint. 6) If the deleted the last row of CR, then the position of CR is after the last row; otherwise, the position of CR is before the next row. Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. Data manipulation 385 X3H2-92-154/DBL CBR-002 13.7 13.7 Function Delete rows of a table. Format ::= DELETE FROM
[ WHERE ] Syntax Rules 1) Let T be the table identified by the
. T shall not be a read-only table. 2) The scope of the
is the entire . Access Rules 1) The applicable privileges shall include DELETE for the
. Note: The applicable privileges for a
are defined in Subclause 10.3, "". General Rules 1) If the access mode of the current SQL-transaction is read-only and T is not a temporary table, then an exception condition is raised: invalid transaction state. 2) Case: a) If is not specified, then all rows of T are marked for deletion. b) If is specified, then it is applied to each row of T with the
bound to that row, and all rows for which the result of the is true are marked for deletion. The is effectively evaluated for each row of T before marking for deletion any row of T. Each in the is effectively executed for each row of T and the results used in the ap- plication of the to the given row of T. If any executed contains an outer reference to a column of T, the reference is to the value of that column in the given row of T. Note: Outer reference is defined in Subclause 6.4, "". 386 Database Language SQL X3H2-92-154/DBL CBR-002 13.7 3) If any row that is marked for deletion by the has been marked for deletion by any that identifies some cursor CR that is still open or updated by any that identifies some cursor CR that is still open, then a completion condition is raised: warning-cursor operation conflict. 4) All rows that are marked for deletion are effectively deleted at the end of the prior to the checking of any integrity constraint. 5) If no row is deleted, then a completion condition is raised: no data. Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) No leaf generally underlying table of T shall be an under- lying table of any generally contained in the . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. Data manipulation 387 X3H2-92-154/DBL CBR-002 13.8 13.8 Function Create new rows in a table. Format ::= INSERT INTO
::= [ ] | DEFAULT VALUES ::= Syntax Rules 1) The table T identified by the
shall not be a read- only table. 2) An that specifies DEFAULT VALUES is equivalent to an that specifies a of the form VALUES (DEFAULT, . . . ) where the number of "DEFAULT" entries is equal to the number of columns of T. 3) No of T shall be identified more than once. If the is omitted, then an that identifies all columns of T in the ascending sequence of their ordinal positions within T is implicit. 4) A column identified by the is an object column. 5) Let QT be the table specified by the . The degree of QT shall be equal to the number of s in the . The column of table T identified by the i-th in the corresponds with the i-th column of QT. 6) The Syntax Rules of Subclause 9.2, "Store assignment", apply to corresponding columns of T and QT as TARGET and VALUE, respec- tively. 388 Database Language SQL X3H2-92-154/DBL CBR-002 13.8 Access Rules 1) Case: a) If an is specified, then the applicable shall include INSERT for each in the . b) Otherwise, the applicable privileges shall include INSERT for each in T. Note: The applicable privileges for a
are defined in Subclause 10.3, "". 2) Each in the shall identify a column of T. General Rules 1) If the access mode of the current SQL-transaction is read-only and T is not a temporary table, then an exception condition is raised: invalid transaction state. 2) Let B be the leaf generally underlying table of T. 3) The is effectively evaluated before inserting any rows into B. 4) Let Q be the result of that . Case: a) If Q is empty, then no row is inserted and a completion con- dition is raised: no data. b) Otherwise, for each row R of Q: i) A candidate row of B is effectively created in which the value of each column is its default value, as specified in the General Rules of Subclause 11.5, "". The candidate row includes every column of B. ii) For every object column in the candidate row, the value of the object column identified by the i-th in the is replaced by the i-th value of R. iii) Let C be a column that is represented in the candidate row and let SV be its value in the candidate row. The General Rules of Subclause 9.2, "Store assignment", are applied to C and SV as TARGET and VALUE, respectively. iv) The candidate row is inserted into B. Note: The data values allowable in the candidate row may be constrained by a WITH CHECK OPTION constraint. The effect of a WITH CHECK OPTION constraint is defined in the General Rules of Subclause 11.19, "". Data manipulation 389 X3H2-92-154/DBL CBR-002 13.8 Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) The leaf generally underlying table of T shall not be gen- erally contained in the immediately contained in the except as the of a . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) The that is contained in an shall be a or it shall be a that contains exactly one of the form " ", and each of that shall be a . b) If the data type of the target identified by the i-th is an exact numeric type, then the data type of the i- th item of the shall be an exact numeric type. c) If the data type of the target C identified by the i-th is character string, then the length in characters of the i-th item of the shall be less than or equal to the length of C. d) The shall immediately contain a . 390 Database Language SQL X3H2-92-154/DBL CBR-002 13.9 13.9 Function Update a row of a table. Format ::= UPDATE
SET WHERE CURRENT OF ::= [ { }... ] ::= ::= | | DEFAULT ::= Syntax Rules 1) The containing shall contain a for a cursor whose is the same as the in the . Let CR be the cursor specified by . 2) CR shall be an updatable cursor. Note: updatable cursor is defined in Subclause 13.1, "". 3) Let T be the table identified by the
. Let QS be the that is the simply underlying table of the simply underlying table of CR. The simply underlying table of QS shall be T. Note: The simply underlying table of a is defined in Subclause 13.1, "". 4) If CR is an ordered cursor, then for each OC, the column of T identified by OC shall not be directly or in- directly referenced in the of the defining for CR. Data manipulation 391 X3H2-92-154/DBL CBR-002 13.9 5) No leaf generally underlying table of T shall be an underly- ing table of any generally contained in any immediately contained in any contained in the . 6) A in a shall not directly con- tain a . 7) The same shall not appear more than once in a . 8) If the cursor identified by was specified using an explicit or implicit of FOR UPDATE, then each specified as an shall identify a column in the explicit or implicit associated with the . 9) The scope of the
is the entire . 10)For every , the Syntax Rules of Subclause 9.2, "Store assignment", apply to the column of T identified by the and the of the as TARGET and VALUE, respectively. Access Rules 1) The applicable privileges shall include UPDATE for each . Note: The applicable privileges for a
are defined in Subclause 10.3, "". 2) Each specified as an shall iden- tify a column of T. General Rules 1) If the access mode of the current SQL-transaction is read-only and T is not a temporary table, then an exception condition is raised: invalid transaction state. 2) If cursor CR is not positioned on a row, then an exception con- dition is raised: invalid cursor state. 3) The object row is that row from which the current row of CR is derived. 4) If, while CR is open, the object row has been marked for dele- tion by any , marked for deletion by any that identifies any cursor other than CR, updated by any , or updated by any that identifies any cursor other than CR, then a completion condition is raised: warning-cursor operation conflict. 392 Database Language SQL X3H2-92-154/DBL CBR-002 13.9 5) The value of DEFAULT is the default value indicated in the col- umn descriptor for the in the containing . 6) The s are effectively evaluated before updat- ing the object row. If a contains a reference to a column of T, then the reference is to the value of that column in the object row before any value of the object row is updated. 7) CR remains positioned on its current row, even if an exception condition is raised during derivation of any associated with the object row. 8) A specifies an object column and an update value of that column. The object column is the column identified by the in the . The update value is the value specified by the . Note: The data values allowable in the object row may be con- strained by a WITH CHECK OPTION constraint. The effect of a WITH CHECK OPTION constraint is defined in the General Rules of Subclause 11.19, "". 9) The object row is updated as specified by each . For each , the value of the specified object column, denoted by C, is replaced by the specified update value, denoted by SV. The General Rules of Subclause 9.2, "Store assignment", are applied to C and SV as TARGET and VALUE, respectively. Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) CR shall not be an ordered cursor. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) If the data type of the column identified by the i-th is an exact numeric type, then the data type of the i-th in the shall be an exact numeric type. b) If the data type of the column identified by the i-th C is character string, then the length in characters of the i-th in the shall be less than or equal to the length of C. c) An shall not specify DEFAULT. Data manipulation 393 X3H2-92-154/DBL CBR-002 13.10 13.10 Function Update rows of a table. Format ::= UPDATE
SET [ WHERE ] Syntax Rules 1) Let T be the table identified by the
. T shall be an updatable table. 2) A in a shall not directly con- tain a . 3) The same shall not appear more than once in a . 4) The scope of the
is the entire . 5) For every , the Syntax Rules of Subclause 9.2, "Store assignment", apply to the column of T identified by the and the of the as TARGET and VALUE, respectively. Access Rules 1) The applicable privileges shall include UPDATE for each . Note: The applicable privileges for a
are defined in Subclause 10.3, "". 2) Each specified as an shall iden- tify a column of T. General Rules 1) If the access mode of the current SQL-transaction is read-only and T is not a temporary table, then an exception condition is raised: invalid transaction state. 2) Case: a) If a is not specified, then all rows of T are the object rows. 394 Database Language SQL X3H2-92-154/DBL CBR-002 13.10 b) If a is specified, then it is applied to each row of T with the
bound to that row, and the object rows are those rows for which the result of the is true. The is effectively evaluated for each row of T before updating any row of T. Each in the is effectively executed for each row of T and the results used in the ap- plication of the to the given row of T. If any executed contains an outer reference to a column of T, the reference is to the value of that column in the given row of T. Note: Outer reference is defined in Subclause 6.4, "". 3) If any row in the set of object rows has been marked for dele- tion by any that identifies some cursor CR that is still open or updated by any that identifies some cursor CR that is still open, then a completion condition is raised: warning-cursor operation conflict. 4) If the set of object rows is empty, then a completion condition is raised: no data. 5) If a completion condition no data has been raised, then no fur- ther General Rules of this Subclause are applied. 6) The s are effectively evaluated for each row of T before updating any row of T. 7) A specifies an object column and an update value of that column. The object column is the column identified by the in the . The update value is the value specified by the . Note: The data values allowable in the object row may be con- strained by a WITH CHECK OPTION constraint. The effect of a WITH CHECK OPTION constraint is defined in the General Rules of Subclause 11.19, "". 8) Each object row is updated as specified by each . For each , the value of the specified object column, denoted by C, is replaced by the specified update value, denoted by SV. The General Rules of Subclause 9.2, "Store assignment", are applied to C and SV as TARGET and VALUE, respectively. Data manipulation 395 X3H2-92-154/DBL CBR-002 13.10 Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) No leaf generally underlying table of T shall be an under- lying table of any generally contained in the or in any immedi- ately contained in any contained in the . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) If the data type of the column identified by the i-th is an exact numeric type, then the data type of the i-th in the shall be an exact numeric type. b) If the data type of the column identified by the i-th C is character string, then the length in characters of the i-th in the shall be less than or equal to the length of C. 396 Database Language SQL X3H2-92-154/DBL CBR-002 13.11 13.11 Function Declare a declared local temporary table that will be effectively materialized the first time that any in the that contains the is executed and whose scope is all the s of that executed within the same SQL-session. Format ::= DECLARE LOCAL TEMPORARY TABLE
[ ON COMMIT { PRESERVE | DELETE } ROWS ] Syntax Rules 1) Let T be the of . T shall be different from the of any other contained within the . 2) Let A be the current . 3) The descriptor of the table defined by a includes the name of T and the column descriptor speci- fied by each . The i-th column descriptor is given by the i-th . 4) A shall contain at least one . 5) If ON COMMIT is not specified, then ON COMMIT DELETE ROWS is implicit. Access Rules None. General Rules 1) Let U be the implementation-dependent that is effectively derived from the implementation-dependent SQL- session identifier associated with the SQL-session and an implementation-dependent name associated with the that contains the . 2) The definition of T within a is effectively equivalent to the definition of a persistent base table U.T. Within the module, any reference to MODULE.T is equivalent to a reference to U.T. Data manipulation 397 X3H2-92-154/DBL CBR-002 13.11 3) A set of privilege descriptors is created that define the priv- ileges INSERT, SELECT, UPDATE, DELETE, and REFERENCES on this table and INSERT (), UPDATE (), and REFERENCES () for every in the table definition to A. These privileges are not grantable. The grantor for each of these privilege descriptors is set to the special grantor value "_SYSTEM". 4) The definition of a temporary table persists for the duration of the SQL-session. The termination of the SQL-session is effec- tively followed by the execution of the following with the current and current U without further Access Rule checking: DROP TABLE T 5) The definition of a declared local temporary table does not appear in any view of the Information Schema. Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall not contain any . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. 398 Database Language SQL X3H2-92-154/DBL CBR-002 14 Transaction management 14.1 Function Set the attributes of the next SQL-transaction for the SQL-agent. Format ::= SET TRANSACTION [ { }... ] ::= | | ::= READ ONLY | READ WRITE ::= ISOLATION LEVEL ::= READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE ::= DIAGNOSTICS SIZE ::= Syntax Rules 1) No shall be specified more than once. 2) If an is not specified, then a of ISOLATION LEVEL SERIALIZABLE is implicit. 3) If READ WRITE is specified, then the shall not be READ UNCOMMITTED. Transaction management 399 X3H2-92-154/DBL CBR-002 14.1 4) If a is not specified and a of READ UNCOMMITTED is specified, then READ ONLY is implicit. Otherwise, READ WRITE is implicit. 5) The data type of shall be exact numeric with scale 0. Access Rules None. General Rules 1) If a statement is executed when an SQL-transaction is currently active, then an exception condition is raised: invalid transaction state. 2) If is specified and is less than 1, then an exception condition is raised: invalid condition number. 3) Let TXN be the next SQL-transaction for the SQL-agent. 4) If READ ONLY is specified, then the access mode of TXN is set to read-only. If READ WRITE is specified, then the access mode of TXN is set to read-write. 5) The isolation level of TXN is set to an implementation-defined isolation level that will not exhibit any of the phenomena that the explicit or implicit would not exhibit, as specified in Table 9, "SQL-transaction isolation levels and the three phenomena". 6) If is specified, then the diagnostics area limit of TXN is set to . 7) If is not specified, then the diagnostics area limit of TXN is set to an implementation-dependent value not less than 1. Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain any . 400 Database Language SQL X3H2-92-154/DBL CBR-002 14.2 14.2 Function If an SQL-transaction is currently active, then set the constraint mode for that SQL-transaction in the current SQL-session. If no SQL-transaction is currently active, then set the constraint mode for the next SQL-transaction in the current SQL-session for the SQL-agent. Format ::= SET CONSTRAINTS { DEFERRED | IMMEDIATE } ::= ALL | [ { }... ] Syntax Rules 1) If a is specified, then it shall identify a constraint. 2) The constraint identified by shall be DEFERRABLE. Access Rules None. General Rules 1) If an SQL-transaction is currently active, then let TXN be the currently active SQL-transaction. Otherwise, let TXN be the next SQL-transaction for the SQL-agent. 2) If IMMEDIATE is specified, then Case: a) If ALL is specified, then the constraint mode in TXN of all constraints that are DEFERRABLE is set to immediate. b) Otherwise, the constraint mode in TXN for the constraints identified by the s in the is set to immediate. 3) If DEFERRED is specified, then Transaction management 401 X3H2-92-154/DBL CBR-002 14.2 Case: a) If ALL is specified, then the constraint mode in TXN of all constraints that are DEFERRABLE is set to deferred. b) Otherwise, the constraint mode in TXN for the constraints identified by the s in the is set to deferred. Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall not contain any . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. 402 Database Language SQL X3H2-92-154/DBL CBR-002 14.3 14.3 Function Terminate the current SQL-transaction with commit. Format ::= COMMIT [ WORK ] Syntax Rules None. Access Rules None. General Rules 1) If the current SQL-transaction is part of an encompassing trans- action that is controlled by an agent other than the SQL-agent, then an exception condition is raised: invalid transaction ter- mination. 2) For every open cursor CR in any associated with the current SQL-transaction, the following statement is implicitly executed: CLOSE CR 3) For every temporary table in any associated with the current SQL-transaction that specifies the ON COMMIT DELETE option and that was updated by the current SQL-transaction, the execution of the is effectively preceded by the execution of a that specifies DELETE FROM T, where T is the
of that temporary table. 4) The effects specified in the General Rules of Subclause 14.2, "" occur as if the statement SET CONSTRAINTS ALL IMMEDIATE were executed. 5) Case: a) If any constraint is not satisfied, then any changes to SQL- data or schemas that were made by the current SQL-transaction are canceled and an exception condition is raised: transac- tion rollback-integrity constraint violation. Transaction management 403 X3H2-92-154/DBL CBR-002 14.3 b) If any other error preventing commitment of the SQL- transaction has occurred, then any changes to SQL-data or schemas that were made by the current SQL-transaction are canceled and an exception condition is raised: transaction rollback with an implementation-defined subclass value. c) Otherwise, any changes to SQL-data or schemas that were made by the current SQL-transaction are made accessible to all concurrent and subsequent SQL-transactions. 6) The current SQL-transaction is terminated. Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) In conforming Entry SQL language, WORK shall be specified. 404 Database Language SQL X3H2-92-154/DBL CBR-002 14.4 14.4 Function Terminate the current SQL-transaction with rollback. Format ::= ROLLBACK [ WORK ] Syntax Rules None. Access Rules None. General Rules 1) If the current SQL-transaction is part of an encompassing trans- action that is controlled by an agent other than the SQL-agent and the is not being implicitly executed, then an exception condition is raised: invalid transaction ter- mination. 2) For every open cursor CR in any associated with the current SQL-transaction, the following statement is implicitly executed: CLOSE CR 3) Any changes to SQL-data or schemas that were made by the current SQL-transaction are canceled. 4) The current SQL-transaction is terminated. Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) In conforming Entry SQL language, WORK shall be specified. Transaction management 405 X3H2-92-154/DBL CBR-002 406 Database Language SQL X3H2-92-154/DBL CBR-002 15 Connection management 15.1 Function Establish an SQL-connection. Format ::= CONNECT TO ::= [ AS ] [ USER ] | DEFAULT Syntax Rules 1) If is not specified, then an implementation-defined for the SQL-connection is implicit. Access Rules None. General Rules 1) If a is executed after the first transaction- initiating SQL-statement executed by the current SQL-transaction and the implementation does not support transactions that affect more than one SQL-server, then an exception condition is raised: feature not supported-multiple server transactions 2) If is specified, then let S be the character string that is the value of and let V be the character string that is the value of TRIM ( BOTH ' ' FROM CV ) 3) If V does not conform to the Format and Syntax Rules of an , then an exception condition is raised: invalid authorization specification. Connection management 407 X3H2-92-154/DBL CBR-002 15.1 4) If the that contains the that contains the specifies a , then whether or not must be identical to that is implementation-defined, as are any other restrictions on the value of . Otherwise, any restrictions on the value of are implementation-defined. 5) If the value of violates the implementation-defined restrictions, then an exception condition is raised: invalid authorization specification. 6) If was specified, then let CV be the value of the immediately contained in . If neither DEFAULT nor were specified, then let CV be the value of . Let CN be the result of TRIM ( BOTH ' ' FROM CV ) If CN does not conform to the Format and Syntax Rules of an , then an exception condition is raised: invalid connection name. 7) If an SQL-connection with name CN has already been established by the current SQL-agent and has not been disconnected, or if DEFAULT is specified and a default SQL-connection has already been established by the current SQL-agent and has not been dis- connected, then an exception condition is raised: connection exception-connection name in use. 8) Case: a) If DEFAULT is specified, then the default SQL-session is initiated and associated with the default SQL-server. The method by which the default SQL-server is determined is implementation-defined. b) Otherwise, an SQL-session is initiated and associated with the SQL-server identified by . The method by which is used to determine the appropriate SQL-server is implementation-defined. 9) If the successfully initiates an SQL- session, then: a) The current SQL-connection and current SQL-session, if any, become a dormant SQL-connection and a dormant SQL-session, respectively. The SQL-server context information is preserved and is not affected in any way by operations performed over the initiated SQL-connection. Note: The SQL-session context information is defined in Subclause 4.30, "SQL-sessions". 408 Database Language SQL X3H2-92-154/DBL CBR-002 15.1 b) The SQL-session initiated by the becomes the current SQL-session and the SQL-connection established to that SQL-session becomes the current SQL-connection. Note: If the fails to initiate an SQL- session, then the current SQL-connection and current SQL- session, if any, remain unchanged. 10)If the SQL-client cannot establish the SQL-connection, then an exception condition is raised: connection exception- SQL-client unable to establish SQL-connection. 11)If the SQL-server rejects the establishment of the SQL- connection, then an exception condition is raised: connection exception- SQL-server rejected establishment of SQL-connection. 12)The SQL-server for the subsequent execution of s in any s associated with the SQL-agent is set to the SQL-server identified by . 13)The SQL-session is set to . Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall not contain any . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. Connection management 409 X3H2-92-154/DBL CBR-002 15.2 15.2 Function Select an SQL-connection from the available SQL-connections. Format ::= SET CONNECTION ::= DEFAULT | Syntax Rules None. Access Rules None. General Rules 1) If a is executed after the first transaction-initiating SQL-statement executed by the current SQL-transaction and the implementation does not support trans- actions that affect more than one SQL-server, then an excep- tion condition is raised: feature not supported-multiple server transactions. 2) Case: a) If DEFAULT is specified and there is no default SQL- connection that is current or dormant for the current SQL- agent, then an exception condition is raised: connection exception-connection does not exist. b) Otherwise, if does not identify an SQL- session that is current or dormant for the current SQL-agent, then an exception condition is raised: connection exception- connection does not exist. 3) If the SQL-connection identified by cannot be selected, then an exception condition is raised: connection exception-connection failure. 4) The current SQL-connection and current SQL-session become a dor- mant SQL-connection and a dormant SQL-session, respectively. 410 Database Language SQL X3H2-92-154/DBL CBR-002 15.2 The SQL-server context information is preserved and is not af- fected in any way by operations performed over the selected SQL-connection. Note: The SQL-session context information is defined in Subclause 4.30, "SQL-sessions". 5) The SQL-connection identified by becomes the current SQL-connection and the SQL-session associated with that SQL-connection becomes the current SQL-session. All SQL-session context information is restored to the same state as at the time the SQL-connection became dormant. Note: The SQL-session context information is defined in Subclause 4.30, "SQL-sessions". 6) The SQL-server for the subsequent execution of s in any s associated with the SQL-agent is set to that of the current SQL-connection. Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall not contain any . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. Connection management 411 X3H2-92-154/DBL CBR-002 15.3 15.3 Function Terminate an SQL-connection. Format ::= DISCONNECT ::= | ALL | CURRENT Syntax Rules None. Access Rules None. General Rules 1) If is specified and does not identify an SQL-connection that is current or dormant for the current SQL-agent, then an exception condition is raised: connection exception-connection does not exist. 2) If DEFAULT is specified and there is no default SQL-connection that is current or dormant for the current SQL-agent, then an exception condition is raised: connection exception-connection does not exist. 3) If CURRENT is specified and there is no current SQL-connection for the current SQL-agent, then an exception condition is raised: connection exception-connection does not exist. 4) Let C be the current SQL-connection. 5) Let L be a list of SQL-connections. If a is specified, then L is that SQL-connection. If CURRENT is spec- ified, then L is the current SQL-connection, if any. If ALL is specified, then L is a list representing every SQL-connection that is current or dormant for the current SQL-agent, in an implementation-dependent order. If DEFAULT is specified, then L is the default SQL-connection. 6) If any SQL-connection in L is active, then an exception condi- tion is raised: invalid transaction state. 412 Database Language SQL X3H2-92-154/DBL CBR-002 15.3 7) For every SQL-connection C1 in L, treating the SQL-session S1 identified by C1 as the current SQL-session, all of the actions that are required after the last call of a by an SQL-agent, except for the execution of a or a , are performed. C1 is terminated, re- gardless of any exception condition that might occur during the disconnection process. Note: See the General Rules of Subclause 12.1, "", for the actions to be performed after the last call of a by an SQL-agent. 8) If any error is detected during execution of a , then a completion condition is raised: warning- disconnect error. 9) If C is contained in L, then there is no current SQL-connection following the execution of the . Otherwise, C remains the current SQL-connection. Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall not contain any . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. Connection management 413 X3H2-92-154/DBL CBR-002 414 Database Language SQL X3H2-92-154/DBL CBR-002 16 Session management 16.1 Function Set the default catalog name for unqualified s in s that are prepared in the current SQL- session by an or a and in s that are invoked directly. Format ::= SET CATALOG Syntax Rules 1) The of the shall be an SQL character data type. Access Rules None. General Rules 1) Let S be the character string that is the value of the and let V be the character string that is the value of TRIM ( BOTH ' ' FROM S ) 2) If V does not conform to the Format and Syntax Rules of a , then an exception condition is raised: invalid catalog name. 3) The default catalog name of the current SQL-session is set to V. Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall not contain any . Session management 415 X3H2-92-154/DBL CBR-002 16.1 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions None. 416 Database Language SQL X3H2-92-154/DBL CBR-002 16.2 16.2 Function Set the default schema name for unqualified s in s that are prepared in the current SQL- session by an or a and in s that are invoked directly. Format ::= SET SCHEMA Syntax Rules 1) The data type of the shall be an SQL char- acter data type. Access Rules None. General Rules 1) Let S be the character string that is the value of the and let V be the character string that is the value of TRIM ( BOTH ' ' FROM S ) 2) If V does not conform to the Format and Syntax Rules of a , then an exception condition is raised: invalid schema name. 3) Case: a) If V conforms to the Format and Syntax Rules for a that contains a , then let X be the part and let Y be the part of V. The following statement is implicitly executed: SET CATALOG 'X' and the is effectively replaced by: SET SCHEMA 'Y' b) Otherwise, the default unqualified schema name of the current SQL-session is set to V. Session management 417 X3H2-92-154/DBL CBR-002 16.2 Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall not contain any . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. 418 Database Language SQL X3H2-92-154/DBL CBR-002 16.3 16.3 Function Set the default character set name for s and s in s that are prepared in the current SQL-session by an or a and in s that are invoked directly. Format ::= SET NAMES Syntax Rules 1) The of the shall be an SQL character data type. Access Rules None. General Rules 1) Let S be the character string that is the value of the and let V be the character string that is the value of TRIM ( BOTH ' ' FROM S ) 2) If V does not conform to the Format and Syntax Rules of a , then an exception condition is raised: invalid character set name. 3) The default character set name of the current SQL-session is set to V. Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall not contain any . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. Session management 419 X3H2-92-154/DBL CBR-002 16.4 16.4 Function Set the of the current SQL-session. Format ::= SET SESSION AUTHORIZATION Syntax Rules 1) The of the shall be an SQL character data type. Access Rules None. General Rules 1) If a is ex- ecuted and an SQL-transaction is currently active, then an exception condition is raised: invalid transaction state. 2) Let S be the character string that is the value of the and let V be the character string that is the value of TRIM ( BOTH ' 'FROM S ) 3) If V does not conform to the Format and Syntax Rules of an , then an exception condition is raised: invalid authorization specification. 4) Whether or not the for the SQL- session can be set to an other than the of the SQL-session when the SQL-session is started is implementation-defined, as are any restrictions pertaining to such changes. 5) If the current is restricted from setting the to the specified value, then an exception condition is raised: invalid authorization specification. 6) Let T be any temporary table defined in the currently active SQL-session. In all the privilege descriptors for T and for each of the columns of T, the is set to V. 420 Database Language SQL X3H2-92-154/DBL CBR-002 16.4 7) The of the current SQL-session is set to V. Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain any . Session management 421 X3H2-92-154/DBL CBR-002 16.5 16.5 Function Set the default local time zone displacement for the current SQL- session. Format ::= SET TIME ZONE ::= | LOCAL Syntax Rules 1) The of the immediately contained in the shall be INTERVAL HOUR TO MINUTE. Access Rules None. General Rules 1) Case: a) If LOCAL is specified, then the default local time zone dis- placement of the current SQL-session is set to the original implementation-defined default local time zone displace- ment that was established when the current SQL-session was started. b) Otherwise, Case: i) If the value of the is not the null value and is between INTERVAL -'12:59' and INTERVAL +'13:00', then the default local time zone displacement of the current SQL-session is set to the value of the . ii) Otherwise, an exception condition is raised: data exception- invalid time zone displacement value. 422 Database Language SQL X3H2-92-154/DBL CBR-002 16.5 Leveling Rules 1) The following restrictions apply for Intermediate SQL; None. 2) The following restrictions apply for Entry SQL; a) Conforming Entry SQL language shall not contain any . Session management 423 X3H2-92-154/DBL CBR-002 424 Database Language SQL X3H2-92-154/DBL CBR-002 17 Dynamic SQL 17.1 Description of SQL item descriptor areas Function Specify the identifiers, data types, and codes used in SQL item descriptor areas. Syntax Rules 1) An SQL item descriptor area comprises the items specified in Table 17, "Data types of s used in SQL item descriptor areas". 2) Let DT be a data type. The data type T of a or a SVT is said to match the data type specified by the item descriptor area if and only if one of the following conditions is true. Case: a) TYPE indicates NUMERIC and T is specified by NUMERIC(P,S), where P is the value of PRECISION and S is the value of SCALE. b) TYPE indicates DECIMAL and T is specified by DECIMAL(P,S), where P is the value of PRECISION and S is the value of SCALE. c) TYPE indicates INTEGER and T is specified by INTEGER. d) TYPE indicates SMALLINT and T is specified by SMALLINT. e) TYPE indicates FLOAT and T is specified by FLOAT(P), where P is the value of PRECISION. f) TYPE indicates REAL and T is specified by REAL. g) TYPE indicates DOUBLE PRECISION and T is specified by DOUBLE PRECISION. h) TYPE indicates BIT and T is specified by BIT(L), where L is the value of LENGTH. i) TYPE indicates BIT VARYING and T is specified by BIT VARYING(L), where Case: i) SVT is a and L is the value of LENGTH. Dynamic SQL 425 X3H2-92-154/DBL CBR-002 17.1 Description of SQL item descriptor areas ii) SVT is a and L is not less than the value of LENGTH. j) TYPE indicates CHARACTER and T is specified by CHARACTER(L), where L is the value of LENGTH and the formed by the values of CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, and CHARACTER_SET_NAME identifies the character set of SVT. k) TYPE indicates CHARACTER VARYING and T is specified by CHARACTER VARYING(L), where the formed by the values of CHARACTER_SET_CATALOG, CHARACTER_SET_ SCHEMA, and CHARACTER_SET_NAME identifies the character set of SVT and Case: i) SVT is a and L is the value of LENGTH. ii) SVT is a and L is not less than the value of LENGTH. 3) An item descriptor area is valid if and only if TYPE indicates a code defined in Table 18, "Codes used for SQL data types in Dynamic SQL", and one of the following is true: Case: a) TYPE indicates NUMERIC and PRECISION and SCALE are valid precision and scale values for the NUMERIC data type. b) TYPE indicates DECIMAL and PRECISION and SCALE are valid precision and scale values for the DECIMAL data type. c) TYPE indicates FLOAT and PRECISION is a valid precision value for the FLOAT data type. d) TYPE indicates INTEGER, SMALLINT, REAL, or DOUBLE PRECISION. e) TYPE indicates BIT or BIT VARYING and LENGTH is a valid length value for the BIT date type. f) TYPE indicates CHARACTER or CHARACTER VARYING, LENGTH is a valid length value for the CHARACTER data type, and CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, and CHARACTER_ SET_NAME are a valid qualified character set name for the CHARACTER data type. g) TYPE indicates a , DATETIME_INTERVAL_CODE is a code specified in Table 19, "Codes associated with datetime data types in Dynamic SQL", and PRECISION is a valid value for the
in which the i-th in each is E1 and either: i) P is not an , or ii) P is an and the
is not the simply contained in the . k) P contains an with an in which E1 is both the and the first of the . l) P contains a in which both immediately contained s are E1. m) P contains a or a whose immediately contained is E1. n) P contains a whose operand is E1. o) P contains a whose or is E1. p) P contains a whose is E1. q) P contains a . 444 Database Language SQL X3H2-92-154/DBL CBR-002 17.6 5) Case: a) If E1 is followed by an IQ, then the data type of E1 is assumed to be INTERVAL IQ. b) In OCTET_LENGTH(E1), CHARACTER_LENGTH(E1), and CHARACTER_ LENGTH(E1), the data type of E1 is assumed to be CHARACTER VARYING(L), where L is the implementation-defined maximum value of for CHARACTER VARYING. c) In POSITION(X1 IN X2), and SUBSTRING(X1 FROM X3 FOR X4), if X1 (X2) meets the criteria for E1, E2, E3, and E4, then the data type of X1 (X2) is assumed to be CHARACTER VARYING(L), where L is the implementation-defined maximum value of for CHARACTER VARYING. If X3 (X4) meets the cri- teria for E1, E2, E3, and E4, then the data type of X3 (X4) is assumed to be NUMERIC(P,0), where P is the implementation- defined maximum value of for NUMERIC. d) In a of the form "X1 X2", if X1 (X2) meets the criteria for E1, E2, E3, and E4, then the data type of X1 (X2) is assumed to be CHARACTER VARYING(L), where L is the implementation-defined maximum value of for CHARACTER VARYING. e) In BIT_LENGTH(E1), the data type of E1 is assumed to be BIT VARYING(L), where L is the implementation-defined maximum value of for BIT VARYING. f) In a of the form "E1 + ", " + E1" or " - E1", the data type of E1 is assumed to be Case: i) If the is a date data type, then the data type of E1 is assumed to be INTERVAL YEAR(P) TO MONTH, where P is the implementation-defined maximum . ii) Otherwise, the data type of E1 is assumed to be INTERVAL DAY(P) TO SECOND(F), where P and F are the implementation- defined maximum and max- imum , respectively. g) In a of the form " * E1" or " / E1", the data type of E1 is assumed to be NUMERIC(P,0), where P is the implementation-defined maximum value of for NUMERIC. h) In all other s of the form "E1+ F", "E1- F", "E1* F", "E1/ F", "F + E1", "F - E1", "F * E1", or "F / E1", the data type of E1 is assumed to be the data type of F. Dynamic SQL 445 X3H2-92-154/DBL CBR-002 17.6 i) In a of the form "CAST (E1 AS )", "CAST (E1 AS )", the data type of E1 is the of the specified domain or the explicitly- specified . j) If one or more operands of COALESCE are E1, then the data type of E1 is assumed to be the data type of the first operand. k) If one or more s in a are E1, then the data type of E1 is assumed to be the data type of the first . l) If one operand of NULLIF is E1, then the data type of E1 is assumed to be the data type of the other operand. m) In the first and second operands of a or , or the first and third operands of a , if the i-th value of one operand is E1, then the data type of E1 is the data type of the i-th value of the other operand. n) In the first and second operands of an , if either of the first s is E1, then the data type of E1 is the data type of the first of the other operand. If both of the first s are E1, then the data type of each E1 is assumed to be TIMESTAMP WITH TIME ZONE. o) In a
in which the i-th of some is E1 that contains a whose i-th is not E1, the data type of E1 is the data type of the i-th of the first whose i-th is not E1. p) In a
in which the i-th in each is E1 that is the simply contained in an , the data type of E1 is the data type of the corresponding column of the implicit or explicit con- tained in the . q) In an that specifies a
, the data types of s E1 in the are assumed to be the same as the data types of the respective columns of the
. r) In an that specifies an , if the is not E1, then let D be its data type. Otherwise, let D be the data type of the first of the . The data type of any E1 in the is assumed to be D. 446 Database Language SQL X3H2-92-154/DBL CBR-002 17.6 s) If E1 appears for , , or in , then the data type of E1 is assumed to be CHARACTER VARYING(L), where L is the implementation-defined maximum value of for CHARACTER VARYING. t) If any value in the of a or is E1, then the data type of E1 is assumed to be the same as the data type of the respective column of the
. u) If in , , , or is E1, then the data type of E1 is assumed to be CHARACTER VARYING(L), where L is the implementation-defined maximum value of for CHARACTER VARYING. v) If in is E1, then the data type of E1 is assumed to be CHARACTER VARYING(L), where L is the implementation-defined maximum value of for CHARACTER VARYING. w) If a in a is E1, then the data type of E1 is assumed to be the same data type as the corresponding . 6) If the value of the identifies an existing prepared statement, then an implicit DEALLOCATE PREPARE SSN is executed, where SSN is the value of the . 7) P is prepared for execution. 8) If is specified for the , then let S be the character string that is the value of the and let V be the character string that is the result of TRIM ( BOTH ' ' FROM S ) If V does not conform to the Format and Syntax Rules of an , then an exception condition is raised: invalid SQL statement identifier. 9) Case: a) If is specified for the , then the value of the is associated with the prepared statement. This value and ex- plicit or implied shall be specified for each or that is to be associated with this prepared statement. Dynamic SQL 447 X3H2-92-154/DBL CBR-002 17.6 b) If is specified for the , then: i) If P is a and is associated with a cursor C through a , then an association is made between C and P. The association is preserved until the prepared statement is destroyed. ii) If P is not a and is associated with a cursor C through a , then an exception condition is raised: dynamic SQL error- prepared statement is not a cursor specification. iii) Otherwise, the same shall be specified for each that is to be associated with this prepared statement. 10)The validity of an value or a in an SQL-transaction different from the one in which the statement was prepared is implementation-dependent. Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain any Dynamic SQL language. 448 Database Language SQL X3H2-92-154/DBL CBR-002 17.7 17.7 Function Deallocate SQL-statements that have been prepared with a . Format ::= DEALLOCATE PREPARE Syntax Rules 1) If is a , then the that contains the shall also contain a that specifies the same . Access Rules None. General Rules 1) If the does not identify a statement pre- pared in the scope of the , then an excep- tion condition is raised: invalid SQL statement name. 2) If the value of identifies an existing prepared statement that is the of an open cursor, then an exception condition is raised: invalid cursor state. 3) The prepared statement identified by the is destroyed. Any cursor that was allocated with an that is associated with the prepared state- ment identified by the is destroyed. If the value of the identifies an existing prepared statement that is a , then any prepared statements that reference that cursor are destroyed. Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall contain no . 2) The following restrictions apply for Entry SQL in addition to Intermediate SQL restrictions: None. Dynamic SQL 449 X3H2-92-154/DBL CBR-002 17.8 17.8 Function Obtain information about the columns for the prepared statement is stored in the specified SQL descriptor area as follows: a) Let N be the specified when the was allocated. Dynamic SQL 451 X3H2-92-154/DBL CBR-002 17.9 b) If the prepared statement that is being described is a or a , then let T be the table defined by the prepared statement and let D be the degree of T. Otherwise, let D be 0. c) COUNT is set to D. d) If D is greater than N, then a completion condition is raised: warning-insufficient item descriptor areas. e) If D is 0 or D is greater than N, then no item descriptor areas are set. Otherwise, the first D item descriptor areas are set so that the i-th item descriptor area contains the descriptor of the i-th column of T. The descriptor of a col- umn consists of values for TYPE, NULLABLE, NAME, UNNAMED, and other fields depending on the value of TYPE as described be- low. The DATA and INDICATOR fields are not relevant in this case. Those fields and fields that are not applicable for a particular value of TYPE are set to implementation-dependent values. i) TYPE is set to a code, as shown in Table 18, "Codes used for SQL data types in Dynamic SQL", indicating the data type of the column. ii) NULLABLE is set to 1 if the resulting column is possibly nullable and 0 otherwise. iii) If the column name is implementation-dependent, then NAME is set to the implementation-dependent name of the column, and UNNAMED is set to 1. Otherwise, NAME is set to the name for the column and UNNAMED is set to 0. iv) Case: 1) If TYPE indicates a , then: LENGTH is set to the length or maximum length in char- acters of the character string; OCTET_LENGTH is set to the maximum possible length in octets of the character string; CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA and CHARACTER_SET_NAME are set to the of the character string's character set; and COLLATION_ CATALOG, COLLATION_SCHEMA and COLLATION_NAME are set to the of the character string's colla- tion. If the subject specifies C, then the lengths specified in LENGTH and OCTET_LENGTH do not include the implementation-defined null character that terminates a C character string. 452 Database Language SQL X3H2-92-154/DBL CBR-002 17.9 2) If TYPE indicates a , then LENGTH is set to the length or maximum length in bits of the bit string and OCTET_LENGTH is set to the maximum possible length in octets of the bit string. 3) If TYPE indicates an , then PRECISION and SCALE are set to the precision and scale of the exact numeric. 4) If TYPE indicates an , then PRECISION is set to the precision of the approximate numeric. 5) If TYPE indicates a , then LENGTH is set to the length in positions of the datetime type, DATETIME_INTERVAL_CODE is set to a code as specified in Table 19, "Codes associated with datetime data types in Dynamic SQL", to indicate the specific datetime data type, and PRECISION is set to the
of a created or declared local temporary table and if the is not in the same as the that prepared the pre- pared statement, then an exception condition is raised: syntax rule or access rule violation in dynamic SQL statement. Dynamic SQL 459 X3H2-92-154/DBL CBR-002 17.10 4) If P contains s and a is not specified, then an exception condition is raised: dynamic SQL error- using clause required for dynamic parameters. 5) If P is a and a is not specified, then an exception condition is raised: dynamic SQL error-using clause required for result fields. 6) If a that is is specified, then the General Rules specified in Subclause 17.9, "", for a in an are applied. 7) If P is a , then the General Rules specified in Subclause 17.9, "", for a in an are applied. 8) P is executed. Case: a) If P is a , then all General Rules in Subclause 13.5, " of P. b) If the is a , then all General Rules in Subclause 17.19, "", apply to the . c) If the is a , then all General Rules in Subclause 17.20, "", apply to the . d) Otherwise, the results of the execution are the same as if the statement was contained in a and executed; these are described in Subclause 12.3, "". Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall contain no . 460 Database Language SQL X3H2-92-154/DBL CBR-002 17.10 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain any Dynamic SQL language. Dynamic SQL 461 X3H2-92-154/DBL CBR-002 17.11 17.11 Function Dynamically prepare and execute a preparable statement. Format ::= EXECUTE IMMEDIATE Syntax Rules 1) The data type of shall be character string. Access Rules None. General Rules 1) Let P be the contents of the . 2) If P is a or a , then P refers to either a dynamic cursor with the same or to an extended dynamic cursor whose value is the same as the . Case: a) If both an extended dynamic cursor and a dynamic cursor with the same name as the exist, then an exception condition is raised: ambiguous cursor name. b) If there is neither an extended dynamic cursor nor a dynamic cursor with the name of , then an exception condition is raised: invalid cursor name. 3) If one or more of the following are true, then an exception condition is raised: syntax error or access rule violation in dynamic SQL statement. a) P does not conform to the Format, Syntax Rules, and Access Rules for a or P is a or a . b) P contains a . c) P contains a . 4) The that is the value of the is prepared and executed. 462 Database Language SQL X3H2-92-154/DBL CBR-002 17.11 Case: a) If the is a , then all General Rules in Subclause 17.19, "", apply to the . b) If the is a , then all General Rules in Subclause 17.20, "", apply to the . c) Otherwise, the results of the execution are the same as if the statement was contained in a and executed; these are described in Subclause 12.3, "". Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain any Dynamic SQL language. Dynamic SQL 463 X3H2-92-154/DBL CBR-002 17.12 17.12 Function Declare a cursor to be associated with a , which may in turn be associated with a . Format ::= DECLARE [ INSENSITIVE ] [ SCROLL ] CURSOR FOR Syntax Rules 1) The shall not be identical to the specified in any other or in the same . 2) The containing shall contain a whose is the same as the of the . Access Rules None. General Rules 1) All General Rules of Subclause 13.1, "" apply to , replacing "" with "" and "" with "prepared statement". Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall contain no that specifies INSENSITIVE. b) If an of FOR UPDATE with or without a is specified, then neither SCROLL nor ORDER BY shall be specified. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain any Dynamic SQL language. 464 Database Language SQL X3H2-92-154/DBL CBR-002 17.13 17.13 Function Define a cursor based on a for a . Format ::= ALLOCATE [ INSENSITIVE ] [ SCROLL ] CURSOR FOR Syntax Rules None. Access Rules None. General Rules 1) When the is executed, if the value of the does not identify a statement previously prepared in the scope of the , then an exception condition is raised: invalid SQL state- ment name. 2) If the prepared statement associated with the is not a , then an exception condition is raised: dynamic SQL error-prepared statement not a cursor specification. 3) All General Rules of Subclause 13.1, "" apply to , replacing "" with "" and "" with "prepared statement". 4) Let S be the character string that is the value of the immediately contained in . Let V be the character string that is the result of TRIM ( BOTH ' ' FROM S ) If V does not conform to the Format and Syntax Rules of an , then an exception condition is raised: invalid cursor name. Dynamic SQL 465 X3H2-92-154/DBL CBR-002 17.13 5) If the value of the is identical to the value of the of any other cursor al- located in the scope of the , then an exception condition is raised: invalid cursor name. 6) An association is made between the value of the and the prepared statement in the scope of the . The association is preserved until the prepared statement is destroyed, at which time the cursor identified by is also destroyed. Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall not contain any . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. 466 Database Language SQL X3H2-92-154/DBL CBR-002 17.14 17.14 Function Associate input parameters with a and open the cursor. Format ::= OPEN [ ] Syntax Rules 1) If DCN is a CN, then the containing shall contain a whose is CN. 2) Let CR be the cursor identified by DCN. Access Rules 1) The Access Rules for the simply contained in the prepared statement associated with the are applied. General Rules 1) If is a and the of the associated is not associ- ated with a prepared statement, then an exception condition is raised: invalid SQL statement name. 2) If is an whose value does not identify a cursor allocated in the scope of the , then an exception condition is raised: invalid cursor name. 3) If the prepared statement associated with the contains s and a is not specified, then an exception condition is raised: dynamic SQL error-using clause required for dynamic parameters. 4) The cursor specified by is updatable if and only if the associated specified an updatable cursor. Note: updatable cursor is defined in Subclause 13.1, "". 5) If a is specified, then the General Rules spec- ified in Subclause 17.9, "", for are applied. Dynamic SQL 467 X3H2-92-154/DBL CBR-002 17.14 6) All General Rules of Subclause 13.2, "", apply to the . Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain any Dynamic SQL language. 468 Database Language SQL X3H2-92-154/DBL CBR-002 17.15 17.15 Function Fetch a row for a cursor declared with a . Format ::= FETCH [ [ ] FROM ] Syntax Rules 1) If is omitted, then NEXT is implicit. 2) The shall specify INTO. 3) If DCN is a CN, then the containing shall contain a whose is CN. 4) Let CR be the cursor identified by DCN and let T be the table defined by the of CR. 5) If the implicit or explicit is not NEXT, then the or associated with CR shall specify SCROLL. 6) The number of s in or the number of item descriptor areas in the SQL descriptor area referenced by , as appropriate, shall be the same as the degree of T. The i-th in or the i-th item descriptor area of the SQL de- scriptor area, as appropriate, corresponds with the i-th column of T. 7) The Syntax Rules of Subclause 9.1, "Retrieval assignment", apply to each corresponding in and each column of T as TARGET and VALUE, respectively. Access Rules None. General Rules 1) The General Rules specified in Subclause 17.9, "", for are applied. Dynamic SQL 469 X3H2-92-154/DBL CBR-002 17.15 2) All General Rules of Subclause 13.3, "", ap- ply to the , replacing "targets in the " and "targets identified by the " with "s in the or item descriptor areas of the SQL descriptor area, as appropriate". Leveling Rules 1) The following restrictions apply for Intermediate SQL: 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain any Dynamic SQL language. 470 Database Language SQL X3H2-92-154/DBL CBR-002 17.16 17.16 Function Close a cursor. Format ::= CLOSE Syntax Rules 1) If DCN is a CN, then the containing shall contain a whose is CN. 2) Let CR be the cursor identified by DCN. Access Rules None. General Rules 1) All General Rules of Subclause 13.4, "", apply to the . Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain any Dynamic SQL language. Dynamic SQL 471 X3H2-92-154/DBL CBR-002 17.17 17.17 Function Delete a row of a table. Format ::= DELETE FROM
WHERE CURRENT OF Syntax Rules 1) If DCN is a CN, then the containing shall contain a whose is CN. 2) Let CR be the cursor identified by DCN. 3) CR shall be an updatable cursor. Note: updatable cursor is defined in Subclause 13.1, "". 4) Let T be the table identified by the
. Let QS be the that is the simply underlying table of the simply underlying table of CR. The simply underlying table of QS shall be T. Note: The simply underlying table of a is defined in Subclause 13.1, "". Access Rules 1) All Access Rules of Subclause 13.6, "", apply to the . General Rules 1) All General Rules of Subclause 13.6, "", apply to the , replacing "" with "". Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 472 Database Language SQL X3H2-92-154/DBL CBR-002 17.17 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain any Dynamic SQL language. Dynamic SQL 473 X3H2-92-154/DBL CBR-002 17.18 17.18 Function Update a row of a table. Format ::= UPDATE
SET [ { }... ] WHERE CURRENT OF Syntax Rules 1) If DCN is a CN, then the containing shall contain a whose is CN. 2) Let CR be the cursor identified by DCN. 3) CR shall be an updatable cursor. Note: updatable cursor is defined in Subclause 13.1, "". 4) Let T be the table identified by the
. Let QS be the that is the simply underlying table of the simply underlying table of CR. The simply underlying table of QS shall be T. Note: The simply underlying table of a is defined in Subclause 13.1, "". 5) If CR is an ordered cursor, then for each OC, the column of T identified by OC shall not be directly or in- directly referenced in the of the defining for CR. 6) No leaf generally underlying table of T shall be an underly- ing table of any generally contained in any immediately contained in any contained in the . 7) A in a shall not directly con- tain a . 8) The same shall not appear more than once in a . 9) If CR was specified using an explicit or implicit of FOR UPDATE, then each specified as an shall identify a column in the explicit or implicit associated with the . 474 Database Language SQL X3H2-92-154/DBL CBR-002 17.18 10)The scope of the
is the entire . 11)For every , the Syntax Rules of Subclause 9.2, "Store assignment", apply to the column of T identified by the and the of the as TARGET and VALUE, respectively. Access Rules 1) All Access Rules of Subclause 13.9, "", apply to the . General Rules 1) All General Rules of Subclause 13.9, "", apply to the , replacing "" with "" and "" with "". Leveling Rules 1) The following restrictions apply for Intermediate SQL: None. 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain any Dynamic SQL language. Dynamic SQL 475 X3H2-92-154/DBL CBR-002 17.19 17.19 Function Delete a row of a table through a dynamic cursor. Format ::= DELETE [ FROM
] WHERE CURRENT OF Syntax Rules 1) If
is not specified, then the name of the under- lying table of the identified by is implicit. 2) All Syntax Rules of Subclause 13.6, "", apply to the , replacing "" with " or " and "" with "". Access Rules 1) All Access Rules of Subclause 13.6, "", apply to the . General Rules 1) All General Rules of Subclause 13.6, "", apply to the , replacing "" with "". Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall contain no . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. 476 Database Language SQL X3H2-92-154/DBL CBR-002 17.20 17.20 Function Update a row of a table through a dynamic cursor. Format ::= UPDATE [
] SET WHERE CURRENT OF Syntax Rules 1) If
is not specified, then the name of the under- lying table of the identified by is implicit. 2) All Syntax Rules of Subclause 13.9, "", apply to the , replacing "" with " or " and "" with "". Access Rules 1) All Access Rules of Subclause 13.9, "", apply to the . General Rules 1) All General Rules of Subclause 13.9, "", apply to the , replacing "" with "". Leveling Rules 1) The following restrictions apply for Intermediate SQL: a) Conforming Intermediate SQL language shall contain no . 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. Dynamic SQL 477 X3H2-92-154/DBL CBR-002 478 Database Language SQL X3H2-92-154/DBL CBR-002 18 Diagnostics management 18.1 Function Get exception or completion condition information from the diagnos- tics area. Format ::= GET DIAGNOSTICS ::= | ::= [ { }... ] ::= ::= NUMBER | MORE | COMMAND_FUNCTION | DYNAMIC_FUNCTION | ROW_COUNT ::= EXCEPTION [ { }... ] ::= ::= CONDITION_NUMBER | RETURNED_SQLSTATE | CLASS_ORIGIN | SUBCLASS_ORIGIN | SERVER_NAME Diagnostics management 479 X3H2-92-154/DBL CBR-002 18.1 | CONNECTION_NAME | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | CATALOG_NAME | SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | CURSOR_NAME | MESSAGE_TEXT | MESSAGE_LENGTH | MESSAGE_OCTET_LENGTH ::= Syntax Rules 1) The data type of a contained in a or shall be the data type specified in Table 21, "s for use with ", for the corresponding or . 2) The data type of shall be exact numeric with scale 0. 480 Database Language SQL X3H2-92-154/DBL CBR-002 18.1 __Table_21-s_for_use_with___ ____________Data_Type__________________________________ |_________________s_______________| | | | | NUMBER exact numeric with scale 0 | | | | MORE | character string (1) | | | | | COMMAND_FUNCTION | character varying (L) | | | | | DYNAMIC_FUNCTION | character varying (L) | | | | |_ROW_COUNT____________|_exact_numeric_with_scale_0________________| | | | |_________________s_______________| | | | | CONDITION_NUMBER exact numeric with scale 0 | | | | RETURNED_SQLSTATE | character string (5) | | | | | CLASS_ORIGIN | character varying (L) | | | | | SUBCLASS_ORIGIN | character varying (L) | | | | | SERVER_NAME | character varying (L) | | | | | CONNECTION_NAME | character varying (L) | | | | | CONSTRAINT_CATALOG | character varying (L) | | | | | CONSTRAINT_SCHEMA | character varying (L) | | | | | CONSTRAINT_NAME | character varying (L) | | | | | CATALOG_NAME | character varying (L) | | | | | SCHEMA_NAME | character varying (L) | | | | | TABLE_NAME | character varying (L) | | | | | COLUMN_NAME | character varying (L) | | | | | CURSOR_NAME | character varying (L) | | | | | MESSAGE_TEXT | character varying (L) | | | | | MESSAGE_LENGTH | exact numeric with scale 0 | | | | |_MESSAGE_OCTET_LENGTH_|_exact_numeric_with_scale_0________________| | | | | Where L is an impleme|tation-defined integer not less than 128. | |______________________|___________________________________________| | | Access Rules None. Diagnostics management 481 X3H2-92-154/DBL CBR-002 18.1 General Rules 1) Specification of retrieves informa- tion about the statement execution recorded in the diagnostics area into . a) The value of NUMBER is the number of exception or completion conditions that have been stored in the diagnostics area as a result of executing the previous SQL-statement other than a . Note: The itself may return infor- mation via the SQLCODE or SQLSTATE parameters, but does not modify the previous contents of the diagnostics area. b) The value of MORE is: Y More conditions were raised during execution of the SQL-statement than have been stored in the diagnostics area. N All of the conditions that were raised during execution of the SQL-statement have been stored in the diagnostics area. c) The value of COMMAND_FUNCTION is the identification of the SQL-statement executed. Table 22, "SQL-statement character codes for use in the diagnostics area" specifies the identi- fier of the SQL-statements. d) The value of DYNAMIC_FUNCTION is the identification of the prepared statement executed. Table 22, "SQL-statement char- acter codes for use in the diagnostics area", specifies the identifier of the SQL-statements. Table 22-SQL-statement character _______________codes_for_use_in_the_diagnostics_area_______________ _SQL-statement____________________Identifier_______________________ | | ALLOCATE CURSOR | | | | | | | ALTER DOMAIN | | | | | | ALTER TABLE | | | | | | CREATE ASSERTION | | | | | | CREATE CHARACTER SET | | | | | | CLOSE CURSOR | | | | | | CREATE COLLATION | | | | | | COMMIT WORK | | | | 482 Database Language SQL X3H2-92-154/DBL CBR-002 18.1 Table 22-SQL-statement character codes ______________for_use_in_the_diagnostics_area_(Cont.)______________ _SQL-statement____________________Identifier_______________________ | | CONNECT | | | | | | | | | | | | | DELETE CURSOR | | | | | | DELETE WHERE | | | | | | DESCRIBE | | | | | | | DISCONNECT | | | | | | CREATE DOMAIN | | | | | | DROP ASSERTION | | | | | | DROP CHARACTER SET | | | | | | DROP COLLATION | | | | | | DROP DOMAIN | | | | | | DROP SCHEMA | | | | | | DROP TABLE | | | | | | DROP TRANSLATION | | | | | | DROP VIEW | | | | | | DYNAMIC CLOSE | | | | | | | DYNAMIC FETCH | | | | | | DYNAMIC OPEN | | | | | | | | | | | | | EXECUTE IMMEDIATE | | | | | | EXECUTE | | | | | | FETCH | | | | | | GET DESCRIPTOR | | | | Diagnostics management 483 X3H2-92-154/DBL CBR-002 18.1 Table 22-SQL-statement character codes ______________for_use_in_the_diagnostics_area_(Cont.)______________ _SQL-statement____________________Identifier_______________________ | | GET DIAGNOSTICS | | | | | | GRANT | | | | | | INSERT | | | | | | OPEN | | | | | | | | | | | | | PREPARE | | | | | | REVOKE | | | | | | ROLLBACK WORK | | | | | | CREATE SCHEMA | | | | |
| CREATE TABLE | | | | | | CREATE TRANSLATION | | | | | | UPDATE CURSOR | | | | | | UPDATE WHERE | | | | |_______________|_CREATE_VIEW_____________________| | | | e) The value of ROW_COUNT is the number of rows affected as the result of executing a , , or as a direct re- sult of executing the previous SQL-statement. Let S be the , , or statement: searched>. Let T be the table identified by the
directly contained in S. Case: i) If S is an , then the value of ROW_COUNT is the number of rows inserted into T. ii) If S is not an and does not contain a , then the value of ROW_COUNT is the cardinality of T before the execution of S. iii) Otherwise, let SC be the directly con- tained in S. The value of ROW_COUNT is effectively derived by executing the statement: SELECT COUNT(*) FROM T WHERE SC before the execution of S. The value of ROW_COUNT following the execution of an SQL- statement that does not directly result in the execution of a , an , or an