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> 
<simple Latin upper case letter> ::=
    A | B | C | D | E | F | G | H | I | J | K | L | M | N | O
  | P | Q | R | S | T | U | V | W | X | Y | Z
<simple Latin lower case letter> ::=
    a | b | c | d | e | f | g | h | i | j | k | l | m | n | o
  | p | q | r | s | t | u | v | w | x | y | z
<digit> ::=
    0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9
<space> ::=
  !! See the Syntax Rules.
<double quote> ::=
  "
<percent> ::=
  %
<ampersand> ::=
  &
<quote> ::=
  '
<left paren> ::=
  (
<right paren> ::=
  )
<asterisk> ::=
  *
<plus sign> ::=
  +
<comma> ::=
  ,
<minus sign> ::=
  -
<period> ::=
  .
<solidus> ::=
  /
<reverse solidus> ::=
  \
<colon> ::=
  :
<semicolon> ::=
  ;
<less than operator> ::=
  <
<equals operator> ::=
  =
<greater than operator> ::=
  >
<question mark> ::=
  ?
<left bracket or trigraph> ::=
    <left bracket> 
  | <left bracket trigraph> 
<right bracket or trigraph> ::=
    <right bracket> 
  | <right bracket trigraph> 
<left bracket> ::=
  [
<left bracket trigraph> ::=
  ??(
<right bracket> ::=
  ]
<right bracket trigraph> ::=
  ??)
<circumflex> ::=
  ^
<underscore> ::=
  _
<vertical bar> ::=
  |
<left brace> ::=
  {
<right brace> ::=
  }

5.2 <token> and <separator>

Function

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

<regular identifier> ::=
  <identifier body> 
<identifier body> ::=
  <identifier start>  [ <identifier part> ... ]
<identifier 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 body> ::=
  <delimited identifier part> ...
<delimited identifier part> ::=
    <nondoublequote character> 
  | <doublequote symbol> 
<Unicode escape specifier> ::=
  [ UESCAPE <quote>  <Unicode escape character>  <quote>  ]
<Unicode delimiter body> ::=
  <Unicode identifier part> ...
<Unicode character escape value> ::=
  <Unicode escape character>  <Unicode escape character> 
<Unicode escape character> ::=
  !! See the Syntax Rules.
<nondoublequote character> ::=
  !! See the Syntax Rules.
<doublequote symbol> ::=
  ""!! two consecutive double quote characters
<not equals operator> ::=
  <<, <> >>
<greater than or equals operator> ::=
  >=
<less than or equals operator> ::=
  <=
<concatenation operator> ::=
  ||
<right arrow> ::=
  ->
<double colon> ::=
  ::
<double period> ::=
  ..
<named argument assignment token> ::=
  =>
<separator> ::=
  { <comment>  | <white space>  }...
<white space> ::=
  !! See the Syntax Rules.
<simple comment introducer> ::=
  <minus sign>  <minus sign> 
<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.
<non-reserved word> ::=
    A | ABSOLUTE | ACTION | ADA | ADD | ADMIN | AFTER | ALWAYS | ASC
  | ASSERTION | ASSIGNMENT | ATTRIBUTE | ATTRIBUTES

  | BEFORE | BERNOULLI | BREADTH

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

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

  | ENFORCED | EXCLUDE | EXCLUDING | EXPRESSION

  | FINAL | FIRST | FLAG | FOLLOWING | FORTRAN | FOUND

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

  | HIERARCHY

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

  | K | KEY | KEY_MEMBER | KEY_TYPE

  | LAST | LENGTH | LEVEL | LOCATOR

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

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

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

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

  | READ | RELATIVE | REPEATABLE | RESPECT | RESTART | RESTRICT | RETURNED_CARDINALITY
  | RETURNED_LENGTH | RETURNED_OCTET_LENGTH | RETURNED_SQLSTATE | ROLE
  | ROUTINE | ROUTINE_CATALOG | ROUTINE_NAME | ROUTINE_SCHEMA | ROW_COUNT
  | SCALE | SCHEMA | SCHEMA_NAME | SCOPE_CATALOG | SCOPE_NAME | SCOPE_SCHEMA
  | SECTION | SECURITY | SELF | SEQUENCE | SERIALIZABLE | SERVER_NAME | SESSION
  | SETS | SIMPLE | SIZE | SOURCE | SPACE | SPECIFIC_NAME | STATE | STATEMENT
  | STRUCTURE | STYLE | SUBCLASS_ORIGIN

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

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

  | VIEW

  | WORK | WRITE

  | ZONE
<reserved word> ::=
    ABS | ALL | ALLOCATE | ALTER | AND | ANY | ARE | ARRAY | ARRAY_AGG
  | ARRAY_MAX_CARDINALITY | AS | ASENSITIVE | ASYMMETRIC | AT | 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 | CLOB | CLOSE
  | COALESCE | COLLATE | COLLECT | COLUMN | COMMIT | CONDITION | CONNECT
  | CONSTRAINT | CONTAINS | CONVERT | CORR | CORRESPONDING | 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_TRANSFORM_GROUP_FOR_TYPE | CURRENT_USER | CURSOR | CYCLE

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

  | EACH | ELEMENT | ELSE | END | END_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 | INNER | INOUT | INSENSITIVE | INSERT
  | INT | INTEGER | INTERSECT | INTERSECTION | INTERVAL | INTO | IS

  | JOIN

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

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

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

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

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

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

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

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

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

