sql-2016-foundation-grammar.txt

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

<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

<SQL special character> ::=
      <space>
  |   <double quote>
  |   <percent>
  |   <ampersand>
  |   <quote>
  |   <left paren>
  |   <right paren>
  |   <asterisk>
  |   <plus sign>
  |   <comma>
  |   <minus sign>
  |   <period>
  |   <solidus>
  |   <colon>
  |   <semicolon>
  |   <less than operator>
  |   <equals operator>
  |   <greater than operator>
  |   <question mark>
  |   <left bracket>
  |   <right bracket>
  |   <circumflex>
  |   <underscore>
  |   <vertical bar>
  |   <left brace>
  |   <right brace>
  |   <dollar sign>
  |   <apostrophe>

<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> ::=
  }

<dollar sign> ::=
  $

<apostrophe> ::=
  '

5.2     <token> and <separator>

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


Format
<token> ::=
    <nondelimiter token>
  | <delimiter token>

<nondelimiter token> ::=
    <regular identifier>
  | <key word>
  | <unsigned numeric literal>
  | <national character string literal>
  | <binary string literal>
  | <large object length token>
  | <Unicode delimited identifier>
  | <Unicode character string literal>
  | <SQL language identifier>

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

<delimited identifier> ::=
  <double quote> <delimited identifier body> <double quote>

<delimited identifier body> ::=
  <delimited identifier part>...

<delimited identifier part> ::=
    <nondoublequote character>
  | <doublequote symbol>

<Unicode delimited identifier> ::=
  U <ampersand> <double quote> <Unicode delimiter body> <double quote>
      <Unicode escape specifier>

<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 escape value> ::=
    <Unicode 4 digit escape value>
  | <Unicode 6 digit escape value>
  | <Unicode character escape value>

<Unicode 4 digit escape value> ::=
  <Unicode escape character> <hexit> <hexit> <hexit> <hexit>

<Unicode 6 digit escape value> ::=
  <Unicode escape character> <plus sign>
      <hexit> <hexit> <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

<delimiter token> ::=
    <character string literal>
  | <date string>
  | <time string>
  | <timestamp string>
  | <interval string>
  | <delimited identifier>
  | <SQL special character>
  | <not equals operator>
  | <greater than or equals operator>
  | <less than or equals operator>
  | <concatenation operator>
  | <right arrow>
  | <left bracket trigraph>
  | <right bracket trigraph>
  | <double colon>
  | <double period>
  | <named argument assignment token>
  | <left brace minus>
  | <right minus brace>

<not equals operator> ::=
  <>

<greater than or equals operator> ::=
  >=

<less than or equals operator> ::=
  <=

<concatenation operator> ::=
  ||

<right arrow> ::=
  ->

<double colon> ::=
  ::

<double period> ::=
  ..

<named argument assignment token> ::=
  =>

<left brace minus> ::=
  {-

<right minus brace> ::=
  -}

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

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

<comment> ::=
    <simple comment>
  | <bracketed comment>

<simple comment> ::=
  <simple comment introducer> [ <comment character>... ] <newline>

<simple comment introducer> ::=
  <minus sign> <minus sign>

<bracketed comment> ::=
  <bracketed comment introducer>
      <bracketed comment contents>
      <bracketed comment terminator>

<bracketed comment introducer> ::=
  /*

<bracketed comment terminator> ::=
  */

<bracketed comment contents> ::=
  [ { <comment character> | <separator> }... ]!! See the Syntax Rules.

<comment character> ::=
    <nonquote character>
  | <quote>

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

<key word> ::=
    <reserved word>
  | <non-reserved word>

<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 | CHAINING | CHARACTER_SET_CATALOG
  | CHARACTER_SET_NAME | CHARACTER_SET_SCHEMA | CHARACTERISTICS | CHARACTERS
  | CLASS_ORIGIN | COBOL | COLLATION | COLLATION_CATALOG | COLLATION_NAME | COLLATION_SCHEMA
  | COLUMNS | COLUMN_NAME | COMMAND_FUNCTION | COMMAND_FUNCTION_CODE | COMMITTED
  | CONDITIONAL | CONDITION_NUMBER | CONNECTION | CONNECTION_NAME | CONSTRAINT_CATALOG
  | CONSTRAINT_NAME | CONSTRAINT_SCHEMA | CONSTRAINTS | CONSTRUCTOR
  | CONTINUE | CURSOR_NAME

  | DATA | DATETIME_INTERVAL_CODE | DATETIME_INTERVAL_PRECISION | DEFAULTS | DEFERRABLE
  | DEFERRED | DEFINED | DEFINER | DEGREE | DEPTH | DERIVED | DESC | DESCRIBE_CATALOG
  | DESCRIBE_NAME | DESCRIBE_PROCEDURE_SPECIFIC_CATALOG
  | DESCRIBE_PROCEDURE_SPECIFIC_NAME | DESCRIBE_PROCEDURE_SPECIFIC_SCHEMA
  | DESCRIBE_SCHEMA | DESCRIPTOR
  | DIAGNOSTICS | DISPATCH | DOMAIN | DYNAMIC_FUNCTION | DYNAMIC_FUNCTION_CODE

  | ENCODING | ENFORCED | ERROR | EXCLUDE | EXCLUDING | EXPRESSION

  | FINAL | FINISH | FINISH_CATALOG | FINISH_NAME | FINISH_PROCEDURE_SPECIFIC_CATALOG
  | FINISH_PROCEDURE_SPECIFIC_NAME | FINISH_PROCEDURE_SPECIFIC_SCHEMA
  | FINISH_SCHEMA | FIRST | FLAG | FOLLOWING | FORMAT | FORTRAN | FOUND | FULFILL
  | FULFILL_CATALOG | FULFILL_NAME | FULFILL_PROCEDURE_SPECIFIC_CATALOG
  | FULFILL_PROCEDURE_SPECIFIC_NAME | FULFILL_PROCEDURE_SPECIFIC_SCHEMA | FULFILL_SCHEMA

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

  | HAS_PASS_THROUGH_COLUMNS | HAS_PASS_THRU_COLS | HIERARCHY

  | IGNORE | IMMEDIATE | IMMEDIATELY | IMPLEMENTATION | INCLUDING | INCREMENT | INITIALLY
  | INPUT | INSTANCE | INSTANTIABLE | INSTEAD | INVOKER | ISOLATION | IS_PRUNABLE

  | JSON

  | K | KEEP | KEY | KEYS | KEY_MEMBER | KEY_TYPE

  | LAST | LENGTH | LEVEL | LOCATOR

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

  | NAME | NAMES | NESTED | NESTING | NEXT | NFC | NFD | NFKC | NFKD
  | NORMALIZED | NULLABLE | NULLS | NUMBER

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

  | P | PAD | PARAMETER_MODE | PARAMETER_NAME | PARAMETER_ORDINAL_POSITION
  | PARAMETER_SPECIFIC_CATALOG | PARAMETER_SPECIFIC_NAME | PARAMETER_SPECIFIC_SCHEMA
  | PARTIAL | PASCAL | PASS | PASSING | PAST | PATH | PLACING | PLAN | PLI
  | PRECEDING | PRESERVE | PRIOR | PRIVATE | PRIVATE_PARAMETERS | PRIVATE_PARAMS_S
  | PRIVILEGES | PRUNE | PUBLIC

  | QUOTES

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

  | SCALAR | 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 | START_CATALOG
  | START_NAME | START_PROCEDURE_SPECIFIC_CATALOG | START_PROCEDURE_SPECIFIC_NAME
  | START_PROCEDURE_SPECIFIC_SCHEMA | START_SCHEMA | STATE | STATEMENT
  | STRING | STRUCTURE | STYLE | SUBCLASS_ORIGIN

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

  | UNBOUNDED | UNCOMMITTED | UNCONDITIONAL | UNDER | UNNAMED | USAGE
  | USER_DEFINED_TYPE_CATALOG | USER_DEFINED_TYPE_CODE | USER_DEFINED_TYPE_NAME
  | USER_DEFINED_TYPE_SCHEMA | UTF16 | UTF32 | UTF8

  | VIEW

  | WORK | WRAPPER | WRITE

  | ZONE

<reserved word> ::=
    ABS | ACOS | ALL | ALLOCATE | ALTER | AND | ANY | ARE | ARRAY | ARRAY_AGG
  | ARRAY_MAX_CARDINALITY | AS | ASENSITIVE | ASIN | ASYMMETRIC | AT | ATAN
  | ATOMIC | AUTHORIZATION | AVG

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

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

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

  | EACH | ELEMENT | ELSE | EMPTY | END | END_FRAME | END_PARTITION | END-EXEC
  | EQUALS | ESCAPE | EVERY | EXCEPT | EXEC | EXECUTE | EXISTS | EXP
  | EXTERNAL | EXTRACT

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

  | GET | GLOBAL | GRANT | GROUP | GROUPING | GROUPS

  | HAVING | HOLD | HOUR

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

  | JOIN | JSON_ARRAY | JSON_ARRAYAGG | JSON_EXISTS | JSON_OBJECT
  | JSON_OBJECTAGG | JSON_QUERY | JSON_TABLE | JSON_TABLE_PRIMITIVE | JSON_VALUE

  | LAG | LANGUAGE | LARGE | LAST_VALUE | LATERAL | LEAD | LEADING | LEFT | LIKE
  | LIKE_REGEX | LISTAGG | LN | LOCAL | LOCALTIME | LOCALTIMESTAMP | LOG | LOG10 | LOWER

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

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

  | OCTET_LENGTH | OCCURRENCES_REGEX | OF | OFFSET | OLD | OMIT | ON | ONE
  | ONLY | OPEN | OR | ORDER | OUT | OUTER | OVER | OVERLAPS | OVERLAY

  | PARAMETER | PARTITION | PATTERN | PER | PERCENT | PERCENT_RANK
  | PERCENTILE_CONT | PERCENTILE_DISC | PERIOD | PORTION | POSITION | POSITION_REGEX
  | POWER | PRECEDES | PRECISION | PREPARE | PRIMARY | PROCEDURE | PTF

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

  | SAVEPOINT | SCOPE | SCROLL | SEARCH | SECOND | SEEK | SELECT | SENSITIVE
  | SESSION_USER | SET | SHOW | SIMILAR | SIN | SINH | SKIP | SMALLINT | SOME | SPECIFIC
  | SPECIFICTYPE | SQL | SQLEXCEPTION | SQLSTATE | SQLWARNING | SQRT | START
  | STATIC | STDDEV_POP | STDDEV_SAMP | SUBMULTISET | SUBSET | SUBSTRING
  | SUBSTRING_REGEX | SUCCEEDS | SUM | SYMMETRIC | SYSTEM | SYSTEM_TIME
  | SYSTEM_USER

  | TABLE | TABLESAMPLE | TAN | TANH | THEN | TIME | TIMESTAMP | TIMEZONE_HOUR
  | TIMEZONE_MINUTE | TO | TRAILING | TRANSLATE | TRANSLATE_REGEX | TRANSLATION | TREAT
  | TRIGGER | TRIM | TRIM_ARRAY | TRUE | TRUNCATE

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

  | VALUE | VALUES | VALUE_OF | VAR_POP | VAR_SAMP | VARBINARY
  | VARCHAR | VARYING | VERSIONING

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

  | YEAR

5.3     <literal>

Function
Specify a non-null value.


Format
<literal> ::=
    <signed numeric literal>
  | <general literal>

<unsigned literal> ::=
    <unsigned numeric literal>
  | <general literal>

<general literal> ::=
    <character string literal>
  | <national character string literal>
  | <Unicode character string literal>
  | <binary string literal>
  | <datetime literal>
  | <interval literal>
  | <boolean literal>

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

<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 character string literal> ::=
  [ <introducer> <character set specification> ]
      U <ampersand> <quote> [ <Unicode representation>... ] <quote>
      [ { <separator> <quote> [ <Unicode representation>... ] <quote> }... ]
      <Unicode escape specifier>

<Unicode representation> ::=
    <character representation>
  | <Unicode escape value>

