5 Lexical elements

5.1 <SQL terminal character>

Function

Define the terminal symbols of the SQL language and the elements of strings.

Format
<SQL terminal character> ::= <SQL language character>
<SQL language character> ::=
    <simple Latin letter>
  | <digit>
  | <SQL special character>
<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> ::= /
<reverse solidus> ::= \
<colon> ::= :
<semicolon> ::= ;
<less than operator> ::= <
<equals operator> ::= =
<greater than operator> ::= >
<question mark> ::= ?
<left bracket or trigraph> ::=
    <left bracket>
  | <left bracket trigraph>
<right bracket or trigraph> ::=
    <right bracket>
  | <right bracket trigraph>
<left bracket> ::= [
<left bracket trigraph> ::= ??(
<right bracket> ::= [
<right bracket trigraph> ::= ??)
<circumflex> ::= ^
<underscore> ::= _
<vertical bar> ::= |
<left brace> ::= {
<right brace> ::= }

5.2 <token> and <separator>

Function

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

<regular identifier> ::= <identifier body>
<identifier body> ::= <identifier start> [ <identifier part>... ]
<identifier part> ::=
    <identifier start>
  | <identifier extend>
<identifier start> ::= !! See the Syntax Rules
<identifier extend> ::= !! See the Syntax Rules
<large object length token> ::= <digit>... <multiplier>
<multiplier> ::=
    K
  | M
  | G
<delimited identifier> ::= <double quote> <delimited identifier body> <double quote>
<delimited identifier body> ::= <delimited identifier part>...
<delimited identifier part> ::=
    <nondoublequote character>
  | <doublequote symbol>
<Unicode escape specifier> ::= [ UESCAPE <quote><Unicode escape character><quote> ]
<Unicode delimiter body> ::= <Unicode identifier part>...
<Unicode identifier part> ::=
    <delimited identifier part>
  | <Unicode escape value>
<Unicode 4 digit escape value> ::= <Unicode escape character><hexit><hexit><hexit><hexit>
<Unicode character escape value> ::= <Unicode escape character><Unicode escape character>
<Unicode escape character> ::= !! See the Syntax Rules
<nondoublequote character> ::= !! See the Syntax Rules
<doublequote symbol> ::= "" !! two consecutive double quote characters
<not equals operator> ::= <>
<greater than or equals operator> ::= >=
<less than or equals operator> ::= <=
<concatenation operator> ::= ||
<right arrow> ::= ->
<double colon> ::= ::
<double period> ::= ..
<separator> ::= { <comment> | <white space> }...
<white space> ::= !! See the Syntax Rules
<simple comment> ::= <simple comment introducer> [ <comment character>... ] <newline>
<simple comment introducer> ::= <minus sign><minus sign>
<bracketed comment introducer> ::= /*
<bracketed comment terminator> ::= */
<bracketed comment contents> ::= !! See the Syntax Rules
    [ { <comment character> | <separator> }... ]
<comment character> ::=
    <nonquote character>
  | <quote>
<newline> ::= !! See the Syntax Rules
<non-reserved word> ::=
    A | ABSOLUTE | ACTION | ADA | ADD | ADMIN | AFTER | ALWAYS | ASC
  | ASSERTION | ASSIGNMENT | ATTRIBUTE | ATTRIBUTES

  | BEFORE | BERNOULLI | BREADTH

  |   C | CASCADE | CATALOG | CATALOG_NAME | CHAIN | CHARACTER_SET_CATALOG
  |   CHARACTER_SET_NAME | CHARACTER_SET_SCHEMA | CHARACTERISTICS | CHARACTERS
  |   CLASS_ORIGIN | COBOL | COLLATION | COLLATION_CATALOG | COLLATION_NAME | COLLATION_SCHEMA
  |   COLUMN_NAME | COMMAND_FUNCTION | COMMAND_FUNCTION_CODE | COMMITTED
  |   CONDITION_NUMBER | CONNECTION | CONNECTION_NAME | CONSTRAINT_CATALOG | CONSTRAINT_NAME
  |   CONSTRAINT_SCHEMA | CONSTRAINTS | CONSTRUCTOR | CONTAINS | CONTINUE | CURSOR_NAME

  | DATA | DATETIME_INTERVAL_CODE | DATETIME_INTERVAL_PRECISION | DEFAULTS | DEFERRABLE
  | DEFERRED | DEFINED | DEFINER | DEGREE | DEPTH | DERIVED | DESC | DESCRIPTOR
  | DIAGNOSTICS | DISPATCH | DOMAIN | DYNAMIC_FUNCTION | DYNAMIC_FUNCTION_CODE

  | EQUALS | EXCEPTION | EXCLUDE | EXCLUDING

  | FINAL | FIRST | FOLLOWING | FORTRAN | FOUND

  | G | GENERAL | GENERATED | GO | GOTO | GRANTED

  | HIERARCHY

  | IMMEDIATE | IMPLEMENTATION | INCLUDING | INCREMENT | INITIALLY | INPUT | INSTANCE
  | INSTANTIABLE | INVOKER | ISOLATION

  | K | KEY | KEY_MEMBER | KEY_TYPE

  | LAST | LENGTH | LEVEL | LOCATOR

  | M | MAP | MATCHED | MAXVALUE | MESSAGE_LENGTH | MESSAGE_OCTET_LENGTH
  | MESSAGE_TEXT | MINVALUE | MORE | MUMPS

  | NAME | NAMES | NESTING | NEXT | NORMALIZED | NULLABLE | NULLS | NUMBER

  | OBJECT | OCTETS | OPTION | OPTIONS | ORDERING | ORDINALITY | OTHERS
  | OUTPUT | OVERRIDING

  |   PAD | PARAMETER_MODE | PARAMETER_NAME | PARAMETER_ORDINAL_POSITION
  |   PARAMETER_SPECIFIC_CATALOG | PARAMETER_SPECIFIC_NAME | PARAMETER_SPECIFIC_SCHEMA
  |   PARTIAL | PASCAL | PATH | PLACING | PLI | PRECEDING | PRESERVE | PRIOR
  |   PRIVILEGES | PUBLIC

  | READ | RELATIVE | REPEATABLE | RESTART | RESTRICT | RETURNED_CARDINALITY
  | RETURNED_LENGTH | RETURNED_OCTET_LENGTH | RETURNED_SQLSTATE | ROLE
  | ROUTINE | ROUTINE_CATALOG | ROUTINE_NAME | ROUTINE_SCHEMA | ROW_COUNT

  |   SCALE | SCHEMA | SCHEMA_NAME | SCOPE_CATALOG | SCOPE_NAME | SCOPE_SCHEMA
  |   SECTION | SECURITY | SELF | SEQUENCE | SERIALIZABLE | SERVER_NAME | SESSION
  |   SETS | SIMPLE | SIZE | SOURCE | SPACE | SPECIFIC_NAME | STATE | STATEMENT
  |   STRUCTURE | STYLE | SUBCLASS_ORIGIN

  | TABLE_NAME | TEMPORARY | TIES | TOP_LEVEL_COUNT | TRANSACTION
  | TRANSACTION_ACTIVE | TRANSACTIONS_COMMITTED | TRANSACTIONS_ROLLED_BACK
  | TRANSFORM | TRANSFORMS | TRIGGER_CATALOG | TRIGGER_NAME | TRIGGER_SCHEMA | TYPE

  | UNBOUNDED | UNCOMMITTED | UNDER | UNNAMED | USAGE | USER_DEFINED_TYPE_CATALOG
  | USER_DEFINED_TYPE_CODE | USER_DEFINED_TYPE_NAME | USER_DEFINED_TYPE_SCHEMA

  | VIEW

  | WORK | WRITE

  | ZONE
<reserved word> ::=
    ABS | ALL | ALLOCATE | ALTER | AND | ANY | ARE | ARRAY | AS | ASENSITIVE
  | ASYMMETRIC | AT | ATOMIC | AUTHORIZATION | AVG

  | BEGIN | BETWEEN | BIGINT | BINARY | BLOB | BOOLEAN | BOTH | BY

  |   CALL | CALLED | CARDINALITY | CASCADED | CASE | CAST | CEIL | CEILING
  |   CHAR | CHAR_LENGTH | CHARACTER | CHARACTER_LENGTH | CHECK | CLOB | CLOSE
  |   COALESCE | COLLATE | COLLECT | COLUMN | COMMIT | CONDITION | CONNECT
  |   CONSTRAINT | CONVERT | CORR | CORRESPONDING | COUNT | COVAR_POP | COVAR_SAMP
  |   CREATE | CROSS | CUBE | CUME_DIST | CURRENT | CURRENT_DATE
  |   CURRENT_DEFAULT_TRANSFORM_GROUP | CURRENT_PATH | CURRENT_ROLE | CURRENT_TIME
  |   CURRENT_TIMESTAMP | CURRENT_TRANSFORM_GROUP_FOR_TYPE | CURRENT_USER
  |   CURSOR | CYCLE

  | DATE | DAY | DEALLOCATE | DEC | DECIMAL | DECLARE | DEFAULT | DELETE
  | DENSE_RANK | DEREF | DESCRIBE | DETERMINISTIC | DISCONNECT | DISTINCT
  | DOUBLE | DROP | DYNAMIC

  | EACH | ELEMENT | ELSE | END | END-EXEC | ESCAPE | EVERY | EXCEPT | EXEC
  | EXECUTE | EXISTS | EXP | EXTERNAL | EXTRACT

  | FALSE | FETCH | FILTER | FLOAT | FLOOR | FOR | FOREIGN | FREE | FROM
  | FULL | FUNCTION | FUSION

  | GET | GLOBAL | GRANT | GROUP | GROUPING

  | HAVING | HOLD | HOUR

  | IDENTITY | IN | INDICATOR | INNER | INOUT | INSENSITIVE | INSERT
  | INT | INTEGER | INTERSECT | INTERSECTION | INTERVAL | INTO | IS

  | JOIN

  | LANGUAGE | LARGE | LATERAL | LEADING | LEFT | LIKE | LN | LOCAL
  | LOCALTIME | LOCALTIMESTAMP | LOWER

  | MATCH | MAX | MEMBER | MERGE | METHOD | MIN | MINUTE
  | MOD | MODIFIES | MODULE | MONTH | MULTISET

  | NATIONAL | NATURAL | NCHAR | NCLOB | NEW | NO | NONE | NORMALIZE | NOT
  | NULL | NULLIF | NUMERIC

  | OCTET_LENGTH | OF | OLD | ON | ONLY | OPEN | OR | ORDER | OUT | OUTER
  | OVER | OVERLAPS | OVERLAY

  | PARAMETER | PARTITION | PERCENT_RANK | PERCENTILE_CONT | PERCENTILE_DISC
  | POSITION | POWER | PRECISION | PREPARE | PRIMARY | PROCEDURE

  |   RANGE | RANK | READS | REAL | RECURSIVE | REF | REFERENCES | REFERENCING
  |   REGR_AVGX | REGR_AVGY | REGR_COUNT | REGR_INTERCEPT | REGR_R2 | REGR_SLOPE
  |   REGR_SXX | REGR_SXY | REGR_SYY | RELEASE | RESULT | RETURN | RETURNS
  |   REVOKE | RIGHT | ROLLBACK | ROLLUP | ROW | ROW_NUMBER | ROWS

  |   SAVEPOINT | SCOPE | SCROLL | SEARCH | SECOND | SELECT | SENSITIVE
  |   SESSION_USER | SET | SIMILAR | SMALLINT | SOME | SPECIFIC | SPECIFICTYPE
  |   SQL | SQLEXCEPTION | SQLSTATE | SQLWARNING | SQRT | START | STATIC
  |   STDDEV_POP | STDDEV_SAMP | SUBMULTISET | SUBSTRING | SUM | SYMMETRIC
  |   SYSTEM | SYSTEM_USER

  | TABLE | TABLESAMPLE | THEN | TIME | TIMESTAMP | TIMEZONE_HOUR | TIMEZONE_MINUTE
  | TO | TRAILING | TRANSLATE | TRANSLATION | TREAT | TRIGGER | TRIM | TRUE

  | UESCAPE | UNION | UNIQUE | UNKNOWN | UNNEST | UPDATE    | UPPER | USER | USING

  | VALUE | VALUES | VAR_POP | VAR_SAMP | VARCHAR | VARYING

  | WHEN | WHENEVER | WHERE | WIDTH_BUCKET | WINDOW | WITH | WITHIN | WITHOUT

  | YEAR

5.3 <literal>

Function

Specify a non-null value.

<introducer> ::= <underscore>
<character representation> ::=
    <nonquote character>
  | <quote symbol>
<nonquote character> ::= !! See the Syntax Rules.
<quote symbol> ::= <quote><quote>
<national character string literal> ::=
    N <quote> [ <character representation>... ]
    <quote> [ { <separator> <quote> [ <character representation>... ] <quote> }... ]
<Unicode representation> ::=
    <character representation>
  | <Unicode escape value>
<binary string literal> ::=
    X <quote> [ { <hexit> <hexit> }... ] <quote>
    [ { <separator> <quote> [ { <hexit> <hexit> }... ] <quote> }... ]
<hexit> ::=
    <digit> | A | B | C | D | E | F | a | b | c | d | e | f
<signed numeric literal> ::= [ <sign> ] <unsigned numeric literal>
<unsigned numeric literal> ::=
    <exact numeric literal>
  | <approximate numeric literal>
<sign> ::=
    <plus sign>
  | <minus sign>
<approximate numeric literal> ::= <mantissa> E <exponent>
<mantissa> ::= <exact numeric literal>
<exponent> ::= <signed integer>
<signed integer> ::= [ <sign> ] <unsigned integer>
<unsigned integer> ::= <digit>...
<date literal> ::= DATE <date string>
<time literal> ::= TIME <time string>
<timestamp literal> ::= TIMESTAMP <timestamp string>
<date string> ::= <quote> <unquoted date string> <quote>
<time string> ::= <quote> <unquoted time string> <quote>
<timestamp string> ::= <quote> <unquoted timestamp string> <quote>
<time zone interval> ::= <sign> <hours value> <colon> <minutes value>
<date value> ::= <years value> <minus sign> <months value> <minus sign> <days value>
<time value> ::= <hours value> <colon> <minutes value> <colon> <seconds value>
<interval literal> ::= INTERVAL [ <sign> ] <interval string> <interval qualifier>
<interval string> ::= <quote> <unquoted interval string> <quote>
<unquoted date string> ::= <date value>
<unquoted time string> ::= <time value> [ <time zone interval> ]
<unquoted timestamp string> ::= <unquoted date string> <space> <unquoted time string>
<unquoted interval string> ::=
    [ <sign> ] { <year-month literal> | <day-time literal> }
<year-month literal> ::=
    <years value> [ <minus sign> <months value> ]
  | <months value>
<day-time literal> ::=
    <day-time interval>
  | <time interval>
<years value> ::= <datetime value>
<months value> ::= <datetime value>
<days value> ::= <datetime value>
<hours value> ::= <datetime value>
<minutes value> ::= <datetime value>
<seconds value> ::= <seconds integer value> [ <period> [ <seconds fraction> ] ]
<seconds integer value> ::= <unsigned integer>
<seconds fraction> ::= <unsigned integer>
<datetime value> ::= <unsigned integer>
<boolean literal> ::=
    TRUE
  | FALSE
  | UNKNOWN

5.4 Names and identifiers

Function

Specify names.

Format
<identifier> ::= <actual identifier>
<SQL language identifier start> ::= <simple Latin letter>
<SQL language identifier part> ::=
    <simple Latin letter>
  | <digit>
  | <underscore>
<authorization identifier> ::=
    <role name>
  | <user identifier>
<table name> ::= <local or schema qualified name>
<domain name> ::= <schema qualified name>
<schema name> ::= [ <catalog name> <period> ] <unqualified schema name>
<unqualified schema name> ::= <identifier>
<catalog name> ::= <identifier>
<schema qualified name> ::= [ <schema name> <period> ] <qualified identifier>
<local or schema qualified name> ::=
    [ <local or schema qualifier> <period> ] <qualified identifier>
<local or schema qualifier> ::=
    <schema name>
  | <local qualifier>
<qualified identifier> ::= <identifier>
<column name> ::= <identifier>
<correlation name> ::= <identifier>
<query name> ::= <identifier>
<SQL-client module name> ::= <identifier>
<procedure name> ::= <identifier>
<schema qualified routine name> ::= <schema qualified name>
<method name> ::= <identifier>
<specific name> ::= <schema qualified name>
<cursor name> ::= <local qualified name>
<local qualified name> ::= [ <local qualifier> <period> ] <qualified identifier>
<local qualifier> ::= MODULE
<host parameter name> ::= <colon> <identifier>
<SQL parameter name> ::= <identifier>
<constraint name> ::= <schema qualified name>
<external routine name> ::=
    <identifier>
  | <character string literal>
<trigger name> ::= <schema qualified name>
<collation name> ::= <schema qualified name>
<character set name> ::= [ <schema name> <period> ] <SQL language identifier>
<transliteration name> ::= <schema qualified name>
<transcoding name> ::= <schema qualified name>
<schema-resolved user-defined type name> ::= <user-defined type name>
<user-defined type name> ::= [ <schema name> <period> ] <qualified identifier>
<attribute name> ::= <identifier>
<field name> ::= <identifier>
<savepoint name> ::= <identifier>
<sequence generator name> ::= <schema qualified name>
<role name> ::= <identifier>
<user identifier> ::= <identifier>
<connection name> ::= <simple value specification>
<SQL-server name> ::= <simple value specification>
<connection user name> ::= <simple value specification>
<SQL statement name> ::=
    <statement name>
  | <extended statement name>
<statement name> ::= <identifier>
<extended statement name> ::= [ <scope option> ] <simple value specification>
<dynamic cursor name> ::=
    <cursor name>
  | <extended cursor name>
<extended cursor name> ::= [ <scope option> ] <simple value specification>
<descriptor name> ::= [ <scope option> ] <simple value specification>
<scope option> ::=
    GLOBAL
  | LOCAL
<window name> ::= <identifier>

6 Scalar expressions

6.1 <data type>

Function

Specify a data type.

<character large object type> ::=
    CHARACTER LARGE OBJECT [ <left paren> <large object length> <right paren> ]
  | CHAR LARGE OBJECT [ <left paren> <large object length> <right paren> ]
  | CLOB [ <left paren> <large object length> <right paren> ]
<national character string type> ::=
    NATIONAL CHARACTER [ <left paren> <length> <right paren> ]
  | NATIONAL CHAR [ <left paren> <length> <right paren> ]
  | NCHAR [ <left paren> <length> <right paren> ]
  | NATIONAL CHARACTER VARYING <left paren> <length> <right paren>
  | NATIONAL CHAR VARYING <left paren> <length> <right paren>
  | NCHAR VARYING <left paren> <length> <right paren>
  | <national character large object type>
<national character large object type> ::=
    NATIONAL CHARACTER LARGE OBJECT [ <left paren> <large object length> <right paren> ]
  | NCHAR LARGE OBJECT [ <left paren> <large object length> <right paren> ]
  | NCLOB [ <left paren> <large object length> <right paren> ]
<binary large object string type> ::=
    BINARY LARGE OBJECT [ <left paren> <large object length> <right paren> ]
  | BLOB [ <left paren> <large object length> <right paren> ]
<exact numeric type> ::=
    NUMERIC [ <left paren> <precision> [ <comma> <scale> ] <right paren> ]
  | DECIMAL [ <left paren> <precision> [ <comma> <scale> ] <right paren> ]
  | DEC [ <left paren> <precision> [ <comma> <scale> ] <right paren> ]
  | SMALLINT
  | INTEGER
  | INT
  | BIGINT
<approximate numeric type> ::=
    FLOAT [ <left paren> <precision> <right paren> ]
  | REAL
  | DOUBLE PRECISION
<length> ::= <unsigned integer> [ <char length units> ]
<char length units> ::=
    CHARACTERS
  | OCTETS
<precision> ::= <unsigned integer>
<scale> ::= <unsigned integer>
<boolean type> ::= BOOLEAN
<with or without time zone> ::=
    WITH TIME ZONE
  | WITHOUT TIME ZONE
<time precision> ::= <time fractional seconds precision>
<timestamp precision> ::= <time fractional seconds precision>
<time fractional seconds precision> ::= <unsigned integer>
<interval type> ::= INTERVAL <interval qualifier>
<row type> ::= ROW <row type body>
<reference type> ::= REF <left paren> <referenced type> <right paren> [ <scope clause> ]
<scope clause> ::= SCOPE <table name>
<referenced type> ::= <path-resolved user-defined type name>
<path-resolved user-defined type name> ::= <user-defined type name>
<collection type> ::=
    <array type>
  | <multiset type>
<maximum cardinality> ::= <unsigned integer>
<multiset type> ::= <data type> MULTISET

6.2 <field definition>

Function

Define a field of a row type.

Format
<field definition> ::= <field name> <data type>

6.4 <value specification> and <target specification>

Function

Specify one or more values, host parameters, SQL parameters, dynamic parameters, or host variables.

Format
<value specification> ::=
    <literal>
  | <general value specification>
<unsigned value specification> ::=
    <unsigned literal>
  | <general value specification>
<general value specification> ::=
    <host parameter specification>
  | <SQL parameter reference>
  | <dynamic parameter specification>
  | <embedded variable specification>
  | <current collation specification>
  | CURRENT_DEFAULT_TRANSFORM_GROUP
  | CURRENT_PATH
  | CURRENT_ROLE
  | CURRENT_TRANSFORM_GROUP_FOR_TYPE <path-resolved user-defined type name>
  | CURRENT_USER
  | SESSION_USER
  | SYSTEM_USER
  | USER
  | VALUE
<host parameter specification> ::= <host parameter name> [ <indicator parameter> ]
<dynamic parameter specification> ::= <question mark>
<embedded variable specification> ::= <embedded variable name> [ <indicator variable> ]
<indicator variable> ::= [ INDICATOR ] <embedded variable name>
<indicator parameter> ::= [ INDICATOR ] <host parameter name>
<target array reference> ::=
    <SQL parameter reference>
  | <column reference>
<current collation specification> ::=
    COLLATION FOR <left paren> <string value expression> <right paren>

6.5 <contextually typed value specification>

Function

Specify a value whose data type is to be inferred from its context.

Format
<contextually typed value specification> ::=
    <implicitly typed value specification>
  | <default specification>
<implicitly typed value specification> ::=
    <null specification>
  | <empty specification>
<null specification> ::= NULL
<default specification> ::= DEFAULT

6.6 <identifier chain>

Function

Disambiguate a <period>-separated chain of identifiers.

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

6.7 <column reference>

Function

Reference a column.

6.8 <SQL parameter reference>

Function

Reference an SQL parameter.

Format
<SQL parameter reference> ::= <basic identifier chain>

6.9 <set function specification>

Function

Specify a value derived by the application of a function to an argument.

Format
<set function specification> ::=
    <aggregate function>
  | <grouping operation>
<grouping operation> ::=
    GROUPING <left paren> <column reference>
    [ { <comma> <column reference> }... ] <right paren>

6.10 <window function>

Function

Specify a window function.

Format
<window function> ::= <window function type> OVER <window name or specification>
<rank function type> ::=
    RANK
  | DENSE_RANK
  | PERCENT_RANK
  | CUME_DIST
<window name or specification> ::=
    <window name>
  | <in-line window specification>
<in-line window specification> ::= <window specification>

6.11 <case expression>

Function

Specify a conditional value.

Format
<case expression> ::=
    <case abbreviation>
  | <case specification>
<case specification> ::=
    <simple case>
  | <searched case>
<simple case> ::= CASE <case operand> <simple when clause>... [ <else clause> ] END
<searched case> ::= CASE <searched when clause>... [ <else clause> ] END
<simple when clause> ::= WHEN <when operand list> THEN <result>
<searched when clause> ::= WHEN <search condition> THEN <result>
<else clause> ::= ELSE <result>
<when operand list> ::= <when operand> [ { <comma> <when operand> }... ]
<result> ::=
    <result expression>
  | NULL
<result expression> ::= <value expression>

6.12 <cast specification>

Function

Specify a data conversion.

Format
<cast specification> ::= CAST <left paren> <cast operand> AS <cast target> <right paren>
<cast target> ::=
    <domain name>
  | <data type>

6.13 <next value expression>

Function

Return the next value of a sequence generator.

Format
<next value expression> ::= NEXT VALUE FOR <sequence generator name>

6.14 <field reference>

Function

Reference a field of a row value.

Format
<field reference> ::= <value expression primary> <period> <field name>

6.15 <subtype treatment>

Function

Modify the declared type of an expression.

Format
<subtype operand> ::= <value expression>

6.16 <method invocation>

Function

Reference an SQL-invoked method of a user-defined type value.

Format
<method invocation> ::=
    <direct invocation>
  | <generalized invocation>
<method selection> ::= <routine invocation>
<constructor method selection> ::= <routine invocation>

6.17 <static method invocation>

Function

Invoke a static method.

<static method selection> ::= <routine invocation>

6.18 <new specification>

Function

Invoke a method on a newly-constructed value of a structured type.

Format
<new invocation> ::=
    <method invocation>
  | <routine invocation>

6.19 <attribute or method reference>

Function

Return a value acquired by accessing a column of the row identified by a value of a reference type or by invoking an SQL-invoked method.

<dereference operator> ::= <right arrow>

6.20 <dereference operation>

Function

Access a column of the row identified by a value of a reference type.

6.21 <method reference>

Function

Return a value acquired from invoking an SQL-invoked routine that is a method.

6.22 <reference resolution>

Function

Obtain the value referenced by a reference value.

Format
<reference resolution> ::=
    DEREF <left paren> <reference value expression> <right paren>

6.23 <array element reference>

Function

Return an element of an array.

6.24 <multiset element reference>

Function

Return the sole element of a multiset of one element.

Format
<multiset element reference> ::=
    ELEMENT <left paren> <multiset value expression> <right paren>

6.25 <value expression>

Function

Specify a value.

<user-defined type value expression> ::= <value expression primary>
<reference value expression> ::= <value expression primary>
<collection value expression> ::=
    <array value expression>
  | <multiset value expression>

6.26 <numeric value expression>

Function

Specify a numeric value.

<factor> ::= [ <sign> ] <numeric primary>

6.27 <numeric value function>

Function

Specify a function yielding a value of type numeric.

<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>
<absolute value expression> ::= ABS <left paren> <numeric value expression> <right paren>
<numeric value expression dividend> ::= <numeric value expression>
<numeric value expression divisor> ::= <numeric value expression>
<natural logarithm> ::= LN <left paren> <numeric value expression> <right paren>
<exponential function> ::= EXP <left paren> <numeric value expression> <right paren>
<numeric value expression base> ::= <numeric value expression>
<numeric value expression exponent> ::= <numeric value expression>
<square root> ::= SQRT <left paren> <numeric value expression> <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 operand> ::= <numeric value expression>
<width bucket bound 1> ::= <numeric value expression>
<width bucket bound 2> ::= <numeric value expression>
<width bucket count> ::= <numeric value expression>

6.28 <string value expression>

Function

Specify a character string value or a binary string value.

Format
<string value expression> ::=
    <character value expression>
  | <blob value expression>
<character value expression> ::=
    <concatenation>
  | <character factor>
<concatenation> ::= <character value expression> <concatenation operator> <character factor>
<character factor> ::= <character primary> [ <collate clause> ]
<blob value expression> ::=
    <blob concatenation>
  | <blob factor>
<blob factor> ::= <blob primary>
<blob concatenation> ::= <blob value expression> <concatenation operator> <blob factor>

6.29 <string value function>

Function

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

Format
<string value function> ::=
    <character value function>
  | <blob value function>
<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>
<character transliteration> ::=
    TRANSLATE <left paren> <character value expression>
    USING <transliteration name> <right paren>
<trim function> ::= TRIM <left paren> <trim operands> <right paren>
<trim operands> ::= [ [ <trim specification> ] [ <trim character> ] FROM ] <trim source>
<trim source> ::= <character value expression>
<trim specification> ::=
    LEADING
  | TRAILING
  | BOTH
<trim character> ::= <character value expression>
<normalize function> ::= NORMALIZE <left paren> <character value expression> <right paren>
<specific type method> ::=
    <user-defined type value expression> <period> SPECIFICTYPE
    [ <left paren> <right paren> ]
<blob substring function> ::=
    SUBSTRING <left paren> <blob value expression> FROM <start position>
    [ FOR <string length> ] <right paren>
<blob trim function> ::= TRIM <left paren> <blob trim operands> <right paren>
<blob trim operands> ::=
    [ [ <trim specification> ] [ <trim octet> ] FROM ] <blob trim source>
<blob trim source> ::= <blob value expression>
<trim octet> ::= <blob value expression>
<start position> ::= <numeric value expression>
<string length> ::= <numeric value expression>

6.30 <datetime value expression>

Function

Specify a datetime value.

<datetime term> ::= <datetime factor>
<datetime factor> ::= <datetime primary> [ <time zone> ]
<time zone> ::= AT <time zone specifier>
<time zone specifier> ::=
    LOCAL
  | TIME ZONE <interval primary>

6.31 <datetime value function>

Function

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>

Function

Specify an interval value.

<interval factor> ::= [ <sign> ] <interval primary>
<interval value expression 1> ::= <interval value expression>
<interval term 1> ::= <interval term>
<interval term 2> ::= <interval term>

6.33 <interval value function>

Function

Specify a function yielding a value of type interval.

Format
<interval value function> ::= <interval absolute value function>
<interval absolute value function> ::=
    ABS <left paren> <interval value expression> <right paren>

6.34 <boolean value expression>

Function

Specify a boolean value.

Format
<boolean value expression> ::=
    <boolean term>
  | <boolean value expression> OR <boolean term>
<boolean factor> ::= [ NOT ] <boolean test>
<boolean test> ::= <boolean primary> [ IS [ NOT ] <truth value> ]
<truth value> ::=
    TRUE
  | FALSE
  | UNKNOWN
<boolean primary> ::=
    <predicate>
  | <boolean predicand>
<parenthesized boolean value expression> ::=
    <left paren> <boolean value expression> <right paren>

6.35 <array value expression>

Function

Specify an array value.

Format
<array value expression> ::=
    <array concatenation>
  | <array primary>
<array concatenation> ::= <array value expression 1> <concatenation operator> <array primary>
<array value expression 1> ::= <array value expression>
<array primary> ::= <value expression primary>

6.36 <array value constructor>

Function

Specify construction of an array.

<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 element> ::= <value expression>
<array value constructor by query> ::=
    ARRAY <left paren> <query expression> [ <order by clause> ] <right paren>

6.37 <multiset value expression>

Function

Specify a multiset value.

Format
<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>

Function

Specify a function yielding a value of a multiset type.

Format
<multiset value function> ::= <multiset set function>
<multiset set function> ::=
    SET <left paren> <multiset value expression> <right paren>

6.39 <multiset value constructor>

Function

Specify construction of a multiset.

<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 element> ::= <value expression>
<multiset value constructor by query> ::=
    MULTISET <left paren> <query expression> <right paren>
<table value constructor by query> ::=
    TABLE <left paren> <query expression> <right paren>

7 Query expressions

7.1 <row value constructor>

Function

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

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

7.2 <row value expression>

Function

Specify a row value.

Format
<table row value expression> ::=
    <row value special case>
  | <row value constructor>
<contextually typed row value expression> ::=
    <row value special case>
  | <contextually typed row value constructor>
<row value special case> ::= <nonparenthesized value expression primary>

7.3 <table value constructor>

Function

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

Format
<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>

Function

Specify a table or a grouped table.

Format

7.5 <from clause>

Function

Specify a table derived from one or more tables.

Format
<from clause> ::= FROM <table reference list>
<table reference list> ::=
    <table reference> [ { <comma> <table reference> }... ]

7.6 <table reference>

Function

Reference a table.

Format
<table reference> ::=
    <table factor>
  | <joined table>
<table factor> ::= <table primary> [ <sample clause> ]
<sample method> ::=
    BERNOULLI
  | SYSTEM
<repeatable clause> ::= REPEATABLE <left paren> <repeat argument> <right paren>
<sample percentage> ::= <numeric value expression>
<repeat argument> ::= <numeric value expression>
<only spec> ::= ONLY <left paren> <table or query name> <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>
<derived table> ::= <table subquery>
<table or query name> ::=
    <table name>
  | <transition table name>
  | <query name>
<derived column list> ::= <column name list>
<column name list> ::= <column name> [ { <comma> <column name> }... ]

7.7 <joined table>

Function

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

Format
<cross join> ::=
    <table reference> CROSS JOIN <table factor>
<natural join> ::=
    <table reference> NATURAL [ <join type> ] JOIN <table factor>
<join specification> ::=
    <join condition>
  | <named columns join>
<join condition> ::= ON <search condition>
<named columns join> ::= USING <left paren> <join column list> <right paren>
<join type> ::=
    INNER
  | <outer join type> [ OUTER ]
<outer join type> ::=
    LEFT
  | RIGHT
  | FULL
<join column list> ::= <column name list>

7.8 <where clause>

Function

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

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

7.9 <group by clause>

Function

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

Format
<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> }... ]
<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> }... ]
<empty grouping set> ::= <left paren> <right paren>

