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> ::= /
<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. .Format

<regular identifier> ::= <identifier body>
<identifier body> ::=
       <identifier start>  [ { <underscore>  | <identifier part>  }... ]
<initial alphabetic character> ::= !! See the Syntax Rules
<ideographic character> ::= !! See the Syntax Rules
<alphabetic character> ::= !! See the Syntax Rules
<decimal digit character> ::= !! See the Syntax Rules
<identifier combining character> ::= !! See the Syntax Rules
<alternate underscore> ::= !! See the Syntax Rules
<extender character> ::= !! See the Syntax Rules
<identifier ignorable character> ::= !! See the Syntax Rules
<connector character> ::= !! See the Syntax Rules
<large object length token> ::=
       <digit> ...<multiplier> 
<multiplier> ::=
         K
       | M
       | G
<delimited identifier body> ::= <delimited identifier part>...
<delimited identifier part> ::=
         <nondoublequote character> 
       | <doublequote symbol> 
<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> ::= ::
<separator> ::= { <comment> | <white space> }...
<white space> ::= !! See the Syntax Rules
<simple comment introducer> ::= <minus sign><minus sign>[<minus sign>...]
<bracketed comment introducer> ::= /*
<bracketed comment terminator> ::= */
<bracketed comment contents> ::=
       [ { <comment character>  | <separator>  }... ]
<comment character> ::=
         <nonquote character> 
       | <quote> 
<newline> ::= !! See the Syntax Rules
<non-reserved word> ::=
         ABS | ADA | ASENSITIVE | ASSIGNMENT | ASYMMETRIC | ATOMIC | AVG

      | BETWEEN | BIT_LENGTH | BITVAR

      |   C | CALLED | CARDINALITY | CATALOG_NAME | CHAIN | CHAR_LENGTH
      |   CHARACTER_LENGTH | CHARACTER_SET_CATALOG | CHARACTER_SET_NAME
      |   CHARACTER_SET_SCHEMA | CHECKED | CLASS_ORIGIN | COALESCE | COBOL
      |   COLLATION_CATALOG | COLLATION_NAME | COLLATION_SCHEMA | COLUMN_NAME
      |   COMMAND_FUNCTION | COMMAND_FUNCTION_CODE | COMMITTED | CONDITION_NUMBER
      |   CONNECTION_NAME | CONSTRAINT_CATALOG | CONSTRAINT_NAME | CONSTRAINT_SCHEMA
      |   CONTAINS | CONVERT | COUNT | CURSOR_NAME

      | DATETIME_INTERVAL_CODE | DATETIME_INTERVAL_PRECISION | DEFINED | DEFINER
      | DISPATCH | DYNAMIC_FUNCTION | DYNAMIC_FUNCTION_CODE

      | EXISTING | EXISTS | EXTRACT

      | FINAL | FORTRAN

      | G | GENERATED | GRANTED

      | HIERARCHY | HOLD

      | IMPLEMENTATION | INFIX | INSENSITIVE | INSTANCE | INSTANTIABLE | INVOKER

      | K | KEY_MEMBER | KEY_TYPE

      | LENGTH | LOWER

      | M | MAX | MIN | MESSAGE_LENGTH | MESSAGE_OCTET_LENGTH | MESSAGE_TEXT
      | METHOD | MOD | MORE | MUMPS

      | NAME | NULLABLE | NUMBER | NULLIF

      | OCTET_LENGTH | OPTIONS | OVERLAPS | OVERLAY | OVERRIDING

      | PASCAL | PARAMETER_MODE | PARAMETER_NAME | PARAMETER_ORDINAL_POSITION
      | PARAMETER_SPECIFIC_CATALOG | PARAMETER_SPECIFIC_NAME
      | PARAMETER_SPECIFIC_SCHEMA | PLI | POSITION

      | REPEATABLE | RETURNED_LENGTH | RETURNED_OCTET_LENGTH | RETURNED_SQLSTATE
      | ROUTINE_CATALOG | ROUTINE_NAME | ROUTINE_SCHEMA | ROW_COUNT

      | SCALE | SCHEMA_NAME | SECURITY | SELF | SENSITIVE | SERIALIZABLE | SERVER_NAME
      | SIMPLE | SOURCE | SPECIFIC_NAME | SIMILAR | SUBLIST | SUBSTRING | SUM | STYLE
      | SUBCLASS_ORIGIN | SYMMETRIC | SYSTEM

      | TABLE_NAME | TRANSACTIONS_COMMITTED | TRANSACTIONS_ROLLED_BACK
      | TRANSACTION_ACTIVE | TRANSFORM | TRANSFORMS | TRANSLATE | TRIGGER_CATALOG
      | TRIGGER_SCHEMA | TRIGGER_NAME | TRIM | TYPE

      | UNCOMMITTED | UNNAMED | UPPER | USER_DEFINED_TYPE_CATALOG
      | USER_DEFINED_TYPE_NAME | USER_DEFINED_TYPE_SCHEMA
