BNF Grammar for ISO/IEC 9075-2:2003 - Database Language SQL (SQL-2003) SQL/Foundation

Cross-Reference: rules
Cross-Reference: keywords

Derived from file sql-2003-2.bnf version 1.11 dated 2005/07/13 18:37:30
Generated on 2005-07-13 18:37:56+00:00 by file bnf2html.pl version 3.7 dated 2005/07/13 18:32:35

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!

The plain text version of this grammar is sql-2003-2.bnf .

Key SQL Statements and Fragments

ALTER DOMAIN <alter domain statement>

ALTER TABLE <alter table statement>

CLOSE cursor <close statement>

Column definition <column definition>

COMMIT WORK <commit statement>

CONNECT <connect statement>

CREATE ASSERTION <assertion definition>

CREATE CHARACTER SET <character set definition>

CREATE COLLATION <collation definition>

CREATE DOMAIN <domain definition>

CREATE FUNCTION <schema function>

CREATE PROCEDURE <schema procedure>

CREATE SCHEMA <schema definition>

CREATE TABLE <table definition>

CREATE TRANSLATION <translation definition>

CREATE TRIGGER <trigger definition>

CREATE VIEW <view definition>

Data type <data type>

DEALLOCATE PREPARE <deallocate prepared statement>

DECLARE cursor <declare cursor> <dynamic declare cursor>

DECLARE LOCAL TEMPORARY TABLE <temporary table declaration>

DELETE <delete statement: positioned> <delete statement: searched> <dynamic delete statement: positioned>

DESCRIBE <describe statement>

DESCRIPTOR statements <system descriptor statement>

DISCONNECT <disconnect statement>

EXECUTE <execute statement>

EXECUTE IMMEDIATE <execute immediate statement>

FETCH cursor <fetch statement>

FROM clause <from clause>

GET DIAGNOSTICS <get diagnostics statement>

GRANT <grant statement>

GROUP BY clause <group by clause>

HAVING clause <having clause>

INSERT <insert statement>

Literals <literal>

Keywords <key word>

MERGE <merge statement>

OPEN cursor <open statement>

ORDER BY clause <order by clause>

PREPARE <prepare statement>

REVOKE <revoke statement>

ROLLBACK WORK <rollback statement>

SAVEPOINT <savepoint statement>

Search condition <search condition> <regular expression>

SELECT <query specification>

SET CATALOG <set catalog statement>

SET CONNECTION <set connection statement>

SET CONSTRAINTS <set constraints mode statement>

SET NAMES <set names statement>

SET SCHEMA <set schema statement>

SET SESSION AUTHORIZATION <set session user identifier statement>

SET TIME ZONE <set local time zone statement>

SET TRANSACTION <set transaction statement>

SQL Client MODULE <SQL-client module definition>

UPDATE <update statement: positioned> <update statement: searched> <dynamic update statement: positioned>

Value expression <value expression>

WHERE clause <where clause>

Top

5 Lexical Elements

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.

5.1 <SQL terminal character> (p151)

<simple Latin upper case letter> ::=
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

<simple Latin lower case letter> ::=
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

<digit> ::= 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9

<space> ::= !! See the Syntax Rules

<double quote> ::= "

<percent> ::= %

<ampersand> ::= &

<quote> ::= '

<left paren> ::= (

<right paren> ::= )

<asterisk> ::= *

<plus sign> ::= +

<comma> ::= ,

<minus sign> ::= -

<period> ::= .

<solidus> ::= /

<colon> ::= :

<semicolon> ::= ;

<less than operator> ::= <

<equals operator> ::= =

<question mark> ::= ?

The trigraphs are new in SQL-2003.

<left bracket> ::= [

<left bracket trigraph> ::= ??(

<right bracket> ::= ]

<right bracket trigraph> ::= ??)

<circumflex> ::= ^

<underscore> ::= _

<vertical bar> ::= /* Nothing */ |

<left brace> ::= {

<right brace> ::= }

Top

5.2 <token> and <separator> (p134)

Specifying lexical units (tokens and separators) that participate in SQL language.

<identifier body> ::= <identifier start> [ <identifier part> ... ]

Previous standard said:

<identifier start> ::= <initial alphabetic character> | <ideographic character>

<identifier start> ::= !! See the Syntax Rules

<identifier extend> ::= !! See the Syntax Rules

<multiplier> ::= K | M | G

The productions for <Unicode delimited identifier> and so on are new in SQL-2003.

<Unicode delimited identifier>    ::=
         U <ampersand> <double quote> <Unicode delimiter body> ... <double quote>
         [ ESCAPE <escape character> ]

<Unicode delimiter body> ::= <Unicode identifier part> ...

<escape character> ::= !! See the Syntax Rules

<nondoublequote character> ::= !! See the Syntax Rules

The rule for <doublequote symbol> in the standard uses two adjacent literal double quotes rather than referencing <double quote>; the reasons are not clear. It is annotated '!! two consecutive double quote characters'.

The rules for <not equals operator> etc in the standard uses two adjacent literal characters rather than referencing <less than> and <greater than>; the reasons are not clear. Note that two characters must be adjacent with no intervening space, not a pair of characters separated by arbitrary white space.

<separator> ::= { <comment> | <white space> }...

<simple comment introducer> ::= <minus sign> <minus sign> [ <minus sign> ... ]

The <bracketed comment> rule included '!! See the Syntax Rules'. This probably says something about the <slash> <asterisk> and <asterisk> <slash> needing to be adjacent characters rather than adjacent tokens.

<bracketed comment contents> ::= [ { <comment character> | <separator> }... ]

<newline> ::= !! See the Syntax Rules

There was a surprising amount of movement of keywords between the reserved and non-reserved word classes. There is also room to think that much of the host language support moved out of Part 2 (SQL/Foundation).

Top

5.3 <literal> (p143)

<character string literal>    ::=
         [ <introducer> <character set specification> ]
         <quote> [ <character representation> ... ] <quote>
         [ { <separator> <quote> [ <character representation> ... ] <quote> }... ]

<nonquote character> ::= !! See the Syntax Rules

The <quote symbol> rule consists of two immediately adjacent <quote> marks with no spaces. As usual, this would be best handled in the lexical analyzer, not in the grammar.

<national character string literal>    ::=
         N <quote> [ <character representation> ... ] <quote>
         [ { <separator> <quote> [ <character representation> ... ] <quote> }... ]

<Unicode character string literal>    ::=
         [ <introducer> <character set specification> ]
         U <ampersand> <quote> [ <Unicode representation> ... ] <quote>
         [ { <separator> <quote> [ <Unicode representation> ... ] <quote> }... ]
         [ ESCAPE <escape character> ]

<binary string literal>    ::=
         X <quote> [ { <hexit> <hexit> }... ] <quote>
         [ { <separator> <quote> [ { <hexit> <hexit> }... ] <quote> }... ]
         [ ESCAPE <escape character> ]

<hexit> ::= <digit> | A | B | C | D | E | F | a | b | c | d | e | f

<timestamp literal> ::= TIMESTAMP <timestamp string>

<interval literal> ::= INTERVAL [ <sign> ] <interval string> <interval qualifier>

<unquoted time string> ::= <time value> [ <time zone interval> ]

<unquoted interval string> ::= [ <sign> ] { <year-month literal> | <day-time literal> }

<year-month literal> ::= <years value> | [ <years value> <minus sign> ] <months value>

<day-time literal> ::= <day-time interval> | <time interval>

<day-time interval> ::=
<days value> [ <space> <hours value> [ <colon> <minutes value> [ <colon> <seconds value> ] ] ]

<seconds value> ::= <seconds integer value> [ <period> [ <seconds fraction> ] ]

<boolean literal> ::= TRUE | FALSE | UNKNOWN

Top

5.4 Names and identifiers (p151)

<SQL language identifier> ::=
<SQL language identifier start> [ { <underscore> | <SQL language identifier part> }... ]

<local or schema qualifier> ::= <schema name> | MODULE