7.10 <having clause>

Function

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

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

7.11 <window clause>

Function

Specify one or more window definitions.

Format
<window clause> ::= WINDOW <window definition list>
<window definition list> ::=
    <window definition> [ { <comma> <window definition> }... ]
<window definition> ::= <new window name> AS <window specification>
<new window name> ::= <window name>
<existing window name> ::= <window name>
<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 units> ::=
    ROWS
  | RANGE
<window frame extent> ::=
  <window frame start>
| <window frame between>
<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 1> ::= <window frame bound>
<window frame bound 2> ::= <window frame bound>
<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>

Function

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

Format
<query specification> ::=
    SELECT [ <set quantifier> ] <select list> <table expression>
<select list> ::=
    <asterisk>
  | <select sublist> [ { <comma> <select sublist> }... ]
<select sublist> ::=
    <derived column>
  | <qualified asterisk>
<asterisked identifier chain> ::=
    <asterisked identifier> [ { <period> <asterisked identifier> }... ]
<asterisked identifier> ::= <identifier>
<derived column> ::= <value expression> [ <as clause> ]
<as clause> ::= [ AS ] <column name>
<all fields column name list> ::= <column name list>

7.13 <query expression>

Function

Specify a table.

Format
<query expression> ::=
    [ <with clause> ] <query expression body>