<binary string literal> ::=
  X <quote> [ <space>... ] [ { <hexit> [ <space>... ] <hexit> [ <space>... ] }... ] <quote>
      [ { <separator> <quote> [ <space>... ] [ { <hexit> [ <space>... ]
      <hexit> [ <space>... ] }... ] <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>

<exact numeric literal> ::=
    <unsigned integer> [ <period> [ <unsigned integer> ] ]
  | <period> <unsigned integer>

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

<datetime literal> ::=
    <date literal>
  | <time literal>
  | <timestamp literal>

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

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

<time interval> ::=
    <hours value> [ <colon> <minutes value> [ <colon> <seconds value> ] ]
  | <minutes value> [ <colon> <seconds value> ]
  | <seconds value>

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

<actual identifier> ::=
    <regular identifier>
  | <delimited identifier>
  | <Unicode delimited identifier>

<SQL language identifier> ::=
  <SQL language identifier start> [ <SQL language identifier part>... ]

<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> ::=
    <conventional dynamic cursor name>
  | <PTF cursor name>

<conventional dynamic cursor name> ::=
    <cursor name>
  | <extended cursor name>

<extended cursor name> ::=
  [ <scope option> ] <simple value specification>

<PTF cursor name> ::=
  PTF <simple value specification>

<descriptor name> ::=
    <conventional descriptor name>
  | <PTF descriptor name>

<conventional descriptor name> ::=
    <non-extended descriptor name>
  | <extended descriptor name>

<non-extended descriptor name> ::=
  <identifier>

<extended descriptor name> ::=
  [ <scope option> ] <simple value specification>

<scope option> ::=
    GLOBAL
  | LOCAL

<PTF descriptor name> ::=
  PTF <simple value specification>

<window name> ::=
  <identifier>

<row pattern variable name> ::=
  <correlation name>

<measure name> ::=
  <identifier>

6 Scalar expressions


6.1     <data type>

Function
Specify a data type.


Format
<data type> ::=
    <predefined type>
  | <row type>
  | <path-resolved user-defined type name>
  | <reference type>
  | <collection type>

<predefined type> ::=
      <character string type> [ CHARACTER SET <character set specification> ]
          [ <collate clause> ]
  |   <national character string type> [ <collate clause> ]
  |   <binary string type>
  |   <numeric type>
  |   <boolean type>
  |   <datetime type>
  |   <interval type>

<character string type> ::=
    CHARACTER [ <left paren> <character length> <right paren> ]
  | CHAR [ <left paren> <character length> <right paren> ]
  | CHARACTER VARYING <left paren> <character length> <right paren>
  | CHAR VARYING <left paren> <character length> <right paren>
  | VARCHAR <left paren> <character length> <right paren>
  | <character large object type>

<character large object type> ::=
    CHARACTER LARGE OBJECT [ <left paren> <character large object length> <right paren> ]
  | CHAR LARGE OBJECT [ <left paren> <character large object length> <right paren> ]
  | CLOB [ <left paren> <character large object length> <right paren> ]

<national character string type> ::=
    NATIONAL CHARACTER [ <left paren> <character length> <right paren> ]
  | NATIONAL CHAR [ <left paren> <character length> <right paren> ]
  | NCHAR [ <left paren> <character length> <right paren> ]
  | NATIONAL CHARACTER VARYING <left paren> <character length> <right paren>
  | NATIONAL CHAR VARYING <left paren> <character length> <right paren>
  | NCHAR VARYING <left paren> <character length> <right paren>
  | <national character large object type>

<national character large object type> ::=
    NATIONAL CHARACTER LARGE OBJECT [ <left paren> <character large object length> <right
    paren> ]
  | NCHAR LARGE OBJECT [ <left paren> <character large object length> <right paren> ]
  | NCLOB [ <left paren> <character large object length> <right paren> ]

<binary string type> ::=
    BINARY [ <left paren> <length> <right paren> ]
  | BINARY VARYING <left paren> <length> <right paren>
  | VARBINARY <left paren> <length> <right paren>
  | <binary large object string type>

<binary large object string type> ::=
    BINARY LARGE OBJECT [ <left paren> <large object length> <right paren> ]
  | BLOB [ <left paren> <large object length> <right paren> ]

<numeric type> ::=
    <exact numeric type>
  | <approximate numeric type>
  | <decimal floating-point type>

<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

<decimal floating-point type> ::=
  DECFLOAT [ <left paren> <precision> <right paren> ]

<length> ::=
  <unsigned integer>

<character length> ::=
  <length> [ <char length units> ]

<large object length> ::=
    <unsigned integer> [ <multiplier> ]
  | <large object length token>

<character large object length> ::=
  <large object length> [ <char length units> ]

<char length units> ::=
    CHARACTERS
  | OCTETS

<precision> ::=
  <unsigned integer>

<scale> ::=
  <unsigned integer>

<boolean type> ::=
  BOOLEAN

<datetime type> ::=
    DATE
  | TIME [ <left paren> <time precision> <right paren> ] [ <with or without time zone> ]
  | TIMESTAMP [ <left paren> <timestamp precision> <right paren> ]
      [ <with or without time zone> ]

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

<row type body> ::=
  <left paren> <field definition> [ { <comma> <field definition> }... ] <right paren>

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

<array type> ::=
  <data type> ARRAY
      [ <left bracket or trigraph> <maximum cardinality> <right bracket or trigraph> ]

<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.3         <value expression primary>

Function
Specify a value that is syntactically self-delimited.


Format
<value expression primary> ::=
    <parenthesized value expression>
  | <nonparenthesized value expression primary>

<parenthesized value expression> ::=
  <left paren> <value expression> <right paren>

<nonparenthesized value expression primary> ::=
       <unsigned value specification>
     | <column reference>
     | <set function specification>
     | <window function>
     | <nested window function>
     | <scalar subquery>
     | <case expression>
     | <cast specification>
     | <field reference>
     | <subtype treatment>
     | <method invocation>
     | <static method invocation>
     | <new specification>
     | <attribute or method reference>
     | <reference resolution>
     | <collection value constructor>
     | <array element reference>
     | <multiset element reference>
     | <next value expression>
     | <routine invocation>
     | <row pattern navigation operation>
     | <JSON value function>

<collection value constructor> ::=
    <array value constructor>
  | <multiset value constructor>

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_CATALOG
  | CURRENT_DEFAULT_TRANSFORM_GROUP
  | CURRENT_PATH
  | CURRENT_ROLE
  | CURRENT_SCHEMA
  | CURRENT_TRANSFORM_GROUP_FOR_TYPE <path-resolved user-defined type name>
  | CURRENT_USER
  | SESSION_USER
  | SYSTEM_USER
  | USER
  | VALUE

<simple value specification> ::=
    <literal>
  | <host parameter name>
  | <SQL parameter reference>
  | <embedded variable name>

<target specification> ::=
    <host parameter specification>
  | <SQL parameter reference>
  | <column reference>
  | <target array element specification>
  | <dynamic parameter specification>
  | <embedded variable specification>

<simple target specification> ::=
    <host parameter name>
  | <SQL parameter reference>
  | <column reference>
  | <embedded variable name>

<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 element specification> ::=
  <target array reference>
      <left bracket or trigraph> <simple value specification> <right bracket or trigraph>

<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

<empty specification> ::=
    ARRAY <left bracket or trigraph> <right bracket or trigraph>
  | MULTISET <left bracket or trigraph> <right bracket or trigraph>

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


Format
<column reference> ::=
    <basic identifier chain>
  | MODULE <period> <qualified identifier> <period> <column name>

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> ::=
    [ <running or final> ] <aggregate function>
  | <grouping operation>

<running or final> ::=
  RUNNING | FINAL

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

<window function type> ::=
    <rank function type> <left paren> <right paren>
  | ROW_NUMBER <left paren> <right paren>
  | <aggregate function>
  | <ntile function>
  | <lead or lag function>
  | <first or last value function>
  | <nth value function>
  | <window row pattern measure>

<rank function type> ::=
    RANK
  | DENSE_RANK
  | PERCENT_RANK
  | CUME_DIST

<ntile function> ::=
  NTILE <left paren> <number of tiles> <right paren>

<number of tiles> ::=
    <simple value specification>
  | <dynamic parameter specification>

<lead or lag function> ::=
  <lead or lag> <left paren> <lead or lag extent>
      [ <comma> <offset> [ <comma> <default expression> ] ] <right paren>
      [ <null treatment> ]

<lead or lag> ::=
  LEAD | LAG

<lead or lag extent> ::=
  <value expression>

<offset> ::=
  <exact numeric literal>

<default expression> ::=
  <value expression>

<null treatment> ::=
  RESPECT NULLS | IGNORE NULLS

<first or last value function> ::=
  <first or last value> <left paren> <value expression> <right paren> [ <null treatment>
  ]

<first or last value> ::=
  FIRST_VALUE | LAST_VALUE

<nth value function> ::=
  NTH_VALUE <left paren> <value expression> <comma> <nth row> <right paren>
      [ <from first or last> ] [ <null treatment> ]

<nth row> ::=
    <simple value specification>
  | <dynamic parameter specification>

<from first or last> ::=
    FROM FIRST
  | FROM LAST

<window name or specification> ::=
    <window name>
  | <in-line window specification>

<in-line window specification> ::=
  <window specification>

<window row pattern measure> ::=
  <measure name>

6.11 <nested window function>

Function
Specify a function nested in an aggregated argument of an <aggregate function> simply contained in a <window
function>.


Format
<nested window function> ::=
    <nested row number function>
  | <value_of expression at row>

<nested row number function> ::=
  ROW_NUMBER <left paren> <row marker> <right paren>

<value_of expression at row> ::=
  VALUE_OF <left paren> <value expression> AT <row marker expression>
      [ <comma> <value_of default value> ] <right paren>

<row marker> ::=
    BEGIN_PARTITION
  | BEGIN_FRAME
  | CURRENT_ROW
  | FRAME_ROW
  | END_FRAME
  | END_PARTITION

<row marker expression> ::=
  <row marker> [ <row marker delta> ]

<row marker delta> ::=
    <plus sign> <row marker offset>
  | <minus sign> <row marker offset>

<row marker offset> ::=
    <simple value specification>
  | <dynamic parameter specification>

<value_of default value> ::=
  <value expression>

6.12 <case expression>

Function
Specify a conditional value.


Format
<case expression> ::=
    <case abbreviation>
  | <case specification>

<case abbreviation> ::=
    NULLIF <left paren> <value expression> <comma> <value expression> <right paren>
  | COALESCE <left paren> <value expression>
      { <comma> <value expression> }... <right paren>

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

<case operand> ::=
    <row value predicand>
  | <overlaps predicate part 1>

<when operand list> ::=
  <when operand> [ { <comma> <when operand> }... ]

<when operand> ::=
    <row value predicand>
  | <comparison predicate part 2>
  | <between predicate part 2>
  | <in predicate part 2>
  | <character like predicate part 2>
  | <octet like predicate part 2>
  | <similar predicate part 2>
  | <regex like predicate part 2>
  | <null predicate part 2>
  | <quantified comparison predicate part 2>
  | <normalized predicate part 2>
  | <match predicate part 2>
  | <overlaps predicate part 2>
  | <distinct predicate part 2>
  | <member predicate part 2>
  | <submultiset predicate part 2>
  | <set predicate part 2>
  | <type predicate part 2>

<result> ::=
    <result expression>
  | NULL

<result expression> ::=
  <value expression>

6.13 <cast specification>

Function
Specify a data conversion.


Format
<cast specification> ::=
     CAST <left paren>
         <cast operand> AS <cast target>
         [ FORMAT <cast template> ]
         <right paren>

<cast operand> ::=
    <value expression>
  | <implicitly typed value specification>

<cast target> ::=
    <domain name>
  | <data type>

<cast template> ::=
  <character string literal>

6.14 <next value expression>

Function
Return the next value of a sequence generator.


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

6.15 <field reference>

Function
Reference a field of a row value.


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

6.16 <subtype treatment>

Function
Modify the declared type of an expression.


Format
<subtype treatment> ::=
  TREAT <left paren> <subtype operand> AS <target subtype> <right paren>

<subtype operand> ::=
  <value expression>

<target subtype> ::=
    <path-resolved user-defined type name>
  | <reference type>

6.17 <method invocation>

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


Format
<method invocation> ::=
    <direct invocation>
  | <generalized invocation>

<direct invocation> ::=
  <value expression primary> <period> <method name> [ <SQL argument list> ]

<generalized invocation> ::=
  <left paren> <value expression primary> AS <data type> <right paren>
      <period> <method name> [ <SQL argument list> ]

<method selection> ::=
  <routine invocation>

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

6.18 <static method invocation>

Function
Invoke a static method.


Format
<static method invocation> ::=
  <path-resolved user-defined type name> <double colon> <method name>
      [ <SQL argument list> ]

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

6.19 <new specification>

Function
Invoke a method on a structured type.


Format
<new specification> ::=
  NEW <path-resolved user-defined type name> <SQL argument list>

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

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


Format
<attribute or method reference> ::=
  <value expression primary> <dereference operator> <qualified identifier>
      [ <SQL argument list> ]

<dereference operator> ::=
  <right arrow>

6.21 <dereference operation>

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


Format
<dereference operation> ::=
  <reference value expression> <dereference operator> <attribute name>

6.22 <method reference>

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


Format
<method reference> ::=
  <value expression primary> <dereference operator> <method name> <SQL argument list>

6.23 <reference resolution>

Function
Obtain the value referenced by a REF value.


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

6.24 <array element reference>

Function
Return an element of an array.


Format
<array element reference> ::=
  <array value expression>
      <left bracket or trigraph> <numeric value expression> <right bracket or trigraph>

6.25 <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.26 <row pattern navigation operation>

Function
Return the value of a value expression evaluated in a row determined by navigation within the row pattern
partition using logical and physical offsets from a row mapped to a row pattern variable.


Format
<row pattern navigation operation> ::=
    <row pattern navigation: logical>
  | <row pattern navigation: physical>
  | <row pattern navigation: compound>

<row pattern navigation: logical> ::=
  [ <running or final> ] <first or last>
      <left paren> <value expression> [ <comma> <logical offset> ] <right paren>

<row pattern navigation: physical> ::=
  <prev or next>
      <left paren> <value expression> [ <comma> <physical offset> ] <right paren>

<row pattern navigation: compound> ::=
  <prev or next> <left paren> [ <running or final> ] <first or last>
      <left paren> <value expression> [ <comma> <logical offset> ] <right paren>
      [ <comma> <physical offset> ] <right paren>

<first or last> ::=
  FIRST | LAST

<prev or next> ::=
  PREV | NEXT

<logical offset> ::=
    <simple value specification>
  | <dynamic parameter specification>

<physical offset> ::=
    <simple value specification>
  | <dynamic parameter specification>

6.27 <JSON value function>

Function
Extract an SQL value of a predefined type from a JSON value.


Format
<JSON value function> ::=
  JSON_VALUE <left paren>
      <JSON API common syntax>
      [ <JSON returning clause> ]
      [ <JSON value empty behavior> ON EMPTY ]
      [ <JSON value error behavior> ON ERROR ]
  <right paren>

<JSON returning clause> ::=
  RETURNING <data type>

<JSON value empty behavior> ::=
    ERROR
  | NULL
  | DEFAULT <value expression>

<JSON value error behavior> ::=
    ERROR
  | NULL
  | DEFAULT <value expression>

6.28 <value expression>

Function
Specify a value.


Format
<value expression> ::=
    <common value expression>
  | <boolean value expression>
  | <row value expression>

<common value expression> ::=
         <numeric value expression>
     |   <string value expression>
     |   <datetime value expression>
     |   <interval value expression>
     |   <user-defined type value expression>
     |   <reference value expression>
     |   <collection value expression>

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

<reference value expression> ::=
  <value expression primary>

<collection value expression> ::=
    <array value expression>
  | <multiset value expression>

6.29 <numeric value expression>

Function
Specify a numeric value.


Format
<numeric value expression> ::=
    <term>
  | <numeric value expression> <plus sign> <term>
  | <numeric value expression> <minus sign> <term>

<term> ::=
    <factor>
  | <term> <asterisk> <factor>
  | <term> <solidus> <factor>

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

<numeric primary> ::=
    <value expression primary>
  | <numeric value function>

6.30 <numeric value function>

Function
Specify a function yielding a value of type numeric.


Format
<numeric value function> ::=
    <position expression>
  | <regex occurrences function>
  | <regex position expression>
  | <extract expression>
  | <length expression>
  | <cardinality expression>
  | <max cardinality expression>
  | <absolute value expression>
  | <modulus expression>
  | <trigonometric function>
  | <general logarithm function>
  | <common logarithm>
  | <natural logarithm>
  | <exponential function>
  | <power function>
  | <square root>
  | <floor function>
  | <ceiling function>
  | <width bucket function>
  | <match number function>

<position expression> ::=
    <character position expression>
  | <binary position expression>

<regex occurrences function> ::=
  OCCURRENCES_REGEX <left paren>
      <XQuery pattern> [ FLAG <XQuery option flag> ]
      IN <regex subject string>
      [ FROM <start position> ]
      [ USING <char length units> ]
      <right paren>

<XQuery pattern> ::=
  <character value expression>

<XQuery option flag> ::=
  <character value expression>

<regex subject string> ::=
  <character value expression>

<regex position expression> ::=
  POSITION_REGEX <left paren>
      [ <regex position start or after> ]
      <XQuery pattern> [ FLAG <XQuery option flag> ]
      IN <regex subject string>
      [ FROM <start position> ]
      [ USING <char length units> ]
      [ OCCURRENCE <regex occurrence> ]
      [ GROUP <regex capture group> ]
      <right paren>

<regex position start or after> ::=
    START
  | AFTER

<regex occurrence> ::=
  <numeric value expression>

<regex capture group> ::=
  <numeric value expression>

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

<character value expression 1> ::=
  <character value expression>

<character value expression 2> ::=
  <character value expression>

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

<length expression> ::=
    <char length expression>
  | <octet length expression>

<char length expression> ::=
  { CHAR_LENGTH | CHARACTER_LENGTH } <left paren> <character 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>

<extract field> ::=
    <primary datetime field>
  | <time zone field>

<time zone field> ::=
    TIMEZONE_HOUR
  | TIMEZONE_MINUTE

<extract source> ::=
    <datetime value expression>
  | <interval value expression>

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

<max cardinality expression> ::=
  ARRAY_MAX_CARDINALITY <left paren> <array value expression> <right paren>

<absolute value expression> ::=
  ABS <left paren> <numeric value expression> <right paren>

<modulus expression> ::=
  MOD <left paren> <numeric value expression dividend> <comma>
      <numeric value expression divisor> <right paren>

<numeric value expression dividend> ::=
  <numeric value expression>

<numeric value expression divisor> ::=
  <numeric value expression>

<trigonometric function> ::=
  <trigonometric function name> <left paren> <numeric value expression> <right paren>

<trigonometric function name> ::=
  SIN | COS | TAN | SINH | COSH | TANH | ASIN | ACOS | ATAN

<general logarithm function> ::=
  LOG <left paren> <general logarithm base> <comma>
      <general logarithm argument> <right paren>

<general logarithm base> ::=
  <numeric value expression>

<general logarithm argument> ::=
  <numeric value expression>

<common logarithm> ::=
  LOG10 <left paren> <numeric value expression> <right paren>

<natural logarithm> ::=
  LN <left paren> <numeric value expression> <right paren>

<exponential function> ::=
  EXP <left paren> <numeric value expression> <right paren>

<power function> ::=
  POWER <left paren> <numeric value expression base> <comma>
      <numeric value expression exponent> <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 function> ::=
  WIDTH_BUCKET <left paren> <width bucket operand> <comma> <width bucket bound 1> <comma>
      <width bucket bound 2> <comma> <width bucket count> <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>

<match number function> ::=
  MATCH_NUMBER <left paren> <right paren>

6.31 <string value expression>

Function
Specify a character string value or a binary string value.


Format
<string value expression> ::=
    <character value expression>
  | <binary value expression>

<character value expression> ::=
    <concatenation>
  | <character factor>

<concatenation> ::=
  <character value expression> <concatenation operator> <character factor>

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

<character primary> ::=
    <value expression primary>
  | <string value function>

<binary value expression> ::=
    <binary concatenation>
  | <binary factor>

<binary factor> ::=
  <binary primary>

<binary primary> ::=
    <value expression primary>
  | <string value function>

<binary concatenation> ::=
  <binary value expression> <concatenation operator> <binary factor>

6.32 <string value function>

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


Format
<string value function> ::=
    <character value function>
  | <binary value function>
  | <JSON value constructor>
  | <JSON query>

<character value function> ::=
    <character substring function>
  | <regular expression substring function>
  | <regex substring function>
  | <fold>
  | <transcoding>
  | <character transliteration>
  | <regex transliteration>
  | <trim function>
  | <character overlay function>
  | <normalize function>
  | <specific type method>
  | <classifier 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>

<regex substring function> ::=
  SUBSTRING_REGEX <left paren>
      <XQuery pattern> [ FLAG <XQuery option flag> ]
      IN <regex subject string>
      [ FROM <start position> ]
      [ USING <char length units> ]
      [ OCCURRENCE <regex occurrence> ]
      [ GROUP <regex capture group> ]
      <right paren>

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

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

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

<regex transliteration> ::=
  TRANSLATE_REGEX <left paren>
      <XQuery pattern> [ FLAG <XQuery option flag> ]
      IN <regex subject string>
      [ WITH <XQuery replacement string> ]
      [ FROM <start position> ]
      [ USING <char length units> ]
      [ OCCURRENCE <regex transliteration occurrence> ]
      <right paren>

<XQuery replacement string> ::=
  <character value expression>

<regex transliteration occurrence> ::=
    <regex occurrence>
  | ALL

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

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

<normalize function> ::=
  NORMALIZE <left paren> <character value expression>
      [ <comma> <normal form> [ <comma> <normalize function result length> ] ] <right paren>

<normal form> ::=
    NFC
  | NFD
  | NFKC
  | NFKD

<normalize function result length> ::=
    <character length>
  | <character large object length>

<specific type method> ::=
  <user-defined type value expression> <period> SPECIFICTYPE
      [ <left paren> <right paren> ]

<binary value function> ::=
       <binary substring function>
     | <binary trim function>
     | <binary overlay function>

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

<binary trim function> ::=
  TRIM <left paren> <binary trim operands> <right paren>

<binary trim operands> ::=
  [ [ <trim specification> ] [ <trim octet> ] FROM ] <binary trim source>

<binary trim source> ::=
  <binary value expression>

<trim octet> ::=
  <binary value expression>

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

<start position> ::=
  <numeric value expression>

<string length> ::=
  <numeric value expression>

<classifier function> ::=
  CLASSIFIER <left paren> [ <row pattern variable name> ] <right paren>

6.33 <JSON value constructor>

Function
Generate a JSON text fragment.


Format
<JSON value constructor> ::=
    <JSON object constructor>
  | <JSON array constructor>

<JSON object constructor> ::=
  JSON_OBJECT <left paren>
      [ <JSON name and value> [ { <comma> <JSON name and value> }... ]
      [ <JSON constructor null clause> ]
      [ <JSON key uniqueness constraint> ] ]
      [ <JSON output clause> ]
      <right paren>

<JSON name and value> ::=
    [ KEY ] <JSON name> VALUE <JSON value expression>
  | <JSON name> <colon> <JSON value expression>

<JSON name> ::=
  <character value expression>

<JSON constructor null clause> ::=
    NULL ON NULL
  | ABSENT ON NULL

<JSON array constructor> ::=
    <JSON array constructor by enumeration>
  | <JSON array constructor by query>

<JSON array constructor by enumeration> ::=
  JSON_ARRAY <left paren>
      [ <JSON value expression> [ { <comma> <JSON value expression> }... ]
      [ <JSON constructor null clause> ] ]
      [ <JSON output clause> ]
      <right paren>

<JSON array constructor by query> ::=
  JSON_ARRAY <left paren>
      <query expression>
      [ <JSON input clause> ]
      [ <JSON constructor null clause> ]
      [ <JSON output clause> ]
      <right paren>

6.34 <JSON query>

Function
Extract a JSON text from a JSON text using an SQL/JSON path expression.


Format
<JSON query> ::=
  JSON_QUERY <left paren>
  <JSON API common syntax>
      [ <JSON output clause> ]
      [ <JSON query wrapper behavior> WRAPPER ]
      [ <JSON query quotes behavior> QUOTES [ ON SCALAR STRING ] ]
      [ <JSON query empty behavior> ON EMPTY ]
      [ <JSON query error behavior> ON ERROR ]
  <right paren>

<JSON query wrapper behavior> ::=
    WITHOUT [ ARRAY ]
  | WITH [ CONDITIONAL | UNCONDITIONAL ] [ ARRAY ]

<JSON query quotes behavior> ::=
    KEEP
  | OMIT

<JSON query empty behavior> ::=
    ERROR
  | NULL
  | EMPTY ARRAY
  | EMPTY OBJECT

<JSON query error behavior> ::=
    ERROR
  | NULL
  | EMPTY ARRAY
  | EMPTY OBJECT

6.35 <datetime value expression>

Function
Specify a datetime value.


Format
<datetime value expression> ::=
    <datetime term>
  | <interval value expression> <plus sign> <datetime term>
  | <datetime value expression> <plus sign> <interval term>
  | <datetime value expression> <minus sign> <interval term>

<datetime term> ::=
  <datetime factor>

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

<datetime primary> ::=
    <value expression primary>
  | <datetime value function>

<time zone> ::=
  AT <time zone specifier>

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

6.36 <datetime value function>

Function
Specify a function yielding a value of type datetime.


Format
<datetime value function> ::=
    <current date value function>
  | <current time value function>
  | <current timestamp value function>
  | <current local time value function>
  | <current local timestamp value function>

<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.37 <interval value expression>

Function
Specify an interval value.


Format
<interval value expression> ::=
    <interval term>
  | <interval value expression 1> <plus sign> <interval term 1>
  | <interval value expression 1> <minus sign> <interval term 1>
  | <left paren> <datetime value expression> <minus sign> <datetime term> <right paren>
      <interval qualifier>

<interval term> ::=
    <interval factor>
  | <interval term 2> <asterisk> <factor>
  | <interval term 2> <solidus> <factor>
  | <term> <asterisk> <interval factor>

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

<interval primary> ::=
    <value expression primary> [ <interval qualifier> ]
  | <interval value function>

<interval value expression 1> ::=
  <interval value expression>

<interval term 1> ::=
  <interval term>

<interval term 2> ::=
  <interval term>

6.38 <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.39 <boolean value expression>

Function
Specify a boolean value.


Format
<boolean value expression> ::=
    <boolean term>
  | <boolean value expression> OR <boolean term>

<boolean term> ::=
    <boolean factor>
  | <boolean term> AND <boolean factor>

<boolean factor> ::=
  [ NOT ] <boolean test>

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

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

<boolean primary> ::=
    <predicate>
  | <boolean predicand>

<boolean predicand> ::=
    <parenthesized boolean value expression>
  | <nonparenthesized value expression primary>

<parenthesized boolean value expression> ::=
  <left paren> <boolean value expression> <right paren>

6.40 <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> ::=
    <array value function>
  | <value expression primary>

6.41 <array value function>

Function
Specify a function yielding a value of an array type.


Format
<array value function> ::=
  <trim array function>

<trim array function> ::=
  TRIM_ARRAY <left paren> <array value expression> <comma> <numeric value expression> <right
  paren>

6.42 <array value constructor>

Function
Specify construction of an array.


Format
<array value constructor> ::=
    <array value constructor by enumeration>
  | <array value constructor by query>

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

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

<multiset primary> ::=
    <multiset value function>
  | <value expression primary>

6.44 <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.45 <multiset value constructor>

Function
Specify construction of a multiset.


Format
<multiset value constructor> ::=
    <multiset value constructor by enumeration>
  | <multiset value constructor by query>
  | <table value constructor by query>

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

<table value constructor by query> ::=
  TABLE <table subquery>

7 Query expressions


7.1     <row value constructor>

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


Format
<row value constructor> ::=
    <common value expression>
  | <boolean value expression>
  | <explicit row value constructor>

<explicit row value constructor> ::=
    <left paren> <row value constructor element> <comma>
        <row value constructor element list> <right paren>
  | ROW <left paren> <row value constructor element list> <right paren>
  | <row subquery>

<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> ::=
    <common value expression>
  | <boolean value expression>
  | <contextually typed value specification>
  | <left paren> <contextually typed value specification> <right paren>
  | <left paren> <contextually typed row value constructor element> <comma>
      <contextually typed row value constructor element list> <right paren>
  | ROW <left paren> <contextually typed row value constructor element list> <right paren>

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

<row value constructor predicand> ::=
    <common value expression>
  | <boolean predicand>
  | <explicit row value constructor>

7.2      <row value expression>

Function
Specify a row value.


Format
<row value expression> ::=
    <row value special case>
  | <explicit row value constructor>

<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 predicand> ::=
    <row value special case>
  | <row value constructor predicand>

<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
<table expression> ::=
  <from clause>
      [ <where clause> ]
      [ <group by clause> ]
      [ <having clause> ]
      [ <window clause> ]

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 clause> ::=
  TABLESAMPLE <sample method> <left paren> <sample percentage> <right paren>
      [ <repeatable clause> ]

<sample method> ::=
    BERNOULLI
  | SYSTEM

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

<sample percentage> ::=
  <numeric value expression>

<repeat argument> ::=
  <numeric value expression>

<table primary> ::=
    <table or query name>
        [ <query system time period specification> ]
        [ <correlation or recognition> ]
  | <derived table> <correlation or recognition>
  | <lateral derived table> <correlation or recognition>
  | <collection derived table> <correlation or recognition>
  | <table function derived table> <correlation or recognition>
  | <PTF derived table> [ <correlation or recognition> ]
  | <only spec> [ <correlation or recognition> ]
  | <data change delta table> [ <correlation or recognition> ]
  | <JSON table> <correlation or recognition>
  | <JSON table primitive> <correlation name>
  | <parenthesized joined table>

<correlation or recognition> ::=
    [ AS ] <correlation name>
        [ <parenthesized derived column list> ]
  | <row pattern recognition clause and name>
<query system time period specification> ::=
    FOR SYSTEM_TIME AS OF <point in time 1>
  | FOR SYSTEM_TIME BETWEEN [ ASYMMETRIC | SYMMETRIC ]
      <point in time 1> AND <point in time 2>
  | FOR SYSTEM_TIME FROM <point in time 1> TO <point in time 2>

<point in time 1> ::=
  <point in time>

<point in time 2> ::=
  <point in time>

<point in time> ::=
  <datetime 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>
      [ { <comma> <collection value expression> }... ] <right paren>
      [ WITH ORDINALITY ]

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

<derived table> ::=
  <table subquery>

<PTF derived table> ::=
  TABLE <left paren> <routine invocation> <right paren>

<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> }... ]