<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>  }... ]
<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> 
<approximate numeric literal> ::=
  <mantissa>  E <exponent> 
<mantissa> ::=
  <exact numeric literal> 
<exponent> ::=
  <signed integer> 
<signed integer> ::=
  [ <sign>  ] <unsigned integer> 
<unsigned integer> ::=
  <digit> ...
<date literal> ::=
  DATE <date string> 
<time literal> ::=
  TIME <time string> 
<timestamp literal> ::=
  TIMESTAMP <timestamp string> 
<interval literal> ::=
  INTERVAL [ <sign>  ] <interval string>  <interval qualifier> 
<unquoted date string> ::=
  <date value> 
<unquoted time string> ::=
  <time value>  [ <time zone interval>  ]
<unquoted interval string> ::=
  [ <sign>  ] { <year-month literal>  | <day-time literal>  }
<day-time literal> ::=
    <day-time interval> 
  | <time interval> 
<years value> ::=
  <datetime value> 
<months value> ::=
  <datetime value> 
<days value> ::=
  <datetime value> 
<hours value> ::=
  <datetime value> 
<minutes value> ::=
  <datetime value> 
<seconds integer value> ::=
  <unsigned integer> 
<seconds fraction> ::=
  <unsigned integer> 
<datetime value> ::=
  <unsigned integer> 
<boolean literal> ::=
    TRUE
  | FALSE
  | UNKNOWN

5.4 Names and identifiers

Function

Specify names.

Format
<identifier> ::=
  <actual identifier> 
<SQL language identifier start> ::=
  <simple Latin letter> 
<SQL language identifier part> ::=
    <simple Latin letter> 
  | <digit> 
  | <underscore> 
<authorization identifier> ::=
    <role name> 
  | <user identifier> 
<domain name> ::=
  <schema qualified 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 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> 
<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> 
<statement name> ::=
  <identifier> 
<extended statement name> ::=
  [ <scope option>  ] <simple value specification> 
<dynamic cursor name> ::=
    <cursor name> 
  | <extended cursor name> 
<extended cursor name> ::=
  [ <scope option>  ] <simple value specification> 
<non-extended descriptor name> ::=
  <identifier> 
<extended descriptor name> ::=
  [ <scope option>  ] <simple value specification> 
<scope option> ::=
    GLOBAL
  | LOCAL
<window name> ::=
  <identifier> 

6 Scalar expressions

6.1 <data type>

Function

Specify a data type.

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

6.2 <field definition>

Function

Define a field of a row type.

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

6.4 <value specification> and <target specification>

Function

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

Format
<value specification> ::=
    <literal> 
  | <general value specification> 
<unsigned value specification> ::=
    <unsigned literal> 
  | <general value specification> 
<general value specification> ::=
    <host parameter specification> 
  | <SQL parameter reference> 
  | <dynamic parameter specification> 
  | <embedded variable specification> 
  | <current collation specification> 
  | CURRENT_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
<host parameter specification> ::=
  <host parameter name>  [ <indicator parameter>  ]
<dynamic parameter specification> ::=
  <question mark> 
<embedded variable specification> ::=
  <embedded variable name>  [ <indicator variable>  ]
<indicator variable> ::=
  [ INDICATOR ] <embedded variable name> 
<indicator parameter> ::=
  [ INDICATOR ] <host parameter name> 
<target array reference> ::=
    <SQL parameter reference> 
  | <column reference> 
<current collation specification> ::=
  COLLATION FOR <left paren>  <string value expression>  <right paren> 

6.5 <contextually typed value specification>

Function

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

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

6.6 <identifier chain>

Function

Disambiguate a <period>-separated chain of identifiers.

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

6.7 <column reference>

Function

Reference a column.

6.8 <SQL parameter reference>

Function

Reference an SQL parameter.

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

6.9 <set function specification>

Function

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

Format
<set function specification> ::=
    <aggregate function> 
  | <grouping operation> 

6.10 <window function>

Function

Specify a window function.

<rank function type> ::=
    RANK
  | DENSE_RANK
  | PERCENT_RANK
  | CUME_DIST
<lead or lag> ::=
  LEAD | LAG
<lead or lag extent> ::=
  <value expression> 
<default expression> ::=
  <value expression> 
<null treatment> ::=
  RESPECT NULLS | IGNORE NULLS
<first or last value> ::=
  FIRST_VALUE | LAST_VALUE
<from first or last> ::=
    FROM FIRST
  | FROM LAST
<window name or specification> ::=
    <window name> 
  | <in-line window specification> 
<in-line window specification> ::=
  <window specification> 

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 row number function> ::=
  ROW_NUMBER <left paren>  <row marker>  <right paren> 
<row marker> ::=
    BEGIN_PARTITION
  | BEGIN_FRAME
  | CURRENT_ROW
  | FRAME_ROW
  | END_FRAME
  | END_PARTITION
<row marker expression> ::=
  <row marker>  [ <row marker delta>  ]
<value_of default value> ::=
  <value expression> 

6.13 <cast specification>

Function

Specify a data conversion.

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

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.

6.16 <subtype treatment>

Function

Modify the declared type of an expression.

<subtype operand> ::=
  <value expression> 

6.17 <method invocation>

Function

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

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

6.18 <static method invocation>

Function