<SQL-client module name> ::= <identifier>

<local qualifier> ::= MODULE

<user-defined type name> ::= <schema qualified type name>

<schema-resolved user-defined type name> ::= <user-defined type name>

<SQL-server name> ::= <simple value specification>

<scope option> ::= GLOBAL | LOCAL

Top

6 Scalar expressions

6.25 <value expression> (p236)

Specify a value.

<user-defined type value expression> ::= <value expression primary>

6.26 <numeric value expression> (p240)

Specify a numeric value.

6.27 <numeric value function> (p242)

Specify a function yielding a value of type numeric.

<string position expression> ::=
POSITION <left paren> <string value expression> IN <string value expression> [ USING <char length units> ] <right paren>

<blob position expression> ::=
POSITION <left paren> <blob value expression> IN <blob value expression> <right paren>

<char length expression> ::=
{ CHAR_LENGTH | CHARACTER_LENGTH } <left paren> <string value expression> [ USING <char length units> ] <right paren>

<octet length expression> ::= OCTET_LENGTH <left paren> <string value expression> <right paren>

<extract expression> ::= EXTRACT <left paren> <extract field> FROM <extract source> <right paren>

<time zone field> ::= TIMEZONE_HOUR | TIMEZONE_MINUTE

<cardinality expression> ::= CARDINALITY <left paren> <collection value expression> <right paren>

<power function> ::= POWER <left paren> <numeric value expression base> <comma> <numeric value expression exponent> <right paren>

<floor function> ::= FLOOR <left paren> <numeric value expression> <right paren>

<ceiling function> ::= { CEIL | CEILING } <left paren> <numeric value expression> <right paren>

<width bucket function> ::= WIDTH_BUCKET <left paren> <width bucket operand> <comma> <width bucket bound 1> <comma> <width bucket bound 2> <comma> <width bucket count> <right paren>

6.28 <string value expression> (p251)

Specify a character string value or a binary string value.

<character factor> ::= <character primary> [ <collate clause> ]

6.29 <string value function> (p255)

Specify a function yielding a value of type character string or binary string.

<character substring function>    ::=
         SUBSTRING <left paren> <character value expression> FROM <start position>
         [ FOR <string length> ] [ USING <char length units> ] <right paren>

<regular expression substring function>    ::=
         SUBSTRING <left paren> <character value expression>
         SIMILAR <character value expression> ESCAPE <escape character> <right paren>

<fold> ::= { UPPER | LOWER } <left paren> <character value expression> <right paren>

<transcoding> ::= CONVERT <left paren> <character value expression> USING <transcoding name> <right paren>

<character transliteration> ::= TRANSLATE <left paren> <character value expression> USING <transliteration name> <right paren>

<trim specification> ::= LEADING | TRAILING | BOTH

<character overlay function>    ::=
         OVERLAY <left paren> <character value expression> PLACING <character value expression>
         FROM <start position> [ FOR <string length> ] [ USING <char length units> ] <right paren>

<normalize function> ::= NORMALIZE <left paren> <character value expression> <right paren>

<specific type method> ::= <user-defined type value expression> <period> SPECIFICTYPE

<blob substring function> ::=
SUBSTRING <left paren> <blob value expression> FROM <start position> [ FOR <string length> ] <right paren>

<blob overlay function>    ::=
         OVERLAY <left paren> <blob value expression> PLACING <blob value expression>
         FROM <start position> [ FOR <string length> ] <right paren>

6.30 <datetime value expression> (p266)

Specify a datetime value.

<datetime factor> ::= <datetime primary> [ <time zone> ]

<time zone specifier> ::= LOCAL | TIME ZONE <interval primary>

6.31 <datetime value function> (p269)

Specify a function yielding a value of type datetime.

<current date value function> ::= CURRENT_DATE

<current time value function> ::= CURRENT_TIME [ <left paren> <time precision> <right paren> ]

<current local time value function> ::= LOCALTIME [ <left paren> <time precision> <right paren> ]

<current timestamp value function> ::= CURRENT_TIMESTAMP [ <left paren> <timestamp precision> <right paren> ]

<current local timestamp value function> ::= LOCALTIMESTAMP [ <left paren> <timestamp precision> <right paren> ]

6.32 <interval value expression> (p271)

Specify an interval value.

6.33 <interval value function> (p276)

6.34 <boolean value expression> (p277)

<boolean test> ::= <boolean primary> [ IS [ NOT ] <truth value> ]

<truth value> ::= TRUE | FALSE | UNKNOWN

6.35 <array value expression> (p284)

6.36 <array value constructor> (p284)

<array value constructor by enumeration> ::=
ARRAY <left bracket or trigraph> <array element list> <right bracket or trigraph>

<array element list> ::= <array element> [ { <comma> <array element> }... ]

<array value constructor by query> ::=
ARRAY <left paren> <query expression> [ <order by clause> ] <right paren>

6.37 <multiset value expression> (p286)

<multiset value expression>    ::=
         <multiset term>
     |     <multiset value expression> MULTISET UNION [ ALL | DISTINCT ] <multiset term>
     |     <multiset value expression> MULTISET EXCEPT [ ALL | DISTINCT ] <multiset term>

<multiset term>    ::=
         <multiset primary>
     |     <multiset term> MULTISET INTERSECT [ ALL | DISTINCT ] <multiset primary>

6.38 <multiset value function> (p289)

6.39 <multiset value constructor> (p290)

<multiset value constructor by enumeration> ::= MULTISET <left bracket or trigraph> <multiset element list> <right bracket or trigraph>

<multiset element list> ::= <multiset element> [ { <comma> <multiset element> } ]

<multiset value constructor by query> ::= MULTISET <left paren> <query expression> <right paren>

<table value constructor by query> ::= TABLE <left paren> <query expression> <right paren>

Top

7 Query expressions

7.1 <row value constructor> (p293)

Specify a value or list of values to be constructed into a row or partial row.

<row value constructor element list> ::=
<row value constructor element> [ { <comma> <row value constructor element> }... ]

<contextually typed row value constructor element list>    ::=
         <contextually typed row value constructor element>
         [ { <comma> <contextually typed row value constructor element> }... ]

7.2 <row value expression> (p296)

Specify a row value.

7.3 <table value constructor> (p298)

Specify a set of <row value expression>s to be constructed into a table.

<table value constructor> ::= VALUES <row value expression list>

<row value expression list> ::= <table row value expression> [ { <comma> <table row value expression> }... ]

<contextually typed table value constructor> ::= VALUES <contextually typed row value expression list>

<contextually typed row value expression list> ::= <contextually typed row value expression> [ { <comma> <contextually typed row value expression> }... ]

7.4 <table expression> (p300)

Specify a table or a grouped table.

<table expression>    ::=
         <from clause>
         [ <where clause> ]
         [ <group by clause> ]
         [ <having clause> ]
         [ <window clause> ]

7.5 <from clause> (p301)

Specify a table derived from one or more tables.

<table reference list> ::= <table reference> [ { <comma> <table reference> }... ]

7.6 <table reference> (p303)

Reference a table.

<table reference> ::= <table primary or joined table> [ <sample clause> ]

<sample clause> ::=
TABLESAMPLE <sample method> <left paren> <sample percentage> <right paren> [ <repeatable clause> ]

<sample method> ::= BERNOULLI | SYSTEM

<repeatable clause> ::= REPEATABLE <left paren> <repeat argument> <right paren>

<lateral derived table> ::= LATERAL <table subquery>

<collection derived table> ::= UNNEST <left paren> <collection value expression> <right paren> [ WITH ORDINALITY ]

<table function derived table> ::= TABLE <left paren> <collection value expression> <right paren>

<column name list> ::= <column name> [ { <comma> <column name> }... ]

7.7 <joined table> (p312)

Specify a table derived from a Cartesian product, inner or outer join, or union join.

<cross join> ::= <table reference> CROSS JOIN <table primary>

<natural join> ::= <table reference> NATURAL [ <join type> ] JOIN <table primary>