<with clause> ::=
    WITH [ RECURSIVE ] <with list>
<with list> ::=
    <with list element> [ { <comma> <with list element> }... ]
<with column list> ::= <column name list>
<query expression body> ::=
    <query term>
  | <query expression body> UNION [ ALL | DISTINCT ]
    [ <corresponding spec> ] <query term>
  | <query expression body> EXCEPT [ ALL | DISTINCT ]
    [ <corresponding spec> ] <query term>
<query term> ::=
    <query primary>
  | <query term> INTERSECT [ ALL | DISTINCT ]
    [ <corresponding spec> ] <query primary>
<explicit table> ::= TABLE <table or query name>
<corresponding spec> ::=
    CORRESPONDING [ BY <left paren> <corresponding column list> <right paren> ]
<corresponding column list> ::= <column name list>

7.14 <search or cycle clause>

Function

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

Format
<search clause> ::=
    SEARCH <recursive search order> SET <sequence column>
<recursive search order> ::=
    DEPTH FIRST BY <sort specification list>
  | BREADTH FIRST BY <sort specification list>
<sequence column> ::= <column name>
<cycle column list> ::=
    <cycle column> [ { <comma> <cycle column> }... ]
<cycle column> ::= <column name>
<cycle mark column> ::= <column name>
<path column> ::= <column name>
<cycle mark value> ::= <value expression>
<non-cycle mark value> ::= <value expression>

