此内容没有您所选择的语言版本。

Chapter 12. BNF for SQL Grammar

12.1. Reserved Keywords
复制链接

Expand

Keyword	Usage
ADD	add set child option, add set option, ADD column, ADD constraint
ALL	standard aggregate function, CREATE POLICY, function, GRANT, query expression body, query term, Revoke GRANT, select clause, quantified comparison predicate
ALTER	alter, ALTER PROCEDURE , alterStatement, ALTER TABLE , grant type
AND	between predicate, boolean term, window frame
ANY	standard aggregate function, with role, quantified comparison predicate
ARRAY	ARRAY expression constructor
ARRAY_AGG	ordered aggregate function
AS	alter, ALTER PROCEDURE , ALTER TABLE , ALTER TRIGGER , array table, create procedure, create a domain or type alias, option namespace, create trigger, create view, delete statement, derived column, dynamic data statement, function, json table, loop statement, xml namespace element, object table, select derived column, table subquery, text table, table name, unescapedFunction, update statement, with list element, xml serialize, xml table
ASC	sort specification
ATOMIC	compound statement, for each row trigger action
AUTHENTICATED	with role
BEGIN	compound statement, for each row trigger action
BETWEEN	between predicate, window frame
BIGDECIMAL	simple data type
BIGINT	simple data type
BIGINTEGER	simple data type
BLOB	simple data type, xml serialize
BOOLEAN	simple data type
BOTH	function
BREAK	branching statement
BY	group by clause, order by clause, window specification
BYTE	simple data type
CALL	callable statement, call statement
CASE	case expression, searched case expression
CAST	function
CHAR	function, simple data type
CLOB	simple data type, xml serialize
COLUMN	ADD column, DROP column, ALTER TABLE , GRANT, Revoke GRANT
COMMIT	create temporary table
CONSTRAINT	GRANT, table constraint
CONTINUE	branching statement
CONVERT	function
CREATE	create procedure, create data wrapper, create database, create a domain or type alias, create foreign temp table, CREATE POLICY, create role , create schema, create server, create table, create temporary table, create trigger
CROSS	cross join
CUME_DIST	analytic aggregate function
CURRENT_DATE	function
CURRENT_TIME	function
CURRENT_TIMESTAMP	function
DATE	non numeric literal, simple data type
DAY	function
DECIMAL	simple data type
DECLARE	declare statement
DELETE	alter, ALTER TRIGGER , CREATE POLICY, create trigger, delete statement, grant type
DESC	sort specification
DISTINCT	standard aggregate function, function, is distinct, query expression body, query term, select clause
DOUBLE	simple data type
DROP	DROP column, drop option, Drop data wrapper, drop option, DROP POLICY, drop procedure, drop role , drop schema, drop server, drop table, drop table, grant type
EACH	for each row trigger action
ELSE	case expression, if statement, searched case expression
END	case expression, compound statement, for each row trigger action, searched case expression
ERROR	raise error statement
ESCAPE	match predicate, text table
EXCEPT	query expression body
EXEC	dynamic data statement, call statement
EXECUTE	dynamic data statement, grant type, call statement
EXISTS	exists predicate
FALSE	explain option, json table, non numeric literal
FETCH	fetch clause
FILTER	filter clause
FLOAT	simple data type
FOR	CREATE POLICY, for each row trigger action, function, json table column, text aggreate function, text table column, xml table column
FOREIGN	ALTER PROCEDURE , ALTER TABLE , create procedure, create data wrapper, create foreign or global temporary table, create foreign temp table, create schema, create server, Drop data wrapper, drop procedure, drop schema, drop table, foreign key, Import foreign schema, with role
FROM	delete statement, from clause, function, Import foreign schema, is distinct, Revoke GRANT
FULL	qualified table
FUNCTION	create procedure, drop procedure, GRANT, Revoke GRANT
GLOBAL	create foreign or global temporary table, drop table
GRANT	GRANT
GROUP	function, group by clause
HANDLER	create data wrapper
HAVING	having clause
HOUR	function
IF	if statement
IMMEDIATE	dynamic data statement
IMPORT	Import another Database, Import foreign schema
IN	function, procedure parameter, in predicate
INNER	qualified table
INOUT	procedure parameter
INSERT	alter, ALTER TRIGGER , CREATE POLICY, create trigger, function, insert statement, grant type
INTEGER	simple data type
INTERSECT	query term
INTO	dynamic data statement, Import foreign schema, insert statement, into clause
IS	is distinct, is null predicate
JOIN	cross join, make dep options, qualified table
LANGUAGE	GRANT, object table, Revoke GRANT
LATERAL	table subquery
LEADING	function
LEAVE	branching statement
LEFT	function, qualified table
LIKE	match predicate
LIKE_REGEX	like regex predicate
LIMIT	limit clause
LOCAL	create foreign temp table, create temporary table
LONG	simple data type
LOOP	loop statement
MAKEDEP	option clause, table primary
MAKEIND	option clause, table primary
MAKENOTDEP	option clause, table primary
MERGE	insert statement
MINUTE	function
MONTH	function
NO	make dep options, xml namespace element, text aggreate function, text table column, text table
NOCACHE	option clause
NOT	alter column options, between predicate, compound statement, table element, create a domain or type alias, view element, GRANT, is distinct, is null predicate, match predicate, boolean factor, procedure parameter, procedure result column, like regex predicate, in predicate, temporary table element
NULL	alter column options, table element, create a domain or type alias, view element, is null predicate, non numeric literal, procedure parameter, procedure result column, temporary table element, xml query
OF	alter, ALTER TRIGGER , create trigger
OFFSET	limit clause
ON	alter, ALTER TRIGGER , create foreign temp table, CREATE POLICY, create temporary table, create trigger, DROP POLICY, GRANT, loop statement, qualified table, Revoke GRANT, xml query
ONLY	fetch clause
OPTION	option clause
OPTIONS	alter child options list, alter options list, options clause
OR	boolean value expression
ORDER	GRANT, order by clause
OUT	procedure parameter
OUTER	qualified table
OVER	window specification
PARAMETER	ALTER PROCEDURE
PARTITION	window specification
PERCENT_RANK	analytic aggregate function
PRIMARY	create temporary table, inline constraint, primary key
PROCEDURE	alter, ALTER PROCEDURE , create procedure, CREATE POLICY, DROP POLICY, drop procedure, GRANT, Revoke GRANT
RANGE	window frame
REAL	simple data type
REFERENCES	foreign key
RETURN	assignment statement, return statement, data statement
RETURNS	create procedure
REVOKE	Revoke GRANT
RIGHT	function, qualified table
ROLLUP	group by clause
ROW	array table, fetch clause, for each row trigger action, limit clause, text table, window frame bound
ROWS	array table, create temporary table, fetch clause, limit clause, window frame
SECOND	function
SELECT	CREATE POLICY, grant type, select clause
SERVER	ALTER SERVER , create schema, create server, drop server, Import foreign schema
SET	add set child option, add set option, option namespace, update statement, set schema
SHORT	simple data type
SIMILAR	match predicate
SMALLINT	simple data type
SOME	standard aggregate function, quantified comparison predicate
SQLEXCEPTION	sql exception
SQLSTATE	sql exception
SQLWARNING	raise statement
STRING	dynamic data statement, simple data type, xml serialize
TABLE	ALTER TABLE , create procedure, create foreign or global temporary table, create foreign temp table, create temporary table, drop table, drop table, GRANT, query primary, Revoke GRANT, table subquery
TEMPORARY	create foreign or global temporary table, create foreign temp table, create temporary table, drop table, GRANT, Revoke GRANT
THEN	case expression, searched case expression
TIME	non numeric literal, simple data type
TIMESTAMP	non numeric literal, simple data type
TINYINT	simple data type
TO	rename column options, RENAME Table, CREATE POLICY, DROP POLICY, GRANT, match predicate
TRAILING	function
TRANSLATE	function
TRIGGER	alter, ALTER TRIGGER , create trigger
TRUE	explain option, json table, non numeric literal
UNION	cross join, query expression body
UNIQUE	other constraints, inline constraint
UNKNOWN	non numeric literal
UPDATE	alter, ALTER TRIGGER , CREATE POLICY, create trigger, dynamic data statement, grant type, update statement
USER	function
USING	CREATE POLICY, dynamic data statement
VALUES	query primary
VARBINARY	simple data type, xml serialize
VARCHAR	simple data type, xml serialize
VIRTUAL	ALTER PROCEDURE , ALTER TABLE , create procedure, create schema, create view, drop procedure, drop schema, drop table
WHEN	case expression, searched case expression
WHERE	filter clause, where clause
WHILE	while statement
WITH	assignment statement, create role , Import another Database, query expression, data statement
WITHIN	function
WITHOUT	assignment statement, data statement
WRAPPER	ALTER DATA WRAPPER , create data wrapper, create server, Drop data wrapper
XML	explain option, simple data type
XMLAGG	ordered aggregate function
XMLATTRIBUTES	xml attributes
XMLCAST	unescapedFunction
XMLCOMMENT	function
XMLCONCAT	function
XMLELEMENT	xml element
XMLEXISTS	xml query
XMLFOREST	xml forest
XMLNAMESPACES	xml namespaces
XMLPARSE	xml parse
XMLPI	function
XMLQUERY	xml query
XMLSERIALIZE	xml serialize
XMLTABLE	xml table
XMLTEXT	function
YEAR	function

12.2. Non-Reserved Keywords
复制链接

Expand