<union join> ::= <table reference> UNION JOIN <table primary>

<named columns join> ::= USING <left paren> <join column list> <right paren>

<join type> ::= INNER | <outer join type> [ OUTER ]

<outer join type> ::= LEFT | RIGHT | FULL

7.8 <where clause> (p319)

Specify a table derived by the application of a <search condition> to the result of the preceding <from clause>.

<where clause> ::= WHERE <search condition>

7.9 <group by clause> (p320)

Specify a grouped table derived by the application of the <group by clause> to the result of the previously specified clause.

<group by clause> ::= GROUP BY [ <set quantifier> ] <grouping element list>

<grouping element list> ::= <grouping element> [ { <comma> <grouping element> }... ]

<grouping column reference> ::= <column reference> [ <collate clause> ]

<grouping column reference list> ::= <grouping column reference> [ { <comma> <grouping column reference> }... ]

<rollup list> ::= ROLLUP <left paren> <ordinary grouping set list> <right paren>

<ordinary grouping set list> ::= <ordinary grouping set> [ { <comma> <ordinary grouping set> }... ]

<grouping sets specification> ::= GROUPING SETS <left paren> <grouping set list> <right paren>

<grouping set list> ::= <grouping set> [ { <comma> <grouping set> }... ]

7.10 <having clause> (p329)

Specify a grouped table derived by the elimination of groups that do not satisfy a <search condition>.

<having clause> ::= HAVING <search condition>

7.11 <window clause> (p331)

Specify one or more window definitions.

<window clause> ::= WINDOW <window definition list>

<window definition list> ::= <window definition> [ { <comma> <window definition> }... ]

<window specification details> ::=
[ <existing window name> ] [ <window partition clause> ] [ <window order clause> ] [ <window frame clause> ]

<window partition clause> ::= PARTITION BY <window partition column reference list>

<window partition column reference list> ::= <window partition column reference> [ { <comma> <window partition column reference> }... ]

<window partition column reference> ::= <column reference> [ <collate clause> ]

<window order clause> ::= ORDER BY <sort specification list>

<window frame clause> ::= <window frame units> <window frame extent> [ <window frame exclusion> ]

<window frame units> ::= ROWS | RANGE

<window frame start> ::= UNBOUNDED PRECEDING | <window frame preceding> | CURRENT ROW

<window frame preceding> ::= <unsigned value specification> PRECEDING

<window frame between> ::= BETWEEN <window frame bound 1> AND <window frame bound 2>

<window frame bound>    ::=
         <window frame start>
     |     UNBOUNDED FOLLOWING
     |     <window frame following>

<window frame following> ::= <unsigned value specification> FOLLOWING

<window frame exclusion>    ::=
         EXCLUDE CURRENT ROW
     |     EXCLUDE GROUP
     |     EXCLUDE TIES
     |     EXCLUDE NO OTHERS

7.12 <query specification> (p341)

Specify a table derived from the result of a <table expression>.

<query specification> ::= SELECT [ <set quantifier> ] <select list> <table expression>

<select list> ::= <asterisk> | <select sublist> [ { <comma> <select sublist> }... ]

<asterisked identifier chain> ::= <asterisked identifier> [ { <period> <asterisked identifier> }... ]

<derived column> ::= <value expression> [ <as clause> ]

<all fields reference> ::= <value expression primary> <period> <asterisk> [ AS <left paren> <all fields column name list> <right paren> ]

7.13 <query expression> (p350)

Specify a table.

<with clause> ::= WITH [ RECURSIVE ] <with list>

<with list> ::= <with list element> [ { <comma> <with list element> }... ]

<with list element>    ::=
         <query name> [ <left paren> <with column list> <right paren> ]
         AS <left paren> <query expression> <right paren> [ <search or cycle clause> ]

<non-join query expression>    ::=
         <non-join query term>
     |     <query expression body> UNION [ ALL | DISTINCT ] [ <corresponding spec> ] <query term>
     |     <query expression body> EXCEPT [ ALL | DISTINCT ] [ <corresponding spec> ] <query term>

<non-join query term>    ::=
         <non-join query primary>
     |     <query term> INTERSECT [ ALL | DISTINCT ] [ <corresponding spec> ] <query primary>

<non-join query primary> ::= <simple table> | <left paren> <non-join query expression> <right paren>

<explicit table> ::= TABLE <table or query name>

<corresponding spec> ::= CORRESPONDING [ BY <left paren> <corresponding column list> <right paren> ]

7.14 <search or cycle clause> (p363)

Specify the generation of ordering and cycle detection information in the result of recursive query expressions.

<search clause> ::= SEARCH <recursive search order> SET <sequence column>

<recursive search order>    ::=
         DEPTH FIRST BY <sort specification list>
     |     BREADTH FIRST BY <sort specification list>

<cycle clause>    ::=
         CYCLE <cycle column list>
         SET <cycle mark column> TO <cycle mark value>
         DEFAULT <non-cycle mark value>
         USING <path column>

<cycle column list> ::= <cycle column> [ { <comma> <cycle column> }... ]

<non-cycle mark value> ::= <value expression>

7.15 <subquery> (p368)

Specify a scalar value, a row, or a table derived from a <query expression>.

Top

8 Predicates

8.1 <predicate> (p371)

Specify a condition that can be evaluated to give a boolean value.

8.2 <comparison predicate> (p373)

Specify a comparison of two row values.

8.3 <between predicate> (p380)

Specify a range comparison.

<between predicate part 2> ::= [ NOT ] BETWEEN [ ASYMMETRIC | SYMMETRIC ] <row value predicand> AND <row value predicand>

8.4 <in predicate> (p381)

Specify a quantified comparison.

<in value list> ::= <row value expression> [ { <comma> <row value expression> }... ]

8.5 <like predicate> (p383)

Specify a pattern-match comparison.

<character like predicate part 2> ::= [ NOT ] LIKE <character pattern> [ ESCAPE <escape character> ]

<octet like predicate part 2> ::= [ NOT ] LIKE <octet pattern> [ ESCAPE <escape octet> ]

8.6 <similar predicate> (p389)

Specify a character string similarity by means of a regular expression.

<upper limit> ::= <comma> [ <high value> ]

<non-escaped character> ::= !! See the Syntax Rules

<escaped character> ::= !! See the Syntax Rules

8.7 <null predicate> (p395)

Specify a test for a null value.

<null predicate part 2> ::= IS [ NOT ] NULL

8.8 <quantified comparison predicate> (p397)

Specify a quantified comparison.

<all> ::= ALL

<some> ::= SOME | ANY

8.9 <exists predicate> (p399)

Specify a test for a non-empty set.

<exists predicate> ::= EXISTS <table subquery>

8.10 <unique predicate> (p400)

Specify a test for the absence of duplicate rows

<unique predicate> ::= UNIQUE <table subquery>

8.11 <normalized predicate> (p401)

Determine whether a character string value is normalized.

<normalized predicate> ::= <string value expression> IS [ NOT ] NORMALIZED

8.12 <match predicate> (p402)

Specify a test for matching rows.

<match predicate part 2> ::= MATCH [ UNIQUE ] [ SIMPLE | PARTIAL | FULL ] <table subquery>

8.13 <overlaps predicate> (p405)

Specify a test for an overlap between two datetime periods.

<overlaps predicate part 2> ::= OVERLAPS <row value predicand 2>

8.14 <distinct predicate> (p407)

Specify a test of whether two row values are distinct

<distinct predicate part 2> ::= IS DISTINCT FROM <row value predicand 4>

8.15 <member predicate> (p409)

Specify a test of whether a value is a member of a multiset.

<member predicate part 2> ::= [ NOT ] MEMBER [ OF ] <multiset value expression>

8.16 <submultiset predicate> (p411)

Specify a test of whether a multiset is a submultiset of another multiset.

<submultiset predicate part 2> ::= [ NOT ] SUBMULTISET [ OF ] <multiset value expression>

8.17 <set predicate> (p413)