<reserved word> ::=
         ABSOLUTE | ACTION | ADD | ADMIN | AFTER | AGGREGATE
       | ALIAS | ALL | ALLOCATE | ALTER | AND | ANY | ARE | ARRAY | AS | ASC
       | ASSERTION | AT | AUTHORIZATION

      | BEFORE | BEGIN | BINARY | BIT | BLOB | BOOLEAN | BOTH | BREADTH | BY

      |   CALL | CASCADE | CASCADED | CASE | CAST | CATALOG | CHAR | CHARACTER
      |   CHECK | CLASS | CLOB | CLOSE | COLLATE | COLLATION | COLUMN | COMMIT
      |   COMPLETION | CONNECT | CONNECTION | CONSTRAINT | CONSTRAINTS
      |   CONSTRUCTOR | CONTINUE | CORRESPONDING | CREATE | CROSS | CUBE | CURRENT
      |   CURRENT_DATE | CURRENT_PATH | CURRENT_ROLE | CURRENT_TIME | CURRENT_TIMESTAMP
      |   CURRENT_USER | CURSOR | CYCLE

      |   DATA | DATE | DAY | DEALLOCATE | DEC | DECIMAL | DECLARE | DEFAULT
      |   DEFERRABLE | DEFERRED | DELETE | DEPTH | DEREF | DESC | DESCRIBE | DESCRIPTOR
      |   DESTROY | DESTRUCTOR | DETERMINISTIC | DICTIONARY | DIAGNOSTICS | DISCONNECT
      |   DISTINCT | DOMAIN | DOUBLE | DROP | DYNAMIC

      | EACH | ELSE | END | END-EXEC | EQUALS | ESCAPE | EVERY | EXCEPT
      | EXCEPTION | EXEC | EXECUTE | EXTERNAL

      | FALSE | FETCH | FIRST | FLOAT | FOR | FOREIGN | FOUND | FROM | FREE | FULL
      | FUNCTION

      | GENERAL | GET | GLOBAL | GO | GOTO | GRANT | GROUP | GROUPING

      | HAVING | HOST | HOUR

      | IDENTITY | IGNORE | IMMEDIATE | IN | INDICATOR | INITIALIZE | INITIALLY
      | INNER | INOUT | INPUT | INSERT | INT | INTEGER | INTERSECT | INTERVAL
      | INTO | IS | ISOLATION | ITERATE

      | JOIN

      | KEY

      | LANGUAGE | LARGE | LAST | LATERAL | LEADING | LEFT | LESS | LEVEL | LIKE
      | LIMIT
      | LOCAL | LOCALTIME | LOCALTIMESTAMP | LOCATOR

        | MAP | MATCH | MINUTE | MODIFIES | MODIFY | MODULE | MONTH

        | NAMES | NATIONAL | NATURAL | NCHAR | NCLOB | NEW | NEXT | NO | NONE
        | NOT | NULL | NUMERIC

        | OBJECT | OF | OFF | OLD | ON | ONLY | OPEN | OPERATION | OPTION
        | OR | ORDER | ORDINALITY | OUT | OUTER | OUTPUT

        | PAD | PARAMETER | PARAMETERS | PARTIAL | PATH | POSTFIX | PRECISION | PREFIX
        | PREORDER | PREPARE | PRESERVE | PRIMARY
        | PRIOR | PRIVILEGES | PROCEDURE | PUBLIC

        | READ | READS | REAL | RECURSIVE | REF | REFERENCES | REFERENCING | RELATIVE
        | RESTRICT | RESULT | RETURN | RETURNS | REVOKE | RIGHT
        | ROLE | ROLLBACK | ROLLUP | ROUTINE | ROW | ROWS

        |   SAVEPOINT | SCHEMA | SCROLL | SCOPE | SEARCH | SECOND | SECTION | SELECT
        |   SEQUENCE | SESSION | SESSION_USER | SET | SETS | SIZE | SMALLINT | SOME| SPACE
        |   SPECIFIC | SPECIFICTYPE | SQL | SQLEXCEPTION | SQLSTATE | SQLWARNING | START
        |   STATE | STATEMENT | STATIC | STRUCTURE | SYSTEM_USER

        | TABLE | TEMPORARY | TERMINATE | THAN | THEN | TIME | TIMESTAMP
        | TIMEZONE_HOUR | TIMEZONE_MINUTE | TO | TRAILING | TRANSACTION | TRANSLATION
        | TREAT | TRIGGER | TRUE

        | UNDER | UNION | UNIQUE | UNKNOWN
        | UNNEST | UPDATE | USAGE | USER | USING

        | VALUE | VALUES | VARCHAR | VARIABLE | VARYING | VIEW

        | WHEN | WHENEVER | WHERE | WITH | WITHOUT | WORK | WRITE

        | YEAR

        | ZONE

5.3 <literal>

Function

Specify a non-null value. .Format

<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>  }... ]
<bit string literal> ::=
       B <quote>  [ <bit> ... ] <quote> 
         [ { <separator>  <quote>  [ <bit> ... ] <quote>  }... ]
<hex string literal> ::=
       X <quote>  [ <hexit> ... ] <quote> 
         [ { <separator>  <quote>  [ <hexit> ... ] <quote>  }... ]
<binary string literal> ::=
       X <quote>  [ { <hexit>  <hexit>  }... ] <quote> 
         [ { <separator>  <quote>  [ { <hexit>  <hexit>  }... ] <quote>  }... ]
<bit> ::= 0 | 1
<hexit> ::= <digit> | A | B | C | D | E | F | a | b | c | d | e | f
<signed numeric literal> ::=
       [ <sign>  ] <unsigned numeric literal> 