7.15 <subquery>

Function

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

Format
<scalar subquery> ::= <subquery>
<row subquery> ::= <subquery>
<table subquery> ::= <subquery>
<subquery> ::= <left paren> <query expression> <right paren>

8 Predicates

8.2 <comparison predicate>

Function

Specify a comparison of two row values.

Format
<comparison predicate> ::= <row value predicand> <comparison predicate part 2>
<comparison predicate part 2> ::= <comp op> <row value predicand>

8.3 <between predicate>

Function

Specify a range comparison.

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

8.4 <in predicate>

Function

Specify a quantified comparison.

Format
<in predicate> ::= <row value predicand> <in predicate part 2>
<in predicate part 2> ::= [ NOT ] IN <in predicate value>
<in value list> ::= <row value expression> [ { <comma> <row value expression> }... ]

8.5 <like predicate>

Function

Specify a pattern-match comparison.

Format
<character like predicate part 2> ::=
    [ NOT ] LIKE <character pattern> [ ESCAPE <escape character> ]
<character pattern> ::= <character value expression>
<escape character> ::= <character value expression>
<octet like predicate part 2> ::=
    [ NOT ] LIKE <octet pattern> [ ESCAPE <escape octet> ]
<octet pattern> ::= <blob value expression>
<escape octet> ::= <blob value expression>

8.6 <similar predicate>

Function

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

Format
<similar predicate part 2> ::=
    [ NOT ] SIMILAR TO <similar pattern> [ ESCAPE <escape character> ]
<similar pattern> ::= <character value expression>
<repeat factor> ::= <left brace> <low value> [ <upper limit> ] <right brace>
<upper limit> ::= <comma> [ <high value> ]
<low value> ::= <unsigned integer>
<high value> ::= <unsigned integer>
<character specifier> ::=
    <non-escaped character>
  | <escaped character>
<non-escaped character> ::= !! See the Syntax Rules
<escaped character> ::= !! See the Syntax Rules
<character enumeration include> ::= <character enumeration>
<character enumeration exclude> ::= <character enumeration>
<regular character set identifier> ::= <identifier>

8.7 <null predicate>

Function

Specify a test for a null value.

Format
<null predicate> ::= <row value predicand> <null predicate part 2>
<null predicate part 2> ::= IS [ NOT ] NULL

8.8 <quantified comparison predicate>

Function

Specify a quantified comparison.

Format
<quantified comparison predicate> ::=
    <row value predicand> <quantified comparison predicate part 2>
<quantified comparison predicate part 2> ::=
    <comp op> <quantifier> <table subquery>
<quantifier> ::=
    <all>
  | <some>
<all> ::= ALL
<some> ::=
    SOME
  | ANY

8.9 <exists predicate>

Function

Specify a test for a non-empty set.

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

8.10 <unique predicate>

Function

Specify a test for the absence of duplicate rows.

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

8.11 <normalized predicate>

Function

Determine whether a character string value is normalized.

Format
<normalized predicate> ::= <row value predicand> <normalized predicate part 2>
<normalized predicate part 2> ::= IS [ NOT ] NORMALIZED

8.12 <match predicate>

Function

Specify a test for matching rows.

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

8.13 <overlaps predicate>

Function

Specify a test for an overlap between two datetime periods.

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

8.14 <distinct predicate>

Function

Specify a test of whether two row values are distinct

Format
<distinct predicate part 2> ::=
    IS [ NOT ] DISTINCT FROM <row value predicand 4>
<row value predicand 3> ::= <row value predicand>
<row value predicand 4> ::= <row value predicand>

8.15 <member predicate>

Function

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

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

8.16 <submultiset predicate>

Function

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

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

8.17 <set predicate>

Function

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

Format
<set predicate> ::= <row value predicand> <set predicate part 2>
<set predicate part 2> ::= IS [ NOT ] A SET

8.18 <type predicate>

Function

Specify a type test.

Format
<type predicate part 2> ::=
    IS [ NOT ] OF <left paren> <type list> <right paren>
<inclusive user-defined type specification> ::=
    <path-resolved user-defined type name>
<exclusive user-defined type specification> ::=
    ONLY <path-resolved user-defined type name>

8.19 <search condition>

Function

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

Format
<search condition> ::= <boolean value expression>

10 Additional common elements

10.1 <interval qualifier>

Function

Specify the precision of an interval data type.

Format
<interval qualifier> ::=
    <start field> TO <end field>
  | <single datetime field>
<primary datetime field> ::=
    <non-second primary datetime field>
  | SECOND
<non-second primary datetime field> ::=
    YEAR
  | MONTH
  | DAY
  | HOUR
  | MINUTE
<interval fractional seconds precision> ::= <unsigned integer>
<interval leading field precision> ::= <unsigned integer>

10.2 <language clause>

Function

Specify a standard programming language.

Format
<language clause> ::= LANGUAGE <language name>
<language name> ::=
    ADA
  | C
  | COBOL
  | FORTRAN
  | M | MUMPS
  | PASCAL
  | PLI
  | SQL

10.3 <path specification>

Function

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

Format
<path specification> ::= PATH <schema name list>
<schema name list> ::= <schema name> [ { <comma> <schema name> }... ]

10.4 <routine invocation>

Function

Invoke an SQL-invoked routine.

Format
<routine invocation> ::= <routine name> <SQL argument list>
<routine name> ::= [ <schema name> <period> ] <qualified identifier>
<SQL argument list> ::=
    <left paren> [ <SQL argument> [ { <comma> <SQL argument> }... ] ] <right paren>

10.5 <character set specification>

Function

Identify a character set.

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

10.6 <specific routine designator>

Function

Specify an SQL-invoked routine.

Format
<routine type> ::=
    ROUTINE
  | FUNCTION
  | PROCEDURE
  | [ INSTANCE | STATIC | CONSTRUCTOR ] METHOD
<member name> ::= <member name alternatives> [ <data type list> ]
<member name alternatives> ::=
    <schema qualified routine name>
  | <method name>
<data type list> ::=
    <left paren> [ <data type> [ { <comma> <data type> }... ] ] <right paren>

10.7 <collate clause>

Function

Specify a default collation.

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

10.8 <constraint name definition> and <constraint characteristics>

Function

Specify the name of a constraint and its characteristics.

Format
<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>

Function

Specify a value computed from a collection of rows.

<set function type> ::= <computational operation>
<computational operation> ::=
    AVG
  | MAX
  | MIN
  | SUM
  | EVERY
  | ANY
  | SOME
  | COUNT
  | STDDEV_POP
  | STDDEV_SAMP
  | VAR_SAMP
  | VAR_POP
  | COLLECT
  | FUSION
  | INTERSECTION
<set quantifier> ::=
    DISTINCT
  | ALL
<filter clause> ::=
    FILTER <left paren> WHERE <search condition> <right paren>
<binary set function type> ::=
    COVAR_POP
  | COVAR_SAMP
  | CORR
  | REGR_SLOPE
  | REGR_INTERCEPT
  |   REGR_COUNT
  |   REGR_R2
  |   REGR_AVGX
  |   REGR_AVGY
  |   REGR_SXX
  |   REGR_SYY
  |   REGR_SXY
<dependent variable expression> ::= <numeric value expression>
<independent variable expression> ::= <numeric value expression>
<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 argument> ::= <numeric value expression>
<inverse distribution function type> ::=
    PERCENTILE_CONT
  | PERCENTILE_DISC

10.10 <sort specification list>

Function

Specify a sort order.

Format
<sort specification list> ::=
    <sort specification> [ { <comma> <sort specification> }... ]
<sort specification> ::=
    <sort key> [ <ordering specification> ] [ <null ordering> ]
<sort key> ::= <value expression>
<ordering specification> ::=
    ASC
  | DESC
<null ordering> ::=
    NULLS FIRST
  | NULLS LAST

11 Schema definition and manipulation

11.2 <drop schema statement>

Function

Destroy a schema.

Format
<drop schema statement> ::= DROP SCHEMA <schema name> <drop behavior>
<drop behavior> ::=
    CASCADE
  | RESTRICT

11.3 <table definition>

Function

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

Format
<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
<table element list> ::=
    <left paren> <table element> [ { <comma> <table element> }... ] <right paren>