Name	Usage
ACCESS	basicNonReserved, Import another Database
ACCESSPATTERN	basicNonReserved, other constraints
AFTER	alter, basicNonReserved, create trigger
ANALYZE	basicNonReserved, explain option
ARRAYTABLE	array table, basicNonReserved
AUTO_INCREMENT	alter column options, basicNonReserved, table element, view element
AVG	standard aggregate function, basicNonReserved
CHAIN	basicNonReserved, sql exception
COLUMNS	array table, basicNonReserved, json table, object table, text table, xml table
CONDITION	basicNonReserved, GRANT, Revoke GRANT
CONTENT	basicNonReserved, xml parse, xml serialize
CONTROL	basicNonReserved, Import another Database
COUNT	standard aggregate function, basicNonReserved
COUNT_BIG	standard aggregate function, basicNonReserved
CURRENT	basicNonReserved, window frame bound
DATA	ALTER DATA WRAPPER , basicNonReserved, create data wrapper, create server, Drop data wrapper
DATABASE	ALTER DATABASE , basicNonReserved, create database, Import another Database, use database
DEFAULT	xml namespace element, non-reserved identifier, object table column, post create column, procedure parameter, xml table column
DELIMITER	basicNonReserved, text aggreate function, text table
DENSE_RANK	analytic aggregate function, basicNonReserved
DISABLED	alter, ALTER TRIGGER , basicNonReserved
DOCUMENT	basicNonReserved, xml parse, xml serialize
DOMAIN	basicNonReserved, create a domain or type alias
EMPTY	basicNonReserved, xml query
ENABLED	alter, ALTER TRIGGER , basicNonReserved
ENCODING	basicNonReserved, text aggreate function, xml serialize
EPOCH	basicNonReserved, function
EVERY	standard aggregate function, basicNonReserved
EXCEPTION	compound statement, declare statement, non-reserved identifier
EXCLUDING	basicNonReserved, xml serialize
EXPLAIN	basicNonReserved, explain
EXTRACT	basicNonReserved, function
FIRST	basicNonReserved, fetch clause, sort specification
FOLLOWING	basicNonReserved, window frame bound
FORMAT	basicNonReserved, explain option
GEOGRAPHY	non-reserved identifier, simple data type
GEOMETRY	non-reserved identifier, simple data type
HEADER	basicNonReserved, text aggreate function, text table column, text table
INCLUDING	basicNonReserved, xml serialize
INDEX	other constraints, inline constraint, non-reserved identifier
INSTEAD	alter, ALTER TRIGGER , basicNonReserved, create trigger
JAAS	basicNonReserved, with role
JSON	non-reserved identifier, simple data type
JSONARRAY_AGG	basicNonReserved, ordered aggregate function
JSONOBJECT	basicNonReserved, json object
JSONTABLE	basicNonReserved, json table
KEY	basicNonReserved, create temporary table, foreign key, inline constraint, primary key
LAST	basicNonReserved, sort specification
LISTAGG	basicNonReserved, function
MASK	basicNonReserved, GRANT, Revoke GRANT
MAX	standard aggregate function, basicNonReserved, make dep options
MIN	standard aggregate function, basicNonReserved
NAME	basicNonReserved, function, xml element
NAMESPACE	basicNonReserved, option namespace
NEXT	basicNonReserved, fetch clause
NONE	basicNonReserved
NULLS	basicNonReserved, sort specification
OBJECT	non-reserved identifier, simple data type
OBJECTTABLE	basicNonReserved, object table
ORDINALITY	basicNonReserved, json table column, text table column, xml table column
PASSING	basicNonReserved, object table, xml query, xml query, xml table
PATH	basicNonReserved, json table column, xml table column
POLICY	basicNonReserved, CREATE POLICY, DROP POLICY
POSITION	basicNonReserved, function
PRECEDING	basicNonReserved, window frame bound
PRESERVE	basicNonReserved, create temporary table
PRIVILEGES	basicNonReserved, GRANT, Revoke GRANT
QUARTER	basicNonReserved, function
QUERYSTRING	basicNonReserved, querystring function
QUOTE	basicNonReserved, text aggreate function, text table
RAISE	basicNonReserved, raise statement
RANK	analytic aggregate function, basicNonReserved
RENAME	ALTER PROCEDURE , ALTER TABLE , basicNonReserved
REPOSITORY	basicNonReserved, Import foreign schema
RESULT	basicNonReserved, procedure parameter
ROLE	basicNonReserved, create role , drop role , with role
ROW_NUMBER	analytic aggregate function, basicNonReserved
SCHEMA	basicNonReserved, create schema, drop schema, GRANT, Import foreign schema, Revoke GRANT, set schema
SELECTOR	basicNonReserved, text table column, text table
SERIAL	alter column options, table element, view element, non-reserved identifier, temporary table element
SKIP	basicNonReserved, text table
SQL_TSI_DAY	basicNonReserved, time interval
SQL_TSI_FRAC_SECOND	basicNonReserved, time interval
SQL_TSI_HOUR	basicNonReserved, time interval
SQL_TSI_MINUTE	basicNonReserved, time interval
SQL_TSI_MONTH	basicNonReserved, time interval
SQL_TSI_QUARTER	basicNonReserved, time interval
SQL_TSI_SECOND	basicNonReserved, time interval
SQL_TSI_WEEK	basicNonReserved, time interval
SQL_TSI_YEAR	basicNonReserved, time interval
STDDEV_POP	standard aggregate function, basicNonReserved
STDDEV_SAMP	standard aggregate function, basicNonReserved
SUBSTRING	basicNonReserved, function
SUM	standard aggregate function, basicNonReserved
TEXT	basicNonReserved, explain option
TEXTAGG	basicNonReserved, text aggreate function
TEXTTABLE	basicNonReserved, text table
TIMESTAMPADD	basicNonReserved, function
TIMESTAMPDIFF	basicNonReserved, function
TO_BYTES	basicNonReserved, function
TO_CHARS	basicNonReserved, function
TRANSLATOR	ALTER DATA WRAPPER , basicNonReserved, create data wrapper, create server, Drop data wrapper
TRIM	basicNonReserved, function, text table column, text table
TYPE	alter column options, basicNonReserved, create data wrapper, create server
UNBOUNDED	basicNonReserved, window frame bound
UPSERT	basicNonReserved, insert statement
USAGE	basicNonReserved, GRANT, Revoke GRANT
USE	basicNonReserved, use database
VARIADIC	basicNonReserved, procedure parameter
VAR_POP	standard aggregate function, basicNonReserved
VAR_SAMP	standard aggregate function, basicNonReserved
VERSION	basicNonReserved, create database, create server, Import another Database, use database, xml serialize
VIEW	alter, ALTER TABLE , basicNonReserved, create view, drop table
WELLFORMED	basicNonReserved, xml parse
WIDTH	basicNonReserved, text table column
XMLDECLARATION	basicNonReserved, xml serialize
YAML	basicNonReserved, explain option

12.3. Reserved Keywords For Future Use
复制链接

Expand

ALLOCATE	ARE	ASENSITIVE
ASYMETRIC	AUTHORIZATION	BINARY
CALLED	CASCADED	CHARACTER
CHECK	CLOSE	COLLATE
CONNECT	CORRESPONDING	CRITERIA
CURRENT_USER	CURSOR	CYCLE
DATALINK	DEALLOCATE	DEC
DEREF	DESCRIBE	DETERMINISTIC
DISCONNECT	DLNEWCOPY	DLPREVIOUSCOPY
DLURLCOMPLETE	DLURLCOMPLETEONLY	DLURLCOMPLETEWRITE
DLURLPATH	DLURLPATHONLY	DLURLPATHWRITE
DLURLSCHEME	DLURLSERVER	DLVALUE
DYNAMIC	ELEMENT	EXTERNAL
FREE	GET	HAS
HOLD	IDENTITY	INDICATOR
INPUT	INSENSITIVE	INT
INTERVAL	ISOLATION	LARGE
LOCALTIME	LOCALTIMESTAMP	MATCH
MEMBER	METHOD	MODIFIES
MODULE	MULTISET	NATIONAL
NATURAL	NCHAR	NCLOB
NEW	NUMERIC	OLD
OPEN	OUTPUT	OVERLAPS
PRECISION	PREPARE	READS
RECURSIVE	REFERENCING	RELEASE
ROLLBACK	SAVEPOINT	SCROLL
SEARCH	SENSITIVE	SESSION_USER
SPECIFIC	SPECIFICTYPE	SQL
START	STATIC	SUBMULTILIST
SYMETRIC	SYSTEM	SYSTEM_USER
TIMEZONE_HOUR	TIMEZONE_MINUTE	TRANSLATION
TREAT	VALUE	VARYING
WHENEVER	WINDOW	XMLBINARY
XMLDOCUMENT	XMLITERATE	XMLVALIDATE

12.4. Tokens
复制链接

Expand

Name	Definition	Usage
all in group identifier	<identifier> <period> <star>	all in group
binary string literal	"X" \| "x" "\'" (<hexit> <hexit>)+ "\'"	non numeric literal
colon	":"	make dep options, statement
comma	","	alter child options list, alter options list, ARRAY expression constructor, column list, create procedure, typed element list, CREATE POLICY, create table body, create temporary table, create view body, derived column list, sql exception, named parameter list, explain, expression list, from clause, function, GRANT, identifier list, json table, limit clause, nested expression, object table, option clause, options clause, order by clause, simple data type, query expression, query primary, querystring function, Revoke GRANT, select clause, set clause list, in predicate, text aggreate function, text table, xml attributes, xml element, xml query, xml forest, xml namespaces, xml query, xml table
concat_op	"\|\|"	common value expression
decimal numeric literal	(<digit>)* <period> <unsigned integer literal>	unsigned numeric literal
digit	\["0"\-"9"\]
dollar	"$"	parameter reference
double_amp_op	"&&"	common value expression
eq	"="	assignment statement, callable statement, declare statement, named parameter list, comparison operator, set clause list
escaped function	"{" "fn"	unsigned value expression primary
escaped join	"{" "oj"	table reference
escaped type	"{" ("d" \| "t" \| "ts" \| "b")	non numeric literal
approximate numeric literal	<digit> <period> <unsigned integer literal> \["e","E"\] (<plus> \| <minus>)? <unsigned integer literal>	unsigned numeric literal
ge	">="	comparison operator
gt	">"	named parameter list, comparison operator
hexit	\["a"\-"f","A"\-"F"\] \| <digit>
identifier	<quoted_id> (<period> <quoted_id>)*	create a domain or type alias, identifier, data type, Unqualified identifier, unsigned value expression primary
id_part	("" \| "@" \| "#" \| <letter>) (<letter> \| "" \| <digit>)*
lbrace	"{"	callable statement, match predicate
le	"⇐"	comparison operator
letter	\["a"\-"z","A"\-"Z"\] \| \["\u0153"\-"\ufffd"\]
lparen	"("	standard aggregate function, alter child options list, alter options list, analytic aggregate function, ARRAY expression constructor, array table, callable statement, column list, other constraints, create procedure, CREATE POLICY, create table body, create temporary table, create view body, explain, filter clause, function, group by clause, if statement, json object, json table, loop statement, make dep options, nested expression, object table, options clause, ordered aggregate function, simple data type, query primary, querystring function, in predicate, call statement, subquery, quantified comparison predicate, table subquery, table primary, text aggreate function, text table, unescapedFunction, while statement, window specification, with list element, xml attributes, xml element, xml query, xml forest, xml namespaces, xml parse, xml query, xml serialize, xml table
lsbrace	"["	ARRAY expression constructor, basic data type, data type, value expression primary
lt	"<"	comparison operator
minus	"-"	plus or minus
ne	"<>"	comparison operator
ne2	"!="	comparison operator
period	"."
plus	"+"	plus or minus
qmark	"?"	callable statement, parameter reference
quoted_id	<id_part> \| "\"" ("\"\"" \| ~\["\""\])+ "\""
rbrace	"}"	callable statement, match predicate, non numeric literal, table reference, unsigned value expression primary
rparen	")"	standard aggregate function, alter child options list, alter options list, analytic aggregate function, ARRAY expression constructor, array table, callable statement, column list, other constraints, create procedure, CREATE POLICY, create table body, create temporary table, create view body, explain, filter clause, function, group by clause, if statement, json object, json table, loop statement, make dep options, nested expression, object table, options clause, ordered aggregate function, simple data type, query primary, querystring function, in predicate, call statement, subquery, quantified comparison predicate, table subquery, table primary, text aggreate function, text table, unescapedFunction, while statement, window specification, with list element, xml attributes, xml element, xml query, xml forest, xml namespaces, xml parse, xml query, xml serialize, xml table
rsbrace	"]"	ARRAY expression constructor, basic data type, data type, value expression primary
semicolon	";"	delimited statement
slash	"/"	star or slash
star	"*"	standard aggregate function, dynamic data statement, select clause, star or slash
string literal	("N" \| "E")? "\'" ("\'\'" \| ~\["\'"\])* "\'"	string
unsigned integer literal	(<digit>)+	unsigned integer, unsigned numeric literal

12.5. Production Cross-Reference
复制链接

Expand

