BNF Grammar for ISO/IEC 9075-2:2003 - Database Language SQL (SQL-2003) SQL/Foundation ===================================================================================== @(#)$Id: sql-2003-2.bnf,v 1.23 2017/11/14 06:53:22 jleffler Exp $ --p Information taken from the Final Committee Draft (FCD) of ISO/IEC 9075-2:2003. However, the page numbers and some section titles (9.14 through 9.23, for example) are from the final standard. This means there could be other as yet undiagnosed differences between the final standard and the notation in this document; you were warned! --/p --p The plain text version of this grammar is --## sql-2003-2.bnf . --/p --hr --h2 Key SQL Statements and Fragments --/h2 --bl --li ALTER DOMAIN --li ALTER TABLE --li CLOSE cursor --li Column definition --li COMMIT WORK --li CONNECT --li CREATE ASSERTION --li CREATE CHARACTER SET --li CREATE COLLATION --li CREATE DOMAIN --li CREATE FUNCTION --li CREATE PROCEDURE --li CREATE SCHEMA --li CREATE TABLE --li CREATE TRANSLATION --li CREATE TRIGGER --li CREATE VIEW --li Data type --li DEALLOCATE PREPARE --li DECLARE cursor --li DECLARE LOCAL TEMPORARY TABLE --li DELETE --li DESCRIBE --li DESCRIPTOR statements --li DISCONNECT --li EXECUTE --li EXECUTE IMMEDIATE --li FETCH cursor --li FROM clause --li GET DIAGNOSTICS --li GRANT --li GROUP BY clause --li HAVING clause --li INSERT --li Literals --li Keywords --li MERGE --li OPEN cursor --li ORDER BY clause --li PREPARE --li REVOKE --li ROLLBACK WORK --li SAVEPOINT --li Search condition --li SELECT --li SET CATALOG --li SET CONNECTION --li SET CONSTRAINTS --li SET NAMES --li SET SCHEMA --li SET SESSION AUTHORIZATION --li SET TIME ZONE --li SET TRANSACTION --li SQL Client MODULE --li UPDATE --li Value expression --li WHERE clause --/bl --hr --h2 5 Lexical Elements --/h2 --p Basic definitions of characters used, tokens, symbols, etc. Most of this section would normally be handled within the lexical analyzer rather than in the grammar proper. Further, the original document does not quote the various single characters, which makes it hard to process automatically. --/p --h3 5.1 (p151) --/h3 ::= ::= | | ::= | ::= A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z ::= a | b | c | d | e | f | g | h | i | j | k | l | m | n | o | p | q | r | s | t | u | v | w | x | y | z ::= 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 ::= | | | | | | | | | | | | | | | | | | | | | | | | | ::= !! See the Syntax Rules. ::= " ::= % ::= & ::= ' ::= ( ::= ) ::= * ::= + ::= , ::= - ::= . ::= / ::= : ::= ; ::= < ::= = ::= > ::= ? --p --i The trigraphs are new in SQL-2003. --/i --/p ::= | ::= | ::= [ ::= ??( ::= ] ::= ??) ::= ^ ::= _ ::= | ::= { ::= } --hr --h3 5.2 and (p134) --/h3 --p Specifying lexical units (tokens and separators) that participate in SQL language. --/p ::= | ::= | | | | | | | ::= ::= [ ... ] ::= | --p --i --small The previous version of the SQL standard defined an identifier start as either an or an . Neither of the defining terms is defined in SQL 2003 (and the SQL 99 definitions of those defininng terms referred to the syntax rules), but the result of the SQL 2003 syntax rules will be similar to SQL 99 ones except with Unicode support added. --/small --/i --/p --@@ ::= | ::= !! See the Syntax Rules. ::= !! See the Syntax Rules. ::= ... ::= K | M | G ::= ::= ... ::= | --p --i --small The productions for and so on are new in SQL-2003. --/small --/i --/p ::= U ::= [ UESCAPE ] ::= ... ::= | ::= | | --p --i --small Syntax rule 20: '+xyzw' is equivalent to the Unicode code point specified by U+xyzw. --/small --/i --/p ::= --p --i --small Syntax rule 21: '+xyzwrs' is equivalent to the Unicode code point specified by U+xyzwrs. --/small --/i --/p --p --i --small NOTE 64: The 6-hexit notation is derived by taking the UCS-4 notation defined by ISO/IEC 10646-1 and removing the leading two hexits, whose values are always 0 (zero). --/small --/i --/p ::= --p --i --small Syntax rule 22: is equivalent to a single instance of . --/small --/i --/p ::= --p --i --small Syntax rule 15: shall be a single character from the source language character set other than a , , or . --/small --/i --/p --p --i --small Syntax rule 16: If the source language character set contains , then let DEC be ; otherwise, let DEC be an implementation-defined character from the source language character set that is not a , , , or . --/small --/i --/p --p --i --small Syntax rule 17: If a does not contain , then "UESCAPE DEC" is implicit. --/small --/i --/p --p --i --small Syntax rule 18: In a there shall be no between the and the first , nor between any of the s. --/small --/i --/p ::= !! See the Syntax Rules (15-18 above). --p --i --small Syntax rule 6: A is any character of the source language character set other than a . --/small --/i --/p ::= !! See the Syntax Rules. --p --i --small The rule for in the standard uses two adjacent literal double quotes rather than referencing ; the reasons are not clear. It is annotated '!! two consecutive double quote characters'. --/small --/i --/p ::= ::= | |
::= ::= ::= [ ] ::= ::= [ ] ::= [ ] ::= | MODULE ::= ::= ::= ::= ::= ::= ::= ::= ::= ::= ::= [ ] ::= MODULE ::= ::= ::= ::= | ::= ::= ::= [ ] ::= ::= ::= ::= ::= [ ] ::= ::= ::= ::= ::= ::= ::= ::= ::= ::= | ::= ::= [ ] ::= | ::= [ ] ::= [ ] ::= GLOBAL | LOCAL ::= --hr --h2 6 Scalar expressions --/h2 --h3 6.1 (p161) --/h3 ::= | | | | ::= [ CHARACTER SET ] [ ] | [ ] | | | | | ::= CHARACTER [ ] | CHAR [ ] | CHARACTER VARYING | CHAR VARYING | VARCHAR | CHARACTER LARGE OBJECT [ ] | CHAR LARGE OBJECT [ ] | CLOB [ ] ::= NATIONAL CHARACTER [ ] | NATIONAL CHAR [ ] | NCHAR [ ] | NATIONAL CHARACTER VARYING | NATIONAL CHAR VARYING | NCHAR VARYING | NATIONAL CHARACTER LARGE OBJECT [ ] | NCHAR LARGE OBJECT [ ] | NCLOB [ ] ::= BINARY LARGE OBJECT [ ] | BLOB [ ] ::= | ::= NUMERIC [ [ ] ] | DECIMAL [ [ ] ] | DEC [ [ ] ] | SMALLINT | INTEGER | INT | BIGINT ::= FLOAT [ ] | REAL | DOUBLE PRECISION ::= ::= [ ] [ ] | [ ] ::= CHARACTERS | CODE_UNITS | OCTETS ::= ::= ::= BOOLEAN ::= DATE | TIME [
::= ::= ::= | ::= ARRAY [ ] ::= MULTISET --hr --h3 6.2 (p173) --/h3 ::= [ ] --hr --h3 6.3 (p174) --/h3 ::= | ::= ::= | | | | | | | | | | | | | | | | | | --hr --h3 6.4 and (p176) --/h3 ::= | ::= | ::= | | | | | CURRENT_DEFAULT_TRANSFORM_GROUP | CURRENT_PATH | CURRENT_ROLE | CURRENT_TRANSFORM_GROUP_FOR_TYPE | CURRENT_USER | SESSION_USER | SYSTEM_USER | USER | VALUE ::= | | | ::= | | | | | ::= | | | ::= [ ] ::= ::= [ ] ::= [ INDICATOR ] ::= [ INDICATOR ] ::= ::= | ::= CURRENT_COLLATION --hr --h3 6.5 (p181) --/h3 ::= | ::= | ::= NULL ::= ARRAY | MULTISET ::= DEFAULT --hr --h3 6.6 (p183) --/h3 ::= [ { }... ] ::= --hr --h3 6.7 (p187) --/h3 ::= | MODULE --hr --h3 6.8 (p190) --/h3 ::= --hr --h3 6.9 (p191) --/h3 ::= | ::= GROUPING [ { }... ] --hr --h3 6.10 (p193) --/h3 ::= OVER ::= | ROW_NUMBER | ::= RANK | DENSE_RANK | PERCENT_RANK | CUME_DIST ::= | ::= --hr --h3 6.11 (p197) --/h3 ::= | ::= NULLIF | COALESCE { }... ::= | ::= CASE ... [ ] END ::= CASE ... [ ] END ::= WHEN THEN ::= WHEN THEN ::= ELSE ::= | ::= | | | | | | | | | | | | | | | ::= | NULL ::= --hr --h3 6.12 (p200) --/h3 ::= CAST AS ::= | ::= | --hr --h3 6.13 (p216) --/h3 ::= NEXT VALUE FOR --hr --h3 6.14 (p218) --/h3 ::= --hr --h3 6.15 (p219) --/h3 ::= TREAT AS ::= ::= | --hr --h3 6.16 (p221) --/h3 ::= | ::= [ ] ::= AS [ ] ::= ::= --hr --h3 6.17 (p223) --/h3 ::= [ ] ::= --hr --h3 6.18 (p225) --/h3 ::= NEW ::= | --hr --h3 6.19 (p227) --/h3 ::= [ ] ::= --hr --h3 6.20 (p229) --/h3 ::= --hr --h3 6.21 (p230) --/h3 ::= --hr --h3 6.22 (p232) --/h3 ::= DEREF --hr --h3 6.23 (p234) --/h3 ::= --hr --h3 6.24 (p235) --/h3 ::= ELEMENT --h3 6.25 (p236) --/h3 --p Specify a value. --/p ::= | | ::= | | | | | | ::= ::= ::= | ::= | --h3 6.26 (p240) --/h3 --p Specify a numeric value. --/p ::= | | ::= | | ::= [ ] ::= | --h3 6.27 (p242) --/h3 --p Specify a function yielding a value of type numeric. --/p ::= | | | | | | | | | | | | ::= | ::= POSITION IN [ USING ] ::= POSITION IN ::= | ::= { CHAR_LENGTH | CHARACTER_LENGTH } [ USING ] ::= OCTET_LENGTH ::= EXTRACT FROM ::= |
::= MULTISET ::= [ { } ] ::= ::= MULTISET
::= TABLE --hr --h2 7 Query expressions --/h2 --h3 7.1 (p293) --/h3 --p Specify a value or list of values to be constructed into a row or partial row. --/p ::= | | ::= | ROW | ::= [ { }... ] ::= ::= | | | | ROW ::= [ { }... ] ::= | ::= | | --h3 7.2 (p296) --/h3 --p Specify a row value. --/p ::= |
::= | ::= | ::= | ::= --h3 7.3
(p298) --/h3 --p Specify a set of s to be constructed into a table. --/p
::= VALUES ::=
[ {
}... ] ::= VALUES ::= [ { }... ] --h3 7.4
(p300) --/h3 --p Specify a table or a grouped table. --/p
::= [ ] [ ] [ ] [ ] --h3 7.5 (p301) --/h3 --p Specify a table derived from one or more tables. --/p ::= FROM
::=
[ {
}... ] --h3 7.6
(p303) --/h3 --p Reference a table. --/p
::=
[ ]
::=
| ::= TABLESAMPLE [ ] ::= BERNOULLI | SYSTEM ::= REPEATABLE ::= ::=
::=
[ [ AS ] [ ] ] | [ AS ] [ ] | [ AS ] [ ] | [ AS ] [ ] |
[ AS ] [ ] | [ [ AS ] [ ] ] | ::= ONLY
::= LATERAL
::= UNNEST [ WITH ORDINALITY ]
::= TABLE ::=
::=
| ::= ::= [ { }... ] --h3 7.7 (p312) --/h3 --p Specify a table derived from a Cartesian product, inner or outer join, or union join. --/p ::= | | | ::=
CROSS JOIN
::=
[ ] JOIN
::=
NATURAL [ ] JOIN
::=
UNION JOIN
::= | ::= ON ::= USING ::= INNER | [ OUTER ] ::= LEFT | RIGHT | FULL ::= --h3 7.8 (p319) --/h3 --p Specify a table derived by the application of a to the result of the preceding . --/p ::= WHERE --h3 7.9 (p320) --/h3 --p Specify a grouped table derived by the application of the to the result of the previously specified clause. --/p ::= GROUP BY [ ] ::= [ { }... ] ::= | | | | ::= | ::= [ ] ::= [ { }... ] ::= ROLLUP ::= [ { }... ] ::= CUBE ::= GROUPING SETS ::= [ { }... ] ::= | | | | ::= --h3 7.10 (p329) --/h3 --p Specify a grouped table derived by the elimination of groups that do not satisfy a . --/p ::= HAVING --h3 7.11 (p331) --/h3 --p Specify one or more window definitions. --/p ::= WINDOW ::= [ { }... ] ::= AS ::= ::= ::= [ ] [ ] [ ] [ ] ::= ::= PARTITION BY ::= [ { }... ] ::= [ ] ::= ORDER BY ::= [ ] ::= ROWS | RANGE ::= | ::= UNBOUNDED PRECEDING | | CURRENT ROW ::= PRECEDING ::= BETWEEN AND ::= ::= ::= | UNBOUNDED FOLLOWING | ::= FOLLOWING ::= EXCLUDE CURRENT ROW | EXCLUDE GROUP | EXCLUDE TIES | EXCLUDE NO OTHERS --h3 7.12 (p341) --/h3 --p Specify a table derived from the result of a
. --/p ::= SELECT [ ]
[ { ::= | ::= | ::= [ { }... ] ::= ::= [ ] ::= [ AS ] ::= [ AS ] ::= --h3 7.13 (p350) --/h3 --p Specify a table. --/p ::= [ ] ::= WITH [ RECURSIVE ] ::= [ { }... ] ::= [ ] AS [ ] ::= ::= | ::= | UNION [ ALL | DISTINCT ] [ ] | EXCEPT [ ALL | DISTINCT ] [ ] ::= | ::= | INTERSECT [ ALL | DISTINCT ] [ ] ::= | ::= | ::= |
| ::= TABLE
::= CORRESPONDING [ BY ] ::= --h3 7.14 (p363) --/h3 --p Specify the generation of ordering and cycle detection information in the result of recursive query expressions. --/p ::= | | ::= SEARCH SET ::= DEPTH FIRST BY | BREADTH FIRST BY ::= ::= CYCLE SET TO DEFAULT USING ::= [ { }... ] ::= ::= ::= ::= ::= --h3 7.15 (p368) --/h3 --p Specify a scalar value, a row, or a table derived from a . --/p ::= ::=
::= ::= --hr --h2 8 Predicates --/h2 --h3 8.1 (p371) --/h3 --p Specify a condition that can be evaluated to give a boolean value. --/p ::= | | | | | | | | | | | | | | | | --h3 8.2 (p373) --/h3 --p Specify a comparison of two row values. --/p ::= ::= ::= | | | | | --h3 8.3 (p380) --/h3 --p Specify a range comparison. --/p ::= ::= [ NOT ] BETWEEN [ ASYMMETRIC | SYMMETRIC ] AND --h3 8.4 (p381) --/h3 --p Specify a quantified comparison. --/p ::= ::= [ NOT ] IN ::=
| ::= [ { }... ] --h3 8.5 (p383) --/h3 --p Specify a pattern-match comparison. --/p ::= | ::= ::= [ NOT ] LIKE [ ESCAPE ] ::= ::= ::= ::= [ NOT ] LIKE [ ESCAPE ] ::= ::= --h3 8.6 (p389) --/h3 --p Specify a character string similarity by means of a regular expression. --/p ::= ::= [ NOT ] SIMILAR TO [ ESCAPE ] ::= ::= | ::= | ::= | | | | ::= [ ] ::= [ ] ::= ::= ::= | | | ::= | ::= !! See the Syntax Rules. ::= !! See the Syntax Rules. ::= | ... | ... | ... ... ::= ::= ::= | | ::= --h3 8.7 (p395) --/h3 --p Specify a test for a null value. --/p ::= ::= IS [ NOT ] NULL --h3 8.8 (p397) --/h3 --p Specify a quantified comparison. --/p ::= ::=
::= | ::= ALL ::= SOME | ANY --h3 8.9 (p399) --/h3 --p Specify a test for a non-empty set. --/p ::= EXISTS
--h3 8.10 (p400) --/h3 --p Specify a test for the absence of duplicate rows --/p ::= UNIQUE
--h3 8.11 (p401) --/h3 --p Determine whether a character string value is normalized. --/p ::= IS [ NOT ] NORMALIZED --h3 8.12 (p402) --/h3 --p Specify a test for matching rows. --/p ::= ::= MATCH [ UNIQUE ] [ SIMPLE | PARTIAL | FULL ]
--h3 8.13 (p405) --/h3 --p Specify a test for an overlap between two datetime periods. --/p ::= ::= ::= OVERLAPS ::= ::= --h3 8.14 (p407) --/h3 --p Specify a test of whether two row values are distinct --/p ::= ::= IS DISTINCT FROM ::= ::= --h3 8.15 (p409) --/h3 --p Specify a test of whether a value is a member of a multiset. --/p ::= ::= [ NOT ] MEMBER [ OF ] --h3 8.16 (p411) --/h3 --p Specify a test of whether a multiset is a submultiset of another multiset. --/p ::= ::= [ NOT ] SUBMULTISET [ OF ] --h3 8.17 (p413) --/h3 --p Specify a test of whether a multiset is a set (that is, does not contain any duplicates). --/p ::= ::= IS [ NOT ] A SET --h3 8.18 (p414) --/h3 --p Specify a type test. --/p ::= ::= IS [ NOT ] OF ::= [ { }... ] ::= | ::= ::= ONLY --h3 8.19 (p416) --/h3 --p Specify a condition that is True , False , or Unknown , depending on the value of a . --/p ::= --hr --h2 9 Additional common rules --/h2 --h3 9.1 Retrieval assignment (p417) --/h3 --h3 9.2 Store assignment (p422) --/h3 --h3 9.3 Data types of results of aggregations (p427) --/h3 --h3 9.4 Subject routine determination (p430) --/h3 --h3 9.5 Type precedence list determination (p431) --/h3 --h3 9.6 Host parameter mode determination (p434) --/h3 --h3 9.7 Type name determination (p436) --/h3 --h3 9.8 Determination of identical values (p438) --/h3 --h3 9.9 Equality operations (p440) --/h3 --h3 9.10 Grouping operations (p443) --/h3 --h3 9.11 Multiset element grouping operations (p445) --/h3 --h3 9.12 Ordering operations (p447) --/h3 --h3 9.13 Collation determination (p449) --/h3 --h3 9.14 Execution of array-returning functions (p450) --/h3 --h3 9.15 Execution of multiset-returning functions (p453) --/h3 --h3 9.16 Data type identity (p454) --/h3 --h3 9.17 Determination of a from-sql function (p456) --/h3 --h3 9.18 Determination of a from-sql function for an overriding method (p457) --/h3 --h3 9.19 Determination of a to-sql function (p458) --/h3 --h3 9.20 Determination of a to-sql function for an overriding method (p459) --/h3 --h3 9.21 Generation of the next value of a sequence generator (p460) --/h3 --h3 9.22 Creation of a sequence generator (p461) --/h3 --h3 9.23 Altering a sequence generator (p463) --/h3 --hr --h2 10 Additional common elements --/h2 --h3 10.1 (p465) --/h3 --p Specify the precision of an interval data type. --/p ::= TO | ::= [ ] ::= | SECOND [ ] ::= [ ] | SECOND [ [ ] ] ::= | SECOND ::= YEAR | MONTH | DAY | HOUR | MINUTE ::= ::= --h3 10.2 (p469) --/h3 --p Specify a standard programming language. --/p ::= LANGUAGE ::= ADA | C | COBOL | FORTRAN | MUMPS | PASCAL | PLI | SQL --p Table 14 -- Standard programming languages --/p --##
--## --## --## --## --## --## --## --## --## --##
Language keyword Relevant standard
ADAISO/IEC 8652
CISO/IEC 9899
COBOLISO 1989
FORTRANISO 1539
MUMPSISO/IEC 11756
PASCALISO/IEC 7185 and ISO/IEC 10206
PLIISO 6160
SQLISO/IEC 9075
--h3 10.3 (p471) --/h3 --p Specify an order for searching for an SQL-invoked routine. --/p ::= PATH ::= [ { }... ] --h3 10.4 (p472) --/h3 --p Invoke an SQL-invoked routine. --/p ::= ::= [ ] ::= [ [ { }... ] ] ::= | | ::= AS --h3 10.5 (p495) --/h3 --p Identify a character set. --/p ::= | | ::= ::= ::= --h3 10.6 (p497) --/h3 --p Specify an SQL-invoked routine. --/p ::= SPECIFIC | [ FOR ] ::= ROUTINE | FUNCTION | PROCEDURE | [ INSTANCE | STATIC | CONSTRUCTOR ] METHOD ::= [ ] ::= | ::= [ [ { }... ] ] --h3 10.7 (p500) --/h3 --p Specify a default collating sequence. --/p ::= COLLATE --h3 10.8 and (p501) --/h3 --p Specify the name of a constraint and its characteristics. --/p ::= CONSTRAINT ::= [ [ NOT ] DEFERRABLE ] | [ NOT ] DEFERRABLE [ ] ::= INITIALLY DEFERRED | INITIALLY IMMEDIATE --h3 10.9 (p503) --/h3 --p Specify a value computed from a collection of rows. --/p ::= COUNT [ ] | [ ] | [ ] | [ ] ::= [ ] ::= ::= AVG | MAX | MIN | SUM | EVERY | ANY | SOME | COUNT | STDDEV_POP | STDDEV_SAMP | VAR_SAMP | VAR_POP | COLLECT | FUSION | INTERSECTION ::= DISTINCT | ALL ::= FILTER WHERE ::= ::= COVAR_POP | COVAR_SAMP | CORR | REGR_SLOPE | REGR_INTERCEPT | REGR_COUNT | REGR_R2 | REGR_AVGX | REGR_AVGY | REGR_SXX | REGR_SYY | REGR_SXY ::= ::= ::= | ::= ::= WITHIN GROUP ORDER BY ::= [ { }... ] ::= ::= ::= PERCENTILE_CONT | PERCENTILE_DISC --h3 10.10 (p515) --/h3 --p Specify a sort order. --/p ::= [ { }... ] ::= [ ] [ ] ::= ::= ASC | DESC ::= NULLS FIRST | NULLS LAST --hr --h2 11 Schema definition and manipulation --/h2 --h3 11.1 (p517) --/h3 --p Define a schema. --/p ::= CREATE SCHEMA [ ] [ ... ] ::= | | | ::= | AUTHORIZATION | AUTHORIZATION ::= ::= DEFAULT CHARACTER SET ::= ::= | | | | | | | | | | | | | | | --h3 11.2 (p520) --/h3 --p Destroy a schema. --/p ::= DROP SCHEMA ::= CASCADE | RESTRICT --h3 11.3
(p523) --/h3 --p Define a persistent base table, a created local temporary table, or a global temporary table. --/p
::= CREATE [
] TABLE
[ ON COMMIT
ROWS ]
::=
| OF [ ] [
] |
::= TEMPORARY ::= GLOBAL | LOCAL
::= PRESERVE | DELETE
::=
[ {
}... ]
::= |
| | | ::= REF IS ::= SYSTEM GENERATED | USER GENERATED | DERIVED ::= ::= WITH OPTIONS ::= [ ] [ ] [ ... ] ::= UNDER ::= ::=
::= LIKE
[ ] ::= | ::= INCLUDING IDENTITY | EXCLUDING IDENTITY ::= INCLUDING DEFAULTS | EXCLUDING DEFAULTS ::= [ ] AS ::= WITH NO DATA | WITH DATA --h3 11.4 (p534) --/h3 --p Define a column of a base table. --/p ::= [ | ] [ ] [ | | ] [ ... ] [ ] ::= [ ] [ ] ::= NOT NULL | | | ::= REFERENCES ARE [ NOT ] CHECKED [ ON DELETE ] ::= ::= GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ] ::= AS ::= GENERATED ALWAYS ::= --h3 11.5 (p539) --/h3 --p Specify the default for a column, domain, or attribute. --/p ::= DEFAULT ::= | | USER | CURRENT_USER | CURRENT_ROLE | SESSION_USER | SYSTEM_USER | CURRENT_PATH | --h3 11.6
(p543) --/h3 --p Specify an integrity constraint. --/p
::= [ ]
[ ]
::= | | --h3 11.7 (p545) --/h3 --p Specify a uniqueness constraint for a table. --/p ::= | UNIQUE ( VALUE ) ::= UNIQUE | PRIMARY KEY ::= --h3 11.8 (p547) --/h3 --p Specify a referential constraint. --/p ::= FOREIGN KEY ::= REFERENCES [ MATCH ] [ ] ::= FULL | PARTIAL | SIMPLE ::= ::=
[ ] ::= ::= [ ] | [ ] ::= ON UPDATE ::= ON DELETE ::= CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION --h3 11.9 (p567) --/h3 --p Specify a condition for the SQL-data. --/p ::= CHECK --h3 11.10 (p569) --/h3 --p Change the definition of a table. --/p ::= ALTER TABLE
::= | | | | --h3 11.11 (p570) --/h3 --p Add a column to a table. --/p ::= ADD [ COLUMN ] --h3 11.12 (p572) --/h3 --p Change a column and its definition. --/p ::= ALTER [ COLUMN ] ::= | | | | --h3 11.13 (p573) --/h3 --p Set the default clause for a column. --/p ::= SET --h3 11.14 (p574) --/h3 --p Drop the default clause from a column. --/p ::= DROP DEFAULT --h3 11.15 (p575) --/h3 --p Add a non-empty scope for an existing column of data type REF in a base table. --/p ::= ADD --h3 11.16 (p576) --/h3 --p Drop the scope from an existing column of data type REF in a base table. --/p ::= DROP SCOPE --h3 11.17 (p578) --/h3 --p Change the options specified for an identity column. --/p ::= ... ::= | SET --h3 11.18 (p579) --/h3 --p Destroy a column of a base table. --/p ::= DROP [ COLUMN ] --h3 11.19 (p581) --/h3 --p Add a constraint to a table. --/p ::= ADD
--h3 11.20 (p582) --/h3 --p Destroy a constraint on a table. --/p ::= DROP CONSTRAINT --h3 11.21 (p585) --/h3 --p Destroy a table. --/p ::= DROP TABLE
--h3 11.22 (p588) --/h3 --p Define a viewed table. --/p ::= CREATE [ RECURSIVE ] VIEW
AS [ WITH [ ] CHECK OPTION ] ::= | ::= [ ] ::= OF [ ] [ ] ::= UNDER
::= [ { }... ] ::= | ::= WITH OPTIONS ::= CASCADED | LOCAL ::= --h3 11.23 (p598) --/h3 --p Destroy a view. --/p ::= DROP VIEW
--h3 11.24 (p601) --/h3 --p Define a domain. --/p ::= CREATE DOMAIN [ AS ] [ ] [ ... ] [ ] ::= [ ] [ ] --h3 11.25 (p603) --/h3 --p Change a domain and its definition. --/p ::= ALTER DOMAIN ::= | | | --h3 11.26 (p604) --/h3 --p Set the default value in a domain. --/p ::= SET --h3 11.27 (p605) --/h3 --p Remove the default clause of a domain. --/p ::= DROP DEFAULT --h3 11.28 (p606) --/h3 --p Add a constraint to a domain. --/p ::= ADD --h3 11.29 (p607) --/h3 --p Destroy a constraint on a domain. --/p ::= DROP CONSTRAINT --h3 11.30 (p608) --/h3 --p Destroy a domain. --/p ::= DROP DOMAIN --h3 11.31 (p610) --/h3 --p Define a character set. --/p ::= CREATE CHARACTER SET [ AS ] [ ] ::= GET --h3 11.32 (p612) --/h3 --p Destroy a character set. --/p ::= DROP CHARACTER SET --h3 11.33 (p614) --/h3 --p Define a collating sequence. --/p ::= CREATE COLLATION FOR FROM [ ] ::= ::= NO PAD | PAD SPACE --h3 11.34 (p616) --/h3 --p Destroy a collating sequence. --/p ::= DROP COLLATION --h3 11.35 (p618) --/h3 --p Define a character transliteration. --/p ::= CREATE TRANSLATION FOR TO FROM ::= ::= ::= | ::= ::= --h3 11.36 (p621) --/h3 --p Destroy a character transliteration. --/p ::= DROP TRANSLATION --h3 11.37 (p623) --/h3 --p Specify an integrity constraint. --/p ::= CREATE ASSERTION CHECK [ ] --h3 11.38 (p625) --/h3 --p Destroy an assertion. --/p ::= DROP ASSERTION --h3 11.39 (p627) --/h3 --p Define triggered SQL-statements. --/p ::= CREATE TRIGGER ON
[ REFERENCING ] ::= BEFORE | AFTER ::= INSERT | DELETE | UPDATE [ OF ] ::= ::= [ FOR EACH { ROW | STATEMENT } ] [ WHEN ] ::= | BEGIN ATOMIC { }... END ::= ... ::= OLD [ ROW ] [ AS ] | NEW [ ROW ] [ AS ] | OLD TABLE [ AS ] | NEW TABLE [ AS ] ::= ::= ::= ::= --h3 11.40 (p631) --/h3 --p Destroy a trigger. --/p ::= DROP TRIGGER --h3 11.41 (p632) --/h3 --p Define a user-defined type. --/p ::= CREATE TYPE ::= [ ] [ AS ] [ ] [ ] ::= [ ... ] ::= | | | | ::= UNDER ::= ::= | ::= [ { }... ] ::= ::= INSTANTIABLE | NOT INSTANTIABLE ::= FINAL | NOT FINAL ::= | | ::= REF USING ::= REF FROM ::= REF IS SYSTEM GENERATED ::= [ ] [ ] ::= CAST SOURCE AS REF WITH ::= ::= CAST REF AS SOURCE WITH ::= ::= [ { }...] ::= [ ] [ ] ::= CAST SOURCE AS DISTINCT WITH ::= ::= CAST DISTINCT AS SOURCE WITH ::= ::= [ { }... ] ::= | ::= [ SELF AS RESULT ] [ SELF AS LOCATOR ] [ ] ::= OVERRIDING ::= [ INSTANCE | STATIC | CONSTRUCTOR ] METHOD [ SPECIFIC ] ::= [ ] ::= ... ::= | | | | --h3 11.42 (p648) --/h3 --p Define an attribute of a structured type. --/p ::= [ ] [ ] [ ] ::= --h3 11.43 (p650) --/h3 --p Change the definition of a user-defined type. --/p ::= ALTER TYPE ::= | | | | --h3 11.44 (p651) --/h3 --p Add an attribute to a user-defined type. --/p ::= ADD ATTRIBUTE --h3 11.45 (p653) --/h3 --p Destroy an attribute of a user-defined type. --/p ::= DROP ATTRIBUTE RESTRICT --h3 11.46 (p655) --/h3 --p Add an original method specification to a user-defined type. --/p ::= ADD --h3 11.47 (p661) --/h3 --p Add an overriding method specification to a user-defined type. --/p ::= ADD --h3 11.48 (p666) --/h3 --p Remove a method specification from a user-defined type. --/p ::= DROP RESTRICT ::= [ INSTANCE | STATIC | CONSTRUCTOR ] METHOD --h3 11.49 (p670) --/h3 --p Destroy a user-defined type. --/p ::= DROP TYPE --h3 11.50 (p673) --/h3 --p Define an SQL-invoked routine. --/p ::= ::= | ::= CREATE ::= CREATE ::= PROCEDURE ::= { | } ::= [ [ { }... ] ] ::= [ ] [ ] [ RESULT ] ::= IN | OUT | INOUT ::= [ ] ::= AS LOCATOR ::= FUNCTION [ ] ::= SPECIFIC METHOD | [ INSTANCE | STATIC | CONSTRUCTOR ] METHOD [ ] FOR ::= [ ... ] ::= | | SPECIFIC | | | | | ::= NEW SAVEPOINT LEVEL | OLD SAVEPOINT LEVEL ::= DYNAMIC RESULT SETS ::= PARAMETER STYLE ::= STATIC DISPATCH ::= RETURNS ::= [ ] | ::= TABLE
::=
[ {
}... ]
::= ::= CAST FROM ::= [ ] ::= [ ] ::= | ::= [ ] ::= SQL SECURITY INVOKER | SQL SECURITY DEFINER ::= ::= EXTERNAL [ NAME ] [ ] [ ] [ ] ::= EXTERNAL SECURITY DEFINER | EXTERNAL SECURITY INVOKER | EXTERNAL SECURITY IMPLEMENTATION DEFINED ::= SQL | GENERAL ::= DETERMINISTIC | NOT DETERMINISTIC ::= NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA ::= RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ::= ::= TRANSFORM GROUP { | } ::= ::= [ { }... ] ::= FOR TYPE --h3 11.51 (p698) --/h3 --p Alter a characteristic of an SQL-invoked routine. --/p ::= ALTER ::= ... ::= | | | | | NAME ::= RESTRICT --h3 11.52 (p701) --/h3 --p Destroy an SQL-invoked routine. --/p ::= DROP --h3 11.53 (p703) --/h3 --p Define a user-defined cast. --/p ::= CREATE CAST AS WITH [ AS ASSIGNMENT ] ::= ::= ::= --h3 11.54 (p705) --/h3 --p Destroy a user-defined cast. --/p ::= DROP CAST AS --h3 11.55 (p707) --/h3 --p Define a user-defined ordering for a user-defined type. --/p ::= CREATE ORDERING FOR ::= | ::= EQUALS ONLY BY ::= ORDER FULL BY ::= | | ::= RELATIVE WITH ::= MAP WITH ::= STATE [ ] ::= ::= --h3 11.56 (p710) --/h3 --p Destroy a user-defined ordering method. --/p ::= DROP ORDERING FOR --h3 11.57 (p712) --/h3 --p Define one or more transform functions for a user-defined type. --/p ::= CREATE { TRANSFORM | TRANSFORMS } FOR ... ::= ::= ::= [ ] ::= | ::= TO SQL WITH ::= FROM SQL WITH ::= ::= --h3 11.58 (p715) --/h3 --p Change the definition of one or more transform groups. --/p ::= ALTER { TRANSFORM | TRANSFORMS } FOR ... ::= ::= [ { }... ] ::= | --h3 11.59 (p717) --/h3 --p Add a transform element ( and/or ) to an existing transform group. --/p ::= ADD --h3 11.60 (p719) --/h3 --p Remove a transform element ( and/or ) from a transform group. --/p ::= DROP [ ] ::= TO SQL | FROM SQL --h3 11.61 (p721) --/h3 --p Remove one or more transform functions associated with a transform. --/p ::= DROP { TRANSFORM | TRANSFORMS } FOR ::= ALL | ::= --h3 11.62 (p724) --/h3 --p Define an external sequence generator. --/p ::= CREATE SEQUENCE [ ] ::= ... ::= | ::= ... ::= | ::= | | | ::= AS ::= START WITH ::= ::= INCREMENT BY ::= ::= MAXVALUE | NO MAXVALUE ::= ::= MINVALUE | NO MINVALUE ::= ::= CYCLE | NO CYCLE --h3 11.63 (p726) --/h3 --p Change the definition of an external sequence generator. --/p ::= ALTER SEQUENCE ::= ... ::= | ::= RESTART WITH ::= --h3 11.64 (p727) --/h3 --p Destroy an external sequence generator. --/p ::= DROP SEQUENCE --hr --h2 12 Access control --/h2 --h3 12.1 (p729) --/h3 --p Define privileges and role authorizations. --/p ::= | --h3 12.2 (p734) --/h3 --p Define privileges. --/p ::= GRANT TO [ { }... ] [ WITH HIERARCHY OPTION ] [ WITH GRANT OPTION ] [ GRANTED BY ] --h3 12.3 (p737) --/h3 --p Specify privileges. --/p ::= ON ::= [ TABLE ]
| DOMAIN | COLLATION | CHARACTER SET | TRANSLATION | TYPE | SEQUENCE | ::= ALL PRIVILEGES | [ { }... ] ::= SELECT | SELECT | SELECT | DELETE | INSERT [ ] | UPDATE [ ] | REFERENCES [ ] | USAGE | TRIGGER | UNDER | EXECUTE ::= [ { }... ] ::= ::= PUBLIC | ::= CURRENT_USER | CURRENT_ROLE --h3 12.4 (p741) --/h3 --p Define a role. --/p ::= CREATE ROLE [ WITH ADMIN ] --h3 12.5 (p742) --/h3 --p Define role authorizations. --/p ::= GRANT [ { }... ] TO [ { }... ] [ WITH ADMIN OPTION ] [ GRANTED BY ] ::= --h3 12.6 (p744) --/h3 --p Destroy a role. --/p ::= DROP ROLE --h3 12.7 (p745) --/h3 --p Destroy privileges and role authorizations. --/p ::= | ::= REVOKE [ ] FROM [ { }... ] [ GRANTED BY ] ::= GRANT OPTION FOR | HIERARCHY OPTION FOR ::= REVOKE [ ADMIN OPTION FOR ] [ { }... ] FROM [ { }... ] [ GRANTED BY ] ::= --hr --h2 13 SQL-client modules --/h2 --h3 13.1 (p763) --/h3 --p Define an SQL-client module. --/p ::= [ ] [ ] [ ] [ ... ] ... ::= SCHEMA | AUTHORIZATION [ FOR STATIC { ONLY | AND DYNAMIC } ] | SCHEMA AUTHORIZATION [ FOR STATIC { ONLY | AND DYNAMIC } ] ::= ::= ::= ::= ... ::= COLLATION [ FOR ] --p --i --small There was another definition in section 18.3. That was slightly different in format (simpler) but functionally equivalent. It is not clear why it was repeated. The alternative definition is now commented out. --/small --/i --/p ::= [ { }... ] ::= | | --h3 13.2 (p768) --/h3 --p Name an SQL-client module. --/p ::= MODULE [ ] [ ] ::= NAMES ARE --h3 13.3 (p769) --/h3 --p Define an externally-invoked procedure. --/p ::= PROCEDURE ::= [ { }... ] ::= | ::= [ ] ::= SQLSTATE --h3 13.4 Calls to an (p772) --/h3 --h3 13.5 (p788) --/h3 --p Define all of the SQL-statements that are s. --/p ::= ::= | | | | | | | ::= | ::= |
| | | | | | | | | | | | | | | ::= | | | | | | | | | | | | | | | | | | | | | | ::= | | |
--## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --##
SQL Data Type C Data Type
SQLSTATE char, with length 6
CHARACTER (L)3 char, with length (L+1)*k1
CHARACTER VARYING (L)3 char, with length (L+1)*k1
CHARACTER LARGE OBJECT(L) --##
--## struct {
--## long hvn3_reserved
--## unsigned long hvn2_length
--## char3 hvn2_data[L];
--## } hvn2
--## 
BINARY LARGE OBJECT(L) --##
 struct {
--## long hvn2_reserved
--## unsigned long hvn2_length
--## char hvn2_data[L];
--## } hvn2
--## 
NUMERIC(P,S) None
DECIMAL(P,S) None
SMALLINT pointer to short
INTEGER pointer to long
BIGINT pointer to long long
FLOAT(P) None
REAL pointer to float
DOUBLE PRECISION pointer to double
BOOLEAN pointer to long
DATE None
TIME(T) None
TIMESTAMP(T) None
INTERVAL(Q) None
user-defined type None
REF char, with length N
ROW None
ARRAY None
MULTISET None
--p --## 1 For character set UTF16, as well as other implementation-defined character sets in which a code unit occupies two octets, k is the length in units of C unsigned short of the character encoded using the greatest number of such units in the character set; for character set UTF32, as well as other implementation-defined character sets in which a code unit occupies four octets, k is four; for other character sets, k is the length in units of C char of the character encoded using the greatest number of such units in the character set. --br --## 2 hvn is the name of the host variable defined to correspond to the SQL data type --br --## 3 For character set UTF16, as well as other implementation-defined character sets in which a code unit occupies two octets, char or unsigned char should be replaced with unsigned short; for character set UTF32, as well as other implementation-defined character sets in which a code unit occupies four octets, char or unsigned char should be replaced with unsigned int. Otherwise, char or unsigned char should be used. --/p --hr --h2 14 Data manipulation --/h2 --h3 14.1 (p807) --/h3 --p Define a cursor. --/p ::= DECLARE [ ] [ ] CURSOR [ ] [ ] FOR ::= SENSITIVE | INSENSITIVE | ASENSITIVE ::= SCROLL | NO SCROLL ::= WITH HOLD | WITHOUT HOLD ::= WITH RETURN | WITHOUT RETURN ::= [ ] [ ] ::= FOR { READ ONLY | UPDATE [ OF ] } ::= ORDER BY --h3 14.2 (p813) --/h3 --p Open a cursor. --/p ::= OPEN --h3 14.3 (p815) --/h3 --p Position a cursor on a specified row of a table and retrieve values from that row. --/p ::= FETCH [ [ ] FROM ] INTO ::= NEXT | PRIOR | FIRST | LAST | { ABSOLUTE | RELATIVE } ::= [ { }... ] --h3 14.4 (p820) --/h3 --p Close a cursor. --/p ::= CLOSE --h3 14.5 ::= SELECT [ ]
| ONLY
--h3 14.7 (p829) --/h3 --p Delete rows of a table. --/p ::= DELETE FROM [ WHERE ] --h3 14.8 (p832) --/h3 --p Create new rows in a table. --/p ::= INSERT INTO ::=
::= | | ::= [ ] [ ] ::= [ ] [ ] ::= OVERRIDING USER VALUE | OVERRIDING SYSTEM VALUE ::= DEFAULT VALUES ::= --h3 14.9 (p837) --/h3 --p Conditionally update rows of a table, or insert new rows into a table, or both. --/p ::= MERGE INTO [ [ AS ] ] USING
ON ::= ::= ... ::= | ::= WHEN MATCHED THEN ::= WHEN NOT MATCHED THEN ::= UPDATE SET ::= INSERT [ ] [ ] VALUES ::= [ { }... ] ::= | --h3 14.10 (p844) --/h3 --p Update a row of a table. --/p ::= UPDATE SET WHERE CURRENT OF --h3 14.11 (p847) --/h3 --p Update rows of a table. --/p ::= UPDATE SET [ WHERE ] --h3 14.12 (p851) --/h3 --p Specify a list of updates. --/p ::= [ { }... ] ::= | ::= | ::= ::= [ { }... ] ::= ::= | ::= ::= ::= | ::= | --h3 14.13 (p856) --/h3 --p Declare a declared local temporary table. --/p ::= DECLARE LOCAL TEMPORARY TABLE
[ ON COMMIT
ROWS ] --h3 14.14 (p858) --/h3 --p Remove the association between a locator variable and the value that is represented by that locator. --/p ::= FREE LOCATOR [ { }... ] ::= | --h3 14.15 (p859) --/h3 --p Mark a locator variable as being holdable. --/p ::= HOLD LOCATOR [ { }... ] --h3 14.16 Effect of deleting rows from base tables (p860) --/h3 --h3 14.17 Effect of deleting some rows from a derived table (p862) --/h3 --h3 14.18 Effect of deleting some rows from a viewed table (p864) --/h3 --h3 14.19 Effect of inserting tables into base tables (p865) --/h3 --h3 14.20 Effect of inserting a table into a derived table (p867) --/h3 --h3 14.21 Effect of inserting a table into a viewed table (p869) --/h3 --h3 14.22 Effect of replacing rows in base tables (p871) --/h3 --h3 14.23 Effect of replacing some rows in a derived table (p874) --/h3 --h3 14.24 Effect of replacing some rows in a viewed table (p877) --/h3 --h3 14.25 Execution of BEFORE triggers (p879) --/h3 --h3 14.26 Execution of AFTER triggers (p880) --/h3 --h3 14.27 Execution of triggers (p881) --/h3 --hr --h2 15 Control statements --/h2 --h3 15.1 (p883) --/h3 --p Invoke an SQL-invoked routine. --/p ::= CALL --h3 15.2 (p884) --/h3 --p Return a value from an SQL function. --/p ::= RETURN ::= | NULL --hr --h2 16 Transaction management --/h2 --h3 16.1 (p885) --/h3 --p Start an SQL-transaction and set its characteristics. --/p ::= START TRANSACTION [ [ { }...] ] ::= | | ::= READ ONLY | READ WRITE ::= ISOLATION LEVEL ::= READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE ::= DIAGNOSTICS SIZE ::= --h3 16.2 (p888) --/h3 --p Set the characteristics of the next SQL-transaction for the SQL-agent. NOTE 402 - This statement has no effect on any SQL-transactions subsequent to the next SQL-transaction. --/p ::= SET [ LOCAL ] ::= TRANSACTION [ { }... ] --h3 16.3 (p890) --/h3 --p 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. NOTE 404: This statement has no effect on any SQL-transactions subsequent to this SQL-transaction. --/p ::= SET CONSTRAINTS { DEFERRED | IMMEDIATE } ::= ALL | [ { }... ] --h3 16.4 (p892) --/h3 --p Establish a savepoint. --/p ::= SAVEPOINT ::= --h3 16.5 (p893) --/h3 --p Destroy a savepoint. --/p ::= RELEASE SAVEPOINT --h3 16.6 (p894) --/h3 --p Terminate the current SQL-transaction with commit. --/p ::= COMMIT [ WORK ] [ AND [ NO ] CHAIN ] --h3 16.7 (p896) --/h3 --p Terminate the current SQL-transaction with rollback, or rollback all actions affecting SQL-data and/or schemas since the establishment of a savepoint. --/p ::= ROLLBACK [ WORK ] [ AND [ NO ] CHAIN ] [ ] ::= TO SAVEPOINT --hr --h2 17 Connection management --/h2 --h3 17.1 (p899) --/h3 --p Establish an SQL-session. --/p ::= CONNECT TO ::= [ AS ] [ USER ] | DEFAULT --h3 17.2 (p902) --/h3 --p Select an SQL-connection from the available SQL-connections. --/p ::= SET CONNECTION ::= DEFAULT | --h3 17.3 (p904) --/h3 --p Terminate an SQL-connection. --/p ::= DISCONNECT ::= | ALL | CURRENT --hr --h2 18 Session management --/h2 --h3 18.1 (p907) --/h3 --p Set one or more characteristics for the current SQL-session. --/p ::= SET SESSION CHARACTERISTICS AS ::= [ { }... ] ::= --h3 18.2 (p908) --/h3 --p Set the SQL-session user identifier and the current user identifier of the current SQL-session context. --/p ::= SET SESSION AUTHORIZATION --h3 18.3 (p909) --/h3 --p Set the current role name for the current SQL-session context. --/p ::= SET ROLE ::= | NONE --h3 18.4 (p911) --/h3 --p Set the default local time zone displacement for the current SQL-session. --/p ::= SET TIME ZONE ::= | LOCAL --h3 18.5 (p912) --/h3 --p 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. --/p ::= SET ::= CATALOG --h3 18.6 (p913) --/h3 --p 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. --/p ::= SET ::= SCHEMA --h3 18.7 (p915) --/h3 --p Set the default character set name for s in s that are prepared in the current SQL-session by an or a and in s that are invoked directly. --/p ::= SET ::= NAMES --h3 18.8 (p916) --/h3 --p Set the SQL-path used to determine the subject routine of s with unqualified s in s that are prepared in the current SQL-session by an or a and in s, respectively, that are invoked directly. The SQL-path remains the current SQL-path of the SQLsession until another SQL-path is successfully set. --/p ::= SET ::= PATH --h3 18.9 (p917) --/h3 --p Set the group name that identifies the group of transform functions for mapping values of userdefined types to predefined data types. --/p ::= SET ::= DEFAULT TRANSFORM GROUP | TRANSFORM GROUP FOR TYPE --h3 18.10 (p918) --/h3 --p Set the SQL-session collation of the SQL-session for one or more character sets. An SQL-session collation remains effective until another SQL-session collation for the same character set is successfully set. --/p ::= SET COLLATION [ FOR ] | SET NO COLLATION [ FOR ] --@@ This is a second definition; the first --@@ is in section 13.1. --@@ It is marginally different in detail from the previous version, but the --@@ overall effect is the same — a comma-separated list of items. --@@ It isn't clear why there's a repeat of the rule or the difference in the expansion. --@@ --@@ ::= [ , ... ] ::= --hr --h2 19 Dynamic SQL --h3 19.1 Description of SQL descriptor areas (p921) --/h3 --h3 19.2 (p931) --/h3 --p Allocate an SQL descriptor area. --/p ::= ALLOCATE [ SQL ] DESCRIPTOR [ WITH MAX ] ::= --h3 19.3 (p933) --/h3 --p Deallocate an SQL descriptor area. --/p ::= DEALLOCATE [ SQL ] DESCRIPTOR --h3 19.4 (p934) --/h3 --p Get information from an SQL descriptor area. --/p ::= GET [ SQL ] DESCRIPTOR ::= [ { }... ] | VALUE [ { }... ] ::=
::= COUNT | KEY_TYPE | DYNAMIC_FUNCTION | DYNAMIC_FUNCTION_CODE | TOP_LEVEL_COUNT ::= --p --i --small The rule for was repeated verbatim in section 19.5. That rule is now omitted. --/small --/i --/p ::= ::= ::= ::= CARDINALITY | CHARACTER_SET_CATALOG | CHARACTER_SET_NAME | CHARACTER_SET_SCHEMA | COLLATION_CATALOG | COLLATION_NAME | COLLATION_SCHEMA | DATA | DATETIME_INTERVAL_CODE | DATETIME_INTERVAL_PRECISION | DEGREE | INDICATOR | KEY_MEMBER | LENGTH | LEVEL | NAME | NULLABLE | OCTET_LENGTH | PARAMETER_MODE | PARAMETER_ORDINAL_POSITION | PARAMETER_SPECIFIC_CATALOG | PARAMETER_SPECIFIC_NAME | PARAMETER_SPECIFIC_SCHEMA | PRECISION | RETURNED_CARDINALITY | RETURNED_LENGTH | RETURNED_OCTET_LENGTH | SCALE | SCOPE_CATALOG | SCOPE_NAME | SCOPE_SCHEMA | TYPE | UNNAMED | USER_DEFINED_TYPE_CATALOG | USER_DEFINED_TYPE_NAME | USER_DEFINED_TYPE_SCHEMA | USER_DEFINED_TYPE_CODE --h3 19.5 (p937) --/h3 --p Set information in an SQL descriptor area. --/p ::= SET [ SQL ] DESCRIPTOR ::= [ { }... ] | VALUE [ { }... ] ::=
::= ::= ::= --@@ This is a repeat of the rule in section 19.4 --@@ ::= --h3 19.6 (p941) --/h3 --p Prepare a statement for execution. --/p ::= PREPARE [ ] FROM ::= ATTRIBUTES ::= ::= ::= | | | | | ::= | | | | | | | ::= ::= ::= ::= ::= ::= !! See the Syntax Rules. --h3 19.7 (p953) --/h3 --p Specify a list of cursor attributes. --/p ::= ... ::= | | | --h3 19.8 (p954) --/h3 --p Deallocate SQL-statements that have been prepared with a . --/p ::= DEALLOCATE PREPARE --h3 19.9 (p955) --/h3 --p Obtain information about the (p961) --/h3 --p Supply input values for an . --/p ::= | ::= USING [ { }... ] ::= ::= --h3 19.11 (p965) --/h3 --p Supply output variables for an . --/p ::= | ::= INTO [ { }... ] ::= ::= INTO [ SQL ] DESCRIPTOR --h3 19.12 (p970) --/h3 --p Associate input SQL parameters and output targets with a prepared statement and execute the statement. --/p ::= EXECUTE [ ] [ ] ::= ::= --h3 19.13 (p972) --/h3 --p Dynamically prepare and execute a preparable statement. --/p ::= EXECUTE IMMEDIATE --h3 19.14 (p973) --/h3 --p Declare a cursor to be associated with a , which may in turn be associated with a . --/p ::= DECLARE [ ] [ ] CURSOR [ ] [ ] FOR --h3 19.15 (p974) --/h3 --p Define a cursor based on a prepared statement for a or assign a cursor to the ordered set of result sets returned from an SQL-invoked procedure. --/p ::= ALLOCATE ::= | ::= [ ] [ ] CURSOR [ ] [ ] FOR ::= FOR PROCEDURE --h3 19.16 (p976) --/h3 --p Associate input dynamic parameters with a and open the cursor. --/p ::= OPEN [ ] --h3 19.17 (p977) --/h3 --p Fetch a row for a cursor declared with a . --/p ::= FETCH [ [ ] FROM ] --h3 19.18 (p978) --/h3 --p Retrieve values from a dynamically-specified row of a table. --/p ::= --h3 19.19 (p979) --/h3 --p Close a cursor. --/p ::= CLOSE --h3 19.20 (p980) --/h3 --p Delete a row of a table. --/p ::= DELETE FROM WHERE CURRENT OF --h3 19.21 (p982) --/h3 --p Update a row of a table. --/p ::= UPDATE SET WHERE CURRENT OF --h3 19.22 (p984) --/h3 --p Delete a row of a table through a dynamic cursor. --/p ::= DELETE [ FROM ] WHERE CURRENT OF [ ] --h3 19.23 (p986) --/h3 --p Update a row of a table through a dynamic cursor. --/p ::= UPDATE [ ] SET WHERE CURRENT OF [ ] --hr --h2 20 Embedded SQL --/h2 --h3 20.1 (p989) --/h3 --p Specify an . --/p ::= | | | | | | ::= [ ] ::= | | | | | | | | | ::= EXEC SQL | SQL ::= END-EXEC | | ::= DECLARE ::= SCHEMA | AUTHORIZATION [ FOR STATIC { ONLY | AND DYNAMIC } ] | SCHEMA AUTHORIZATION [ FOR STATIC { ONLY | AND DYNAMIC } ] ::= ::= ::= ::= ::= [ ] [ ... ] | ::= SQL NAMES ARE ::= BEGIN DECLARE SECTION [ ] ::= END DECLARE SECTION [ ] ::= BEGIN DECLARE SECTION [ ] [ ... ] END DECLARE SECTION ::= | | | | | | ::= ::= | | | | | | --h3 20.2 (p1001) --/h3 --p Specify the action to be taken when an SQL-statement causes a specific class of condition to be raised. --/p ::= WHENEVER ::= ::= | SQLSTATE ( [ , ] ) | CONSTRAINT ::= SQLEXCEPTION | SQLWARNING | NOT FOUND ::= !! See the Syntax Rules. ::= !! See the Syntax Rules. ::= | ::= CONTINUE | ::= { GOTO | GO TO } ::= | | ::= !! See the Syntax Rules. ::= !! See the Syntax Rules. --h3 20.3 (p1005) --/h3 --p Specify an . --/p ::= !! See the Syntax Rules. ::= [ { }... ] [ ] ::= ... ::= ::= !! See the Syntax Rules. ::= | | ::= Interfaces.SQL CHAR [ CHARACTER SET [ IS ] ] 1 | Interfaces.SQL SMALLINT | Interfaces.SQL INT | Interfaces.SQL BIGINT | Interfaces.SQL REAL | Interfaces.SQL DOUBLE_PRECISION | Interfaces.SQL BOOLEAN | Interfaces.SQL SQLSTATE_TYPE | Interfaces.SQL INDICATOR_TYPE ::= CHAR 1 | SMALLINT | INT | BIGINT | REAL | DOUBLE_PRECISION | BOOLEAN | SQLSTATE_TYPE | INDICATOR_TYPE ::= | | | | | | | | ::= SQL TYPE IS CLOB [ CHARACTER SET [ IS ] ] ::= SQL TYPE IS CLOB AS LOCATOR ::= SQL TYPE IS BLOB ::= SQL TYPE IS BLOB AS LOCATOR ::= SQL TYPE IS AS ::= SQL TYPE IS AS LOCATOR ::= SQL TYPE IS ::= SQL TYPE IS AS LOCATOR ::= SQL TYPE IS AS LOCATOR --h3 20.4 (p1011) --/h3 --p Specify an . --/p ::= !! See the Syntax Rules. ::= [ ] [ ] ::= | | ::= auto | extern | static ::= const | volatile --p --small --i It is curious that the C types in SQL 2003 don't include the C keywords int or signed. It is slightly less surprising that the grammar doesn't include C99 keywords such as restrict. --/i --/small --/p ::= { long long | long | short | float | double } [ ] [ { [ ] }... ] ::= [ CHARACTER SET [ IS ] ] [ ] [ { [ ] }... ] ::= char | unsigned char | unsigned short ::= ::= !! See the Syntax Rules. ::= | | | | | | | | | | | | ::= VARCHAR [ CHARACTER SET [ IS ] ] [ ] [ { [ ] }... ] ::= NCHAR [ CHARACTER SET [ IS ] ] [ ] [ { [ ] } ... ] ::= NCHAR VARYING [ CHARACTER SET [ IS ] ] [ ] [ { [ ] } ... ] ::= SQL TYPE IS CLOB [ CHARACTER SET [ IS ] ] [ ] [ { [ ] }... ] ::= SQL TYPE IS NCLOB [ CHARACTER SET [ IS ] ] [ ] [ { [ ] }... ] ::= SQL TYPE IS AS [ ] [ { [ ] } ... ] ::= SQL TYPE IS BLOB [ ] [ { [ ] } ... ] ::= SQL TYPE IS CLOB AS LOCATOR [ ] [ { [ ] } ... ] ::= SQL TYPE IS BLOB AS LOCATOR [ ] [ { [ ] } ... ] ::= SQL TYPE IS AS LOCATOR [ ] [ { [ ] } ... ] ::= SQL TYPE IS AS LOCATOR [ ] [ { [ ] } ... ] ::= SQL TYPE IS AS LOCATOR [ ] [ { [ ] }... ] ::= SQL TYPE IS ::= ... --h3 20.5 (p1019) --/h3 --p Specify an . --/p ::= !! See the Syntax Rules. ::= { 01 | 77 } [ ... ] ::= !! See the Syntax Rules. ::= | | | | ::= | | | | | | | | | ::= [ CHARACTER SET [ IS ] ] { PIC | PICTURE } [ IS ] { X [ ] }... ::= [ CHARACTER SET [ IS ] ] { PIC | PICTURE } [ IS ] { N [ ] }... ::= [ USAGE [ IS ] ] SQL TYPE IS CLOB [ CHARACTER SET [ IS ] ] ::= [ USAGE [ IS ] ] SQL TYPE IS NCLOB [ CHARACTER SET [ IS ] ] ::= [ USAGE [ IS ] ] SQL TYPE IS BLOB ::= [ USAGE [ IS ] ] SQL TYPE IS AS ::= [ USAGE [ IS ] ] SQL TYPE IS CLOB AS LOCATOR ::= [ USAGE [ IS ] ] SQL TYPE IS BLOB AS LOCATOR ::= [ USAGE [ IS ] ] SQL TYPE IS AS LOCATOR ::= [ USAGE [ IS ] ] SQL TYPE IS AS LOCATOR ::= [ USAGE [ IS ] ] SQL TYPE IS AS LOCATOR ::= [ USAGE [ IS ] ] SQL TYPE IS ::= { PIC | PICTURE } [ IS ] S [ USAGE [ IS ] ] DISPLAY SIGN LEADING SEPARATE ::= [ V [ ] ] | V ::= ::= { PIC | PICTURE } [ IS ] S [ USAGE [ IS ] ] BINARY ::= { 9 [ ] }... --h3 20.6 (p1025) --/h3 --p Specify an . --/p ::= !! See the Syntax Rules. ::= [ { }... ] ::= !! See the Syntax Rules. ::= CHARACTER [ ] [ CHARACTER SET [ IS ] ] | CHARACTER KIND = n [ ] [ CHARACTER SET [ IS ] ] | INTEGER | REAL | DOUBLE PRECISION | LOGICAL | ::= | | | | | | | | ::= SQL TYPE IS CLOB [ CHARACTER SET [ IS ] ] ::= SQL TYPE IS BLOB ::= SQL TYPE IS AS ::= SQL TYPE IS CLOB AS LOCATOR ::= SQL TYPE IS BLOB AS LOCATOR ::= SQL TYPE IS AS LOCATOR ::= SQL TYPE IS AS LOCATOR ::= SQL TYPE IS AS LOCATOR ::= SQL TYPE IS --h3 20.7 (p1030) --/h3 --p Specify an . --/p ::= !! See the Syntax Rules. ::= | | ::= VARCHAR [ { }... ] ::= !! See the Syntax Rules. ::= ::= [ { }... ] ::= INT | DEC [ [ ] ] | REAL ::= | | | | | | | | ::= SQL TYPE IS CLOB [ CHARACTER SET [ IS ] ] ::= SQL TYPE IS BLOB ::= SQL TYPE IS AS ::= SQL TYPE IS CLOB AS LOCATOR ::= SQL TYPE IS BLOB AS LOCATOR ::= SQL TYPE IS AS LOCATOR ::= SQL TYPE IS AS LOCATOR ::= SQL TYPE IS AS LOCATOR ::= SQL TYPE IS --h3 20.8 (p1035) --/h3 --p Specify an . --/p ::= !! See the Syntax Rules. ::= [ { }... ] ::= !! See the Syntax Rules. ::= PACKED ARRAY 1 OF CHAR [ CHARACTER SET [ IS ] ] | INTEGER | REAL | CHAR [ CHARACTER SET [ IS ] ] | BOOLEAN | ::= | | | | | | | | ::= SQL TYPE IS CLOB [ CHARACTER SET [ IS ] ] ::= SQL TYPE IS BLOB ::= SQL TYPE IS CLOB AS LOCATOR ::= SQL TYPE IS AS ::= SQL TYPE IS BLOB AS LOCATOR ::= SQL TYPE IS AS LOCATOR ::= SQL TYPE IS AS LOCATOR ::= SQL TYPE IS AS LOCATOR ::= SQL TYPE IS --h3 20.9 (p1040) --/h3 --p Specify an . --/p ::= !! See the Syntax Rules. ::= { DCL | DECLARE } { | [ { }... ] } [ ... ] ::= !! See the Syntax Rules. ::= { CHAR | CHARACTER } [ VARYING ] [ CHARACTER SET [ IS ] ] | [ ] | [ ] | | ::= | | | | | | | | ::= SQL TYPE IS CLOB [ CHARACTER SET [ IS ] ] ::= SQL TYPE IS BLOB ::= SQL TYPE IS AS ::= SQL TYPE IS CLOB AS LOCATOR ::= SQL TYPE IS BLOB AS LOCATOR ::= SQL TYPE IS AS LOCATOR ::= SQL TYPE IS AS LOCATOR ::= SQL TYPE IS AS LOCATOR ::= SQL TYPE IS ::= { DEC | DECIMAL } FIXED | FIXED { DEC | DECIMAL } ::= { BIN | BINARY } FIXED | FIXED { BIN | BINARY } ::= { BIN | BINARY } FLOAT | FLOAT { BIN | BINARY } --hr --h2 21 Direct invocation of SQL --/h2 --h3 21.1 (p1047) --/h3 --p Specify direct execution of SQL. --/p ::= ::= | | | | | ::= | | | | | ::= !! See the Syntax Rules. --h3 21.2 (p1051) --/h3 --p Specify a statement to retrieve multiple rows from a specified table. --/p ::= --hr --h2 22 Diagnostics management --h3 22.1 (p1053) --/h3 --p Get exception or completion condition information from a diagnostics area. --/p ::= GET DIAGNOSTICS ::= | ::= [ { }... ] ::= ::= NUMBER | MORE | COMMAND_FUNCTION | COMMAND_FUNCTION_CODE | DYNAMIC_FUNCTION | DYNAMIC_FUNCTION_CODE | ROW_COUNT | TRANSACTIONS_COMMITTED | TRANSACTIONS_ROLLED_BACK | TRANSACTION_ACTIVE ::= { EXCEPTION | CONDITION } [ { }... ] ::= ::= CATALOG_NAME | CLASS_ORIGIN | COLUMN_NAME | CONDITION_NUMBER | CONNECTION_NAME | CONSTRAINT_CATALOG | CONSTRAINT_NAME | CONSTRAINT_SCHEMA | CURSOR_NAME | MESSAGE_LENGTH | MESSAGE_OCTET_LENGTH | MESSAGE_TEXT | PARAMETER_MODE | PARAMETER_NAME | PARAMETER_ORDINAL_POSITION | RETURNED_SQLSTATE | ROUTINE_CATALOG | ROUTINE_NAME | ROUTINE_SCHEMA | SCHEMA_NAME | SERVER_NAME | SPECIFIC_NAME | SUBCLASS_ORIGIN | TABLE_NAME | TRIGGER_CATALOG | TRIGGER_NAME | TRIGGER_SCHEMA ::= --h3 22.2 Pushing and popping the diagnostics area stack (p1068) --/h3 --hr --h2 23 Status codes --/h2 --h3 23.1 SQLSTATE (p1069) --/h3 --p The character string value returned in an SQLSTATE parameter comprises a 2-character class value followed by a 3-character subclass value, each with an implementation-defined character set that has a one-octet character encoding form and is restricted to s and s. Table 31, 'SQLSTATE class and subclass values', specifies the class value for each condition and the subclass value or values for each class value. --/p --p Class values that begin with one of the s '0', '1', '2', '3', or '4' or one of the s 'A', 'B', 'C', 'D', 'E', 'F', 'G', or 'H' are returned only for conditions defined in ISO/IEC 9075 or in any other International Standard. The range of such class values are called standard-defined classes. Some such class codes are reserved for use by specific International Standards, as specified elsewhere in this Clause. Subclass values associated with such classes that also begin with one of those 13 characters are returned only for conditions defined in ISO/IEC 9075 or some other International Standard. The range of such class values are called standard-defined classes. Subclass values associated with such classes that begin with one of the s '5', '6', '7', '8', or '9' or one of the s 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', or 'Z' are reserved for implementation-specified conditions and are called implementation-defined subclasses. --/p --p Class values that begin with one of the s '5', '6', '7', '8', or '9' or one of the s 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', or 'Z' are reserved for implementation-specified exception conditions and are called implementation-defined classes. All subclass values except '000', which means no subclass, associated with such classes are reserved for implementation-specified conditions and are called implementation-defined subclasses. An implementation-defined completion condition shall be indicated by returning an implementation-defined subclass in conjunction with one of the classes successful completion, warning, or no data. --/p --p The 'Category' column has the following meanings: 'S' means that the class value given corresponds to successful completion and is a completion condition; 'W' means that the class value given corresponds to a successful completion but with a warning and is a completion condition; 'N' means that the class value given corresponds to a no-data situation and is a completion condition; 'X' means that the class value given corresponds to an exception condition. --/p --p Table 31 - SQLSTATE class and subclass values --/p --##
--## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --##
Category Condition Class Subcondition Subclass
X ambiguous cursor name 3C (no subclass) 000
X attempt to assign to non-updatable column 0U (no subclass) 000
X attempt to assign to ordering column 0V (no subclass) 000
X cardinality violation 21 (no subclass) 000
X connection exception 08 (no subclass) 000
      connection does not exist 003
      connection failure 006
      connection name in use 002
      SQL-client unable to establish SQL-connection 001
      SQL-server rejected establishment of SQL-connection 004
      transaction resolution unknown 007
X cursor sensitivity exception 36 (no subclass) 000
      request failed 002
      request rejected 001
X data exception 22 (no subclass) 000
      array data, right truncation 02F
      array element error 02E
      character not in repertoire 021
      datetime field overflow 008
      division by zero 012
      error in assignment 005
      escape character conflict 00B
      indicator overflow 022
      interval field overflow 015
      invalid argument for natural logarithm 01E
      invalid argument for power function 01F
      invalid argument for width bucket function 01G
      invalid character value for cast 018
      invalid datetime format 007
      invalid escape character 019
      invalid escape octet 00D
      invalid escape sequence 025
      invalid indicator parameter value 010
      invalid interval format 006
      invalid parameter value 023
      invalid preceding or following size in window function 013
      invalid regular expression 01B
      invalid repeat argument in a sample clause 02G
      invalid sample size 02H
      invalid time zone displacement value 009
      invalid use of escape character 00C
      most specific type mismatch 00G
      noncharacter in UCS string 029
      null value substituted for mutator subject parameter 02D
      null row not permitted in table 01C
      null value in array target 00E
      null value, no indicator parameter 002
      null value not allowed 004
      numeric value out of range 003
      sequence generator limit exceeded 00H
      string data, length mismatch 026
      string data, right truncation 001
      substring error 011
      trim error 027
      unterminated C string 024
      zero-length character string 00F
X dependent privilege descriptors still exist 2B (no subclass) 000
X diagnostics exception 0Z (no subclass) 000
      maximum number of stacked diagnostics areas exceeded 001
X dynamic SQL error 07 (no subclass) 000
      cursor specification cannot be executed 003
      data type transform function violation 00B
      invalid DATA target 00D
      invalid DATETIME_INTERVAL_CODE 00F
      invalid descriptor count 008
      invalid descriptor index 009
      invalid LEVEL value 00E
      prepared statement not a cursor specification 005
      restricted data type attribute violation 006
      undefined DATA value 00C
      using clause does not match dynamic parameter specifications 001
      using clause does not match target specifications 002
      using clause required for dynamic parameters 004
      using clause required for result fields 007
X external routine exception 38 (no subclass) 000
      containing SQL not permitted 001
      modifying SQL-data not permitted 002
      prohibited SQL-statement attempted 003
      reading SQL-data not permitted 004
X external routine invocation exception 39 (no subclass) 000
      invalid SQLSTATE returned 001
      null value not allowed 004
X feature not supported 0A (no subclass) 000
      multiple server transactions 001
X integrity constraint violation 23 (no subclass) 000
      restrict violation 001
X invalid authorization specification 28 (no subclass) 000
X invalid catalog name 3D (no subclass) 000
X invalid character set name 2C (no subclass) 000
X invalid condition number 35 (no subclass) 000
X invalid connection name 2E (no subclass) 000
X invalid cursor name 34 (no subclass) 000
X invalid cursor state 24 (no subclass) 000
X invalid grantor 0L (no subclass) 000
X invalid role specification 0P (no subclass) 000
X invalid schema name 3F (no subclass) 000
X invalid schema name list specification 0E (no subclass) 000
X invalid session collation specification 2H (no subclass) 000
X invalid SQL descriptor name 33 (no subclass) 000
X invalid SQL-invoked procedure reference 0M (no subclass) 000
X invalid SQL statement name 26 (no subclass) 000
X invalid SQL statement identifier 30 (no subclass) 000
X invalid target type specification 0D (no subclass) 000
X invalid transaction initiation 0B (no subclass) 000
X invalid transaction state 25 (no subclass) 000
      active SQL-transaction 001
      branch transaction already active 002
      held cursor requires same isolation level 008
      inappropriate access mode for branch transaction 003
      inappropriate isolation level for branch transaction 004
      no active SQL-transaction for branch transaction 005
      read-only SQL-transaction 006
      schema and data statement mixing not supported 007
X invalid transaction termination 2D (no subclass) 000
X invalid transform group name specification 0S (no subclass) 000
X locator exception 0F (no subclass) 000
      invalid specification 001
N no data 02 (no subclass) 000
      no additional dynamic result sets returned 001
X prohibited statement encountered during trigger execution 0W (no subclass) 000
X Remote Database Access HZ (See Table 32, 'SQLSTATE class codes for RDA', for the definition of protocol subconditions and subclass code values)  
X savepoint exception 3B (no subclass) 000
      invalid specification 001
      too many 002
X SQL routine exception 2F (no subclass) 000
      function executed no return statement 005
      modifying SQL-data not permitted 002
      prohibited SQL-statement attempted 003
      reading SQL-data not permitted 004
S successful completion 00 (no subclass) 000
X syntax error or access rule violation 42 (no subclass) 000
X target table disagrees with cursor specification 0T (no subclass) 000
X transaction rollback 40 (no subclass) 000
      integrity constraint violation 002
      serialization failure 001
      statement completion unknown 003
      triggered action exception 004
X triggered action exception 09 (no subclass) 000
X triggered data change violation 27 (no subclass) 000
W warning 01 (no subclass) 000
      additional result sets returned 00D
      array data, right truncation 02F
      attempt to return too many result sets 00E
      cursor operation conflict 001
      default value too long for information schema 00B
      disconnect error 002
      dynamic result sets returned 00C
      external routine warning (the value of xx to be chosen by the author of the external routine) Hxx
      insufficient item descriptor areas 005
      null value eliminated in set function 003
      privilege not granted 007
      privilege not revoked 006
      query expression too long for information schema 00A
      search condition too long for information schema 009
      statement too long for information schema 008
      string data, right truncation 004
X with check option violation 44 (no subclass) 000
--hr --h2 24 Conformance --/h2 --h3 24.1 General Conformance Requirements (p1079) --/h3 --p Table 33 - Implied feature relationships --/p --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --## --##
Feature ID Feature Description Implied Feature ID Implied Feature Description
B032 Extended dynamic SQL B031 Basic dynamic SQL
B034 Dynamic specification of cursor attributes B031 Basic dynamic SQL
F381 Extended schema manipulation F491 Constraint management
F451 Character set definition F461 Named character sets
F711 ALTER domain F251 Domain support
F801 Full set function F441 Extended set function support
S024 Enhanced structured types S023 Basic structured types
S041 Basic reference types S023 Basic structured types
S041 Basic reference types S051 Create table of type
S043 Enhanced reference types S041 Basic reference types
S051 Create table of type S023 Basic structured types
S081 Subtables S023 Basic structured types
S081 Subtables S051 Create table of type
S092 Arrays of user-defined types S091 Basic array support
S094 Arrays of reference types S041 Basic reference types
S094 Arrays of reference types S091 Basic array support
S095 Array constructors by query S091 Basic array support
S096 Optional array bounds S091 Basic array support
S111 ONLY in query expressions S023 Basic structured types
S111 ONLY in query expressions S051 Create table of type
S201 SQL-invoked routines on arrays S091 Basic array support
S202 SQL-invoked routines on multisets S271 Basic multiset support
S231 Structured type locators S023 Basic structured types
S232 Array locators S091 Basic array support
S233 Multiset locators S271 Basic multiset support
S242 Alter transform statement S241 Transform functions
S272 Multisets of user-defined types S271 Basic multiset support
S274 Multisets of reference types S041 Basic reference types
S274 Multisets of reference types S271 Basic multiset support
S275 Advanced multiset support S271 Basic multiset support
T042 Extended LOB data type support T041 Basic LOB data type support
T061 UCS Support F461 Named character sets
T071 BIGINT data type E001-01 INTEGER and SMALLINT data types (including all spellings)
T131 Recursive query T121 WITH (excluding RECURSIVE) in query expression
T173 Extended LIKE clause in table definition T171 LIKE clause in table definition
T212 Enhanced trigger capability T211 Basic trigger capability
T332 Extended roles T331 Basic roles
T511 Transaction counts F121 Basic diagnostics management
T571 Array-returning external SQL-invoked functions S091 Basic array support
T571 Array-returning external SQL-invoked functions S201 SQL-invoked routines on arrays
T572 Multiset-returning external SQLinvoked functions S202 SQL-invoked routines on multisets
T572 Multiset-returning external SQLinvoked functions S271 Basic multiset support
T612 Advanced OLAP operations T611 Elementary OLAP operations
--hr --h2 END OF SQL-2003 Part 2 (SQL/Foundation) GRAMMAR --/h2 --h2 Notes on Automatically Converting the SQL Grammar to a YACC Grammar --/h2 --p Automatic translation of this grammar is non-trivial for a number of reasons. One is that the grammar has a number of actions '!! See the Syntax Rules' which cannot be translated automatically. Another is that the grammar contains rules that are usually better handled by the lexical analyzer than the grammar proper. Then there are incomplete rules such as those which reference parts 6 to 10 (they are not defined; indeed, part 7, which was going to be SQL/Temporal, is in complete abeyance), and the packages (almost completely undefined in the grammar). It is not clear whether these can be ignored, or annotated out of the way. --/p --p Another complication is automatically generating rules to deal with optional components and repetitive components in the grammar. Square brackets do not contain alternative non-terminals; all those expressions are contained within curly brackets within the square brackets. However, some square brackets do contain alternative terminals. Curly brackets contain and group mandatory elements. However, they are usually used in conjunction with the 'one or more times' repeater ellipsis '...' mark. --/p --hr --h2 END OF SQL 2003-2 (SQL/FOUNDATION) GRAMMAR --/h2