<data change delta table> ::=
  <result option> TABLE <left paren> <data change statement> <right paren>

<data change statement> ::=
    <delete statement: searched>
  | <insert statement>
  | <merge statement>
  | <update statement: searched>

<result option> ::=
    FINAL
  | NEW
  | OLD

<parenthesized joined table> ::=
    <left paren> <parenthesized joined table> <right paren>
  | <left paren> <joined table> <right paren>

<row pattern recognition clause and name> ::=
  [ [ AS ] <row pattern input name>
      [ <row pattern input derived column list> ] ]
      <row pattern recognition clause> [ [ AS ] <row pattern output name>
      [ <row pattern output derived column list> ] ]

<row pattern input name> ::=
  <correlation name>

<row pattern output name> ::=
  <correlation name>

<row pattern input derived column list> ::=
  <parenthesized derived column list>

<row pattern output derived column list> ::=
  <parenthesized derived column list>

<parenthesized derived column list> ::=
  <left paren> <derived column list> <right paren>

7.7        <row pattern recognition clause>

Function
Match row patterns.


Format
<row pattern recognition clause> ::=
  MATCH_RECOGNIZE <left paren>
      [ <row pattern partition by> ]
      [ <row pattern order by> ]
      [ <row pattern measures> ]
      [ <row pattern rows per match> ]
      <row pattern common syntax>
      <right paren>

<row pattern partition by> ::=
  PARTITION BY <row pattern partition list>

<row pattern partition list> ::=
  <row pattern partition column> [ { <comma> <row pattern partition column> }... ]

<row pattern partition column> ::=
  <column reference> [ <collate clause> ]

<row pattern order by> ::=
  ORDER BY <sort specification list>

<row pattern rows per match> ::=
    ONE ROW PER MATCH
  | ALL ROWS PER MATCH [ <row pattern empty match handling> ]

<row pattern empty match handling> ::=
    SHOW EMPTY MATCHES
  | OMIT EMPTY MATCHES
  | WITH UNMATCHED ROWS

7.8        <row pattern measures>

Function
Specify the measure columns of a row pattern.


Format
<row pattern measures> ::=
  MEASURES <row pattern measure list>

<row pattern measure list> ::=
  <row pattern measure definition> [ { <comma> <row pattern measure definition> }... ]

<row pattern measure definition> ::=
  <row pattern measure expression> AS <measure name>

<row pattern measure expression> ::=
  <value expression>

7.9    <row pattern common syntax>

Function
Specify syntax that is common to row patterns in <table reference> and in <window clause>.


Format
<row pattern common syntax> ::=
  [ AFTER MATCH <row pattern skip to> ]
      [ <row pattern initial or seek> ]
      PATTERN <left paren> <row pattern> <right paren>
      [ <row pattern subset clause> ]
      DEFINE <row pattern definition list>

<row pattern skip to> ::=
    SKIP TO NEXT ROW
  | SKIP PAST LAST ROW
  | SKIP TO FIRST <row pattern skip to variable name>
  | SKIP TO LAST <row pattern skip to variable name>
  | SKIP TO <row pattern skip to variable name>

<row pattern skip to variable name> ::=
  <row pattern variable name>

<row pattern initial or seek> ::=
    INITIAL
  | SEEK

<row pattern> ::=
    <row pattern term>
  | <row pattern alternation>

<row pattern alternation> ::=
  <row pattern> <vertical bar> <row pattern term>

<row pattern term> ::=
    <row pattern factor>
  | <row pattern term> <row pattern factor>

<row pattern factor> ::=
  <row pattern primary> [ <row pattern quantifier> ]

<row pattern quantifier> ::=
    <asterisk> [ <question mark> ]
  | <plus sign> [ <question mark> ]
  | <question mark> [ <question mark> ]
  | <left brace> [ <unsigned integer> ] <comma> [ <unsigned integer> ] <right brace>
      [ <question mark> ]
  | <left brace> <unsigned integer> <right brace>

<row pattern primary> ::=
    <row pattern primary variable name>
  | <dollar sign>
  | <circumflex>
  | <left paren> [ <row pattern> ] <right paren>
  | <left brace minus> <row pattern> <right minus brace>
  | <row pattern permute>

<row pattern primary variable name> ::=
  <row pattern variable name>

<row pattern permute> ::=
  PERMUTE <left paren> <row pattern> [ { <comma> <row pattern> }... ] <right paren>

<row pattern subset clause> ::=
  SUBSET <row pattern subset list>

<row pattern subset list> ::=
  <row pattern subset item> [ { <comma> <row pattern subset item> }... ]

<row pattern subset item> ::=
  <row pattern subset item variable name> <equals operator>
      <left paren> <row pattern subset rhs> <right paren>

<row pattern subset item variable name> ::=
  <row pattern variable name>

<row pattern subset rhs> ::=
  <row pattern subset rhs variable name>
      [ { <comma> <row pattern subset rhs variable name> }... ]

<row pattern subset rhs variable name> ::=
  <row pattern variable name>

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

<row pattern definition> ::=
  <row pattern definition variable name> AS <row pattern definition search condition>

<row pattern definition variable name> ::=
  <row pattern variable name>

<row pattern definition search condition> ::=
  <search condition>

7.10 <joined table>

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


Format
<joined table> ::=
    <cross join>
  | <qualified join>
  | <natural join>

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

<qualified join> ::=
  { <table reference> | <partitioned join table> }
      [ <join type> ] JOIN
      { <table reference> | <partitioned join table> }
      <join specification>

<partitioned join table> ::=
  <table factor> PARTITION BY
      <partitioned join column reference list>