Specify a test of whether a multiset is a set (that is, does not contain any duplicates).

<set predicate part 2> ::= IS [ NOT ] A SET

8.18 <type predicate> (p414)

Specify a type test.

<type list> ::= <user-defined type specification> [ { <comma> <user-defined type specification> }... ]

<user-defined type specification>    ::=
         <inclusive user-defined type specification>
     |     <exclusive user-defined type specification>

8.19 <search condition> (p416)

Specify a condition that is True , False , or Unknown , depending on the value of a <boolean value expression>.

Top

9 Additional common rules

9.1 Retrieval assignment (p417)

9.2 Store assignment (p422)

9.3 Data types of results of aggregations (p427)

9.4 Subject routine determination (p430)

9.5 Type precedence list determination (p431)

9.6 Host parameter mode determination (p434)

9.7 Type name determination (p436)

9.8 Determination of identical values (p438)

9.9 Equality operations (p440)

9.10 Grouping operations (p443)

9.11 Multiset element grouping operations (p445)

9.12 Ordering operations (p447)

9.13 Collation determination (p449)

9.14 Execution of array-returning functions (p450)

9.15 Execution of multiset-returning functions (p453)

9.16 Data type identity (p454)

9.17 Determination of a from-sql function (p456)

9.18 Determination of a from-sql function for an overriding method (p457)

9.19 Determination of a to-sql function (p458)

9.20 Determination of a to-sql function for an overriding method (p459)

9.21 Generation of the next value of a sequence generator (p460)

9.22 Creation of a sequence generator (p461)

9.23 Altering a sequence generator (p463)

Top

10 Additional common elements

10.1 <interval qualifier> (p465)

Specify the precision of an interval data type.

<start field> ::= <non-second primary datetime field> [ <left paren> <interval leading field precision> <right paren> ]

<end field>    ::=
         <non-second primary datetime field>
     |     SECOND [ <left paren> <interval fractional seconds precision> <right paren> ]

<single datetime field>    ::=
         <non-second primary datetime field> [ <left paren> <interval leading field precision> <right paren> ]
     |     SECOND [ <left paren> <interval leading field precision> [ <comma> <interval fractional seconds precision> ] <right paren> ]

<primary datetime field>    ::=
         <non-second primary datetime field>
     |     SECOND

<non-second primary datetime field> ::= YEAR | MONTH | DAY | HOUR | MINUTE

10.2 <language clause> (p469)

Specify a standard programming language.

<language clause> ::= LANGUAGE <language name>

Table 14 -- Standard programming languages

Language keyword	Relevant standard
ADA	ISO/IEC 8652
C	ISO/IEC 9899
COBOL	ISO 1989
FORTRAN	ISO 1539
MUMPS	ISO/IEC 11756
PASCAL	ISO/IEC 7185 and ISO/IEC 10206
PLI	ISO 6160
SQL	ISO/IEC 9075

10.3 <path specification> (p471)

Specify an order for searching for an SQL-invoked routine.

<schema name list> ::= <schema name> [ { <comma> <schema name> }... ]

10.4 <routine invocation> (p472)

Invoke an SQL-invoked routine.

10.5 <character set specification> (p495)

Identify a character set.

<implementation-defined character set name> ::= <character set name>

<user-defined character set name> ::= <character set name>

10.6 <specific routine designator> (p497)

Specify an SQL-invoked routine.

<specific routine designator>    ::=
         SPECIFIC <routine type> <specific name>
             |     <routine type> <member name>
         [ FOR <schema-resolved user-defined type name> ]

<member name> ::= <member name alternatives> [ <data type list> ]

10.7 <collate clause> (p500)

Specify a default collating sequence.

<collate clause> ::= COLLATE <collation name>

10.8 <constraint name definition> and <constraint characteristics> (p501)

Specify the name of a constraint and its characteristics.

<constraint name definition> ::= CONSTRAINT <constraint name>

<constraint characteristics>    ::=
         <constraint check time> [ [ NOT ] DEFERRABLE ]
     |     [ NOT ] DEFERRABLE [ <constraint check time> ]

<constraint check time> ::= INITIALLY DEFERRED | INITIALLY IMMEDIATE

10.9 <aggregate function> (p503)

Specify a value computed from a collection of rows.

<aggregate function>    ::=
         COUNT <left paren> <asterisk> <right paren> [ <filter clause> ]
     |     <general set function> [ <filter clause> ]
     |     <binary set function> [ <filter clause> ]
     |     <ordered set function> [ <filter clause> ]

<set quantifier> ::= DISTINCT | ALL

<filter clause> ::= FILTER <left paren> WHERE <search condition> <right paren>

<within group specification> ::= WITHIN GROUP <left paren> ORDER BY <sort specification list> <right paren>

<hypothetical set function value expression list> ::= <value expression> [ { <comma> <value expression> }... ]

<inverse distribution function type> ::= PERCENTILE_CONT | PERCENTILE_DISC

10.10 <sort specification list> (p515)

Specify a sort order.

<sort specification list> ::= <sort specification> [ { <comma> <sort specification> }... ]

<sort specification> ::= <sort key> [ <ordering specification> ] [ <null ordering> ]

<ordering specification> ::= ASC | DESC

<null ordering> ::= NULLS FIRST | NULLS LAST

Top

11 Schema definition and manipulation

11.1 <schema definition> (p517)

Define a schema.

<schema definition> ::= CREATE SCHEMA <schema name clause> [ <schema character set or path> ] [ <schema element> ... ]

<schema name clause>    ::=
         <schema name>
     |     AUTHORIZATION <schema authorization identifier>
     |     <schema name> AUTHORIZATION <schema authorization identifier>

<schema character set specification> ::= DEFAULT CHARACTER SET <character set specification>

11.2 <drop schema statement> (p520)

Destroy a schema.

<drop schema statement> ::= DROP SCHEMA <schema name> <drop behavior>

<drop behavior> ::= CASCADE | RESTRICT

11.3 <table definition> (p523)

Define a persistent base table, a created local temporary table, or a global temporary table.

<table definition>    ::=
         CREATE [ <table scope> ] TABLE <table name> <table contents source>
         [ ON COMMIT <table commit action> ROWS ]

<table scope> ::= <global or local> TEMPORARY

<global or local> ::= GLOBAL | LOCAL

<table commit action> ::= PRESERVE | DELETE

<self-referencing column specification> ::= REF IS <self-referencing column name> <reference generation>

<reference generation> ::= SYSTEM GENERATED | USER GENERATED | DERIVED

<self-referencing column name> ::= <column name>

<column options> ::= <column name> WITH OPTIONS <column option list>

<column option list> ::= [ <scope clause> ] [ <default clause> ] [ <column constraint definition> ... ]

<subtable clause> ::= UNDER <supertable clause>

<like clause> ::= LIKE <table name> [ <like options> ]

<identity option> ::= INCLUDING IDENTITY | EXCLUDING IDENTITY

<column default option> ::= INCLUDING DEFAULTS | EXCLUDING DEFAULTS

<with or without data> ::= WITH NO DATA | WITH DATA

11.4 <column definition> (p534)

Define a column of a base table.

<column definition>    ::=
         <column name> [ <data type> | <domain name> ] [ <reference scope check> ]
         [ <default clause> | <identity column specification> | <generation clause> ]
         [ <column constraint definition> ... ] [ <collate clause> ]

<column constraint definition> ::= [ <constraint name definition> ] <column constraint> [ <constraint characteristics> ]

<reference scope check> ::= REFERENCES ARE [ NOT ] CHECKED [ ON DELETE <reference scope check action> ]

<identity column specification>    ::=
         GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
         [ <left paren> <common sequence generator options> <right paren> ]

<generation rule> ::= GENERATED ALWAYS

11.5 <default clause> (p539)

Specify the default for a column, domain, or attribute.

<default clause> ::= DEFAULT <default option>

11.6 <table constraint definition> (p543)

Specify an integrity constraint.