Name	Usage
add set child option	alter child options list
add set option	alter options list
standard aggregate function	unescapedFunction
all in group	select sublist
alter	directly executable statement
ADD column	ALTER TABLE
ADD constraint	ALTER TABLE
alter child option pair	add set child option
alter child options list	alter column options
alter column options	ALTER PROCEDURE , ALTER TABLE
ALTER DATABASE	alterStatement
DROP column	ALTER TABLE
alter option pair	add set option
alter options list	ALTER DATABASE , ALTER PROCEDURE , ALTER SERVER , ALTER TABLE , ALTER DATA WRAPPER
ALTER PROCEDURE	alterStatement
rename column options	ALTER PROCEDURE , ALTER TABLE
RENAME Table	ALTER TABLE
ALTER SERVER	alterStatement
alterStatement	ddl statement
ALTER TABLE	alterStatement
ALTER DATA WRAPPER	alterStatement
ALTER TRIGGER	alterStatement
analytic aggregate function	unescapedFunction
ARRAY expression constructor	unsigned value expression primary
array table	table primary
assignment statement	delimited statement
assignment statement operand	assignment statement, declare statement
basicNonReserved	create a domain or type alias, non-reserved identifier, data type
between predicate	boolean primary
boolean primary	CREATE POLICY, filter clause, boolean factor
branching statement	delimited statement
callable statement
case expression	unsigned value expression primary
character	match predicate, text aggreate function, text table
column list	other constraints, create temporary table, foreign key, insert statement, primary key, with list element
common value expression	between predicate, boolean primary, comparison predicate, sql exception, function, is distinct, match predicate, like regex predicate, in predicate, text table
comparison predicate	boolean primary
boolean term	boolean value expression
boolean value expression	condition
compound statement	statement, directly executable statement
other constraints	table constraint
table element	ADD column, create table body
create procedure	ddl statement
create data wrapper	ddl statement
create database	ddl statement
create a domain or type alias	ddl statement
typed element list	array table, dynamic data statement
create foreign or global temporary table	create table
create foreign temp table	directly executable statement
option namespace	ddl statement
CREATE POLICY	ddl statement
create role	ddl statement
create schema	ddl statement
create server	ddl statement
create table	ddl statement
create table body	create foreign or global temporary table, create foreign temp table
create temporary table	directly executable statement
create trigger	ddl statement, directly executable statement
create view	create table
create view body	create view
view element	create view body
condition	expression, having clause, if statement, qualified table, searched case expression, where clause, while statement
cross join	joined table
ddl statement	ddl statement
declare statement	delimited statement
delete statement	assignment statement operand, directly executable statement
delimited statement	statement
derived column	derived column list, object table, querystring function, text aggreate function, xml attributes, xml query, xml query, xml table
derived column list	json object, xml forest
drop option	alter child options list
Drop data wrapper	ddl statement
drop option	alter options list
DROP POLICY	ddl statement
drop procedure	ddl statement
drop role	ddl statement
drop schema	ddl statement
drop server	ddl statement
drop table	directly executable statement
drop table	ddl statement
dynamic data statement	data statement
raise error statement	delimited statement
sql exception	assignment statement operand, exception reference
exception reference	sql exception, raise statement
named parameter list	callable statement, call statement
exists predicate	boolean primary
explain
explain option	explain
expression	standard aggregate function, ARRAY expression constructor, assignment statement operand, case expression, derived column, dynamic data statement, raise error statement, named parameter list, expression list, function, nested expression, object table column, ordered aggregate function, post create column, procedure parameter, querystring function, return statement, searched case expression, select derived column, set clause list, sort key, quantified comparison predicate, unescapedFunction, xml table column, xml element, xml parse, xml serialize
expression list	callable statement, other constraints, function, group by clause, query primary, call statement, window specification
fetch clause	limit clause
filter clause	function, unescapedFunction
for each row trigger action	alter, ALTER TRIGGER , create trigger
foreign key	table constraint
from clause	query
function	unescapedFunction, unsigned value expression primary
GRANT	ddl statement
group by clause	query
having clause	query
identifier	alter, alter child option pair, alter column options, ALTER DATABASE , DROP column, alter option pair, ALTER PROCEDURE , rename column options, RENAME Table, ALTER SERVER , ALTER TABLE , ALTER DATA WRAPPER , ALTER TRIGGER , array table, assignment statement, branching statement, callable statement, column list, compound statement, table element, create data wrapper, create database, typed element list, create foreign temp table, option namespace, CREATE POLICY, create schema, create trigger, view element, declare statement, delete statement, derived column, drop option, Drop data wrapper, drop option, DROP POLICY, drop procedure, drop role , drop schema, drop server, drop table, drop table, dynamic data statement, exception reference, named parameter list, foreign key, function, GRANT, identifier list, Import another Database, Import foreign schema, insert statement, into clause, json table column, json table, loop statement, xml namespace element, object table column, object table, option clause, option pair, procedure parameter, procedure result column, query primary, Revoke GRANT, select derived column, set clause list, statement, call statement, table subquery, table constraint, temporary table element, text aggreate function, text table column, text table, table name, update statement, use database, set schema, with list element, xml table column, xml element, xml serialize, xml table
identifier list	create schema, with role
if statement	statement
Import another Database	ddl statement
Import foreign schema	ddl statement
inline constraint	post create column
insert statement	assignment statement operand, directly executable statement
integer parameter	fetch clause, limit clause
unsigned integer	dynamic data statement, function, GRANT, integer parameter, make dep options, parameter reference, simple data type, text table column, text table, window frame bound
time interval	function
into clause	query
is distinct	boolean primary
is null predicate	boolean primary
joined table	table primary, table reference
json table column	json table
json object	function
json table	table primary
limit clause	query expression body
loop statement	statement
make dep options	option clause, table primary
match predicate	boolean primary
xml namespace element	xml namespaces
nested expression	unsigned value expression primary
non numeric literal	alter child option pair, alter option pair, option pair, value expression primary
non-reserved identifier	identifier, Unqualified identifier, unsigned value expression primary
boolean factor	boolean term
object table column	object table
object table	table primary
comparison operator	comparison predicate, quantified comparison predicate
option clause	callable statement, delete statement, insert statement, query expression body, call statement, update statement
option pair	options clause
options clause	create procedure, create data wrapper, create database, create schema, create server, create table body, create view, create view body, Import foreign schema, post create column, procedure parameter, procedure result column, table constraint
order by clause	function, ordered aggregate function, query expression body, text aggreate function, window specification
ordered aggregate function	unescapedFunction
parameter reference	unsigned value expression primary
basic data type	typed element list, json table column, object table column, data type, temporary table element, text table column, xml table column
data type	alter column options, table element, create procedure, create a domain or type alias, view element, declare statement, function, procedure parameter, procedure result column, unescapedFunction
simple data type	basic data type
numeric value expression	common value expression, value expression primary
plus or minus	alter child option pair, alter option pair, option pair, numeric value expression, value expression primary
post create column	table element, view element
primary key	table constraint
procedure parameter	create procedure
procedure result column	create procedure
qualified table	joined table
query	query primary
query expression	alter, ALTER TABLE , ARRAY expression constructor, assignment statement operand, create view, insert statement, loop statement, subquery, table subquery, directly executable statement, with list element
query expression body	query expression, query primary
query primary	query term
querystring function	function
query term	query expression body
raise statement	delimited statement
grant type	GRANT, Revoke GRANT
with role	create role
like regex predicate	boolean primary
return statement	delimited statement
Revoke GRANT	ddl statement
searched case expression	unsigned value expression primary
select clause	query
select derived column	select sublist
select sublist	select clause
set clause list	dynamic data statement, update statement
in predicate	boolean primary
sort key	sort specification
sort specification	order by clause
data statement	delimited statement
statement	alter, ALTER PROCEDURE , compound statement, create procedure, for each row trigger action, if statement, loop statement, while statement
call statement	assignment statement, subquery, table subquery, directly executable statement
string	character, create database, option namespace, create server, function, GRANT, Import another Database, json table column, json table, xml namespace element, non numeric literal, object table column, object table, text table column, text table, use database, xml table column, xml query, xml query, xml serialize, xml table
subquery	exists predicate, in predicate, quantified comparison predicate, unsigned value expression primary
quantified comparison predicate	boolean primary
table subquery	table primary
table constraint	ADD constraint, create table body, create view body
temporary table element	create temporary table
table primary	cross join, joined table
table reference	from clause, qualified table
text aggreate function	unescapedFunction
text table column	text table
text table	table primary
term	numeric value expression
star or slash	term
table name	table primary
unescapedFunction	unsigned value expression primary
Unqualified identifier	create procedure, create data wrapper, create foreign or global temporary table, create foreign temp table, create role , create server, create temporary table, create view
unsigned numeric literal	alter child option pair, alter option pair, option pair, value expression primary
unsigned value expression primary	integer parameter, value expression primary
update statement	assignment statement operand, directly executable statement
use database	ddl statement
set schema	ddl statement
directly executable statement	explain, data statement
value expression primary	array table, json table, term
where clause	delete statement, query, update statement
while statement	statement
window frame	window specification
window frame bound	window frame
window specification	unescapedFunction
with list element	query expression
xml attributes	xml element
xml table column	xml table
xml element	function
xml query	boolean primary
xml forest	function
xml namespaces	xml element, xml query, xml forest, xml query, xml table
xml parse	function
xml query	function
xml serialize	function
xml table	table primary

12.6. Productions
复制链接

12.6.1. string ::=
复制链接

<string literal>

A string literal value. Use '' to escape ' in the string.

Example:

'a string'

'it''s a string'

12.6.2. non-reserved identifier ::=
复制链接

Allows non-reserved keywords to be parsed as identifiers

Example: SELECT COUNT FROM …

12.6.3. basicNonReserved ::=
复制链接

12.6.4. Unqualified identifier ::=
复制链接

Unqualified name of a single entity.

Example:

"tbl"

12.6.5. identifier ::=
复制链接

Partial or full name of a single entity.

Example:

tbl.col

"tbl"."col"

12.6.6. create trigger ::=
复制链接

CREATE TRIGGER ( <identifier> )? ON <identifier> ( ( INSTEAD OF ) | AFTER ) ( INSERT | UPDATE | DELETE ) AS <for each row trigger action>

Creates a trigger action on the given target.

Example:

CREATE TRIGGER ON vw INSTEAD OF INSERT AS FOR EACH ROW BEGIN ATOMIC ... END

12.6.7. alter ::=
复制链接

ALTER ( ( VIEW <identifier> AS <query expression> ) | ( PROCEDURE <identifier> AS <statement> ) | ( TRIGGER ( <identifier> )? ON <identifier> ( ( INSTEAD OF ) | AFTER ) ( INSERT | UPDATE | DELETE ) ( ( AS <for each row trigger action> ) | ENABLED | DISABLED ) ) )

Alter the given target.

Example:

ALTER VIEW vw AS SELECT col FROM tbl

12.6.8. for each row trigger action ::=
复制链接

FOR EACH ROW ( ( BEGIN ( ATOMIC )? ( <statement> )* END ) | <statement> )

Defines an action to perform on each row.

Example:

FOR EACH ROW BEGIN ATOMIC ... END

12.6.9. explain ::=
复制链接

EXPLAIN ( <lparen> <explain option> ( <comma> <explain option> )* <rparen> )? <directly executable statement>

Returns the query plan for the statement

Example: EXPLAIN select 1

12.6.10. explain option ::=
复制链接

( ANALYZE ( TRUE | FALSE )? )
( FORMAT ( XML | TEXT | YAML )? )

Option for the explain statement

Example: FORMAT YAML

12.6.11. directly executable statement ::=
复制链接

A statement that can be executed at runtime.

Example:

SELECT * FROM tbl

12.6.12. drop table ::=
复制链接

DROP TABLE <identifier>

Drop the given table.

Example:

DROP TABLE #temp

12.6.13. create temporary table ::=
复制链接

CREATE ( LOCAL )? TEMPORARY TABLE <Unqualified identifier> <lparen> <temporary table element> ( <comma> <temporary table element> )* ( <comma> PRIMARY KEY <column list> )? <rparen> ( ON COMMIT PRESERVE ROWS )?

Creates a temporary table.

Example:

CREATE LOCAL TEMPORARY TABLE tmp (col integer)

12.6.14. temporary table element ::=
复制链接

<identifier> ( <basic data type> | SERIAL ) ( NOT NULL )?

Defines a temporary table column.

Example:

col string NOT NULL

12.6.15. raise error statement ::=
复制链接

ERROR <expression>

Raises an error with the given message.

