Derived from file sql-2003-2 version 1.16
dated 2011/07/11 18:00:02
Generated on 2011-07-11 19:57:42+00:00 by file
bnf2html.pl version 3.11 dated 2011/07/11 19:57:38
Information taken from the Final Committee Draft (FCD) of ISO/IEC 9075-2:2003. However, the page numbers and some section titles (9.14 through 9.23, for example) are from the final standard. This means there could be other as yet undiagnosed differences between the final standard and the notation in this document; you were warned!
The plain text version of this grammar is sql-2003-2 .
Basic definitions of characters used, tokens, symbols, etc. Most of this section would normally be handled within the lexical analyzer rather than in the grammar proper. Further, the original document does not quote the various single characters, which makes it hard to process automatically.
<SQL terminal character> ::= <SQL language character>
<SQL language character> ::= <simple Latin letter> | <digit> | <SQL special character>
<simple Latin letter> ::= <simple Latin upper case letter> | <simple Latin lower case letter>
<simple Latin upper case letter> ::=
A | B
| C
| D
| E
| F
| G
| H
| I
| J
| K
| L
| M
| N
| O
| P
| Q
| R
| S
| T
| U
| V
| W
| X
| Y
| Z
<simple Latin lower case letter> ::=
a | b
| c
| d
| e
| f
| g
| h
| i
| j
| k
| l
| m
| n
| o
| p
| q
| r
| s
| t
| u
| v
| w
| x
| y
| z
<digit> ::= 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9
<SQL special character> ::=
<space>
| <double
quote>
| <percent>
| <ampersand>
| <quote>
| <left
paren>
| <right
paren>
| <asterisk>
| <plus
sign>
| <comma>
| <minus
sign>
| <period>
| <solidus>
| <colon>
| <semicolon>
| <less
than operator>
| <equals
operator>
| <greater
than operator>
| <question
mark>
| <left
bracket>
| <right
bracket>
| <circumflex>
| <underscore>
| <vertical
bar>
| <left
brace>
| <right
brace>
<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> ::= ?
The trigraphs are new in SQL-2003.
<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> ::= /* Nothing */ |
<left brace> ::= {
<right brace> ::= }
Specifying lexical units (tokens and separators) that participate in SQL language.
<token> ::= <nondelimiter token> | <delimiter token>
<nondelimiter token> ::=
<regular
identifier>
| <key
word>
| <unsigned
numeric literal>
| <national
character string literal>
| <bit
string literal>
| <hex
string literal>
| <large
object length token>
| <multiplier>
<regular identifier> ::= <identifier body>
<identifier body> ::= <identifier start> [ <identifier part> ... ]
<identifier part> ::= <identifier start> | <identifier extend>
Previous standard said:
<identifier start> ::= <initial alphabetic character> | <ideographic character>
<identifier start> ::= !! See the Syntax Rules.
<identifier extend> ::= !! See the Syntax Rules.
<large object length token> ::= <digit> ... <multiplier>
<multiplier> ::= K | M | G
<delimited identifier> ::= <double quote> <delimited identifier body> <double quote>
<delimited identifier body> ::= <delimited identifier part> ...
<delimited identifier part> ::= <nondoublequote character> | <doublequote symbol>
The productions for <Unicode delimited identifier> and so on are new in SQL-2003.
<Unicode delimited identifier> ::=
U <ampersand>
<double
quote> <Unicode
delimiter body> <double
quote>
<Unicode
escape specifier>
<Unicode escape specifier> ::= [ UESCAPE <quote> <Unicode escape character> <quote> ]
<Unicode delimiter body> ::= <Unicode identifier part> ...
<Unicode identifier part> ::= <delimited identifier part> | <Unicode escape value>
<Unicode escape value> ::=
<Unicode
4 digit escape value>
| <Unicode
6 digit escape value>
| <Unicode
character escape value>
Syntax rule 20: <Unicode 4 digit escape value>'<Unicode escape character>+xyzw' is equivalent to the Unicode code point specified by U+xyzw.
<Unicode 4 digit escape value> ::= <Unicode escape character> <hexit> <hexit> <hexit> <hexit>
Syntax rule 21: <Unicode 6 digit escape value>'<Unicode escape character>+xyzwrs' is equivalent to the Unicode code point specified by U+xyzwrs.
NOTE 64: The 6-hexit notation is derived by taking the UCS-4 notation defined by ISO/IEC 10646-1 and removing the leading two hexits, whose values are always 0 (zero).
<Unicode 6 digit escape value> ::=
<Unicode
escape character> <plus
sign> <hexit>
<hexit>
<hexit>
<hexit>
<hexit>
<hexit>
Syntax rule 22: <Unicode character escape value> is equivalent to a single instance of <Unicode escape character>.
<Unicode character escape value> ::= <Unicode escape character> <Unicode escape character>
Syntax rule 15: <Unicode escape character> shall be a single character from the source language character set other than a <hexit>, <plus sign>, or <white space>.
Syntax rule 16: If the source language character set contains <reverse solidus>, then let DEC be <reverse solidus>; otherwise, let DEC be an implementation-defined character from the source language character set that is not a <hexit>, <plus sign>, <double quote>, or <white space>.
Syntax rule 17: If a <Unicode escape specifier> does not contain< Unicode escape character>, then "UESCAPE <quote>DEC<quote>" is implicit.
Syntax rule 18: In a <Unicode escape value> there shall be no < separator> between the <Unicode escape character> and the first < hexit>, nor between any of the <hexit>s.
<Unicode escape character> ::= !! See the Syntax Rules (15-18 above).
Syntax rule 6: A <nondoublequote character> is any character of the source language character set other than a <double quote>.
<nondoublequote character> ::= !! See the Syntax Rules.
The rule for <doublequote symbol> in the standard uses two adjacent literal double quotes rather than referencing <double quote>; the reasons are not clear. It is annotated '!! two consecutive double quote characters'.
<doublequote symbol> ::= <double quote> <double quote>
<delimiter token> ::=
<character
string literal>
| <date
string>
| <time
string>
| <timestamp
string>
| <interval
string>
| <delimited
identifier>
| <Unicode
delimited identifier>
| <SQL
special character>
|
<not
equals operator>
| <greater
than or equals operator>
| <less
than or equals operator>
| <concatenation
operator>
| <right
arrow>
| <left
bracket trigraph>
|
<right
bracket trigraph>
|
<double
colon>
| <double
period>
The rules for <not equals operator> etc in the standard uses two adjacent literal characters rather than referencing< less than> and <greater than>; the reasons are not clear. Note that two characters must be adjacent with no intervening space, not a pair of characters separated by arbitrary white space.
<not equals operator> ::= <less than operator> <greater than operator>
<greater than or equals operator> ::= <greater than operator> <equals operator>
<less than or equals operator> ::= <less than operator> <equals operator>
<concatenation operator> ::= <vertical bar> <vertical bar>
<right arrow> ::= <minus sign> <greater than operator>
<double colon> ::= <colon> <colon>
<double period> ::= <period> <period>
<separator> ::= { <comment> | <white space> }...
<comment> ::= <simple comment> | <bracketed comment>
<simple comment> ::= <simple comment introducer> [ <comment character> ... ] <newline>
<simple comment introducer> ::= <minus sign> <minus sign> [ <minus sign> ... ]
The <bracketed comment> rule included '!! See the Syntax Rules'. This probably says something about the <slash> <asterisk> and <asterisk>< slash> needing to be adjacent characters rather than adjacent tokens.
<bracketed comment> ::=
<bracketed
comment introducer> <bracketed
comment contents> <bracketed
comment terminator>
<bracketed comment introducer> ::= <slash> <asterisk>
<bracketed comment terminator> ::= <asterisk> <slash>
<bracketed comment contents> ::= [ { <comment character> | <separator> }... ]
<comment character> ::= <nonquote character> | <quote>
<newline> ::= !! See the Syntax Rules.
There was a surprising amount of movement of keywords between the reserved and non-reserved word classes between SQL-99 and SQL-2003-2 FCD and again between SQL 2003-2 FCD and SQL 2003-2 IS. There is also room to think that much of the host language support moved out of Part 2 (SQL/Foundation).
<key word> ::= <reserved word> | <non-reserved word>
<non-reserved word> ::=
A
|
ABS
|
ABSOLUTE
|
ACTION
|
ADA
|
ADMIN
|
AFTER
|
ALWAYS
|
ASC
|
ASSERTION
|
ASSIGNMENT
|
ATTRIBUTE
|
ATTRIBUTES
|
AVG
|
BEFORE
|
BERNOULLI
|
BREADTH
|
C
|
CARDINALITY
|
CASCADE
|
CATALOG
|
CATALOG_NAME
|
CEIL
|
CEILING
|
CHAIN
|
CHARACTERISTICS
|
CHARACTERS
|
CHARACTER_LENGTH
|
CHARACTER_SET_CATALOG
|
CHARACTER_SET_NAME
|
CHARACTER_SET_SCHEMA
|
CHAR_LENGTH
|
CHECKED
|
CLASS_ORIGIN
|
COALESCE
|
COBOL
|
CODE_UNITS
|
COLLATION
|
COLLATION_CATALOG
|
COLLATION_NAME
|
COLLATION_SCHEMA
| COLLECT
|
COLUMN_NAME
|
COMMAND_FUNCTION
|
COMMAND_FUNCTION_CODE
|
COMMITTED
|
CONDITION
|
CONDITION_NUMBER
|
CONNECTION_NAME
|
CONSTRAINTS
|
CONSTRAINT_CATALOG
|
CONSTRAINT_NAME
|
CONSTRAINT_SCHEMA
|
CONSTRUCTORS
|
CONTAINS
|
CONVERT
|
CORR
|
COUNT
|
COVAR_POP
|
COVAR_SAMP
|
CUME_DIST
|
CURRENT_COLLATION
|
CURSOR_NAME
|
DATA
|
DATETIME_INTERVAL_CODE
|
DATETIME_INTERVAL_PRECISION
|
DEFAULTS
|
DEFERRABLE
|
DEFERRED
|
DEFINED
|
DEFINER
|
DEGREE
|
DENSE_RANK
|
DEPTH
|
DERIVED
|
DESC
|
DESCRIPTOR
|
DIAGNOSTICS
|
DISPATCH
|
DOMAIN
|
DYNAMIC_FUNCTION
|
DYNAMIC_FUNCTION_CODE
|
EQUALS
|
EVERY
|
EXCEPTION
|
EXCLUDE
|
EXCLUDING
|
EXP
|
EXTRACT
|
FINAL
|
FIRST
|
FLOOR
|
FOLLOWING
|
FORTRAN
|
FOUND
|
FUSION
|
G
|
GENERAL
|
GO
|
GOTO
|
GRANTED
|
HIERARCHY
|
IMPLEMENTATION
|
INCLUDING
|
INCREMENT
|
INITIALLY
|
INSTANCE
|
INSTANTIABLE
|
INTERSECTION
|
INVOKER
|
ISOLATION
|
K
|
KEY
|
KEY_MEMBER
|
KEY_TYPE
|
LAST
|
LENGTH
|
LEVEL
|
LN
|
LOCATOR
|
LOWER
|
M
|
MAP
|
MATCHED
|
MAX
|
MAXVALUE
|
MESSAGE_LENGTH
|
MESSAGE_OCTET_LENGTH
|
MESSAGE_TEXT
|
MIN
|
MINVALUE
|
MOD
|
MORE
|
MUMPS
|
NAME
|
NAMES
|
NESTING
|
NEXT
|
NORMALIZE
|
NORMALIZED
|
NULLABLE
|
NULLIF
|
NULLS
|
NUMBER
|
OBJECT
|
OCTETS
|
OCTET_LENGTH
|
OPTION
|
OPTIONS
|
ORDERING
|
ORDINALITY
|
OTHERS
|
OVERLAY
|
OVERRIDING
|
PAD
|
PARAMETER_MODE
|
PARAMETER_NAME
|
PARAMETER_ORDINAL_POSITION
|
PARAMETER_SPECIFIC_CATALOG
|
PARAMETER_SPECIFIC_NAME
|
PARAMETER_SPECIFIC_SCHEMA
|
PARTIAL
|
PASCAL
|
PATH
|
PERCENTILE_CONT
|
PERCENTILE_DISC
|
PERCENT_RANK
|
PLACING
|
PLI
|
POSITION
|
POWER
|
PRECEDING
|
PRESERVE
|
PRIOR
|
PRIVILEGES
|
PUBLIC
|
RANK
|
READ
|
RELATIVE
|
REPEATABLE
|
RESTART
|
RETURNED_CARDINALITY
|
RETURNED_LENGTH
|
RETURNED_OCTET_LENGTH
|
RETURNED_SQLSTATE
| ROLE
|
ROUTINE
|
ROUTINE_CATALOG
|
ROUTINE_NAME
|
ROUTINE_SCHEMA
|
ROW_COUNT
|
ROW_NUMBER
|
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
| SQRT
|
STATE
|
STATEMENT
|
STDDEV_POP
|
STDDEV_SAMP
|
STRUCTURE
|
STYLE
|
SUBCLASS_ORIGIN
|
SUBSTRING
|
SUM
|
TABLESAMPLE
|
TABLE_NAME
|
TEMPORARY
|
TIES
|
TOP_LEVEL_COUNT
|
TRANSACTION
|
TRANSACTIONS_COMMITTED
|
TRANSACTIONS_ROLLED_BACK
|
TRANSACTION_ACTIVE
|
TRANSFORM
|
TRANSFORMS
|
TRANSLATE
|
TRIGGER_CATALOG
|
TRIGGER_NAME
|
TRIGGER_SCHEMA
| TRIM
|
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> ::=
ADD
|
ALL
|
ALLOCATE
|
ALTER
|
AND
|
ANY
|
ARE
|
ARRAY
|
AS
|
ASENSITIVE
|
ASYMMETRIC
|
AT
|
ATOMIC
|
AUTHORIZATION
| BEGIN
|
BETWEEN
|
BIGINT
|
BINARY
|
BLOB
|
BOOLEAN
|
BOTH
|
BY
|
CALL
|
CALLED
|
CASCADED
|
CASE
|
CAST
|
CHAR
|
CHARACTER
|
CHECK
|
CLOB
|
CLOSE
|
COLLATE
|
COLUMN
|
COMMIT
|
CONNECT
|
CONSTRAINT
|
CONTINUE
|
CORRESPONDING
| CREATE
|
CROSS
|
CUBE
|
CURRENT
|
CURRENT_DATE
|
CURRENT_DEFAULT_TRANSFORM_GROUP
|
CURRENT_PATH
|
CURRENT_ROLE
|
CURRENT_TIME
|
CURRENT_TIMESTAMP
|
CURRENT_TRANSFORM_GROUP_FOR_TYPE
|
CURRENT_USER
|
CURSOR
|
CYCLE
|
DATE
|
DAY
|
DEALLOCATE
|
DEC
|
DECIMAL
|
DECLARE
|
DEFAULT
|
DELETE
|
DEREF
|
DESCRIBE
|
DETERMINISTIC
|
DISCONNECT
|
DISTINCT
|
DOUBLE
|
DROP
|
DYNAMIC
|
EACH
|
ELEMENT
|
ELSE
|
END
|
END-EXEC
|
ESCAPE
|
EXCEPT
|
EXEC
|
EXECUTE
|
EXISTS
|
EXTERNAL
|
FALSE
|
FETCH
|
FILTER
|
FLOAT
|
FOR
|
FOREIGN
|
FREE
|
FROM
|
FULL
|
FUNCTION
|
GET
|
GLOBAL
|
GRANT
|
GROUP
|
GROUPING
|
HAVING
|
HOLD
|
HOUR
|
IDENTITY
|
IMMEDIATE
|
IN
|
INDICATOR
|
INNER
|
INOUT
|
INPUT
|
INSENSITIVE
|
INSERT
|
INT
|
INTEGER
|
INTERSECT
|
INTERVAL
|
INTO
|
IS
|
ISOLATION
|
JOIN
|
LANGUAGE
|
LARGE
|
LATERAL
|
LEADING
|
LEFT
|
LIKE
|
LOCAL
|
LOCALTIME
|
LOCALTIMESTAMP
| MATCH
|
MEMBER
|
MERGE
|
METHOD
|
MINUTE
|
MODIFIES
|
MODULE
|
MONTH
|
MULTISET
|
NATIONAL
|
NATURAL
|
NCHAR
|
NCLOB
|
NEW
|
NO
|
NONE
|
NOT
|
NULL
|
NUMERIC
|
OF
|
OLD
|
ON
|
ONLY
|
OPEN
|
OR
|
ORDER
|
OUT
|
OUTER
|
OUTPUT
|
OVER
|
OVERLAPS
|
PARAMETER
|
PARTITION
|
PRECISION
|
PREPARE
|
PRIMARY
|
PROCEDURE
|
RANGE
|
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
|
ROWS
|
SAVEPOINT
|
SCROLL
|
SEARCH
|
SECOND
|
SELECT
|
SENSITIVE
|
SESSION_USER
|
SET
|
SIMILAR
|
SMALLINT
|
SOME
|
SPECIFIC
|
SPECIFICTYPE
|
SQL
|
SQLEXCEPTION
|
SQLSTATE
|
SQLWARNING
|
START
|
STATIC
|
SUBMULTISET
|
SYMMETRIC
|
SYSTEM
|
SYSTEM_USER
|
TABLE
|
THEN
|
TIME
|
TIMESTAMP
|
TIMEZONE_HOUR
|
TIMEZONE_MINUTE
| TO
|
TRAILING
|
TRANSLATION
|
TREAT
|
TRIGGER
|
TRUE
|
UESCAPE
|
UNION
|
UNIQUE
|
UNKNOWN
|
UNNEST
|
UPDATE
|
UPPER
|
USER
|
USING
|
VALUE
|
VALUES
|
VAR_POP
|
VAR_SAMP
|
VARCHAR
|
VARYING
|
WHEN
|
WHENEVER
|
WHERE
|
WIDTH_BUCKET
|
WINDOW
|
WITH
|
WITHIN
|
WITHOUT
|
YEAR
<literal> ::= <signed numeric literal> | <general literal>
<unsigned literal> ::= <unsigned numeric literal> | <general literal>
<general literal> ::=
<character
string literal>
| <national
character string literal>
| <Unicode
character string literal>
| <binary
string literal>
| <datetime
literal>
| <interval
literal>
| <boolean
literal>
<character string literal> ::=
[ <introducer>
<character
set specification> ]
<quote>
[ <character
representation> ... ] <quote>
[ { <separator>
<quote>
[ <character
representation> ... ] <quote>
}... ]
<character representation> ::= <nonquote character> | <quote symbol>
<nonquote character> ::= !! See the Syntax Rules.
The <quote symbol> rule consists of two immediately adjacent <quote> marks with no spaces. As usual, this would be best handled in the lexical analyzer, not in the grammar.
<quote symbol> ::= <quote> <quote>
<national character string literal> ::=
N <quote>
[ <character
representation> ... ] <quote>
[ { <separator>
<quote>
[ <character
representation> ... ] <quote>
}... ]
<Unicode character string literal> ::=
[ <introducer>
<character
set specification> ]
U <ampersand>
<quote>
[ <Unicode
representation> ... ] <quote>
[ { <separator>
<quote>
[ <Unicode
representation> ... ] <quote>
}... ]
[
ESCAPE <escape
character> ]
<Unicode representation> ::= <character representation> | <Unicode escape value>
<binary string literal> ::=
X <quote>
[ { <hexit>
<hexit>
}... ] <quote>
[ { <separator>
<quote>
[ { <hexit>
<hexit>
}... ] <quote>
}... ]
[
ESCAPE <escape
character> ]
<hexit> ::= <digit> | A | B | C | D | E | F | a | b | c | d | e | f
<signed numeric literal> ::= [ <sign> ] <unsigned numeric literal>
<unsigned numeric literal> ::= <exact numeric literal> | <approximate numeric literal>
<exact numeric literal> ::=
<unsigned
integer> [ <period>
[ <unsigned
integer> ] ]
| <period>
<unsigned
integer>
<sign> ::= <plus sign> | <minus sign>
<approximate numeric literal> ::= <mantissa> E <exponent>
<mantissa> ::= <exact numeric literal>
<exponent> ::= <signed integer>
<signed integer> ::= [ <sign> ] <unsigned integer>
<datetime literal> ::= <date literal> | <time literal> | <timestamp literal>
<date literal> ::= DATE <date string>
<time literal> ::= TIME <time string>
<timestamp literal> ::= TIMESTAMP <timestamp string>
<date string> ::= <quote> <unquoted date string> <quote>
<time string> ::= <quote> <unquoted time string> <quote>
<timestamp string> ::= <quote> <unquoted timestamp string> <quote>
<time zone interval> ::= <sign> <hours value> <colon> <minutes value>
<date value> ::= <years value> <minus sign> <months value> <minus sign> <days value>
<time value> ::= <hours value> <colon> <minutes value> <colon> <seconds value>
<interval literal> ::= INTERVAL [ <sign> ] <interval string> <interval qualifier>
<interval string> ::= <quote> <unquoted interval string> <quote>
<unquoted date string> ::= <date value>
<unquoted time string> ::= <time value> [ <time zone interval> ]
<unquoted timestamp string> ::= <unquoted date string> <space> <unquoted time string>
<unquoted interval string> ::= [ <sign> ] { <year-month literal> | <day-time literal> }
<year-month literal> ::= <years value> | [ <years value> <minus sign> ] <months value>
<day-time literal> ::= <day-time interval> | <time interval>
<day-time interval> ::=
<days
value> [ <space>
<hours
value> [ <colon>
<minutes
value> [ <colon>
<seconds
value> ] ] ]
<time interval> ::=
<hours
value> [ <colon>
<minutes
value> [ <colon>
<seconds
value> ] ]
| <minutes
value> [ <colon>
<seconds
value> ]
| <seconds
value>
<years value> ::= <datetime value>
<months value> ::= <datetime value>
<days value> ::= <datetime value>
<hours value> ::= <datetime value>
<minutes value> ::= <datetime value>
<seconds value> ::= <seconds integer value> [ <period> [ <seconds fraction> ] ]
<seconds integer value> ::= <unsigned integer>
<seconds fraction> ::= <unsigned integer>
<datetime value> ::= <unsigned integer>
<boolean literal> ::= TRUE | FALSE | UNKNOWN
<identifier> ::= <actual identifier>
<actual identifier> ::= <regular identifier> | <delimited identifier>
<SQL language identifier> ::=
<SQL
language identifier start> [ { <underscore>
| <SQL
language identifier part> }... ]
<SQL language identifier start> ::= <simple Latin letter>
<SQL language identifier part> ::= <simple Latin letter> | <digit>
<authorization identifier> ::= <role name> | <user identifier>
<table name> ::= <local or schema qualified name>
<domain name> ::= <schema qualified name>
<schema name> ::= [ <catalog name> <period> ] <unqualified schema name>
<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>
<SQL-client module name> ::= <identifier>
<procedure name> ::= <identifier>
<schema qualified routine name> ::= <schema qualified name>
<method name> ::= <identifier>
<specific name> ::= <schema qualified name>
<cursor name> ::= <local qualified name>
<local qualified name> ::= [ <local qualifier> <period> ] <qualified identifier>
<host parameter name> ::= <colon> <identifier>
<SQL parameter name> ::= <identifier>
<constraint name> ::= <schema qualified name>
<external routine name> ::= <identifier> | <character string literal>
<trigger name> ::= <schema qualified name>
<collation name> ::= <schema qualified name>
<character set name> ::= [ <schema name> <period> ] <SQL language identifier>
<transliteration name> ::= <schema qualified name>
<transcoding name> ::= <schema qualified name>
<user-defined type name> ::= <schema qualified type name>
<schema-resolved user-defined type name> ::= <user-defined type name>
<schema qualified type name> ::= [ <schema name> <period> ] <qualified identifier>
<attribute name> ::= <identifier>
<savepoint name> ::= <identifier>
<sequence generator name> ::= <schema qualified name>
<user identifier> ::= <identifier>
<connection name> ::= <simple value specification>
<SQL-server name> ::= <simple value specification>
<connection user name> ::= <simple value specification>
<SQL statement name> ::= <statement name> | <extended statement name>
<statement name> ::= <identifier>
<extended statement name> ::= [ <scope option> ] <simple value specification>
<dynamic cursor name> ::= <cursor name> | <extended cursor name>
<extended cursor name> ::= [ <scope option> ] <simple value specification>
<descriptor name> ::= [ <scope option> ] <simple value specification>
<scope option> ::= GLOBAL | LOCAL
<window name> ::= <identifier>
<data type> ::=
<predefined
type>
| <row
type>
| <path-resolved
user-defined type name>
| <reference
type>
| <collection
type>
<predefined type> ::=
<character
string type> [
CHARACTER
SET <character
set specification> ] [ <collate
clause> ]
| <national
character string type> [ <collate
clause> ]
| <binary
large object string type>
| <numeric
type>
| <boolean
type>
| <datetime
type>
| <interval
type>
<character string type> ::=
CHARACTER [ <left
paren> <length>
<right
paren> ]
|
CHAR [ <left
paren> <length>
<right
paren> ]
|
CHARACTER
VARYING <left
paren> <length>
<right
paren>
|
CHAR
VARYING <left
paren> <length>
<right
paren>
|
VARCHAR <left
paren> <length>
<right
paren>
|
CHARACTER
LARGE
OBJECT [ <left
paren> <large
object length> <right
paren> ]
|
CHAR LARGE
OBJECT
[ <left
paren> <large
object length> <right
paren> ]
|
CLOB [ <left
paren> <large
object length> <right
paren> ]
<national character string type> ::=
NATIONAL
CHARACTER [ <left
paren> <length>
<right
paren> ]
|
NATIONAL
CHAR [ <left
paren> <length>
<right
paren> ]
|
NCHAR [ <left
paren> <length>
<right
paren> ]
|
NATIONAL
CHARACTER
VARYING <left
paren> <length>
<right
paren>
|
NATIONAL
CHAR
VARYING <left
paren> <length>
<right
paren>
|
NCHAR
VARYING <left
paren> <length>
<right
paren>
|
NATIONAL
CHARACTER
LARGE
OBJECT [ <left
paren> <large
object length> <right
paren> ]
|
NCHAR
LARGE
OBJECT [ <left
paren> <large
object length> <right
paren> ]
|
NCLOB [ <left
paren> <large
object length> <right
paren> ]
<binary large object string type> ::=
BINARY
LARGE
OBJECT [ <left
paren> <large
object length> <right
paren> ]
|
BLOB [ <left
paren> <large
object length> <right
paren> ]
<numeric type> ::= <exact numeric type> | <approximate numeric type>
<exact numeric type> ::=
NUMERIC [ <left
paren> <precision>
[ <comma>
<scale>
] <right
paren> ]
|
DECIMAL [ <left
paren> <precision>
[ <comma>
<scale>
] <right
paren> ]
|
DEC [ <left
paren> <precision>
[ <comma>
<scale>
] <right
paren> ]
|
SMALLINT
|
INTEGER
|
INT
|
BIGINT
<approximate numeric type> ::=
FLOAT [ <left
paren> <precision>
<right
paren> ]
|
REAL
|
DOUBLE
PRECISION
<length> ::= <unsigned integer>
<large object length> ::=
<unsigned
integer> [ <multiplier>
] [ <char
length units> ]
| <large
object length token> [ <char
length units> ]
<char length units> ::= CHARACTERS | CODE_UNITS | OCTETS
<precision> ::= <unsigned integer>
<scale> ::= <unsigned integer>
<datetime type> ::=
DATE
|
TIME [ <left
paren> <time
precision> <right
paren> ] [ <with
or without time zone> ]
|
TIMESTAMP [ <left
paren> <timestamp
precision> <right
paren> ] [ <with
or without time zone> ]
<with or without time zone> ::= WITH TIME ZONE | WITHOUT TIME ZONE
<time precision> ::= <time fractional seconds precision>
<timestamp precision> ::= <time fractional seconds precision>
<time fractional seconds precision> ::= <unsigned integer>
<interval type> ::= INTERVAL <interval qualifier>
<row type> ::= ROW <row type body>
<row type body> ::= <left paren> <field definition> [ { <comma> <field definition> }... ] <right paren>
<reference type> ::= REF <left paren> <referenced type> <right paren> [ <scope clause> ]
<scope clause> ::= SCOPE <table name>
<referenced type> ::= <path-resolved user-defined type name>
<path-resolved user-defined type name> ::= <user-defined type name>
<path-resolved user-defined type name> ::= <user-defined type name>
<collection type> ::= <array type> | <multiset type>
<array type> ::= <data type> ARRAY [ <left bracket or trigraph> <unsigned integer> <right bracket or trigraph> ]
<multiset type> ::= <data type> MULTISET
<field definition> ::= <field name> <data type> [ <reference scope check> ]
<value expression primary> ::=
<parenthesized
value expression>
|
<nonparenthesized
value expression primary>
<parenthesized value expression> ::= <left paren> <value expression> <right paren>
<nonparenthesized value expression primary> ::=
<unsigned
value specification>
| <column
reference>
| <set
function specification>
| <window
function>
| <scalar
subquery>
| <case
expression>
| <cast
specification>
| <field
reference>
| <subtype
treatment>
| <method
invocation>
| <static
method invocation>
|
<new
specification>
| <attribute
or method reference>
| <reference
resolution>
| <collection
value constructor>
|
<array
element reference>
|
<multiset
element reference>
|
<routine
invocation>
| <next
value expression>
<value specification> ::= <literal> | <general value specification>
<unsigned value specification> ::= <unsigned literal> | <general value specification>
<general value specification> ::=
<host
parameter specification>
| <SQL
parameter reference>
| <dynamic
parameter specification>
| <embedded
variable specification>
| <current
collation specification>
|
CURRENT_DEFAULT_TRANSFORM_GROUP
|
CURRENT_PATH
|
CURRENT_ROLE
|
CURRENT_TRANSFORM_GROUP_FOR_TYPE <path-resolved
user-defined type name>
|
CURRENT_USER
|
SESSION_USER
|
SYSTEM_USER
|
USER
|
VALUE
<simple value specification> ::=
<literal>
| <host
parameter name>
| <SQL
parameter reference>
| <embedded
variable name>
<target specification> ::=
<host
parameter specification>
| <SQL
parameter reference>
| <column
reference>
| <target
array element specification>
| <dynamic
parameter specification>
| <embedded
variable specification>
<simple target specification> ::=
<host
parameter specification>
| <SQL
parameter reference>
| <column
reference>
| <embedded
variable name>
<host parameter specification> ::= <host parameter name> [ <indicator parameter> ]
<dynamic parameter specification> ::= <question mark>
<embedded variable specification> ::= <embedded variable name> [ <indicator variable> ]
<indicator variable> ::= [ INDICATOR ] <embedded variable name>
<indicator parameter> ::= [ INDICATOR ] <host parameter name>
<target array element specification> ::=
<target
array reference> <left
bracket or trigraph> <simple
value specification> <right
bracket or trigraph>
<target array reference> ::= <SQL parameter reference> | <column reference>
<current collation specification> ::= CURRENT_COLLATION <left paren> <string value expression> <right paren>
<contextually typed value specification> ::=
<implicitly
typed value specification> | <default
specification>
<implicitly typed value specification> ::= <null specification> | <empty specification>
<empty specification> ::=
ARRAY <left
bracket or trigraph> <right
bracket or trigraph>
|
MULTISET <left
bracket or trigraph> <right
bracket or trigraph>
<default specification> ::= DEFAULT
<identifier chain> ::= <identifier> [ { <period> <identifier> }... ]
<basic identifier chain> ::= <identifier chain>
<column reference> ::=
<basic
identifier chain>
|
MODULE
<period>
<qualified
identifier> <period>
<column
name>
<SQL parameter reference> ::= <basic identifier chain>
<set function specification> ::= <aggregate function> | <grouping operation>
<grouping operation> ::= GROUPING <left paren> <column reference> [ { <comma> <column reference> }... ] <right paren>
<window function> ::= <window function type> OVER <window name or specification>
<window function type> ::=
<rank
function type> <left
paren> <right
paren>
|
ROW_NUMBER <left
paren> <right
paren>
| <aggregate
function>
<rank function type> ::= RANK | DENSE_RANK | PERCENT_RANK | CUME_DIST
<window name or specification> ::= <window name> | <in-line window specification>
<in-line window specification> ::= <window specification>
<case expression> ::= <case abbreviation> | <case specification>
<case abbreviation> ::=
NULLIF <left
paren> <value
expression> <comma>
<value
expression> <right
paren>
|
COALESCE <left
paren> <value
expression> { <comma>
<value
expression> }... <right
paren>
<case specification> ::= <simple case> | <searched case>
<simple case> ::= CASE <case operand> <simple when clause> ... [ <else clause> ] END
<searched case> ::= CASE <searched when clause> ... [ <else clause> ] END
<simple when clause> ::= WHEN <when operand> THEN <result>
<searched when clause> ::= WHEN <search condition> THEN <result>
<else clause> ::= ELSE <result>
<case operand> ::= <row value predicand> | <overlaps predicate part>
<when operand> ::=
<row
value predicand>
| <comparison
predicate part 2>
|
<between
predicate part 2>
|
<in
predicate part 2>
|
<character
like predicate part 2>
| <octet
like predicate part 2>
| <similar
predicate part 2>
|
<null
predicate part 2>
|
<quantified
comparison predicate part 2>
| <match
predicate part 2>
|
<overlaps
predicate part 2>
|
<distinct
predicate part 2>
|
<member
predicate part 2>
|
<submultiset
predicate part 2>
|
<set
predicate part 2>
|
<type
predicate part 2>
<result> ::= <result expression> | NULL
<result expression> ::= <value expression>
<cast specification> ::= CAST <left paren> <cast operand> AS <cast target> <right paren>
<cast operand> ::= <value expression> | <implicitly typed value specification>
<cast target> ::= <domain name> | <data type>
<next value expression> ::= NEXT VALUE FOR <sequence generator name>
<field reference> ::= <value expression primary> <period> <field name>
<subtype treatment> ::=
TREAT <left
paren> <subtype
operand>
AS <target
subtype> <right
paren>
<subtype operand> ::= <value expression>
<target subtype> ::=
<path-resolved
user-defined type name>
| <reference
type>
<method invocation> ::= <direct invocation> | <generalized invocation>
<direct invocation> ::=
<value
expression primary> <period>
<method
name> [ <SQL
argument list> ]
<generalized invocation> ::=
<left
paren> <value
expression primary>
AS <data
type> <right
paren> <period>
<method
name>
[ <SQL
argument list> ]
<method selection> ::= <routine invocation>
<constructor method selection> ::= <routine invocation>
<static method invocation> ::=
<path-resolved
user-defined type name> <double
colon> <method
name> [ <SQL
argument list> ]
<static method selection> ::= <routine invocation>
<new specification> ::= NEW <routine invocation>
<new invocation> ::= <method invocation> | <routine invocation>
<attribute or method reference> ::=
<value
expression primary> <dereference
operator> <qualified
identifier>
[ <SQL
argument list> ]
<dereference operator> ::= <right arrow>
<dereference operation> ::= <reference value expression> <dereference operator> <attribute name>
<method reference> ::=
<value
expression primary> <dereference
operator> <method
name> <SQL
argument list>
<reference resolution> ::= DEREF <left paren> <reference value expression> <right paren>
<array element reference> ::=
<array
value expression> <left
bracket or trigraph> <numeric
value expression> <right
bracket or trigraph>
<multiset element reference> ::=
ELEMENT <left
paren> <multset
value expression> <right
paren>
Specify a value.
<value expression> ::=
<common
value expression>
|
<boolean
value expression>
|
<row
value expression>
<common value expression> ::=
<numeric
value expression>
|
<string
value expression>
|
<datetime
value expression>
|
<interval
value expression>
|
<user-defined
type value expression>
| <reference
value expression>
|
<collection
value expression>
<user-defined type value expression> ::= <value expression primary>
<reference value expression> ::= <value expression primary>
<collection value expression> ::= <array value expression> | <multiset value expression>
<collection value constructor> ::= <array value constructor> | <multiset value constructor>
Specify a numeric value.
<numeric value expression> ::=
<term>
| <numeric
value expression> <plus
sign> <term>
| <numeric
value expression> <minus
sign> <term>
<term> ::=
<factor>
| <term>
<asterisk>
<factor>
| <term>
<solidus>
<factor>
<factor> ::= [ <sign> ] <numeric primary>
<numeric primary> ::=
<value
expression primary>
|
<numeric
value function>
Specify a function yielding a value of type numeric.
<numeric value function> ::=
<position
expression>
| <extract
expression>
| <length
expression>
| <cardinality
expression>
| <absolute
value expression>
|
<modulus
expression>
| <natural
logarithm>
| <exponential
function>
| <power
function>
| <square
root>
| <floor
function>
| <ceiling
function>
| <width
bucket function>
<position expression> ::=
<string
position expression>
| <blob
position expression>
<string position expression> ::=
POSITION <left
paren> <string
value expression>
IN <string
value expression> [
USING <char
length units> ] <right
paren>
<blob position expression> ::=
POSITION <left
paren> <blob
value expression>
IN <blob
value expression> <right
paren>
<length expression> ::=
<char
length expression>
|
<octet
length expression>
<char length expression> ::=
{
CHAR_LENGTH |
CHARACTER_LENGTH } <left
paren> <string
value expression> [
USING <char
length units> ] <right
paren>
<octet length expression> ::= OCTET_LENGTH <left paren> <string value expression> <right paren>
<extract expression> ::= EXTRACT <left paren> <extract field> FROM <extract source> <right paren>
<extract field> ::= <primary datetime field> | <time zone field>
<time zone field> ::= TIMEZONE_HOUR | TIMEZONE_MINUTE
<extract source> ::= <datetime value expression> | <interval value expression>
<cardinality expression> ::= CARDINALITY <left paren> <collection value expression> <right paren>
<absolute value expression> ::= ABS <left paren> <numeric value expression> <right paren>
<modulus expression> ::= MOD <left paren> <numeric value expression dividend> <comma> <numeric value expression divisor> <right paren>
<natural logarithm> ::= LN <left paren> <numeric value expression> <right paren>
<exponential function> ::= EXP <left paren> <numeric value expression> <right paren>
<power function> ::= POWER <left paren> <numeric value expression base> <comma> <numeric value expression exponent> <right paren>
<numeric value expression base> ::= <numeric value expression>
<numeric value expression exponent> ::= <numeric value expression>
<square root> ::= SQRT <left paren> <numeric value expression> <right paren>
<floor function> ::= FLOOR <left paren> <numeric value expression> <right paren>
<ceiling function> ::= { CEIL | CEILING } <left paren> <numeric value expression> <right paren>
<width bucket function> ::= WIDTH_BUCKET <left paren> <width bucket operand> <comma> <width bucket bound 1> <comma> <width bucket bound 2> <comma> <width bucket count> <right paren>
<width bucket operand> ::= <numeric value expression>
<width bucket bound 1> ::= <numeric value expression>
<width bucket bound 2> ::= <numeric value expression>
<width bucket count> ::= <numeric value expression>
Specify a character string value or a binary string value.
<string value expression> ::= <character value expression> | <blob value expression>
<character value expression> ::= <concatenation> | <character factor>
<concatenation> ::= <character value expression> <concatenation operator> <character factor>
<character factor> ::= <character primary> [ <collate clause> ]
<character primary> ::= <value expression primary> | <string value function>
<blob value expression> ::= <blob concatenation> | <blob factor>
<blob factor> ::= <blob primary>
<blob primary> ::= <value expression primary> | <string value function>
<blob concatenation> ::= <blob value expression> <concatenation operator> <blob factor>
Specify a function yielding a value of type character string or binary string.
<string value function> ::= <character value function> | <blob value function>
<character value function> ::=
<character
substring function>
|
<regular
expression substring function>
| <fold>
| <transcoding>
| <character
transliteration>
| <trim
function>
| <character
overlay function>
|
<normalize
function>
| <specific
type method>
<character substring function> ::=
SUBSTRING <left
paren> <character
value expression>
FROM <start
position>
[
FOR <string
length> ] [
USING <char
length units> ] <right
paren>
<regular expression substring function> ::=
SUBSTRING <left
paren> <character
value expression>
SIMILAR
<character
value expression>
ESCAPE <escape
character> <right
paren>
<fold> ::= { UPPER | LOWER } <left paren> <character value expression> <right paren>
<transcoding> ::= CONVERT <left paren> <character value expression> USING <transcoding name> <right paren>
<character transliteration> ::= TRANSLATE <left paren> <character value expression> USING <transliteration name> <right paren>
<trim function> ::= TRIM <left paren> <trim operands> <right paren>
<trim operands> ::= [ [ <trim specification> ] [ <trim character> ] FROM ] <trim source>
<trim source> ::= <character value expression>
<trim specification> ::= LEADING | TRAILING | BOTH
<trim character> ::= <character value expression>
<character overlay function> ::=
OVERLAY <left
paren> <character
value expression>
PLACING <character
value expression>
FROM <start
position> [
FOR <string
length> ] [
USING <char
length units> ] <right
paren>
<normalize function> ::= NORMALIZE <left paren> <character value expression> <right paren>
<specific type method> ::= <user-defined type value expression> <period> SPECIFICTYPE
<blob value function> ::=
<blob
substring function>
|
<blob
trim function>
| <blob
overlay function>
<blob substring function> ::=
SUBSTRING <left
paren> <blob
value expression>
FROM <start
position> [
FOR <string
length> ] <right
paren>
<blob trim function> ::= TRIM <left paren> <blob trim operands> <right paren>
<blob trim operands> ::= [ [ <trim specification> ] [ <trim octet> ] FROM ] <blob trim source>
<blob trim source> ::= <blob value expression>
<trim octet> ::= <blob value expression>
<blob overlay function> ::=
OVERLAY <left
paren> <blob
value expression>
PLACING <blob
value expression>
FROM <start
position> [
FOR <string
length> ] <right
paren>
<start position> ::= <numeric value expression>
<string length> ::= <numeric value expression>
Specify a datetime value.
<datetime value expression> ::=
<datetime
term>
| <interval
value expression> <plus
sign> <datetime
term>
| <datetime
value expression> <plus
sign> <interval
term>
| <datetime
value expression> <minus
sign> <interval
term>
<datetime term> ::= <datetime factor>
<datetime factor> ::= <datetime primary> [ <time zone> ]
<datetime primary> ::= <value expression primary> | <datetime value function>
<time zone> ::= AT <time zone specifier>
<time zone specifier> ::= LOCAL | TIME ZONE <interval primary>
Specify a function yielding a value of type datetime.
<datetime value function> ::=
<current
date value function>
| <current
time value function>
| <current
timestamp value function>
| <current
local time value function>
| <current
local timestamp value function>
<current date value function> ::= CURRENT_DATE
<current time value function> ::= CURRENT_TIME [ <left paren> <time precision> <right paren> ]
<current local time value function> ::= LOCALTIME [ <left paren> <time precision> <right paren> ]
<current timestamp value function> ::= CURRENT_TIMESTAMP [ <left paren> <timestamp precision> <right paren> ]
<current local timestamp value function> ::= LOCALTIMESTAMP [ <left paren> <timestamp precision> <right paren> ]
Specify an interval value.
<interval value expression> ::=
<interval
term>
| <interval
value expression 1> <plus
sign> <interval
term 1>
| <interval
value expression 1> <minus
sign> <interval
term 1>
| <left
paren> <datetime
value expression> <minus
sign> <datetime
term> <right
paren> <interval
qualifier>
<interval term> ::=
<interval
factor>
| <interval
term 2> <asterisk>
<factor>
| <interval
term 2> <solidus>
<factor>
| <term>
<asterisk>
<interval
factor>
<interval factor> ::= [ <sign> ] <interval primary>
<interval primary> ::=
<value
expression primary> [ <interval
qualifier> ]
| <interval
value function>
<interval value expression 1> ::= <interval value expression>
<interval term 1> ::= <interval term>
<interval term 2> ::= <interval term>
<interval value function> ::= <interval absolute value function>
<interval absolute value function> ::= ABS <left paren> <interval value expression> <right paren>
<boolean value expression> ::=
<boolean
term>
| <boolean
value expression>
OR <boolean
term>
<boolean term> ::=
<boolean
factor>
| <boolean
term>
AND <boolean
factor>
<boolean factor> ::= [ NOT ] <boolean test>
<boolean test> ::= <boolean primary> [ IS [ NOT ] <truth value> ]
<truth value> ::= TRUE | FALSE | UNKNOWN
<boolean primary> ::= <predicate> | <boolean predicand>
<boolean predicand> ::=
<parenthesized
boolean value expression>
| <nonparenthesized
value expression primary>
<parenthesized boolean value expression> ::= <left paren> <boolean value expression> <right paren>
<array value expression> ::= <array concatenation> | <array factor>
<array concatenation> ::= <array value expression 1> <concatenation operator> <array factor>
<array value expression 1> ::= <array value expression>
<array factor> ::= <value expression primary>
<array value constructor> ::=
<array
value constructor by enumeration>
| <array
value constructor by query>
<array value constructor by enumeration> ::=
ARRAY <left
bracket or trigraph> <array
element list> <right
bracket or trigraph>
<array element list> ::= <array element> [ { <comma> <array element> }... ]
<array element> ::= <value expression>
<array value constructor by query> ::=
ARRAY <left
paren> <query
expression> [ <order
by clause> ] <right
paren>
<multiset value expression> ::=
<multiset
term>
| <multiset
value expression>
MULTISET
UNION [
ALL |
DISTINCT ] <multiset
term>
| <multiset
value expression>
MULTISET
EXCEPT [
ALL |
DISTINCT ] <multiset
term>
<multiset term> ::=
<multiset
primary>
| <multiset
term>
MULTISET
INTERSECT [
ALL |
DISTINCT ] <multiset
primary>
<multiset primary> ::= <multiset value function> | <value expression primary>
<multiset value function> ::= <multiset set function>
<multiset set function> ::= SET <left paren> <multiset value expression> <right paren>
<multiset value constructor> ::=
<multiset
value constructor by enumeration>
| <multiset
value constructor by query>
| <table
value constructor by query>
<multiset value constructor by enumeration> ::= MULTISET <left bracket or trigraph> <multiset element list> <right bracket or trigraph>
<multiset element list> ::= <multiset element> [ { <comma> <multiset element> } ]
<multiset element> ::= <value expression>
<multiset value constructor by query> ::= MULTISET <left paren> <query expression> <right paren>
<table value constructor by query> ::= TABLE <left paren> <query expression> <right paren>
Specify a value or list of values to be constructed into a row or partial row.
<row value constructor> ::=
<common
value expression>
|
<boolean
value expression>
|
<explicit
row value constructor>
<explicit row value constructor> ::=
<left
paren> <row
value constructor element> <comma>
<row
value constructor element list> <right
paren>
|
ROW <left
paren> <row
value constructor element list> <right
paren>
| <row
subquery>
<row value constructor element list> ::=
<row
value constructor element> [ { <comma>
<row
value constructor element> }... ]
<row value constructor element> ::= <value expression>
<contextually typed row value constructor> ::=
<common
value expression>
|
<boolean
value expression>
|
<contextually
typed value specification>
| <left
paren> <contextually
typed row value constructor element> <comma>
<contextually
typed row value constructor element list> <right
paren>
|
ROW <left
paren> <contextually
typed row value constructor element list> <right
paren>
<contextually typed row value constructor element list>
::=
<contextually
typed row value constructor
element>
[ { <comma>
<contextually
typed row value constructor element> }... ]
<contextually typed row value constructor element>
::=
<value
expression>
| <contextually
typed value specification>
<row value constructor predicand> ::=
<common
value expression>
|
<boolean
predicand>
| <explicit
row value constructor>
Specify a row value.
<row value expression> ::=
<row
value special case>
|
<explicit
row value constructor>
<table row value expression> ::=
<row
value special case>
|
<row
value constructor>
<contextually typed row value expression> ::=
<row
value special case>
|
<contextually
typed row value constructor>
<row value predicand> ::=
<row
value special case>
|
<row
value constructor predicand>
<row value special case> ::= <nonparenthesized value expression primary>
Specify a set of <row value expression>s to be constructed into a table.
<table value constructor> ::= VALUES <row value expression list>
<row value expression list> ::= <table row value expression> [ { <comma> <table row value expression> }... ]
<contextually typed table value constructor> ::= VALUES <contextually typed row value expression list>
<contextually typed row value expression list> ::= <contextually typed row value expression> [ { <comma> <contextually typed row value expression> }... ]
Specify a table or a grouped table.
<table expression> ::=
<from
clause>
[ <where
clause> ]
[ <group
by clause> ]
[ <having
clause> ]
[ <window
clause> ]
Specify a table derived from one or more tables.
<from clause> ::= FROM <table reference list>
<table reference list> ::= <table reference> [ { <comma> <table reference> }... ]
Reference a table.
<table reference> ::= <table primary or joined table> [ <sample clause> ]
<table primary or joined table> ::= <table primary> | <joined table>
<sample clause> ::=
TABLESAMPLE <sample
method> <left
paren> <sample
percentage> <right
paren> [ <repeatable
clause> ]
<sample method> ::= BERNOULLI | SYSTEM
<repeatable clause> ::= REPEATABLE <left paren> <repeat argument> <right paren>
<sample percentage> ::= <numeric value expression>
<repeat argument> ::= <numeric value expression>
<table primary> ::=
<table
or query name> [ [
AS ] <correlation
name> [ <left
paren> <derived
column list> <right
paren> ] ]
| <derived
table> [
AS ] <correlation
name> [ <left
paren> <derived
column list> <right
paren> ]
| <lateral
derived table> [
AS ] <correlation
name> [ <left
paren> <derived
column list> <right
paren> ]
| <collection
derived table> [
AS ] <correlation
name> [ <left
paren> <derived
column list> <right
paren> ]
| <table
function derived table> [
AS ] <correlation
name> [ <left
paren> <derived
column list> <right
paren> ]
| <only
spec> [ [
AS ] <correlation
name> [ <left
paren> <derived
column list> <right
paren> ] ]
| <left
paren> <joined
table> <right
paren>
<only spec> ::= ONLY <left paren> <table or query name> <right paren>
<lateral derived table> ::= LATERAL <table subquery>
<collection derived table> ::= UNNEST <left paren> <collection value expression> <right paren> [ WITH ORDINALITY ]
<table function derived table> ::= TABLE <left paren> <collection value expression> <right paren>
<derived table> ::= <table subquery>
<table or query name> ::= <table name> | <query name>
<derived column list> ::= <column name list>
<column name list> ::= <column name> [ { <comma> <column name> }... ]
Specify a table derived from a Cartesian product, inner or outer join, or union join.
<joined table> ::=
<cross
join>
| <qualified
join>
| <natural
join>
| <union
join>
<cross join> ::= <table reference> CROSS JOIN <table primary>
<qualified join> ::= <table reference> [ <join type> ] JOIN <table reference> <join specification>
<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>
Specify a table derived by the application of a <search condition> to the result of the preceding< from clause>.
<where clause> ::= WHERE <search condition>
Specify a grouped table derived by the application of the <group by clause> to the result of the previously specified clause.
<group by clause> ::= GROUP BY [ <set quantifier> ] <grouping element list>
<grouping element list> ::= <grouping element> [ { <comma> <grouping element> }... ]
<grouping element> ::=
<ordinary
grouping set>
| <rollup
list>
| <cube
list>
| <grouping
sets specification>
|
<empty
grouping set>
<ordinary grouping set> ::=
<grouping
column reference>
|
<left
paren> <grouping
column reference list> <right
paren>
<grouping column reference> ::= <column reference> [ <collate clause> ]
<grouping column reference list> ::= <grouping column reference> [ { <comma> <grouping column reference> }... ]
<rollup list> ::= ROLLUP <left paren> <ordinary grouping set list> <right paren>
<ordinary grouping set list> ::= <ordinary grouping set> [ { <comma> <ordinary grouping set> }... ]
<cube list> ::= CUBE <left paren> <ordinary grouping set list> <right paren>
<grouping sets specification> ::= GROUPING SETS <left paren> <grouping set list> <right paren>
<grouping set list> ::= <grouping set> [ { <comma> <grouping set> }... ]
<grouping set> ::=
<ordinary
grouping set>
| <rollup
list>
| <cube
list>
| <grouping
sets specification>
|
<empty
grouping set>
<empty grouping set> ::= <left paren> <right paren>
Specify a grouped table derived by the elimination of groups that do not satisfy a <search condition>.
<having clause> ::= HAVING <search condition>
Specify one or more window definitions.
<window clause> ::= WINDOW <window definition list>
<window definition list> ::= <window definition> [ { <comma> <window definition> }... ]
<window definition> ::= <new window name> AS <window specification>
<new window name> ::= <window name>
<window specification> ::= <left paren> <window specification details> <right paren>
<window specification details> ::=
[ <existing
window name> ] [ <window
partition clause> ] [ <window
order clause> ] [ <window
frame clause> ]
<existing window name> ::= <window name>
<window partition clause> ::= PARTITION BY <window partition column reference list>
<window partition column reference list> ::= <window partition column reference> [ { <comma> <window partition column reference> }... ]
<window partition column reference> ::= <column reference> [ <collate clause> ]
<window order clause> ::= ORDER BY <sort specification list>
<window frame clause> ::= <window frame units> <window frame extent> [ <window frame exclusion> ]
<window frame units> ::= ROWS | RANGE
<window frame extent> ::= <window frame start> | <window frame between>
<window frame start> ::= UNBOUNDED PRECEDING | <window frame preceding> | CURRENT ROW
<window frame preceding> ::= <unsigned value specification> PRECEDING
<window frame between> ::= BETWEEN <window frame bound 1> AND <window frame bound 2>
<window frame bound 1> ::= <window frame bound>
<window frame bound 2> ::= <window frame bound>
<window frame bound> ::=
<window
frame start>
|
UNBOUNDED
FOLLOWING
| <window
frame following>
<window frame following> ::= <unsigned value specification> FOLLOWING
<window frame exclusion> ::=
EXCLUDE
CURRENT
ROW
|
EXCLUDE
GROUP
|
EXCLUDE
TIES
|
EXCLUDE NO OTHERS
Specify a table derived from the result of a <table expression>.
<query specification> ::= SELECT [ <set quantifier> ] <select list> <table expression>
<select list> ::= <asterisk> | <select sublist> [ { <comma> <select sublist> }... ]
<select sublist> ::= <derived column> | <qualified asterisk>
<qualified asterisk> ::=
<asterisked
identifier chain> <period>
<asterisk>
| <all
fields reference>
<asterisked identifier chain> ::= <asterisked identifier> [ { <period> <asterisked identifier> }... ]
<asterisked identifier> ::= <identifier>
<derived column> ::= <value expression> [ <as clause> ]
<as clause> ::= [ AS ] <column name>
<all fields reference> ::= <value expression primary> <period> <asterisk> [ AS <left paren> <all fields column name list> <right paren> ]
<all fields column name list> ::= <column name list>
Specify a table.
<query expression> ::= [ <with clause> ] <query expression body>
<with clause> ::= WITH [ RECURSIVE ] <with list>
<with list> ::= <with list element> [ { <comma> <with list element> }... ]
<with list element> ::=
<query
name> [ <left
paren> <with
column list> <right
paren> ]
AS <left
paren> <query
expression> <right
paren> [ <search
or cycle clause> ]
<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>
<query term> ::= <non-join query term> | <joined table>
<non-join query term> ::=
<non-join
query primary>
| <query
term>
INTERSECT [
ALL |
DISTINCT ] [ <corresponding
spec> ] <query
primary>
<query primary> ::= <non-join query primary> | <joined table>
<non-join query primary> ::= <simple table> | <left paren> <non-join query expression> <right paren>
<simple table> ::=
<query
specification>
| <table
value constructor>
|
<explicit
table>
<explicit table> ::= TABLE <table or query name>
<corresponding spec> ::= CORRESPONDING [ BY <left paren> <corresponding column list> <right paren> ]
<corresponding column list> ::= <column name list>
Specify the generation of ordering and cycle detection information in the result of recursive query expressions.
<search or cycle clause> ::=
<search
clause>
| <cycle
clause>
| <search
clause> <cycle
clause>
<search clause> ::= SEARCH <recursive search order> SET <sequence column>
<recursive search order> ::=
DEPTH
FIRST
BY <sort
specification list>
|
BREADTH
FIRST
BY <sort
specification list>
<sequence column> ::= <column name>
<cycle clause> ::=
CYCLE <cycle
column list>
SET <cycle
mark column>
TO <cycle
mark value>
DEFAULT <non-cycle
mark value>
USING <path
column>
<cycle column list> ::= <cycle column> [ { <comma> <cycle column> }... ]
<cycle column> ::= <column name>
<cycle mark column> ::= <column name>
<path column> ::= <column name>
<cycle mark value> ::= <value expression>
<non-cycle mark value> ::= <value expression>
Specify a scalar value, a row, or a table derived from a <query expression>.
<scalar subquery> ::= <subquery>
<table subquery> ::= <subquery>
<subquery> ::= <left paren> <query expression> <right paren>
Specify a condition that can be evaluated to give a boolean value.
<predicate> ::=
<comparison
predicate>
| <between
predicate>
| <in
predicate>
| <like
predicate>
| <similar
predicate>
| <null
predicate>
| <quantified
comparison predicate>
| <exists
predicate>
| <unique
predicate>
| <normalized
predicate>
| <match
predicate>
| <overlaps
predicate>
| <distinct
predicate>
| <member
predicate>
| <submultiset
predicate>
| <set
predicate>
| <type
predicate>
Specify a comparison of two row values.
<comparison predicate> ::= <row value predicand> <comparison predicate part 2>
<comparison predicate part 2> ::= <comp op> <row value predicand>
<comp op> ::=
<equals
operator>
| <not
equals operator>
| <less
than operator>
| <greater
than operator>
| <less
than or equals operator>
| <greater
than or equals operator>
Specify a range comparison.
<between predicate> ::= <row value predicand> <between predicate part 2>
<between predicate part 2> ::= [ NOT ] BETWEEN [ ASYMMETRIC | SYMMETRIC ] <row value predicand> AND <row value predicand>
Specify a quantified comparison.
<in predicate> ::= <row value predicand> <in predicate part 2>
<in predicate part 2> ::= [ NOT ] IN <in predicate value>
<in predicate value> ::=
<table
subquery>
| <left
paren> <in
value list> <right
paren>
<in value list> ::= <row value expression> [ { <comma> <row value expression> }... ]
Specify a pattern-match comparison.
<like predicate> ::= <character like predicate> | <octet like predicate>
<character like predicate> ::= <row value predicand> <character like predicate part 2>
<character like predicate part 2> ::= [ NOT ] LIKE <character pattern> [ ESCAPE <escape character> ]
<character pattern> ::= <character value expression>
<escape character> ::= <character value expression>
<octet like predicate> ::= <row value predicand> <octet like predicate part 2>
<octet like predicate part 2> ::= [ NOT ] LIKE <octet pattern> [ ESCAPE <escape octet> ]
<octet pattern> ::= <blob value expression>
<escape octet> ::= <blob value expression>
Specify a character string similarity by means of a regular expression.
<similar predicate> ::= <row value predicand> <similar predicate part 2>
<similar predicate part 2> ::= [ NOT ] SIMILAR TO <similar pattern> [ ESCAPE <escape character> ]
<similar pattern> ::= <character value expression>
<regular expression> ::=
<regular
term>
| <regular
expression> <vertical
bar> <regular
term>
<regular term> ::=
<regular
factor>
| <regular
term> <regular
factor>
<regular factor> ::=
<regular
primary>
| <regular
primary> <asterisk>
| <regular
primary> <plus
sign>
| <regular
primary> <question
mark>
| <regular
primary> <repeat
factor>
<repeat factor> ::= <left brace> <low value> [ <upper limit> ] <right brace>
<upper limit> ::= <comma> [ <high value> ]
<low value> ::= <unsigned integer>
<high value> ::= <unsigned integer>
<regular primary> ::=
<character
specifier>
| <percent>
| <regular
character set>
| <left
paren> <regular
expression> <right
paren>
<character specifier> ::= <non-escaped character> | <escaped character>
<non-escaped character> ::= !! See the Syntax Rules.
<escaped character> ::= !! See the Syntax Rules.
<regular character set> ::=
<underscore>
| <left
bracket> <character
enumeration> ... <right
bracket>
| <left
bracket> <circumflex>
<character
enumeration> ... <right
bracket>
| <left
bracket> <character
enumeration include> ... <circumflex>
<character
enumeration exclude> ... <right
bracket>
<character enumeration include> ::= <character enumeration>
<character enumeration exclude> ::= <character enumeration>
<character enumeration> ::=
<character
specifier>
| <character
specifier> <minus
sign> <character
specifier>
| <left
bracket> <colon>
<regular
character set identifier> <colon>
<right
bracket>
<regular character set identifier> ::= <identifier>
Specify a test for a null value.
<null predicate> ::= <row value predicand> <null predicate part 2>
<null predicate part 2> ::= IS [ NOT ] NULL
Specify a quantified comparison.
<quantified comparison predicate> ::= <row value predicand> <quantified comparison predicate part 2>
<quantified comparison predicate part 2> ::= <comp op> <quantifier> <table subquery>
<quantifier> ::= <all> | <some>
Specify a test for a non-empty set.
<exists predicate> ::= EXISTS <table subquery>
Specify a test for the absence of duplicate rows
<unique predicate> ::= UNIQUE <table subquery>
Determine whether a character string value is normalized.
<normalized predicate> ::= <string value expression> IS [ NOT ] NORMALIZED
Specify a test for matching rows.
<match predicate> ::= <row value predicand> <match predicate part 2>
<match predicate part 2> ::= MATCH [ UNIQUE ] [ SIMPLE | PARTIAL | FULL ] <table subquery>
Specify a test for an overlap between two datetime periods.
<overlaps predicate> ::= <overlaps predicate part 1> <overlaps predicate part 2>
<overlaps predicate part 1> ::= <row value predicand 1>
<overlaps predicate part 2> ::= OVERLAPS <row value predicand 2>
<row value predicand 1> ::= <row value predicand>
<row value predicand 2> ::= <row value predicand>
Specify a test of whether two row values are distinct
<distinct predicate> ::= <row value predicand 3> <distinct predicate part 2>
<distinct predicate part 2> ::= IS DISTINCT FROM <row value predicand 4>
<row value predicand 3> ::= <row value predicand>
<row value predicand 4> ::= <row value predicand>
Specify a test of whether a value is a member of a multiset.
<member predicate> ::= <row value predicand> <member predicate part 2>
<member predicate part 2> ::= [ NOT ] MEMBER [ OF ] <multiset value expression>
Specify a test of whether a multiset is a submultiset of another multiset.
<submultiset predicate> ::= <row value predicand> <submultiset predicate part 2>
<submultiset predicate part 2> ::= [ NOT ] SUBMULTISET [ OF ] <multiset value expression>
Specify a test of whether a multiset is a set (that is, does not contain any duplicates).
<set predicate> ::= <row value predicand> <set predicate part 2>
<set predicate part 2> ::= IS [ NOT ] A SET
Specify a type test.
<type predicate> ::= <row value predicand> <type predicate part 2>
<type predicate part 2> ::= IS [ NOT ] OF <left paren> <type list> <right paren>
<type list> ::= <user-defined type specification> [ { <comma> <user-defined type specification> }... ]
<user-defined type specification> ::=
<inclusive
user-defined type specification>
| <exclusive
user-defined type specification>
<inclusive user-defined type specification> ::= <path-resolved user-defined type name>
<exclusive user-defined type specification> ::= ONLY <path-resolved user-defined type name>
Specify a condition that is True , False , or Unknown , depending on the value of a <boolean value expression>.
<search condition> ::= <boolean value expression>
Specify the precision of an interval data type.
<interval qualifier> ::=
<start
field>
TO <end
field>
| <single
datetime field>
<start field> ::= <non-second primary datetime field> [ <left paren> <interval leading field precision> <right paren> ]
<end field> ::=
<non-second
primary datetime field>
|
SECOND [ <left
paren> <interval
fractional seconds precision> <right
paren> ]
<single datetime field> ::=
<non-second
primary datetime field> [ <left
paren> <interval
leading field precision> <right
paren> ]
|
SECOND [ <left
paren> <interval
leading field precision> [ <comma>
<interval
fractional seconds precision> ] <right
paren> ]
<primary datetime field> ::=
<non-second
primary datetime field>
|
SECOND
<non-second primary datetime field> ::= YEAR | MONTH | DAY | HOUR | MINUTE
<interval fractional seconds precision> ::= <unsigned integer>
<interval leading field precision> ::= <unsigned integer>
Specify a standard programming language.
<language clause> ::= LANGUAGE <language name>
<language name> ::= ADA | C | COBOL | FORTRAN | MUMPS | PASCAL | PLI | SQL
Table 14 -- Standard programming languages
Language keyword | Relevant standard |
---|---|
ADA | ISO/IEC 8652 |
C | ISO/IEC 9899 |
COBOL | ISO 1989 |
FORTRAN | ISO 1539 |
MUMPS | ISO/IEC 11756 |
PASCAL | ISO/IEC 7185 and ISO/IEC 10206 |
PLI | ISO 6160 |
SQL | ISO/IEC 9075 |
Specify an order for searching for an SQL-invoked routine.
<path specification> ::= PATH <schema name list>
<schema name list> ::= <schema name> [ { <comma> <schema name> }... ]
Invoke an SQL-invoked routine.
<routine invocation> ::= <routine name> <SQL argument list>
<routine name> ::= [ <schema name> <period> ] <qualified identifier>
<SQL argument list> ::= <left paren> [ <SQL argument> [ { <comma> <SQL argument> }... ] ] <right paren>
<SQL argument> ::=
<value
expression>
| <generalized
expression>
| <target
specification>
<generalized expression> ::= <value expression> AS <path-resolved user-defined type name>
Identify a character set.
<character set specification> ::=
<standard
character set name>
|
<implementation-defined
character set name>
|
<user-defined
character set name>
<standard character set name> ::= <character set name>
<implementation-defined character set name> ::= <character set name>
<user-defined character set name> ::= <character set name>
Specify an SQL-invoked routine.
<specific routine designator> ::=
SPECIFIC <routine
type> <specific
name>
| <routine
type> <member
name>
[
FOR <schema-resolved
user-defined type name> ]
<routine type> ::=
ROUTINE
|
FUNCTION
|
PROCEDURE
| [
INSTANCE |
STATIC |
CONSTRUCTOR ]
METHOD
<member name> ::= <member name alternatives> [ <data type list> ]
<member name alternatives> ::= <schema qualified routine name> | <method name>
<data type list> ::= <left paren> [ <data type> [ { <comma> <data type> }... ] ] <right paren>
Specify a default collating sequence.
<collate clause> ::= COLLATE <collation name>
Specify the name of a constraint and its characteristics.
<constraint name definition> ::= CONSTRAINT <constraint name>
<constraint characteristics> ::=
<constraint
check time> [ [
NOT ]
DEFERRABLE ]
| [ NOT ]
DEFERRABLE [ <constraint
check time> ]
<constraint check time> ::= INITIALLY DEFERRED | INITIALLY IMMEDIATE
Specify a value computed from a collection of rows.
<aggregate function> ::=
COUNT <left
paren> <asterisk>
<right
paren> [ <filter
clause> ]
| <general
set function> [ <filter
clause> ]
| <binary
set function> [ <filter
clause> ]
| <ordered
set function> [ <filter
clause> ]
<general set function> ::= <set function type> <left paren> [ <set quantifier> ] <value expression> <right paren>
<set function type> ::= <computational operation>
<computational operation> ::=
AVG | MAX |
MIN |
SUM
|
EVERY |
ANY |
SOME
|
COUNT
|
STDDEV_POP |
STDDEV_SAMP |
VAR_SAMP |
VAR_POP
|
COLLECT |
FUSION |
INTERSECTION
<set quantifier> ::= DISTINCT | ALL
<filter clause> ::= FILTER <left paren> WHERE <search condition> <right paren>
<binary set function> ::= <binary set function type> <left paren> <dependent variable expression> <comma> <independent variable expression> <right paren>
<binary set function type> ::=
COVAR_POP |
COVAR_SAMP |
CORR |
REGR_SLOPE
|
REGR_INTERCEPT |
REGR_COUNT |
REGR_R2 |
REGR_AVGX |
REGR_AVGY
|
REGR_SXX |
REGR_SYY |
REGR_SXY
<dependent variable expression> ::= <numeric value expression>
<independent variable expression> ::= <numeric value expression>
<ordered set function> ::= <hypothetical set function> | <inverse distribution function>
<hypothetical set function> ::= <rank function type> <left paren> <hypothetical set function value expression list> <right paren> <within group specification>
<within group specification> ::= WITHIN GROUP <left paren> ORDER BY <sort specification list> <right paren>
<hypothetical set function value expression list> ::= <value expression> [ { <comma> <value expression> }... ]
<inverse distribution function> ::= <inverse distribution function type> <left paren> <inverse distribution function argument> <right paren> <within group specification>
<inverse distribution function argument> ::= <numeric value expression>
<inverse distribution function type> ::= PERCENTILE_CONT | PERCENTILE_DISC
Specify a sort order.
<sort specification list> ::= <sort specification> [ { <comma> <sort specification> }... ]
<sort specification> ::= <sort key> [ <ordering specification> ] [ <null ordering> ]
<sort key> ::= <value expression>
<ordering specification> ::= ASC | DESC
<null ordering> ::= NULLS FIRST | NULLS LAST
Define a schema.
<schema definition> ::= CREATE SCHEMA <schema name clause> [ <schema character set or path> ] [ <schema element> ... ]
<schema character set or path> ::=
<schema
character set specification>
| <schema
path specification>
|
<schema
character set specification> <schema
path specification>
|
<schema
path specification> <schema
character set specification>
<schema name clause> ::=
<schema
name>
|
AUTHORIZATION <schema
authorization identifier>
| <schema
name>
AUTHORIZATION <schema
authorization identifier>
<schema authorization identifier> ::= <authorization identifier>
<schema character set specification> ::= DEFAULT CHARACTER SET <character set specification>
<schema path specification> ::= <path specification>
<schema element> ::=
<table
definition>
| <view
definition>
| <domain
definition>
| <character
set definition>
| <collation
definition>
| <transliteration
definition>
| <assertion
definition>
| <trigger
definition>
| <user-defined
type definition>
| <user-defined
cast definition>
| <user-defined
ordering definition>
| <transform
definition>
| <schema
routine>
| <sequence
generator definition>
| <grant
statement>
| <role
definition>
Destroy a schema.
<drop schema statement> ::= DROP SCHEMA <schema name> <drop behavior>
<drop behavior> ::= CASCADE | RESTRICT
Define a persistent base table, a created local temporary table, or a global temporary table.
<table definition> ::=
CREATE [ <table
scope> ]
TABLE <table
name> <table
contents source>
[
ON COMMIT
<table
commit action>
ROWS ]
<table contents source> ::=
<table
element list>
| OF <path-resolved
user-defined type name> [ <subtable
clause> ] [ <table
element list> ]
| <as
subquery clause>
<table scope> ::= <global or local> TEMPORARY
<global or local> ::= GLOBAL | LOCAL
<table commit action> ::= PRESERVE | DELETE
<table element list> ::= <left paren> <table element> [ { <comma> <table element> }... ] <right paren>
<table element> ::=
<column
definition>
| <table
constraint definition>
| <like
clause>
| <self-referencing
column specification>
| <column
options>
<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> ::= <identity option> | <column default option>
<identity option> ::= INCLUDING IDENTITY | EXCLUDING IDENTITY
<column default option> ::= INCLUDING DEFAULTS | EXCLUDING DEFAULTS
<as subquery clause> ::= [ <left paren> <column name list> <right paren> ] AS <subquery> <with or without data>
<with or without data> ::= WITH NO DATA | WITH DATA
Define a column of a base table.
<column definition> ::=
<column
name> [ <data
type> | <domain
name> ] [ <reference
scope check> ]
[ <default
clause> | <identity
column specification> | <generation
clause> ]
[ <column
constraint definition> ... ] [ <collate
clause> ]
<column constraint definition> ::= [ <constraint name definition> ] <column constraint> [ <constraint characteristics> ]
<column constraint> ::=
NOT
NULL
| <unique
specification>
| <references
specification>
| <check
constraint definition>
<reference scope check> ::= REFERENCES ARE [ NOT ] CHECKED [ ON DELETE <reference scope check action> ]
<reference scope check action> ::= <referential action>
<identity column specification> ::=
GENERATED {
ALWAYS |
BY DEFAULT
} AS
IDENTITY
[ <left
paren> <common
sequence generator options> <right
paren> ]
<generation clause> ::= <generation rule> AS <generation expression>
<generation rule> ::= GENERATED ALWAYS
<generation expression> ::= <left paren> <value expression> <right paren>
Specify the default for a column, domain, or attribute.
<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>
Specify an integrity constraint.
<table constraint definition> ::= [ <constraint name definition> ] <table constraint> [ <constraint characteristics> ]
<table constraint> ::=
<unique
constraint definition>
| <referential
constraint definition>
| <check
constraint definition>
Specify a uniqueness constraint for a table.
<unique constraint definition> ::=
<unique
specification> <left
paren> <unique
column list> <right
paren>
|
UNIQUE (
VALUE )
<unique specification> ::= UNIQUE | PRIMARY KEY
<unique column list> ::= <column name list>
Specify a referential constraint.
<referential constraint definition> ::= FOREIGN KEY <left paren> <referencing columns> <right paren> <references specification>
<references specification> ::= REFERENCES <referenced table and columns> [ MATCH <match type> ] [ <referential triggered action> ]
<match type> ::= FULL | PARTIAL | SIMPLE
<referencing columns> ::= <reference column list>
<referenced table and columns> ::= <table name> [ <left paren> <reference column list> <right paren> ]
<reference column list> ::= <column name list>
<referential triggered action> ::= <update rule> [ <delete rule> ] | <delete rule> [ <update rule> ]
<update rule> ::= ON UPDATE <referential action>
<delete rule> ::= ON DELETE <referential action>
<referential action> ::= CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION
Specify a condition for the SQL-data.
<check constraint definition> ::= CHECK <left paren> <search condition> <right paren>
Change the definition of a table.
<alter table statement> ::= ALTER TABLE <table name> <alter table action>
<alter table action> ::=
<add
column definition>
|
<alter
column definition>
|
<drop
column definition>
|
<add
table constraint definition>
| <drop
table constraint definition>
Add a column to a table.
<add column definition> ::= ADD [ COLUMN ] <column definition>
Change a column and its definition.
<alter column definition> ::= ALTER [ COLUMN ] <column name> <alter column action>
<alter column action> ::=
<set
column default clause>
| <drop
column default clause>
| <add
column scope clause>
| <drop
column scope clause>
| <alter
identity column specification>
Set the default clause for a column.
<set column default clause> ::= SET <default clause>
Drop the default clause from a column.
<drop column default clause> ::= DROP DEFAULT
Add a non-empty scope for an existing column of data type REF in a base table.
<add column scope clause> ::= ADD <scope clause>
Drop the scope from an existing column of data type REF in a base table.
<drop column scope clause> ::= DROP SCOPE <drop behavior>
Change the options specified for an identity column.
<alter identity column specification> ::= <alter identity column option> ...
<alter identity column option> ::=
<alter
sequence generator restart option>
|
SET <basic
sequence generator option>
Destroy a column of a base table.
<drop column definition> ::= DROP [ COLUMN ] <column name> <drop behavior>
Add a constraint to a table.
<add table constraint definition> ::= ADD <table constraint definition>
Destroy a constraint on a table.
<drop table constraint definition> ::= DROP CONSTRAINT <constraint name> <drop behavior>
Destroy a table.
<drop table statement> ::= DROP TABLE <table name> <drop behavior>
Define a viewed table.
<view definition> ::=
CREATE [
RECURSIVE ]
VIEW <table
name> <view
specification>
AS <query
expression>
[ WITH [
<levels
clause> ]
CHECK
OPTION ]
<view specification> ::= <regular view specification> | <referenceable view specification>
<regular view specification> ::= [ <left paren> <view column list> <right paren> ]
<referenceable view specification> ::= OF <path-resolved user-defined type name> [ <subview clause> ] [ <view element list> ]
<subview clause> ::= UNDER <table name>
<view element list> ::= <left paren> <view element> [ { <comma> <view element> }... ] <right paren>
<view element> ::= <self-referencing column specification> | <view column option>
<view column option> ::= <column name> WITH OPTIONS <scope clause>
<levels clause> ::= CASCADED | LOCAL
<view column list> ::= <column name list>
Destroy a view.
<drop view statement> ::= DROP VIEW <table name> <drop behavior>
Define a domain.
<domain definition> ::=
CREATE
DOMAIN <domain
name> [
AS ] <data
type>
[ <default
clause> ] [ <domain
constraint> ... ] [ <collate
clause> ]
<domain constraint> ::= [ <constraint name definition> ] <check constraint definition> [ <constraint characteristics> ]
Change a domain and its definition.
<alter domain statement> ::= ALTER DOMAIN <domain name> <alter domain action>
<alter domain action> ::=
<set
domain default clause>
| <drop
domain default clause>
| <add
domain constraint definition>
| <drop
domain constraint definition>
Set the default value in a domain.
<set domain default clause> ::= SET <default clause>
Remove the default clause of a domain.
<drop domain default clause> ::= DROP DEFAULT
Add a constraint to a domain.
<add domain constraint definition> ::= ADD <domain constraint>
Destroy a constraint on a domain.
<drop domain constraint definition> ::= DROP CONSTRAINT <constraint name>
Destroy a domain.
<drop domain statement> ::= DROP DOMAIN <domain name> <drop behavior>
Define a character set.
<character set definition> ::=
CREATE
CHARACTER
SET <character
set name> [
AS ] <character
set source> [ <collate
clause> ]
<character set source> ::= GET <character set specification>
Destroy a character set.
<drop character set statement> ::= DROP CHARACTER SET <character set name>
Define a collating sequence.
<collation definition> ::=
CREATE
COLLATION <collation
name>
FOR <character
set specification>
FROM <existing
collation name> [ <pad
characteristic> ]
<existing collation name> ::= <collation name>
<pad characteristic> ::= NO PAD | PAD SPACE
Destroy a collating sequence.
<drop collation statement> ::= DROP COLLATION <collation name> <drop behavior>
Define a character transliteration.
<transliteration definition> ::=
CREATE
TRANSLATION <transliteration
name>
FOR <source
character set
specification>
TO <target
character set specification>
FROM <transliteration
source>
<source character set specification> ::= <character set specification>
<target character set specification> ::= <character set specification>
<transliteration source> ::= <existing transliteration name> | <transliteration routine>
<existing transliteration name> ::= <transliteration name>
<transliteration routine> ::= <specific routine designator>
Destroy a character transliteration.
<drop transliteration statement> ::= DROP TRANSLATION <transliteration name>
Specify an integrity constraint.
<assertion definition> ::=
CREATE
ASSERTION <constraint
name>
CHECK <left
paren> <search
condition> <right
paren> [ <constraint
characteristics> ]
Destroy an assertion.
<drop assertion statement> ::= DROP ASSERTION <constraint name>
Define triggered SQL-statements.
<trigger definition> ::=
CREATE
TRIGGER <trigger
name> <trigger
action time> <trigger
event>
ON <table
name> [
REFERENCING <old
or new values alias list>
]
<triggered
action>
<trigger action time> ::= BEFORE | AFTER
<trigger event> ::= INSERT | DELETE | UPDATE [ OF <trigger column list> ]
<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>
Destroy a trigger.
<drop trigger statement> ::= DROP TRIGGER <trigger name>
Define a user-defined type.
<user-defined type definition> ::= CREATE TYPE <user-defined type body>
<user-defined type body> ::=
<schema-resolved
user-defined type name> [ <subtype
clause> ]
[ AS <representation>
] [ <user-defined
type option list> ] [ <method
specification list> ]
<user-defined type option list> ::= <user-defined type option> [ <user-defined type option> ... ]
<user-defined type option> ::=
<instantiable
clause>
| <finality>
| <reference
type specification>
|
<ref
cast option>
| <cast
option>
<subtype clause> ::=
UNDER <supertype
name>
<supertype name> ::=
<path-resolved
user-defined type name>
<representation> ::= <predefined type> | <member list>
<member list> ::= <left paren> <member> [ { <comma> <member> }... ] <right paren>
<member> ::= <attribute definition>
<instantiable clause> ::= INSTANTIABLE | NOT INSTANTIABLE
<finality> ::= FINAL | NOT FINAL
<reference type specification> ::=
<user-defined
representation>
| <derived
representation>
| <system-generated
representation>
<user-defined representation> ::= REF USING <predefined type>
<derived representation> ::= REF FROM <list of attributes>
<system-generated representation> ::= REF IS SYSTEM GENERATED
<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>
<list of attributes> ::= <left paren> <attribute name> [ { <comma> <attribute name> }...] <right paren>
<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> }... ]
<method specification> ::= <original method specification> | <overriding method specification>
<original method specification> ::=
<partial
method specification> [
SELF
AS
RESULT ] [
SELF
AS LOCATOR
] [ <method
characteristics> ]
<overriding method specification> ::= OVERRIDING <partial method specification>
<partial method specification> ::=
[
INSTANCE |
STATIC |
CONSTRUCTOR ]
METHOD <method
name> <SQL
parameter declaration
list>
<returns
clause> [
SPECIFIC <specific
method name> ]
<specific method name> ::= [ <schema name> <period> ]<qualified identifier >
<method characteristics> ::= <method characteristic> ...
<method characteristic> ::=
<language
clause>
| <parameter
style clause>
| <deterministic
characteristic>
| <SQL-data
access indication>
|
<null-call
clause>
Define an attribute of a structured type.
<attribute definition> ::=
<attribute
name> <data
type> [ <reference
scope check> ] [ <attribute
default> ] [ <collate
clause> ]
<attribute default> ::= <default clause>
Change the definition of a user-defined type.
<alter type statement> ::= ALTER TYPE <schema-resolved user-defined type name> <alter type action>
<alter type action> ::=
<add
attribute definition>
| <drop
attribute definition>
| <add
original method specification>
| <add
overriding method specification>
| <drop
method specification>
Add an attribute to a user-defined type.
<add attribute definition> ::= ADD ATTRIBUTE <attribute definition>
Destroy an attribute of a user-defined type.
<drop attribute definition> ::= DROP ATTRIBUTE <attribute name> RESTRICT
Add an original method specification to a user-defined type.
<add original method specification> ::= ADD <original method specification>
Add an overriding method specification to a user-defined type.
<add overriding method specification> ::= ADD <overriding method specification>
Remove a method specification from a user-defined type.
<drop method specification> ::= DROP <specific method specification designator> RESTRICT
<specific method specification designator> ::= [ INSTANCE | STATIC | CONSTRUCTOR ] METHOD <method name> <data type list>
Destroy a user-defined type.
<drop data type statement> ::= DROP TYPE <schema-resolved user-defined type name> <drop behavior>
Define an SQL-invoked routine.
<SQL-invoked routine> ::= <schema routine>
<schema routine> ::= <schema procedure> | <schema function>
<schema procedure> ::= CREATE <SQL-invoked procedure>
<schema function> ::= CREATE <SQL-invoked function>
<SQL-invoked procedure> ::=
PROCEDURE <schema
qualified routine name> <SQL
parameter declaration list> <routine
characteristics> <routine
body>
<SQL-invoked function> ::=
{ <function
specification> | <method
specification designator> } <routine
body>
<SQL parameter declaration list> ::=
<left
paren> [ <SQL
parameter declaration> [ { <comma>
<SQL
parameter declaration> }... ] ] <right
paren>
<SQL parameter declaration> ::= [ <parameter mode> ] [ <SQL parameter name> ] <parameter type> [ RESULT ]
<parameter mode> ::= IN | OUT | INOUT
<parameter type> ::= <data type> [ <locator indication> ]
<locator indication> ::= AS LOCATOR
<function specification> ::=
FUNCTION <schema
qualified routine
name>
<SQL
parameter declaration list> <returns
clause> <routine
characteristics> [ <dispatch
clause> ]
<method specification designator> ::=
SPECIFIC
METHOD <specific
method name>
| [ INSTANCE
|
STATIC |
CONSTRUCTOR ]
METHOD <method
name> <SQL
parameter declaration
list>
[ <returns
clause> ]
FOR <schema-resolved
user-defined type name>
<routine characteristics> ::= [ <routine characteristic> ... ]
<routine characteristic> ::=
<language
clause>
| <parameter
style clause>
| SPECIFIC
<specific
name>
| <deterministic
characteristic>
| <SQL-data
access indication>
|
<null-call
clause>
| <dynamic
result sets characteristic>
| <savepoint
level indication>
<savepoint level indication> ::= NEW SAVEPOINT LEVEL | OLD SAVEPOINT LEVEL
<dynamic result sets characteristic> ::= DYNAMIC RESULT SETS <maximum dynamic result sets>
<parameter style clause> ::= PARAMETER STYLE <parameter style>
<dispatch clause> ::= STATIC DISPATCH
<returns clause> ::= RETURNS <returns type>
<returns type> ::=
<returns
data type> [ <result
cast> ]
| <returns
table type>
<returns table type> ::= TABLE <table function column list>
<table function column list> ::=
<left
paren>
<table
function column list element> [ { <comma>
<table
function column list element> }...
]
<right
paren>
<table function column list element> ::= <column name> <data type>
<result cast> ::= CAST FROM <result cast from type>
<result cast from type> ::= <data type> [ <locator indication> ]
<returns data type> ::= <data type> [ <locator indication> ]
<routine body> ::=
<SQL
routine spec>
| <external
body reference>
<SQL routine spec> ::= [ <rights clause> ] <SQL routine body>
<rights clause> ::= SQL SECURITY INVOKER | SQL SECURITY DEFINER
<SQL routine body> ::= <SQL procedure statement>
<external body reference> ::=
EXTERNAL [
NAME <external
routine name> ] [ <parameter
style clause> ]
[
<transform
group specification> ] [ <external
security clause> ]
<external security clause> ::=
EXTERNAL
SECURITY
DEFINER
|
EXTERNAL
SECURITY
INVOKER
|
EXTERNAL
SECURITY
IMPLEMENTATION
DEFINED
<parameter style> ::= SQL | GENERAL
<deterministic characteristic> ::= DETERMINISTIC | NOT DETERMINISTIC
<SQL-data access indication> ::=
NO
SQL
|
CONTAINS
SQL
|
READS
SQL
DATA
|
MODIFIES
SQL
DATA
<null-call clause> ::=
RETURNS
NULL
ON
NULL INPUT
|
CALLED
ON
NULL INPUT
<maximum dynamic result sets> ::= <unsigned integer>
<transform group specification> ::= TRANSFORM GROUP { <single group specification> | <multiple group specification> }
<single group specification> ::= <group name>
<multiple group specification> ::= <group specification> [ { <comma> <group specification> }... ]
<group specification> ::= <group name> FOR TYPE <path-resolved user-defined type name>
Alter a characteristic of an SQL-invoked routine.
<alter routine statement> ::= ALTER <specific routine designator> <alter routine characteristics> <alter routine behavior>
<alter routine characteristics> ::= <alter routine characteristic> ...
<alter routine characteristic> ::=
<language
clause>
| <parameter
style clause>
| <SQL-data
access indication>
|
<null-call
clause>
| <dynamic
result sets characteristic>
|
NAME <external
routine name>
<alter routine behavior> ::= RESTRICT
Destroy an SQL-invoked routine.
<drop routine statement> ::= DROP <specific routine designator> <drop behavior>
Define a user-defined cast.
<user-defined cast definition> ::=
CREATE
CAST <left
paren> <source
data type>
AS <target
data type> <right
paren>
WITH <cast
function> [
AS
ASSIGNMENT ]
<cast function> ::= <specific routine designator>
<source data type> ::= <data type>
<target data type> ::= <data type>
Destroy a user-defined cast.
<drop user-defined cast statement> ::=
DROP
CAST <left
paren> <source
data type>
AS <target
data type> <right
paren> <drop
behavior>
Define a user-defined ordering for a user-defined type.
<user-defined ordering definition> ::=
CREATE
ORDERING
FOR <schema-resolved
user-defined type name> <ordering
form>
<ordering form> ::= <equals ordering form> | <full ordering form>
<equals ordering form> ::= EQUALS ONLY BY <ordering category>
<full ordering form> ::= ORDER FULL BY <ordering category>
<ordering category> ::= <relative category> | <map category> | <state category>
<relative category> ::= RELATIVE WITH <relative function specification>
<map category> ::= MAP WITH <map function specification>
<state category> ::= STATE [ <specific name> ]
<relative function specification> ::= <specific routine designator>
<map function specification> ::= <specific routine designator>
Destroy a user-defined ordering method.
<drop user-defined ordering statement> ::=
DROP
ORDERING
FOR <schema-resolved
user-defined type name> <drop
behavior>
Define one or more transform functions for a user-defined type.
<transform definition> ::= CREATE { TRANSFORM | TRANSFORMS } FOR <schema-resolved user-defined type name> <transform group> ...
<transform group> ::= <group name> <left paren> <transform element list> <right paren>
<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>
Change the definition of one or more transform groups.
<alter transform statement> ::=
ALTER {
TRANSFORM |
TRANSFORMS }
FOR <schema-resolved
user-defined type name> <alter
group> ...
<alter group> ::= <group name> <left paren> <alter transform action list> <right paren>
<alter transform action list> ::= <alter transform action> [ { <comma> <alter transform action> }... ]
<alter transform action> ::= <add transform element list> | <drop transform element list>
Add a transform element (<to sql> and/or <from sql>) to an existing transform group.
<add transform element list> ::= ADD <left paren> <transform element list> <right paren>
Remove a transform element (<to sql> and/or <from sql>) from a transform group.
<drop transform element list> ::= DROP <left paren> <transform kind> [ <comma> <transform kind> ] <drop behavior> <right paren>
<transform kind> ::= TO SQL | FROM SQL
Remove one or more transform functions associated with a transform.
<drop transform statement> ::=
DROP {
TRANSFORM |
TRANSFORMS } <transforms
to be dropped>
FOR <schema-resolved
user-defined type name> <drop
behavior>
<transforms to be dropped> ::= ALL | <transform group element>
<transform group element> ::= <group name>
Define an external sequence generator.
<sequence generator definition> ::= CREATE SEQUENCE <sequence generator name> [ <sequence generator options> ]
<sequence generator options> ::= <sequence generator option> ...
<sequence generator option> ::= <sequence generator data type option> | <common sequence generator options>
<common sequence generator options> ::= <common sequence generator option> ...
<common sequence generator option> ::= <sequence generator start with option> | <basic sequence generator option>
<basic sequence generator option> ::=
<sequence
generator increment by option>
| <sequence
generator maxvalue option>
| <sequence
generator minvalue option>
| <sequence
generator cycle option>
<sequence generator data type option> ::= AS <data type>
<sequence generator start with option> ::= START WITH <sequence generator start value>
<sequence generator start value> ::= <signed numeric literal>
<sequence generator increment by option> ::= INCREMENT BY <sequence generator increment>
<sequence generator increment> ::= <signed numeric literal>
<sequence generator maxvalue option> ::=
MAXVALUE <sequence
generator max value>
|
NO
MAXVALUE
<sequence generator max value> ::= <signed numeric literal>
<sequence generator minvalue option> ::= MINVALUE <sequence generator min value> | NO MINVALUE
<sequence generator min value> ::= <signed numeric literal>
<sequence generator cycle option> ::= CYCLE | NO CYCLE
Change the definition of an external sequence generator.
<alter sequence generator statement> ::=
ALTER
SEQUENCE <sequence
generator name> <alter
sequence generator options>
<alter sequence generator options> ::= <alter sequence generator option> ...
<alter sequence generator option> ::=
<alter
sequence generator restart option>
| <basic
sequence generator option>
<alter sequence generator restart option> ::= RESTART WITH <sequence generator restart value>
<sequence generator restart value> ::= <signed numeric literal>
Destroy an external sequence generator.
<drop sequence generator statement> ::= DROP SEQUENCE <sequence generator name> <drop behavior>
Define privileges and role authorizations.
<grant statement> ::= <grant privilege statement> | <grant role statement>
Define privileges.
<grant privilege statement> ::=
GRANT <privileges>
TO <grantee>
[ { <comma>
<grantee>
}... ]
[
WITH
HIERARCHY
OPTION ] [
WITH GRANT
OPTION
] [
GRANTED BY <grantor>
]
Specify privileges.
<privileges> ::= <object privileges> ON <object name>
<object name> ::=
[
TABLE ] <table
name>
|
DOMAIN <domain
name>
|
COLLATION <collation
name>
|
CHARACTER
SET <character
set name>
|
TRANSLATION <transliteration
name>
|
TYPE <schema-resolved
user-defined type name>
|
SEQUENCE <sequence
generator name>
| <specific
routine designator>
<object privileges> ::=
ALL
PRIVILEGES
| <action>
[ { <comma>
<action>
}... ]
<action> ::=
SELECT
|
SELECT <left
paren> <privilege
column list> <right
paren>
|
SELECT <left
paren> <privilege
method list> <right
paren>
|
DELETE
|
INSERT [ <left
paren> <privilege
column list> <right
paren> ]
|
UPDATE [ <left
paren> <privilege
column list> <right
paren> ]
|
REFERENCES [ <left
paren> <privilege
column list> <right
paren> ]
|
USAGE
|
TRIGGER
|
UNDER
|
EXECUTE
<privilege method list> ::= <specific routine designator> [ { <comma> <specific routine designator> }... ]
<privilege column list> ::= <column name list>
<grantee> ::= PUBLIC | <authorization identifier>
<grantor> ::= CURRENT_USER | CURRENT_ROLE
Define a role.
<role definition> ::= CREATE ROLE <role name> [ WITH ADMIN <grantor> ]
Define role authorizations.
<grant role statement> ::=
GRANT <role
granted> [ { <comma>
<role
granted> }... ]
TO <grantee>
[ { <comma>
<grantee>
}... ] [
WITH ADMIN
OPTION
] [
GRANTED BY <grantor>
]
<role granted> ::= <role name>
Destroy a role.
<drop role statement> ::= DROP ROLE <role name>
Destroy privileges and role authorizations.
<revoke statement> ::=
<revoke
privilege statement>
| <revoke
role statement>
<revoke privilege statement> ::=
REVOKE [ <revoke
option extension> ] <privileges>
FROM
<grantee>
[ { <comma>
<grantee>
}... ]
[
GRANTED BY <grantor>
] <drop
behavior>
<revoke option extension> ::= GRANT OPTION FOR | HIERARCHY OPTION FOR
<revoke role statement> ::=
REVOKE [
ADMIN
OPTION FOR ]
<role
revoked> [ { <comma>
<role
revoked> }... ]
FROM <grantee>
[ { <comma>
<grantee>
}... ] [
GRANTED BY <grantor>
] <drop
behavior>
<role revoked> ::= <role name>
Define an SQL-client module.
<SQL-client module definition> ::=
<module
name clause> <language
clause> <module
authorization clause>
[ <module
path specification>
]
[ <module
transform group specification>
]
[ <module
collation> ]
[ <temporary
table declaration> ...
]
<module
contents> ...
<module authorization clause> ::=
SCHEMA <schema
name>
|
AUTHORIZATION <module
authorization identifier> [
FOR STATIC
{ ONLY
|
AND
DYNAMIC } ]
| SCHEMA
<schema
name>
AUTHORIZATION <module
authorization identifier> [
FOR STATIC
{ ONLY
|
AND
DYNAMIC } ]
<module authorization identifier> ::= <authorization identifier>
<module path specification> ::= <path specification>
<module transform group specification> ::= <transform group specification>
<module collations> ::= <module collation specification> ...
<module collation specification> ::= COLLATION <collation name> [ FOR <character set specification list> ]
<character set specification list> ::= <character set specification> [ { <comma> <character set specification> }... ]
<module contents> ::=
<declare
cursor>
| <dynamic
declare cursor>
| <externally-invoked
procedure>
Name an SQL-client module.
<module name clause> ::=
MODULE [ <SQL-client
module name> ] [ <module
character set specification> ]
<module character set specification> ::= NAMES ARE <character set specification>
Define an externally-invoked procedure.
<externally-invoked procedure> ::=
PROCEDURE <procedure
name> <host
parameter declaration list> <semicolon>
<SQL
procedure statement> <semicolon>
<host parameter declaration list> ::=
<left
paren> <host
parameter declaration> [ { <comma>
<host
parameter declaration> }... ] <right
paren>
<host parameter declaration> ::=
<host
parameter name> <host
parameter data type>
| <status
parameter>
<host parameter data type> ::= <data type> [ <locator indication> ]
<status parameter> ::= SQLSTATE
Define all of the SQL-statements that are <SQL procedure statement>s.
<SQL procedure statement> ::= <SQL executable statement>
<SQL executable statement> ::=
<SQL
schema statement>
|
<SQL
data statement>
| <SQL
control statement>
|
<SQL
transaction statement>
| <SQL
connection statement>
| <SQL
session statement>
|
<SQL
diagnostics statement>
| <SQL
dynamic statement>
<SQL schema statement> ::=
<SQL
schema definition statement>
| <SQL
schema manipulation statement>
<SQL schema definition statement> ::=
<schema
definition>
| <table
definition>
| <view
definition>
| <SQL-invoked
routine>
| <grant
statement>
| <role
definition>
| <domain
definition>
| <character
set definition>
| <collation
definition>
| <transliteration
definition>
| <assertion
definition>
| <trigger
definition>
| <user-defined
type definition>
| <user-defined
cast definition>
| <user-defined
ordering definition>
| <transform
definition>
| <sequence
generator definition>
<SQL schema manipulation statement> ::=
<drop
schema statement>
|
<alter
table statement>
| <drop
table statement>
| <drop
view statement>
| <alter
routine statement>
|
<drop
routine statement>
|
<drop
user-defined cast statement>
| <revoke
statement>
| <drop
role statement>
| <alter
domain statement>
|
<drop
domain statement>
|
<drop
character set statement>
| <drop
collation statement>
| <drop
transliteration statement>
| <drop
assertion statement>
| <drop
trigger statement>
|
<alter
type statement>
| <drop
data type statement>
| <drop
user-defined ordering statement>
| <alter
transform statement>
| <drop
transform statement> | <alter
sequence generator statement>
| <drop
sequence generator statement>
<SQL data statement> ::=
<open
statement>
| <fetch
statement>
| <close
statement>
| <select
statement: single row>
| <free
locator statement>
|
<hold
locator statement>
|
<SQL
data change statement>
<SQL data change statement> ::=
<delete
statement: positioned>
| <delete
statement: searched>
| <insert
statement>
| <update
statement: positioned>
| <update
statement: searched>
| <merge
statement>
<SQL control statement> ::=
<call
statement>
| <return
statement>
<SQL transaction statement> ::=
<start
transaction statement>
| <set
transaction statement>
| <set
constraints mode statement>
| <savepoint
statement>
| <release
savepoint statement>
| <commit
statement>
| <rollback
statement>
<SQL connection statement> ::=
<connect
statement>
| <set
connection statement>
| <disconnect
statement>
<SQL session statement> ::=
<set
session user identifier statement>
| <set
role statement>
| <set
local time zone statement>
| <set
session characteristics statement>
| <set
catalog statement>
|
<set
schema statement>
|
<set
names statement>
| <set
path statement>
| <set
transform group statement>
| <set
session collation statement>
<SQL diagnostics statement> ::= <get diagnostics statement>
<SQL dynamic statement> ::=
<system
descriptor statement>
| <prepare
statement>
| <deallocate
prepared statement>
|
<describe
statement>
| <execute
statement>
| <execute
immediate statement>
| <SQL
dynamic data statement>
<SQL dynamic data statement> ::=
<allocate
cursor statement>
|
<dynamic
open statement>
| <dynamic
fetch statement>
| <dynamic
close statement>
| <dynamic
delete statement: positioned>
| <dynamic
update statement: positioned>
<system descriptor statement> ::=
<allocate
descriptor statement>
| <deallocate
descriptor statement>
| <set
descriptor statement>
| <get
descriptor statement>
SQL Data Type | C Data Type |
---|---|
SQLSTATE | char, with length 6 |
CHARACTER (L)3 | char, with length (L+1)*k1 |
CHARACTER VARYING (L)3 | char, with length (L+1)*k1 |
CHARACTER LARGE OBJECT(L) |
struct { long hvn3_reserved unsigned long hvn2_length char3 hvn2_data[L]; } hvn2 |
BINARY LARGE OBJECT(L) |
struct { long hvn2_reserved unsigned long hvn2_length char hvn2_data[L]; } hvn2 |
NUMERIC(P,S) | None |
DECIMAL(P,S) | None |
SMALLINT | pointer to short |
INTEGER | pointer to long |
BIGINT | pointer to long long |
FLOAT(P) | None |
REAL | pointer to float |
DOUBLE PRECISION | pointer to double |
BOOLEAN | pointer to long |
DATE | None |
TIME(T) | None |
TIMESTAMP(T) | None |
INTERVAL(Q) | None |
user-defined type | None |
REF | char, with length N |
ROW | None |
ARRAY | None |
MULTISET | None |
1 For character set UTF16, as well as other implementation-defined
character sets in which a code unit occupies two octets, k is the length in
units of C unsigned short of the character encoded using the greatest number of
such units in the character set; for character set UTF32, as well as other
implementation-defined character sets in which a code unit occupies four octets,
k is four; for other character sets, k is the length in units of C char of the
character encoded using the greatest number of such units in the character set.
2 hvn is the name of the host variable defined to correspond to
the SQL data type
3 For character set UTF16, as well as other
implementation-defined character sets in which a code unit occupies two octets,
char or unsigned char should be replaced with unsigned short; for character set
UTF32, as well as other implementation-defined character sets in which a code
unit occupies four octets, char or unsigned char should be replaced with
unsigned int. Otherwise, char or unsigned char should be used.
Define a cursor.
<declare cursor> ::=
DECLARE <cursor
name> [ <cursor
sensitivity> ] [ <cursor
scrollability> ]
CURSOR
[ <cursor
holdability> ] [ <cursor
returnability> ]
FOR <cursor
specification>
<cursor sensitivity> ::= SENSITIVE | INSENSITIVE | ASENSITIVE
<cursor scrollability> ::= SCROLL | NO SCROLL
<cursor holdability> ::= WITH HOLD | WITHOUT HOLD
<cursor returnability> ::= WITH RETURN | WITHOUT RETURN
<cursor specification> ::= <query expression> [ <order by clause> ] [ <updatability clause> ]
<updatability clause> ::= FOR { READ ONLY | UPDATE [ OF <column name list> ] }
<order by clause> ::= ORDER BY <sort specification list>
Open a cursor.
<open statement> ::= OPEN <cursor name>
Position a cursor on a specified row of a table and retrieve values from that row.
<fetch statement> ::=
FETCH [ [ <fetch
orientation> ]
FROM ] <cursor
name>
INTO <fetch
target list>
<fetch orientation> ::=
NEXT
|
PRIOR
|
FIRST
|
LAST
| {
ABSOLUTE |
RELATIVE } <simple
value specification>
<fetch target list> ::= <target specification> [ { <comma> <target specification> }... ]
Close a cursor.
<close statement> ::= CLOSE <cursor name>
Retrieve values from a specified row of a table.
<select statement: single row> ::=
SELECT [ <set
quantifier> ] <select
list>
INTO <select
target list> <table
expression>
<select target list> ::= <target specification> [ { <comma> <target specification> }... ]
Delete a row of a table.
<delete statement: positioned> ::= DELETE FROM <target table> WHERE CURRENT OF <cursor name>
<target table> ::=
<table
name>
|
ONLY <left
paren> <table
name> <right
paren>
Delete rows of a table.
<delete statement: searched> ::= DELETE FROM <target table> [ WHERE <search condition> ]
Create new rows in a table.
<insert statement> ::= INSERT INTO <insertion target> <insert columns and source>
<insertion target> ::= <table name>
<insert columns and source> ::=
<from
subquery>
| <from
constructor>
| <from
default>
<from subquery> ::= [ <left paren> <insert column list> <right paren> ] [ <override clause> ] <query expression>
<from constructor> ::=
[ <left
paren> <insert
column list> <right
paren> ] [ <override
clause> ] <contextually
typed table value constructor>
<override clause> ::= OVERRIDING USER VALUE | OVERRIDING SYSTEM VALUE
<from default> ::= DEFAULT VALUES
<insert column list> ::= <column name list>
Conditionally update rows of a table, or insert new rows into a table, or both.
<merge statement> ::=
MERGE INTO <target
table> [ [
AS ] <merge
correlation name> ]
USING
<table
reference>
ON <search
condition> <merge
operation specification>
<merge correlation name> ::= <correlation name>
<merge operation specification> ::= <merge when clause> ...
<merge when clause> ::= <merge when matched clause> | <merge when not matched clause>
<merge when matched clause> ::= WHEN MATCHED THEN <merge update specification>
<merge when not matched clause> ::= WHEN NOT MATCHED THEN <merge insert specification>
<merge update specification> ::= UPDATE SET <set clause list>
<merge insert specification> ::=
INSERT [ <left
paren> <insert
column list> <right
paren> ]
[ <override
clause> ]
VALUES <merge
insert value list>
<merge insert value list> ::=
<left
paren> <merge
insert value element> [ { <comma>
<merge
insert value element> }... ] <right
paren>
<merge insert value element> ::= <value expression> | <contextually typed value specification>
Update a row of a table.
<update statement: positioned> ::= UPDATE <target table> SET <set clause list> WHERE CURRENT OF <cursor name>
Update rows of a table.
<update statement: searched> ::= UPDATE <target table> SET <set clause list> [ WHERE <search condition> ]
Specify a list of updates.
<set clause list> ::= <set clause> [ { <comma> <set clause> }... ]
<set clause> ::=
<multiple
column assignment>
|
<set
target> <equals
operator> <update
source>
<set target> ::= <update target> | <mutated set clause>
<multiple column assignment> ::= <set target list> <equals operator> <assigned row>
<set target list> ::= <left paren> <set target> [ { <comma> <set target> }... ] <right paren>
<assigned row> ::= <contextually typed row value expression>
<update target> ::=
<object
column>
| <object
column> <left
bracket or trigraph> <simple
value specification> <right
bracket or trigraph>
<object column> ::= <column name>
<mutated set clause> ::= <mutated target> <period> <method name>
<mutated target> ::= <object column> | <mutated set clause>
<update source> ::= <value expression> | <contextually typed value specification>
Declare a declared local temporary table.
<temporary table declaration> ::=
DECLARE
LOCAL
TEMPORARY
TABLE <table
name> <table
element list>
[ ON COMMIT
<table
commit action>
ROWS ]
Remove the association between a locator variable and the value that is represented by that locator.
<free locator statement> ::= FREE LOCATOR <locator reference> [ { <comma> <locator reference> }... ]
<locator reference> ::= <host parameter name> | <embedded variable name>
Mark a locator variable as being holdable.
<hold locator statement> ::= HOLD LOCATOR <locator reference> [ { <comma> <locator reference> }... ]
Invoke an SQL-invoked routine.
<call statement> ::= CALL <routine invocation>
Return a value from an SQL function.
<return statement> ::= RETURN <return value>
<return value> ::= <value expression> | NULL
Start an SQL-transaction and set its characteristics.
<start transaction statement> ::= START TRANSACTION [ <transaction mode> [ { <comma> <transaction mode> }...] ]
<transaction mode> ::= <isolation level> | <transaction access mode> | <diagnostics size>
<transaction access mode> ::= READ ONLY | READ WRITE
<isolation level> ::= ISOLATION LEVEL <level of isolation>
<level of isolation> ::= READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE
<diagnostics size> ::= DIAGNOSTICS SIZE <number of conditions>
<number of conditions> ::= <simple value specification>
Set the characteristics of the next SQL-transaction for the SQL-agent. NOTE 402 - This statement has no effect on any SQL-transactions subsequent to the next SQL-transaction.
<set transaction statement> ::= SET [ LOCAL ] <transaction characteristics>
<transaction characteristics> ::= TRANSACTION <transaction mode> [ { <comma> <transaction mode> }... ]
If an SQL-transaction is currently active, then set the constraint mode for that SQL-transaction in the current SQL-session. If no SQL-transaction is currently active, then set the constraint mode for the next SQL-transaction in the current SQL-session for the SQL-agent. NOTE 404 – This statement has no effect on any SQL-transactions subsequent to this SQL-transaction.
<set constraints mode statement> ::= SET CONSTRAINTS <constraint name list> { DEFERRED | IMMEDIATE }
<constraint name list> ::= ALL | <constraint name> [ { <comma> <constraint name> }... ]
Establish a savepoint.
<savepoint statement> ::= SAVEPOINT <savepoint specifier>
<savepoint specifier> ::= <savepoint name>
Destroy a savepoint.
<release savepoint statement> ::= RELEASE SAVEPOINT <savepoint specifier>
Terminate the current SQL-transaction with commit.
<commit statement> ::= COMMIT [ WORK ] [ AND [ NO ] CHAIN ]
Terminate the current SQL-transaction with rollback, or rollback all actions affecting SQL-data and/or schemas since the establishment of a savepoint.
<rollback statement> ::= ROLLBACK [ WORK ] [ AND [ NO ] CHAIN ] [ <savepoint clause> ]
<savepoint clause> ::= TO SAVEPOINT <savepoint specifier>
Establish an SQL-session.
<connect statement> ::= CONNECT TO <connection target>
<connection target> ::=
<SQL-server
name> [
AS <connection
name> ] [
USER <connection
user name> ]
| DEFAULT
Select an SQL-connection from the available SQL-connections.
<set connection statement> ::= SET CONNECTION <connection object>
<connection object> ::= DEFAULT | <connection name>
Terminate an SQL-connection.
<disconnect statement> ::= DISCONNECT <disconnect object>
<disconnect object> ::= <connection object> | ALL | CURRENT
Set one or more characteristics for the current SQL-session.
<set session characteristics statement> ::= SET SESSION CHARACTERISTICS AS <session characteristic list>
<session characteristic list> ::= <session characteristic> [ { <comma> <session characteristic> }... ]
<session characteristic> ::= <transaction characteristics>
Set the SQL-session user identifier and the current user identifier of the current SQL-session context.
<set session user identifier statement> ::= SET SESSION AUTHORIZATION <value specification>
Set the current role name for the current SQL-session context.
<set role statement> ::= SET ROLE <role specification>
<role specification> ::= <value specification> | NONE
Set the default local time zone displacement for the current SQL-session.
<set local time zone statement> ::= SET TIME ZONE <set time zone value>
<set time zone value> ::= <interval value expression> | LOCAL
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.
<set catalog statement> ::= SET <catalog name characteristic>
<catalog name characteristic> ::= CATALOG <value specification>
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.
<set schema statement> ::= SET <schema name characteristic>
<schema name characteristic> ::= SCHEMA <value specification>
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.
<set names statement> ::= SET <character set name characteristic>
<character set name characteristic> ::= NAMES <value specification>
Set the SQL-path used to determine the subject routine of <routine invocation>s with unqualified< routine name>s in <preparable statement>s that are prepared in the current SQL-session by an <execute immediate statement> or a <prepare statement> and in <direct SQL statement>s, respectively, that are invoked directly. The SQL-path remains the current SQL-path of the SQLsession until another SQL-path is successfully set.
<set path statement> ::= SET <SQL-path characteristic>
<SQL-path characteristic> ::= PATH <value specification>
Set the group name that identifies the group of transform functions for mapping values of userdefined types to predefined data types.
<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>
Set the SQL-session collation of the SQL-session for one or more character sets. An SQL-session collation remains effective until another SQL-session collation for the same character set is successfully set.
<set session collation statement> ::=
SET
COLLATION <collation
specification> [
FOR <character
set specification list> ]
|
SET NO
COLLATION [
FOR <character
set specification list> ]
<character set specification list> ::= <character set specification> [ , <character set specification> ... ]
<collation specification> ::= <value specification>
Allocate an SQL descriptor area.
<allocate descriptor statement> ::= ALLOCATE [ SQL ] DESCRIPTOR <descriptor name> [ WITH MAX <occurrences> ]
<occurrences> ::= <simple value specification>
Deallocate an SQL descriptor area.
<deallocate descriptor statement> ::= DEALLOCATE [ SQL ] DESCRIPTOR <descriptor name>
Get information from an SQL descriptor area.
<get descriptor statement> ::= GET [ SQL ] DESCRIPTOR <descriptor name> <get descriptor information>
<get descriptor information> ::=
<get
header information> [ { <comma>
<get
header information> }... ]
|
VALUE <item
number> <get
item information> [ { <comma>
<get
item information> }... ]
<get header information> ::= <simple target specification 1> <equals operator> <header item name>
<header item name> ::= COUNT | KEY_TYPE | DYNAMIC_FUNCTION | DYNAMIC_FUNCTION_CODE | TOP_LEVEL_COUNT
<get item information> ::= <simple target specification 2> <equals operator> <descriptor item name>
<item number> ::= <simple value specification>
<simple target specification 1> ::= <simple target specification>
<simple target specification 2> ::= <simple target specification>
<descriptor item name> ::=
CARDINALITY
|
CHARACTER_SET_CATALOG
|
CHARACTER_SET_NAME
|
CHARACTER_SET_SCHEMA
|
COLLATION_CATALOG
|
COLLATION_NAME
|
COLLATION_SCHEMA
| DATA
|
DATETIME_INTERVAL_CODE
|
DATETIME_INTERVAL_PRECISION
|
DEGREE
|
INDICATOR
|
KEY_MEMBER
|
LENGTH
|
LEVEL
|
NAME
|
NULLABLE
|
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
Set information in an SQL descriptor area.
<set descriptor statement> ::= SET [ SQL ] DESCRIPTOR <descriptor name> <set descriptor information>
<set descriptor information> ::=
<set
header information> [ { <comma>
<set
header information> }... ]
|
VALUE <item
number> <set
item information> [ { <comma>
<set
item information> }... ]
<set header information> ::= <header item name> <equals operator> <simple value specification 1>
<set item information> ::= <descriptor item name> <equals operator> <simple value specification 2>
<simple value specification 1> ::= <simple value specification>
<simple value specification 2> ::= <simple value specification>
<item number> ::= <simple value specification>
Prepare a statement for execution.
<prepare statement> ::=
PREPARE <SQL
statement name> [ <attributes
specification> ]
FROM <SQL
statement variable>
<attributes specification> ::= ATTRIBUTES <attributes variable>
<attributes variable> ::= <simple value specification>
<SQL statement variable> ::= <simple value specification>
<preparable statement> ::=
<preparable
SQL data statement>
|
<preparable
SQL schema statement>
| <preparable
SQL transaction statement>
| <preparable
SQL control statement>
| <preparable
SQL session statement>
| <preparable
implementation-defined statement>
<preparable SQL data statement> ::=
<delete
statement: searched>
| <dynamic
single row select statement>
| <insert
statement>
| <dynamic
select statement>
|
<update
statement: searched>
| <merge
statement>
| <preparable
dynamic delete statement: positioned>
| <preparable
dynamic update statement: positioned>
<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.
Specify a list of cursor attributes.
<cursor attributes> ::= <cursor attribute> ...
<cursor attribute> ::=
<cursor
sensitivity>
| <cursor
scrollability>
| <cursor
holdability>
| <cursor
returnability>
Deallocate SQL-statements that have been prepared with a <prepare statement>.
<deallocate prepared statement> ::= DEALLOCATE PREPARE <SQL statement name>
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 statement> ::= <describe input statement> | <describe output statement>
<describe input statement> ::= DESCRIBE INPUT <SQL statement name> <using descriptor> [ <nesting option> ]
<describe output statement> ::= DESCRIBE [ OUTPUT ] <described object> <using descriptor> [ <nesting option> ]
<nesting option> ::= WITH NESTING | WITHOUT NESTING
<using descriptor> ::= USING [ SQL ] DESCRIPTOR <descriptor name>
<described object> ::=
<SQL
statement name>
| CURSOR
<extended
cursor name>
STRUCTURE
Supply input values for an <SQL dynamic statement>.
<input using clause> ::= <using arguments> | <using input descriptor>
<using arguments> ::= USING <using argument> [ { <comma> <using argument> }... ]
<using argument> ::= <general value specification>
<using input descriptor> ::= <using descriptor>
Supply output variables for an <SQL dynamic statement>.
<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>
Associate input SQL parameters and output targets with a prepared statement and execute the statement.
<execute statement> ::= EXECUTE <SQL statement name> [ <result using clause> ] [ <parameter using clause> ]
<result using clause> ::= <output using clause>
<parameter using clause> ::= <input using clause>
Dynamically prepare and execute a preparable statement.
<execute immediate statement> ::= EXECUTE IMMEDIATE <SQL statement variable>
Declare a cursor to be associated with a <statement name>, which may in turn be associated with a< cursor specification>.
<dynamic declare cursor> ::=
DECLARE <cursor
name> [ <cursor
sensitivity> ] [ <cursor
scrollability> ]
CURSOR
[ <cursor
holdability> ] [ <cursor
returnability> ]
FOR <statement
name>
Define a cursor based on a prepared statement for a <cursor specification> or assign a cursor to the ordered set of result sets returned from an SQL-invoked procedure.
<allocate cursor statement> ::= ALLOCATE <extended cursor name> <cursor intent>
<cursor intent> ::= <statement cursor> | <result set cursor>
<statement cursor> ::=
[ <cursor
sensitivity> ] [ <cursor
scrollability> ]
CURSOR
[ <cursor
holdability> ] [ <cursor
returnability> ]
FOR <extended
statement name>
<result set cursor> ::= FOR PROCEDURE <specific routine designator>
Associate input dynamic parameters with a <cursor specification> and open the cursor.
<dynamic open statement> ::= OPEN <dynamic cursor name> [ <input using clause> ]
Fetch a row for a cursor declared with a <dynamic declare cursor>.
<dynamic fetch statement> ::= FETCH [ [ <fetch orientation> ] FROM ] <dynamic cursor name> <output using clause>
Retrieve values from a dynamically-specified row of a table.
<dynamic single row select statement> ::= <query specification>
Close a cursor.
<dynamic close statement> ::= CLOSE <dynamic cursor name>
Delete a row of a table.
<dynamic delete statement: positioned> ::= DELETE FROM <target table> WHERE CURRENT OF <dynamic cursor name>
Update a row of a table.
<dynamic update statement: positioned> ::=
UPDATE <target
table>
SET <set
clause list>
WHERE
CURRENT OF <dynamic
cursor name>
Delete a row of a table through a dynamic cursor.
<preparable dynamic delete statement: positioned>
::=
DELETE
[
FROM <target
table> ]
WHERE
CURRENT OF [ <scope
option> ] <cursor
name>
Update a row of a table through a dynamic cursor.
<preparable dynamic update statement: positioned>
::=
UPDATE
[ <target
table> ]
SET <set
clause list>
WHERE
CURRENT OF [ <scope
option> ] <cursor
name>
Specify an <embedded SQL host program>.
<embedded SQL host program> ::=
<embedded
SQL Ada program>
| <embedded
SQL C program>
| <embedded
SQL COBOL program>
|
<embedded
SQL Fortran program>
| <embedded
SQL MUMPS program>
|
<embedded
SQL Pascal program>
|
<embedded
SQL PL/I program>
<embedded SQL statement> ::= <SQL prefix> <statement or declaration> [ <SQL terminator> ]
<statement or declaration> ::=
<declare
cursor>
| <dynamic
declare cursor>
| <temporary
table declaration>
|
<embedded
authorization declaration>
| <embedded
path specification>
|
<embedded
transform group specification>
| <embedded
collation specification>
| <embedded
exception declaration>
| <handler
declaration>
| <SQL
procedure statement>
<SQL prefix> ::= EXEC SQL | <ampersand> SQL <left paren>
<SQL terminator> ::= END-EXEC | <semicolon> | <right paren>
<embedded authorization declaration> ::= DECLARE <embedded authorization clause>
<embedded authorization clause> ::=
SCHEMA <schema
name>
|
AUTHORIZATION <embedded
authorization identifier> [
FOR STATIC
{ ONLY
|
AND
DYNAMIC } ]
| SCHEMA
<schema
name>
AUTHORIZATION <embedded
authorization identifier> [
FOR STATIC
{ ONLY
|
AND
DYNAMIC } ]
<embedded authorization identifier> ::= <module authorization identifier>
<embedded path specification> ::= <path specification>
<embedded transform group specification> ::= <transform group specification>
<embedded collation specification> ::= <module collations>
<embedded SQL declare section> ::=
<embedded
SQL begin declare> [ <embedded
character set declaration> ] [ <host
variable definition> ... ] <embedded
SQL end declare>
| <embedded
SQL MUMPS declare>
<embedded character set declaration> ::= SQL NAMES ARE <character set specification>
<embedded SQL begin declare> ::= <SQL prefix> BEGIN DECLARE SECTION [ <SQL terminator> ]
<embedded SQL end declare> ::= <SQL prefix> END DECLARE SECTION [ <SQL terminator> ]
<embedded SQL MUMPS declare> ::=
<SQL
prefix>
BEGIN
DECLARE
SECTION [ <embedded
character set declaration>
]
[ <host
variable definition> ... ]
END
DECLARE
SECTION <SQL
terminator>
<host variable definition> ::=
<Ada
variable definition>
| <C
variable definition>
| <COBOL
variable definition>
| <Fortran
variable definition>
| <MUMPS
variable definition>
| <Pascal
variable definition>
| <PL/I
variable definition>
<embedded variable name> ::= <colon> <host identifier>
<host identifier> ::=
<Ada
host identifier>
| <C
host identifier>
| <COBOL
host identifier>
| <Fortran
host identifier>
| <MUMPS
host identifier>
| <Pascal
host identifier>
| <PL/I
host identifier>
Specify the action to be taken when an SQL-statement causes a specific class of condition to be raised.
<embedded exception declaration> ::= WHENEVER <condition> <condition action>
<condition> ::= <SQL condition>
<SQL condition> ::=
<major
category>
|
SQLSTATE ( <SQLSTATE
class value> [ , <SQLSTATE
subclass value> ] )
|
CONSTRAINT <constraint
name>
<major category> ::= SQLEXCEPTION | SQLWARNING | NOT FOUND
<SQLSTATE class value> ::= <SQLSTATE char> <SQLSTATE char> !! See the Syntax Rules.
<SQLSTATE subclass value> ::= <SQLSTATE char> <SQLSTATE char> <SQLSTATE char> !! See the Syntax Rules.
<SQLSTATE char> ::= <simple Latin upper case letter> | <digit>
<condition action> ::= CONTINUE | <go to>
<go to> ::= { GOTO | GO TO } <goto target>
<goto target> ::=
<host
label identifier>
|
<unsigned
integer>
| <host
PL/I label variable>
<host label identifier> ::= !! See the Syntax Rules.
<host PL/I label variable> ::= !! See the Syntax Rules.
Specify an <embedded SQL Ada program>.
<embedded SQL Ada program> ::= !! See the Syntax Rules.
<Ada variable definition> ::=
<Ada
host identifier> [ { <comma>
<Ada
host identifier> }... ] <colon>
<Ada
type specification> [ <Ada
initial value> ]
<Ada initial value> ::= <Ada assignment operator> <character representation> ...
<Ada assignment operator> ::= <colon> <equals operator>
<Ada host identifier> ::= !! See the Syntax Rules.
<Ada type specification> ::=
<Ada
qualified type specification>
| <Ada
unqualified type specification>
| <Ada
derived type specification>
<Ada qualified type specification> ::=
Interfaces.SQL <period>
CHAR
[
CHARACTER
SET [
IS ] <character
set specification> ]
<left
paren> 1 <double
period> <length>
<right
paren>
|
Interfaces.SQL <period>
SMALLINT
|
Interfaces.SQL <period>
INT
|
Interfaces.SQL <period>
BIGINT
|
Interfaces.SQL <period>
REAL
|
Interfaces.SQL <period>
DOUBLE_PRECISION
|
Interfaces.SQL <period>
BOOLEAN
|
Interfaces.SQL <period>
SQLSTATE_TYPE
|
Interfaces.SQL <period>
INDICATOR_TYPE
<Ada unqualified type specification> ::=
CHAR <left
paren> 1 <double
period> <length>
<right
paren>
|
SMALLINT
|
INT
|
BIGINT
|
REAL
|
DOUBLE_PRECISION
| BOOLEAN
|
SQLSTATE_TYPE
|
INDICATOR_TYPE
<Ada derived type specification> ::=
<Ada
CLOB variable>
| <Ada
CLOB locator variable>
| <Ada
BLOB variable>
| <Ada
BLOB locator variable>
| <Ada
user-defined type variable>
| <Ada
user-defined type locator variable>
| <Ada
REF variable>
| <Ada
array locator variable>
| <Ada
multiset locator variable>
<Ada CLOB variable> ::=
SQL
TYPE
IS
CLOB <left
paren> <large
object length> <right
paren> [
CHARACTER
SET [
IS ] <character
set specification> ]
<Ada CLOB locator variable> ::= SQL TYPE IS CLOB AS LOCATOR
<Ada BLOB variable> ::= SQL TYPE IS BLOB <left paren> <large object length> <right paren>
<Ada BLOB locator variable> ::= SQL TYPE IS BLOB AS LOCATOR
<Ada user-defined type variable> ::= SQL TYPE IS <path-resolved user-defined type name> AS <predefined type>
<Ada user-defined type locator variable> ::= SQL TYPE IS <path-resolved user-defined type name> AS LOCATOR
<Ada REF variable> ::= SQL TYPE IS <reference type>
<Ada array locator variable> ::= SQL TYPE IS <array type> AS LOCATOR
<Ada multiset locator variable> ::= SQL TYPE IS <multiset type> AS LOCATOR
Specify an <embedded SQL C program>.
<embedded SQL C program> ::= !! See the Syntax Rules.
<C variable definition> ::= [ <C storage class> ] [ <C class modifier> ] <C variable specification> <semicolon>
<C variable specification> ::= <C numeric variable> | <C character variable> | <C derived variable>
<C storage class> ::= auto | extern | static
<C class modifier> ::= const | volatile
<C numeric variable> ::=
{
long
long |
long |
short |
float |
double } <C
host identifier> [ <C
initial value> ]
[
{ <comma>
<C
host identifier> [ <C
initial value> ] }... ]
<C character variable> ::=
<C
character type> [
CHARACTER
SET [
IS ] <character
set specification> ]
<C
host identifier> <C
array specification> [ <C
initial value> ]
[
{ <comma>
<C
host identifier> <C
array specification> [ <C
initial value> ] }... ]
<C character type> ::= char | unsigned char | unsigned short
<C array specification> ::= <left bracket> <length> <right bracket>
<C host identifier> ::= !! See the Syntax Rules.
<C derived variable> ::=
<C
VARCHAR variable>
|
<C
NCHAR variable>
| <C
NCHAR VARYING variable>
| <C
CLOB variable>
| <C
NCLOB variable>
| <C
BLOB variable>
| <C
user-defined type variable>
| <C
CLOB locator variable>
| <C
BLOB locator variable>
| <C
array locator variable>
| <C
multiset locator variable>
| <C
user-defined type locator variable>
| <C
REF variable>
<C VARCHAR variable> ::=
VARCHAR [
CHARACTER
SET [
IS ] <character
set specification> ]
<C
host identifier> <C
array specification> [ <C
initial value> ]
[
{ <comma>
<C
host identifier> <C
array specification> [ <C
initial value> ] }... ]
<C NCHAR variable> ::=
NCHAR [
CHARACTER
SET [
IS ] <character
set specification> ]
<C
host identifier> <C
array specification> [ <C
initial value> ]
[
{ <comma>
<C
host identifier> <C
array specification> [ <C
initial value> ] } ... ]
<C NCHAR VARYING variable> ::=
NCHAR
VARYING [
CHARACTER
SET [
IS ] <character
set specification> ]
<C
host identifier> <C
array specification> [ <C
initial value> ]
[
{ <comma>
<C
host identifier> <C
array specification> [ <C
initial value> ] } ... ]
<C CLOB variable> ::=
SQL
TYPE
IS
CLOB <left
paren> <large
object length> <right
paren>
[
CHARACTER
SET [
IS ] <character
set specification> ]
<C
host identifier> [ <C
initial value> ]
[
{ <comma>
<C
host identifier> [ <C
initial value> ] }... ]
<C NCLOB variable> ::=
SQL
TYPE
IS
NCLOB <left
paren> <large
object length> <right
paren>
[
CHARACTER
SET [
IS ] <character
set specification> ]
<C
host identifier> [ <C
initial value> ]
[
{ <comma>
<C
host identifier> [ <C
initial value> ] }... ]
<C user-defined type variable> ::=
SQL
TYPE
IS <path-resolved
user-defined type name>
AS <predefined
type>
<C
host identifier> [ <C
initial value> ]
[
{ <comma>
<C
host identifier> [ <C
initial value> ] } ... ]
<C BLOB variable> ::=
SQL
TYPE
IS
BLOB <left
paren> <large
object length> <right
paren>
<C
host identifier> [ <C
initial value> ]
[
{ <comma>
<C
host identifier> [ <C
initial value> ] } ... ]
<C CLOB locator variable> ::=
SQL
TYPE
IS
CLOB
AS LOCATOR
<C
host identifier> [ <C
initial value> ]
[
{ <comma>
<C
host identifier> [ <C
initial value> ] } ... ]
<C BLOB locator variable> ::=
SQL
TYPE
IS
BLOB
AS LOCATOR
<C
host identifier> [ <C
initial value> ]
[
{ <comma>
<C
host identifier> [ <C
initial value> ] } ... ]
<C array locator variable> ::=
SQL
TYPE
IS <array
type> AS LOCATOR
<C
host identifier> [ <C
initial value> ]
[
{ <comma>
<C
host identifier> [ <C
initial value> ] } ... ]
<C multiset locator variable> ::=
SQL
TYPE
IS <multiset
type> AS LOCATOR
<C
host identifier> [ <C
initial value> ]
[
{ <comma>
<C
host identifier> [ <C
initial value> ] } ... ]
<C user-defined type locator variable> ::=
SQL
TYPE
IS
<path-resolved
user-defined type name>
AS LOCATOR
<C
host identifier> [ <C
initial value> ]
[
{ <comma>
<C
host identifier> [ <C
initial value> ] }... ]
<C REF variable> ::= SQL TYPE IS <reference type>
<C initial value> ::= <equals operator> <character representation> ...
Specify an <embedded SQL COBOL program>.
<embedded SQL COBOL program> ::= !! See the Syntax Rules.
<COBOL variable definition> ::=
{ 01 | 77 } <COBOL
host identifier> <COBOL
type specification>
[
<character
representation> ... ] <period>
<COBOL host identifier> ::= !! See the Syntax Rules.
<COBOL type specification> ::=
<COBOL
character type>
| <COBOL
national character type>
| <COBOL
numeric type>
| <COBOL
integer type>
| <COBOL
derived type specification>
<COBOL derived type specification> ::=
<COBOL
CLOB variable>
| <COBOL
NCLOB variable>
| <COBOL
BLOB variable>
| <COBOL
user-defined type variable>
| <COBOL
CLOB locator variable>
| <COBOL
BLOB locator variable>
| <COBOL
array locator variable>
| <COBOL
multiset locator variable>
| <COBOL
user-defined type locator variable>
| <COBOL
REF variable>
<COBOL character type> ::=
[
CHARACTER
SET [
IS ] <character
set specification> ]
{ PIC
|
PICTURE } [
IS ] { X [ <left
paren> <length>
<right
paren> ] }...
<COBOL national character type> ::=
[
CHARACTER
SET [
IS ] <character
set specification> ]
{ PIC
|
PICTURE } [
IS ] { N [ <left
paren> <length>
<right
paren> ] }...
<COBOL CLOB variable> ::=
[
USAGE [
IS ] ]
SQL
TYPE
IS
CLOB <left
paren> <large
object length> <right
paren>
[
CHARACTER
SET [
IS ] <character
set specification> ]
<COBOL NCLOB variable> ::=
[
USAGE [
IS ] ]
SQL
TYPE
IS
NCLOB <left
paren> <large
object length> <right
paren>
[
CHARACTER
SET [
IS ] <character
set specification> ]
<COBOL BLOB variable> ::=
[
USAGE [
IS ] ]
SQL
TYPE
IS
BLOB <left
paren> <large
object length> <right
paren>
<COBOL user-defined type variable> ::=
[
USAGE [
IS ] ]
SQL
TYPE
IS <path-resolved
user-defined type name>
AS <predefined
type>
<COBOL CLOB locator variable> ::=
[
USAGE [
IS ] ]
SQL
TYPE
IS
CLOB
AS LOCATOR
<COBOL BLOB locator variable> ::=
[
USAGE [
IS ] ]
SQL
TYPE
IS
BLOB
AS LOCATOR
<COBOL array locator variable> ::=
[
USAGE [
IS ] ]
SQL
TYPE
IS <array
type> AS LOCATOR
<COBOL multiset locator variable> ::=
[
USAGE [
IS ] ]
SQL
TYPE
IS <multiset
type> AS LOCATOR
<COBOL user-defined type locator variable> ::=
[
USAGE [
IS ] ]
SQL
TYPE
IS <path-resolved
user-defined type name>
AS LOCATOR
<COBOL REF variable> ::= [ USAGE [ IS ] ] SQL TYPE IS <reference type>
<COBOL numeric type> ::=
{
PIC |
PICTURE } [
IS ] S
<COBOL
nines specification> [
USAGE [
IS ] ]
DISPLAY
SIGN
LEADING
SEPARATE
<COBOL nines specification> ::=
<COBOL
nines> [
V [ <COBOL
nines> ] ]
| V <COBOL
nines>
<COBOL integer type> ::= <COBOL binary integer>
<COBOL binary integer> ::= { PIC | PICTURE } [ IS ] S <COBOL nines> [ USAGE [ IS ] ] BINARY
<COBOL nines> ::= { 9 [ <left paren> <length> <right paren> ] }...
Specify an <embedded SQL Fortran program>.
<embedded SQL Fortran program> ::= !! See the Syntax Rules.
<Fortran variable definition> ::=
<Fortran
type specification> <Fortran
host identifier> [ { <comma>
<Fortran
host identifier> }... ]
<Fortran host identifier> ::= !! See the Syntax Rules.
<Fortran type specification> ::=
CHARACTER [ <asterisk>
<length>
] [
CHARACTER
SET [
IS ] <character
set specification> ]
|
CHARACTER
KIND =
n [ <asterisk>
<length>
] [
CHARACTER
SET [
IS ] <character
set specification> ]
|
INTEGER
|
REAL
|
DOUBLE
PRECISION
|
LOGICAL
| <Fortran
derived type specification>
<Fortran derived type specification> ::=
<Fortran
CLOB variable>
| <Fortran
BLOB variable>
| <Fortran
user-defined type variable>
| <Fortran
CLOB locator variable>
| <Fortran
BLOB locator variable>
| <Fortran
user-defined type locator variable>
| <Fortran
array locator variable>
| <Fortran
multiset locator variable>
| <Fortran
REF variable>
<Fortran CLOB variable> ::=
SQL
TYPE
IS
CLOB <left
paren> <large
object length> <right
paren>
[
CHARACTER
SET [
IS ] <character
set specification> ]
<Fortran BLOB variable> ::=
SQL
TYPE
IS
BLOB <left
paren> <large
object length> <right
paren>
<Fortran user-defined type variable> ::=
SQL
TYPE
IS <path-resolved
user-defined type name>
AS <predefined
type>
<Fortran CLOB locator variable> ::= SQL TYPE IS CLOB AS LOCATOR
<Fortran BLOB locator variable> ::= SQL TYPE IS BLOB AS LOCATOR
<Fortran user-defined type locator variable> ::= SQL TYPE IS <path-resolved user-defined type name> AS LOCATOR
<Fortran array locator variable> ::= SQL TYPE IS <array type> AS LOCATOR
<Fortran multiset locator variable> ::= SQL TYPE IS <multiset type> AS LOCATOR
<Fortran REF variable> ::= SQL TYPE IS <reference type>
Specify an <embedded SQL MUMPS program>.
<embedded SQL MUMPS program> ::= !! See the Syntax Rules.
<MUMPS variable definition> ::=
<MUMPS
numeric variable> <semicolon>
| <MUMPS
character variable> <semicolon>
| <MUMPS
derived type specification> <semicolon>
<MUMPS character variable> ::=
VARCHAR <MUMPS
host identifier> <MUMPS
length specification>
[ { <comma>
<MUMPS
host identifier> <MUMPS
length specification> }... ]
<MUMPS host identifier> ::= !! See the Syntax Rules.
<MUMPS length specification> ::= <left paren> <length> <right paren>
<MUMPS numeric variable> ::= <MUMPS type specification> <MUMPS host identifier> [ { <comma> <MUMPS host identifier> }... ]
<MUMPS type specification> ::=
INT
|
DEC [ <left
paren> <precision>
[ <comma>
<scale>
] <right
paren> ]
|
REAL
<MUMPS derived type specification> ::=
<MUMPS
CLOB variable>
| <MUMPS
BLOB variable>
| <MUMPS
user-defined type variable>
| <MUMPS
CLOB locator variable>
| <MUMPS
BLOB locator variable>
| <MUMPS
user-defined type locator variable>
| <MUMPS
array locator variable>
| <MUMPS
multiset locator variable>
| <MUMPS
REF variable>
<MUMPS CLOB variable> ::=
SQL
TYPE
IS
CLOB <left
paren> <large
object length> <right
paren>
[
CHARACTER
SET [
IS ] <character
set specification> ]
<MUMPS BLOB variable> ::= SQL TYPE IS BLOB <left paren> <large object length> <right paren>
<MUMPS user-defined type variable> ::= SQL TYPE IS <path-resolved user-defined type name> AS <predefined type>
<MUMPS CLOB locator variable> ::= SQL TYPE IS CLOB AS LOCATOR
<MUMPS BLOB locator variable> ::= SQL TYPE IS BLOB AS LOCATOR
<MUMPS user-defined type locator variable> ::= SQL TYPE IS <path-resolved user-defined type name> AS LOCATOR
<MUMPS array locator variable> ::= SQL TYPE IS <array type> AS LOCATOR
<MUMPS multiset locator variable> ::= SQL TYPE IS <multiset type> AS LOCATOR
<MUMPS REF variable> ::= SQL TYPE IS <reference type>
Specify an <embedded SQL Pascal program>.
<embedded SQL Pascal program> ::= !! See the Syntax Rules.
<Pascal variable definition> ::=
<Pascal
host identifier> [ { <comma>
<Pascal
host identifier> }... ] <colon>
<Pascal
type specification> <semicolon>
<Pascal host identifier> ::= !! See the Syntax Rules.
<Pascal type specification> ::=
PACKED
ARRAY <left
bracket>
1 <double
period> <length>
<right
bracket>
OF
CHAR
[
CHARACTER
SET [
IS ] <character
set specification> ]
|
INTEGER
|
REAL
|
CHAR [
CHARACTER
SET [
IS ] <character
set specification> ]
|
BOOLEAN
| <Pascal
derived type specification>
<Pascal derived type specification> ::=
<Pascal
CLOB variable>
| <Pascal
BLOB variable>
| <Pascal
user-defined type variable>
| <Pascal
CLOB locator variable>
| <Pascal
BLOB locator variable>
| <Pascal
user-defined type locator variable>
| <Pascal
array locator variable>
| <Pascal
multiset locator variable>
| <Pascal
REF variable>
<Pascal CLOB variable> ::=
SQL
TYPE
IS
CLOB <left
paren> <large
object length> <right
paren>
[
CHARACTER
SET [
IS ] <character
set specification> ]
<Pascal BLOB variable> ::= SQL TYPE IS BLOB <left paren> <large object length> <right paren>
<Pascal CLOB locator variable> ::= SQL TYPE IS CLOB AS LOCATOR
<Pascal user-defined type variable> ::= SQL TYPE IS <path-resolved user-defined type name> AS <predefined type>
<Pascal BLOB locator variable> ::= SQL TYPE IS BLOB AS LOCATOR
<Pascal user-defined type locator variable> ::= SQL TYPE IS <path-resolved user-defined type name> AS LOCATOR
<Pascal array locator variable> ::= SQL TYPE IS <array type> AS LOCATOR
<Pascal multiset locator variable> ::= SQL TYPE IS <multiset type> AS LOCATOR
<Pascal REF variable> ::= SQL TYPE IS <reference type>
Specify an <embedded SQL PL/I program>.
<embedded SQL PL/I program> ::= !! See the Syntax Rules.
<PL/I variable definition> ::=
{
DCL |
DECLARE } { <PL/I
host identifier>
| <left
paren> <PL/I
host identifier> [ { <comma>
<PL/I
host identifier> }... ] <right
paren> }
<PL/I
type specification> [ <character
representation> ... ] <semicolon>
<PL/I host identifier> ::= !! See the Syntax Rules.
<PL/I type specification> ::=
{
CHAR |
CHARACTER } [
VARYING ] <left
paren> <length>
<right
paren> [
CHARACTER
SET [
IS ] <character
set specification> ]
| <PL/I
type fixed decimal> <left
paren> <precision>
[ <comma>
<scale>
] <right
paren>
| <PL/I
type fixed binary> [ <left
paren> <precision>
<right
paren> ]
| <PL/I
type float binary> <left
paren> <precision>
<right
paren>
| <PL/I
derived type specification>
<PL/I derived type specification> ::=
<PL/I
CLOB variable>
| <PL/I
BLOB variable>
| <PL/I
user-defined type variable>
| <PL/I
CLOB locator variable>
| <PL/I
BLOB locator variable>
| <PL/I
user-defined type locator variable>
| <PL/I
array locator variable>
| <PL/I
multiset locator variable>
| <PL/I
REF variable>
<PL/I CLOB variable> ::=
SQL
TYPE
IS
CLOB <left
paren> <large
object length> <right
paren>
[
CHARACTER
SET [
IS ] <character
set specification> ]
<PL/I BLOB variable> ::= SQL TYPE IS BLOB <left paren> <large object length> <right paren>
<PL/I user-defined type variable> ::= SQL TYPE IS <path-resolved user-defined type name> AS <predefined type>
<PL/I CLOB locator variable> ::= SQL TYPE IS CLOB AS LOCATOR
<PL/I BLOB locator variable> ::= SQL TYPE IS BLOB AS LOCATOR
<PL/I user-defined type locator variable> ::= SQL TYPE IS <path-resolved user-defined type name> AS LOCATOR
<PL/I array locator variable> ::= SQL TYPE IS <array type> AS LOCATOR
<PL/I multiset locator variable> ::= SQL TYPE IS <multiset type> AS LOCATOR
<PL/I REF variable> ::= SQL TYPE IS <reference type>
<PL/I type fixed decimal> ::=
{
DEC |
DECIMAL }
FIXED
|
FIXED {
DEC |
DECIMAL }
<PL/I type fixed binary> ::=
{
BIN |
BINARY }
FIXED
|
FIXED {
BIN |
BINARY }
<PL/I type float binary> ::=
{
BIN |
BINARY }
FLOAT
|
FLOAT {
BIN |
BINARY }
Specify direct execution of SQL.
<direct SQL statement> ::= <directly executable statement> <semicolon>
<directly executable statement> ::=
<direct
SQL data statement>
|
<SQL
schema statement>
|
<SQL
transaction statement>
| <SQL
connection statement>
| <SQL
session statement>
|
<direct
implementation-defined statement>
<direct SQL data statement> ::=
<delete
statement: searched>
| <direct
select statement: multiple rows>
| <insert
statement>
| <update
statement: searched>
| <merge
statement>
| <temporary
table declaration>
<direct implementation-defined statement> ::= !! See the Syntax Rules.
Specify a statement to retrieve multiple rows from a specified table.
<direct select statement: multiple rows> ::= <cursor specification>
Get exception or completion condition information from a diagnostics area.
<get diagnostics statement> ::= GET DIAGNOSTICS <SQL diagnostics information>
<SQL diagnostics information> ::= <statement information> | <condition information>
<statement information> ::= <statement information item> [ { <comma> <statement information item> }... ]
<statement information item> ::= <simple target specification> <equals operator> <statement information item name>
<statement information item name> ::=
NUMBER
|
MORE
|
COMMAND_FUNCTION
|
COMMAND_FUNCTION_CODE
|
DYNAMIC_FUNCTION
|
DYNAMIC_FUNCTION_CODE
|
ROW_COUNT
|
TRANSACTIONS_COMMITTED
|
TRANSACTIONS_ROLLED_BACK
|
TRANSACTION_ACTIVE
<condition information> ::=
{
EXCEPTION |
CONDITION } <condition
number> <condition
information item> [ { <comma>
<condition
information item> }... ]
<condition information item> ::= <simple target specification> <equals operator> <condition information item name>
<condition information item name> ::=
CATALOG_NAME
|
CLASS_ORIGIN
|
COLUMN_NAME
|
CONDITION_NUMBER
|
CONNECTION_NAME
|
CONSTRAINT_CATALOG
|
CONSTRAINT_NAME
|
CONSTRAINT_SCHEMA
|
CURSOR_NAME
|
MESSAGE_LENGTH
|
MESSAGE_OCTET_LENGTH
|
MESSAGE_TEXT
|
PARAMETER_MODE
|
PARAMETER_NAME
|
PARAMETER_ORDINAL_POSITION
|
RETURNED_SQLSTATE
|
ROUTINE_CATALOG
|
ROUTINE_NAME
|
ROUTINE_SCHEMA
|
SCHEMA_NAME
|
SERVER_NAME
|
SPECIFIC_NAME
|
SUBCLASS_ORIGIN
|
TABLE_NAME
|
TRIGGER_CATALOG
|
TRIGGER_NAME
|
TRIGGER_SCHEMA
<condition number> ::= <simple value specification>
The character string value returned in an SQLSTATE parameter comprises a 2-character class value followed by a 3-character subclass value, each with an implementation-defined character set that has a one-octet character encoding form and is restricted to <digit>s and< simple Latin upper case letter>s. Table 31, 'SQLSTATE class and subclass values', specifies the class value for each condition and the subclass value or values for each class value.
Class values that begin with one of the <digit>s '0', '1', '2', '3', or '4' or one of the <simple Latin upper case letter>s 'A', 'B', 'C', 'D', 'E', 'F', 'G', or 'H' are returned only for conditions defined in ISO/IEC 9075 or in any other International Standard. The range of such class values are called standard-defined classes. Some such class codes are reserved for use by specific International Standards, as specified elsewhere in this Clause. Subclass values associated with such classes that also begin with one of those 13 characters are returned only for conditions defined in ISO/IEC 9075 or some other International Standard. The range of such class values are called standard-defined classes. Subclass values associated with such classes that begin with one of the< digit>s '5', '6', '7', '8', or '9' or one of the <simple Latin upper case letter>s 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', or 'Z' are reserved for implementation-specified conditions and are called implementation-defined subclasses.
Class values that begin with one of the <digit>s '5', '6', '7', '8', or '9' or one of the <simple Latin upper case letter>s 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', or 'Z' are reserved for implementation-specified exception conditions and are called implementation-defined classes. All subclass values except '000', which means no subclass, associated with such classes are reserved for implementation-specified conditions and are called implementation-defined subclasses. An implementation-defined completion condition shall be indicated by returning an implementation-defined subclass in conjunction with one of the classes successful completion, warning, or no data.
The 'Category' column has the following meanings: 'S' means that the class value given corresponds to successful completion and is a completion condition; 'W' means that the class value given corresponds to a successful completion but with a warning and is a completion condition; 'N' means that the class value given corresponds to a no-data situation and is a completion condition; 'X' means that the class value given corresponds to an exception condition.
Table 31 - SQLSTATE class and subclass values
Category | Condition | Class | Subcondition | Subclass |
---|---|---|---|---|
X | ambiguous cursor name | 3C | (no subclass) | 000 |
X | attempt to assign to non-updatable column | 0U | (no subclass) | 000 |
X | attempt to assign to ordering column | 0V | (no subclass) | 000 |
X | cardinality violation | 21 | (no subclass) | 000 |
X | connection exception | 08 | (no subclass) | 000 |
connection does not exist | 003 | |||
connection failure | 006 | |||
connection name in use | 002 | |||
SQL-client unable to establish SQL-connection | 001 | |||
SQL-server rejected establishment of SQL-connection | 004 | |||
transaction resolution unknown | 007 | |||
X | cursor sensitivity exception | 36 | (no subclass) | 000 |
request failed | 002 | |||
request rejected | 001 | |||
X | data exception | 22 | (no subclass) | 000 |
array data, right truncation | 02F | |||
array element error | 02E | |||
character not in repertoire | 021 | |||
datetime field overflow | 008 | |||
division by zero | 012 | |||
error in assignment | 005 | |||
escape character conflict | 00B | |||
indicator overflow | 022 | |||
interval field overflow | 015 | |||
invalid argument for natural logarithm | 01E | |||
invalid argument for power function | 01F | |||
invalid argument for width bucket function | 01G | |||
invalid character value for cast | 018 | |||
invalid datetime format | 007 | |||
invalid escape character | 019 | |||
invalid escape octet | 00D | |||
invalid escape sequence | 025 | |||
invalid indicator parameter value | 010 | |||
invalid interval format | 006 | |||
invalid parameter value | 023 | |||
invalid preceding or following size in window function | 013 | |||
invalid regular expression | 01B | |||
invalid repeat argument in a sample clause | 02G | |||
invalid sample size | 02H | |||
invalid time zone displacement value | 009 | |||
invalid use of escape character | 00C | |||
most specific type mismatch | 00G | |||
noncharacter in UCS string | 029 | |||
null value substituted for mutator subject parameter | 02D | |||
null row not permitted in table | 01C | |||
null value in array target | 00E | |||
null value, no indicator parameter | 002 | |||
null value not allowed | 004 | |||
numeric value out of range | 003 | |||
sequence generator limit exceeded | 00H | |||
string data, length mismatch | 026 | |||
string data, right truncation | 001 | |||
substring error | 011 | |||
trim error | 027 | |||
unterminated C string | 024 | |||
zero-length character string | 00F | |||
X | dependent privilege descriptors still exist | 2B | (no subclass) | 000 |
X | diagnostics exception | 0Z | (no subclass) | 000 |
maximum number of stacked diagnostics areas exceeded | 001 | |||
X | dynamic SQL error | 07 | (no subclass) | 000 |
cursor specification cannot be executed | 003 | |||
data type transform function violation | 00B | |||
invalid DATA target | 00D | |||
invalid DATETIME_INTERVAL_CODE | 00F | |||
invalid descriptor count | 008 | |||
invalid descriptor index | 009 | |||
invalid LEVEL value | 00E | |||
prepared statement not a cursor specification | 005 | |||
restricted data type attribute violation | 006 | |||
undefined DATA value | 00C | |||
using clause does not match dynamic parameter specifications | 001 | |||
using clause does not match target specifications | 002 | |||
using clause required for dynamic parameters | 004 | |||
using clause required for result fields | 007 | |||
X | external routine exception | 38 | (no subclass) | 000 |
containing SQL not permitted | 001 | |||
modifying SQL-data not permitted | 002 | |||
prohibited SQL-statement attempted | 003 | |||
reading SQL-data not permitted | 004 | |||
X | external routine invocation exception | 39 | (no subclass) | 000 |
invalid SQLSTATE returned | 001 | |||
null value not allowed | 004 | |||
X | feature not supported | 0A | (no subclass) | 000 |
multiple server transactions | 001 | |||
X | integrity constraint violation | 23 | (no subclass) | 000 |
restrict violation | 001 | |||
X | invalid authorization specification | 28 | (no subclass) | 000 |
X | invalid catalog name | 3D | (no subclass) | 000 |
X | invalid character set name | 2C | (no subclass) | 000 |
X | invalid condition number | 35 | (no subclass) | 000 |
X | invalid connection name | 2E | (no subclass) | 000 |
X | invalid cursor name | 34 | (no subclass) | 000 |
X | invalid cursor state | 24 | (no subclass) | 000 |
X | invalid grantor | 0L | (no subclass) | 000 |
X | invalid role specification | 0P | (no subclass) | 000 |
X | invalid schema name | 3F | (no subclass) | 000 |
X | invalid schema name list specification | 0E | (no subclass) | 000 |
X | invalid session collation specification | 2H | (no subclass) | 000 |
X | invalid SQL descriptor name | 33 | (no subclass) | 000 |
X | invalid SQL-invoked procedure reference | 0M | (no subclass) | 000 |
X | invalid SQL statement name | 26 | (no subclass) | 000 |
X | invalid SQL statement identifier | 30 | (no subclass) | 000 |
X | invalid target type specification | 0D | (no subclass) | 000 |
X | invalid transaction initiation | 0B | (no subclass) | 000 |
X | invalid transaction state | 25 | (no subclass) | 000 |
active SQL-transaction | 001 | |||
branch transaction already active | 002 | |||
held cursor requires same isolation level | 008 | |||
inappropriate access mode for branch transaction | 003 | |||
inappropriate isolation level for branch transaction | 004 | |||
no active SQL-transaction for branch transaction | 005 | |||
read-only SQL-transaction | 006 | |||
schema and data statement mixing not supported | 007 | |||
X | invalid transaction termination | 2D | (no subclass) | 000 |
X | invalid transform group name specification | 0S | (no subclass) | 000 |
X | locator exception | 0F | (no subclass) | 000 |
invalid specification | 001 | |||
N | no data | 02 | (no subclass) | 000 |
no additional dynamic result sets returned | 001 | |||
X | prohibited statement encountered during trigger execution | 0W | (no subclass) | 000 |
X | Remote Database Access | HZ | (See Table 32, 'SQLSTATE class codes for RDA', for the definition of protocol subconditions and subclass code values) | |
X | savepoint exception | 3B | (no subclass) | 000 |
invalid specification | 001 | |||
too many | 002 | |||
X | SQL routine exception | 2F | (no subclass) | 000 |
function executed no return statement | 005 | |||
modifying SQL-data not permitted | 002 | |||
prohibited SQL-statement attempted | 003 | |||
reading SQL-data not permitted | 004 | |||
S | successful completion | 00 | (no subclass) | 000 |
X | syntax error or access rule violation | 42 | (no subclass) | 000 |
X | target table disagrees with cursor specification | 0T | (no subclass) | 000 |
X | transaction rollback | 40 | (no subclass) | 000 |
integrity constraint violation | 002 | |||
serialization failure | 001 | |||
statement completion unknown | 003 | |||
triggered action exception | 004 | |||
X | triggered action exception | 09 | (no subclass) | 000 |
X | triggered data change violation | 27 | (no subclass) | 000 |
W | warning | 01 | (no subclass) | 000 |
additional result sets returned | 00D | |||
array data, right truncation | 02F | |||
attempt to return too many result sets | 00E | |||
cursor operation conflict | 001 | |||
default value too long for information schema | 00B | |||
disconnect error | 002 | |||
dynamic result sets returned | 00C | |||
external routine warning (the value of xx to be chosen by the author of the external routine) | Hxx | |||
insufficient item descriptor areas | 005 | |||
null value eliminated in set function | 003 | |||
privilege not granted | 007 | |||
privilege not revoked | 006 | |||
query expression too long for information schema | 00A | |||
search condition too long for information schema | 009 | |||
statement too long for information schema | 008 | |||
string data, right truncation | 004 | |||
X | with check option violation | 44 | (no subclass) | 000 |
Table 33 - Implied feature relationships
Feature ID | Feature Description | Implied Feature | ID Implied Feature Description |
---|---|---|---|
B032 | Extended dynamic SQL | B031 | Basic dynamic SQL |
B034 | Dynamic specification of cursor attributes | B031 | Basic dynamic SQL |
F381 | Extended schema manipulation | F491 | Constraint management |
F451 | Character set definition | F461 | Named character sets |
F711 | ALTER domain | F251 | Domain support |
F801 | Full set function | F441 | Extended set function support |
S024 | Enhanced structured types | S023 | Basic structured types |
S041 | Basic reference types | S023 | Basic structured types |
S041 | Basic reference types | S051 | Create table of type |
S043 | Enhanced reference types | S041 | Basic reference types |
S051 | Create table of type | S023 | Basic structured types |
S081 | Subtables | S023 | Basic structured types |
S081 | Subtables | S051 | Create table of type |
S092 | Arrays of user-defined types | S091 | Basic array support |
S094 | Arrays of reference types | S041 | Basic reference types |
S094 | Arrays of reference types | S091 | Basic array support |
S095 | Array constructors by query | S091 | Basic array support |
S096 | Optional array bounds | S091 | Basic array support |
S111 | ONLY in query expressions | S023 | Basic structured types |
S111 | ONLY in query expressions | S051 | Create table of type |
S201 | SQL-invoked routines on arrays | S091 | Basic array support |
S202 | SQL-invoked routines on multisets | S271 | Basic multiset support |
S231 | Structured type locators | S023 | Basic structured types |
S232 | Array locators | S091 | Basic array support |
S233 | Multiset locators | S271 | Basic multiset support |
S242 | Alter transform statement | S241 | Transform functions |
S272 | Multisets of user-defined types | S271 | Basic multiset support |
S274 | Multisets of reference types | S041 | Basic reference types |
S274 | Multisets of reference types | S271 | Basic multiset support |
S275 | Advanced multiset support | S271 | Basic multiset support |
T042 | Extended LOB data type support | T041 | Basic LOB data type support |
T061 | UCS Support | F461 | Named character sets |
T071 | BIGINT data type | E001-01 | INTEGER and SMALLINT data types (including all spellings) |
T131 | Recursive query | T121 | WITH (excluding RECURSIVE) in query expression |
T173 | Extended LIKE clause in table definition | T171 | LIKE clause in table definition |
T212 | Enhanced trigger capability | T211 | Basic trigger capability |
T332 | Extended roles | T331 | Basic roles |
T511 | Transaction counts | F121 | Basic diagnostics management |
T571 | Array-returning external SQL-invoked functions | S091 | Basic array support |
T571 | Array-returning external SQL-invoked functions | S201 | SQL-invoked routines on arrays |
T572 | Multiset-returning external SQLinvoked functions | S202 | SQL-invoked routines on multisets |
T572 | Multiset-returning external SQLinvoked functions | S271 | Basic multiset support |
T612 | Advanced OLAP operations | T611 | Elementary OLAP operations |
Automatic translation of this grammar is non-trivial for a number of reasons. One is that the grammar has a number of actions '!! See the Syntax Rules' which cannot be translated automatically. Another is that the grammar contains rules that are usually better handled by the lexical analyzer than the grammar proper. Then there are incomplete rules such as those which reference parts 6 to 10 (they are not defined; indeed, part 7, which was going to be SQL/Temporal, is in complete abeyance), and the packages (almost completely undefined in the grammar). It is not clear whether these can be ignored, or annotated out of the way.
Another complication is automatically generating rules to deal with optional components and repetitive components in the grammar. Square brackets do not contain alternative non-terminals; all those expressions are contained within curly brackets within the square brackets. However, some square brackets do contain alternative terminals. Curly brackets contain and group mandatory elements. However, they are usually used in conjunction with the 'one or more times' repeater ellipsis '...' mark.