<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>
<supertable clause> ::= <supertable name>
<supertable name> ::= <table name>
<like clause> ::= LIKE <table name> [ <like options> ]
<like options> ::= <like option>...
<identity option> ::=
    INCLUDING IDENTITY
  | EXCLUDING IDENTITY
<column default option> ::=
    INCLUDING DEFAULTS
  | EXCLUDING DEFAULTS
<generation option> ::=
    INCLUDING GENERATED
  | EXCLUDING GENERATED
<with or without data> ::=
    WITH NO DATA
  | WITH DATA

11.4 <column definition>

Function

Define a column of a base table.

<data type or domain name> ::=
    <data type>
  | <domain name>
<identity column specification> ::=
    GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
    [ <left paren> <common sequence generator options> <right paren> ]
<generation clause> ::= <generation rule> AS <generation expression>
<generation rule> ::= GENERATED ALWAYS
<generation expression> ::= <left paren> <value expression> <right paren>

11.5 <default clause>

Function

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

Format
<default clause> ::= DEFAULT <default option>
<default option> ::=
    <literal>
  | <datetime value function>
  | USER
  | CURRENT_USER
  | CURRENT_ROLE
  | SESSION_USER
  | SYSTEM_USER
  | CURRENT_PATH
  | <implicitly typed value specification>

11.6 <table constraint definition>

Function

Specify an integrity constraint.

Format

11.7 <unique constraint definition>

Function

Specify a uniqueness constraint for a table.

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

11.8 <referential constraint definition>

Function

Specify a referential constraint.

Format
<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
<referencing columns> ::= <reference column list>
<referenced table and columns> ::=
    <table name> [ <left paren> <reference column list> <right paren> ]
<reference column list> ::= <column name list>
<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>

Function

Specify a condition for the SQL-data.

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

11.10 <alter table statement>

Function

Change the definition of a table.

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

11.11 <add column definition>

Function

Add a column to a table.

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

11.12 <alter column definition>

Function

Change a column and its definition.

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

11.13 <set column default clause>

Function

Set the default clause for a column.

Format
<set column default clause> ::= SET <default clause>

11.14 <drop column default clause>

Function

Drop the default clause from a column.

Format
<drop column default clause> ::= DROP DEFAULT

11.15 <add column scope clause>

Function

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

Format
<add column scope clause> ::= ADD <scope clause>

11.16 <drop column scope clause>

Function

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

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

11.17 <alter identity column specification>

Function

Change the options specified for an identity column.

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

11.18 <drop column definition>

Function

Destroy a column of a base table.

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

11.19 <add table constraint definition>

Function

Add a constraint to a table.

Format
<add table constraint definition> ::= ADD <table constraint definition>

11.20 <drop table constraint definition>

Function

Destroy a constraint on a table.

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

11.21 <drop table statement>

Function

Destroy a table.

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

11.22 <view definition>

Function

Define a viewed table.

Format
<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 element list> ::=
    <left paren> <view element> [ { <comma> <view element> }... ] <right paren>
<view column option> ::= <column name> WITH OPTIONS <scope clause>
<levels clause> ::=
    CASCADED
  | LOCAL
<view column list> ::= <column name list>

11.23 <drop view statement>

Function

Destroy a view.

Format
<drop view statement> ::= DROP VIEW <table name> <drop behavior>

11.24 <domain definition>

Function

Define a domain.

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

11.25 <alter domain statement>

Function

Change a domain and its definition.

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

11.26 <set domain default clause>

Function

Set the default value in a domain.

Format
<set domain default clause> ::= SET <default clause>

11.27 <drop domain default clause>

Function

Remove the default clause of a domain.

Format
<drop domain default clause> ::= DROP DEFAULT

11.28 <add domain constraint definition>

Function

Add a constraint to a domain.

Format
<add domain constraint definition> ::= ADD <domain constraint>

11.29 <drop domain constraint definition>

Function

Destroy a constraint on a domain.

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

11.30 <drop domain statement>

Function

Destroy a domain.

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

11.31 <character set definition>

Function

Define a character set.

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

11.32 <drop character set statement>

Function

Destroy a character set.

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

11.33 <collation definition>

Function

Define a collation.

Format
<collation definition> ::=
    CREATE COLLATION <collation name> FOR <character set specification>
    FROM <existing collation name> [ <pad characteristic> ]
<existing collation name> ::= <collation name>
<pad characteristic> ::=
    NO PAD
  | PAD SPACE

11.34 <drop collation statement>

Function

Destroy a collation.

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

11.35 <transliteration definition>

Function

Define a character transliteration.

<source character set specification> ::= <character set specification>
<target character set specification> ::= <character set specification>
<existing transliteration name> ::= <transliteration name>
<transliteration routine> ::= <specific routine designator>

11.36 <drop transliteration statement>

Function

Destroy a character transliteration.

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

11.37 <assertion definition>

Function

Specify an integrity constraint.

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

11.38 <drop assertion statement>

Function

Destroy an assertion.

Format
<drop assertion statement> ::= DROP ASSERTION <constraint name> [ <drop behavior> ]

11.39 <trigger definition>

Function

Define triggered SQL-statements.

<trigger action time> ::=
    BEFORE
  | AFTER
<trigger event> ::=
    INSERT
  | DELETE
  | UPDATE [ OF <trigger column list> ]
<trigger column list> ::= <column name 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
<transition table or variable list> ::= <transition table or variable>...
<transition table or          variable> ::=
    OLD [ ROW ] [ AS          ] <old transition variable name>
  | NEW [ ROW ] [ AS          ] <new transition variable name>
  | OLD TABLE [ AS ]          <old transition table name>
  | NEW TABLE [ AS ]          <new transition table name>
<old transition table name> ::= <transition table name>
<new transition table name> ::= <transition table name>
<transition table name> ::= <identifier>
<old transition variable name> ::= <correlation name>
<new transition variable name> ::= <correlation name>

11.40 <drop trigger statement>

Function

Destroy a trigger.

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

11.41 <user-defined type definition>

Function

Define a user-defined type.

Format
<user-defined type definition> ::= CREATE TYPE <user-defined type body>
<user-defined type option list> ::=
    <user-defined type option> [ <user-defined type option>... ]
<subtype clause> ::= UNDER <supertype name>
<supertype name> ::= <path-resolved user-defined type name>
<representation> ::=
    <predefined type>
  | <member list>
<member list> ::= <left paren> <member> [ { <comma> <member> }... ] <right paren>
<member> ::= <attribute definition>
<instantiable clause> ::=
    INSTANTIABLE
  | NOT INSTANTIABLE
<finality> ::=
    FINAL
  | NOT FINAL
<user-defined representation> ::= REF USING <predefined type>
<derived representation> ::= REF FROM <list of attributes>
<system-generated representation> ::= REF IS SYSTEM GENERATED
<cast to ref> ::=
    CAST <left paren> SOURCE AS REF <right paren> WITH <cast to ref identifier>
<cast to ref identifier> ::= <identifier>
<cast to type> ::=
    CAST <left paren> REF AS SOURCE <right paren> WITH <cast to type identifier>
<cast to type identifier> ::= <identifier>
<cast to distinct> ::=
    CAST <left paren> SOURCE AS DISTINCT <right paren>
    WITH <cast to distinct identifier>
<cast to distinct identifier> ::= <identifier>
<cast to source> ::=
    CAST <left paren> DISTINCT AS SOURCE <right paren>
    WITH <cast to source identifier>
<cast to source identifier> ::= <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> ]
<specific method name> ::= [ <schema name> <period> ]<qualified identifier>
<method characteristics> ::= <method characteristic>...

11.42 <attribute definition>

Function

Define an attribute of a structured type.

Format
<attribute default> ::= <default clause>

11.43 <alter type statement>

Function

Change the definition of a user-defined type.

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

11.44 <add attribute definition>

Function

Add an attribute to a user-defined type.

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

11.45 <drop attribute definition>

Function

Destroy an attribute of a user-defined type.

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

11.46 <add original method specification>

Function

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

Format
<add original method specification> ::= ADD <original method specification>

11.47 <add overriding method specification>

Function

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

Format
<add overriding method specification> ::=
    ADD <overriding method specification>

11.48 <drop method specification>

Function

Remove a method specification from a user-defined type.

Format
<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>

Function

Destroy a user-defined type.

Format
<drop data type statement> ::=
    DROP TYPE <schema-resolved user-defined type name> <drop behavior>

11.50 <SQL-invoked routine>

Function

Define an SQL-invoked routine.

Format
<SQL-invoked routine> ::= <schema routine>
<schema routine> ::=
    <schema procedure>
  | <schema function>
<schema procedure> ::= CREATE <SQL-invoked procedure>
<schema function> ::= CREATE <SQL-invoked function>
<SQL parameter declaration list> ::=
    <left paren> [ <SQL parameter declaration>
    [ { <comma> <SQL parameter declaration> }... ] ] <right paren>
<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
<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>
<table function column list element> ::= <column name> <data type>
<result cast> ::= CAST FROM <result cast from type>
<result cast from type> ::= <data type> [ <locator indication> ]
<returns data type> ::= <data type> [ <locator indication> ]
<SQL routine spec> ::= [ <rights clause> ] <SQL routine body>
<rights clause> ::=
    SQL SECURITY INVOKER
  | SQL SECURITY DEFINER
<SQL routine body> ::= <SQL procedure statement>
<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
<maximum dynamic result sets> ::= <unsigned integer>
<transform group specification> ::=
    TRANSFORM GROUP { <single group specification> | <multiple group specification> }
<single group specification> ::= <group name>
<multiple group specification> ::=
    <group specification> [ { <comma> <group specification> }... ]
<group specification> ::=
    <group name> FOR TYPE <path-resolved user-defined type name>

11.51 <alter routine statement>

Function

Alter a characteristic of an SQL-invoked routine.

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

11.52 <drop routine statement>

Function

Destroy an SQL-invoked routine.

Format
<drop routine statement> ::= DROP <specific routine designator> <drop behavior>

11.53 <user-defined cast definition>

Function

Define a user-defined cast.

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

11.54 <drop user-defined cast statement>

Function

Destroy a user-defined cast.

Format
<drop user-defined cast statement> ::=
    DROP CAST <left paren> <source data type> AS <target data type> <right paren>
    <drop behavior>

11.55 <user-defined ordering definition>

Function

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

Format
<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>
<map category> ::= MAP WITH <map function specification>
<state category> ::= STATE [ <specific name> ]
<relative function specification> ::= <specific routine designator>
<map function specification> ::= <specific routine designator>

11.56 <drop user-defined ordering statement>

Function

Destroy a user-defined ordering method.

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

11.57 <transform definition>

Function

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

Format
<transform definition> ::=
    CREATE { TRANSFORM | TRANSFORMS } FOR
    <schema-resolved user-defined type name> <transform group>...
<group name> ::= <identifier>
<transform element list> ::= <transform element> [ <comma> <transform element> ]
<transform element> ::=
    <to sql>
  | <from sql>
<to sql> ::= TO SQL WITH <to sql function>
<from sql> ::= FROM SQL WITH <from sql function>
<to sql function> ::= <specific routine designator>
<from sql function> ::= <specific routine designator>