Example:

ERROR 'something went wrong'

12.6.16. raise statement ::=
复制链接

RAISE ( SQLWARNING )? <exception reference>

Raises an error or warning with the given message.

Example:

RAISE SQLEXCEPTION 'something went wrong'

12.6.17. exception reference ::=
复制链接

<identifier>
<sql exception>

a reference to an exception

Example:

SQLEXCEPTION 'something went wrong' SQLSTATE '00X', 2

12.6.18. sql exception ::=
复制链接

SQLEXCEPTION <common value expression> ( SQLSTATE <common value expression> ( <comma> <common value expression> )? )? ( CHAIN <exception reference> )?

creates a sql exception or warning with the specified message, state, and code

Example:

SQLEXCEPTION 'something went wrong' SQLSTATE '00X', 2

12.6.19. statement ::=
复制链接

( ( <identifier> <colon> )? ( <loop statement> | <while statement> | <compound statement> ) )
<if statement> | <delimited statement>

A procedure statement.

Example:

IF (x = 5) BEGIN ... END

12.6.20. delimited statement ::=
复制链接

( <assignment statement> | <data statement> | <raise error statement> | <raise statement> | <declare statement> | <branching statement> | <return statement> ) <semicolon>

A procedure statement terminated by ;.

Example:

SELECT * FROM tbl;

12.6.21. compound statement ::=
复制链接

BEGIN ( ( NOT )? ATOMIC )? ( <statement> )* ( EXCEPTION <identifier> ( <statement> )* )? END

A procedure statement block contained in BEGIN END.

Example:

BEGIN NOT ATOMIC ... END

12.6.22. branching statement ::=
复制链接

( ( BREAK | CONTINUE ) ( <identifier> )? )
( LEAVE <identifier> )

A procedure branching control statement, which typically specifies a label to return control to.

Example:

BREAK x

12.6.23. return statement ::=
复制链接

RETURN ( <expression> )?

A return statement.

Example:

RETURN 1

12.6.24. while statement ::=
复制链接

WHILE <lparen> <condition> <rparen> <statement>

A procedure while statement that executes until its condition is false.

Example:

WHILE (var) BEGIN ... END

12.6.25. loop statement ::=
复制链接

LOOP ON <lparen> <query expression> <rparen> AS <identifier> <statement>

A procedure loop statement that executes over the given cursor.

Example:

LOOP ON (SELECT * FROM tbl) AS x BEGIN ... END

12.6.26. if statement ::=
复制链接

IF <lparen> <condition> <rparen> <statement> ( ELSE <statement> )?

A procedure loop statement that executes over the given cursor.

Example:

IF (boolVal) BEGIN variables.x = 1 END ELSE BEGIN variables.x = 2 END

12.6.27. declare statement ::=
复制链接

DECLARE ( <data type> | EXCEPTION ) <identifier> ( <eq> <assignment statement operand> )?

A procedure declaration statement that creates a variable and optionally assigns a value.

Example:

DECLARE STRING x = 'a'

12.6.28. assignment statement ::=
复制链接

<identifier> <eq> ( <assignment statement operand> | ( <call statement> ( ( WITH | WITHOUT ) RETURN )? ) )

Assigns a variable a value in a procedure.

Example:

x = 'b'

12.6.29. assignment statement operand ::=
复制链接

A value or command that can be used in an assignment. {note}All assignments except for expression are deprecated.{note}

12.6.30. data statement ::=
复制链接

( <directly executable statement> | <dynamic data statement> ) ( ( WITH | WITHOUT ) RETURN )?

A procedure statement that executes a SQL statement. An update statement can have its update count accessed via the ROWCOUNT variable.

12.6.31. dynamic data statement ::=
复制链接

( EXECUTE | EXEC ) ( STRING | IMMEDIATE )? <expression> ( AS <typed element list> ( INTO <identifier> )? )? ( USING <set clause list> )? ( UPDATE ( <unsigned integer> | <star> ) )?

A procedure statement that can execute arbitrary sql.

Example:

EXECUTE IMMEDIATE 'SELECT * FROM tbl' AS x STRING INTO #temp

12.6.32. set clause list ::=
复制链接

<identifier> <eq> <expression> ( <comma> <identifier> <eq> <expression> )*

A list of value assignments.

Example:

col1 = 'x', col2 = 'y' ...

12.6.33. typed element list ::=
复制链接

<identifier> <basic data type> ( <comma> <identifier> <basic data type> )*

A list of typed elements.

Example:

col1 string, col2 integer ...

12.6.34. callable statement ::=
复制链接

<lbrace> ( <qmark> <eq> )? CALL <identifier> ( <lparen> ( <named parameter list> | ( <expression list> )? ) <rparen> )? <rbrace> ( <option clause> )?

A callable statement defined using JDBC escape syntax.

Example:

{? = CALL proc}

12.6.35. call statement ::=
复制链接

( ( EXEC | EXECUTE | CALL ) <identifier> <lparen> ( <named parameter list> | ( <expression list> )? ) <rparen> ) ( <option clause> )?

Executes the procedure with the given parameters.

Example:

CALL proc('a', 1)

12.6.36. named parameter list ::=
复制链接

( <identifier> <eq> ( <gt> )? <expression> ( <comma> <identifier> <eq> ( <gt> )? <expression> )* )

A list of named parameters.

Example:

param1 => 'x', param2 => 1

12.6.37. insert statement ::=
复制链接

( INSERT | MERGE | UPSERT ) INTO <identifier> ( <column list> )? <query expression> ( <option clause> )?

Inserts values into the given target.

Example:

INSERT INTO tbl (col1, col2) VALUES ('a', 1)

12.6.38. expression list ::=
复制链接

<expression> ( <comma> <expression> )*

A list of expressions.

Example:

col1, 'a', ...

12.6.39. update statement ::=
复制链接

UPDATE <identifier> ( ( AS )? <identifier> )? SET <set clause list> ( <where clause> )? ( <option clause> )?

Update values in the given target.

Example:

UPDATE tbl SET (col1 = 'a') WHERE col2 = 1

12.6.40. delete statement ::=
复制链接

DELETE FROM <identifier> ( ( AS )? <identifier> )? ( <where clause> )? ( <option clause> )?

Delete rows from the given target.

Example:

DELETE FROM tbl WHERE col2 = 1

12.6.41. query expression ::=
复制链接

( WITH <with list element> ( <comma> <with list element> )* )? <query expression body>

A declarative query for data.

Example:

SELECT * FROM tbl WHERE col2 = 1

12.6.42. with list element ::=
复制链接

<identifier> ( <column list> )? AS <lparen> <query expression> <rparen>

A query expression for use in the enclosing query.

Example:

X (Y, Z) AS (SELECT 1, 2)

12.6.43. query expression body ::=
复制链接

<query term> ( ( UNION | EXCEPT ) ( ALL | DISTINCT )? <query term> )* ( <order by clause> )? ( <limit clause> )? ( <option clause> )?

The body of a query expression, which can optionally be ordered and limited.

Example:

SELECT * FROM tbl ORDER BY col1 LIMIT 1

12.6.44. query term ::=
复制链接

<query primary> ( INTERSECT ( ALL | DISTINCT )? <query primary> )*

Used to establish INTERSECT precedence.

Example:

SELECT * FROM tbl

SELECT * FROM tbl1 INTERSECT SELECT * FROM tbl2

12.6.45. query primary ::=
复制链接

<query>
( VALUES <lparen> <expression list> <rparen> ( <comma> <lparen> <expression list> <rparen> )* )
( TABLE <identifier> )
( <lparen> <query expression body> <rparen> )

A declarative source of rows.

Example:

TABLE tbl

SELECT * FROM tbl1

12.6.46. query ::=
复制链接

<select clause> ( <into clause> )? ( <from clause> ( <where clause> )? ( <group by clause> )? ( <having clause> )? )?

A SELECT query.

Example:

SELECT col1, max(col2) FROM tbl GROUP BY col1

12.6.47. into clause ::=
复制链接

INTO <identifier>

Used to direct the query into a table. {note}This is deprecated. Use INSERT INTO with a query expression instead.{note}

Example:

INTO tbl

12.6.48. select clause ::=
复制链接

SELECT ( ALL | DISTINCT )? ( <star> | ( <select sublist> ( <comma> <select sublist> )* ) )

The columns returned by a query. Can optionally be distinct.

Example:

SELECT *

SELECT DISTINCT a, b, c

12.6.49. select sublist ::=
复制链接

<select derived column>
<all in group >

An element in the select clause

Example:

tbl.*

tbl.col AS x

12.6.50. select derived column ::=
复制链接

( <expression> ( ( AS )? <identifier> )? )

A select clause item that selects a single column. {note}This is slightly different than a derived column in that the AS keyword is optional.{note}

Example:

tbl.col AS x

12.6.51. derived column ::=
复制链接

( <expression> ( AS <identifier> )? )

An optionally named expression.

Example:

tbl.col AS x

12.6.52. all in group ::=
复制链接

<all in group identifier>

A select sublist that can select all columns from the given group.

Example:

tbl.*

12.6.53. ordered aggregate function ::=
复制链接

( XMLAGG | ARRAY_AGG | JSONARRAY_AGG ) <lparen> <expression> ( <order by clause> )? <rparen>

An aggregate function that can optionally be ordered.

Example:

XMLAGG(col1) ORDER BY col2

ARRAY_AGG(col1)

12.6.54. text aggreate function ::=
复制链接

TEXTAGG <lparen> ( FOR )? <derived column> ( <comma> <derived column> )* ( DELIMITER <character> )? ( ( QUOTE <character> ) | ( NO QUOTE ) )? ( HEADER )? ( ENCODING <identifier> )? ( <order by clause> )? <rparen>

An aggregate function for creating separated value clobs.

Example:

TEXTAGG (col1 as t1, col2 as t2 DELIMITER ',' HEADER)

12.6.55. standard aggregate function ::=
复制链接

( ( COUNT | COUNT_BIG ) <lparen> <star> <rparen> )
( ( COUNT | COUNT_BIG | SUM | AVG | MIN | MAX | EVERY | STDDEV_POP | STDDEV_SAMP | VAR_SAMP | VAR_POP | SOME | ANY ) <lparen> ( DISTINCT | ALL )? <expression> <rparen> )

A standard aggregate function.

Example:

COUNT(*)

12.6.56. analytic aggregate function ::=
复制链接

( ROW_NUMBER | RANK | DENSE_RANK | PERCENT_RANK | CUME_DIST ) <lparen> <rparen>

An analytic aggregate function.

Example:

ROW_NUMBER()

12.6.57. filter clause ::=
复制链接

FILTER <lparen> WHERE <boolean primary> <rparen>

An aggregate filter clause applied prior to accumulating the value.

Example:

FILTER (WHERE col1='a')

12.6.58. from clause ::=
复制链接

FROM ( <table reference> ( <comma> <table reference> )* )

A query from clause containing a list of table references.

Example:

FROM a, b

FROM a right outer join b, c, d join e".</p>

12.6.59. table reference ::=
复制链接

( <escaped join> <joined table> <rbrace> )
<joined table>

An optionally escaped joined table.

Example:

a inner join b

12.6.60. joined table ::=
复制链接

<table primary> ( <cross join> | <qualified table> )*

A table or join.

Example:

a inner join b

12.6.61. cross join ::=
复制链接

( ( CROSS | UNION ) JOIN <table primary> )

A cross join.

Example:

a CROSS JOIN b

12.6.62. qualified table ::=
复制链接

( ( ( RIGHT ( OUTER )? ) | ( LEFT ( OUTER )? ) | ( FULL ( OUTER )? ) | INNER )? JOIN <table reference> ON <condition> )

An INNER or OUTER join.

Example:

a inner join b