<sign> ::= <plus sign> | <minus sign>
<approximate numeric literal> ::= <mantissa> E <exponent>
<mantissa> ::= <exact numeric literal>
<exponent> ::= <signed integer>
<signed integer> ::= [ <sign> ] <unsigned integer>
<unsigned integer> ::= <digit>...
<date literal> ::=
       DATE <date string> 
<time literal> ::=
       TIME <time string> 
<timestamp literal> ::=
       TIMESTAMP <timestamp string> 
<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> 
<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> 
       | MODULE
<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>
<character set name> ::= [ <schema name> <period> ] <SQL language identifier>
<translation name> ::= <schema qualified name>
<form-of-use conversion name> ::= <schema qualified name>
<user-defined type name> ::= <schema qualified type name>
<schema qualified type name> ::=
       [ <schema name>  <period>  ] <qualified identifier> 
<attribute name> ::=
         <identifier> 
<field name> ::= <identifier>
<savepoint name> ::= <identifier>
<role name> ::= <identifier>
<user identifier> ::= <identifier>
<connection name> ::= <simple value specification>
<SQL-server name> ::= <simple value specification>
<connection user name> ::= <simple value specification>

6 Scalar expressions

6.1 <data type>

Function

Specify a data type. .Format

<binary large object string type> ::=
         BINARY LARGE OBJECT [ <left paren>  <large object length>  <right paren>  ]
       | BLOB [ <left paren>  <large object length>  <right paren>  ]
<approximate numeric type> ::=
         FLOAT [ <left paren>  <precision>  <right paren>  ]
       | REAL
       | DOUBLE PRECISION
<length> ::= <unsigned integer>
<precision> ::= <unsigned integer>
<scale> ::= <unsigned integer>
<boolean type> ::= BOOLEAN
<with or without time zone> ::=
         WITH TIME ZONE
       | WITHOUT TIME ZONE
<time precision> ::= <time fractional seconds precision>
<timestamp precision> ::= <time fractional seconds precision>
<time fractional seconds precision> ::= <unsigned integer>
<interval type> ::= INTERVAL <interval qualifier>
<row type> ::=
       ROW <row type body> 
<row type body> ::=
       <left paren> 
         <field definition>  [ { <comma>  <field definition>  }... ]
       <right paren> 
<scope clause> ::=
       SCOPE <table name> 
<referenced type> ::= <user-defined type>
<user-defined type> ::= <user-defined type name>
<collection type> ::=
         <data type>  <array specification> 
<collection type constructor> ::=
         ARRAY

6.2 <field definition>

Function

Define a field of a row type. .Format

6.3 <value specification> and <target specification>

Function

Specify one or more values, host parameters, or SQL parameters. .Format

<value specification> ::=
         <literal> 
       | <general value specification> 
<unsigned value specification> ::=
         <unsigned literal> 
       | <general value specification> 
<general value specification> ::=
         <host parameter specification> 
       | <SQL parameter reference> 
       | CURRENT_PATH
       | CURRENT_ROLE
       | CURRENT_USER
       | SESSION_USER
       | SYSTEM_USER
       | USER
       | VALUE
<host parameter specification> ::=
       <host parameter name>  [ <indicator parameter>  ]
<indicator parameter> ::=
       [ INDICATOR ] <host parameter name>

6.4 <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.5 <identifier chain>

Function

Disambiguate a <period>-separated chain of identifiers. .Format

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

6.6 <column reference>

Function

Reference a column. .Format

6.7 <SQL parameter reference>

Function

Reference an SQL parameter. .Format

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

6.8 <field reference>

Function

Reference a field of a row value. .Format

6.9 <attribute or method reference>

Function

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

<dereference operator> ::= <right arrow>

6.10 <method reference>

Function

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

6.11 <method invocation>

Function

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

<method selection> ::= <routine invocation>

6.12 <static method invocation>

Function

Invoke a static method. .Format

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

6.13 <element reference>

Function

Return an element of an array. .Format

6.14 <dereference operation>

Function

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

6.15 <reference resolution>

Function

Obtain the value referenced by a reference value. .Format

6.16 <set function specification>

Function

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

<set function type> ::=
         <computational operation> 
<computational operation> ::=
         AVG | MAX | MIN | SUM
       | EVERY | ANY | SOME
       | COUNT
<grouping operation> ::=
       GROUPING <left paren>  <column reference>  <right paren> 
<set quantifier> ::=
         DISTINCT
       | ALL

6.17 <numeric value function>

Function

Specify a function yielding a value of type numeric. .Format

<char length expression> ::=
       { CHAR_LENGTH | CHARACTER_LENGTH }
           <left paren>  <string value expression>  <right paren> 
<octet length expression> ::=
       OCTET_LENGTH <left paren>  <string value expression>  <right paren> 
<bit length expression> ::=
       BIT_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> 
<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>

6.18 <string value function>

Function

Specify a function yielding a value of type character string or bit string. .Format

<fold> ::= { UPPER | LOWER } <left paren> <character value expression> <right paren>
<trim operands> ::=
       [ [ <trim specification>  ] [ <trim character>  ] FROM ] <trim source> 
<trim source> ::= <character value expression>
<trim specification> ::=
         LEADING
       | TRAILING
       | BOTH
<trim character> ::= <character value expression>
<specific type method> ::=
       <user-defined type value expression>  <period>  SPECIFICTYPE
<blob trim operands> ::=
       [ [ <trim specification>  ] [ <trim octet>  ] FROM ] <blob trim source> 