Invoke a static method.

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

6.19 <new specification>

Function

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

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.

<dereference operator> ::=
  <right arrow> 

6.21 <dereference operation>

Function

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

6.22 <method reference>

Function

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

6.23 <reference resolution>

Function

Obtain the value referenced by a reference value.

Format

6.24 <array element reference>

Function

Return an element of an array.

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.27 <numeric value expression>

Function

Specify a numeric value.

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

6.28 <numeric value function>

Function

Specify a function yielding a value of type numeric.

<XQuery pattern> ::=
  <character value expression> 
<XQuery option flag> ::=
  <character value expression> 
<regex subject string> ::=
  <character value expression> 
<regex position start or after> ::=
    START
  | AFTER
<regex occurrence> ::=
  <numeric value expression> 
<regex capture group> ::=
  <numeric value expression> 
<character value expression 1> ::=
  <character value expression> 
<character value expression 2> ::=
  <character value 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> 
<time zone field> ::=
    TIMEZONE_HOUR
  | TIMEZONE_MINUTE
<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> 
<numeric value expression dividend> ::=
  <numeric value expression> 
<numeric value expression divisor> ::=
  <numeric value expression> 
<numeric value expression base> ::=
  <numeric value expression> 
<numeric value expression exponent> ::=
  <numeric value expression> 
<ceiling function> ::=
  { CEIL | CEILING } <left paren>  <numeric value expression>  <right paren> 
<width bucket operand> ::=
  <numeric value expression> 
<width bucket bound 1> ::=
  <numeric value expression> 
<width bucket bound 2> ::=
  <numeric value expression> 
<width bucket count> ::=
  <numeric value expression> 

6.29 <string value expression>

Function

Specify a character string value or a binary string value.

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

6.30 <string value function>

Function

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

Format
<regular expression substring function> ::=
  SUBSTRING <left paren>  <character value expression>  SIMILAR <character value expression> 
      ESCAPE <escape character>  <right paren> 
<XQuery replacement string> ::=
  <character value expression> 
<regex transliteration occurrence> ::=
    <regex occurrence> 
  | ALL
<trim operands> ::=
  [ [ <trim specification>  ] [ <trim character>  ] FROM ] <trim source> 
<trim specification> ::=
    LEADING
  | TRAILING
  | BOTH
<trim character> ::=
  <character value expression> 
<normal form> ::=
    NFC
  | NFD
  | NFKC
  | NFKD
<normalize function result length> ::=
    <character length> 
  | <character large object length> 
<binary trim operands> ::=
  [ [ <trim specification>  ] [ <trim octet>  ] FROM ] <binary trim source> 
<binary trim source> ::=
  <binary value expression> 
<trim octet> ::=
  <binary value expression> 
<start position> ::=
  <numeric value expression> 
<string length> ::=
  <numeric value expression> 

6.31 <datetime value expression>

Function

Specify a datetime value.

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

6.32 <datetime value function>

Function

Specify a function yielding a value of type datetime.

<current date value function> ::=
  CURRENT_DATE
<current time value function> ::=
  CURRENT_TIME [ <left paren>  <time precision>  <right paren>  ]
<current local time value function> ::=
  LOCALTIME [ <left paren>  <time precision>  <right paren>  ]
<current timestamp value function> ::=
  CURRENT_TIMESTAMP [ <left paren>  <timestamp precision>  <right paren>  ]
<current local timestamp value function> ::=
  LOCALTIMESTAMP [ <left paren>  <timestamp precision>  <right paren>  ]

6.34 <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.35 <boolean value expression>

Function

Specify a boolean value.

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

6.36 <array value expression>

Function

Specify an array value.

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

6.37 <array value function>

Function

Specify a function yielding a value of an array type.

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

6.38 <array value constructor>

Function

Specify construction of an array.

<array value constructor by enumeration> ::=
  ARRAY <left bracket or trigraph>  <array element list>  <right bracket or trigraph> 
<array element list> ::=
  <array element>  [ { <comma>  <array element>  }... ]
<array element> ::=
  <value expression> 
<array value constructor by query> ::=
  ARRAY <table subquery> 

6.39 <multiset value expression>

Function

Specify a multiset value.

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

6.40 <multiset value function>

Function

Specify a function yielding a value of a multiset type.

Format
<multiset value function> ::=
  <multiset set function> 

6.41 <multiset value constructor>

Function

Specify construction of a multiset.

<multiset value constructor by enumeration> ::=
  MULTISET <left bracket or trigraph>  <multiset element list>  <right bracket or trigraph> 
<multiset element list> ::=
  <multiset element>  [ { <comma>  <multiset element>  }... ]
<multiset element> ::=
  <value expression> 
<multiset value constructor by query> ::=
  MULTISET <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.

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

7.2 <row value expression>

Function

Specify a row value.

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

7.3 <table value constructor>

Function

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

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

7.4 <table expression>

Function

Specify a table or a grouped table.

7.5 <from clause>

Function

Specify a table derived from one or more tables.

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

7.6 <table reference>

Function

Reference a table.

Format
<table reference> ::=
    <table factor> 
  | <joined table> 
<table factor> ::=
  <table primary>  [ <sample clause>  ]
<sample method> ::=
    BERNOULLI
  | SYSTEM
<repeatable clause> ::=
  REPEATABLE <left paren>  <repeat argument>  <right paren> 