12.6.63. table primary ::=
复制链接

( <text table> | <array table> | <json table> | <xml table> | <object table> | <table name> | <table subquery> | ( <lparen> <joined table> <rparen> ) ) ( ( MAKEDEP <make dep options> ) | MAKENOTDEP )? ( ( MAKEIND <make dep options> ) )?

A single source of rows.

Example:

12.6.64. make dep options ::=
复制链接

( <lparen> ( MAX <colon> <unsigned integer> )? ( ( NO )? JOIN )? <rparen> )?

options for the make dep hint

Example:

(min:10000)

12.6.65. xml serialize ::=
复制链接

XMLSERIALIZE <lparen> ( DOCUMENT | CONTENT )? <expression> ( AS ( STRING | VARCHAR | CLOB | VARBINARY | BLOB ) )? ( ENCODING <identifier> )? ( VERSION <string> )? ( ( INCLUDING | EXCLUDING ) XMLDECLARATION )? <rparen>

Serializes an XML value.

Example:

XMLSERIALIZE(col1 AS CLOB)

12.6.66. array table ::=
复制链接

ARRAYTABLE <lparen> ( ROW | ROWS )? <value expression primary> COLUMNS <typed element list> <rparen> ( AS )? <identifier>

The ARRAYTABLE table function creates tabular results from arrays. It can be used as a nested table reference.

Example:

ARRAYTABLE (col1 COLUMNS x STRING) AS y

12.6.67. json table ::=
复制链接

JSONTABLE <lparen> <value expression primary> <comma> <string> ( <comma> ( TRUE | FALSE ) )? COLUMNS <json table column> ( <comma> <json table column> )* <rparen> ( AS )? <identifier>

The JSONTABLE table function creates tabular results from JSON. It can be used as a nested table reference.

Example:

JSONTABLE (col1, '$..book', false COLUMNS x STRING) AS y

12.6.68. json table column ::=
复制链接

<identifier> ( ( FOR ORDINALITY ) | ( <basic data type> ( PATH <string> )? ) )

json table column.

Example:

col FOR ORDINALITY

12.6.69. text table ::=
复制链接

TEXTTABLE <lparen> <common value expression> ( SELECTOR <string> )? COLUMNS <text table column> ( <comma> <text table column> )* ( ( NO ROW DELIMITER ) | ( ROW DELIMITER <character> ) )? ( DELIMITER <character> )? ( ( ESCAPE <character> ) | ( QUOTE <character> ) )? ( HEADER ( <unsigned integer> )? )? ( SKIP <unsigned integer> )? ( NO TRIM )? <rparen> ( AS )? <identifier>

The TEXTTABLE table function creates tabular results from text. It can be used as a nested table reference.

Example:

TEXTTABLE (file COLUMNS x STRING) AS y

12.6.70. text table column ::=
复制链接

<identifier> ( ( FOR ORDINALITY ) | ( ( HEADER <string> )? <basic data type> ( WIDTH <unsigned integer> ( NO TRIM )? )? ( SELECTOR <string> <unsigned integer> )? ) )

A text table column.

Example:

x INTEGER WIDTH 6

12.6.71. xml query ::=
复制链接

XMLEXISTS <lparen> ( <xml namespaces> <comma> )? <string> ( PASSING <derived column> ( <comma> <derived column> )* )? <rparen>

Executes an XQuery to return an XML result.

Example:

XMLQUERY('<a>...</a>' PASSING doc)

12.6.72. xml query ::=
复制链接

XMLQUERY <lparen> ( <xml namespaces> <comma> )? <string> ( PASSING <derived column> ( <comma> <derived column> )* )? ( ( NULL | EMPTY ) ON EMPTY )? <rparen>

Executes an XQuery to return an XML result.

Example:

XMLQUERY('<a>...</a>' PASSING doc)

12.6.73. object table ::=
复制链接

OBJECTTABLE <lparen> ( LANGUAGE <string> )? <string> ( PASSING <derived column> ( <comma> <derived column> )* )? COLUMNS <object table column> ( <comma> <object table column> )* <rparen> ( AS )? <identifier>

Returns table results by processing a script.

Example:

OBJECTTABLE('z' PASSING val AS z COLUMNS col OBJECT 'teiid_row') AS X

12.6.74. object table column ::=
复制链接

<identifier> <basic data type> <string> ( DEFAULT <expression> )?

object table column.

Example:

y integer 'teiid_row_number'

12.6.75. xml table ::=
复制链接

XMLTABLE <lparen> ( <xml namespaces> <comma> )? <string> ( PASSING <derived column> ( <comma> <derived column> )* )? ( COLUMNS <xml table column> ( <comma> <xml table column> )* )? <rparen> ( AS )? <identifier>

Returns table results by processing an XQuery.

Example:

XMLTABLE('/a/b' PASSING doc COLUMNS col XML PATH '.') AS X

12.6.76. xml table column ::=
复制链接

<identifier> ( ( FOR ORDINALITY ) | ( <basic data type> ( DEFAULT <expression> )? ( PATH <string> )? ) )

XML table column.

Example:

y FOR ORDINALITY

12.6.77. unsigned integer ::=
复制链接

<unsigned integer literal>

An unsigned interger value.

Example:

12.6.78. table subquery ::=
复制链接

( TABLE | LATERAL )? <lparen> ( <query expression> | <call statement> ) <rparen> ( AS )? <identifier>

A table defined by a subquery.

Example:

(SELECT * FROM tbl) AS x

12.6.79. table name ::=
复制链接

( <identifier> ( ( AS )? <identifier> )? )

A table named in the FROM clause.

Example:

tbl AS x

12.6.80. where clause ::=
复制链接

WHERE <condition>

Specifies a search condition

Example:

WHERE x = 'a'

12.6.81. condition ::=
复制链接

<boolean value expression>

A boolean expression.

12.6.82. boolean value expression ::=
复制链接

<boolean term> ( OR <boolean term> )*

An optionally ORed boolean expression.

12.6.83. boolean term ::=
复制链接

<boolean factor> ( AND <boolean factor> )*

An optional ANDed boolean factor.

12.6.84. boolean factor ::=
复制链接

( NOT )? <boolean primary>

A boolean factor.

Example:

NOT x = 'a'

12.6.85. boolean primary ::=
复制链接

( <common value expression> ( <between predicate> | <match predicate> | <like regex predicate> | <in predicate> | <is null predicate> | <quantified comparison predicate> | <comparison predicate> | <is distinct> )? )
<exists predicate>
<xml query>

A boolean predicate or simple expression.

Example:

col LIKE 'a%'

12.6.86. comparison operator ::=
复制链接

<eq>
<ne>
<ne2>
<lt>
<le>
<gt>
<ge>

A comparison operator.

Example:

12.6.87. is distinct ::=
复制链接

IS ( NOT )? DISTINCT FROM <common value expression>

Is Distinct Right Hand Side

Example:

IS DISTINCT FROM expression

12.6.88. comparison predicate ::=
复制链接

<comparison operator> <common value expression>

A value comparison.

Example:

= 'a'

12.6.89. subquery ::=
复制链接

<lparen> ( <query expression> | <call statement> ) <rparen>

A subquery.

Example:

(SELECT * FROM tbl)

12.6.90. quantified comparison predicate ::=
复制链接

<comparison operator> ( ANY | SOME | ALL ) ( <subquery> | ( <lparen> <expression> <rparen> ) )

A subquery comparison.

Example:

= ANY (SELECT col FROM tbl)

12.6.91. match predicate ::=
复制链接

( NOT )? ( LIKE | ( SIMILAR TO ) ) <common value expression> ( ESCAPE <character> | ( <lbrace> ESCAPE <character> <rbrace> ) )?

Matches based upon a pattern.

Example:

LIKE 'a_'

12.6.92. like regex predicate ::=
复制链接

( NOT )? LIKE_REGEX <common value expression>

A regular expression match.

Example:

LIKE_REGEX 'a.*b'

12.6.93. character ::=
复制链接

<string>

A single character.

Example:

'a'

12.6.94. between predicate ::=
复制链接

( NOT )? BETWEEN <common value expression> AND <common value expression>

A comparison between two values.

Example:

BETWEEN 1 AND 5

12.6.95. is null predicate ::=
复制链接

IS ( NOT )? NULL

A null test.

Example:

IS NOT NULL

12.6.96. in predicate ::=
复制链接

( NOT )? IN ( <subquery> | ( <lparen> <common value expression> ( <comma> <common value expression> )* <rparen> ) )

A comparison with multiple values.

Example:

IN (1, 5)

12.6.97. exists predicate ::=
复制链接

EXISTS <subquery>

A test if rows exist.

Example:

EXISTS (SELECT col FROM tbl)

12.6.98. group by clause ::=
复制链接

GROUP BY ( ROLLUP <lparen> <expression list> <rparen> | <expression list> )

Defines the grouping columns

Example:

GROUP BY col1, col2

12.6.99. having clause ::=
复制链接

HAVING <condition>

Search condition applied after grouping.

Example:

HAVING max(col1) = 5

12.6.100. order by clause ::=
复制链接

ORDER BY <sort specification> ( <comma> <sort specification> )*

Specifices row ordering.

Example:

ORDER BY x, y DESC

12.6.101. sort specification ::=
复制链接

<sort key> ( ASC | DESC )? ( NULLS ( FIRST | LAST ) )?

Defines how to sort on a particular expression

Example:

col1 NULLS FIRST

12.6.102. sort key ::=
复制链接

<expression>

A sort expression.

Example:

col1

12.6.103. integer parameter ::=
复制链接

A literal integer or parameter reference to an integer.

Example:

12.6.104. limit clause ::=
复制链接

( LIMIT <integer parameter> ( ( <comma> <integer parameter> ) | ( OFFSET <integer parameter> ) )? )
( OFFSET <integer parameter> ( ROW | ROWS ) ( <fetch clause> )? )
<fetch clause>

Limits and/or offsets the resultant rows.

Example:

LIMIT 2

12.6.105. fetch clause ::=
复制链接

FETCH ( FIRST | NEXT ) ( <integer parameter> )? ( ROW | ROWS ) ONLY

ANSI limit.

Example:

FETCH FIRST 1 ROWS ONLY

12.6.106. option clause ::=
复制链接

OPTION ( MAKEDEP <identifier> <make dep options> ( <comma> <identifier> <make dep options> )* | MAKEIND <identifier> <make dep options> ( <comma> <identifier> <make dep options> )* | MAKENOTDEP <identifier> ( <comma> <identifier> )* | NOCACHE ( <identifier> ( <comma> <identifier> )* )? )*

Specifies query options.

Example:

OPTION MAKEDEP tbl

12.6.107. expression ::=
复制链接

<condition>

A value.

Example:

col1

12.6.108. common value expression ::=
复制链接

( <numeric value expression> ( ( <double_amp_op> | <concat_op> ) <numeric value expression> )* )

Establishes the precedence of concat.

Example:

'a' || 'b'

12.6.109. numeric value expression ::=
复制链接

( <term> ( <plus or minus> <term> )* )

Example:

1 + 2

12.6.110. plus or minus ::=
复制链接

<plus>
<minus>

The + or - operator.

Example:

12.6.111. term ::=
复制链接

( <value expression primary> ( <star or slash> <value expression primary> )* )

A numeric term

Example:

1 * 2

12.6.112. star or slash ::=
复制链接

<star>
<slash>

The * or / operator.

Example:

12.6.113. value expression primary ::=
复制链接

<non numeric literal>
( <plus or minus> )? ( <unsigned numeric literal> | ( <unsigned value expression primary> ( <lsbrace> <numeric value expression> <rsbrace> )* ) )

A simple value expression.

Example:

+col1

12.6.114. parameter reference ::=
复制链接

<qmark>
( <dollar> <unsigned integer> )