<partitioned join column reference list> ::=
  <left paren> <partitioned join column reference>
      [ { <comma> <partitioned join column reference> }... ]
      <right paren>

<partitioned join column reference> ::=
  <column reference>

<natural join> ::=
  { <table reference> | <partitioned join table> }
      NATURAL [ <join type> ] JOIN
      { <table factor> | <partitioned join table> }

<join specification> ::=
    <join condition>
  | <named columns join>

<join condition> ::=
  ON <search condition>

<named columns join> ::=
  USING <left paren> <join column list> <right paren> [ AS <join correlation name> ]

<join correlation name> ::=
  <correlation name>

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

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

<join column list> ::=
  <column name list>

7.11 <JSON table>

Function
Query a JSON text and present it as a relational table.


Format
<JSON table> ::=
  JSON_TABLE <left paren>
      <JSON API common syntax>
      <JSON table columns clause>
      [ <JSON table plan clause> ]
      [ <JSON table error behavior> ON ERROR ]
  <right paren>

<JSON table columns clause> ::=
  COLUMNS <left paren>
      <JSON table column definition> [ { <comma> <JSON table column definition> }... ]
  <right paren>

<JSON table column definition> ::=
    <JSON table ordinality column definition>
  | <JSON table regular column definition>
  | <JSON table formatted column definition>
  | <JSON table nested columns>

<JSON table ordinality column definition> ::=
  <column name> FOR ORDINALITY

<JSON table regular column definition> ::=
  <column name> <data type>
      [ PATH <JSON table column path specification> ]
      [ <JSON table column empty behavior> ON EMPTY ]
      [ <JSON table column error behavior> ON ERROR ]

<JSON table column empty behavior> ::=
    ERROR
  | NULL
  | DEFAULT <value expression>

<JSON table column error behavior> ::=
    ERROR
  | NULL
  | DEFAULT <value expression>

<JSON table column path specification> ::=
  <JSON path specification>

<JSON table formatted column definition> ::=
  <column name> <data type>
      FORMAT <JSON representation>
      [ PATH <JSON table column path specification> ]
      [ <JSON table formatted column wrapper behavior> WRAPPER ]
      [ <JSON table formatted column quotes behavior> QUOTES [ ON SCALAR STRING ] ]
      [ <JSON table formatted column empty behavior> ON EMPTY ]
      [ <JSON table formatted column error behavior> ON ERROR ]

<JSON table formatted column wrapper behavior> ::=
    WITHOUT [ ARRAY ]
  | WITH [ CONDITIONAL | UNCONDITIONAL ] [ ARRAY ]

<JSON table formatted column quotes behavior> ::=
    KEEP
  | OMIT

<JSON table formatted column empty behavior> ::=
    ERROR
  | NULL
  | EMPTY ARRAY
  | EMPTY OBJECT

<JSON table formatted column error behavior> ::=
    ERROR
  | NULL
  | EMPTY ARRAY
  | EMPTY OBJECT

<JSON table nested columns> ::=
  NESTED [ PATH ] <JSON table nested path specification>
      [ AS <JSON table nested path name> ]
  <JSON table columns clause>

<JSON table nested path specification> ::=
  <JSON path specification>

<JSON table nested path name> ::=
  <JSON table path name>

<JSON table path name> ::=
  <identifier>

<JSON table plan clause> ::=
    <JSON table specific plan>
  | <JSON table default plan>

<JSON table specific plan> ::=
  PLAN <left paren> <JSON table plan> <right paren>

<JSON table plan> ::=
    <JSON table path name>
  | <JSON table plan parent/child>
  | <JSON table plan sibling>

<JSON table plan parent/child> ::=
    <JSON table plan outer>
  | <JSON table plan inner>

<JSON table plan outer> ::=
  <JSON table path name> OUTER <JSON table plan primary>

<JSON table plan inner> ::=
  <JSON table path name> INNER <JSON table plan primary>

<JSON table plan sibling> ::=
    <JSON table plan union>
  | <JSON table plan cross>

<JSON table plan union> ::=
  <JSON table plan primary> UNION <JSON table plan primary>
      [ { UNION <JSON table plan primary> }... ]

<JSON table plan cross> ::=
  <JSON table plan primary> CROSS <JSON table plan primary>
      [ { CROSS <JSON table plan primary> }... ]

<JSON table plan primary> ::=
    <JSON table path name>
  | <left paren> <JSON table plan> <right paren>

<JSON table default plan> ::=
  PLAN DEFAULT <left paren>
      <JSON table default plan choices> <right paren>

<JSON table default plan choices> ::=
    <JSON table default plan inner/outer>
        [ <comma> <JSON table default plan union/cross> ]
  | <JSON table default plan union/cross>
      [ <comma> <JSON table default plan inner/outer> ]

<JSON table default plan inner/outer> ::=
  INNER | OUTER

<JSON table default plan union/cross> ::=
  UNION | CROSS

<JSON table error behavior> ::=
    ERROR
  | EMPTY

<JSON table primitive> ::=
  JSON_TABLE_PRIMITIVE <left paren>
      <JSON API common syntax>
      <JSON table primitive columns clause>
      <JSON table error behavior> ON ERROR
  <right paren>

<JSON table primitive columns clause> ::=
  COLUMNS <left paren>
      <JSON table primitive column definition>
      [ { <comma> <JSON table primitive column definition> }... ]
  <right paren>

<JSON table primitive column definition> ::=
    <JSON table ordinality column definition>
  | <JSON table regular column definition>
  | <JSON table formatted column definition>
  | <JSON table primitive chaining column>

<JSON table primitive chaining column> ::=
  <column name> FOR CHAINING

7.12 <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.13 <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 element> ::=
    <ordinary grouping set>
  | <rollup list>
  | <cube list>
  | <grouping sets specification>
  | <empty grouping set>

<ordinary grouping set> ::=
    <grouping column reference>
  | <left paren> <grouping column reference list> <right paren>

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

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

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

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

<cube list> ::=
  CUBE <left paren> <ordinary grouping set list> <right paren>

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

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

<grouping set> ::=
    <ordinary grouping set>
  | <rollup list>
  | <cube list>
  | <grouping sets specification>
  | <empty grouping set>

<empty grouping set> ::=
  <left paren> <right paren>

7.14 <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.15 <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>

<window specification> ::=
  <left paren> <window specification details> <right paren>

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

<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 clause> ::=
  [ <row pattern measures> ]
      <window frame units> <window frame extent>
      [ <window frame exclusion> ]
      [ <row pattern common syntax> ]

<window frame units> ::=
    ROWS
  | RANGE
  | GROUPS

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

<qualified asterisk> ::=
    <asterisked identifier chain> <period> <asterisk>
  | <all fields reference>

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

<asterisked identifier> ::=
  <identifier>

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

<as clause> ::=
  [ AS ] <column name>

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

<all fields column name list> ::=
  <column name list>

7.17 <query expression>

Function
Specify a table.


Format
<query expression> ::=
  [ <with clause> ] <query expression body>
      [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ]

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

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

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

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

<query primary> ::=
    <simple table>
  | <left paren> <query expression body>
      [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ]
      <right paren>

<simple table> ::=
    <query specification>
  | <table value constructor>
  | <explicit table>

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

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

<corresponding column list> ::=
  <column name list>

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

<result offset clause> ::=
  OFFSET <offset row count> { ROW | ROWS }

<fetch first clause> ::=
  FETCH { FIRST | NEXT } [ <fetch first quantity> ] { ROW | ROWS } { ONLY | WITH TIES }

<fetch first quantity> ::=
    <fetch first row count>
  | <fetch first percentage>

<offset row count> ::=
  <simple value specification>

<fetch first row count> ::=
  <simple value specification>

<fetch first percentage> ::=
  <simple value specification> PERCENT

7.18 <search or cycle clause>

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


Format
<search or cycle clause> ::=
    <search clause>
  | <cycle clause>
  | <search clause> <cycle clause>

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

<recursive search order> ::=
    DEPTH FIRST BY <column name list>
  | BREADTH FIRST BY <column name list>

<sequence column> ::=
  <column name>

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

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

<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.19 <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.1      <predicate>

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


Format
<predicate> ::=
    <comparison predicate>
  | <between predicate>
  | <in predicate>
  | <like predicate>
  | <similar predicate>
  | <regex like predicate>
  | <null predicate>
  | <quantified comparison predicate>
  | <exists predicate>
  | <unique predicate>
  | <normalized predicate>
  | <match predicate>
  | <overlaps predicate>
  | <distinct predicate>
  | <member predicate>
  | <submultiset predicate>
  | <set predicate>
  | <type predicate>
  | <period predicate>
  | <JSON predicate>
  | <JSON exists predicate>

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>

<comp op> ::=
    <equals operator>
  | <not equals operator>
  | <less than operator>
  | <greater than operator>
  | <less than or equals operator>
  | <greater than or equals operator>

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 predicate value> ::=
    <table subquery>
  | <left paren> <in value list> <right paren>

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

8.5        <like predicate>

Function
Specify a pattern-match comparison.


Format
<like predicate> ::=
    <character like predicate>
  | <octet like predicate>

<character like predicate> ::=
  <row value predicand> <character like predicate part 2>

<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> ::=
  <row value predicand> <octet like predicate part 2>

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

<octet pattern> ::=
  <binary value expression>

<escape octet> ::=
  <binary value expression>

8.6     <similar predicate>

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


Format
<similar predicate> ::=
  <row value predicand> <similar predicate part 2>

<similar predicate part 2> ::=
  [ NOT ] SIMILAR TO <similar pattern> [ ESCAPE <escape character> ]

<similar pattern> ::=
  <character value expression>

<regular expression> ::=
    <regular term>
  | <regular expression> <vertical bar> <regular term>

<regular term> ::=
    <regular factor>
  | <regular term> <regular factor>

<regular factor> ::=
    <regular primary>
  | <regular primary>      <asterisk>
  | <regular primary>      <plus sign>
  | <regular primary>      <question mark>
  | <regular primary>      <repeat factor>

<repeat factor> ::=
  <left brace> <low value> [ <upper limit> ] <right brace>

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

<low value> ::=
  <unsigned integer>

<high value> ::=
  <unsigned integer>

<regular primary> ::=
    <character specifier>
  | <percent>
  | <regular character set>
  | <left paren> <regular expression> <right paren>

<character specifier> ::=
    <non-escaped character>
  | <escaped character>

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

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

<regular character set> ::=
    <underscore>
  | <left bracket> <character enumeration>... <right bracket>
  | <left bracket> <circumflex> <character enumeration>... <right bracket>
  | <left bracket> <character enumeration include>...
      <circumflex> <character enumeration exclude>... <right bracket>

<character enumeration include> ::=
  <character enumeration>

<character enumeration exclude> ::=
  <character enumeration>

<character enumeration> ::=
    <character specifier>
  | <character specifier> <minus sign> <character specifier>
  | <left bracket> <colon> <regular character set identifier> <colon> <right bracket>

<regular character set identifier> ::=
  <identifier>

8.7        <regex like predicate>

Function
Specify a pattern-match comparison using an XQuery regular expression.


Format
<regex like predicate> ::=
  <row value predicand> <regex like predicate part 2>

<regex like predicate part 2> ::=
  [ NOT ] LIKE_REGEX <XQuery pattern> [ FLAG <XQuery option flag> ]

8.8        <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.9        <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.10 <exists predicate>

Function
Specify a test for a non-empty set.


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

8.11 <unique predicate>

Function
Specify a test for the absence of duplicate rows.


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

8.12 <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 ] [ <normal form> ] NORMALIZED

8.13 <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.14 <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.15 <distinct predicate>

Function
Specify a test of whether two row values are distinct


Format
<distinct predicate> ::=
  <row value predicand 3> <distinct predicate part 2>

<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.16 <member predicate>

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


Format
<member predicate> ::=
  <row value predicand> <member predicate part 2>

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

8.17 <submultiset predicate>

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


Format
<submultiset predicate> ::=
  <row value predicand> <submultiset predicate part 2>

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

8.18 <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.19 <type predicate>

Function
Specify a type test.


Format
<type predicate> ::=
  <row value predicand> <type predicate part 2>

<type predicate part 2> ::=
  IS [ NOT ] OF <left paren> <type list> <right paren>

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

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

<inclusive user-defined type specification> ::=
  <path-resolved user-defined type name>

<exclusive user-defined type specification> ::=
  ONLY <path-resolved user-defined type name>

8.20 <period predicate>

Function
Specify a test to determine the relationship between periods.


Format
<period predicate> ::=
    <period overlaps predicate>
  | <period equals predicate>
  | <period contains predicate>
  | <period precedes predicate>
  | <period succeeds predicate>
  | <period immediately precedes predicate>
  | <period immediately succeeds predicate>

<period overlaps predicate> ::=
  <period predicand 1> <period overlaps predicate part 2>

<period overlaps predicate part 2> ::=
  OVERLAPS <period predicand 2>

<period predicand 1> ::=
  <period predicand>

<period predicand 2> ::=
  <period predicand>

<period predicand> ::=
    <period reference>
  | PERIOD <left paren> <period start value> <comma> <period end value> <right paren>

<period reference> ::=
  <basic identifier chain>

<period start value> ::=
  <datetime value expression>

<period end value> ::=
  <datetime value expression>

<period equals predicate> ::=
  <period predicand 1> <period equals predicate part 2>

<period equals predicate part 2> ::=
  EQUALS <period predicand 2>

<period contains predicate> ::=
  <period predicand 1> <period contains predicate part 2>

<period contains predicate part 2> ::=
  CONTAINS <period or point-in-time predicand>

<period or point-in-time predicand> ::=
    <period predicand>
  | <datetime value expression>

<period precedes predicate> ::=
  <period predicand 1> <period precedes predicate part 2>

<period precedes predicate part 2> ::=
  PRECEDES <period predicand 2>

<period succeeds predicate> ::=
  <period predicand 1> <period succeeds predicate part 2>

<period succeeds predicate part 2> ::=
  SUCCEEDS <period predicand 2>

<period immediately precedes predicate> ::=
  <period predicand 1> <period immediately precedes predicate part 2>

<period immediately precedes predicate part 2> ::=
  IMMEDIATELY PRECEDES <period predicand 2>

<period immediately succeeds predicate> ::=
  <period predicand 1> <period immediately succeeds predicate part 2>

<period immediately succeeds predicate part 2> ::=
  IMMEDIATELY SUCCEEDS <period predicand 2>

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

8.22 <JSON predicate>

Function
Test whether a string value is a JSON text.


Format
<JSON predicate> ::=
  <string value expression> [ <JSON input clause> ]
      IS [ NOT ] JSON
      [ <JSON predicate type constraint> ]
      [ <JSON key uniqueness constraint> ]

<JSON predicate type constraint> ::=
    VALUE
  | ARRAY
  | OBJECT
  | SCALAR

<JSON key uniqueness constraint> ::=
    WITH UNIQUE [ KEYS ]
  | WITHOUT UNIQUE [ KEYS ]

8.23 <JSON exists predicate>

Function
Test whether a JSON path expression returns any SQL/JSON items.


Format
<JSON exists predicate> ::=
  JSON_EXISTS <left paren>
      <JSON API common syntax>
      [ <JSON exists error behavior> ON ERROR ]
  <right paren>

<JSON exists error behavior> ::=
  TRUE | FALSE | UNKNOWN | ERROR

9 Additional common rules

9.38 SQL/JSON path language: lexical elements

Function
Specify the lexical analysis of the SQL/JSON path language.


Format
<SQL/JSON special symbol> ::=
    <asterisk>
  | <at sign>
  | <comma>
  | <dollar sign>
  | <double ampersand>
  | <double equals>
  | <double vertical bar>
  | <exclamation mark>
  | <greater than operator>
  | <greater than or equals operator>
  | <left bracket>
  | <left paren>
  | <less than operator>
  | <less than or equals operator>
  | <minus sign>
  | <not equals operator>
  | <percent>
  | <period>
  | <plus sign>
  | <question mark>
  | <right bracket>
  | <right paren>
  | <solidus>

<at sign> ::=
  @

<double ampersand> ::=
  &&

<double equals> ::=
  ==

<double vertical bar> ::=
  ||

<exclamation mark> ::=
  !