<sample percentage> ::=
  <numeric value expression> 
<repeat argument> ::=
  <numeric value expression> 
<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> 
<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> 
<derived column list> ::=
  <column name list> 
<column name list> ::=
  <column name>  [ { <comma>  <column name>  }... ]
<result option> ::=
    FINAL
  | NEW
  | OLD

7.7 <joined table>

Function

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

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

7.8 <where clause>

Function

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

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

7.9 <group by clause>

Function

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

Format
<group by clause> ::=
  GROUP BY [ <set quantifier>  ] <grouping element list> 
<grouping element list> ::=
  <grouping element>  [ { <comma>  <grouping element>  }... ]
<grouping column reference> ::=
  <column reference>  [ <collate clause>  ]
<grouping column reference list> ::=
  <grouping column reference>  [ { <comma>  <grouping column reference>  }... ]
<ordinary grouping set list> ::=
  <ordinary grouping set>  [ { <comma>  <ordinary grouping set>  }... ]
<grouping sets specification> ::=
  GROUPING SETS <left paren>  <grouping set list>  <right paren> 
<grouping set list> ::=
  <grouping set>  [ { <comma>  <grouping set>  }... ]
<empty grouping set> ::=
  <left paren>  <right paren> 

7.10 <having clause>

Function

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

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

7.11 <window clause>

Function

Specify one or more window definitions.

Format
<window clause> ::=
  WINDOW <window definition list> 
<window definition list> ::=
  <window definition>  [ { <comma>  <window definition>  }... ]
<window definition> ::=
  <new window name>  AS <window specification> 
<new window name> ::=
  <window name> 
<existing window name> ::=
  <window name> 
<window partition clause> ::=
  PARTITION BY <window partition column reference list> 
<window partition column reference list> ::=
  <window partition column reference> 
      [ { <comma>  <window partition column reference>  }... ]
<window partition column reference> ::=
  <column reference>  [ <collate clause>  ]
<window order clause> ::=
  ORDER BY <sort specification list> 
<window frame units> ::=
    ROWS
  | RANGE
  | GROUPS
<window frame start> ::=
    UNBOUNDED PRECEDING
  | <window frame preceding> 
  | CURRENT ROW
<window frame preceding> ::=
  <unsigned value specification>  PRECEDING
<window frame between> ::=
  BETWEEN <window frame bound 1>  AND <window frame bound 2> 
<window frame bound 1> ::=
  <window frame bound> 
<window frame bound 2> ::=
  <window frame bound> 
<window frame bound> ::=
    <window frame start> 
  | UNBOUNDED FOLLOWING
  | <window frame following> 
<window frame following> ::=
  <unsigned value specification>  FOLLOWING
<window frame exclusion> ::=
    EXCLUDE CURRENT ROW
  | EXCLUDE GROUP
  | EXCLUDE TIES
  | EXCLUDE NO OTHERS

7.12 <query specification>

Function

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

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

7.13 <query expression>

Function

Specify a table.

<with clause> ::=
  WITH [ RECURSIVE ] <with list> 
<with column list> ::=
  <column name list> 
<query expression body> ::=
    <query term> 
  | <query expression body>  UNION [ ALL | DISTINCT ]
      [ <corresponding spec>  ] <query term> 
  | <query expression body>  EXCEPT [ ALL | DISTINCT ]
      [ <corresponding spec>  ] <query term> 
<query term> ::=
    <query primary> 
  | <query term>  INTERSECT [ ALL | DISTINCT ]
      [ <corresponding spec>  ] <query primary> 
<explicit table> ::=
  TABLE <table or query name> 
<corresponding spec> ::=
  CORRESPONDING [ BY <left paren>  <corresponding column list>  <right paren>  ]
<corresponding column list> ::=
  <column name list> 
<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 }
<offset row count> ::=
  <simple value specification> 
<fetch first row count> ::=
  <simple value specification> 
<fetch first percentage> ::=
  <simple value specification>  PERCENT

7.14 <search or cycle clause>

Function

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

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

7.15 <subquery>

Function

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

Format
<scalar subquery> ::=
  <subquery> 
<row subquery> ::=
  <subquery> 
<table subquery> ::=
  <subquery> 

8 Predicates

8.2 <comparison predicate>

Function

Specify a comparison of two row values.

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

8.3 <between predicate>

Function

Specify a range comparison.

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

8.4 <in predicate>

Function

Specify a quantified comparison.

<in predicate part 2> ::=
  [ NOT ] IN <in predicate value> 

8.5 <like predicate>

Function

Specify a pattern-match comparison.

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

8.6 <similar predicate>

Function

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

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

8.7 <regex like predicate>

Function

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

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

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

8.9 <quantified comparison predicate>

Function

Specify a quantified comparison.

Format
<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 part 2> ::=
  IS [ NOT ] [ <normal form>  ] NORMALIZED

8.13 <match predicate>

Function

Specify a test for matching rows.

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

<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

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

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

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

8.19 <type predicate>

Function

Specify a type test.

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

8.20 <period predicate>

Function

Specify a test to determine the relationship between periods.

<period overlaps predicate part 2> ::=
  OVERLAPS <period predicand 2> 
<period predicand 1> ::=
  <period predicand> 
<period predicand 2> ::=
  <period predicand> 
<period reference> ::=
  <basic identifier chain> 
<period start value> ::=
  <datetime value expression> 