<blob trim source> ::= <blob value expression>
<trim octet> ::= <blob value expression>
<bit value function> ::=
       <bit substring function> 
<start position> ::= <numeric value expression>
<string length> ::= <numeric value expression>

6.19 <datetime value function>

Function

Specify a function yielding a value of type datetime. .Format

<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.20 <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.21 <case expression>

Function

Specify a conditional value. .Format

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

6.22 <cast specification>

Function

Specify a data conversion. .Format

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

6.23 <value expression>

Function

Specify a value. .Format

<user-defined type value expression> ::=
       <value expression primary> 
<reference value expression> ::=
       <value expression primary> 
<collection value expression> ::=
         <value expression primary> 
<parenthesized value expression> ::=
       <left paren>  <value expression>  <right paren> 
<collection value constructor> ::=
         <array value expression>

6.24 <new specification>

Function

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

<new specification> ::=
       NEW <routine invocation> 
<new invocation> ::=
       <method invocation>

6.25 <subtype treatment>

Function

Modify the declared type of an expression. .Format

<subtype operand> ::= <value expression>
<target data type> ::=
       <user-defined type>

6.26 <numeric value expression>

Function

Specify a numeric value. .Format

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

6.27 <string value expression>

Function

Specify a character string value or a bit string value. .Format

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

6.28 <datetime value expression>

Function

Specify a datetime value. .Format

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

Function

Specify an interval value. .Format

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

6.30 <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
<parenthesized boolean value expression> ::=
       <left paren>  <boolean value expression>  <right paren>

6.31 <array value expression>

Function

Specify an array value. .Format

<array value expression 1> ::= <array value expression>
<array value expression 2> ::= <array value expression>

6.32 <array value constructor>

Function

Specify construction of an array. .Format

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

7 Query expressions

7.1 <row value constructor>

Function

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

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

<contextually typed row value expression> ::=
         <row value special case> 
       | <contextually typed row value constructor> 
<row value special case> ::=
         <value specification> 
       | <value expression>

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> ::=
       <row value expression>  [ { <comma>  <row value expression>  }... ]
<contextually typed table value constructor> ::=
       VALUES <contextually typed row value expression list> 
<contextually typed row value expression list> ::=
       <contextually typed row value expression> 
           [ { <comma>  <contextually typed row value expression>  }... ]

7.4 <table expression>

Function

Specify a table or a grouped table. .Format

7.5 <from clause>

Function

Specify a table derived from one or more tables. .Format

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

7.6 <table reference>

Function

Reference a table. .Format

<table reference> ::=
         <table primary> 
       | <joined table> 
<lateral derived table> ::=
       LATERAL <left paren>  <query expression>  <right paren> 
<collection derived table> ::=
       UNNEST <left paren>  <collection value expression>  <right paren> 
           [ WITH ORDINALITY ]
<derived table> ::= <table subquery>
<table or query name> ::=
         <table name> 
       | <query name> 
<derived column list> ::= <column name list>
<column name list> ::=
       <column name>  [ { <comma>  <column name>  }... ]

7.7 <joined table>

Function

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

<cross join> ::=
       <table reference>  CROSS JOIN <table primary> 
<natural join> ::=
       <table reference>  NATURAL [ <join type>  ] JOIN <table primary> 
<union join> ::=
       <table reference>  UNION JOIN <table primary> 
<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 <grouping specification> 
<grouping sets list> ::=
       GROUPING SETS <left paren>  <grouping set list>  <right paren> 
<grouping set list> ::=
       <grouping set>  [ { <comma>  <grouping set>  }... ]
<concatenated grouping> ::=
       <grouping set>  <comma>  <grouping set list> 
<grand total> ::= <left paren> <right paren>
<grouping column reference list> ::=
       <grouping column reference>  [ { <comma>  <grouping column reference>  }... ]
<grouping column reference> ::=
       <column reference>  [ <collate clause>  ]

7.10 <having clause>

Function

Specify a grouped table derived by the elimination of groups that do not satisfy a <search condi- tion>. .Format

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

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

7.12 <query expression>

Function

Specify a table. .Format

<query expression> ::=
       [ <with clause>  ] <query expression body> 
<with clause> ::= WITH [ RECURSIVE ] <with list>
<with column list> ::= <column name list>
<query expression body> ::=
         <non-join query expression> 
       | <joined table> 
<non-join query expression> ::=
         <non-join query term> 
       | <query expression body>  UNION [ ALL | DISTINCT ]
           [ <corresponding spec>  ] <query term> 
       | <query expression body>  EXCEPT [ ALL | DISTINCT ]
           [ <corresponding spec>  ] <query term> 
<non-join query term> ::=
         <non-join query primary> 
       | <query term>  INTERSECT [ ALL | DISTINCT ]
           [ <corresponding spec>  ] <query primary> 
<explicit table> ::= TABLE <table name>
<corresponding spec> ::=
       CORRESPONDING [ BY <left paren>  <corresponding column list>  <right paren>  ]
<corresponding column list> ::= <column name list>

7.13 <search or cycle clause>

Function

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

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

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

Function

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

8.2 <comparison predicate>

Function

Specify a comparison of two row values. .Format

8.3 <between predicate>

Function

Specify a range comparison. .Format

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

8.4 <in predicate>

Function

Specify a quantified comparison. .Format

<in predicate> ::=
       <row value expression> 
         [ NOT ] IN <in predicate value>

8.5 <like predicate>

Function