A parameter reference to be bound later.

Example:

12.6.115. unescapedFunction ::=
复制链接

( ( <text aggreate function> | <standard aggregate function> | <ordered aggregate function> ) ( <filter clause> )? ( <window specification> )? ) | ( <analytic aggregate function> ( <filter clause> )? <window specification> ) | ( <function> ( <window specification> )? )
( XMLCAST <lparen> <expression> AS <data type> <rparen> )

12.6.116. nested expression ::=
复制链接

( <lparen> ( <expression> ( <comma> <expression> )* )? ( <comma> )? <rparen> )

An expression nested in parens

Example:

(1)

12.6.117. unsigned value expression primary ::=
复制链接

<parameter reference>
( <escaped function> <function> <rbrace> )
<unescapedFunction>
<identifier> | <non-reserved identifier>
<subquery>
<nested expression>
<ARRAY expression constructor>
<searched case expression>
<case expression>

An unsigned simple value expression.

Example:

col1

12.6.118. ARRAY expression constructor ::=
复制链接

ARRAY ( ( <lsbrace> ( <expression> ( <comma> <expression> )* )? <rsbrace> ) | ( <lparen> <query expression> <rparen> ) )

Creates and array of the given expressions.

Example:

ARRAY[1,2]

12.6.119. window specification ::=
复制链接

OVER <lparen> ( PARTITION BY <expression list> )? ( <order by clause> )? ( <window frame> )? <rparen>

The window specification for an analytical or windowed aggregate function.

Example:

OVER (PARTION BY col1)

12.6.120. window frame ::=
复制链接

( RANGE | ROWS ) ( ( BETWEEN <window frame bound> AND <window frame bound> ) | <window frame bound> )

Defines the mode, start, and optionally end of the window frame

Example:

RANGE UNBOUNDED PRECEDING

12.6.121. window frame bound ::=
复制链接

( ( UNBOUNDED | <unsigned integer> ) ( FOLLOWING | PRECEDING ) )
( CURRENT ROW )

Defines the start or end of a window frame

Example:

CURRENT ROW

12.6.122. case expression ::=
复制链接

CASE <expression> ( WHEN <expression> THEN <expression> )+ ( ELSE <expression> )? END

If/then/else chain using a common search predicand.

Example:

CASE col1 WHEN 'a' THEN 1 ELSE 2

12.6.123. searched case expression ::=
复制链接

CASE ( WHEN <condition> THEN <expression> )+ ( ELSE <expression> )? END

If/then/else chain using multiple search conditions.

Example:

CASE WHEN x = 'a' THEN 1 WHEN y = 'b' THEN 2

12.6.124. function ::=
复制链接

( CONVERT <lparen> <expression> <comma> <data type> <rparen> )
( CAST <lparen> <expression> AS <data type> <rparen> )
( SUBSTRING <lparen> <expression> ( ( FROM <expression> ( FOR <expression> )? ) | ( <comma> <expression list> ) ) <rparen> )
( EXTRACT <lparen> ( YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | QUARTER | EPOCH ) FROM <expression> <rparen> )
( TRIM <lparen> ( ( ( ( LEADING | TRAILING | BOTH ) ( <expression> )? ) | <expression> ) FROM )? <expression> <rparen> )
( ( TO_CHARS | TO_BYTES ) <lparen> <expression> <comma> <string> ( <comma> <expression> )? <rparen> )
( ( TIMESTAMPADD | TIMESTAMPDIFF ) <lparen> <time interval> <comma> <expression> <comma> <expression> <rparen> )
<querystring function>
( ( LEFT | RIGHT | CHAR | USER | YEAR | MONTH | HOUR | MINUTE | SECOND | XMLCONCAT | XMLCOMMENT | XMLTEXT ) <lparen> ( <expression list> )? <rparen> )
( ( TRANSLATE | INSERT ) <lparen> ( <expression list> )? <rparen> )
<xml parse>
<xml element>
( XMLPI <lparen> ( ( NAME )? <identifier> ) ( <comma> <expression> )? <rparen> )
<xml forest>
<json object>
<xml serialize>
<xml query>
( POSITION <lparen> <common value expression> IN <common value expression> <rparen> )
( LISTAGG <lparen> <expression> ( <comma> <string> )? <rparen> WITHIN GROUP <lparen> <order by clause> <rparen> )
( <identifier> <lparen> ( ALL | DISTINCT )? ( <expression list> )? ( <order by clause> )? <rparen> ( <filter clause> )? )
( CURRENT_DATE ( <lparen> <rparen> )? )
( ( CURRENT_TIMESTAMP | CURRENT_TIME ) ( <lparen> <unsigned integer> <rparen> )? )

Calls a scalar function.

Example:

func('1', col1)

12.6.125. xml parse ::=
复制链接

XMLPARSE <lparen> ( DOCUMENT | CONTENT ) <expression> ( WELLFORMED )? <rparen>

Parses the given value as XML.

Example:

XMLPARSE(DOCUMENT doc WELLFORMED)

12.6.126. querystring function ::=
复制链接

QUERYSTRING <lparen> <expression> ( <comma> <derived column> )* <rparen>

Produces a URL query string from the given arguments.

Example:

QUERYSTRING('path', col1 AS opt, col2 AS val)

12.6.127. xml element ::=
复制链接

XMLELEMENT <lparen> ( ( NAME )? <identifier> ) ( <comma> <xml namespaces> )? ( <comma> <xml attributes> )? ( <comma> <expression> )* <rparen>

Creates an XML element.

Example:

XMLELEMENT(NAME "root", child)

12.6.128. xml attributes ::=
复制链接

XMLATTRIBUTES <lparen> <derived column> ( <comma> <derived column> )* <rparen>

Creates attributes for the containing element.

Example:

XMLATTRIBUTES(col1 AS attr1, col2 AS attr2)

12.6.129. json object ::=
复制链接

JSONOBJECT <lparen> <derived column list> <rparen>

Produces a JSON object containing name value pairs.

Example:

JSONOBJECT(col1 AS val1, col2 AS val2)

12.6.130. derived column list ::=
复制链接

<derived column> ( <comma> <derived column> )*

a list of name value pairs

Example:

col1 AS val1, col2 AS val2

12.6.131. xml forest ::=
复制链接

XMLFOREST <lparen> ( <xml namespaces> <comma> )? <derived column list> <rparen>

Produces an element for each derived column.

Example:

XMLFOREST(col1 AS ELEM1, col2 AS ELEM2)

12.6.132. xml namespaces ::=
复制链接

XMLNAMESPACES <lparen> <xml namespace element> ( <comma> <xml namespace element> )* <rparen>

Defines XML namespace URI/prefix combinations

Example:

XMLNAMESPACES('http://foo' AS foo)

12.6.133. xml namespace element ::=
复制链接

An xml namespace

Example:

NO DEFAULT

12.6.134. simple data type ::=
复制链接

( STRING ( <lparen> <unsigned integer> <rparen> )? )
( VARCHAR ( <lparen> <unsigned integer> <rparen> )? )
BOOLEAN
BYTE
TINYINT
SHORT
SMALLINT
( CHAR ( <lparen> <unsigned integer> <rparen> )? )
INTEGER
LONG
BIGINT
( BIGINTEGER ( <lparen> <unsigned integer> <rparen> )? )
FLOAT
REAL
DOUBLE
( BIGDECIMAL ( <lparen> <unsigned integer> ( <comma> <unsigned integer> )? <rparen> )? )
( DECIMAL ( <lparen> <unsigned integer> ( <comma> <unsigned integer> )? <rparen> )? )
DATE
TIME
( TIMESTAMP ( <lparen> <unsigned integer> <rparen> )? )
( OBJECT ( <lparen> <unsigned integer> <rparen> )? )
( BLOB ( <lparen> <unsigned integer> <rparen> )? )
( CLOB ( <lparen> <unsigned integer> <rparen> )? )
JSON
( VARBINARY ( <lparen> <unsigned integer> <rparen> )? )
GEOMETRY
GEOGRAPHY
XML

A non-collection data type.

Example:

STRING

12.6.135. basic data type ::=
复制链接

<simple data type> ( <lsbrace> <rsbrace> )*

A data type.

Example:

STRING[]

12.6.136. data type ::=
复制链接

<basic data type>
( ( <identifier> | <basicNonReserved> ) ( <lsbrace> <rsbrace> )* )

A data type.

Example:

STRING[]

12.6.137. time interval ::=
复制链接

A time interval keyword.

Example:

SQL_TSI_HOUR

12.6.138. non numeric literal ::=
复制链接

<string>
<binary string literal>
FALSE
TRUE
UNKNOWN
NULL
( <escaped type> <string> <rbrace> )
( ( DATE | TIME | TIMESTAMP ) <string> )

An escaped or simple non numeric literal.

Example:

'a'

12.6.139. unsigned numeric literal ::=
复制链接

An unsigned numeric literal value.

Example:

1.234

12.6.140. ddl statement ::=
复制链接

<create table> ( <create table> | <create procedure> )?
<option namespace>
<alterStatement>
<create trigger>
<create a domain or type alias>
<create server>
<create role >
<drop role >
<GRANT>
<Revoke GRANT>
<CREATE POLICY>
<DROP POLICY>
<drop server>
<drop table>
<Import foreign schema>
<Import another Database>
<create database>
<use database>
<drop schema>
<set schema>
<create schema>
<create procedure> ( <ddl statement> )?
<create data wrapper>
<Drop data wrapper>
<drop procedure>

A data definition statement.

Example:

CREATE FOREIGN TABLE X (Y STRING)

12.6.141. option namespace ::=
复制链接

SET NAMESPACE <string> AS <identifier>

A namespace used to shorten the full name of an option key.

Example:

SET NAMESPACE 'http://foo' AS foo

12.6.142. create database ::=
复制链接

CREATE DATABASE <identifier> ( VERSION <string> )? ( <options clause> )?

create a new database

Example:

CREATE DATABASE foo OPTIONS(x 'y')

12.6.143. use database ::=
复制链接

USE DATABASE <identifier> ( VERSION <string> )?

database into working context

Example:

USE DATABASE foo

12.6.144. create schema ::=
复制链接

CREATE ( VIRTUAL | FOREIGN )? SCHEMA <identifier> ( SERVER <identifier list> )? ( <options clause> )?

create a schema in database

Example:

CREATE VIRTUAL SCHEMA foo SERVER (s1,s2,s3);

12.6.145. drop schema ::=
复制链接

DROP ( VIRTUAL | FOREIGN )? SCHEMA <identifier>

drop a schema in database

Example:

DROP SCHEMA foo

12.6.146. set schema ::=
复制链接

SET SCHEMA <identifier>

set the schema for subsequent ddl statements

Example:

SET SCHEMA foo

12.6.147. create a domain or type alias ::=
复制链接

CREATE DOMAIN ( <identifier> | <basicNonReserved> ) ( AS )? <data type> ( NOT NULL )?

creates a named type with optional constraints

Example:

CREATE DOMAIN my_type AS INTEGER NOT NULL

12.6.148. create data wrapper ::=
复制链接

CREATE FOREIGN ( ( DATA WRAPPER ) | TRANSLATOR ) <Unqualified identifier> ( ( TYPE | HANDLER ) <identifier> )? ( <options clause> )?

Defines a translator; use the options to override the translator properties.

Example:

CREATE FOREIGN DATA WRAPPER wrapper OPTIONS (x true)

12.6.149. Drop data wrapper ::=
复制链接

DROP FOREIGN ( ( DATA WRAPPER ) | TRANSLATOR ) <identifier>

Deletes a translator

Example:

DROP FOREIGN DATA WRAPPER wrapper

12.6.150. create role ::=
复制链接