<period end value> ::=
  <datetime value expression> 
<period equals predicate part 2> ::=
  EQUALS <period predicand 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 part 2> ::=
  PRECEDES <period predicand 2> 
<period succeeds predicate part 2> ::=
  SUCCEEDS <period predicand 2> 
<period immediately precedes predicate part 2> ::=
  IMMEDIATELY PRECEDES <period predicand 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> 

10 Additional common elements

10.1 <interval qualifier>

Function

Specify the precision of an interval data type.

Format
<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.5 <character set specification>

Function

Identify a character set.

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

10.6 <specific routine designator>

Function

Specify an SQL-invoked routine.

<routine type> ::=
    ROUTINE
  | FUNCTION
  | PROCEDURE
  | [ INSTANCE | STATIC | CONSTRUCTOR ] METHOD
<member name alternatives> ::=
    <schema qualified routine name> 
  | <method name> 

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.

<set function type> ::=
  <computational operation> 
<computational operation> ::=
    AVG
  | MAX
  | MIN
  | SUM
  | EVERY
  | ANY
  | SOME
  | COUNT
  | STDDEV_POP
  | STDDEV_SAMP
  | VAR_SAMP
  | VAR_POP
  | COLLECT
  | FUSION
  | INTERSECTION
<set quantifier> ::=
    DISTINCT
  | ALL
<filter clause> ::=
  FILTER <left paren>  WHERE <search condition>  <right paren> 
<binary set function type> ::=
    COVAR_POP
  | COVAR_SAMP
  | CORR
  | REGR_SLOPE
  | REGR_INTERCEPT
  | REGR_COUNT
  | REGR_R2
  | REGR_AVGX
  | REGR_AVGY
  | REGR_SXX
  | REGR_SYY
  | REGR_SXY
<dependent variable expression> ::=
  <numeric value expression> 
<independent variable expression> ::=
  <numeric value expression> 
<within group specification> ::=
  WITHIN GROUP <left paren>  ORDER BY <sort specification list>  <right paren> 
<hypothetical set function value expression list> ::=
  <value expression>  [ { <comma>  <value expression>  }... ]
<inverse distribution function argument> ::=
  <numeric value expression> 
<inverse distribution function type> ::=
    PERCENTILE_CONT
  | PERCENTILE_DISC
<array aggregate function> ::=
  ARRAY_AGG
      <left paren>  <value expression>  [ ORDER BY <sort specification list>  ] <right paren> 

10.10 <sort specification list>

Function

Specify a sort order.

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

11 Schema definition and manipulation

11.2 <drop schema statement>

Function

Destroy a schema.

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

11.3 <table definition>

Function

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

Format
<table definition> ::=
  CREATE [ <table scope>  ] TABLE <table name>  <table contents source> 
      [ WITH <system versioning clause>  ]
      [ ON COMMIT <table commit action>  ROWS ]
<table scope> ::=
  <global or local>  TEMPORARY
<global or local> ::=
    GLOBAL
  | LOCAL
<system versioning clause> ::=
  SYSTEM VERSIONING
<table commit action> ::=
    PRESERVE
  | DELETE
<self-referencing column specification> ::=
  REF IS <self-referencing column name>  [ <reference generation>  ]
<reference generation> ::=
    SYSTEM GENERATED
  | USER GENERATED
  | DERIVED
<self-referencing column name> ::=
  <column name> 
<column options> ::=
  <column name>  WITH OPTIONS <column option list> 
<column option list> ::=
  [ <scope clause>  ] [ <default clause>  ] [ <column constraint definition> ... ]
<subtable clause> ::=
  UNDER <supertable clause> 
<supertable clause> ::=
  <supertable name> 
<supertable name> ::=
  <table name> 
<like clause> ::=
  LIKE <table name>  [ <like options>  ]
<like options> ::=
  <like option> ...
<identity option> ::=
    INCLUDING IDENTITY
  | EXCLUDING IDENTITY
<column default option> ::=
    INCLUDING DEFAULTS
  | EXCLUDING DEFAULTS
<generation option> ::=
    INCLUDING GENERATED
  | EXCLUDING GENERATED
<with or without data> ::=
    WITH NO DATA
  | WITH DATA
<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.

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

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.

11.7 <unique constraint definition>

Function

Specify a uniqueness constraint for a table.

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

<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 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.11 <add column definition>

Function

Add a column to a table.

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

11.12 <alter column definition>

Function

Change a column and its definition.

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

11.13 <set column default clause>

Function

Set the default clause for a column.

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

11.14 <drop column default clause>

Function

Drop the default clause from a column.

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

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

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

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

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 ]
<regular view specification> ::=
  [ <left paren>  <view column list>  <right paren>  ]
<referenceable view specification> ::=
  OF <path-resolved user-defined type name>  [ <subview clause>  ]
      [ <view element list>  ]
<subview clause> ::=
  UNDER <table name> 
<view column option> ::=
  <column name>  WITH OPTIONS <scope clause> 
<levels clause> ::=
    CASCADED
  | LOCAL
<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>  ]

11.35 <alter domain statement>

Function

Change a domain and its definition.

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

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

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

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

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.

<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 option list> ::=
  <user-defined type option>  [ <user-defined type option> ... ]
<subtype clause> ::=
  UNDER <supertype name> 
<instantiable clause> ::=
    INSTANTIABLE
  | NOT INSTANTIABLE