11.58 <alter transform statement>

Function

Change the definition of one or more transform groups.

Format
<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>

Function

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

Format
<add transform element list> ::=
    ADD <left paren> <transform element list> <right paren>

11.60 <drop transform element list>

Function

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

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

11.61 <drop transform statement>

Function

Remove one or more transform functions associated with a transform.

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

11.62 <sequence generator definition>

Function

Define an external sequence generator.

Format
<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 data type option> ::= AS <data type>
<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 increment> ::= <signed numeric literal>
<sequence generator maxvalue option> ::=
    MAXVALUE <sequence generator max value>
  | NO MAXVALUE
<sequence generator max value> ::= <signed numeric literal>
<sequence generator minvalue option> ::=
    MINVALUE <sequence generator min value>
  | NO MINVALUE
<sequence generator min value> ::= <signed numeric literal>
<sequence generator cycle option> ::=
    CYCLE
  | NO CYCLE

11.63 <alter sequence generator statement>

Function

Change the definition of an external sequence generator.

Format
<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>
<sequence generator restart value> ::= <signed numeric literal>

11.64 <drop sequence generator statement>

Function

Destroy an external sequence generator.

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

12 Access control

12.1 <grant statement>

Function

Define privileges and role authorizations.

Format

12.2 <grant privilege statement>

Function

Define privileges.

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

12.3 <privileges>

Function

Specify privileges.

Format
<privileges> ::= <object privileges> ON <object name>
<object privileges> ::=
    ALL PRIVILEGES
  | <action> [ { <comma> <action> }... ]
<privilege column list> ::= <column name list>
<grantee> ::=
    PUBLIC
  | <authorization identifier>
<grantor> ::=
    CURRENT_USER
  | CURRENT_ROLE

12.4 <role definition>

Function

Define a role.

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

12.5 <grant role statement>

Function

Define role authorizations.

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

12.6 <drop role statement>

Function

Destroy a role.

Format
<drop role statement> ::= DROP ROLE <role name>

12.7 <revoke statement>

Function

Destroy privileges and role authorizations.

Format
<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>
<role revoked> ::= <role name>

13 SQL-client modules

13.1 <SQL-client module definition>

Function

Define an SQL-client module.

<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 authorization identifier> ::= <authorization identifier>
<module path specification> ::= <path specification>
<module transform group specification> ::= <transform group specification>
<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>

Function

Name an SQL-client module.

Format
<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>

Function

Define an externally-invoked procedure.

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

13.5 <SQL procedure statement>

Function

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

Format
<SQL procedure statement> ::= <SQL executable statement>
<SQL control statement> ::=
    <call statement>
  | <return statement>
<SQL diagnostics statement> ::= <get diagnostics statement>

14 Data manipulation

14.1 <declare cursor>

Function

Define a cursor.

<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>

Function

Open a cursor.

Format
<open statement> ::= OPEN <cursor name>

14.3 <fetch statement>

Function

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

Format
<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>

Function

Close a cursor.

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

14.5 <select statement: single row>

Function

Retrieve values from a specified row of a table.

Format
<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>

Function

Delete a row of a table.

Format
<delete statement: positioned> ::=
    DELETE FROM <target table> [ [ AS ] <correlation name> ]
    WHERE CURRENT OF <cursor name>

14.7 <delete statement: searched>

Function

Delete rows of a table.

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

14.8 <insert statement>

Function

Create new rows in a table.

Format
<insert statement> ::=
    INSERT INTO <insertion target> <insert columns and source>
<insertion target> ::= <table name>
<insert columns and source> ::=
    <from subquery>
  | <from constructor>
  | <from default>
<override clause> ::=
    OVERRIDING USER VALUE
  | OVERRIDING SYSTEM VALUE
<from default> ::= DEFAULT VALUES
<insert column list> ::= <column name list>

14.9 <merge statement>

Function

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

<merge correlation name> ::= <correlation name>
<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>

Function

Update a row of a table.

Format
<update statement: positioned> ::=
    UPDATE <target table> [ [ AS ] <correlation name> ]
    SET <set clause list>
    WHERE CURRENT OF <cursor name>

14.11 <update statement: searched>

Function

Update rows of a table.

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

14.12 <set clause list>

Function

Specify a list of updates.

Format
<set clause list> ::= <set clause> [ { <comma> <set clause> }... ]
<multiple column assignment> ::=
    <set target list> <equals operator> <assigned row>
<set target list> ::=
    <left paren> <set target> [ { <comma> <set target> }... ] <right paren>
<assigned row> ::= <contextually typed row value expression>
<object column> ::= <column name>
<mutated set clause> ::= <mutated target> <period> <method name>
<mutated target> ::=
    <object column>
  | <mutated set clause>

14.13 <temporary table declaration>

Function

Declare a declared local temporary table.

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

14.14 <free locator statement>

Function

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

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

14.15 <hold locator statement>

Function

Mark a locator variable as being holdable.

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

15 Control statements

15.1 <call statement>

Function

Invoke an SQL-invoked routine.

Format
<call statement> ::= CALL <routine invocation>

15.2 <return statement>

Function

Return a value from an SQL function.

Format
<return statement> ::= RETURN <return value>
<return value> ::=
    <value expression>
  | NULL

16 Transaction management

16.1 <start transaction statement>

Function

Start an SQL-transaction and set its characteristics.

Format
<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>
<number of conditions> ::= <simple value specification>

16.2 <set transaction statement>

Function

Set the characteristics of the next SQL-transaction for the SQL-agent. NOTE 411 — This statement has no effect on any SQL-transactions subsequent to the next SQL-transaction.

Format
<set transaction statement> ::=
    SET [ LOCAL ] <transaction characteristics>
<transaction characteristics> ::=
    TRANSACTION <transaction mode> [ { <comma> <transaction mode> }... ]

16.3 <set constraints mode statement>

Function

If an SQL-transaction is currently active, then set the constraint mode for that SQL-transaction in the current SQL-session. If no SQL-transaction is currently active, then set the constraint mode for the next SQL-transaction in the current SQL-session for the SQL-agent. NOTE 413 — This statement has no effect on any SQL-transactions subsequent to this SQL-transaction.

Format
<set constraints mode statement> ::=
    SET CONSTRAINTS <constraint name list> { DEFERRED | IMMEDIATE }
<constraint name list> ::=
    ALL
  | <constraint name> [ { <comma> <constraint name> }... ]

16.4 <savepoint statement>

Function

Establish a savepoint.

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

16.5 <release savepoint statement>

Function

Destroy a savepoint.

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

16.6 <commit statement>

Function

Terminate the current SQL-transaction with commit.

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

16.7 <rollback statement>

Function

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

Format
<rollback statement> ::= ROLLBACK [ WORK ] [ AND [ NO ] CHAIN ] [ <savepoint clause> ]
<savepoint clause> ::= TO SAVEPOINT <savepoint specifier>

17 Connection management

17.1 <connect statement>

Function

Establish an SQL-session.

Format
<connect statement> ::= CONNECT TO <connection target>
<connection target> ::=
    <SQL-server name> [ AS <connection name> ] [ USER <connection user name> ]
  | DEFAULT

17.2 <set connection statement>

Function

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

Format
<set connection statement> ::= SET CONNECTION <connection object>
<connection object> ::=
    DEFAULT
  | <connection name>

17.3 <disconnect statement>

Function

Terminate an SQL-connection.

Format
<disconnect statement> ::= DISCONNECT <disconnect object>
<disconnect object> ::=
    <connection object>
  | ALL
  | CURRENT

18 Session management

18.1 <set session characteristics statement>

Function

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

Format
<set session characteristics statement> ::=
    SET SESSION CHARACTERISTICS AS <session characteristic list>
<session characteristic list> ::=
    <session characteristic> [ { <comma> <session characteristic> }... ]
<session characteristic> ::= <transaction characteristics>

18.2 <set session user identifier statement>

Function

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

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

18.3 <set role statement>

Function

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

Format
<set role statement> ::= SET ROLE <role specification>
<role specification> ::=
    <value specification>
  | NONE

18.4 <set local time zone statement>

Function

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

Format
<set local time zone statement> ::= SET TIME ZONE <set time zone value>
<set time zone value> ::=
    <interval value expression>
  | LOCAL

18.5 <set catalog statement>

Function

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.

Format
<set catalog statement> ::= SET <catalog name characteristic>
<catalog name characteristic> ::= CATALOG <value specification>

18.6 <set schema statement>

Function

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.
Format
<set schema statement> ::= SET <schema name characteristic>
<schema name characteristic> ::= SCHEMA <value specification>

18.7 <set names statement>

Function

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.

Format
<set names statement> ::= SET <character set name characteristic>
<character set name characteristic> ::= NAMES <value specification>

18.8 <set path statement>

Function

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 SQL-session until another SQL-path is successfully set.

Format
<set path statement> ::= SET <SQL-path characteristic>
<SQL-path characteristic> ::= PATH <value specification>

18.9 <set transform group statement>

Function

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

Format
<set transform group statement> ::= SET <transform group characteristic>
<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>

Function

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.

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

19 Dynamic SQL

19.2 <allocate descriptor statement>

Function

Allocate an SQL descriptor area.

Format
<allocate descriptor statement> ::=
    ALLOCATE [ SQL ] DESCRIPTOR <descriptor name> [ WITH MAX <occurrences> ]
<occurrences> ::= <simple value specification>

19.3 <deallocate descriptor statement>

Function

Deallocate an SQL descriptor area.

Format
<deallocate descriptor statement> ::=
    DEALLOCATE [ SQL ] DESCRIPTOR <descriptor name>

19.4 <get descriptor statement>

Function

Get information from an SQL descriptor area.

Format
<get descriptor statement> ::=
    GET [ SQL ] DESCRIPTOR <descriptor name> <get descriptor information>
<header item name> ::=
    COUNT
  | KEY_TYPE
  | DYNAMIC_FUNCTION
  | DYNAMIC_FUNCTION_CODE
  | TOP_LEVEL_COUNT
<item number> ::= <simple value specification>
<simple target specification 1> ::= <simple target specification>
<simple target specification 2> ::= <simple target specification>
<descriptor item name> ::=
    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

19.5 <set descriptor statement>

Function

Set information in an SQL descriptor area.

Format
<set descriptor statement> ::=
    SET [ SQL ] DESCRIPTOR <descriptor name> <set descriptor information>
<simple value specification 1> ::= <simple value specification>
<simple value specification 2> ::= <simple value specification>

19.6 <prepare statement>

Function

Prepare a statement for execution.

Format
<attributes specification> ::= ATTRIBUTES <attributes variable>
<attributes variable> ::= <simple value specification>
<SQL statement variable> ::= <simple value specification>
<preparable SQL schema statement> ::= <SQL schema statement>
<preparable SQL transaction statement> ::= <SQL transaction statement>
<preparable SQL control statement> ::= <SQL control statement>
<preparable SQL session statement> ::= <SQL session statement>
<dynamic select statement> ::= <cursor specification>
<preparable implementation-defined statement> ::= !! See the Syntax Rules.