CREATE ROLE <Unqualified identifier> ( WITH <with role> )?

Defines data role for the database

Example:

CREATE ROLE lowly WITH FOREIGN ROLE "role"

12.6.151. with role ::=
复制链接

( ANY AUTHENTICATED )
( ( JAAS | FOREIGN ) ROLE <identifier list> )

12.6.152. drop role ::=
复制链接

DROP ROLE <identifier>

Removes data role for the database

Example:

DROP ROLE <data-role>

12.6.153. CREATE POLICY ::=
复制链接

CREATE POLICY <identifier> ON ( ( <identifier> ( FOR ( ALL | ( ( SELECT | INSERT | UPDATE | DELETE ) ( <comma> ( SELECT | INSERT | UPDATE | DELETE ) )* ) ) )? ) | ( PROCEDURE <identifier> ( FOR ALL )? ) ) TO <identifier> USING <lparen> <boolean primary> <rparen>

CREATE row level policy

Example:

CREATE POLICY pname ON tbl FOR SELECT,INSERT TO role USING col = user();

12.6.154. DROP POLICY ::=
复制链接

DROP POLICY <identifier> ON ( <identifier> | ( PROCEDURE <identifier> ) ) TO <identifier>

DROP row level policy

Example:

----DROP POLICY pname ON tbl TO role
----

12.6.155. GRANT ::=
复制链接

GRANT ( ( ( <grant type> ( <comma> <grant type> )* )? ON ( TABLE <identifier> ( CONDITION ( ( NOT )? CONSTRAINT )? <string> )? | FUNCTION <identifier> | PROCEDURE <identifier> ( CONDITION ( ( NOT )? CONSTRAINT )? <string> )? | SCHEMA <identifier> | COLUMN <identifier> ( MASK ( ORDER <unsigned integer> )? <string> )? ) ) | ( ALL PRIVILEGES ) | ( TEMPORARY TABLE ) | ( USAGE ON LANGUAGE <identifier> ) ) TO <identifier>

Defines GRANT for a role

Example:

GRANT SELECT ON TABLE x.y TO role

12.6.156. Revoke GRANT ::=
复制链接

REVOKE ( ( ( <grant type> ( <comma> <grant type> )* )? ON ( TABLE <identifier> ( CONDITION )? | FUNCTION <identifier> | PROCEDURE <identifier> ( CONDITION )? | SCHEMA <identifier> | COLUMN <identifier> ( MASK )? ) ) | ( ALL PRIVILEGES ) | ( TEMPORARY TABLE ) | ( USAGE ON LANGUAGE <identifier> ) ) FROM <identifier>

Revokes GRANT for a role

Example:

REVOKE SELECT ON TABLE x.y TO role

12.6.157. create server ::=
复制链接

CREATE SERVER <Unqualified identifier> ( TYPE <string> )? ( VERSION <string> )? FOREIGN ( ( DATA WRAPPER ) | TRANSLATOR ) <Unqualified identifier> ( <options clause> )?

Defines a connection to a source

Example:

CREATE SERVER "h2-connector" FOREIGN DATA WRAPPER h2 OPTIONS ("resource-name" 'java:/accounts-ds');

12.6.158. drop server ::=
复制链接

DROP SERVER <identifier>

Defines dropping connection to foreign source

Example:

DROP SERVER server_name

12.6.159. create procedure ::=
复制链接

CREATE ( VIRTUAL | FOREIGN )? ( PROCEDURE | FUNCTION ) <Unqualified identifier> ( <lparen> ( <procedure parameter> ( <comma> <procedure parameter> )* )? <rparen> ( RETURNS ( <options clause> )? ( ( ( TABLE )? <lparen> <procedure result column> ( <comma> <procedure result column> )* <rparen> ) | <data type> ) )? ( <options clause> )? ( AS <statement> )? )

Defines a procedure or function invocation.

Example:

CREATE FOREIGN PROCEDURE proc (param STRING) RETURNS STRING

12.6.160. drop procedure ::=
复制链接

DROP ( VIRTUAL | FOREIGN )? ( PROCEDURE | FUNCTION ) <identifier>

Drops a table or view.

Example:

DROP FOREIGN TABLE table-name

12.6.161. procedure parameter ::=
复制链接

( IN | OUT | INOUT | VARIADIC )? <identifier> <data type> ( NOT NULL )? ( RESULT )? ( DEFAULT <expression> )? ( <options clause> )?

A procedure or function parameter

Example:

OUT x INTEGER

12.6.162. procedure result column ::=
复制链接

<identifier> <data type> ( NOT NULL )? ( <options clause> )?

A procedure result column.

Example:

x INTEGER

12.6.163. create table ::=
复制链接

CREATE ( <create view> | <create foreign or global temporary table> )

Defines a table or view.

Example:

CREATE VIEW vw AS SELECT 1

12.6.164. create foreign or global temporary table ::=
复制链接

( ( FOREIGN TABLE ) | ( GLOBAL TEMPORARY TABLE ) ) <Unqualified identifier> <create table body>

Defines a foreign or global temporary table.

Example:

FOREIGN TABLE ft (col integer)

12.6.165. create view ::=
复制链接

( VIRTUAL )? VIEW <Unqualified identifier> ( <create view body> | ( <options clause> )? ) AS <query expression>

Defines a view.

Example:

VIEW vw AS SELECT 1

12.6.166. drop table ::=
复制链接

DROP ( ( FOREIGN TABLE ) | ( ( VIRTUAL )? VIEW ) | ( GLOBAL TEMPORARY TABLE ) ) <identifier>

Drops a table or view.

Example:

DROP VIEW name

12.6.167. create foreign temp table ::=
复制链接

CREATE ( LOCAL )? FOREIGN TEMPORARY TABLE <Unqualified identifier> <create table body> ON <identifier>

Defines a foreign temp table

Example:

CREATE FOREIGN TEMPORARY TABLE t (x string) ON z

12.6.168. create table body ::=
复制链接

<lparen> <table element> ( <comma> ( <table constraint> | <table element> ) )* <rparen> ( <options clause> )?

Defines a table.

Example:

(x string) OPTIONS (CARDINALITY 100)

12.6.169. create view body ::=
复制链接

<lparen> <view element> ( <comma> ( <table constraint> | <view element> ) )* <rparen> ( <options clause> )?

Defines a view.

Example:

(x) OPTIONS (CARDINALITY 100)

12.6.170. table constraint ::=
复制链接

( CONSTRAINT <identifier> )? ( <primary key> | <other constraints> | <foreign key> ) ( <options clause> )?

Defines a constraint on a table or view.

Example:

FOREIGN KEY (a, b) REFERENCES tbl (x, y)

12.6.171. foreign key ::=
复制链接

FOREIGN KEY <column list> REFERENCES <identifier> ( <column list> )?

Defines the foreign key referential constraint.

Example:

FOREIGN KEY (a, b) REFERENCES tbl (x, y)

12.6.172. primary key ::=
复制链接

PRIMARY KEY <column list>

Defines the primary key.

Example:

PRIMARY KEY (a, b)

12.6.173. other constraints ::=
复制链接

( ( UNIQUE | ACCESSPATTERN ) <column list> )
( INDEX <lparen> <expression list> <rparen> )

Defines ACCESSPATTERN and UNIQUE constraints and INDEXes.

Example:

UNIQUE (a)

12.6.174. column list ::=
复制链接

<lparen> <identifier> ( <comma> <identifier> )* <rparen>

A list of column names.

Example:

(a, b)

12.6.175. table element ::=
复制链接

<identifier> ( SERIAL | ( <data type> ( NOT NULL )? ( AUTO_INCREMENT )? ) ) <post create column>

Defines a table column.

Example:

x INTEGER NOT NULL

12.6.176. view element ::=
复制链接

<identifier> ( SERIAL | ( <data type> ( NOT NULL )? ( AUTO_INCREMENT )? ) )? <post create column>

Defines a view column with optional type.

Example:

x INTEGER NOT NULL

12.6.177. post create column ::=
复制链接

( <inline constraint> )? ( DEFAULT <expression> )? ( <options clause> )?

Common options trailing a column

Example:

PRIMARY KEY

12.6.178. inline constraint ::=
复制链接

Defines a constraint on a single column

Example:

x INTEGER PRIMARY KEY

12.6.179. options clause ::=
复制链接

OPTIONS <lparen> <option pair> ( <comma> <option pair> )* <rparen>

A list of statement options.

Example:

OPTIONS ('x' 'y', 'a' 'b')

12.6.180. option pair ::=
复制链接

<identifier> ( <non numeric literal> | ( <plus or minus> )? <unsigned numeric literal> )

An option key/value pair.

Example:

'key' 'value'

12.6.181. alter option pair ::=
复制链接

<identifier> ( <non numeric literal> | ( <plus or minus> )? <unsigned numeric literal> )

Alter An option key/value pair.

Example:

'key' 'value'

12.6.182. alterStatement ::=
复制链接

ALTER ( <ALTER TABLE > | <ALTER PROCEDURE > | <ALTER TRIGGER > | <ALTER SERVER > | <ALTER DATA WRAPPER > | <ALTER DATABASE > )

12.6.183. ALTER TABLE ::=
复制链接

( ( ( VIRTUAL )? VIEW <identifier> ) | ( ( FOREIGN )? TABLE <identifier> ) ) ( ( AS <query expression> ) | <ADD column> | <ADD constraint> | <alter options list> | <DROP column> | ( ALTER COLUMN <alter column options> ) | ( RENAME ( <RENAME Table> | ( COLUMN <rename column options> ) ) ) )

alters options of database

Example:

ALTER TABLE foo ADD COLUMN x xml

12.6.184. RENAME Table ::=
复制链接

TO <identifier>

alters table name

Example:

ALTER TABLE foo RENAME TO BAR;

12.6.185. ADD constraint ::=
复制链接

ADD <table constraint>

alters table and adds a constraint

Example:

ADD PRIMARY KEY (ID)

12.6.186. ADD column ::=
复制链接

ADD COLUMN <table element>

alters table and adds a column

Example:

ADD COLUMN bar type OPTIONS (ADD updatable true)

12.6.187. DROP column ::=
复制链接

DROP COLUMN <identifier>

alters table and adds a column

Example:

DROP COLUMN bar

12.6.188. alter column options ::=
复制链接

<identifier> ( ( TYPE ( SERIAL | ( <data type> ( NOT NULL )? ( AUTO_INCREMENT )? ) ) ) | <alter child options list> )

alters a set of column options

Example:

ALTER COLUMN bar OPTIONS (ADD updatable true)

12.6.189. rename column options ::=
复制链接

<identifier> TO <identifier>

renames either a table column or procedure’s parameter name

Example:

RENAME COLUMN bar TO foo

12.6.190. ALTER PROCEDURE ::=
复制链接

( VIRTUAL | FOREIGN )? PROCEDURE <identifier> ( ( AS <statement> ) | <alter options list> | ( ALTER PARAMETER <alter column options> ) | ( RENAME PARAMETER <rename column options> ) )

alters options of database

Example:

ALTER PROCEDURE foo OPTIONS (ADD x y)

12.6.191. ALTER TRIGGER ::=
复制链接

TRIGGER ON <identifier> INSTEAD OF ( INSERT | UPDATE | DELETE ) ( AS <for each row trigger action> | ENABLED | DISABLED )

alters options of table triggers

Example:

ALTER TRIGGER ON vw INSTEAD OF INSERT ENABLED

12.6.192. ALTER SERVER ::=
复制链接

SERVER <identifier> <alter options list>

alters options of database

Example:

ALTER SERVER foo OPTIONS (ADD x y)

12.6.193. ALTER DATA WRAPPER ::=
复制链接

( ( DATA WRAPPER ) | TRANSLATOR ) <identifier> <alter options list>