Specify a pattern-match comparison. .Format

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

8.6 <similar predicate>

Function

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

<similar predicate> ::=
       <character match value>  [ NOT ] SIMILAR TO <similar pattern> 
         [ ESCAPE <escape character>  ]
<similar pattern> ::= <character value expression>
<non-escaped character> ::= !! See the Syntax Rules
<escaped character> ::= !! See the Syntax Rules
<regular character set identifier> ::= <identifier>

8.7 <null predicate>

Function

Specify a test for a null value. .Format

<null predicate> ::=
       <row value expression>  IS [ NOT ] NULL

8.8 <quantified comparison predicate>

Function

Specify a quantified comparison. .Format

<quantifier> ::= <all> | <some>
<all> ::= ALL
<some> ::= SOME | ANY

8.9 <exists predicate>

Function

Specify a test for a non-empty set. .Format

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

8.10 <unique predicate>

Function

Specify a test for the absence of duplicate rows. .Format

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

8.11 <match predicate>

Function

Specify a test for matching rows. .Format

<match predicate> ::=
       <row value expression>  MATCH [ UNIQUE ] [ SIMPLE | PARTIAL | FULL ]
           <table subquery>

8.12 <overlaps predicate>

Function

Specify a test for an overlap between two datetime periods. .Format

<overlaps predicate> ::=
       <row value expression 1>  OVERLAPS <row value expression 2> 
<row value expression 1> ::= <row value expression>
<row value expression 2> ::= <row value expression>

8.13 <distinct predicate>

Function

Specify a test of whether two row values are distinct .Format

<distinct predicate> ::=
       <row value expression 3>  IS DISTINCT FROM <row value expression 4> 
<row value expression 3> ::= <row value expression>
<row value expression 4> ::= <row value expression>

8.14 <type predicate>

Function

Specify a type test. .Format

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

8.15 <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 standard programming language. .Format

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

10.3 <path specification>

Function

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

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

10.4 <routine invocation>

Function

Invoke an SQL-invoked routine. .Format

<routine invocation> ::=
       <routine name>  <SQL argument list> 
<generalized expression> ::=
       <value expression>  AS <user-defined type>

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

10.6 <character set specification>

Function

Identify a character set. .Format

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

10.7 <specific routine designator>

Function

Specify an SQL-invoked routine. .Format

<specific routine designator> ::=
         SPECIFIC <routine type>  <specific name> 
       | <routine type>  <member name>  [ FOR user-defined type> ]
<routine type> ::=
         ROUTINE
       | FUNCTION
       | PROCEDURE
       | [ INSTANCE | STATIC ] METHOD
<member name> ::= <schema qualified routine name> [ <data type list> ]

10.8 <collate clause>

Function

Specify a default collating sequence. .Format

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

10.9 <constraint name definition> and <constraint characteristics>

Function

Specify the name of a constraint and its characteristics. .Format

<constraint name definition> ::=
       CONSTRAINT <constraint name> 
<constraint characteristics> ::=
         <constraint check time>  [ [ NOT ] DEFERRABLE ]
       | [ NOT ] DEFERRABLE [ <constraint check time>  ]
<constraint check time> ::=       INITIALLY DEFERRED | INITIALLY IMMEDIATE

11 Schema definition and manipulation

11.1 <schema definition>

Function

Define a schema. .Format

<schema definition> ::=
       CREATE SCHEMA <schema name clause> 
         [ <schema character set or path>  ]
         [ <schema element> ... ]
<schema authorization identifier> ::=
       <authorization identifier> 
<schema character set specification> ::=
       DEFAULT CHARACTER SET <character set specification> 
<schema path specification> ::=
       <path specification>

11.2 <drop schema statement>

Function

Destroy a schema. .Format

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

11.3 <table definition>

Function

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

<table definition> ::=
       CREATE [ <table scope>  ] TABLE <table name> 
         <table contents source> 
         [ ON COMMIT <table commit action>  ROWS ]
<table scope> ::=
       <global or local>  TEMPORARY
<global or local> ::=
         GLOBAL
       | LOCAL
<table commit action> ::=
         PRESERVE
       | DELETE
<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> 
<subtable clause> ::=
       UNDER <supertable clause> 
<supertable clause> ::= <supertable name>
<supertable name> ::= <table name>
<like clause> ::= LIKE <table name>

11.4 <column definition>

Function

Define a column of a base table. .Format

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

11.5 <default clause>

Function

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

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

11.6 <table constraint definition>

Function

Specify an integrity constraint. .Format

11.7 <unique constraint definition>

Function

Specify a uniqueness constraint for a table. .Format

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

11.8 <referential constraint definition>

Function

Specify a referential constraint. .Format

<referential constraint definition> ::=
       FOREIGN KEY <left paren>  <referencing columns>  <right paren> 
         <references specification> 
<references specification> ::=
       REFERENCES <referenced table and columns> 
         [ MATCH <match type>  ]
         [ <referential triggered action>  ]
<match type> ::=
         FULL
       | PARTIAL
       | SIMPLE
<referencing columns> ::=
       <reference column list> 
<reference column list> ::= <column name list>
<referential triggered action> ::=
         <update rule>  [ <delete rule>  ]
       | <delete rule>  [ <update rule>  ]
<update rule> ::= ON UPDATE <referential action>
<delete rule> ::= ON DELETE <referential action>
<referential action> ::=
         CASCADE
       | SET NULL
       | SET DEFAULT
       | RESTRICT
       | NO ACTION