<SQL/JSON key word> ::=
    abs
  | ceiling
  | datetime
  | double
  | exists
  | false
  |   flag
  |   floor
  |   is
  |   keyvalue
  |   last
  |   lax
  |   like_regex
  |   null
  |   size
  |   starts
  |   strict
  |   to
  |   true
  |   type
  |   unknown
  |   with

<JSON path literal> ::=
  !! See the Syntax Rules.

<JSON path string literal> ::=
  !! See the Syntax Rules.

<JSON path numeric literal> ::=
  !! See the Syntax Rules.

<JSON path identifier> ::=
  !! See the Syntax Rules.

<JSON path context variable> ::=
  !! See the Syntax Rules.

<JSON path named variable> ::=
  !! See the Syntax Rules.

<JSON path key name> ::=
  !! See the Syntax Rules.

9.39 SQL/JSON path language: syntax and semantics

Function
Specify the syntax and semantics of SQL/JSON path language.


Format
<JSON path expression> ::=
  <JSON path mode> <JSON path wff>

<JSON path mode> ::=
  strict | lax

<JSON path primary> ::=
    <JSON path literal>
  | <JSON path variable>
  | <left paren> <JSON path wff> <right paren>

<JSON path variable> ::=
    <JSON path context variable>
  | <JSON path named variable>
  | <at sign>
  | <JSON last subscript>

<JSON path context variable> ::=
  <dollar sign>

<JSON path named variable> ::=
  <dollar sign> <JSON path identifier>

<JSON last subscript> ::=
  last

<JSON accessor expression> ::=
    <JSON path primary>
  | <JSON accessor expression> <JSON accessor op>

<JSON accessor op> ::=
      <JSON    member accessor>
  |   <JSON    wildcard member accessor>
  |   <JSON    array accessor>
  |   <JSON    wildcard array accessor>
  |   <JSON    filter expression>
  |   <JSON    item method>

<JSON member accessor> ::=
    <period> <JSON path key name>
  | <period> <JSON path string literal>
      NOTE 447 - Unlike [ECMAScript], SQL/JSON path language does not provide a member accessor using brackets that enclose
      a character string.

<JSON wildcard member accessor> ::=
  <period> <asterisk>

<JSON array accessor> ::=
  <left bracket> <JSON subscript list> <right bracket>

<JSON subscript list> ::=
  <JSON subscript> [ { <comma> <JSON subscript> }... ]

<JSON subscript> ::=
    <JSON path wff 1>
  | <JSON path wff 2> to <JSON path wff 3>

<JSON path wff 1> ::=
  <JSON path wff>

<JSON path wff 2> ::=
  <JSON path wff>

<JSON path wff 3> ::=
  <JSON path wff>

<JSON wildcard array accessor> ::=
  <left bracket> <asterisk> <right bracket>

<JSON filter expression> ::=
  <question mark> <left paren> <JSON path predicate> <right paren>
      NOTE 448 - Unlike [ECMAScript], predicates are not expressions; instead they form a separate language that can only be invoked
      within a <JSON filter expression>.

<JSON item method> ::=
  <period> <JSON method>

<JSON method> ::=
    type <left paren> <right paren>
  | size <left paren> <right paren>
  | double <left paren> <right paren>
  | ceiling <left paren> <right paren>
  | floor <left paren> <right paren>
  | abs <left paren> <right paren>
  | datetime <left paren> [ <JSON datetime template> ] <right paren>
  | keyvalue <left paren> <right paren>

<JSON datetime template> ::=
  <JSON path string literal>
<JSON unary expression> ::=
    <JSON accessor expression>
  | <plus sign> <JSON unary expression>
  | <minus sign> <JSON unary expression>

<JSON multiplicative expression> ::=
    <JSON unary expression>
  | <JSON multiplicative expression> <asterisk> <JSON unary expression>
  | <JSON multiplicative expression> <solidus> <JSON unary expression>
  | <JSON multiplicative expression> <percent> <JSON unary expression>

<JSON additive expression> ::=
    <JSON multiplicative expression>
  | <JSON additive expression> <plus sign> <JSON multiplicative expression>
  | <JSON additive expression> <minus sign> <JSON multiplicative expression>

<JSON path wff> ::=
  <JSON additive expression>
   NOTE 449 - This concludes the main language for JSON path expressions. Next comes the language for predicates, used only
   in <JSON filter expression>.

<JSON predicate primary> ::=
    <JSON delimited predicate>
  | <JSON non-delimited predicate>

<JSON delimited predicate> ::=
    <JSON exists path predicate>
  | <left paren> <JSON path predicate> <right paren>

<JSON non-delimited predicate> ::=
    <JSON comparison predicate>
  | <JSON like_regex predicate>
  | <JSON starts with predicate>
  | <JSON unknown predicate>

<JSON exists path predicate> ::=
  exists <left paren> <JSON path wff> <right paren>

<JSON comparison predicate> ::=
  <JSON path wff> <JSON comp op> <JSON path wff>
   NOTE 450 - Comparison operators are not left associative, unlike [ECMAScript].

<JSON comp op> ::=
    <double equals>
  | <not equals operator>
  | <less than operator>
  | <greater than operator>
  | <less than or equals operator>
  | <greater than or equals operator>
   NOTE 451 - Equality operators have the same precedence as inequality comparison operators, unlike [ECMAScript].

<JSON like_regex predicate> ::=
  <JSON path wff> like_regex <JSON like_regex pattern>
      [ flag <JSON like_regex flags> ]

<JSON like_regex pattern> ::=
  <JSON path string literal>
<JSON like_regex flags> ::=
  <JSON path string literal>

<JSON starts with predicate> ::=
  <JSON starts with whole> starts with <JSON starts with initial>

<JSON starts with whole> ::=
  <JSON path wff>

<JSON starts with initial> ::=
    <JSON path string literal>
  | <JSON path named variable>

<JSON unknown predicate> ::=
  <left paren> <JSON path predicate> <right paren> is unknown

<JSON boolean negation> ::=
    <JSON predicate primary>
  | <exclamation mark> <JSON delimited predicate>

<JSON boolean conjunction> ::=
    <JSON boolean negation>
  | <JSON boolean conjunction> <double ampersand> <JSON boolean negation>

<JSON boolean disjunction> ::=
    <JSON boolean conjunction>
  | <JSON boolean disjunction> <double vertical bar> <JSON boolean conjunction>

<JSON path predicate> ::=
  <JSON boolean disjunction>

9.44 Datetime templates

Function
Specify the templates to use to convert between datetime types and character string types.


Format
<datetime template> ::=
  { <datetime template part> }...

<datetime template part> ::=
    <datetime template field>
  | <datetime template delimiter>

<datetime template field> ::=
    <datetime template year>
  | <datetime template rounded year>
  | <datetime template month>
  | <datetime template day of month>
  | <datetime template day of year>
  | <datetime template 12-hour>
  | <datetime template 24-hour>
  | <datetime template minute>
  | <datetime template second of minute>
  | <datetime template second of day>
  | <datetime template fraction>
  | <datetime template am/pm>
  | <datetime template time zone hour>
  | <datetime template time zone minute>

<datetime template delimiter> ::=
    <minus sign>
  | <period>
  | <solidus>
  | <comma>
  | <apostrophe>
  | <semicolon>
  | <colon>
  | <space>

<datetime template year> ::=
  YYYY | YYY | YY | Y

<datetime template rounded year> ::=
  RRRR | RR
<datetime template month> ::=
  MM

<datetime template day of month> ::=
  DD

<datetime template day of year> ::=
  DDD

<datetime template 12-hour> ::=
  HH | HH12

<datetime template 24-hour> ::=
  HH24

<datetime template minute> ::=
  MI

<datetime template second of minute> ::=
  SS

<datetime template second of day> ::=
  SSSSS

<datetime template fraction> ::=
  FF1 | FF2 | FF3 | FF4 | FF5 | FF6 | FF7 | FF8 | FF9

<datetime template am/pm> ::=
  A.M. | P.M.

<datetime template time zone hour> ::=
  TZH

<datetime template time zone minute> ::=
  TZM


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>

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

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

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

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

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

<interval fractional seconds precision> ::=
  <unsigned integer>

<interval leading field precision> ::=
  <unsigned integer>

10.2 <language clause>


Function
Specify a 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> }... ] [ <copartition clause> ] ]
      <right paren>

<SQL argument> ::=
    <value expression>
  | <generalized expression>
  | <target specification>
  | <contextually typed value specification>
  | <named argument specification>
  | <table argument>
  | <descriptor argument>

<generalized expression> ::=
  <value expression> AS <path-resolved user-defined type name>

<named argument specification> ::=
  <SQL parameter name> <named argument assignment token>
      <named argument SQL argument>

<named argument SQL argument> ::=
    <value expression>
  | <target specification>
  | <contextually typed value specification>
  | <table argument>
  | <descriptor argument>

<table argument> ::=
  <table argument proper>
      [ [ AS ] <table argument correlation name>
      [ <table argument parenthesized derived column list> ] ]
      [ <table argument partitioning> ]
      [ <table argument pruning> ]
      [ <table argument ordering> ]

<table argument correlation name> ::=
  <correlation name>

<table argument parenthesized derived column list> ::=
  <parenthesized derived column list>

<table argument proper> ::=
    TABLE <left paren> <table or query name> <right paren>
  | TABLE <table subquery>
  | <table function invocation>

<table function invocation> ::=
  <routine invocation>

<table argument partitioning> ::=
  PARTITION BY <table argument partitioning list>

<table argument partitioning list> ::=
    <column reference>
  | <left paren> [ <column reference> [ { <comma> <column reference> }... ] ] <right paren>

<table argument pruning> ::=
    PRUNE WHEN EMPTY
  | KEEP WHEN EMPTY

<table argument ordering> ::=
  ORDER BY <table argument ordering list>

<table argument ordering list> ::=
    <table argument ordering column>
  | <left paren>
      <table argument ordering column>
      [ { <comma> <table argument ordering column> }... ]
      <right paren>

<table argument ordering column> ::=
  <column reference> [ <ordering specification> ] [ <null ordering> ]

<copartition clause> ::=
  COPARTITION <copartition list>

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

<copartition specification> ::=
  <left paren> <range variable> <comma> <range variable>
      [ { <comma> <range variable> }... ] <right paren>

<range variable> ::=
    <table name>
  | <query name>
  | <correlation name>

<descriptor argument> ::=
    <descriptor value constructor>
  | CAST <left paren> NULL AS DESCRIPTOR <right paren>


10.5 <character set specification>

Function
Identify a character set.


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

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

<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 ] [ <constraint enforcement> ]
  | [ NOT ] DEFERRABLE [ <constraint check time> ] [ <constraint enforcement> ]
  | <constraint enforcement>

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

<constraint enforcement> ::=
  [ NOT ] ENFORCED

10.9 <aggregate function>

Function
Specify a value computed from a collection of rows.


Format
<aggregate function> ::=
    COUNT <left paren> <asterisk> <right paren> [ <filter clause> ]
  | <general set function> [ <filter clause> ]
  | <binary set function> [ <filter clause> ]
  | <ordered set function> [ <filter clause> ]
  | <array aggregate function> [ <filter clause> ]
  | <row pattern count function> [ <filter clause> ]
  | <JSON aggregate function> [ <filter clause> ]

<general set function> ::=
  <set function type> <left paren> [ <set quantifier> ]
      <value expression> <right paren>

<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> ::=
  <binary set function type> <left paren> <dependent variable expression> <comma>
      <independent variable expression> <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>

<ordered set function> ::=
    <hypothetical set function>
  | <inverse distribution function>
  | <listagg set function>

<hypothetical set function> ::=
  <rank function type> <left paren>
      <hypothetical set function value expression list> <right paren>
      <within group specification>

<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> ::=
  <inverse distribution function type> <left paren>
      <inverse distribution function argument> <right paren>
      <within group specification>

<inverse distribution function argument> ::=
  <numeric value expression>

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

<listagg set function> ::=
  LISTAGG <left paren> [ <set quantifier> ] <character value expression>
      <comma> <listagg separator> [ <listagg overflow clause> ] <right paren>
      <within group specification>

<listagg separator> ::=
  <character string literal>

<listagg overflow clause> ::=
  ON OVERFLOW <overflow behavior>

<overflow behavior> ::=
    ERROR
  | TRUNCATE [ <listagg truncation filler> ] <listagg count indication>

<listagg truncation filler> ::=
  <character string literal>

<listagg count indication> ::=
    WITH COUNT>
  | WITHOUT COUNT>

<array aggregate function> ::=
  ARRAY_AGG
      <left paren> <value expression> [ ORDER BY <sort specification list> ] <right paren>

<row pattern count function> ::=
  COUNT <left paren> <row pattern variable name> <period> <asterisk> <right paren>

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


10.11 <JSON aggregate function>

Function
Construct a JSON object or a JSON array from an aggregation of SQL data.


Format
<JSON aggregate function> ::=
    <JSON object aggregate constructor>
  | <JSON array aggregate constructor>

<JSON object aggregate constructor> ::=
  JSON_OBJECTAGG <left paren>
      <JSON name and value>
      [ <JSON constructor null clause> ]
      [ <JSON key uniqueness constraint> ]
      [ <JSON output clause> ]
      <right paren>

<JSON array aggregate constructor> ::=
  JSON_ARRAYAGG <left paren>
      <JSON value expression>
      [ <JSON array aggregate order by clause> ]
      [ <JSON constructor null clause> ]
      [ <JSON output clause> ]
      <right paren>

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


10.12 <JSON value expression>

Function
Specify a value to be used as input by an SQL/JSON function.


Format
<JSON value expression> ::=
  <value expression> [ <JSON input clause> ]

<JSON input clause> ::=
  FORMAT <JSON representation>


10.13 <JSON output clause>

Function
Specify the data type, format, and encoding of the JSON text created by a JSON-returning function.


Format
<JSON output clause> ::=
  RETURNING <data type> [ FORMAT <JSON representation> ]

<JSON representation> ::=
    JSON [ ENCODING { UTF8 | UTF16 | UTF32 } ]
  | <implementation-defined JSON representation option>

<implementation-defined JSON representation option> ::=
  !! See the Syntax Rules.


10.14 <JSON API common syntax>

Subclause Signature
"<JSON API common syntax>" [General Rules] (
  Parameter: "JSON API COMMON SYNTAX"
) Returns: "STATUS" and "SQL/JSON SEQUENCE"


Function
Define the inputs to JSON_VALUE, JSON_QUERY, JSON_TABLE, and JSON_EXISTS.


Format
<JSON API common syntax> ::=
  <JSON context item> <comma> <JSON path specification> [ AS <JSON table path name> ]
      [ <JSON passing clause> ]

<JSON context item> ::=
  <JSON value expression>

<JSON path specification> ::=
  <character string literal>

<JSON passing clause> ::=
  PASSING <JSON argument> [ { <comma> <JSON argument> }... ]

<JSON argument> ::=
  <JSON value expression> AS <identifier>


11 Schema definition and manipulation


11.1 <schema definition>

Function
Define a schema.


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

<schema character set or path> ::=
    <schema character set specification>
  | <schema path specification>
  | <schema character set specification> <schema path specification>
  | <schema path specification> <schema character set specification>

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

<schema authorization identifier> ::=
  <authorization identifier>

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

<schema path specification> ::=
  <path specification>

<schema element> ::=
      <table definition>
  |   <view definition>
  |   <domain definition>
  |   <character set definition>
  |   <collation definition>
  |   <transliteration definition>
  |   <assertion definition>
  |   <trigger definition>
  |   <user-defined type definition>
  |   <user-defined cast definition>
  |   <user-defined ordering definition>
  |   <transform definition>
  |   <schema routine>
  |   <sequence generator definition>
  |   <grant statement>
  |   <role definition>

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>
      [ WITH <system versioning clause> ]
      [ ON COMMIT <table commit action> ROWS ]

<table contents source> ::=
    <table element list>
  | <typed table clause>
  | <as subquery clause>

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

<global or local> ::=
    GLOBAL
  | LOCAL

<system versioning clause> ::=
  SYSTEM VERSIONING

<table commit action> ::=
    PRESERVE
  | DELETE

<table element list> ::=
  <left paren> <table element> [ { <comma> <table element> }... ] <right paren>

<table element> ::=
    <column definition>
  | <table period definition>
  | <table constraint definition>
  | <like clause>