alters options of data wrapper

Example:

ALTER DATA WRAPPER foo OPTIONS (ADD x y)

12.6.194. ALTER DATABASE ::=
复制链接

DATABASE <identifier> <alter options list>

alters options of database

Example:

ALTER DATABASE foo OPTIONS (ADD x y)

12.6.195. alter options list ::=
复制链接

OPTIONS <lparen> ( <add set option> | <drop option> ) ( <comma> ( <add set option> | <drop option> ) )* <rparen>

a list of alterations to options

Example:

OPTIONS (ADD updatable true)

12.6.196. drop option ::=
复制链接

DROP <identifier>

drop option

Example:

DROP updatable

12.6.197. add set option ::=
复制链接

( ADD | SET ) <alter option pair>

add or set an option pair

Example:

ADD updatable true

12.6.198. alter child options list ::=
复制链接

OPTIONS <lparen> ( <add set child option> | <drop option> ) ( <comma> ( <add set child option> | <drop option> ) )* <rparen>

a list of alterations to options

Example:

OPTIONS (ADD updatable true)

12.6.199. drop option ::=
复制链接

DROP <identifier>

drop option

Example:

DROP updatable

12.6.200. add set child option ::=
复制链接

( ADD | SET ) <alter child option pair>

add or set an option pair

Example:

ADD updatable true

12.6.201. alter child option pair ::=
复制链接

<identifier> ( <non numeric literal> | ( <plus or minus> )? <unsigned numeric literal> )

Alter An option key/value pair.

Example:

'key' 'value'

12.6.202. Import foreign schema ::=
复制链接

IMPORT ( FOREIGN SCHEMA <identifier> )? FROM ( SERVER | REPOSITORY ) <identifier> INTO <identifier> ( <options clause> )?

imports schema metadata from server

Example:

IMPORT FOREIGN SCHEMA foo FROM SERVER bar

12.6.203. Import another Database ::=
复制链接

IMPORT DATABASE <identifier> VERSION <string> ( WITH ACCESS CONTROL )?

imports another database into current database

Example:

IMPORT DATABASE vdb VERSION '1.2.3' WITH ACCESS CONTROL]

12.6.204. identifier list ::=
复制链接

<identifier> ( <comma> <identifier> )*

此内容没有您所选择的语言版本。

Chapter 12. BNF for SQL Grammar

12.1. Reserved Keywords复制链接链接已复制到粘贴板!

12.2. Non-Reserved Keywords复制链接链接已复制到粘贴板!

12.3. Reserved Keywords For Future Use复制链接链接已复制到粘贴板!

12.4. Tokens复制链接链接已复制到粘贴板!

12.5. Production Cross-Reference复制链接链接已复制到粘贴板!

12.6. Productions复制链接链接已复制到粘贴板!

12.6.1. string ::=复制链接链接已复制到粘贴板!

12.6.2. non-reserved identifier ::=复制链接链接已复制到粘贴板!

12.6.3. basicNonReserved ::=复制链接链接已复制到粘贴板!

12.6.4. Unqualified identifier ::=复制链接链接已复制到粘贴板!

12.6.5. identifier ::=复制链接链接已复制到粘贴板!

12.6.6. create trigger ::=复制链接链接已复制到粘贴板!

12.6.7. alter ::=复制链接链接已复制到粘贴板!

12.6.8. for each row trigger action ::=复制链接链接已复制到粘贴板!

12.6.9. explain ::=复制链接链接已复制到粘贴板!

12.6.10. explain option ::=复制链接链接已复制到粘贴板!

12.6.11. directly executable statement ::=复制链接链接已复制到粘贴板!

12.6.12. drop table ::=复制链接链接已复制到粘贴板!

12.6.13. create temporary table ::=复制链接链接已复制到粘贴板!

12.6.14. temporary table element ::=复制链接链接已复制到粘贴板!

12.6.15. raise error statement ::=复制链接链接已复制到粘贴板!

12.6.16. raise statement ::=复制链接链接已复制到粘贴板!

12.6.17. exception reference ::=复制链接链接已复制到粘贴板!

12.6.18. sql exception ::=复制链接链接已复制到粘贴板!

12.6.19. statement ::=复制链接链接已复制到粘贴板!

12.6.20. delimited statement ::=复制链接链接已复制到粘贴板!

12.6.21. compound statement ::=复制链接链接已复制到粘贴板!

12.6.22. branching statement ::=复制链接链接已复制到粘贴板!

12.6.23. return statement ::=复制链接链接已复制到粘贴板!

12.6.24. while statement ::=复制链接链接已复制到粘贴板!

12.6.25. loop statement ::=复制链接链接已复制到粘贴板!

12.6.26. if statement ::=复制链接链接已复制到粘贴板!

12.6.27. declare statement ::=复制链接链接已复制到粘贴板!

12.6.28. assignment statement ::=复制链接链接已复制到粘贴板!

12.6.29. assignment statement operand ::=复制链接链接已复制到粘贴板!

12.6.30. data statement ::=复制链接链接已复制到粘贴板!

12.6.31. dynamic data statement ::=复制链接链接已复制到粘贴板!

12.6.32. set clause list ::=复制链接链接已复制到粘贴板!

12.6.33. typed element list ::=复制链接链接已复制到粘贴板!

12.6.34. callable statement ::=复制链接链接已复制到粘贴板!

12.6.35. call statement ::=复制链接链接已复制到粘贴板!

12.6.36. named parameter list ::=复制链接链接已复制到粘贴板!

12.6.37. insert statement ::=复制链接链接已复制到粘贴板!

12.6.38. expression list ::=复制链接链接已复制到粘贴板!

12.6.39. update statement ::=复制链接链接已复制到粘贴板!

12.6.40. delete statement ::=复制链接链接已复制到粘贴板!

12.6.41. query expression ::=复制链接链接已复制到粘贴板!

12.6.42. with list element ::=复制链接链接已复制到粘贴板!

12.6.43. query expression body ::=复制链接链接已复制到粘贴板!

12.6.44. query term ::=复制链接链接已复制到粘贴板!

12.6.45. query primary ::=复制链接链接已复制到粘贴板!

12.6.46. query ::=复制链接链接已复制到粘贴板!

12.6.47. into clause ::=复制链接链接已复制到粘贴板!

12.6.48. select clause ::=复制链接链接已复制到粘贴板!

12.6.49. select sublist ::=复制链接链接已复制到粘贴板!

12.6.50. select derived column ::=复制链接链接已复制到粘贴板!

12.6.51. derived column ::=复制链接链接已复制到粘贴板!

12.6.52. all in group ::=复制链接链接已复制到粘贴板!

12.6.53. ordered aggregate function ::=复制链接链接已复制到粘贴板!

12.6.54. text aggreate function ::=复制链接链接已复制到粘贴板!

12.6.55. standard aggregate function ::=复制链接链接已复制到粘贴板!

12.6.56. analytic aggregate function ::=复制链接链接已复制到粘贴板!

12.6.57. filter clause ::=复制链接链接已复制到粘贴板!

12.6.58. from clause ::=复制链接链接已复制到粘贴板!

12.6.59. table reference ::=复制链接链接已复制到粘贴板!

12.6.60. joined table ::=复制链接链接已复制到粘贴板!

12.6.61. cross join ::=复制链接链接已复制到粘贴板!

12.6.62. qualified table ::=复制链接链接已复制到粘贴板!

12.6.63. table primary ::=复制链接链接已复制到粘贴板!

12.6.64. make dep options ::=复制链接链接已复制到粘贴板!

12.6.65. xml serialize ::=复制链接链接已复制到粘贴板!

12.6.66. array table ::=复制链接链接已复制到粘贴板!

12.6.67. json table ::=复制链接链接已复制到粘贴板!

12.6.68. json table column ::=复制链接链接已复制到粘贴板!

12.6.69. text table ::=复制链接链接已复制到粘贴板!

12.6.70. text table column ::=复制链接链接已复制到粘贴板!

12.6.71. xml query ::=复制链接链接已复制到粘贴板!

12.6.72. xml query ::=复制链接链接已复制到粘贴板!

12.1. Reserved Keywords
复制链接

12.2. Non-Reserved Keywords
复制链接

12.3. Reserved Keywords For Future Use
复制链接

12.4. Tokens
复制链接

12.5. Production Cross-Reference
复制链接

12.6. Productions
复制链接

12.6.1. string ::=
复制链接

12.6.2. non-reserved identifier ::=
复制链接

12.6.3. basicNonReserved ::=
复制链接

12.6.4. Unqualified identifier ::=
复制链接

12.6.5. identifier ::=
复制链接

12.6.6. create trigger ::=
复制链接

12.6.7. alter ::=
复制链接

12.6.8. for each row trigger action ::=
复制链接

12.6.9. explain ::=
复制链接

12.6.10. explain option ::=
复制链接

12.6.11. directly executable statement ::=
复制链接

12.6.12. drop table ::=
复制链接

12.6.13. create temporary table ::=
复制链接

12.6.14. temporary table element ::=
复制链接

12.6.15. raise error statement ::=
复制链接

12.6.16. raise statement ::=
复制链接

12.6.17. exception reference ::=
复制链接

12.6.18. sql exception ::=
复制链接

12.6.19. statement ::=
复制链接

12.6.20. delimited statement ::=
复制链接

12.6.21. compound statement ::=
复制链接

12.6.22. branching statement ::=
复制链接

12.6.23. return statement ::=
复制链接

12.6.24. while statement ::=
复制链接

12.6.25. loop statement ::=
复制链接

12.6.26. if statement ::=
复制链接

12.6.27. declare statement ::=
复制链接

12.6.28. assignment statement ::=
复制链接

12.6.29. assignment statement operand ::=
复制链接

12.6.30. data statement ::=
复制链接

12.6.31. dynamic data statement ::=
复制链接

12.6.32. set clause list ::=
复制链接

12.6.33. typed element list ::=
复制链接

12.6.34. callable statement ::=
复制链接

12.6.35. call statement ::=
复制链接

12.6.36. named parameter list ::=
复制链接

12.6.37. insert statement ::=
复制链接

12.6.38. expression list ::=
复制链接

12.6.39. update statement ::=
复制链接

12.6.40. delete statement ::=
复制链接

12.6.41. query expression ::=
复制链接

12.6.42. with list element ::=
复制链接

12.6.43. query expression body ::=
复制链接

12.6.44. query term ::=
复制链接

12.6.45. query primary ::=
复制链接

12.6.46. query ::=
复制链接

12.6.47. into clause ::=
复制链接

12.6.48. select clause ::=
复制链接

12.6.49. select sublist ::=
复制链接

12.6.50. select derived column ::=
复制链接

12.6.51. derived column ::=
复制链接

12.6.52. all in group ::=
复制链接

12.6.53. ordered aggregate function ::=
复制链接

12.6.54. text aggreate function ::=
复制链接

12.6.55. standard aggregate function ::=
复制链接

12.6.56. analytic aggregate function ::=
复制链接

12.6.57. filter clause ::=
复制链接

12.6.58. from clause ::=
复制链接

12.6.59. table reference ::=
复制链接

12.6.60. joined table ::=
复制链接

12.6.61. cross join ::=
复制链接

12.6.62. qualified table ::=
复制链接

12.6.63. table primary ::=
复制链接

12.6.64. make dep options ::=
复制链接

12.6.65. xml serialize ::=
复制链接

12.6.66. array table ::=
复制链接

12.6.67. json table ::=
复制链接

12.6.68. json table column ::=
复制链接

12.6.69. text table ::=
复制链接

12.6.70. text table column ::=
复制链接

12.6.71. xml query ::=
复制链接

12.6.72. xml query ::=
复制链接

12.6.73. object table ::=
复制链接

12.6.74. object table column ::=
复制链接