11.9 <check constraint definition>

Function

Specify a condition for the SQL-data. .Format

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

11.10 <alter table statement>

Function

Change the definition of a table. .Format

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

11.11 <add column definition>

Function

Add a column to a table. .Format

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

11.12 <alter column definition>

Function

Change a column and its definition. .Format

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

11.13 <set column default clause>

Function

Set the default clause for a column. .Format

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

11.14 <drop column default clause>

Function

Drop the default clause from a column. .Format

<drop column default clause> ::=
       DROP DEFAULT

11.15 <add column scope clause>

Function

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

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

11.16 <drop column scope clause>

Function

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

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

11.17 <drop column definition>

Function

Destroy a column of a base table. .Format

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

11.18 <add table constraint definition>

Function

Add a constraint to a table. .Format

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

11.19 <drop table constraint definition>

Function

Destroy a constraint on a table. .Format

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

11.20 <drop table statement>

Function

Destroy a table. .Format

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

11.21 <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 <user-defined type> 
       [ <subview clause>  ]
       [ <view element list>  ]
<subview clause> ::= UNDER <table name>
<view element> ::= <view column option>
<view column option> ::= <column name> WITH OPTIONS <scope clause>
<levels clause> ::=
         CASCADED
       | LOCAL
<view column list> ::= <column name list>

11.22 <drop view statement>

Function

Destroy a view. .Format

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

11.23 <domain definition>

Function

Define a domain. .Format

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

11.24 <alter domain statement>

Function

Change a domain and its definition. .Format

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

11.25 <set domain default clause>

Function

Set the default value in a domain. .Format

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

11.26 <drop domain default clause>

Function

Remove the default clause of a domain. .Format

<drop domain default clause> ::= DROP DEFAULT

11.27 <add domain constraint definition>

Function

Add a constraint to a domain. .Format

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

11.28 <drop domain constraint definition>

Function

Destroy a constraint on a domain. .Format

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

11.29 <drop domain statement>

Function

Destroy a domain. .Format

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

11.30 <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.31 <drop character set statement>

Function

Destroy a character set. .Format

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

11.32 <collation definition>

Function

Define a collating sequence. .Format

<existing collation name> ::= <collation name>
<pad characteristic> ::=
         NO PAD
       | PAD SPACE

11.33 <drop collation statement>

Function

Destroy a collating sequence. .Format

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

11.34 <translation definition>

Function

Define a character translation. .Format

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

11.35 <drop translation statement>

Function

Destroy a character translation. .Format

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

11.37 <drop assertion statement>

Function

Destroy an assertion. .Format

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

11.38 <trigger definition>

Function

Define triggered SQL-statements. .Format

<trigger action time> ::=
         BEFORE
       | AFTER
<trigger event> ::=
         INSERT
       | DELETE
       | UPDATE [ OF <trigger column list>  ]
<trigger column list> ::= <column name list>
<triggered action> ::=
       [ FOR EACH { ROW | STATEMENT } ]
         [ WHEN <left paren>  <search condition>  <right paren>  ]
         <triggered SQL statement> 
<triggered SQL statement> ::=
         <SQL procedure statement> 
       | BEGIN ATOMIC
           { <SQL procedure statement>  <semicolon>  }...
         END
<old or new values alias list> ::=
       <old or new values alias> ...
<old or new values   alias> ::=
         OLD [ ROW   ] [ AS ] <old values correlation name> 
       | NEW [ ROW   ] [ AS ] <new values correlation name> 
       | OLD TABLE   [ AS ] <old values table alias> 
       | NEW TABLE   [ AS ] <new values table alias> 
<old values table alias> ::= <identifier>
<new values table alias> ::= <identifier>
<old values correlation name> ::= <correlation name>
<new values correlation name> ::= <correlation name>

11.39 <drop trigger statement>

Function

Destroy a trigger. .Format

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

11.40 <user-defined type definition>

Function

Define a user-defined type. .Format

<user-defined type definition> ::= CREATE TYPE <user-defined type body>
<user-defined type body> ::=
       <user-defined type name> 
       [ <subtype clause>  ]
       [ AS <representation>  ]
       [ <instantiable clause>  ]
       <finality> 
       [ <reference type specification>  ]
       [ <cast option>  ]
       [ <method specification list>  ]
<subtype clause> ::=
       UNDER <supertype name> 
<supertype name> ::=
       <user-defined type> 
<representation> ::=
         <predefined type> 
       | <member list> 
<instantiable clause> ::=
         INSTANTIABLE
       | NOT INSTANTIABLE
<finality> ::=
         FINAL
       | NOT FINAL
<user-defined representation> ::= REF USING <predefined type> [ <ref cast option> ]
<derived representation> ::= REF FROM <list of attributes>
<system-generated representation> ::= REF IS SYSTEM GENERATED
<ref cast option> ::=
       [ <cast to ref>  ]
       [ <cast to type>  ]
<cast to ref> ::=
       CAST <left paren>  SOURCE AS REF <right paren> 
         WITH <cast to ref identifier> 
<cast to 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 option> ::=
       [ <cast to distinct>  ]
       [ <cast to source>  ]
<cast to distinct> ::=
       CAST <left paren>  SOURCE AS DISTINCT <right paren> 
       WITH <cast to distinct identifier> 