<table constraint definition> ::= [ <constraint name definition> ] <table constraint> [ <constraint characteristics> ]

11.7 <unique constraint definition> (p545)

Specify a uniqueness constraint for a table.

<unique constraint definition>    ::=
         <unique specification> <left paren> <unique column list> <right paren>
     |     UNIQUE ( VALUE )

<unique specification> ::= UNIQUE | PRIMARY KEY

11.8 <referential constraint definition> (p547)

Specify a referential constraint.

<referential constraint definition> ::= FOREIGN KEY <left paren> <referencing columns> <right paren> <references specification>

<references specification> ::= REFERENCES <referenced table and columns> [ MATCH <match type> ] [ <referential triggered action> ]

<match type> ::= FULL | PARTIAL | SIMPLE

<referenced table and columns> ::= <table name> [ <left paren> <reference column list> <right paren> ]

<referential triggered action> ::= <update rule> [ <delete rule> ] | <delete rule> [ <update rule> ]

<update rule> ::= ON UPDATE <referential action>

<delete rule> ::= ON DELETE <referential action>

<referential action> ::= CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION

11.9 <check constraint definition> (p567)

Specify a condition for the SQL-data.

<check constraint definition> ::= CHECK <left paren> <search condition> <right paren>

11.10 <alter table statement> (p569)

Change the definition of a table.

<alter table statement> ::= ALTER TABLE <table name> <alter table action>

11.11 <add column definition> (p570)

Add a column to a table.

<add column definition> ::= ADD [ COLUMN ] <column definition>

11.12 <alter column definition> (p572)

Change a column and its definition.

<alter column definition> ::= ALTER [ COLUMN ] <column name> <alter column action>

11.13 <set column default clause> (p573)

Set the default clause for a column.

11.14 <drop column default clause> (p574)

Drop the default clause from a column.

<drop column default clause> ::= DROP DEFAULT

11.15 <add column scope clause> (p575)

Add a non-empty scope for an existing column of data type REF in a base table.

11.16 <drop column scope clause> (p576)

Drop the scope from an existing column of data type REF in a base table.

<drop column scope clause> ::= DROP SCOPE <drop behavior>

11.17 <alter identity column specification> (p578)

Change the options specified for an identity column.

<alter identity column specification> ::= <alter identity column option> ...

11.18 <drop column definition> (p579)

Destroy a column of a base table.

<drop column definition> ::= DROP [ COLUMN ] <column name> <drop behavior>

11.19 <add table constraint definition> (p581)

Add a constraint to a table.

11.20 <drop table constraint definition> (p582)

Destroy a constraint on a table.

<drop table constraint definition> ::= DROP CONSTRAINT <constraint name> <drop behavior>

11.21 <drop table statement> (p585)

Destroy a table.

<drop table statement> ::= DROP TABLE <table name> <drop behavior>

11.22 <view definition> (p588)

Define a viewed table.

<view definition>    ::=
         CREATE [ RECURSIVE ] VIEW <table name> <view specification> AS <query expression>
         [ WITH [ <levels clause> ] CHECK OPTION ]

<regular view specification> ::= [ <left paren> <view column list> <right paren> ]

<referenceable view specification> ::= OF <path-resolved user-defined type name> [ <subview clause> ] [ <view element list> ]

<subview clause> ::= UNDER <table name>

<view column option> ::= <column name> WITH OPTIONS <scope clause>

<levels clause> ::= CASCADED | LOCAL

11.23 <drop view statement> (p598)

Destroy a view.

11.24 <domain definition> (p601)

Define a domain.

<domain definition>    ::=
         CREATE DOMAIN <domain name> [ AS ] <data type>
         [ <default clause> ] [ <domain constraint> ... ] [ <collate clause> ]

<domain constraint> ::= [ <constraint name definition> ] <check constraint definition> [ <constraint characteristics> ]

11.25 <alter domain statement> (p603)

Change a domain and its definition.

<alter domain statement> ::= ALTER DOMAIN <domain name> <alter domain action>

11.26 <set domain default clause> (p604)

Set the default value in a domain.

11.27 <drop domain default clause> (p605)

Remove the default clause of a domain.

<drop domain default clause> ::= DROP DEFAULT

11.28 <add domain constraint definition> (p606)

Add a constraint to a domain.

11.29 <drop domain constraint definition> (p607)

Destroy a constraint on a domain.

<drop domain constraint definition> ::= DROP CONSTRAINT <constraint name>

11.30 <drop domain statement> (p608)

Destroy a domain.

<drop domain statement> ::= DROP DOMAIN <domain name> <drop behavior>

11.31 <character set definition> (p610)

Define a character set.

<character set definition> ::=
CREATE CHARACTER SET <character set name> [ AS ] <character set source> [ <collate clause> ]

11.32 <drop character set statement> (p612)

Destroy a character set.

<drop character set statement> ::= DROP CHARACTER SET <character set name>

11.33 <collation definition> (p614)

Define a collating sequence.

<collation definition>    ::=
         CREATE COLLATION <collation name> FOR <character set specification>
         FROM <existing collation name> [ <pad characteristic> ]

<pad characteristic> ::= NO PAD | PAD SPACE

11.34 <drop collation statement> (p616)

Destroy a collating sequence.

<drop collation statement> ::= DROP COLLATION <collation name> <drop behavior>

11.35 <transliteration definition> (p618)

Define a character transliteration.

<transliteration definition>    ::=
         CREATE TRANSLATION <transliteration name> FOR <source character set specification>
         TO <target character set specification> FROM <transliteration source>

11.36 <drop transliteration statement> (p621)

Destroy a character transliteration.

<drop transliteration statement> ::= DROP TRANSLATION <transliteration name>

11.37 <assertion definition> (p623)

Specify an integrity constraint.

<assertion definition> ::=
CREATE ASSERTION <constraint name> CHECK <left paren> <search condition> <right paren> [ <constraint characteristics> ]

11.38 <drop assertion statement> (p625)

Destroy an assertion.

<drop assertion statement> ::= DROP ASSERTION <constraint name>

11.39 <trigger definition> (p627)

Define triggered SQL-statements.

<trigger definition>    ::=
         CREATE TRIGGER <trigger name> <trigger action time> <trigger event>
         ON <table name> [ REFERENCING <old or new values alias list> ]
         <triggered action>

<trigger action time> ::= BEFORE | AFTER

<trigger event> ::= INSERT | DELETE | UPDATE [ OF <trigger column list> ]

<triggered action>    ::=
         [ FOR EACH { ROW | STATEMENT } ]
         [ WHEN <left paren> <search condition> <right paren> ]
         <triggered SQL statement>

<triggered SQL statement>    ::=
         <SQL procedure statement>
     |     BEGIN ATOMIC { <SQL procedure statement> <semicolon> }... END

<old or new values alias list> ::= <old or new values alias> ...

<old or new values alias>    ::=
         OLD [ ROW ] [ AS ] <old values correlation name>
     |     NEW [ ROW ] [ AS ] <new values correlation name>
     |     OLD TABLE [ AS ] <old values table alias>
     |     NEW TABLE [ AS ] <new values table alias>

11.40 <drop trigger statement> (p631)

Destroy a trigger.

<drop trigger statement> ::= DROP TRIGGER <trigger name>

11.41 <user-defined type definition> (p632)

Define a user-defined type.

<user-defined type definition> ::= CREATE TYPE <user-defined type body>

<user-defined type body>    ::=
         <schema-resolved user-defined type name> [ <subtype clause> ]
         [ AS <representation> ] [ <user-defined type option list> ] [ <method specification list> ]

<user-defined type option list> ::= <user-defined type option> [ <user-defined type option> ... ]

<user-defined type option>    ::=
         <instantiable clause>
     |     <finality>
     |     <reference type specification>
     |     <ref cast option>
     |     <cast option>

<subtype clause> ::=
UNDER <supertype name>

<instantiable clause> ::= INSTANTIABLE | NOT INSTANTIABLE

<finality> ::= FINAL | NOT FINAL