<typed table clause> ::=
  OF <path-resolved user-defined type name> [ <subtable clause> ]
      [ <typed table element list> ]

<typed table element list> ::=
  <left paren> <typed table element>
      [ { <comma> <typed table element> }... ] <right paren>

<typed table element> ::=
    <column options>
  | <table constraint definition>
  | <self-referencing column specification>

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

<like option> ::=
    <identity option>
  | <column default option>
  | <generation option>

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

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

<generation option> ::=
    INCLUDING GENERATED
  | EXCLUDING GENERATED

<as subquery clause> ::=
  [ <left paren> <column name list> <right paren> ] AS <table subquery>
      <with or without data>

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

<table period definition> ::=
  <system or application time period specification>
      <left paren> <period begin column name> <comma> <period end column name> <right paren>

<system or application time period specification> ::=
    <system time period specification>
  | <application time period specification>

<system time period specification> ::=
  PERIOD FOR SYSTEM_TIME

<application time period specification> ::=
  PERIOD FOR <application time period name>

<application time period name> ::=
  <identifier>

<period begin column name> ::=
  <column name>

<period end column name> ::=
  <column name>

11.4 <column definition>

Function
Define a column of a base table.


Format
<column definition> ::=
  <column name> [ <data type or domain name> ]
      [ <default clause> | <identity column specification> | <generation clause>
      | <system time period start column specification>
      | <system time period end column specification> ]
      [ <column constraint definition>... ]
      [ <collate clause> ]

<data type or domain name> ::=
    <data type>
  | <domain name>

<system time period start column specification> ::=
  <timestamp generation rule> AS ROW START

<system time period end column specification> ::=
  <timestamp generation rule> AS ROW END

<timestamp generation rule> ::=
  GENERATED ALWAYS

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

<column constraint> ::=
    NOT NULL
  | <unique specification>
  | <references specification>
  | <check constraint definition>

<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_CATALOG
  | CURRENT_SCHEMA
  | CURRENT_PATH
  | <implicitly typed value specification>

11.6 <table constraint definition>

Function
Specify an integrity constraint.


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

<table constraint> ::=
    <unique constraint definition>
  | <referential constraint definition>
  | <check constraint definition>

11.7 <unique constraint definition>

Function
Specify a uniqueness constraint for a table.


Format
<unique constraint definition> ::=
    <unique specification> <left paren> <unique column list> [ <comma> <without overlap
    specification> ] <right paren>
  | UNIQUE ( VALUE )

<unique specification> ::=
    UNIQUE
  | PRIMARY KEY

<unique column list> ::=
  <column name list>

<without overlap specification> ::=
  <application time period name> WITHOUT OVERLAPS

11.8 <referential constraint definition>

Function
Specify a referential constraint.


Format
<referential constraint definition> ::=
  FOREIGN KEY <left paren> <referencing column list>
      [ <comma> <referencing period specification> ] <right paren>
      <references specification>

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

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

<referencing column list> ::=
  <column name list>

<referencing period specification> ::=
  PERIOD <application time period name>

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

<referenced column list> ::=
  <column name list>

<referenced period specification> ::=
  PERIOD <application time period name>

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

<alter table action> ::=
    <add column definition>
  | <alter column definition>
  | <drop column definition>
  | <add table constraint definition>
  | <alter table constraint definition>
  | <drop table constraint definition>
  | <add table period definition>
  | <drop table period definition>
  | <add system versioning clause>
  | <drop system versioning clause>

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>

<alter column action> ::=
    <set column default clause>
  | <drop column default clause>
  | <set column not null clause>
  | <drop column not null clause>
  | <add column scope clause>
  | <drop column scope clause>
  | <alter column data type clause>
  | <alter identity column specification>
  | <drop identity property clause>
  | <drop column generation expression clause>

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 <set column not null clause>

Function
Add a not null constraint to a column.


Format
<set column not null clause> ::=
  SET NOT NULL

11.16 <drop column not null clause>

Function
Drop a not null constraint on a column.


Format
<drop column not null clause> ::=
  DROP NOT NULL

11.17 <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.18 <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.19 <alter column data type clause>

Function
Change the declared type of a column.


Format
<alter column data type clause> ::=
  SET DATA TYPE <data type>

11.20 <alter identity column specification>

Function
Change the options specified for an identity column.


Format
<alter identity column specification> ::=
    <set identity column generation clause> [ <alter identity column option>... ]
  | <alter identity column option>...

<set identity column generation clause> ::=
  SET GENERATED { ALWAYS | BY DEFAULT }

<alter identity column option> ::=
    <alter sequence generator restart option>
  | SET <basic sequence generator option>

11.21 <drop identity property clause>

Function
Convert an identity column to a column that is not an identity column.


Format
<drop identity property clause> ::=
  DROP IDENTITY

11.22 <drop column generation expression clause>

Function
Convert a generated column to a column that is not a generated column.


Format
<drop column generation expression clause> ::=
  DROP EXPRESSION

11.23 <drop column definition>

Function
Destroy a column of a base table.


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

11.24 <add table constraint definition>

Function
Add a constraint to a table.


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

11.25 <alter table constraint definition>

Function
Change the definition of a table constraint.


Format
<alter table constraint definition> ::=
  ALTER CONSTRAINT <constraint name> <constraint enforcement>

11.26 <drop table constraint definition>

Function
Destroy a constraint on a table.


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

11.27 <add table period definition>

Function
Add a system-time period or an application-time period to a persistent base table.


Format
<add table period definition> ::=
  ADD <table period definition> [ <add system time period column list> ]

<add system time period column list> ::=
  ADD [ COLUMN ] <column definition 1> ADD [ COLUMN ] <column definition 2>

<column definition 1> ::=
  <column definition>

<column definition 2> ::=
  <column definition>

11.28 <drop table period definition>

Function
Remove a system-time period or application-time period from a persistent base table.


Format
<drop table period definition> ::=
  DROP <system or application time period specification> <drop behavior>

11.29 <add system versioning clause>

Function
Alter a regular persistent base table to a system-versioned table.


Format
<add system versioning clause> ::=
  ADD <system versioning clause>

11.30 <drop system versioning clause>

Function
Change a system-versioned table into a regular persistent base table.


Format
<drop system versioning clause> ::=
  DROP SYSTEM VERSIONING <drop behavior>

11.31 <drop table statement>

Function
Destroy a table.


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

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

<view specification> ::=
    <regular view specification>
  | <referenceable view specification>

<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 element> ::=
    <self-referencing column specification>
  | <view column option>

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

<levels clause> ::=
    CASCADED
  | LOCAL

<view column list> ::=
  <column name list>

11.33 <drop view statement>

Function
Destroy a view.


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

11.34 <domain definition>

Function
Define a domain.


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

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

11.35 <alter domain statement>

Function
Change a domain and its definition.


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

<alter domain action> ::=
    <set domain default clause>
  | <drop domain default clause>
  | <add domain constraint definition>
  | <drop domain constraint definition>

11.36 <set domain default clause>

Function
Set the default value in a domain.


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

11.37 <drop domain default clause>

Function
Remove the default clause of a domain.


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


11.38 <add domain constraint definition>

Function
Add a constraint to a domain.


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

11.39 <drop domain constraint definition>

Function
Destroy a constraint on a domain.


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

11.40 <drop domain statement>

Function
Destroy a domain.


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

11.41 <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.42 <drop character set statement>

Function
Destroy a character set.


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

11.43 <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.44 <drop collation statement>

Function
Destroy a collation.


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

11.45 <transliteration definition>

Function
Define a character transliteration.


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

<source character set specification> ::=
  <character set specification>

<target character set specification> ::=
  <character set specification>

<transliteration source> ::=
    <existing transliteration name>
  | <transliteration routine>

<existing transliteration name> ::=
  <transliteration name>

<transliteration routine> ::=
  <specific routine designator>

11.46 <drop transliteration statement>

Function
Destroy a character transliteration.


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

11.47 <assertion definition>

Function
Specify an integrity constraint.


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

11.48 <drop assertion statement>

Function
Destroy an assertion.


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

11.49 <trigger definition>

Function
Define triggered SQL-statements.


Format
<trigger definition> ::=
  CREATE TRIGGER <trigger name> <trigger action time> <trigger event>
      ON <table name> [ REFERENCING <transition table or variable list> ]
      <triggered action>

<trigger action time> ::=
    BEFORE
  | AFTER
  | INSTEAD OF

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

<trigger column list> ::=
  <column name list>

<triggered action> ::=
  [ FOR EACH { ROW | STATEMENT } ]
      [ <triggered when clause> ]
      <triggered SQL statement>

<triggered when clause> ::=
  WHEN <left paren> <search condition> <right paren>

<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.50 <drop trigger statement>

Function
Destroy a trigger.


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

11.51 <user-defined type definition>

Function
Define a user-defined type.


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

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

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

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

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

<supertype name> ::=
  <path-resolved user-defined type name>

<representation> ::=
    <predefined type>
  | <collection type>
  | <member list>

<member list> ::=
  <left paren> <member> [ { <comma> <member> }... ] <right paren>

<member> ::=
  <attribute definition>

<instantiable clause> ::=
    INSTANTIABLE
  | NOT INSTANTIABLE

<finality> ::=
    FINAL
  | NOT FINAL

<reference type specification> ::=
    <user-defined representation>
  | <derived representation>
  | <system-generated representation>

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

<list of attributes> ::=
  <left paren> <attribute name> [ { <comma> <attribute name> }... ] <right paren>

<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> }... ]

<method specification> ::=
    <original method specification>
  | <overriding 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>...

<method characteristic> ::=
       <language clause>
     | <parameter style clause>
     | <deterministic characteristic>
     | <SQL-data access indication>
     | <null-call clause>

11.52 <attribute definition>

Function
Define an attribute of a structured type.


Format
<attribute definition> ::=
     <attribute name> <data type>
         [ <attribute default> ]
         [ <collate clause> ]

<attribute default> ::=
  <default clause>

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

<alter type action> ::=
    <add attribute definition>
  | <drop attribute definition>
  | <add original method specification>
  | <add overriding method specification>
  | <drop method specification>

11.54 <add attribute definition>

Function
Add an attribute to a user-defined type.


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

11.55 <drop attribute definition>

Function
Destroy an attribute of a user-defined type.


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

11.56 <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.57 <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.58 <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.59 <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.60 <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-invoked procedure> ::=
  PROCEDURE <schema qualified routine name> <SQL parameter declaration list>
      <routine characteristics>
      <routine body>

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

<SQL parameter declaration list> ::=
  <left paren> [ <SQL parameter declaration>
      [ { <comma> <SQL parameter declaration> }... ] ] <right paren>

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

<parameter default> ::=
    <value expression>
  | <contextually typed value specification>
  | <descriptor value constructor>

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

<parameter type> ::=
   <data type> [ <locator indication> ]
 | <generic table parameter type>
 | <descriptor parameter type>

<generic table parameter type> ::=
  TABLE [ <pass through option> ] [ <generic table semantics> ]

<pass through option> ::=
    PASS THROUGH
  | NO PASS THROUGH

<generic table semantics> ::=
    WITH ROW SEMANTICS
  | WITH SET SEMANTICS [ <generic table pruning> ]

<generic table pruning> ::=
    PRUNE ON EMPTY
  | KEEP ON EMPTY

<descriptor parameter type> ::=
  DESCRIPTOR

<locator indication> ::=
  AS LOCATOR

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

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

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

<routine characteristic> ::=
    <language clause>
  | <parameter style clause>
  | SPECIFIC <specific name>
  | <deterministic characteristic>
  | <SQL-data access indication>
  | <null-call clause>
  | <returned result sets characteristic>
  | <savepoint level indication>

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

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

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

<dispatch clause> ::=
  STATIC DISPATCH

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

<returns type> ::=
    <returns data type> [ <result cast> ]
  | <returns table type>

<returns table type> ::=
    TABLE [ <table function column list> ]
  | ONLY PASS THROUGH

<table function column list> ::=
  <left paren> <table function column list element>
      [ { <comma> <table function column list element> }... ] <right paren>

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

<routine body> ::=
    <SQL routine spec>
  | <external body reference>
  | <polymorphic table function body>

<SQL routine spec> ::=
  [ <rights clause> ] <SQL routine body>

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

<SQL routine body> ::=
  <SQL procedure statement>

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

<polymorphic table function body> ::=
  [ <PTF private parameters> ]
      [ DESCRIBE WITH <PTF describe component procedure> ]
      [ START WITH <PTF start component procedure> ]
      FULFILL WITH <PTF fulfill component procedure>
      [ FINISH WITH <PTF finish component procedure> ]
<PTF private parameters> ::=
  PRIVATE [ DATA ] <private parameter declaration list>

<private parameter declaration list> ::=
  <left paren> [ <SQL parameter declaration>
      [ { <comma> <SQL parameter declaration> }... ] ]
      <right paren>

<PTF describe component procedure> ::=
  <specific routine designator>

<PTF start component procedure> ::=
  <specific routine designator>

<PTF fulfill component procedure> ::=
  <specific routine designator>

<PTF finish component procedure> ::=
  <specific routine designator>

<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 returned 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.61 <alter routine statement>

Function
Alter a characteristic of an SQL-invoked routine.


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

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

<alter routine characteristic> ::=
    <language clause>
  | <parameter style clause>
  | <SQL-data access indication>
  | <null-call clause>
  | <returned result sets characteristic>
  | NAME <external routine name>

<alter routine behavior> ::=
  RESTRICT

11.62 <drop routine statement>

Function
Destroy an SQL-invoked routine.


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

11.63 <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.64 <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.65 <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>

<ordering form> ::=
    <equals ordering form>
  | <full ordering form>

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

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

<ordering category> ::=
    <relative category>
  | <map category>
  | <state 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.66 <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.67 <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>...

<transform group> ::=
  <group name> <left paren> <transform element list> <right paren>

<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.68 <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 group> ::=
  <group name> <left paren> <alter transform action list> <right paren>

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

<alter transform action> ::=
    <add transform element list>
  | <drop transform element list>

11.69 <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.70 <drop transform element list>

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


Format
<drop transform element list> ::=
  DROP <left paren> <transform kind>
      [ <comma> <transform kind> ] <drop behavior> <right paren>

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

11.71 <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.72 <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>...

<sequence generator option> ::=
    <sequence generator data type option>
  | <common sequence generator options>

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

<common sequence generator option> ::=
    <sequence generator start with option>
  | <basic sequence generator option>

<basic sequence generator option> ::=
    <sequence generator increment by option>
  | <sequence generator maxvalue option>
  | <sequence generator minvalue option>
  | <sequence generator cycle 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.73 <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 option> ::=
    <alter sequence generator restart option>
  | <basic sequence generator option>

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

<sequence generator restart value> ::=
  <signed numeric literal>

11.74 <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
<grant statement> ::=
    <grant privilege statement>
  | <grant role statement>

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 name> ::=
      [ TABLE ] <table name>
  |   DOMAIN <domain name>
  |   COLLATION <collation name>
  |   CHARACTER SET <character set name>
  |   TRANSLATION <transliteration name>
  |   TYPE <schema-resolved user-defined type name>
  |   SEQUENCE <sequence generator name>
  |   <specific routine designator>

<object privileges> ::=
    ALL PRIVILEGES
  | <action> [ { <comma> <action> }... ]

<action> ::=
    SELECT
  | SELECT <left paren> <privilege column list> <right paren>
  | SELECT <left paren> <privilege method list> <right paren>
  | DELETE
  | INSERT [ <left paren> <privilege column list> <right paren> ]
  | UPDATE [ <left paren> <privilege column list> <right paren> ]
  | REFERENCES [ <left paren> <privilege column list> <right paren> ]
  | USAGE
  | TRIGGER
  | UNDER
  | EXECUTE

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

<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 statement> ::=
    <revoke privilege statement>
  | <revoke role statement>

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


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

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

<module 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> }... ]

<module contents> ::=
    <declare cursor>
  | <dynamic declare cursor>
  | <externally-invoked procedure>

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.


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

<host parameter declaration list> ::=
  <left paren> <host parameter declaration>
      [ { <comma> <host parameter declaration> }... ] <right paren>