<cast to 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 ] METHOD <method name>  <SQL parameter declaration list> 
         <returns clause> 
         [ SPECIFIC <specific name>  ]
<method characteristics> ::=
       <method characteristic> ...

11.42 <alter type statement>

Function

Change the definition of a user-defined type. .Format

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

11.43 <add attribute definition>

Function

Add an attribute to a user-defined type. .Format

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

11.44 <drop attribute definition>

Function

Destroy an attribute of a user-defined type. .Format

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

11.45 <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.46 <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.47 <drop method specification>

Function

Remove a method specification from a user-defined type. .Format

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

11.48 <drop data type statement>

Function

Destroy a user-defined type. .Format

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

11.49 <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 list> ::=
       <left paren> 
         [ <SQL parameter declaration>  [ { <comma>  <SQL parameter declaration>  }... ] ]
<right paren>
<SQL parameter declaration> ::=
         [ <parameter mode>  ] [ <SQL parameter name>  ]
         <parameter type> 
         [ RESULT ]
<parameter mode> ::=
         IN
       | OUT
       | INOUT
<parameter type> ::=
       <data type>  [ <locator indication>  ]
<locator indication> ::=
       AS LOCATOR
<method specification designator> ::=
       [ INSTANCE | STATIC ] METHOD <method name>  <SQL parameter declaration list> 
       [ <returns clause>  ]
       FOR <user-defined type> 
<routine characteristics> ::=
       [ <routine characteristic> ... ]
<dynamic result sets characteristic> ::=
       DYNAMIC RESULT SETS <maximum dynamic result sets> 
<parameter style clause> ::=
       PARAMETER STYLE <parameter style> 
<dispatch clause> ::= STATIC DISPATCH
<returns clause> ::= RETURNS <returns data type> [ <result cast> ]
<result cast> ::= CAST FROM <result cast from type>
<result cast from type> ::=
         <data type>  [ <locator indication>  ]
<returns data type> ::= <data type> [ <locator indication> ]
<routine body> ::=
         <SQL routine body> 
       | <external body reference> 
<SQL routine body> ::= <SQL procedure statement>
<external body reference> ::=
       EXTERNAL [ NAME <external routine name>  ]
       [ <parameter style clause>  ]
       [ <external security clause>  ]
<external security clause>   ::=
         EXTERNAL SECURITY   DEFINER
       | EXTERNAL SECURITY   INVOKER
       | EXTERNAL SECURITY   IMPLEMENTATION DEFINED
<parameter style> ::=
         SQL
       | GENERAL
<deterministic characteristic> ::=
         DETERMINISTIC
       | NOT DETERMINISTIC
<SQL-data access indication> ::=
         NO SQL
       | CONTAINS SQL
       | READS SQL DATA
       | MODIFIES SQL DATA
<null-call clause> ::=
         RETURNS NULL ON NULL INPUT
       | CALLED ON NULL INPUT
<maximum dynamic result sets> ::= <unsigned integer>
<transform group specification> ::=
       TRANSFORM GROUP
           { <single group specification>  | <multiple group specification>  }
<single group specification> ::=
       <group name> 
<multiple group specification> ::=
       <group specification>  [ { <comma>  <group specification>  }... ]
<group specification> ::=
       <group name>  FOR TYPE <user-defined type>

11.50 <alter routine statement>

Function

Alter a characteristic of an SQL-invoked routine. .Format

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

11.51 <drop routine statement>

Function

Destroy an SQL-invoked routine. .Format

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

11.53 <drop user-defined cast statement>

Function

Destroy a user-defined cast. .Format

11.54 <user-defined ordering definition>

Function

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

<user-defined ordering definition> ::=
       CREATE ORDERING FOR <user-defined type>  <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.55 <drop user-defined ordering statement>

Function

Destroy a user-defined ordering method. .Format

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

11.56 <transform definition>

Function

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

<transform definition> ::=
       CREATE { TRANSFORM | TRANSFORMS } FOR <user-defined type>  <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.57 <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 <user-defined type>  <drop behavior> 
<transforms to be dropped> ::=
         ALL
       | <transform group element> 
<transform group element> ::=
       <group name>

12 Access control

12.1 <grant statement>

Function

Define privileges and role authorizations. .Format

12.2 <grant privilege statement>

Function

Define privileges. .Format

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

12.3 <role definition>

Function

Define a role. .Format

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

12.4 <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.5 <drop role statement>

Function

Destroy a role. .Format

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

12.6 <revoke statement>

Function

Destroy privileges and role authorizations. .Format

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

13 SQL-client modules

13.1 <SQL-client module definition>

Function

Define an SQL-client module. .Format

<module authorization clause> ::=
         SCHEMA <schema name> 
       | AUTHORIZATION <module authorization identifier> 
       | SCHEMA <schema name>  AUTHORIZATION <module authorization identifier> 
<module authorization identifier> ::=
       <authorization identifier> 
<module path specification> ::=
       <path specification> 
<module transform group specification> ::=
       <transform group 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. .Format

<host parameter declaration setup> ::=
         <host parameter declaration list> 
       | <host parameter declaration> ...
<host parameter data type> ::=
       <data type>  [ <locator indication>  ]
<status parameter> ::=
         SQLSTATE

13.5 <SQL procedure statement>

Function

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

<SQL procedure statement> ::=
         <SQL executable statement> 