<finality> ::=
    FINAL
  | NOT FINAL
<user-defined representation> ::=
  REF USING <predefined type> 
<derived representation> ::=
  REF FROM <list of attributes> 
<system-generated representation> ::=
  REF IS SYSTEM GENERATED
<cast to ref> ::=
  CAST <left paren>  SOURCE AS REF <right paren>  WITH <cast to ref identifier> 
<cast to ref identifier> ::=
  <identifier> 
<cast to type> ::=
  CAST <left paren>  REF AS SOURCE <right paren>  WITH <cast to type identifier> 
<cast to type identifier> ::=
  <identifier> 
<cast to distinct> ::=
  CAST <left paren>  SOURCE AS DISTINCT <right paren> 
      WITH <cast to distinct identifier> 
<cast to distinct identifier> ::=
  <identifier> 
<cast to source> ::=
  CAST <left paren>  DISTINCT AS SOURCE <right paren> 
      WITH <cast to source identifier> 
<cast to source identifier> ::=
  <identifier> 
<method specification list> ::=
  <method specification>  [ { <comma>  <method specification>  }... ]
<original method specification> ::=
  <partial method specification>  [ SELF AS RESULT ] [ SELF AS LOCATOR ]
      [ <method characteristics>  ]
<overriding method specification> ::=
  OVERRIDING <partial method specification> 
<partial method specification> ::=
  [ INSTANCE | STATIC | CONSTRUCTOR ]
      METHOD <method name>  <SQL parameter declaration list> 
      <returns clause> 
      [ SPECIFIC <specific method name>  ]
<specific method name> ::=
  [ <schema name>  <period>  ] <qualified identifier> 
<method characteristics> ::=
  <method characteristic> ...

11.52 <attribute definition>

Function

Define an attribute of a structured type.

<attribute default> ::=
  <default clause> 

11.53 <alter type statement>

Function

Change the definition of a user-defined type.

Format

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 procedure> ::=
  CREATE <SQL-invoked procedure> 
<schema function> ::=
  CREATE <SQL-invoked function> 
<SQL parameter declaration> ::=
  [ <parameter mode>  ]
      [ <SQL parameter name>  ]
      <parameter type>  [ RESULT ]
      [ DEFAULT <parameter default>  ]
<parameter mode> ::=
    IN
  | OUT
  | INOUT
<parameter type> ::=
  <data type>  [ <locator indication>  ]
<locator indication> ::=
  AS LOCATOR
<method specification designator> ::=
    SPECIFIC METHOD <specific method name> 
  | [ INSTANCE | STATIC | CONSTRUCTOR ]
      METHOD <method name>  <SQL parameter declaration list> 
      [ <returns clause>  ]
      FOR <schema-resolved user-defined type name> 
<routine characteristics> ::=
  [ <routine characteristic> ... ]
<savepoint level indication> ::=
    NEW SAVEPOINT LEVEL
  | OLD SAVEPOINT LEVEL
<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 table type> ::=
  TABLE <table function column list> 
<table function column list element> ::=
  <column name>  <data type> 
<result cast> ::=
  CAST FROM <result cast from type> 
<result cast from type> ::=
  <data type>  [ <locator indication>  ]
<returns data type> ::=
  <data type>  [ <locator indication>  ]
<SQL routine spec> ::=
  [ <rights clause>  ] <SQL routine body> 
<rights clause> ::=
    SQL SECURITY INVOKER
  | SQL SECURITY DEFINER
<SQL routine body> ::=
  <SQL procedure statement> 
<external security clause> ::=
    EXTERNAL SECURITY DEFINER
  | EXTERNAL SECURITY INVOKER
  | EXTERNAL SECURITY IMPLEMENTATION DEFINED
<parameter style> ::=
    SQL
  | GENERAL
<deterministic characteristic> ::=
    DETERMINISTIC
  | NOT DETERMINISTIC
<SQL-data access indication> ::=
    NO SQL
  | CONTAINS SQL
  | READS SQL DATA
  | MODIFIES SQL DATA
<null-call clause> ::=
    RETURNS NULL ON NULL INPUT
  | CALLED ON NULL INPUT
<maximum 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.

<alter routine characteristics> ::=
  <alter routine characteristic> ...
<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

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> 
<equals ordering form> ::=
  EQUALS ONLY BY <ordering category> 
<full ordering form> ::=
  ORDER FULL BY <ordering category> 
<relative category> ::=
  RELATIVE WITH <relative function specification> 
<map category> ::=
  MAP WITH <map function specification> 
<state category> ::=
  STATE [ <specific name>  ]
<relative function specification> ::=
  <specific routine designator> 
<map function specification> ::=
  <specific routine designator> 

11.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> ...
<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 transform action list> ::=
  <alter transform action>  [ { <comma>  <alter transform action>  }... ]

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.

<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> ...
<common sequence generator options> ::=
  <common sequence generator option> ...
<sequence generator data type option> ::=
  AS <data type> 
<sequence generator start with option> ::=
  START WITH <sequence generator start value> 
<sequence generator start value> ::=
  <signed numeric literal> 
<sequence generator increment by option> ::=
  INCREMENT BY <sequence generator increment> 
<sequence generator increment> ::=
  <signed numeric literal> 
<sequence generator maxvalue option> ::=
    MAXVALUE <sequence generator max value> 
  | NO MAXVALUE