19.7 <cursor attributes>

Function

Specify a list of cursor attributes.

Format
<cursor attributes> ::= <cursor attribute>...

19.8 <deallocate prepared statement>

Function

Deallocate SQL-statements that have been prepared with a <prepare statement>.

Format
<deallocate prepared statement> ::= DEALLOCATE PREPARE <SQL statement name>

19.9 <describe statement>

Function

Obtain information about the <select list> columns or <dynamic parameter specification>s contained in a prepared statement or about the columns of the result set associated with a cursor.

Format
<describe input statement> ::=
    DESCRIBE INPUT <SQL statement name> <using descriptor> [ <nesting option> ]
<describe output statement> ::=
    DESCRIBE [ OUTPUT ] <described object> <using descriptor> [ <nesting option> ]
<nesting option> ::=
    WITH NESTING
  | WITHOUT NESTING
<using descriptor> ::= USING [ SQL ] DESCRIPTOR <descriptor name>
<described object> ::=
    <SQL statement name>
  | CURSOR <extended cursor name> STRUCTURE

19.10 <input using clause>

Function

Supply input values for an <SQL dynamic statement>.

Format
<input using clause> ::=
    <using arguments>
  | <using input descriptor>
<using arguments> ::= USING <using argument> [ { <comma> <using argument> }... ]
<using argument> ::= <general value specification>
<using input descriptor> ::= <using descriptor>

19.11 <output using clause>

Function

Supply output variables for an <SQL dynamic statement>.

Format
<output using clause> ::=
    <into arguments>
  | <into descriptor>
<into arguments> ::= INTO <into argument> [ { <comma> <into argument> }... ]
<into argument> ::= <target specification>
<into descriptor> ::= INTO [ SQL ] DESCRIPTOR <descriptor name>

19.12 <execute statement>

Function

Associate input SQL parameters and output targets with a prepared statement and execute the statement.

Format
<execute statement> ::=
    EXECUTE <SQL statement name> [ <result using clause> ] [ <parameter using clause> ]
<result using clause> ::= <output using clause>
<parameter using clause> ::= <input using clause>

19.13 <execute immediate statement>

Function

Dynamically prepare and execute a preparable statement.

Format
<execute immediate statement> ::=
    EXECUTE IMMEDIATE <SQL statement variable>

19.14 <dynamic declare cursor>

Function

Declare a cursor to be associated with a <statement name>, which may in turn be associated with a <cursor specification>.

Format

19.15 <allocate cursor statement>

Function

Define a cursor based on a prepared statement for a <cursor specification> or assign a cursor to the ordered set of result sets returned from an SQL-invoked procedure.

Format
<allocate cursor statement> ::=
    ALLOCATE <extended cursor name> <cursor intent>
<cursor intent> ::=
    <statement cursor>
  | <result set cursor>
<result set cursor> ::= FOR PROCEDURE <specific routine designator>

19.16 <dynamic open statement>

Function

Associate input dynamic parameters with a <cursor specification> and open the cursor.

Format
<dynamic open statement> ::= OPEN <dynamic cursor name> [ <input using clause> ]

19.17 <dynamic fetch statement>

Function

Fetch a row for a cursor declared with a <dynamic declare cursor>.

Format
<dynamic fetch statement> ::=
    FETCH [ [ <fetch orientation> ] FROM ] <dynamic cursor name> <output using clause>

19.18 <dynamic single row select statement>

Function

Retrieve values from a dynamically-specified row of a table.

Format
<dynamic single row select statement> ::= <query specification>

19.19 <dynamic close statement>

Function

Close a cursor.

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

19.20 <dynamic delete statement: positioned>

Function

Delete a row of a table.

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

19.21 <dynamic update statement: positioned>

Function

Update a row of a table.

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

19.22 <preparable dynamic delete statement: positioned>

Function

Delete a row of a table through a dynamic cursor.

Format
<preparable dynamic delete statement: positioned> ::=
    DELETE [ FROM <target table> ]
    WHERE CURRENT OF [ <scope option> ] <cursor name>

19.23 <preparable dynamic update statement: positioned>

Function

Update a row of a table through a dynamic cursor.

Format
<preparable dynamic update statement: positioned> ::=
    UPDATE [ <target table> ] SET <set clause list>
    WHERE CURRENT OF [ <scope option> ] <cursor name>

20 Embedded SQL

20.1 <embedded SQL host program>

Function

Specify an <embedded SQL host program>.

<embedded SQL statement> ::=
    <SQL prefix> <statement or declaration> [ <SQL terminator> ]
<SQL prefix> ::=
    EXEC SQL
  | <ampersand>SQL<left paren>
<SQL terminator> ::=
    END-EXEC
  | <semicolon>
  | <right paren>
<embedded authorization declaration> ::= DECLARE <embedded authorization clause>
<embedded authorization clause> ::=
    SCHEMA <schema name>
  | AUTHORIZATION <embedded authorization identifier>
    [ FOR STATIC { ONLY | AND DYNAMIC } ]
  | SCHEMA <schema name> AUTHORIZATION <embedded authorization identifier>
    [ FOR STATIC { ONLY | AND DYNAMIC } ]
<embedded authorization identifier> ::=
    <module authorization identifier>
<embedded path specification> ::= <path specification>
<embedded transform group specification> ::=
    <transform group specification>
<embedded collation specification> ::= <module collations>
<embedded character set declaration> ::=
    SQL NAMES ARE <character set specification>
<embedded SQL begin declare> ::=
    <SQL prefix> BEGIN DECLARE SECTION [ <SQL terminator> ]
<embedded SQL end declare> ::=
    <SQL prefix> END DECLARE SECTION [ <SQL terminator> ]
<embedded SQL MUMPS declare> ::=
    <SQL prefix>
    BEGIN DECLARE SECTION
    [ <embedded character set declaration> ]
    [ <host variable definition>... ]
    END DECLARE SECTION
    <SQL terminator>
<embedded variable name> ::= <colon><host identifier>

20.2 <embedded exception declaration>

Function

Specify the action to be taken when an SQL-statement causes a specific class of condition to be raised.

Format
<embedded exception declaration> ::= WHENEVER <condition> <condition action>
<condition> ::= <SQL condition>
<SQL condition> ::=
    <major category>
  | SQLSTATE ( <SQLSTATE class value> [ , <SQLSTATE subclass value> ] )
  | CONSTRAINT <constraint name>
<major category> ::=
    SQLEXCEPTION
  | SQLWARNING
  | NOT FOUND
<SQLSTATE class value> ::=
    <SQLSTATE char><SQLSTATE char> !! See the Syntax Rules.
<SQLSTATE subclass value> ::=
    <SQLSTATE char><SQLSTATE char><SQLSTATE char> !! See the Syntax Rules.
<condition action> ::=
    CONTINUE
  | <go to>
<go to> ::= { GOTO | GO TO } <goto target>
<host label identifier> ::= !! See the Syntax Rules.

[[host-PL/I-label-variable]]

<host PL/I label variable> ::= !! See the Syntax Rules.

20.3 <embedded SQL Ada program>

Function

Specify an <embedded SQL Ada program>.

Format
<embedded SQL Ada program> ::= !! See the Syntax Rules.
<Ada assignment operator> ::= <colon><equals operator>
<Ada host identifier> ::= !! See the Syntax Rules.
<Ada qualified type specification> ::=
    Interfaces.SQL <period> CHAR
    [ CHARACTER SET [ IS ] <character set specification> ]
    <left paren> 1 <double period> <length> <right paren>
  | Interfaces.SQL <period> SMALLINT
  | Interfaces.SQL <period> INT
  | Interfaces.SQL <period> BIGINT
  | Interfaces.SQL <period> REAL
  | Interfaces.SQL <period> DOUBLE_PRECISION
  | Interfaces.SQL <period> BOOLEAN
  | Interfaces.SQL <period> SQLSTATE_TYPE
  | Interfaces.SQL <period> INDICATOR_TYPE
<Ada unqualified type specification> ::=
    CHAR <left paren> 1 <double period> <length> <right paren>
  | SMALLINT
  | INT
  | BIGINT
  | REAL
  | DOUBLE_PRECISION
  | BOOLEAN
  | SQLSTATE_TYPE
  | INDICATOR_TYPE
<Ada CLOB variable> ::=
    SQL TYPE IS CLOB <left paren> <large object length> <right paren>
    [ CHARACTER SET [ IS ] <character set specification> ]
<Ada CLOB locator variable> ::= SQL TYPE IS CLOB AS LOCATOR
<Ada BLOB variable> ::=
    SQL TYPE IS BLOB <left paren> <large object length> <right paren>
<Ada BLOB locator variable> ::= SQL TYPE IS BLOB AS LOCATOR
<Ada user-defined type variable> ::=
    SQL TYPE IS <path-resolved user-defined type name> AS <predefined type>
<Ada user-defined type locator variable> ::=
    SQL TYPE IS <path-resolved user-defined type name> AS LOCATOR
<Ada REF variable> ::= SQL TYPE IS <reference type>
<Ada array locator variable> ::= SQL TYPE IS <array type> AS LOCATOR
<Ada multiset locator variable> ::= SQL TYPE IS <multiset type> AS LOCATOR

20.4 <embedded SQL C program>

Function

Specify an <embedded SQL C program>.

Format
<embedded SQL C program> ::= !! See the Syntax Rules.
<C storage class> ::=
    auto
  | extern
  | static
<C class modifier> ::=
    const
  | volatile
<C numeric variable> ::=
    { long long | long | short | float | double }
    <C host identifier> [ <C initial value> ]
    [ { <comma> <C host identifier> [ <C initial value> ] }... ]
<C character type> ::=
    char
  | unsigned char
  | unsigned short
<C array specification> ::= <left bracket> <length> <right bracket>
<C host identifier> ::= !! See the Syntax Rules.
<C CLOB locator variable> ::=
    SQL TYPE IS CLOB AS LOCATOR
    <C host identifier> [ <C initial value> ]
    [ { <comma> <C host identifier> [
    <C initial value> ] } ... ]
<C BLOB locator variable> ::=
    SQL TYPE IS BLOB AS LOCATOR
    <C host identifier> [ <C initial value> ]
    [ { <comma> <C host identifier> [
    <C initial value> ] } ... ]
<C array locator variable> ::=
    SQL TYPE IS <array type> AS LOCATOR
    <C host identifier> [ <C initial value> ]
    [ { <comma> <C host identifier> [
    <C initial value> ] } ... ]
<C multiset locator variable> ::=
    SQL TYPE IS <multiset type> AS LOCATOR
    <C host identifier> [ <C initial value> ]
    [ { <comma> <C host identifier> [
    <C initial value> ] } ... ]
<C user-defined type locator variable> ::=
    SQL TYPE IS <path-resolved user-defined type name> AS LOCATOR
    <C host identifier> [ <C initial value> ]
    [ { <comma> <C host identifier> [
    <C initial value> ] }... ]

20.5 <embedded SQL COBOL program>

Function

Specify an <embedded SQL COBOL program>.