<host parameter declaration> ::=
    <host parameter name> <host parameter data type>
  | <status parameter>

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

<status parameter> ::=
  SQLSTATE

13.4 <SQL procedure statement>

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


Format
<SQL procedure statement> ::=
  <SQL executable statement>

<SQL executable statement> ::=
    <SQL schema statement>
  | <SQL data statement>
  | <SQL control statement>
  | <SQL transaction statement>
  | <SQL connection statement>
  | <SQL session statement>
  | <SQL diagnostics statement>
  | <SQL dynamic statement>

<SQL schema statement> ::=
    <SQL schema definition statement>
  | <SQL schema manipulation statement>

<SQL schema definition statement> ::=
    <schema definition>
  | <table definition>
  | <view definition>
  | <SQL-invoked routine>
  | <grant statement>
  | <role definition>
  | <domain definition>
  | <character set definition>
  | <collation definition>
  | <transliteration definition>
  | <assertion definition>
  | <trigger definition>
  | <user-defined type definition>
  | <user-defined cast definition>
  | <user-defined ordering definition>
  | <transform definition>
  | <sequence generator definition>

<SQL schema manipulation statement> ::=
    <drop schema statement>
  | <alter table statement>
  | <drop table statement>
  | <drop view statement>
  | <alter routine statement>
  | <drop routine statement>
  | <drop user-defined cast statement>
  | <revoke statement>
  | <drop role statement>
  | <alter domain statement>
  | <drop domain statement>
  | <drop character set statement>
  | <drop collation statement>
  | <drop transliteration statement>
  | <drop assertion statement>
  | <drop trigger statement>
  | <alter type statement>
  | <drop data type statement>
  | <drop user-defined ordering statement>
  | <alter transform statement>
  | <drop transform statement>
  | <alter sequence generator statement>
  | <drop sequence generator statement>

<SQL data statement> ::=
    <open statement>
  | <fetch statement>
  | <close statement>
  | <select statement: single row>
  | <free locator statement>
  | <hold locator statement>
  | <SQL data change statement>

<SQL data change statement> ::=
    <delete statement: positioned>
  | <delete statement: searched>
  | <insert statement>
  | <update statement: positioned>
  | <update statement: searched>
  | <truncate table statement>
  | <merge statement>

<SQL control statement> ::=
    <call statement>
  | <return statement>

<SQL transaction statement> ::=
    <start transaction statement>
  | <set transaction statement>
  | <set constraints mode statement>
  | <savepoint statement>
  | <release savepoint statement>
  | <commit statement>
  | <rollback statement>

<SQL connection statement> ::=
    <connect statement>
  | <set connection statement>
  | <disconnect statement>

<SQL session statement> ::=
    <set session user identifier statement>
  | <set role statement>
  | <set local time zone statement>
  | <set session characteristics statement>
  | <set catalog statement>
  | <set schema statement>
  | <set names statement>
  | <set path statement>
  | <set transform group statement>
  | <set session collation statement>

<SQL diagnostics statement> ::=
  <get diagnostics statement>

<SQL dynamic statement> ::=
    <SQL descriptor statement>
  | <prepare statement>
  | <deallocate prepared statement>
  | <describe statement>
  | <execute statement>
  | <execute immediate statement>
  | <SQL dynamic data statement>
  | <copy descriptor statement>
  | <pipe row statement>

<SQL dynamic data statement> ::=
    <allocate extended dynamic cursor statement>
  | <allocate received cursor statement>
  | <dynamic open statement>
  | <dynamic fetch statement>
  | <dynamic close statement>
  | <dynamic delete statement: positioned>
  | <dynamic update statement: positioned>

<SQL descriptor statement> ::=
    <allocate descriptor statement>
  | <deallocate descriptor statement>
  | <set descriptor statement>
  | <get descriptor statement>

14 Data manipulation


14.1 <declare cursor>

Function
Declare a standing cursor.


Format
<declare cursor> ::=
  DECLARE <cursor name> <cursor properties>
      FOR <cursor specification>

14.2 <cursor properties>

Function
Specify the declared properties of a cursor.


Format
<cursor properties> ::=
  [ <cursor sensitivity> ] [ <cursor scrollability> ] CURSOR
      [ <cursor holdability> ]
      [ <cursor returnability> ]

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

<cursor scrollability> ::=
    SCROLL
  | NO SCROLL

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

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

14.3 <cursor specification>

Function
Define a result set.


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

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

14.4 <open statement>

Function
Open a standing cursor.


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

14.5 <fetch statement>

Function
Position a standing cursor on a specified row of the standing cursor's result set 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.6 <close statement>

Function
Close a standing cursor.


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

14.7 <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.8 <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>

<target table> ::=
    <table name>
  | ONLY <left paren> <table name> <right paren>

14.9 <delete statement: searched>

Function
Delete rows of a table.


Format
<delete statement: searched> ::=
  DELETE FROM <target table>
      [ FOR PORTION OF <application time period name>
        FROM <point in time 1> TO <point in time 2> ]
      [ [ AS ] <correlation name> ]
      [ WHERE <search condition> ]

14.10 <truncate table statement>

Function
Delete all rows of a base table without causing any triggered action.


Format
<truncate table statement> ::=
  TRUNCATE TABLE <target table> [ <identity column restart option> ]

<identity column restart option> ::=
    CONTINUE IDENTITY
  | RESTART IDENTITY

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

<from subquery> ::=
  [ <left paren> <insert column list> <right paren> ]
      [ <override clause> ]
      <query expression>

<from constructor> ::=
  [ <left paren> <insert column list> <right paren> ]
      [ <override clause> ]
      <contextually typed table value constructor>

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

<from default> ::=
  DEFAULT VALUES

<insert column list> ::=
  <column name list>

14.12 <merge statement>

Function
Conditionally update and/or delete rows of a table and/or insert new rows into a table.


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

<merge correlation name> ::=
  <correlation name>

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

<merge when clause> ::=
    <merge when matched clause>
  | <merge when not matched clause>

<merge when matched clause> ::=
  WHEN MATCHED [ AND <search condition> ]
      THEN <merge update or delete specification>

<merge update or delete specification> ::=
    <merge update specification>
  | <merge delete specification>

<merge when not matched clause> ::=
  WHEN NOT MATCHED [ AND <search condition> ]
      THEN <merge insert specification>

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

<merge delete specification> ::=
  DELETE

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

<merge insert value list> ::=
  <left paren>
      <merge insert value element> [ { <comma> <merge insert value element> }... ]
      <right paren>

<merge insert value element> ::=
    <value expression>
  | <contextually typed value specification>

14.13 <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.14 <update statement: searched>

Function
Update rows of a table.


Format
<update statement: searched> ::=
  UPDATE <target table>
      [ FOR PORTION OF <application time period name>
        FROM <point in time 1> TO <point in time 2> ]
      [ [ AS ] <correlation name> ]
      SET <set clause list>
      [ WHERE <search condition> ]

14.15 <set clause list>

Function
Specify a list of updates.


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

<set clause> ::=
    <multiple column assignment>
  | <set target> <equals operator> <update source>

<set target> ::=
    <update target>
  | <mutated 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>

<update target> ::=
    <object column>
  | <object column>
      <left bracket or trigraph> <simple value specification> <right bracket or trigraph>

<object column> ::=
  <column name>

<mutated set clause> ::=
  <mutated target> <period> <method name>

<mutated target> ::=
    <object column>
  | <mutated set clause>

<update source> ::=
    <value expression>
  | <contextually typed value specification>

14.16 <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.17 <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> }... ]

<locator reference> ::=
    <host parameter name>
  | <embedded variable name>
  | <dynamic parameter specification>

14.18 <hold locator statement>

Function
Mark a locator variable as being holdable.


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


16 Control statements

16.1 <call statement>


Function
Invoke an SQL-invoked routine.


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

16.2 <return statement>

Function
Return a value from an SQL routine that is an SQL-invoked function.


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

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

17 Transaction management


17.1 <start transaction statement>

Function
Start an SQL-transaction and set its characteristics.


Format
<start transaction statement> ::=
  START TRANSACTION [ <transaction characteristics> ]

17.2 <set transaction statement>

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


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

17.3 <transaction characteristics>

Function
Specify transaction characteristics.


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

<transaction mode> ::=
    <isolation level>
  | <transaction access mode>
  | <diagnostics size>

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

17.4 <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 713 - 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> }... ]

17.5 <savepoint statement>

Function
Establish a savepoint.


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

<savepoint specifier> ::=
  <savepoint name>

17.6 <release savepoint statement>

Function
Destroy a savepoint.


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

17.7 <commit statement>

Function
Terminate the current SQL-transaction with commit.


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

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


18 Connection management


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

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

18.3 <disconnect statement>

Function
Terminate an SQL-connection.


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

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

19 Session management

19.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> ::=
  <session transaction characteristics>

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

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

19.3 <set role statement>

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


Format
<set role statement> ::=
  SET ROLE <role specification>

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

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

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

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

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

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

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

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

20 Dynamic SQL

20.2 <allocate descriptor statement>

Function
Allocate an SQL descriptor area.


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

<occurrences> ::=
  <simple value specification>


20.3 <deallocate descriptor statement>

Function
Deallocate an SQL descriptor area.


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

20.4 <get descriptor statement>

Function
Get information from an SQL descriptor area.


Format
<get descriptor statement> ::=
  GET [ SQL ] DESCRIPTOR <descriptor name> <get descriptor information>

<get descriptor information> ::=
    <get header information> [ { <comma> <get header information> }... ]
  | VALUE <item number> <get item information>
      [ { <comma> <get item information> }... ]

<get header information> ::=
  <simple target specification 1> <equals operator> <header item name>

<header item name> ::=
    COUNT
  | KEY_TYPE
  | DYNAMIC_FUNCTION
  | DYNAMIC_FUNCTION_CODE
  | TOP_LEVEL_COUNT

<get item information> ::=
  <simple target specification 2> <equals operator> <descriptor item name>

<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
  | NULL_ORDERING
  | 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
  | SORT_DIRECTION
  | TYPE
  | UNNAMED
  | USER_DEFINED_TYPE_CATALOG
  | USER_DEFINED_TYPE_NAME
  | USER_DEFINED_TYPE_SCHEMA
  | USER_DEFINED_TYPE_CODE

20.5 <set descriptor statement>

Function
Set information in an SQL descriptor area.


Format
<set descriptor statement> ::=
  SET [ SQL ] DESCRIPTOR <descriptor name> <set descriptor information>

<set descriptor information> ::=
    <set header information> [ { <comma> <set header information> }... ]
  | VALUE <item number> <set item information>
      [ { <comma> <set item information> }... ]

<set header information> ::=
  <header item name> <equals operator> <simple value specification 1>

<set item information> ::=
  <descriptor item name> <equals operator> <simple value specification 2>

<simple value specification 1> ::=
  <simple value specification>

<simple value specification 2> ::=
  <simple value specification>

20.6 <copy descriptor statement>

Function
Copy one SQL descriptor area, in whole or in part, to another SQL descriptor area.


Format
<copy descriptor statement> ::=
    <copy whole descriptor statement>
  | <copy item descriptor statement>

<copy whole descriptor statement> ::=
  COPY <source descriptor name> TO <target descriptor name>

<copy item descriptor statement> ::=
  COPY <source descriptor name> VALUE <item number 1>
      <left paren> <copy descriptor options> <right paren>
      TO <target descriptor name> VALUE <item number 2>

<source descriptor name> ::=
  <descriptor name>

<target descriptor name> ::=
  <PTF descriptor name>

<item number 1> ::=
  <simple value specification>

<item number 2> ::=
  <simple value specification>

<copy descriptor options> ::=
    NAME
  | TYPE
  | NAME <comma> TYPE
  | DATA

20.7 <prepare statement>

Function
Prepare a statement for execution.


Format
<prepare statement> ::=
  PREPARE <SQL statement name> [ <attributes specification> ]
      FROM <SQL statement variable>

<attributes specification> ::=
  ATTRIBUTES <attributes variable>

<attributes variable> ::=
  <simple value specification>

<SQL statement variable> ::=
  <simple value specification>

<preparable statement> ::=
    <preparable SQL data statement>
  | <preparable SQL schema statement>
  | <preparable SQL transaction statement>
  | <preparable SQL control statement>
  | <preparable SQL session statement>
  | <preparable implementation-defined statement>

<preparable SQL data statement> ::=
    <delete statement: searched>
  | <dynamic single row select statement>
  | <insert statement>
  | <dynamic select statement>
  | <update statement: searched>
  | <truncate table statement>
  | <merge statement>
  | <preparable dynamic delete statement: positioned>
  | <preparable dynamic update statement: positioned>
  | <hold locator statement>
  | <free locator statement>

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

20.8 <cursor attributes>

Function
Specify a list of cursor attributes.


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

<cursor attribute> ::=
    <cursor sensitivity>
  | <cursor scrollability>
  | <cursor holdability>
  | <cursor returnability>

20.9 <deallocate prepared statement>

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


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

20.10 <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 statement> ::=
    <describe input statement>
  | <describe output statement>

<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 <cursor name> STRUCTURE

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

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

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

20.14 <execute immediate statement>

Function
Dynamically prepare and execute a preparable statement.


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

20.15 <dynamic declare cursor>

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


Format
<dynamic declare cursor> ::=
  DECLARE <cursor name>
      <cursor properties>
      FOR <statement name>

20.16 <descriptor value constructor>

Function
Construct a PTF descriptor.


Format
<descriptor value constructor> ::=
  DESCRIPTOR <left paren> <descriptor column list> <right paren>

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

<descriptor column specification> ::=
  <column name> [ <data type> ]

20.17 <allocate extended dynamic cursor statement>

Function
Define a cursor based on a prepared statement for a <cursor specification>.


Format
<allocate extended dynamic cursor statement> ::=
  ALLOCATE <extended cursor name>
      <cursor properties>
      FOR <extended statement name>

20.18 <allocate received cursor statement>

Function
Assign a cursor to the result set sequence returned from an SQL-invoked procedure.


Format
<allocate received cursor statement> ::=
  ALLOCATE <cursor name>
      [ CURSOR ] FOR PROCEDURE <specific routine designator>

20.19 <dynamic open statement>

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


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

20.20 <dynamic fetch statement>

Function
Fetch a row for a dynamic cursor.


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

20.21 <dynamic single row select statement>

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


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

20.22 <dynamic close statement>

Function
Close a dynamic cursor.


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

20.23 <dynamic delete statement: positioned>

Function
Delete a row of a table.


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


20.24 <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 <conventional dynamic cursor name>

20.25 <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 <preparable dynamic cursor name>

20.26 <preparable dynamic cursor name>

Function
Specify the cursor of a <preparable dynamic delete statement: positioned> or a <preparable dynamic update
statement: positioned>.


Format
<preparable dynamic cursor name> ::=
  [ <scope option> ] <cursor name>

20.27 <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 <preparable dynamic cursor name>

20.28 <pipe row statement>

Function
Output a row from a polymorphic table function.


Format
<pipe row statement> ::=
  PIPE ROW <PTF descriptor name>

21 Embedded SQL


21.1 <embedded SQL host program>

Function
Specify an <embedded SQL host program>.


Format
<embedded SQL host program> ::=
    <embedded SQL Ada program>
  | <embedded SQL C program>
  | <embedded SQL COBOL program>
  | <embedded SQL Fortran program>
  | <embedded SQL MUMPS program>
  | <embedded SQL Pascal program>
  | <embedded SQL PL/I program>

<embedded SQL statement> ::=
  <SQL prefix> <statement or declaration> [ <SQL terminator> ]

<statement or declaration> ::=
    <declare cursor>
  | <dynamic declare cursor>
  | <temporary table declaration>
  | <embedded authorization declaration>
  | <embedded path specification>
  | <embedded transform group specification>
  | <embedded collation specification>
  | <embedded exception declaration>
  | <SQL procedure statement>

<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 SQL declare section> ::=
    <embedded SQL begin declare>
        [ <embedded character set declaration> ]
        [ <host variable definition>... ]
        <embedded SQL end declare>
  | <embedded SQL MUMPS declare>

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

<host variable definition> ::=
    <Ada variable definition>
  | <C variable definition>
  | <COBOL variable definition>
  | <Fortran variable definition>
  | <MUMPS variable definition>
  | <Pascal variable definition>
  | <PL/I variable definition>