<sequence generator max value> ::=
  <signed numeric literal> 
<sequence generator minvalue option> ::=
    MINVALUE <sequence generator min value> 
  | NO MINVALUE
<sequence generator min value> ::=
  <signed numeric literal> 
<sequence generator cycle option> ::=
    CYCLE
  | NO CYCLE

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

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
<object privileges> ::=
    ALL PRIVILEGES
  | <action>  [ { <comma>  <action>  }... ]
<privilege column list> ::=
  <column name list> 
<grantee> ::=
    PUBLIC
  | <authorization identifier> 
<grantor> ::=
    CURRENT_USER
  | CURRENT_ROLE

12.4 <role definition>

Function

Define a role.

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

12.5 <grant role statement>

Function

Define role authorizations.

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

12.6 <drop role statement>

Function

Destroy a role.

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

12.7 <revoke statement>

Function

Destroy privileges and role authorizations.

<revoke privilege statement> ::=
  REVOKE [ <revoke option extension>  ] <privileges> 
      FROM <grantee>  [ { <comma>  <grantee>  }... ]
      [ GRANTED BY <grantor>  ]
      <drop behavior> 
<revoke option extension> ::=
    GRANT OPTION FOR
  | HIERARCHY OPTION FOR
<revoke role statement> ::=
  REVOKE [ ADMIN OPTION FOR ] <role revoked>  [ { <comma>  <role revoked>  }... ]
      FROM <grantee>  [ { <comma>  <grantee>  }... ]
      [ GRANTED BY <grantor>  ]
      <drop behavior> 
<role revoked> ::=
  <role name> 

13 SQL-client modules

13.1 <SQL-client module definition>

Function

Define an SQL-client module.

<module authorization clause> ::=
    SCHEMA <schema name> 
  | AUTHORIZATION <module authorization identifier> 
      [ FOR STATIC { ONLY | AND DYNAMIC } ]
  | SCHEMA <schema name>  AUTHORIZATION <module authorization identifier> 
      [ FOR STATIC { ONLY | AND DYNAMIC } ]
<module authorization identifier> ::=
  <authorization identifier> 
<module path specification> ::=
  <path specification> 
<module transform group specification> ::=
  <transform group specification> 
<module collations> ::=
  <module collation specification> ...
<module collation specification> ::=
  COLLATION <collation name>  [ FOR <character set specification list>  ]
<character set specification list> ::=
  <character set specification>  [ { <comma>  <character set specification>  }... ]

13.2 <module name clause>

Function

Name an SQL-client module.

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

13.3 <externally-invoked procedure>

Function

Define an externally-invoked procedure.

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

13.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 control statement> ::=
    <call statement> 
  | <return statement> 
<SQL diagnostics statement> ::=
  <get diagnostics statement> 

14 Data manipulation

14.1 <declare cursor>

Function

Declare a standing cursor.

Format

14.2 <cursor properties>

Function

Specify the declared properties of a cursor.

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

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

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

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

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 522 - 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 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 524 - 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 <descriptor name>  [ WITH MAX <occurrences>  ]

20.3 <deallocate descriptor statement>

Function

Deallocate an SQL descriptor area.

Format
<deallocate descriptor statement> ::=
  DEALLOCATE [ SQL ] DESCRIPTOR <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> 
<header item name> ::=
    COUNT
  | KEY_TYPE
  | DYNAMIC_FUNCTION
  | DYNAMIC_FUNCTION_CODE
  | TOP_LEVEL_COUNT
<simple target specification 1> ::=
  <simple target specification> 
<simple target specification 2> ::=
  <simple target specification> 
<descriptor item name> ::=
    CARDINALITY
  | CHARACTER_SET_CATALOG
  | CHARACTER_SET_NAME
  | CHARACTER_SET_SCHEMA
  | COLLATION_CATALOG
  | COLLATION_NAME
  | COLLATION_SCHEMA
  | DATA
  | DATETIME_INTERVAL_CODE
  | DATETIME_INTERVAL_PRECISION
  | DEGREE
  | INDICATOR
  | KEY_MEMBER
  | LENGTH
  | LEVEL
  | NAME
  | NULLABLE
  | OCTET_LENGTH
  | PARAMETER_MODE
  | PARAMETER_ORDINAL_POSITION
  | PARAMETER_SPECIFIC_CATALOG
  | PARAMETER_SPECIFIC_NAME
  | PARAMETER_SPECIFIC_SCHEMA
  | PRECISION
  | RETURNED_CARDINALITY
  | RETURNED_LENGTH
  | RETURNED_OCTET_LENGTH
  | SCALE
  | SCOPE_CATALOG
  | SCOPE_NAME
  | SCOPE_SCHEMA
  | TYPE
  | UNNAMED
  | USER_DEFINED_TYPE_CATALOG
  | USER_DEFINED_TYPE_NAME
  | USER_DEFINED_TYPE_SCHEMA
  | USER_DEFINED_TYPE_CODE

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> 
<simple value specification 1> ::=
  <simple value specification> 
<simple value specification 2> ::=
  <simple value specification> 

20.6 <prepare statement>

Function

Prepare a statement for execution.

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

20.7 <cursor attributes>

Function

Specify a list of cursor attributes.

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

20.8 <deallocate prepared statement>

Function

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

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

20.9 <describe statement>

Function

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