Format
<embedded SQL COBOL program> ::= !! See the Syntax Rules.
<COBOL host identifier> ::= !! See the Syntax Rules.
<COBOL character type> ::=
    [ CHARACTER SET [ IS ] <character set specification> ]
    { PIC | PICTURE } [ IS ] { X [ <left paren> <length> <right paren> ] }...
<COBOL national character type> ::=
    [ CHARACTER SET [ IS ] <character set specification> ]
    { PIC | PICTURE } [ IS ] { N [ <left paren> <length> <right paren> ] }...
<COBOL CLOB variable> ::=
    [ USAGE [ IS ] ] SQL TYPE IS CLOB <left paren> <large object length> <right paren>
    [ CHARACTER SET [ IS ] <character set specification> ]
<COBOL NCLOB variable> ::=
    [ USAGE [ IS ] ] SQL TYPE IS NCLOB <left paren> <large object length> <right paren>
    [ CHARACTER SET [ IS ] <character set specification> ]
<COBOL BLOB variable> ::=
    [ USAGE [ IS ] ] SQL TYPE IS BLOB <left paren> <large object length> <right paren>
<COBOL user-defined type variable> ::=
    [ USAGE [ IS ] ] SQL TYPE IS <path-resolved user-defined type name>
    AS <predefined type>
<COBOL CLOB locator variable> ::=
    [ USAGE [ IS ] ] SQL TYPE IS CLOB AS LOCATOR
<COBOL BLOB locator variable> ::=
    [ USAGE [ IS ] ] SQL TYPE IS BLOB AS LOCATOR
<COBOL array locator variable> ::=
    [ USAGE [ IS ] ] SQL TYPE IS <array type> AS LOCATOR
<COBOL multiset locator variable> ::=
    [ USAGE [ IS ] ] SQL TYPE IS <multiset type> AS LOCATOR
<COBOL user-defined type locator variable> ::=
    [ USAGE [ IS ] ] SQL TYPE IS <path-resolved user-defined type name> AS LOCATOR
<COBOL REF variable> ::=
    [ USAGE [ IS ] ] SQL TYPE IS <reference type>
<COBOL numeric type> ::=
    { PIC | PICTURE } [ IS ] S <COBOL nines specification>
    [ USAGE [ IS ] ] DISPLAY SIGN LEADING SEPARATE
<COBOL nines specification> ::=
    <COBOL nines> [ V [ <COBOL nines> ] ]
  | V <COBOL nines>
<COBOL integer type> ::= <COBOL binary integer>
<COBOL binary integer> ::=
    { PIC | PICTURE } [ IS ] S<COBOL nines>
    [ USAGE [ IS ] ] BINARY
<COBOL nines> ::= { 9 [ <left paren> <length> <right paren> ] }...

20.6 <embedded SQL Fortran program>

Function

Specify an <embedded SQL Fortran program>.

Format
<embedded SQL Fortran program> ::= !! See the Syntax Rules.
<Fortran host identifier> ::= !! See the Syntax Rules.
<Fortran type specification> ::=
    CHARACTER [ <asterisk> <length> ] [ CHARACTER SET
    [ IS ] <character set specification> ]
  | CHARACTER KIND = n [ <asterisk> <length> ]
    [ CHARACTER SET [ IS ] <character set specification> ]
  | INTEGER
  | REAL
  | DOUBLE PRECISION
  | LOGICAL
  | <Fortran derived type specification>
<Fortran CLOB variable> ::=
    SQL TYPE IS CLOB <left paren> <large object length> <right paren>
    [ CHARACTER SET [ IS ] <character set specification> ]
<Fortran BLOB variable> ::=
    SQL TYPE IS BLOB <left paren> <large object length> <right paren>
<Fortran user-defined type variable> ::=
    SQL TYPE IS <path-resolved user-defined type name> AS <predefined type>
<Fortran CLOB locator variable> ::=
    SQL TYPE IS CLOB AS LOCATOR
<Fortran BLOB locator variable> ::=
    SQL TYPE IS BLOB AS LOCATOR
<Fortran user-defined type locator variable> ::=
    SQL TYPE IS <path-resolved user-defined type name> AS LOCATOR
<Fortran array locator variable> ::=
    SQL TYPE IS <array type> AS LOCATOR
<Fortran multiset locator variable> ::=
    SQL TYPE IS <multiset type> AS LOCATOR
<Fortran REF variable> ::=
    SQL TYPE IS <reference type>

20.7 <embedded SQL MUMPS program>

Function

Specify an <embedded SQL MUMPS program>.

Format
<embedded SQL MUMPS program> ::= !! See the Syntax Rules.
<MUMPS host identifier> ::= !! See the Syntax Rules.
<MUMPS length specification> ::= <left paren> <length> <right paren>
<MUMPS type specification> ::=
    INT
  | DEC [ <left paren> <precision> [ <comma> <scale> ] <right paren> ]
  | REAL
<MUMPS CLOB variable> ::=
    SQL TYPE IS CLOB <left paren> <large object length> <right paren>
    [ CHARACTER SET [ IS ] <character set specification> ]
<MUMPS BLOB variable> ::=
    SQL TYPE IS BLOB <left paren> <large object length> <right paren>
<MUMPS user-defined type variable> ::=
    SQL TYPE IS <path-resolved user-defined type name> AS <predefined type>
<MUMPS CLOB locator variable> ::=
     SQL TYPE IS CLOB AS LOCATOR
<MUMPS BLOB locator variable> ::=
    SQL TYPE IS BLOB AS LOCATOR
<MUMPS user-defined type locator variable> ::=
    SQL TYPE IS <path-resolved user-defined type name> AS LOCATOR
<MUMPS array locator variable> ::=
    SQL TYPE IS <array type> AS LOCATOR
<MUMPS multiset locator variable> ::=
    SQL TYPE IS <multiset type> AS LOCATOR
<MUMPS REF variable> ::=
    SQL TYPE IS <reference type>

20.8 <embedded SQL Pascal program>

Function

Specify an <embedded SQL Pascal program>.

Format
<embedded SQL Pascal program> ::= !! See the Syntax Rules.
<Pascal host identifier> ::= !! See the Syntax Rules.
<Pascal type specification> ::=
    PACKED ARRAY <left bracket> 1 <double period> <length> <right bracket>
    OF CHAR [ CHARACTER SET [ IS ] <character set specification> ]
  | INTEGER
  | REAL
  | CHAR [ CHARACTER SET [ IS ] <character set specification> ]
  | BOOLEAN
  | <Pascal derived type specification>
<Pascal CLOB variable> ::=
    SQL TYPE IS CLOB <left paren> <large object length> <right paren>
    [ CHARACTER SET [ IS ] <character set specification> ]
<Pascal BLOB variable> ::=
    SQL TYPE IS BLOB <left paren> <large object length> <right paren>
<Pascal CLOB locator variable> ::=
    SQL TYPE IS CLOB AS LOCATOR
<Pascal user-defined type variable> ::=
    SQL TYPE IS <path-resolved user-defined type name> AS <predefined type>
<Pascal BLOB locator variable> ::=
    SQL TYPE IS BLOB AS LOCATOR
<Pascal user-defined type locator variable> ::=
    SQL TYPE IS <path-resolved user-defined type name> AS LOCATOR
<Pascal array locator variable> ::=
    SQL TYPE IS <array type> AS LOCATOR
<Pascal multiset locator variable> ::=
    SQL TYPE IS <multiset type> AS LOCATOR
<Pascal REF variable> ::=
    SQL TYPE IS <reference type>

20.9 <embedded SQL PL/I program>

Function

Specify an <embedded SQL PL/I program>.

Format

[[embedded-SQL-PL/I-program]]

<embedded SQL PL/I program> ::= !! See the Syntax Rules.

[[PL/I-variable-definition]]

[[PL/I-host-identifier]]

<PL/I host identifier> ::= !! See the Syntax Rules.

[[PL/I-type-specification]]

[[PL/I-derived-type-specification]]

[[PL/I-CLOB-variable]]

<PL/I CLOB variable> ::=
    SQL TYPE IS CLOB <left paren> <large object length> <right paren>
    [ CHARACTER SET [ IS ] <character set specification> ]

[[PL/I-BLOB-variable]]

<PL/I BLOB variable> ::=
    SQL TYPE IS BLOB <left paren> <large object length> <right paren>

[[PL/I-user-defined-type-variable]]

<PL/I user-defined type variable> ::=
    SQL TYPE IS <path-resolved user-defined type name> AS <predefined type>

[[PL/I-CLOB-locator-variable]]

<PL/I CLOB locator variable> ::=
    SQL TYPE IS CLOB AS LOCATOR

[[PL/I-BLOB-locator-variable]]

<PL/I BLOB locator variable> ::=
    SQL TYPE IS BLOB AS LOCATOR

[[PL/I-user-defined-type-locator-variable]]

<PL/I user-defined type locator variable> ::=
     SQL TYPE IS <path-resolved user-defined type name> AS LOCATOR

[[PL/I-array-locator-variable]]

<PL/I array locator variable> ::=
    SQL TYPE IS <array type> AS LOCATOR

[[PL/I-multiset-locator-variable]]

<PL/I multiset locator variable> ::=
    SQL TYPE IS <multiset type> AS LOCATOR

[[PL/I-REF-variable]]

<PL/I REF variable> ::=
    SQL TYPE IS <reference type>

[[PL/I-type-fixed-decimal]]

<PL/I type fixed decimal> ::=
    { DEC | DECIMAL } FIXED
  | FIXED { DEC | DECIMAL }

[[PL/I-type-fixed-binary]]

<PL/I type fixed binary> ::=
    { BIN | BINARY } FIXED
  | FIXED { BIN | BINARY }

[[PL/I-type-float-binary]]

<PL/I type float binary> ::=
    { BIN | BINARY } FLOAT
  | FLOAT { BIN | BINARY }

21 Direct invocation of SQL

21.1 <direct SQL statement>

Function

Specify direct execution of SQL.

Format
<direct SQL statement> ::= <directly executable statement> <semicolon>
<direct implementation-defined statement> ::= !! See the Syntax Rules

21.2 <direct select statement: multiple rows>

Function

Specify a statement to retrieve multiple rows from a specified table.

Format
<direct select statement: multiple rows> ::= <cursor specification>

22 Diagnostics management

22.1 <get diagnostics statement>

Function

Get exception or completion condition information from a diagnostics area.

Format
<get diagnostics statement> ::=
    GET DIAGNOSTICS <SQL diagnostics information>
<SQL diagnostics information> ::=
    <statement information>
  | <condition information>
<statement information> ::=
    <statement information item> [ { <comma> <statement information item> }... ]
<statement information item name> ::=
    NUMBER
  | MORE
  | COMMAND_FUNCTION
  | COMMAND_FUNCTION_CODE
  | DYNAMIC_FUNCTION
  | DYNAMIC_FUNCTION_CODE
  | ROW_COUNT
  | TRANSACTIONS_COMMITTED
  | TRANSACTIONS_ROLLED_BACK
  | TRANSACTION_ACTIVE
<condition information> ::=
    { EXCEPTION | CONDITION } <condition number> <condition information item>
    [ { <comma> <condition information item> }... ]
<condition information item name> ::=
    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
<condition number> ::= <simple value specification>