<embedded variable name> ::=
  <colon> <host identifier>

<host identifier> ::=
    <Ada host identifier>
  | <C host identifier>
  | <COBOL host identifier>
  | <Fortran host identifier>
  | <MUMPS host identifier>
  | <Pascal host identifier>
  | <PL/I host identifier>

21.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 code> [ , <SQLSTATE subclass code> ] )
  | CONSTRAINT <constraint name>

<major category> ::=
    SQLEXCEPTION
  | SQLWARNING
  | NOT FOUND

<SQLSTATE class code> ::=
  <SQLSTATE char> <SQLSTATE char>!! See the Syntax Rules.

<SQLSTATE subclass code> ::=
  <SQLSTATE char> <SQLSTATE char> <SQLSTATE char>!! See the Syntax Rules.

<SQLSTATE char> ::=
    <simple Latin upper case letter>
  | <digit>

<condition action> ::=
    CONTINUE
  | <go to>

<go to> ::=
  { GOTO | GO TO } <goto target>

<goto target> ::=
    <host label identifier>
  | <unsigned integer>
  | <host PL/I label variable>

<host label identifier> ::=
  !! See the Syntax Rules.

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

21.3 <embedded SQL Ada program>

Function
Specify an <embedded SQL Ada program>.


Format
<embedded SQL Ada program> ::=
  !! See the Syntax Rules.

<Ada variable definition> ::=
  <Ada host identifier> [ { <comma> <Ada host identifier> }... ] <colon>
      <Ada type specification> [ <Ada initial value> ]

<Ada initial value> ::=
  <Ada assignment operator> <character representation>...

<Ada assignment operator> ::=
  <colon> <equals operator>

<Ada host identifier> ::=
  !! See the Syntax Rules.

<Ada type specification> ::=
    <Ada qualified type specification>
  | <Ada unqualified type specification>
  | <Ada derived type specification>

<Ada qualified type specification> ::=
    Interfaces.SQL.CHAR
        [ CHARACTER SET [ IS ] <character set specification> ]
        <left paren> 1 <double period> <character length> <right paren>
  | Interfaces.SQL.SMALLINT
  | Interfaces.SQL.INT
  | Interfaces.SQL.BIGINT
  | Interfaces.SQL.REAL
  | Interfaces.SQL.DOUBLE_PRECISION
  | Interfaces.SQL.BOOLEAN
  | Interfaces.SQL.SQLSTATE_TYPE
  | Interfaces.SQL.INDICATOR_TYPE

<Ada unqualified type specification> ::=
    CHAR <left paren> 1 <double period> <character length> <right paren>
  | SMALLINT
  | INT
  | BIGINT
  | REAL
  | DOUBLE_PRECISION
  | BOOLEAN
  | SQLSTATE_TYPE
  | INDICATOR_TYPE

<Ada derived type specification> ::=
    <Ada CLOB variable>
  | <Ada CLOB locator variable>
  | <Ada BINARY variable>
  | <Ada VARBINARY variable>
  | <Ada BLOB variable>
  | <Ada BLOB locator variable>
  | <Ada user-defined type variable>
  | <Ada user-defined type locator variable>
  | <Ada REF variable>
  | <Ada array locator variable>
  | <Ada multiset locator variable>

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

<Ada CLOB locator variable> ::=
  SQL TYPE IS CLOB AS LOCATOR

<Ada BINARY variable> ::=
  SQL TYPE IS BINARY <left paren> <length> <right paren>

<Ada VARBINARY variable> ::=
  SQL TYPE IS VARBINARY <left paren> <length> <right paren>

<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

21.4 <embedded SQL C program>

Function
Specify an <embedded SQL C program>.


Format
<embedded SQL C program> ::=
  !! See the Syntax Rules.

<C variable definition> ::=
  [ <C storage class> ] [ <C class modifier> ]
      <C variable specification> <semicolon>

<C variable specification> ::=
    <C numeric variable>
  | <C character variable>
  | <C derived variable>

<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 variable> ::=
  <C character type> [ CHARACTER SET [ IS ] <character set specification> ]
      <C host identifier> <C array specification> [ <C initial value> ]
      [ { <comma> <C host identifier> <C array specification>
      [ <C initial value> ] }... ]

<C character type> ::=
    char
  | unsigned char
  | unsigned short

<C array specification> ::=
  <left bracket> <character length> <right bracket>

<C host identifier> ::=
  !! See the Syntax Rules.

<C derived variable> ::=
    <C VARCHAR variable>
  | <C NCHAR variable>
  | <C NCHAR VARYING variable>
  | <C CLOB variable>
  | <C NCLOB variable>
  | <C BINARY variable>
  | <C VARBINARY variable>
  | <C BLOB variable>
  | <C user-defined type variable>
  | <C CLOB locator variable>
  | <C BLOB locator variable>
  | <C array locator variable>
  | <C multiset locator variable>
  | <C user-defined type locator variable>
  | <C REF variable>

<C VARCHAR variable> ::=
  VARCHAR [ CHARACTER SET [ IS ] <character set specification> ]
      <C host identifier> <C array specification> [ <C initial value> ]
      [ { <comma> <C host identifier> <C array specification> [
      <C initial value> ] }... ]

<C NCHAR variable> ::=
  NCHAR <C host identifier> <C array specification> [ <C initial value> ]
      [ { <comma> <C host identifier> <C array specification>
      [ <C initial value> ] } ... ]

<C NCHAR VARYING variable> ::=
  NCHAR VARYING <C host identifier> <C array specification> [ <C initial value> ]
      [ { <comma> <C host identifier> <C array specification> [
      <C initial value> ] } ... ]

<C CLOB variable> ::=
  SQL TYPE IS CLOB <left paren> <character large object length> <right paren>
      [ CHARACTER SET [ IS ] <character set specification> ]
      <C host identifier> [ <C initial value> ] [ { <comma> <C host identifier> [
      <C initial value> ] }... ]

<C NCLOB variable> ::=
  SQL TYPE IS NCLOB <left paren> <character large object length> <right paren>
      <C host identifier> [ <C initial value> ] [ { <comma> <C host identifier>
      [ <C initial value> ] }... ]

<C user-defined type variable> ::=
  SQL TYPE IS <path-resolved user-defined type name> AS <predefined type>
      <C host identifier> [ <C initial value> ]
      [ { <comma> <C host identifier> [
      <C initial value> ] } ... ]

<C BINARY variable> ::=
  SQL TYPE IS BINARY <left paren> <length> <right paren>
      <C host identifier> [ <C initial value> ]
      [ { <comma> <C host identifier> [ <C initial value> ] }... ]

<C VARBINARY variable> ::=
  SQL TYPE IS VARBINARY <left paren> <length> <right paren>
      <C host identifier> [ <C initial value> ]
      [ { <comma> <C host identifier> [ <C initial value> ] }... ]

<C BLOB variable> ::=
  SQL TYPE IS BLOB <left paren> <large object length> <right paren>
      <C host identifier> [ <C initial value> ]
      [ { <comma> <C host identifier> [
      <C initial value> ] } ... ]

<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> ] }... ]

<C REF variable> ::=
  SQL TYPE IS <reference type> <C host identifier> [ <C initial value> ]
      [ { <comma> <C host identifier> [ <C initial value> ] }... ]

<C initial value> ::=
  <equals operator> <character representation>...

21.5 <embedded SQL COBOL program>

Function
Specify an <embedded SQL COBOL program>.


Format
<embedded SQL COBOL program> ::=
  !! See the Syntax Rules.

<COBOL variable definition> ::=
  { 01 |77 } <COBOL host identifier>
      <COBOL type specification> [ <character representation>... ] <period>

<COBOL host identifier> ::=
  !! See the Syntax Rules.

<COBOL type specification> ::=
    <COBOL character type>
  | <COBOL national character type>
  | <COBOL numeric type>
  | <COBOL integer type>
  | <COBOL derived type specification>

<COBOL derived type specification> ::=
    <COBOL CLOB variable>
  | <COBOL NCLOB variable>
  | <COBOL BINARY variable>
  | <COBOL BLOB variable>
  | <COBOL user-defined type variable>
  | <COBOL CLOB locator variable>
  | <COBOL BLOB locator variable>
  | <COBOL array locator variable>
  | <COBOL multiset locator variable>
  | <COBOL user-defined type locator variable>
  | <COBOL REF variable>

<COBOL character type> ::=
  [ CHARACTER SET [ IS ] <character set specification> ]
      { PIC | PICTURE } [ IS ] { X [ <left paren> <character length> <right paren> ] }...

<COBOL national character type> ::=
  { PIC | PICTURE } [ IS ] { N [ <left paren> <character length> <right paren> ] }...

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

<COBOL NCLOB variable> ::=
  [ USAGE [ IS ] ] SQL TYPE IS NCLOB <left paren> <character large object length> <right
  paren>

<COBOL BINARY variable> ::=
  [ USAGE [ IS ] ] SQL TYPE IS BINARY <left paren> <length> <right paren>

<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> ::=
  { PIC | PICTURE } [ IS ] S <COBOL nines>
      [ USAGE [ IS ] ] BINARY

<COBOL nines> ::=
  { 9 [ <left paren> <length> <right paren> ] }...

21.6 <embedded SQL Fortran program>

Function
Specify an <embedded SQL Fortran program>.


Format
<embedded SQL Fortran program> ::=
  !! See the Syntax Rules.

<Fortran variable definition> ::=
  <Fortran type specification> <Fortran host identifier>
      [ { <comma> <Fortran host identifier> }... ]

<Fortran host identifier> ::=
  !! See the Syntax Rules.

<Fortran type specification> ::=
    CHARACTER [ <asterisk> <character length> ] [ CHARACTER SET
         [ IS ] <character set specification> ]
  | CHARACTER KIND = n [ <asterisk> <character length> ]
      [ CHARACTER SET [ IS ] <character set specification> ]
  | INTEGER
  | REAL
  | DOUBLE PRECISION
  | LOGICAL
  | <Fortran derived type specification>

<Fortran derived type specification> ::=
    <Fortran CLOB variable>
  | <Fortran BINARY variable>
  | <Fortran VARBINARY variable>
  | <Fortran BLOB variable>
  | <Fortran user-defined type variable>
  | <Fortran CLOB locator variable>
  | <Fortran BLOB locator variable>
  | <Fortran user-defined type locator variable>
  | <Fortran array locator variable>
  | <Fortran multiset locator variable>
  | <Fortran REF variable>

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

<Fortran BINARY variable> ::=
  SQL TYPE IS BINARY <left paren> <length> <right paren>

<Fortran VARBINARY variable> ::=
  SQL TYPE IS VARBINARY <left paren> <length> <right paren>

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

21.7 <embedded SQL MUMPS program>

Function
Specify an <embedded SQL MUMPS program>.


Format
<embedded SQL MUMPS program> ::=
  !! See the Syntax Rules.

<MUMPS variable definition> ::=
    <MUMPS numeric variable> <semicolon>
  | <MUMPS character variable> <semicolon>
  | <MUMPS derived type specification> <MUMPS host identifier> <semicolon>

<MUMPS character variable> ::=
  VARCHAR <MUMPS character variable specifier>
      [ { <comma> <MUMPS character variable specifier> }... ]

<MUMPS character variable specifier> ::=
  <MUMPS host identifier> <MUMPS length specification>
      [ CHARACTER SET [ IS ] <character set specification> ]

<MUMPS host identifier> ::=
  !! See the Syntax Rules.

<MUMPS length specification> ::=
  <left paren> <character length> <right paren>

<MUMPS numeric variable> ::=
  <MUMPS type specification> <MUMPS host identifier>
      [ { <comma> <MUMPS host identifier> }... ]

<MUMPS type specification> ::=
    INT
  | DEC [ <left paren> <precision> [ <comma> <scale> ] <right paren> ]
  | REAL

<MUMPS derived type specification> ::=
    <MUMPS user-defined type variable>
  | <MUMPS CLOB locator variable>
  | <MUMPS BLOB locator variable>
  | <MUMPS user-defined type locator variable>
  | <MUMPS array locator variable>
  | <MUMPS multiset locator variable>
  | <MUMPS REF variable>

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

21.8 <embedded SQL Pascal program>

Function
Specify an <embedded SQL Pascal program>.


Format
<embedded SQL Pascal program> ::=
  !! See the Syntax Rules.

<Pascal variable definition> ::=
  <Pascal host identifier> [ { <comma> <Pascal host identifier> }... ] <colon>
      <Pascal type specification> <semicolon>

<Pascal host identifier> ::=
  !! See the Syntax Rules.

<Pascal type specification> ::=
    PACKED ARRAY <left bracket> 1 <double period> <character 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 derived type specification> ::=
    <Pascal CLOB variable>
  | <Pascal BINARY variable>
  | <Pascal BLOB variable>
  | <Pascal user-defined type variable>
  | <Pascal CLOB locator variable>
  | <Pascal BLOB locator variable>
  | <Pascal user-defined type locator variable>
  | <Pascal array locator variable>
  | <Pascal multiset locator variable>
  | <Pascal REF variable>

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

<Pascal BINARY variable> ::=
  SQL TYPE IS BINARY <left paren> <length> <right paren>

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

21.9 <embedded SQL PL/I program>

Function
Specify an <embedded SQL PL/I program>.


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

<PL/I variable definition> ::=
    { DCL | DECLARE } <PL/I type specification> [ <character representation>... ] <semicolon>
  | { <PL/I host identifier> | <left paren> <PL/I host identifier>
      [ { <comma> <PL/I host identifier> }... ] <right paren> }
      <PL/I type specification> [ <character representation>... ] <semicolon>

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

<PL/I type specification> ::=
    { CHAR | CHARACTER } [ VARYING ] <left paren> <character length> <right paren>
        [ CHARACTER SET [ IS ] <character set specification> ]
  | <PL/I type fixed decimal> <left paren> <precision> [ <comma> <scale> ] <right paren>
  | <PL/I type fixed binary> [ <left paren> <precision> <right paren> ]
  | <PL/I type float binary> <left paren> <precision> <right paren>
  | <PL/I derived type specification>

<PL/I derived type specification> ::=
    <PL/I CLOB variable>
  | <PL/I BINARY variable>
  | <PL/I VARBINARY variable>
  | <PL/I BLOB variable>
  | <PL/I user-defined type variable>
  | <PL/I CLOB locator variable>
  | <PL/I BLOB locator variable>
  | <PL/I user-defined type locator variable>
  | <PL/I array locator variable>
  | <PL/I multiset locator variable>
  | <PL/I REF variable>

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

<PL/I BINARY variable> ::=
  SQL TYPE IS BINARY <left paren> <length> <right paren>

<PL/I VARBINARY variable> ::=
  SQL TYPE IS VARBINARY <left paren> <length> <right paren>

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

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

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

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

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

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

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

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

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

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

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


22 Direct invocation of SQL


22.1 <direct SQL statement>

Function
Specify direct execution of SQL.


Format
<direct SQL statement> ::=
  <directly executable statement> <semicolon>

<directly executable statement> ::=
    <direct SQL data statement>
  | <SQL schema statement>
  | <SQL transaction statement>
  | <SQL connection statement>
  | <SQL session statement>
  | <direct implementation-defined statement>

<direct SQL data statement> ::=
    <delete statement: searched>
  | <direct select statement: multiple rows>
  | <insert statement>
  | <update statement: searched>
  | <truncate table statement>
  | <merge statement>
  | <temporary table declaration>

<direct implementation-defined statement> ::=
  !! See the Syntax Rules.

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

23 Diagnostics management


23.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>
  | <all information>

<statement information> ::=
      <statement information item> [ { <comma> <statement information item> }... ]

<statement information item> ::=
  <simple target specification> <equals operator> <statement information item name>

<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> ::=
  CONDITION <condition number> <condition information item>
      [ { <comma> <condition information item> }... ]

<condition information item> ::=
  <simple target specification> <equals operator> <condition information item name>

<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

<all information> ::=
  <all info target> <equals operator> ALL [ <all qualifier> ]

<all info target> ::=
  <simple target specification>

<all qualifier> ::=
    STATEMENT
  | CONDITION [ <condition number> ]

<condition number> ::=
  <simple value specification>