<SQL data change   statement>   ::=
         <delete   statement:   positioned> 
       | <delete   statement:   searched> 
       | <insert   statement> 
       | <update   statement:   positioned> 
       | <update   statement:   searched> 
<SQL control statement> ::=
         <call statement> 
       | <return statement> 
<SQL diagnostics statement> ::=
       <get diagnostics statement>

14 Data manipulation

14.1 <declare cursor>

Function

Define a cursor. .Format

<cursor sensitivity> ::=
         SENSITIVE
       | INSENSITIVE
       | ASENSITIVE
<cursor scrollability> ::=
         SCROLL
       | NO SCROLL
<cursor holdability> ::=
         WITH HOLD
       | WITHOUT HOLD
<cursor returnability> ::=
         WITH RETURN
       | WITHOUT RETURN
<updatability clause> ::=
       FOR { READ ONLY | UPDATE [ OF <column name list>  ] }
<order by clause> ::=
       ORDER BY <sort specification list> 
<sort specification list> ::=
       <sort specification>  [ { <comma>  <sort specification>  }... ]
<sort key> ::=
       <value expression> 
<ordering specification> ::= ASC | DESC

14.2 <open statement>

Function

Open a cursor. .Format

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

14.3 <fetch statement>

Function

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

<fetch statement> ::=
       FETCH [ [ <fetch orientation>  ] FROM ]
         <cursor name>  INTO <fetch target list> 
<fetch orientation> ::=
         NEXT
       | PRIOR
       | FIRST
       | LAST
       | { ABSOLUTE | RELATIVE } <simple value specification> 
<fetch target list> ::=
       <target specification>  [ { <comma>  <target specification>  }... ]

14.4 <close statement>

Function

Close a cursor. .Format

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

14.5 <select statement: single row>

Function

Retrieve values from a specified row of a table. .Format

<select statement: single row> ::=
       SELECT [ <set quantifier>  ] <select list> 
         INTO <select target list> 
           <table expression> 
<select target list> ::=
       <target specification>  [ { <comma>  <target specification>  }... ]

14.7 <delete statement: searched>

Function

Delete rows of a table. .Format

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

14.8 <insert statement>

Function

Create new rows in a table. .Format

<insert statement> ::=
       INSERT INTO <insertion target> 
         <insert columns and source> 
<insertion target> ::=
       <table name> 
<insert columns and source> ::=
         <from subquery> 
       | <from constructor> 
       | <from default> 
<from subquery> ::=
       [ <left paren>  <insert column list>  <right paren>  ]
         [ override clause> ]
         <query expression> 
<override clause> ::=
         OVERRIDING USER VALUE
       | OVERRIDING SYSTEM VALUE
<from default> ::=
       DEFAULT VALUES
<insert column list> ::= <column name list>

14.9 <update statement: positioned>

Function

Update a row of a table. .Format

<update statement: positioned> ::=
       UPDATE <target table> 
         SET <set clause list> 
           WHERE CURRENT OF <cursor name> 
<set clause list> ::=
       <set clause>  [ { <comma>  <set clause>  }... ]
<object column> ::= <column name>

14.10 <update statement: searched>

Function

Update rows of a table. .Format

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

14.11 <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.12 <free locator statement>

Function

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

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

14.13 <hold locator statement>

Function

Mark a locator variable as being holdable. .Format

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

15 Control statements

15.1 <call statement>

Function

Invoke an SQL-invoked routine. .Format

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

15.2 <return statement>

Function

Return a value from an SQL function. .Format

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

16 Transaction management

16.1 <start transaction statement>

Function

Start an SQL-transaction and set its characteristics. .Format

<start transaction statement> ::=
       START TRANSACTION <transaction mode>  [ { <comma>  <transaction mode>  }...]
<transaction access mode> ::=
         READ ONLY
       | READ WRITE
<isolation level> ::=
       ISOLATION LEVEL <level of isolation> 
<level of isolation> ::=
         READ UNCOMMITTED
       | READ COMMITTED
       | REPEATABLE READ
       | SERIALIZABLE
<diagnostics size> ::=
       DIAGNOSTICS SIZE <number of conditions> 
<number of conditions> ::= <simple value specification>

16.2 <set transaction statement>

Function

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

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

16.3 <set constraints mode statement>

Function

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

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

16.4 <savepoint statement>

Function

Establish a savepoint. .Format

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

16.5 <release savepoint statement>

Function

Destroy a savepoint. .Format

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

16.6 <commit statement>

Function

Terminate the current SQL-transaction with commit. .Format

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

16.7 <rollback statement>

Function

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

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

17 Connection management

17.1 <connect statement>

Function

Establish an SQL-session. .Format

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

17.2 <set connection statement>

Function

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

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

17.3 <disconnect statement>

Function

Terminate an SQL-connection. .Format

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

18 Session management

18.1 <set session characteristics statement>

Function

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

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

18.2 <set session user identifier statement>

Function

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

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

18.3 <set role statement>

Function

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

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

18.4 <set local time zone statement>

Function

Set the default local 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 Diagnostics management

19.1 <get diagnostics statement>

Function

Get exception or completion condition information from the diagnostics area. .Format

<get diagnostics statement> ::=
       GET DIAGNOSTICS <SQL diagnostics information> 
<SQL diagnostics information> ::=
         <statement information> 
       | <condition information> 
<statement information item name> ::=
         NUMBER
       | MORE
       | COMMAND_FUNCTION
       | COMMAND_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
<condition number> ::= <simple value specification>