<user-defined representation> ::= REF USING <predefined type>

<system-generated representation> ::= REF IS SYSTEM GENERATED

<ref cast option> ::= [ <cast to ref> ] [ <cast to type> ]

<cast to ref> ::= CAST <left paren> SOURCE AS REF <right paren> WITH <cast to ref identifier>

<cast to type> ::= CAST <left paren> REF AS SOURCE <right paren> WITH <cast to type identifier>

<cast option> ::= [ <cast to distinct> ] [ <cast to source> ]

<cast to distinct>    ::=
         CAST <left paren> SOURCE AS DISTINCT <right paren>
         WITH <cast to distinct identifier>

<cast to source>    ::=
         CAST <left paren> DISTINCT AS SOURCE <right paren>
         WITH <cast to source identifier>

<method specification list> ::= <method specification> [ { <comma> <method specification> }... ]

<original method specification> ::=
<partial method specification> [ SELF AS RESULT ] [ SELF AS LOCATOR ] [ <method characteristics> ]

<overriding method specification> ::= OVERRIDING <partial method specification>

<partial method specification>    ::=
         [ INSTANCE | STATIC | CONSTRUCTOR ] METHOD <method name> <SQL parameter declaration list>
         <returns clause> [ SPECIFIC <specific method name> ]

<method characteristics> ::= <method characteristic> ...

11.42 <attribute definition> (p648)

Define an attribute of a structured type.

<attribute definition> ::=
<attribute name> <data type> [ <reference scope check> ] [ <attribute default> ] [ <collate clause> ]

11.43 <alter type statement> (p650)

Change the definition of a user-defined type.

<alter type statement> ::= ALTER TYPE <schema-resolved user-defined type name> <alter type action>

11.44 <add attribute definition> (p651)

Add an attribute to a user-defined type.

<add attribute definition> ::= ADD ATTRIBUTE <attribute definition>

11.45 <drop attribute definition> (p653)

Destroy an attribute of a user-defined type.

<drop attribute definition> ::= DROP ATTRIBUTE <attribute name> RESTRICT

11.46 <add original method specification> (p655)

Add an original method specification to a user-defined type.

11.47 <add overriding method specification> (p661)

Add an overriding method specification to a user-defined type.

11.48 <drop method specification> (p666)

Remove a method specification from a user-defined type.

<drop method specification> ::= DROP <specific method specification designator> RESTRICT

<specific method specification designator> ::= [ INSTANCE | STATIC | CONSTRUCTOR ] METHOD <method name> <data type list>

11.49 <drop data type statement> (p670)

Destroy a user-defined type.

11.50 <SQL-invoked routine> (p673)

Define an SQL-invoked routine.

<SQL-invoked routine> ::= <schema routine>

<schema procedure> ::= CREATE <SQL-invoked procedure>

<schema function> ::= CREATE <SQL-invoked function>

<SQL-invoked procedure> ::=
PROCEDURE <schema qualified routine name> <SQL parameter declaration list> <routine characteristics> <routine body>

<SQL-invoked function> ::=
{ <function specification> | <method specification designator> } <routine body>

<SQL parameter declaration> ::= [ <parameter mode> ] [ <SQL parameter name> ] <parameter type> [ RESULT ]

<parameter mode> ::= IN | OUT | INOUT

<parameter type> ::= <data type> [ <locator indication> ]

<locator indication> ::= AS LOCATOR

<function specification>    ::=
         FUNCTION <schema qualified routine name>
         <SQL parameter declaration list> <returns clause> <routine characteristics> [ <dispatch clause> ]

<method specification designator>    ::=
         SPECIFIC METHOD <specific method name>
     |     [ INSTANCE | STATIC | CONSTRUCTOR ] METHOD <method name> <SQL parameter declaration list>
         [ <returns clause> ] FOR <schema-resolved user-defined type name>

<routine characteristics> ::= [ <routine characteristic> ... ]

<savepoint level indication> ::= NEW SAVEPOINT LEVEL | OLD SAVEPOINT LEVEL

<dynamic result sets characteristic> ::= DYNAMIC RESULT SETS <maximum dynamic result sets>

<parameter style clause> ::= PARAMETER STYLE <parameter style>

<dispatch clause> ::= STATIC DISPATCH

<returns clause> ::= RETURNS <returns type>

<returns table type> ::= TABLE <table function column list>

<result cast from type> ::= <data type> [ <locator indication> ]

<returns data type> ::= <data type> [ <locator indication> ]

<rights clause> ::= SQL SECURITY INVOKER | SQL SECURITY DEFINER

<external body reference>    ::=
         EXTERNAL [ NAME <external routine name> ] [ <parameter style clause> ]
         [ <transform group specification> ] [ <external security clause> ]

<external security clause>    ::=
         EXTERNAL SECURITY DEFINER
     |     EXTERNAL SECURITY INVOKER
     |     EXTERNAL SECURITY IMPLEMENTATION DEFINED

<parameter style> ::= SQL | GENERAL

<deterministic characteristic> ::= DETERMINISTIC | NOT DETERMINISTIC

<SQL-data access indication>    ::=
         NO SQL
     |     CONTAINS SQL
     |     READS SQL DATA
     |     MODIFIES SQL DATA

<null-call clause>    ::=
         RETURNS NULL ON NULL INPUT
     |     CALLED ON NULL INPUT

<transform group specification> ::= TRANSFORM GROUP { <single group specification> | <multiple group specification> }

<multiple group specification> ::= <group specification> [ { <comma> <group specification> }... ]

11.51 <alter routine statement> (p698)

Alter a characteristic of an SQL-invoked routine.

<alter routine statement> ::= ALTER <specific routine designator> <alter routine characteristics> <alter routine behavior>

<alter routine characteristics> ::= <alter routine characteristic> ...

<alter routine behavior> ::= RESTRICT

11.52 <drop routine statement> (p701)

Destroy an SQL-invoked routine.

11.53 <user-defined cast definition> (p703)

Define a user-defined cast.

<user-defined cast definition>    ::=
         CREATE CAST <left paren> <source data type> AS <target data type> <right paren>
         WITH <cast function> [ AS ASSIGNMENT ]

11.54 <drop user-defined cast statement> (p705)

Destroy a user-defined cast.

11.55 <user-defined ordering definition> (p707)

Define a user-defined ordering for a user-defined type.

<user-defined ordering definition> ::=
CREATE ORDERING FOR <schema-resolved user-defined type name> <ordering form>

<equals ordering form> ::= EQUALS ONLY BY <ordering category>

<full ordering form> ::= ORDER FULL BY <ordering category>

<relative category> ::= RELATIVE WITH <relative function specification>

<state category> ::= STATE [ <specific name> ]

11.56 <drop user-defined ordering statement> (p710)

Destroy a user-defined ordering method.

<drop user-defined ordering statement> ::=
DROP ORDERING FOR <schema-resolved user-defined type name> <drop behavior>

11.57 <transform definition> (p712)

Define one or more transform functions for a user-defined type.

<transform definition> ::= CREATE { TRANSFORM | TRANSFORMS } FOR <schema-resolved user-defined type name> <transform group> ...

<transform element list> ::= <transform element> [ <comma> <transform element> ]

11.58 <alter transform statement> (p715)

Change the definition of one or more transform groups.

<alter transform statement> ::=
ALTER {TRANSFORM | TRANSFORMS } FOR <schema-resolved user-defined type name> <alter group> ...

<alter transform action list> ::= <alter transform action> [ { <comma> <alter transform action> }... ]

11.59 <add transform element list> (p717)

Add a transform element (<to sql> and/or <from sql>) to an existing transform group.

11.60 <drop transform element list> (p719)

Remove a transform element (<to sql> and/or <from sql>) from a transform group.

<transform kind> ::= TO SQL | FROM SQL

11.61 <drop transform statement> (p721)

Remove one or more transform functions associated with a transform.

<drop transform statement> ::=
DROP { TRANSFORM | TRANSFORMS } <transforms to be dropped> FOR <schema-resolved user-defined type name> <drop behavior>