<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.10 <input using clause>

Function

Supply input values for an <SQL dynamic statement>.

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

20.11 <output using clause>

Function

Supply output variables for an <SQL dynamic statement>.

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

20.12 <execute statement>

Function

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

Format
<result using clause> ::=
  <output using clause> 
<parameter using clause> ::=
  <input using clause> 

20.13 <execute immediate statement>

Function

Dynamically prepare and execute a preparable statement.

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

20.14 <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.15 <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.16 <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.17 <dynamic open statement>

Function

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

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

20.18 <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.19 <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.20 <dynamic close statement>

Function

Close a dynamic cursor.

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

20.21 <dynamic delete statement: positioned>

Function

Delete a row of a table.

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

20.22 <dynamic update statement: positioned>

Function

Update a row of a table.

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

20.23 <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.24 <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.25 <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> 

21 Embedded SQL

21.1 <embedded SQL host program>

Function

Specify an <embedded SQL host program>.

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

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> 
<major category> ::=
    SQLEXCEPTION
  | SQLWARNING
  | NOT FOUND
<SQLSTATE class value> ::=
  <SQLSTATE char>  <SQLSTATE char> !! See the Syntax Rules.
<SQLSTATE subclass value> ::=
  <SQLSTATE char>  <SQLSTATE char>  <SQLSTATE char> !! See the Syntax Rules.
<condition action> ::=
    CONTINUE
  | <go to> 
<go to> ::=
  { GOTO | GO TO } <goto target> 
<host label identifier> ::=
  !! See the Syntax Rules.

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

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

21.3 <embedded SQL Ada program>

Function

Specify an <embedded SQL Ada program>.

Format
<embedded SQL Ada program> ::=
  !! See the Syntax Rules.
<Ada assignment operator> ::=
  <colon>  <equals operator> 
<Ada host identifier> ::=
  !! See the Syntax Rules.
<Ada qualified type specification> ::=
    Interfaces.SQL.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 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 storage class> ::=
    auto
  | extern
  | static
<C class modifier> ::=
    const
  | volatile
<C numeric variable> ::=
  { long long | long | short | float | double }
      <C host identifier>  [ <C initial value>  ]
      [ { <comma>  <C host identifier>  [ <C initial value>  ] }... ]
<C character type> ::=
    char
  | unsigned char
  | unsigned short
<C host identifier> ::=
  !! See the Syntax Rules.
<C CLOB locator variable> ::=
  SQL TYPE IS CLOB AS LOCATOR
      <C host identifier>  [ <C initial value>  ]
      [ { <comma>  <C host identifier>  [
      <C initial value>  ] } ... ]
<C BLOB locator variable> ::=
  SQL TYPE IS BLOB AS LOCATOR
      <C host identifier>  [ <C initial value>  ]
      [ { <comma>  <C host identifier>  [
      <C initial value>  ] } ... ]
<C array locator variable> ::=
  SQL TYPE IS <array type>  AS LOCATOR
      <C host identifier>  [ <C initial value>  ]
      [ { <comma>  <C host identifier>  [
      <C initial value>  ] } ... ]
<C multiset locator variable> ::=
  SQL TYPE IS <multiset type>  AS LOCATOR
      <C host identifier>  [ <C initial value>  ]
      [ { <comma>  <C host identifier>  [
      <C initial value>  ] } ... ]
<C user-defined type locator variable> ::=
  SQL TYPE IS <path-resolved user-defined type name>  AS LOCATOR
      <C host identifier>  [ <C initial value>  ]
      [ { <comma>  <C host identifier>  [
      <C initial value>  ] }... ]

21.5 <embedded SQL COBOL program>

Function

Specify an <embedded SQL COBOL program>.

Format
<embedded SQL COBOL program> ::=
  !! See the Syntax Rules.
<COBOL host identifier> ::=
  !! See the Syntax Rules.
<COBOL character type> ::=
  [ CHARACTER SET [ IS ] <character set specification>  ]
      { PIC | PICTURE } [ IS ] { X [ <left paren>  <character length>  <right paren>  ] }...
<COBOL national character type> ::=
  [ CHARACTER SET [ IS ] <character set specification>  ]
      { 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> 
      [ CHARACTER SET [ IS ] <character set specification>  ]
<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 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 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 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 type specification> ::=
    INT
  | DEC [ <left paren>  <precision>  [ <comma>  <scale>  ] <right paren>  ]
  | REAL
<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 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 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]]

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

[[PL/I-variable-definition]]

[[PL/I-host-identifier]]

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

[[PL/I-type-specification]]

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

[[PL/I-CLOB-variable]]

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

[[PL/I-BINARY-variable]]

<PL/I BINARY variable> ::=
  SQL TYPE IS BINARY <left paren>  <length>  <right paren> 

[[PL/I-VARBINARY-variable]]

<PL/I VARBINARY variable> ::=
  SQL TYPE IS VARBINARY <left paren>  <length>  <right paren> 

[[PL/I-BLOB-variable]]

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

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

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

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

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

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

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

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

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

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

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

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

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

[[PL/I-REF-variable]]

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

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

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

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

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

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

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

22 Direct invocation of SQL

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> 
<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 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 info target> ::=
  <simple target specification> 
<all qualifier> ::=
    STATEMENT
  | CONDITION [ <condition number>  ]
<condition number> ::=
  <simple value specification>