11.62 <sequence generator definition> (p724)

Define an external sequence generator.

<sequence generator definition> ::= CREATE SEQUENCE <sequence generator name> [ <sequence generator options> ]

<sequence generator options> ::= <sequence generator option> ...

<common sequence generator options> ::= <common sequence generator option> ...

<sequence generator start with option> ::= START WITH <sequence generator start value>

<sequence generator start value> ::= <signed numeric literal> <sequence generator increment by option> :: = INCREMENT BY <sequence generator increment>

<sequence generator maxvalue option> ::= MAXVALUE <sequence generator max value>
/* Nothing */ | NO MAXVALUE

<sequence generator minvalue option> ::= MINVALUE <sequence generator min value> | NO MINVALUE

<sequence generator cycle option> ::= CYCLE | NO CYCLE

11.63 <alter sequence generator statement> (p726)

Change the definition of an external sequence generator.

<alter sequence generator statement> ::=
ALTER SEQUENCE <sequence generator name> <alter sequence generator options>

<alter sequence generator options> ::= <alter sequence generator option> ...

<alter sequence generator restart option> ::= RESTART WITH <sequence generator restart value>

11.64 <drop sequence generator statement> (p727)

Destroy an external sequence generator.

<drop sequence generator statement> ::= DROP SEQUENCE <sequence generator name> <drop behavior>

Top

12 Access control

12.1 <grant statement> (p729)

Define privileges and role authorizations.

12.2 <grant privilege statement> (p734)

Define privileges.

<grant privilege statement>    ::=
         GRANT <privileges> TO <grantee> [ { <comma> <grantee> }... ]
         [ WITH HIERARCHY OPTION ] [ WITH GRANT OPTION ] [ GRANTED BY <grantor> ]

12.3 <privileges> (p737)

Specify privileges.

<privilege method list> ::= <specific routine designator> [ { <comma> <specific routine designator> }... ]

<grantee> ::= PUBLIC | <authorization identifier>

<grantor> ::= CURRENT_USER | CURRENT_ROLE

12.4 <role definition> (p741)

Define a role.

<role definition> ::= CREATE ROLE <role name> [ WITH ADMIN <grantor> ]

12.5 <grant role statement> (p742)

Define role authorizations.

<grant role statement>    ::=
         GRANT <role granted> [ { <comma> <role granted> }... ]
         TO <grantee> [ { <comma> <grantee> }... ] [ WITH ADMIN OPTION ] [ GRANTED BY <grantor> ]

12.6 <drop role statement> (p744)

Destroy a role.

12.7 <revoke statement> (p745)

Destroy privileges and role authorizations.

<revoke privilege statement>    ::=
         REVOKE [ <revoke option extension> ] <privileges> FROM <grantee> [ { <comma> <grantee> }... ]
         [ GRANTED BY <grantor> ] <drop behavior>

<revoke option extension> ::= GRANT OPTION FOR | HIERARCHY OPTION FOR

<revoke role statement>    ::=
         REVOKE [ ADMIN OPTION FOR ] <role revoked> [ { <comma> <role revoked> }... ]
         FROM <grantee> [ { <comma> <grantee> }... ] [ GRANTED BY <grantor> ] <drop behavior>

Top

13 SQL-client modules

13.1 <SQL-client module definition> (p763)

Define an SQL-client module.

<SQL-client module definition>    ::=
         <module name clause> <language clause> <module authorization clause>
         [ <module path specification> ]
         [ <module transform group specification> ]
         [ <module collation> ]
         [ <temporary table declaration> ... ]
         <module contents> ...

<module authorization clause>    ::=
         SCHEMA <schema name>
     |     AUTHORIZATION <module authorization identifier> [ FOR STATIC { ONLY | AND DYNAMIC } ]
     |     SCHEMA <schema name> AUTHORIZATION <module authorization identifier> [ FOR STATIC { ONLY | AND DYNAMIC } ]

<module collations> ::= <module collation specification> ...

<module collation specification> ::= COLLATION <collation name> [ FOR <character set specification list> ]

<character set specification list> ::= <character set specification> [ {<comma> <character set specification> }... ]

13.2 <module name clause> (p768)

Name an SQL-client module.

<module name clause> ::=
MODULE [ <SQL-client module name> ] [ <module character set specification> ]

<module character set specification> ::= NAMES ARE <character set specification>

13.3 <externally-invoked procedure> (p769)

Define an externally-invoked procedure.

<externally-invoked procedure>    ::=
         PROCEDURE <procedure name> <host parameter declaration list> <semicolon>
         <SQL procedure statement> <semicolon>

<host parameter data type> ::= <data type> [ <locator indication> ]

<status parameter> ::= SQLSTATE

13.4 Calls to an <externally-invoked procedure> (p772)

13.5 <SQL procedure statement> (p788)

Define all of the SQL-statements that are <SQL procedure statement>s.

13.6 Data type correspondences (p796)

Table 16 -- Data type correspondences for C

SQL Data Type	C Data Type
SQLSTATE	char, with length 6
CHARACTER (L)³	char, with length (L+1)*k¹
CHARACTER VARYING (L)³	char, with length (L+1)*k¹
CHARACTER LARGE OBJECT(L)	struct { long hvn³_reserved unsigned long hvn²_length char³ hvn²_data[L]; } hvn²
BINARY LARGE OBJECT(L)	struct { long hvn²_reserved unsigned long hvn²_length char hvn²_data[L]; } hvn²
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

¹ 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.
² hvn is the name of the host variable defined to correspond to the SQL data type
³ 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.

Top

14 Data manipulation

14.1 <declare cursor> (p807)

Define a cursor.

<declare cursor>    ::=
         DECLARE <cursor name> [ <cursor sensitivity> ] [ <cursor scrollability> ] CURSOR
         [ <cursor holdability> ] [ <cursor returnability> ] FOR <cursor specification>

<cursor sensitivity> ::= SENSITIVE | INSENSITIVE | ASENSITIVE

<cursor scrollability> ::= SCROLL | NO SCROLL

<cursor holdability> ::= WITH HOLD | WITHOUT HOLD

<cursor returnability> ::= WITH RETURN | WITHOUT RETURN

<cursor specification> ::= <query expression> [ <order by clause> ] [ <updatability clause> ]

<updatability clause> ::= FOR { READ ONLY | UPDATE [ OF <column name list> ] }

<order by clause> ::= ORDER BY <sort specification list>

14.2 <open statement> (p813)

Open a cursor.

14.3 <fetch statement> (p815)

Position a cursor on a specified row of a table and retrieve values from that row.

<fetch statement> ::=
FETCH [ [ <fetch orientation> ] FROM ] <cursor name> INTO <fetch target list>

<fetch orientation>    ::=
         NEXT
     |     PRIOR
     |     FIRST
     |     LAST
     |     { ABSOLUTE | RELATIVE } <simple value specification>

<fetch target list> ::= <target specification> [ { <comma> <target specification> }... ]

14.4 <close statement> (p820)

Close a cursor.

<close statement> ::= CLOSE <cursor name>

14.5 <select statement: single row> (p822)

Retrieve values from a specified row of a table.

<select statement: single row> ::=
SELECT [ <set quantifier> ] <select list> INTO <select target list> <table expression>

<select target list> ::= <target specification> [ { <comma> <target specification> }... ]

14.6 <delete statement: positioned> (p826)

Delete a row of a table.

<delete statement: positioned> ::= DELETE FROM <target table> WHERE CURRENT OF <cursor name>

14.7 <delete statement: searched> (p829)

Delete rows of a table.

<delete statement: searched> ::= DELETE FROM <target table> [ WHERE <search condition> ]

14.8 <insert statement> (p832)

Create new rows in a table.

<insert statement> ::= INSERT INTO <insertion target> <insert columns and source>

<override clause> ::= OVERRIDING USER VALUE | OVERRIDING SYSTEM VALUE

<from default> ::= DEFAULT VALUES

14.9 <merge statement> (p837)

Conditionally update rows of a table, or insert new rows into a table, or both.

<merge statement>    ::=
         MERGE INTO <target table> [ [ AS ] <merge correlation name> ]
         USING <table reference> ON <search condition> <merge operation specification>

<merge operation specification> ::= <merge when clause> ...

<merge when matched clause> ::= WHEN MATCHED THEN <merge update specification>

<merge when not matched clause> ::= WHEN NOT MATCHED THEN <merge insert specification>

<merge update specification> ::= UPDATE SET <set clause list>

<merge insert specification>    ::=
         INSERT [ <left paren> <insert column list> <right paren> ]
         [ <override clause> ] VALUES <merge insert value list>

14.10 <update statement: positioned> (p844)

Update a row of a table.

<update statement: positioned> ::= UPDATE <target table> SET <set clause list> WHERE CURRENT OF <cursor name>

14.11 <update statement: searched> (p847)

Update rows of a table.

<update statement: searched> ::= UPDATE <target table> SET <set clause list> [ WHERE <search condition> ]

14.12 <set clause list> (p851)

Specify a list of updates.

<set clause list> ::= <set clause> [ { <comma> <set clause> }... ]

14.13 <temporary table declaration> (p856)

Declare a declared local temporary table.

<temporary table declaration>    ::=
         DECLARE LOCAL TEMPORARY TABLE <table name> <table element list>
         [ ON COMMIT <table commit action> ROWS ]

14.14 <free locator statement> (p858)

Remove the association between a locator variable and the value that is represented by that locator.

<free locator statement> ::= FREE LOCATOR <locator reference> [ { <comma> <locator reference> }... ]

14.15 <hold locator statement> (p859)

Mark a locator variable as being holdable.

<hold locator statement> ::= HOLD LOCATOR <locator reference> [ { <comma> <locator reference> }... ]

14.16 Effect of deleting rows from base tables (p860)

14.17 Effect of deleting some rows from a derived table (p862)

14.18 Effect of deleting some rows from a viewed table (p864)

14.19 Effect of inserting tables into base tables (p865)

14.20 Effect of inserting a table into a derived table (p867)

14.21 Effect of inserting a table into a viewed table (p869)

14.22 Effect of replacing rows in base tables (p871)

14.23 Effect of replacing some rows in a derived table (p874)

14.24 Effect of replacing some rows in a viewed table (p877)

14.25 Execution of BEFORE triggers (p879)

14.26 Execution of AFTER triggers (p880)

14.27 Execution of triggers (p881)

Top

15 Control statements

15.1 <call statement> (p883)

Invoke an SQL-invoked routine.

15.2 <return statement> (p884)

Return a value from an SQL function.

<return statement> ::= RETURN <return value>

<return value> ::= <value expression> | NULL

Top

16 Transaction management

16.1 <start transaction statement> (p885)

Start an SQL-transaction and set its characteristics.

<start transaction statement> ::= START TRANSACTION [ <transaction mode> [ { <comma> <transaction mode> }...] ]

<transaction access mode> ::= READ ONLY | READ WRITE

<isolation level> ::= ISOLATION LEVEL <level of isolation>

<level of isolation> ::= READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE

<diagnostics size> ::= DIAGNOSTICS SIZE <number of conditions>

16.2 <set transaction statement> (p888)

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.

<set transaction statement> ::= SET [ LOCAL ] <transaction characteristics>

<transaction characteristics> ::= TRANSACTION <transaction mode> [ { <comma> <transaction mode> }... ]

16.3 <set constraints mode statement> (p890)

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.

<set constraints mode statement> ::= SET CONSTRAINTS <constraint name list> { DEFERRED | IMMEDIATE }

<constraint name list> ::= ALL | <constraint name> [ { <comma> <constraint name> }... ]

16.4 <savepoint statement> (p892)

Establish a savepoint.

<savepoint statement> ::= SAVEPOINT <savepoint specifier>

16.5 <release savepoint statement> (p893)

Destroy a savepoint.

<release savepoint statement> ::= RELEASE SAVEPOINT <savepoint specifier>

16.6 <commit statement> (p894)

Terminate the current SQL-transaction with commit.

<commit statement> ::= COMMIT [ WORK ] [ AND [ NO ] CHAIN ]

16.7 <rollback statement> (p896)

Terminate the current SQL-transaction with rollback, or rollback all actions affecting SQL-data and/or schemas since the establishment of a savepoint.

<rollback statement> ::= ROLLBACK [ WORK ] [ AND [ NO ] CHAIN ] [ <savepoint clause> ]

<savepoint clause> ::= TO SAVEPOINT <savepoint specifier>

Top

17 Connection management

17.1 <connect statement> (p899)

Establish an SQL-session.

<connect statement> ::= CONNECT TO <connection target>

<connection target>    ::=
         <SQL-server name> [ AS <connection name> ] [ USER <connection user name> ]
     |     DEFAULT

17.2 <set connection statement> (p902)

Select an SQL-connection from the available SQL-connections.

<set connection statement> ::= SET CONNECTION <connection object>

<connection object> ::= DEFAULT | <connection name>

17.3 <disconnect statement> (p904)

Terminate an SQL-connection.

<disconnect statement> ::= DISCONNECT <disconnect object>

<disconnect object> ::= <connection object> | ALL | CURRENT

Top

18 Session management

18.1 <set session characteristics statement> (p907)

Set one or more characteristics for the current SQL-session.

<set session characteristics statement> ::= SET SESSION CHARACTERISTICS AS <session characteristic list>

<session characteristic list> ::= <session characteristic> [ { <comma> <session characteristic> }... ]

18.2 <set session user identifier statement> (p908)

Set the SQL-session user identifier and the current user identifier of the current SQL-session context.

<set session user identifier statement> ::= SET SESSION AUTHORIZATION <value specification>

18.3 <set role statement> (p909)

Set the current role name for the current SQL-session context.

<role specification> ::= <value specification> | NONE

18.4 <set local time zone statement> (p911)

Set the default local time zone displacement for the current SQL-session.

<set time zone value> ::= <interval value expression> | LOCAL

18.5 <set catalog statement> (p912)

Set the default catalog name for unqualified <schema name>s in <preparable statement>s that are prepared in the current SQL-session by an <execute immediate statement> or a <prepare statement> and in <direct SQL statement>s that are invoked directly.

<catalog name characteristic> ::= CATALOG <value specification>

18.6 <set schema statement> (p913)

Set the default schema name for unqualified <schema qualified name>s in <preparable statement>s that are prepared in the current SQL-session by an <execute immediate statement> or a <prepare statement> and in <direct SQL statement>s that are invoked directly.

<schema name characteristic> ::= SCHEMA <value specification>

18.7 <set names statement> (p915)

Set the default character set name for <character string literal>s in <preparable statement>s that are prepared in the current SQL-session by an <execute immediate statement> or a <prepare statement> and in <direct SQL statement>s that are invoked directly.

<character set name characteristic> ::= NAMES <value specification>

18.8 <set path statement> (p916)

Set the SQL-path used to determine the subject routine of <routine invocation>s with unqualified <routine name>s in <preparable statement>s that are prepared in the current SQL-session by an <execute immediate statement> or a <prepare statement> and in <direct SQL statement>s, respectively, that are invoked directly. The SQL-path remains the current SQL-path of the SQLsession until another SQL-path is successfully set.

<SQL-path characteristic> ::= PATH <value specification>

18.9 <set transform group statement> (p917)

Set the group name that identifies the group of transform functions for mapping values of userdefined types to predefined data types.

<transform group characteristic>    ::=
         DEFAULT TRANSFORM GROUP <value specification>
     |     TRANSFORM GROUP FOR TYPE <path-resolved user-defined type name> <value specification>

18.10 <set session collation statement> (p918)

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.

<set session collation statement>    ::=
         SET COLLATION <collation specification> [ FOR <character set specification list> ]
     |     SET NO COLLATION [ FOR <character set specification list> ]