SQLステートメント
ALTER CLUSTER
ALTER CLUSTER [ schema. ]cluster { physical_attributes_clause | SIZE size_clause | allocate_extent_clause | deallocate_unused_clause | { CACHE | NOCACHE } } [ physical_attributes_clause | SIZE size_clause | allocate_extent_clause | deallocate_unused_clause | { CACHE | NOCACHE } ]... [ parallel_clause ]
ALTER DATABASE
ALTER DATABASE [ database ] { startup_clauses | recovery_clauses | database_file_clauses | logfile_clauses | controlfile_clauses | standby_database_clauses | default_settings_clauses | instance_clauses | security_clause } startup_clauses: MOUNT MOUNT CLONE DATABASE MOUNT PARALLEL CONVERT OPEN [READ ONLY] OPEN [READ WRITE] RESETLOGS|NORESETLOGS [MIGRATE] default_settings_clauses: [NATIONAL] CHARACTER SET char_set standby_database_clauses: MOUNT STANDBY DATABASE ACTIVATE STANDBY DATABASE archivelog options: ARCHIVELOG NOARCHIVELOG recovery_clauses: BACKUP CONTROLFILE TO 'filename' [REUSE] BACKUP CONTROLFILE TO TRACE [RESETLOGS] [AS 'filename' [REUSE]] CREATE STANDBY CONTROLFILE AS 'filename' [REUSE] RECOVER recover_clause RECOVER MANAGED STANDBY standby_recover_clause END BACKUP database_file_clauses: CREATE DATAFILE 'filename' AS filespec DATAFILE 'filename' ONLINE DATAFILE 'filename' OFFLINE [DROP] DATAFILE 'filename' RESIZE int K | M DATAFILE 'filename' AUTOEXTEND OFF DATAFILE 'filename' AUTOEXTEND ON [NEXT int K | M] [MAXSIZE int K | M | UNLIMITED] DATAFILE 'filename' END BACKUP RENAME FILE 'data_file_name' TO 'data_file_name' TEMPFILE 'filename' ONLINE TEMPFILE 'filename' OFFLINE TEMPFILE 'filename' DROP [INCLUDING DATAFILES] TEMPFILE 'filename' RESIZE int K | M TEMPFILE 'filename' AUTOEXTEND OFF TEMPFILE 'filename' AUTOEXTEND ON [NEXT int K | M] [MAXSIZE int K | M | UNLIMITED] controlfile_clauses: BACKUP CONTROLFILE TO [TRACE| 'backup_controlfile_name'] CREATE STANDBY CONTROLFILE AS 'filename' [REUSE] redo log options: ADD LOGFILE [THREAD int] [GROUP int] filespec ADD LOGFILE MEMBER 'filename' [REUSE] TO GROUP int ADD LOGFILE MEMBER 'filename' [REUSE] TO 'filename' DROP LOGFILE GROUP int DROP LOGFILE ('filename') DROP LOGFILE MEMBER 'filename' RENAME FILE 'redolog_file_name' TO 'redolog_file_name' CLEAR [UNARCHIVED] LOGFILE GROUP int [UNRECOVERABLE DATAFILE] CLEAR [UNARCHIVED] LOGFILE ('filename') [UNRECOVERABLE DATAFILE] Parallel server options: SET DBLOW = 'text' SET DBHIGH = 'text' SET DBMAC = ON | OFF ENABLE [PUBLIC] THREAD int DISABLE THREAD int Backwards compatibility options: RENAME GLOBAL_NAME TO database [domain] RESET COMPATIBILITY
ALTER DIMENSION
ALTER DIMENSION [ schema. ]dimension { ADD { level_clause | hierarchy_clause | attribute_clause | extended_attribute_clause } [ ADD { level_clause | hierarchy_clause | attribute_clause | extended_attribute_clause } ]... | DROP { LEVEL level [ RESTRICT | CASCADE ] | HIERARCHY hierarchy | ATTRIBUTE attribute [ LEVEL level [ COLUMN column [, COLUMN column ]... ] } [ DROP { LEVEL level [ RESTRICT | CASCADE ] | HIERARCHY hierarchy | ATTRIBUTE attribute [ LEVEL level [ COLUMN column [, COLUMN column ]... ] } ]... | COMPILE }
ALTER DISKGROUP
ALTER DISKGROUP { diskgroup_name { add_disk_clause | drop_disk_clause } [, { add_disk_clause | drop_disk_clause } ]... | resize_disk_clauses } [ rebalance_diskgroup_clause ] | {rebalance_diskgroup_clause | check_diskgroup_clause | diskgroup_template_clauses | diskgroup_directory_clauses | diskgroup_alias_clauses | drop_diskgroup_file_clause } | { diskgroup_name [, diskgroup_name ]... | ALL } { undrop_disk_clause | diskgroup_availability } } ALTER FUNCTION ALTER FUNCTION [ schema. ]function COMPILE [ DEBUG ] [ compiler_parameters_clause [ compiler_parameters_clause ] ... ] [ REUSE SETTINGS ]
ALTER INDEX
ALTER INDEX [ schema. ]index { { deallocate_unused_clause | allocate_extent_clause | shrink_clause | parallel_clause | physical_attributes_clause | logging_clause } [ deallocate_unused_clause | allocate_extent_clause | shrink_clause | parallel_clause | physical_attributes_clause | logging_clause ]... | rebuild_clause | PARAMETERS ('ODCI_parameters') | { ENABLE | DISABLE } | UNUSABLE | RENAME TO new_name | COALESCE | { MONITORING | NOMONITORING } USAGE | UPDATE BLOCK REFERENCES | alter_index_partitioning }
ALTER INDEXTYPE
ALTER INDEXTYPE [ schema. ]indextype { { ADD | DROP } [ schema. ]operator (parameter_types) [, { ADD | DROP } [ schema. ]operator (parameter_types) ]... [ using_type_clause ] | COMPILE }
ALTER JAVA
ALTER JAVA { SOURCE | CLASS } [ schema. ]object_name [ RESOLVER ( ( match_string [, ] { schema_name | - } ) [ ( match_string [, ] { schema_name | - } ) ]... ) ] { { COMPILE | RESOLVE } | invoker_rights_clause }
ALTER MATERIALIZED VIEW
ALTER MATERIALIZED VIEW [ schema. ](materialized_view) [ physical_attributes_clause | table_compression | LOB_storage_clause [, LOB_storage_clause ]... | modify_LOB_storage_clause [, modify_LOB_storage_clause ]... | alter_table_partitioning | parallel_clause | logging_clause | allocate_extent_clause | shrink_clause | { CACHE | NOCACHE } ] [ alter_iot_clauses ] [ USING INDEX physical_attributes_clause ] [ MODIFY scoped_table_ref_constraint | alter_mv_refresh ] [ { ENABLE | DISABLE } QUERY REWRITE | COMPILE | CONSIDER FRESH ]
ALTER MATERIALIZED VIEW LOG
ALTER MATERIALIZED VIEW LOG [ FORCE ] ON [ schema. ]table [ physical_attributes_clause | alter_table_partitioning | parallel_clause | logging_clause | allocate_extent_clause | shrink_clause | { CACHE | NOCACHE } ] [ ADD { { OBJECT ID | PRIMARY KEY | ROWID | SEQUENCE } [ (column [, column ]...) ] | (column [, column ]... ) } [, { { OBJECT ID | PRIMARY KEY | ROWID | SEQUENCE } [ (column [, column ]...) ] | (column [, column ]...) } ]... [ new_values_clause ] ]
ALTER OPERATOR
ALTER OPERATOR [ schema. ]operator { add_binding_clause | drop_binding_clause | COMPILE }
ALTER OUTLINE
ALTER OUTLINE [ PUBLIC | PRIVATE ] outline { REBUILD | RENAME TO new_outline_name | CHANGE CATEGORY TO new_category_name | { ENABLE | DISABLE } } [ REBUILD | RENAME TO new_outline_name | CHANGE CATEGORY TO new_category_name | { ENABLE | DISABLE } ]...
ALTER PACKAGE
ALTER PACKAGE [ schema. ]package COMPILE [ DEBUG ] [ PACKAGE | SPECIFICATION | BODY ] [ compiler_parameters_clause [ compiler_parameters_clause ] ... ] [ REUSE SETTINGS ]
ALTER PROCEDURE
ALTER PROCEDURE [ schema. ]procedure COMPILE [ DEBUG ] [ compiler_parameters_clause [ compiler_parameters_clause ] ... ] [ REUSE SETTINGS ]
ALTER PROFILE
ALTER PROFILE profile LIMIT { resource_parameters | password_parameters } [ resource_parameters | password_parameters ]...
ALTER RESOURCE COST
ALTER RESOURCE COST { CPU_PER_SESSION | CONNECT_TIME | LOGICAL_READS_PER_SESSION | PRIVATE_SGA } integer [ { CPU_PER_SESSION | CONNECT_TIME | LOGICAL_READS_PER_SESSION | PRIVATE_SGA } integer ] ...
ALTER ROLE
ALTER ROLE role { NOT IDENTIFIED | IDENTIFIED { BY password | USING [ schema. ]package | EXTERNALLY | GLOBALLY } }
ALTER ROLLBACK SEGMENT
ALTER ROLLBACK SEGMENT rollback_segment { ONLINE | OFFLINE | storage_clause | SHRINK [ TO size_clause ] }
ALTER SEQUENCE
ALTER SEQUENCE [ schema. ]sequence { INCREMENT BY integer | { MAXVALUE integer | NOMAXVALUE } | { MINVALUE integer | NOMINVALUE } | { CYCLE | NOCYCLE } | { CACHE integer | NOCACHE } | { ORDER | NOORDER } } [ INCREMENT BY integer | { MAXVALUE integer | NOMAXVALUE } | { MINVALUE integer | NOMINVALUE } | { CYCLE | NOCYCLE } | { CACHE integer | NOCACHE } | { ORDER | NOORDER } ]...
ALTER SESSION
ALTER SESSION { ADVISE { COMMIT | ROLLBACK | NOTHING } | CLOSE DATABASE LINK dblink | { ENABLE | DISABLE } COMMIT IN PROCEDURE | { ENABLE | DISABLE } GUARD | { ENABLE | DISABLE | FORCE } PARALLEL { DML | DDL | QUERY } [ PARALLEL integer ] | { ENABLE RESUMABLE [ TIMEOUT integer ] [ NAME string ] | DISABLE RESUMABLE } | alter_session_set_clause }
ALTER SYSTEM
ALTER SYSTEM { archive_log_clause | checkpoint_clause | check_datafiles_clause | distributed_recov_clauses | FLUSH { SHARED_POOL | BUFFER_CACHE } | end_session_clauses | SWITCH LOGFILE | { SUSPEND | RESUME } | quiesce_clauses | alter_system_security_clauses | shutdown_dispatcher_clause | REGISTER | SET alter_system_set_clause [ alter_system_set_clause ]... | RESET alter_system_reset_clause [ alter_system_reset_clause ]... }
ALTER TABLE
ALTER TABLE [ schema. ]table [ alter_table_properties | column_clauses | constraint_clauses | alter_table_partitioning | alter_external_table_clauses | move_table_clause ] [ enable_disable_clause | { ENABLE | DISABLE } { TABLE LOCK | ALL TRIGGERS } [ enable_disable_clause | { ENABLE | DISABLE } { TABLE LOCK | ALL TRIGGERS } ]... ]
constraint_clauses::= ADD | MODIFY | DROP [inline_constraint| out_of_line_constraint| inline_ref_constraint| out_of_line_ref_constraint]
inline_constraint::= [CONSTRAINT constraint_name] ( [NOT] NULL | UNIQUE | PRIMARY KEY | references_close | CHECK(condition) ) [constraint_state]
out_of_line_constraint::= [CONSTRAINT constraint_name] ( UNIQUE(column[, column ...]) | PRIMARY KEY(column[, column ...]) | FOREIGN KEY(column[, column ...]) references_close | CHECK(condition) ) [constraint_state]
inline_ref_constraint::= ( SCOPE IS [schema.]scope_table | WITH ROWID | [CONSTRAINT constraint_name] reference_close [constraint_state] )
out_of_line_ref_constraint::= ( SCOPE FOR ( (ref_col | ref_attr) ) IS [schema.]scope_table | REF ( (ref_col | ref_attr) ) WITH ROWID | [CONSTRAINT constraint_name] FOREIGN KEY ( (ref_col | ref_attr) ) reference_close [constraint_state] )
constraint_state::= [NOT] DEFERRABLE | INITIALLY (IMMEDIATE | DEFERRED) | ENABLE | DISABLE | VALIDATE | NOVALIDATE | RELY | NORELY | using_index_clause | exceptions_clause
using_index_clause::= USING INDEX [schema.] index | (create_index_statement) | index_properties
index_attributes::= [ physical_attributes_clause | logging_clause | ONLINE | COMPUTE STATISTICS | TABLESPACE ( tablespace | DEFAULT ) | key_compression | SORT | NOSORT | REVERSE | parallel_clause ] ...
exceptions_clause::= EXCEPTION INTO [schema.] table
Example:
-- check constraint SQL> create table test (tid varchar2(10), constraint chk_test_tid check(to_number(tid) >0)); SQL> alter table test modify constraint chk_test_tid disable; SQL> alter table test drop constraint chk_test_tid;
ALTER TABLESPACE
ALTER TABLESPACE tablespace { DEFAULT [ table_compression ] storage_clause | MINIMUM EXTENT size_clause | RESIZE size_clause | COALESCE | RENAME TO new_tablespace_name | { BEGIN | END } BACKUP | datafile_tempfile_clauses | tablespace_logging_clauses | tablespace_group_clause | tablespace_state_clauses | autoextend_clause | flashback_mode_clause | tablespace_retention_clause }
ALTER TRIGGER
ALTER TRIGGER [ schema. ]trigger { ENABLE | DISABLE | RENAME TO new_name | COMPILE [ DEBUG ] [ compiler_parameters_clause [ compiler_parameters_clause ] ... ] [ REUSE SETTINGS ] }
ALTER TYPE
ALTER TYPE [ schema. ]type { compile_type_clause | replace_type_clause | { alter_method_spec | alter_attribute_definition | alter_collection_clauses | [ NOT ] { INSTANTIABLE | FINAL } } [ dependent_handling_clause ] }
ALTER USER
ALTER USER { user { IDENTIFIED { BY password [ REPLACE old_password ] | EXTERNALLY [ AS 'certificate_DN' ] | GLOBALLY [ AS '[directory_DN]' ] } | DEFAULT TABLESPACE tablespace | TEMPORARY TABLESPACE { tablespace | tablespace_group_name } | QUOTA { size_clause | UNLIMITED } ON tablespace [ QUOTA { size_clause | UNLIMITED } ON tablespace ]... | PROFILE profile | DEFAULT ROLE { role [, role ]... | ALL [ EXCEPT role [, role ]... ] | NONE } | PASSWORD EXPIRE | ACCOUNT { LOCK | UNLOCK } } [ { IDENTIFIED { BY password [ REPLACE old_password ] | EXTERNALLY [ AS 'certificate_DN' ] | GLOBALLY [ AS '[directory_DN]' ] } | DEFAULT TABLESPACE tablespace | TEMPORARY TABLESPACE { tablespace | tablespace_group_name } | QUOTA { size_clause | UNLIMITED } ON tablespace [ QUOTA { size_clause | UNLIMITED } ON tablespace ]... | PROFILE profile | DEFAULT ROLE { role [, role ]... | ALL [ EXCEPT role [, role ]... ] | NONE } | PASSWORD EXPIRE | ACCOUNT { LOCK | UNLOCK } } ]... | user [, user ]... proxy_clause
ALTER VIEW
ALTER VIEW [ schema. ]view { ADD out_of_line_constraint | MODIFY CONSTRAINT constraint { RELY | NORELY } | DROP { CONSTRAINT constraint | PRIMARY KEY | UNIQUE (column [, column ]...) } | COMPILE }
ANALYZE
ANALYZE { TABLE [ schema. ]table [ PARTITION (partition) | SUBPARTITION (subpartition) ] | INDEX [ schema. ]index [ PARTITION (partition) | SUBPARTITION (subpartition) ] | CLUSTER [ schema. ]cluster } { validation_clauses | LIST CHAINED ROWS [ into_clause ] | DELETE [ SYSTEM ] STATISTICS | compute_statistics_clause | estimate_statistics_clause }
ASSOCIATE STATISTICS
ASSOCIATE STATISTICS WITH { column_association | function_association }
AUDIT
AUDIT { sql_statement_clause | schema_object_clause | NETWORK } [ BY { SESSION | ACCESS } ] [ WHENEVER [ NOT ] SUCCESSFUL ]
CALL
CALL { routine_clause | object_access_expression } [ INTO :host_variable [ [ INDICATOR ] :indicator_variable ] ]
COMMENT
COMMENT ON { TABLE [ schema. ] { table | view } | COLUMN [ schema. ] { table. | view. | materialized_view. } column | OPERATOR [ schema. ] operator | INDEXTYPE [ schema. ] indextype | MATERIALIZED VIEW materialized_view } IS string
COMMIT
COMMIT [ WORK ] [ [ COMMENT string ] | [ WRITE [ IMMEDIATE | BATCH ] [ WAIT | NOWAIT ] ] | FORCE string [, integer ] ]
CREATE CLUSTER
CREATE CLUSTER [ schema. ]cluster (column datatype [ SORT ] [, column datatype [ SORT ] ]... ) [ { physical_attributes_clause | SIZE size_clause | TABLESPACE tablespace | { INDEX | [ SINGLE TABLE ] HASHKEYS integer [ HASH IS expr ] } } [ physical_attributes_clause | SIZE size_clause | TABLESPACE tablespace | { INDEX | [ SINGLE TABLE ] HASHKEYS integer [ HASH IS expr ] } ]... ] [ parallel_clause ] [ NOROWDEPENDENCIES | ROWDEPENDENCIES ] [ CACHE | NOCACHE ]
CREATE CONTEXT
CREATE [ OR REPLACE ] CONTEXT namespace USING [ schema. ] package [ INITIALIZED { EXTERNALLY | GLOBALLY } | ACCESSED GLOBALLY ]
CREATE CONTROLFILE
CREATE CONTROLFILE [ REUSE ] [ SET ] DATABASE database [ logfile_clause ] { RESETLOGS | NORESETLOGS } [ DATAFILE file_specification [, file_specification ]... ] [ { MAXLOGFILES integer | MAXLOGMEMBERS integer | MAXLOGHISTORY integer | MAXDATAFILES integer | MAXINSTANCES integer | { ARCHIVELOG | NOARCHIVELOG } | FORCE LOGGING } [ MAXLOGFILES integer | MAXLOGMEMBERS integer | MAXLOGHISTORY integer | MAXDATAFILES integer | MAXINSTANCES integer | { ARCHIVELOG | NOARCHIVELOG } | FORCE LOGGING ]... ] [ character_set_clause ]
CREATE DATABASE
CREATE DATABASE [ database ] { USER SYS IDENTIFIED BY password | USER SYSTEM IDENTIFIED BY password | CONTROLFILE REUSE | MAXDATAFILES integer | MAXINSTANCES integer | CHARACTER SET charset | NATIONAL CHARACTER SET charset | SET DEFAULT { BIGFILE | SMALLFILE } TABLESPACE | database_logging_clauses | tablespace_clauses | set_time_zone_clause }...
CREATE DATABASE LINK
CREATE [ SHARED ] [ PUBLIC ] DATABASE LINK dblink [ CONNECT TO { CURRENT_USER | user IDENTIFIED BY password [ dblink_authentication ] } | dblink_authentication ] [ CONNECT TO { CURRENT_USER | user IDENTIFIED BY password [ dblink_authentication ] } | dblink_authentication ]... [ USING connect_string ]
CREATE DIMENSION
CREATE DIMENSION [ schema. ]dimension level_clause [ level_clause ]... { hierarchy_clause | attribute_clause | extended_attribute_clause } [ hierarchy_clause | attribute_clause | extended_attribute_clause ]...
CREATE DIRECTORY
CREATE [ OR REPLACE ] DIRECTORY directory AS 'path_name'
CREATE DISKGROUP
CREATE DISKGROUP diskgroup_name [ { HIGH | NORMAL | EXTERNAL } REDUNDANCY ] [ FAILGROUP failgroup_name ] DISK qualified_disk_clause [, qualified_disk_clause ]... [ [ FAILGROUP failgroup_name ] DISK qualified_disk_clause [, qualified_disk_clause ]... ]...
CREATE FUNCTION
CREATE [ OR REPLACE ] FUNCTION [ schema. ]function [ (argument [ IN | OUT | IN OUT ] [ NOCOPY ] datatype [, argument [ IN | OUT | IN OUT ] [ NOCOPY ] datatype ]... ) ] RETURN datatype [ { invoker_rights_clause | DETERMINISTIC | parallel_enable_clause } [ invoker_rights_clause | DETERMINISTIC | parallel_enable_clause ]... ] { { AGGREGATE | PIPELINED } USING [ schema. ]implementation_type | [ PIPELINED ] { IS | AS } { pl/sql_function_body | call_spec } }
CREATE INDEX
CREATE [ UNIQUE | BITMAP ] INDEX [ schema. ]index ON { cluster_index_clause | table_index_clause | bitmap_join_index_clause }
CREATE INDEXTYPE
CREATE [ OR REPLACE ] INDEXTYPE [ schema. ]indextype FOR [ schema. ]operator (paramater_type [, paramater_type ]...) [, [ schema. ]operator (paramater_type [, paramater_type ]...) ]... using_type_clause
CREATE JAVA
CREATE [ OR REPLACE ] [ AND { RESOLVE | COMPILE } ] [ NOFORCE ] JAVA { { SOURCE | RESOURCE } NAMED [ schema. ]primary_name | CLASS [ SCHEMA schema ] } [ invoker_rights_clause ] [ RESOLVER ((match_string [,] { schema_name | - }) [ (match_string [,] { schema_name | - }) ]... ) ] { USING { BFILE (directory_object_name , server_file_name) | { CLOB | BLOB | BFILE } subquery | 'key_for_BLOB' } | AS source_char } CREATE LIBRARY CREATE [ OR REPLACE ] LIBRARY [ schema. ]libname { IS | AS } 'filename' [ AGENT 'agent_dblink' ]
CREATE MATERIALIZED VIEW
CREATE MATERIALIZED VIEW [ schema. ]materialized_view [ column_alias [, column_alias]... ] [ OF [ schema. ]object_type ] [ (scoped_table_ref_constraint) ] { ON PREBUILT TABLE [ { WITH | WITHOUT } REDUCED PRECISION ] | physical_properties materialized_view_props } [ USING INDEX [ physical_attributes_clause | TABLESPACE tablespace ] [ physical_attributes_clause | TABLESPACE tablespace ]... | USING NO INDEX ] [ create_mv_refresh ] [ FOR UPDATE ] [ { DISABLE | ENABLE } QUERY REWRITE ] AS subquery
CREATE MATERIALIZED VIEW LOG
CREATE MATERIALIZED VIEW LOG ON [ schema. ] table [ physical_attributes_clause | TABLESPACE tablespace | logging_clause | { CACHE | NOCACHE } [ physical_attributes_clause | TABLESPACE tablespace | logging_clause | { CACHE | NOCACHE } ]... ] [ parallel_clause ] [ table_partitioning_clauses ] [ WITH { OBJECT ID | PRIMARY KEY | ROWID | SEQUENCE | (column [, column ]...) } [, { OBJECT ID | PRIMARY KEY | ROWID | SEQUENCE | (column [, column ]...) } ]... [ new_values_clause ] ]
CREATE OPERATOR
CREATE [ OR REPLACE ] OPERATOR [ schema. ] operator binding_clause
CREATE OUTLINE
CREATE [ OR REPLACE ] [ PUBLIC | PRIVATE ] OUTLINE [ outline ] [ FROM [ PUBLIC | PRIVATE ] source_outline ] [ FOR CATEGORY category ] [ ON statement ]
CREATE PACKAGE
CREATE [ OR REPLACE ] PACKAGE [ schema. ]package [ invoker_rights_clause ] { IS | AS } pl/sql_package_spec
CREATE PACKAGE BODY
CREATE [ OR REPLACE ] PACKAGE BODY [ schema. ]package { IS | AS } pl/sql_package_body
CREATE PFILE
CREATE PFILE [= 'pfile_name' ] FROM SPFILE [= 'spfile_name']
CREATE PROCEDURE
CREATE [ OR REPLACE ] PROCEDURE [ schema. ]procedure [ (argument [ { IN | OUT | IN OUT } ] [ NOCOPY ] datatype [ DEFAULT expr ] [, argument [ { IN | OUT | IN OUT } ] [ NOCOPY ] datatype [ DEFAULT expr ] ]... ) ] [ invoker_rights_clause ] { IS | AS } { pl/sql_subprogram_body | call_spec }
CREATE PROFILE
CREATE PROFILE profile LIMIT { resource_parameters | password_parameters } [ resource_parameters | password_parameters ]...
CREATE RESTORE POINT
CREATE RESTORE POINT restore_point [ GUARANTEE FLASHBACK DATABASE ]
CREATE ROLE
CREATE ROLE role [ NOT IDENTIFIED | IDENTIFIED { BY password | USING [ schema. ] package | EXTERNALLY | GLOBALLY } ]
CREATE ROLLBACK SEGMENT
CREATE [ PUBLIC ] ROLLBACK SEGMENT rollback_segment [ { TABLESPACE tablespace | storage_clause } [ TABLESPACE tablespace | storage_clause ]... ]
CREATE SCHEMA
CREATE SCHEMA AUTHORIZATION schema { create_table_statement | create_view_statement | grant_statement } [ create_table_statement | create_view_statement | grant_statement ]...
CREATE SEQUENCE
CREATE SEQUENCE [ schema. ]sequence [ { INCREMENT BY | START WITH } integer | { MAXVALUE integer | NOMAXVALUE } | { MINVALUE integer | NOMINVALUE } | { CYCLE | NOCYCLE } | { CACHE integer | NOCACHE } | { ORDER | NOORDER } ] [ { INCREMENT BY | START WITH } integer | { MAXVALUE integer | NOMAXVALUE } | { MINVALUE integer | NOMINVALUE } | { CYCLE | NOCYCLE } | { CACHE integer | NOCACHE } | { ORDER | NOORDER } ]...
CREATE SPFILE
CREATE SPFILE [= 'spfile_name' ] FROM PFILE [= 'pfile_name' ]
CREATE SYNONYM
CREATE [ OR REPLACE ] [ PUBLIC ] SYNONYM [ schema. ]synonym FOR [ schema. ]object [ @ dblink ]
CREATE TABLE
{ relational_table | object_table | XMLType_table } CREATE TABLESPACE CREATE [ BIGFILE | SMALLFILE ] { permanent_tablespace_clause | temporary_tablespace_clause | undo_tablespace_clause }
CREATE TRIGGER
CREATE [ OR REPLACE ] TRIGGER [ schema. ]trigger { BEFORE | AFTER | INSTEAD OF } { dml_event_clause | { ddl_event [ OR ddl_event ]... | database_event [ OR database_event ]... } ON { [ schema. ]SCHEMA | DATABASE } } [ WHEN (condition) ] { pl/sql_block | call_procedure_statement }
CREATE TYPE
{ create_incomplete_type | create_object_type | create_varray_type | create_nested_table_type } CREATE TYPE BODY CREATE [ OR REPLACE ] TYPE BODY [ schema. ]type_name { IS | AS } { subprogram_declaration | map_order_func_declaration } [, { subprogram_declaration | map_order_func_declaration } ]... END
CREATE USER
CREATE USER user IDENTIFIED { BY password | EXTERNALLY [ AS 'certificate_DN' ] | GLOBALLY [ AS '[ directory_DN ]' ] } [ DEFAULT TABLESPACE tablespace | TEMPORARY TABLESPACE { tablespace | tablespace_group_name } | QUOTA size_clause | UNLIMITED } ON tablespace [ QUOTA size_clause | UNLIMITED } ON tablespace ]... | PROFILE profile | PASSWORD EXPIRE | ACCOUNT { LOCK | UNLOCK } [ DEFAULT TABLESPACE tablespace | TEMPORARY TABLESPACE { tablespace | tablespace_group_name } | QUOTA size_clause | UNLIMITED } ON tablespace [ QUOTA size_clause | UNLIMITED } ON tablespace ]... | PROFILE profile | PASSWORD EXPIRE | ACCOUNT { LOCK | UNLOCK } ]... ]
CREATE VIEW
CREATE [ OR REPLACE ] [ [ NO ] FORCE ] VIEW [ schema. ]view [ (alias [ inline_constraint [ inline_constraint ]... ] | out_of_line_constraint [, alias [ inline_constraint [ inline_constraint ]... ] | out_of_line_constraint ]... ) | object_view_clause | XMLType_view_clause ] AS subquery [ subquery_restriction_clause ]
DELETE
DELETE [ hint ] [ FROM ] { dml_table_expression_clause | ONLY (dml_table_expression_clause) } [ t_alias ] [ where_clause ] [ returning_clause ] [error_logging_clause]
DISASSOCIATE STATISTICS FROM
{ COLUMNS [ schema. ]table.column [, [ schema. ]table.column ]... | FUNCTIONS [ schema. ]function [, [ schema. ]function ]... | PACKAGES [ schema. ]package [, [ schema. ]package ]... | TYPES [ schema. ]type [, [ schema. ]type ]... | INDEXES [ schema. ]index [, [ schema. ]index ]... | INDEXTYPES [ schema. ]indextype [, [ schema. ]indextype ]... } [ FORCE ]
DROP CLUSTER
DROP CLUSTER [ schema. ]cluster [ INCLUDING TABLES [ CASCADE CONSTRAINTS ] ]
DROP CONTEXT
DROP CONTEXT namespace
DROP DATABASE
DROP DATABASE
DROP DATABASE LINK
DROP [ PUBLIC ] DATABASE LINK dblink
DROP DIMENSION
DROP DIMENSION [ schema. ]dimension
DROP DIRECTORY
DROP DIRECTORY directory_name
DROP DISKGROUP
DROP DISKGROUP diskgroup_name [ { INCLUDING | EXCLUDING } CONTENTS ]
DROP FUNCTION
DROP FUNCTION [ schema. ]function_name
DROP INDEX
DROP INDEX [ schema. ]index [ FORCE ]
DROP INDEXTYPE
DROP INDEXTYPE [ schema. ]indextype [ FORCE ]
DROP JAVA
DROP JAVA { SOURCE | CLASS | RESOURCE } [ schema. ]object_name
DROP LIBRARY
DROP LIBRARY library_name
DROP MATERIALIZED VIEW
DROP MATERIALIZED VIEW [ schema. ]materialized_view [ PRESERVE TABLE ]
DROP MATERIALIZED VIEW LOG
DROP MATERIALIZED VIEW LOG ON [ schema. ]table
DROP OPERATOR
DROP OPERATOR [ schema. ]operator [ FORCE ]
DROP OUTLINE
DROP OUTLINE outline
DROP PACKAGE
DROP PACKAGE [ BODY ] [ schema. ]package
DROP PROCEDURE
DROP PROCEDURE [ schema. ]procedure
DROP PROFILE
DROP PROFILE profile [ CASCADE ]
DROP RESTORE POINT
DROP RESTORE POINT restore_point
DROP ROLE
DROP ROLE role
DROP ROLLBACK SEGMENT
DROP ROLLBACK SEGMENT rollback_segment
DROP SEQUENCE
DROP SEQUENCE [ schema. ]sequence_name
DROP SYNONYM
DROP [ PUBLIC ] SYNONYM [ schema. ]synonym [ FORCE ]
DROP TABLE
DROP TABLE [ schema. ]table [ CASCADE CONSTRAINTS ] [ PURGE ]
DROP TABLESPACE
DROP TABLESPACE tablespace [ INCLUDING CONTENTS [ {AND | KEEP} DATAFILES ] [ CASCADE CONSTRAINTS ] ]
DROP TRIGGER
DROP TRIGGER [ schema. ]trigger
DROP TYPE
DROP TYPE [ schema. ]type_name [ FORCE | VALIDATE ]
DROP TYPE BODY
DROP TYPE BODY [ schema. ]type_name
DROP USER
DROP USER user [ CASCADE ]
DROP VIEW
DROP VIEW [ schema. ] view [ CASCADE CONSTRAINTS ]
EXPLAIN PLAN
EXPLAIN PLAN [ SET STATEMENT_ID = string ] [ INTO [ schema. ]table [ @ dblink ] ] FOR statement
FLASHBACK DATABASE
FLASHBACK [ STANDBY ] DATABASE [ database ] { TO { { SCN | TIMESTAMP } expr | RESTORE POINT restore_point } | TO BEFORE { SCN | TIMESTAMP} expr | RESETLOGS } }
FLASHBACK TABLE
FLASHBACK TABLE [ schema. ]table [, [ schema. ]table ]... TO { { SCN | TIMESTAMP } expr | RESTORE POINT restore_point } [ { ENABLE | DISABLE } TRIGGERS ] | BEFORE DROP [ RENAME TO table ] }
GRANT
GRANT { grant_system_privileges | grant_object_privileges }
INSERT
INSERT [ hint ] { single_table_insert | multi_table_insert }
LOCK TABLE
LOCK TABLE [ schema. ] { table | view } [ { PARTITION (partition) | SUBPARTITION (subpartition) } | @ dblink ] [, [ schema. ] { table | view } [ { PARTITION (partition) | SUBPARTITION (subpartition) } | @ dblink ] ]... IN lockmode MODE [ NOWAIT ]
MERGE
MERGE [ hint ] INTO [ schema. ] { table | view } [ t_alias ] USING [ schema. ] { table | view | subquery } [ t_alias ] ON ( condition ) [ merge_update_clause ] [ merge_insert_clause ] [ error_logging_clause ]
NOAUDIT
NOAUDIT { sql_statement_clause | schema_object_clause | NETWORK } [ WHENEVER [ NOT ] SUCCESSFUL ]
PURGE
PURGE { { TABLE table | INDEX index } | { RECYCLEBIN | DBA_RECYCLEBIN } | TABLESPACE tablespace [ USER user ] } ; DO NOT IMPORT
RENAME
RENAME old_name TO new_name
REVOKE
REVOKE { revoke_system_privileges | revoke_object_privileges }
ROLLBACK
ROLLBACK [ WORK ] [ TO [ SAVEPOINT ] savepoint | FORCE string ]
SAVEPOINT
SAVEPOINT savepoint
SELECT
subquery [ for_update_clause ]
SET CONSTRAINT / SET CONSTRAINTS
SET { CONSTRAINT | CONSTRAINTS } { constraint [, constraint ]... | ALL } { IMMEDIATE | DEFERRED }
SET ROLE
SET ROLE { role [ IDENTIFIED BY password ] [, role [ IDENTIFIED BY password ] ]... | ALL [ EXCEPT role [, role ]... ] | NONE }
SET TRANSACTION
SET TRANSACTION { { READ { ONLY | WRITE } | ISOLATION LEVEL { SERIALIZABLE | READ COMMITTED } | USE ROLLBACK SEGMENT rollback_segment } [ NAME string ] | NAME string }
TRUNCATE
TRUNCATE { TABLE [ schema. ]table [ { PRESERVE | PURGE } MATERIALIZED VIEW LOG ] | CLUSTER [ schema. ]cluster } [ { DROP | REUSE } STORAGE ]
UPDATE
UPDATE [ hint ] { dml_table_expression_clause | ONLY (dml_table_expression_clause) } [ t_alias ] update_set_clause [ where_clause ] [ returning_clause ] [error_logging_clause]
Reference:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14195/sqlqr01.htm#i87990
http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19201-02/clauses.html#4240
http://www.ss64.com/ora/database_a.html
SQL関数
分析ファンクション
分析ファンクション
分析ファンクションは、行のグループに基づいて集計値を計算します。各グループに対して複数の行を戻す点で、集計ファンクションと異なります。行のグループをウィンドウといい、analytic_clauseで定義されます。各行に対して、行のスライディング・ウィンドウが定義されます。このウィンドウによって、カレント行の計算に使用される行の範囲が決定されます。ウィンドウの大きさは、行の物理数値または時間などのロジカル・インターバルに基づきます。
分析ファンクションは、問合せで最後に実行される演算(最後のORDER BY句を除く)の集合です。すべての結合およびすべてのWHERE、GROUP BYおよびHAVING句は、分析ファンクションが処理される前に実行されます。そのため、分析ファンクションは、SELECT構文のリストまたはORDER BY句のみに指定できます。
通常、分析ファンクションは、累積集計、移動集計、センター集計およびレポート集計の実行に使用されます。
-- 総合文法 analytic_function::= analytic_function([ arguments ]) --analytic_clause OVER ( --query_partition_clause [PARTITION BY { value_expr[, value_expr ]... | ( value_expr[, value_expr ]... ) } ] --order_by_clause [ ORDER [ SIBLINGS ] BY { expr | position | c_alias } [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] [, { expr | position | c_alias } [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] ]... --windowing_clause [ { ROWS | RANGE } { BETWEEN { UNBOUNDED PRECEDING | CURRENT ROW | value_expr { PRECEDING | FOLLOWING } } AND { UNBOUNDED FOLLOWING | CURRENT ROW | value_expr { PRECEDING | FOLLOWING } } | { UNBOUNDED PRECEDING | CURRENT ROW | value_expr PRECEDING } } ] ]) --それぞれのクローズの文法 analytic_function::= analytic_function([ arguments ]) OVER (analytic_clause) analytic_clause::= [ query_partition_clause ] [ order_by_clause [ windowing_clause ] ] query_partition_clause::= PARTITION BY { value_expr[, value_expr ]... | ( value_expr[, value_expr ]... ) } order_by_clause::= ORDER [ SIBLINGS ] BY { expr | position | c_alias } [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] [, { expr | position | c_alias } [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] ]... windowing_clause::= { ROWS | RANGE } { BETWEEN { UNBOUNDED PRECEDING | CURRENT ROW | value_expr { PRECEDING | FOLLOWING } } AND { UNBOUNDED FOLLOWING | CURRENT ROW | value_expr { PRECEDING | FOLLOWING } } | { UNBOUNDED PRECEDING | CURRENT ROW | value_expr PRECEDING } }
analytic_function
分析ファンクションの名前を指定します(セマンティクスの説明の後に示す分析ファンクションのリストを参照)。
引数
分析ファンクションには引数を0~3個指定します。引数には、任意の数値データ型、または暗黙的に数値データ型に変換可能な数値以外のデータ型を指定できます。Oracleは、数値の優先順位が最も高い引数を判断し、残りの引数をそのデータ型に暗黙的に変換します。個々のファンクションに特に指定がないかぎり、戻り型もその引数のデータ型となります。
参照:
数値の優先順位の詳細は、「数値の優先順位」を参照してください。暗黙的な変換の詳細は、表2-10「暗黙的な型変換のマトリックス」を参照してください。
analytic_clause
OVER analytic_clause句は、ファンクションが問合せ結果セットを操作することを示します。FROM、WHERE、GROUP BYおよびHAVING句の後に計算されます。SELECT構文のリストのこの句またはORDER BY句に分析ファンクションを指定できます。分析ファンクションに基づいて、問合せの結果をフィルタするには、これらのファンクションを親問合せ内でネストした後、ネストされた副問合せの結果をフィルタします。
analytic_clauseの注意事項:
analytic_clauseには、次の注意事項があります。
analytic_clauseのどの部分にも、分析ファンクションを指定できません。つまり、分析ファンクションはネストできません。ただし、副問合せで分析ファンクションを指定して、別の分析ファンクションを計算することはできます。
OVER analytic_clauseには、組込み分析ファンクションと同様に、ユーザー定義の分析ファンクションを指定できます。「CREATE FUNCTION」を参照してください。
query_partition_clause
PARTITION BY句を使用すると、1つ以上のvalue_exprに基づいて、問合せ結果セットをグループに分割できます。この句を省略すると、ファンクションは問合せ結果セットのすべての行を単一のグループとして扱います。
分析ファンクションでquery_partition_clauseを使用するには、構文の上位ブランチ(カッコなし)を使用します。この句をモデルの問合せ(model_column_clauses内)またはパーティション化された外部結合(outer_join_clause内)で使用するには、構文の下位ブランチ(カッコ付き)を使用します。
同じまたは異なるPARTITION BYキーで、同じ問合せに複数の分析ファンクションを指定できます。
問い合せているオブジェクトにパラレル属性があり、query_partition_clauseで分析ファンクションを指定する場合は、ファンクションの計算もパラレル化されます。
有効な値のvalue_exprは、定数、列、非分析ファンクション、ファンクション式、またはこれらのいずれかを含む式です。
order_by_clause
order_by_clauseを使用すると、パーティション内でのデータの順序付け方法を指定できます。PERCENTILE_CONTおよび(単一キーのみを適用する)PERCENTILE_DISC以外の分析ファンクションでは、各キーがvalue_exprで定義され、順序付けシーケンスで修飾された複数キーのパーティションの値を順序付けできます。
各ファンクションには、複数の順序式を指定できます。これは、2番目の式が最初の式にある同一値との間の関連性を変換できるため、値をランク付けするファンクションを使用する場合に特に有効です。
order_by_clauseの結果が複数行の個々の値である場合、ファンクションは各行の同じ値を戻します。この動作の詳細は、「SUM」の分析例を参照してください。
ORDER BY句の制限事項:
ORDER BY句には次の制限事項があります。
order_by_clauseを分析ファンクションで使用する場合、式(expr)が必要です。SIBLINGSキーワードは無効です(これは、階層問合せでのみ有効です)。位置(position)および列別名(c_alias)も無効です。それ以外で使用する場合、このorder_by_clauseは、問合せまたは副問合せ全体を順序付ける場合に使用するものと同じです。
RANGEキーワードを使用する分析ファンクションでは、次の2つのウィンドウのいずれかを指定する場合に、ORDER BY句で複数のソート・キーを使用できます。
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。この短縮形は、RANGE UNBOUNDED PRECEDINGです。
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING。この短縮形は、RANGE UNBOUNDED FOLLOWINGです。
この2つ以外のウィンドウ境界では、分析ファンクションのORDER BY句でソート・キーを1つしか持てません。この制限事項は、ROWキーワードで指定したウィンドウ境界には適用されません。
ASC | DESC
順序付けシーケンス(昇順または降順)を指定します。デフォルトはASCです。
NULLS FIRST | NULLS LAST
NULL値を含む戻された行が順序の最初にくるか、最後にくるかを指定します。
NULLS LASTは昇順のデフォルトで、NULLS FIRSTは降順のデフォルトです。
分析ファンクションは、常に、ファンクションのorder_by_clauseで指定された順序で行を操作します。ただし、ファンクションのorder_by_clauseは結果の順序を保証しません。最終結果の順序を保証するには、問合せのorder_by_clauseを使用してください。
参照:
この句の詳細は、「SELECT」の「order_by_clause」を参照してください。
windowing_clause
一部の分析ファンクションでは、windowing_clauseを使用できます。7-16ページに示す分析ファンクションのリストでは、windowing_clauseを使用できるファンクションにアスタリスク(*)が付いています。
ROWS | RANGE
これらのキーワードは、各行に対して、ファンクションの結果の計算に使用されるウィンドウ(行の物理集合または論理集合)を定義します。ファンクションは、ウィンドウのすべての行に適用されます。ウィンドウは、問合せ結果セット内またはパーティションの上から下まで移動します。
ROWSは、物理単位(行)でウィンドウを指定します。
RANGEは、論理オフセットとしてウィンドウを指定します。
order_by_clauseを指定しないと、この句を指定できません。RANGE句で定義したウィンドウ境界には、order_by_clauseで指定できる式が1つのみのものもあります。詳細は、「ORDER BY句の制限事項:」を参照してください。
分析ファンクションが論理オフセットで戻す値は、常に決定的なものです。ただし、分析ファンクションが物理オフセットで戻す値は、順序式の結果が一意の順序にならないかぎり、非決定的な結果を生成することがあります。order_by_clauseに複数の列を指定して、結果の順序を一意にする必要があります。
BETWEEN …AND
BETWEEN … AND句を使用すると、ウィンドウにスタート・ポイントおよびエンド・ポイントを指定できます。最初の式(ANDの前)はスタート・ポイントを定義し、2番目の式(ANDの後)はエンド・ポイントを定義します。
BETWEENを省略してエンド・ポイントを1つのみ指定すると、Oracleはそれをスタート・ポイントとみなし、デフォルトでカレント行をエンド・ポイントに指定します。
UNBOUNDED PRECEDING
UNBOUNDED PRECEDINGを指定すると、パーティションの最初の行で、ウィンドウが開始します。これはスタート・ポイントの指定で、エンド・ポイントの指定としては使用できません。
UNBOUNDED FOLLOWING
UNBOUNDED FOLLOWINGを指定すると、パーティションの最後の行で、ウィンドウが終了します。これはエンド・ポイントの指定で、スタート・ポイントの指定としては使用できません。
CURRENT ROW
スタート・ポイントとして、ウィンドウがカレント行または値(それぞれROWまたはRANGEを指定したかどうかに基づく)で開始することを指定します。この場合、value_expr PRECEDINGをエンド・ポイントにできません。
エンド・ポイントとして、ウィンドウがカレント行または値(それぞれROWまたはRANGEを明示的に指定したかどうかに基づく)で終了することを指定します。この場合、value_expr FOLLOWINGをスタート・ポイントにできません。
value_expr PRECEDINGまたはvalue_expr FOLLOWING
RANGEまたはROWに対して、次のことがいえます。
value_expr FOLLOWINGがスタート・ポイントの場合、エンド・ポイントはvalue_expr FOLLOWINGである必要があります。
value_expr PRECEDINGがエンド・ポイントの場合、スタート・ポイントはvalue_expr PRECEDINGである必要があります。
数値形式の時間間隔で定義されている論理ウィンドウを定義する場合、変換ファンクションを使用する必要があります。
参照:
数値時間から間隔への変換の詳細は、「NUMTOYMINTERVAL」および「NUMTODSINTERVAL」を参照してください。
ROWSを指定した場合、次のことがいえます。
value_exprは物理オフセットになります。これは定数または式であり、正数値に評価する必要があります。
value_exprがスタート・ポイントの一部の場合、エンド・ポイントの前にある行に評価する必要があります。
RANGEを指定した場合、次のことがいえます。
value_exprは論理オフセットになります。これは、正数値または期間リテラルに評価する定数または式である必要があります。期間リテラルの詳細は、「リテラル」を参照してください。
order_by_clauseには、式を1つのみ指定できます。
value_exprが数値に対して評価を行う場合、ORDER BY exprは数値データ型またはDATEデータ型である必要があります。
value_exprが間隔値に対して評価を行う場合、ORDER BY exprはDATEデータ型である必要があります。
windowing_clauseを完全に省略した場合、デフォルトでRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWになります。
分析ファンクションは、通常、データ・ウェアハウス環境で使用されます。次に示す分析ファンクションのリストでは、windowing_clauseを含む完全な構文を使用できるファンクションには、アスタリスク(*)が付いています。
AVG *
CORR *
COVAR_POP *
COVAR_SAMP *
COUNT *
CUME_DIST
DENSE_RANK
FIRST
FIRST_VALUE *
LAG
LAST
LAST_VALUE *
LEAD
MAX *
MIN *
NTILE
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC
RANK
RATIO_TO_REPORT
REGR_(線形回帰)ファンクション *
ROW_NUMBER
STDDEV *
STDDEV_POP *
STDDEV_SAMP *
SUM *
VAR_POP *
VAR_SAMP *
VARIANCE *
参照:
これらのファンクションおよびその使用方法の詳細は、下記の『Oracle Databaseデータ・ウェアハウス・ガイド』の「分析計算およびレポート」を参照してください。
http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19217-02/analysis.htm#i1007779
http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19201-02/functions.html
http://homepage2.nifty.com/sak/w_sak3/doc/sysbrd/sq_kj04_3.htm
ABS
ABS(n)
ACOS
ACOS(n)
ADD_MONTHS
ADD_MONTHS(date, integer)
analytic_function
analytic_function([ arguments ]) OVER (analytic_clause)
APPENDCHILDXML
APPENDCHILDXML ( XMLType_instance, XPath_string, value_expr [, namespace_string ] )
ASCII
ASCII(char)
ASCIISTR
ASCIISTR(char)
ASIN
ASIN(n)
ATAN
ATAN(n)
ATAN2
ATAN2(n1 { , | / } n2)
AVG
AVG([ DISTINCT | ALL ] expr) [ OVER(analytic_clause) ]
BFILENAME
BFILENAME('directory', 'filename')
BIN_TO_NUM
BIN_TO_NUM(expr [, expr ]... )
BITAND
BITAND(expr1, expr2)
CARDINALITY
CARDINALITY(nested_table)
CAST
CAST({ expr | MULTISET (subquery) } AS type_name)
CEIL
CEIL(n)
CHARTOROWID
CHARTOROWID(char)
CHR
CHR(n [ USING NCHAR_CS ])
CLUSTER_ID
CLUSTER_ID ( [ schema . ] model mining_attribute_clause )
CLUSTER_PROBABILITY
CLUSTER_PROBABILITY ( [ schema . ] model [ , cluster_id ] mining_attribute_clause )
CLUSTER_SET
CLUSTER_SET ( [ schema . ] model [ , topN [ , cutoff ] ] mining_attribute_clause )
COALESCE
COALESCE(expr [, expr ]...)
COLLECT
COLLECT (column)
COMPOSE
COMPOSE(char)
CONCAT
CONCAT(char1, char2)
CONVERT
CONVERT(char, dest_char_set[, source_char_set ])
CORR
CORR(expr1, expr2) [ OVER (analytic_clause) ]
CORR_K
{ CORR_K | CORR_S } (expr1, expr2 [, { COEFFICIENT | ONE_SIDED_SIG | ONE_SIDED_SIG_POS | ONE_SIDED_SIG_NEG | TWO_SIDED_SIG } ] )
COS
COS(n)
COSH
COSH(n)
COUNT
COUNT({ * | [ DISTINCT | ALL ] expr }) [ OVER (analytic_clause) ] COVAR_POP COVAR_POP(expr1, expr2) [ OVER (analytic_clause) ] COVAR_SAMP COVAR_SAMP(expr1, expr2) [ OVER (analytic_clause) ] CUME_DIST(集計) CUME_DIST(expr[,expr ]...) WITHIN GROUP (ORDER BY expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] [, expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] ]... )
CUME_DIST(分析)
CUME_DIST( ) OVER ([ query_partition_clause ] order_by_clause)
CURRENT_DATE
CURRENT_DATE CURRENT_TIMESTAMP CURRENT_TIMESTAMP [ (precision) ] CV CV([ dimension_column ])
DBTIMEZONE
DBTIMEZONE DECODE DECODE(expr, search, result [, search, result ]... [, default ] )
DECOMPOSE
DECOMPOSE( string [ CANONICAL | COMPATIBILITY ] )
DELETXML
DELETEXML ( XMLType_instance, XPath_string [, namespace_string ] )
DENSE_RANK(集計)
DENSE_RANK(expr [, expr ]...) WITHIN GROUP (ORDER BY expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] [,expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] ]... )
DENSE_RANK(集計)
DENSE_RANK( ) OVER([ query_partition_clause ] order_by_clause)
DEPTH
DEPTH(correlation_integer)
DEREF
DEREF(expr)
DUMP
DUMP(expr[, return_fmt [, start_position [, length ] ] ] )
EMPTY_BLOB
{ EMPTY_BLOB | EMPTY_CLOB }( )
EXISTSNODE
EXISTSNODE (XMLType_instance, XPath_string [, namespace_string ] )
EXP
EXP(n)
EXTRACT(日時)
EXTRACT( { { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND } | { TIMEZONE_HOUR | TIMEZONE_MINUTE } | { TIMEZONE_REGION | TIMEZONE_ABBR } } FROM { datetime_value_expression | interval_value_expression } )
EXTRACT(XML)
EXTRACT(XMLType_instance, XPath_string [, namespace_string ] )
EXTRACTVALUE
EXTRACTVALUE (XMLType_instance, XPath_string [, namespace_string ] )
FEATURE_ID
FEATURE_ID ( [ schema . ] model mining_attribute_clause )
FEATURE_SET
FEATURE_SET ( [ schema . ] model [ , topN [ , cutoff ] ] mining_attribute_clause )
FEATURE_VALUE
FEATURE_VALUE ( [ schema . ] model [ , feature_id ] mining_attribute_clause )
FIRST
aggregate_function KEEP (DENSE_RANK FIRST ORDER BY expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] [, expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] ]... ) [ OVER query_partition_clause ]
FIRST_VALUE
FIRST_VALUE (expr [ IGNORE NULLS ]) OVER (analytic_clause)
FLOOR
FLOOR(n)
FROM_TZ
FROM_TZ (timestamp_value, time_zone_value)
GREATEST
GREATEST(expr [, expr ]...)
GROUP_ID
GROUP_ID( )
GROUPING
GROUPING(expr)
GROUPING_ID
GROUPING_ID(expr [, expr ]...)
HEXTORAW
HEXTORAW(char)
INITCAP
INITCAP(char)
INSERTCHILDXML
INSERTCHILDXML ( XMLType_instance, XPath_string, child_expr, value_expr [, namespace_string ] )
INSERTXMLBEFORE
INSERTXMLBEFORE ( XMLType_instance, XPath_string, value_expr [, namespace_string ] )
INSTR
{ INSTR | INSTRB | INSTRC | INSTR2 | INSTR4 } (string , substring [, position [, occurrence ] ])
ITERATION_NUMBER
ITERATION_NUMBER
LAG
LAG(value_expr [, offset ] [, default ]) OVER ([ query_partition_clause ] order_by_clause)
LAST
aggregate_function KEEP (DENSE_RANK LAST ORDER BY expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] [, expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] ]... ) [ OVER query_partition_clause ]
LAST_DAY
LAST_DAY(date)
LAST_VALUE
LAST_VALUE(expr [ IGNORE NULLS ]) OVER (analytic_clause)
LEAD
LEAD(value_expr [, offset ] [, default ]) OVER ([ query_partition_clause ] order_by_clause)
LEAST
LEAST(expr [, expr ]...)
LENGTH
{ LENGTH | LENGTHB | LENGTHC | LENGTH2 | LENGTH4 } (char)
LN
LN(n)
LNNVL
LNNVL(condition)
LOCALTIMESTAMP
LOCALTIMESTAMP [ (timestamp_precision) ]
LOG
LOG(n2, n1)
LOWER
LOWER(char)
LPAD
LPAD(expr1, n [, expr2 ])
LTRIM
LTRIM(char [, set ])
MAKE_REF
MAKE_REF({ table | view } , key [, key ]...)
MAX
MAX([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]
MEDIAN
MEDIAN(expr) [ OVER (query_partition_clause) ]
MIN
MIN([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]
MOD
MOD(n2, n1)
MONTHS_BETWEEN
MONTHS_BETWEEN(date1, date2)
NANVL
NANVL(n2, n1)
NCHR
NCHR(number)
NEW_TIME
NEW_TIME(date, timezone1, timezone2)
NEXT_DAY
NEXT_DAY(date, char)
NLS_CHARSET_DECL_LEN
NLS_CHARSET_DECL_LEN(byte_count, 'char_set_id')
NLS_CHARSET_ID
NLS_CHARSET_ID ( string )
NLS_CHARSET_NAME
NLS_CHARSET_NAME(number)
NLS_INITCAP
NLS_INITCAP(char [, 'nlsparam' ])
NLS_LOWER
NLS_LOWER(char [, 'nlsparam' ])
NLS_UPPER
NLS_UPPER(char [, 'nlsparam' ])
NLSSORT
NLSSORT(char [, 'nlsparam' ])
NTILE
NTILE(expr) OVER ([ query_partition_clause ] order_by_clause)
NULLIF
NULLIF(expr1, expr2)
NUMTODSINTERVAL
NUMTODSINTERVAL(n, 'interval_unit')
NUMTOYMINTERVAL
NUMTOYMINTERVAL(n, 'interval_unit')
NVL
NVL(expr1, expr2)
NVL2
NVL2(expr1, expr2, expr3)
ORA_HASH
ORA_HASH (expr [, max_bucket [, seed_value ] ])
PATH
PATH (correlation_integer)
PERCENT_RANK(集計)
PERCENT_RANK(expr [, expr ]...) WITHIN GROUP (ORDER BY expr [ DESC | ASC ] [NULLS { FIRST | LAST } ] [, expr [ DESC | ASC ] [NULLS { FIRST | LAST } ] ]... )
PERCENT_RANK(分析)
PERCENT_RANK( ) OVER ([ query_partition_clause ] order_by_clause)
PERCENTILE_CONT
PERCENTILE_CONT(expr) WITHIN GROUP (ORDER BY expr [ DESC | ASC ]) [ OVER (query_partition_clause) ]
PERCENTILE_DISC
PERCENTILE_DISC(expr) WITHIN GROUP (ORDER BY expr [ DESC | ASC ]) [ OVER (query_partition_clause) ]
POWER
POWER(n2, n1)
POWERMULTISET
POWERMULTISET(expr)
POWERMULTISET_BY_CARDINALITY
POWERMULTISET_BY_CARDINALITY(expr, cardinality)
PREDICTION
PREDICTION ( [ schema . ] model [ cost_matrix_clause ] mining_attribute_clause )
PREDICTION_COST
PREDICTION_COST ( [ schema . ] model [ , class ] cost_matrix_clause mining_attribute_clause )
PREDICTION_DETAILS
PREDICTION_DETAILS ( [ schema . ] model mining_attribute_clause )
PREDICTION_PROBABILITY
PREDICTION_PROBABILITY ( [ schema . ] model [ , class ] mining_attribute_clause )
PREDICTION_SET
PREDICTION_SET ( [ schema . ] model [ , bestN [ , cutoff ] ] [ cost_matrix_clause ] mining_attribute_clause )
PRESENTNNV
PRESENTNNV(cell_reference, expr1, expr2)
PRESENTV
PRESENTV(cell_reference, expr1, expr2)
PREVIOUS
PREVIOUS(cell_reference)
RANK(集計)
RANK(expr [, expr ]...) WITHIN GROUP (ORDER BY expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] [, expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] ]... )
RANK(分析)
RANK( ) OVER ([ query_partition_clause ] order_by_clause)
RATIO_TO_REPORT
RATIO_TO_REPORT(expr) OVER ([ query_partition_clause ])
RAWTOHEX
RAWTOHEX(raw)
RAWTONHEX
RAWTONHEX(raw)
REF
REF (correlation_variable)
REFTOHEX
REFTOHEX (expr)
REGEXP_INSTR
REGEXP_INSTR (source_char, pattern [, position [, occurrence [, return_option [, match_parameter ] ] ] ] )
REGEXP_REPLACE
REGEXP_REPLACE(source_char, pattern [, replace_string [, position [, occurrence [, match_parameter ] ] ] ] )
REGEXP_SUBSTR
REGEXP_SUBSTR(source_char, pattern [, position [, occurrence [, match_parameter ] ] ] )
REGR_AVGX
{ REGR_SLOPE | REGR_INTERCEPT | REGR_COUNT | REGR_R2 | REGR_AVGX | REGR_AVGY | REGR_SXX | REGR_SYY | REGR_SXY } (expr1 , expr2) [ OVER (analytic_clause) ]
REMAINDER
REMAINDER(n2, n1)
REPLACE
REPLACE(char, search_string [, replacement_string ] )
ROUND(日付)
ROUND(date [, fmt ])
ROUND(数値)
ROUND(n [, integer ])
ROW_NUMBER
ROW_NUMBER( ) OVER ([ query_partition_clause ] order_by_clause)
ROWIDTOCHAR
ROWIDTOCHAR(rowid)
ROWIDTONCHAR
ROWIDTONCHAR(rowid)
RPAD
RPAD(expr1 , n [, expr2 ])
RTRIM
RTRIM(char [, set ])
SCN_TO_TIMESTAMP
SCN_TO_TIMESTAMP(number)
SESSIONTIMEZONE
SESSIONTIMEZONE
SET
SET (nested_table)
SIGN
SIGN(n)
SIN
SIN(n)
SINH
SINH(n)
SOUNDEX
SOUNDEX(char)
SQRT
SQRT(n)
STATS_BINOMIAL_TEST
STATS_BINOMIAL_TEST(expr1, expr2, p [, { TWO_SIDED_PROB | EXACT_PROB | ONE_SIDED_PROB_OR_MORE | ONE_SIDED_PROB_OR_LESS } ] )
STATS_CROSSTAB
STATS_CROSSTAB(expr1, expr2 [, { CHISQ_OBS | CHISQ_SIG | CHISQ_DF | PHI_COEFFICIENT | CRAMERS_V | CONT_COEFFICIENT | COHENS_K } ] )
STATS_F_TEST
STATS_F_TEST(expr1, expr2 [, { { STATISTIC | DF_NUM | DF_DEN | ONE_SIDED_SIG } expr3 | TWO_SIDED_SIG } ] )
STATS_KS_TEST
STATS_KS_TEST(expr1, expr2 [, { STATISTIC | SIG } ] )
STATS_MODE
STATS_MODE(expr)
STATS_MW_TEST
STATS_MW_TEST(expr1, expr2 [, { STATISTIC | U_STATISTIC | ONE_SIDED_SIG "expr3" | TWO_SIDED_SIG } ] )
STATS_ONE_WAY_ANOVA
STATS_ONE_WAY_ANOVA(expr1, expr2 [, { SUM_SQUARES_BETWEEN | SUM_SQUARES_WITHIN | DF_BETWEEN | DF_WITHIN | MEAN_SQUARES_BETWEEN | MEAN_SQUARES_WITHIN | F_RATIO | SIG } ] )
STATS_T_TEST_INDEP
{ STATS_T_TEST_INDEP | STATS_T_TEST_INDEPU | STATS_T_TEST_ONE | STATS_T_TEST_PAIRED } (expr1, expr2 [, { { STATISTIC | ONE_SIDED_SIG } expr3 | TWO_SIDED_SIG | DF } ] )
STATS_WSR_TEST
STATS_WSR_TEST(expr1, expr2 [, { STATISTIC | ONE_SIDED_SIG | TWO_SIDED_SIG } ] )
STDDEV
STDDEV([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]
STDDEV_POP
STDDEV_POP(expr) [ OVER (analytic_clause) ]
STDDEV_SAMP
STDDEV_SAMP(expr) [ OVER (analytic_clause) ]
SUBSTR
{ SUBSTR | SUBSTRB | SUBSTRC | SUBSTR2 | SUBSTR4 } (char, position [, substring_length ])
SUM
SUM([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]
SYS_CONNECT_BY_PATH
SYS_CONNECT_BY_PATH(column, char)
SYS_CONTEXT
SYS_CONTEXT('namespace', 'parameter' [, length ])
SYS_DBURIGEN
SYS_DBURIGEN({ column | attribute } [ rowid ] [, { column | attribute } [ rowid ] ]... [, 'text ( )' ] )
SYS_EXTRACT_UTC
SYS_EXTRACT_UTC(datetime_with_timezone)
SYS_GUID
SYS_GUID( )
SYS_TYPEID
SYS_TYPEID(object_type_value)
SYS_XMLAGG
SYS_XMLAGG(expr [, fmt ])
SYS_XMLGEN
SYS_XMLGEN(expr [, fmt ])
SYSDATE
SYSDATE
SYSTIMESTAMP
SYSTIMESTAMP
TAN
TAN(n)
TANH
TANH(n)
TIMESTAMP_TO_SCN
TIMESTAMP_TO_SCN(timestamp)
TO_BINARY_DOUBLE
TO_BINARY_DOUBLE(expr [, fmt [, 'nlsparam' ] ])
TO_BINARY_FLOAT
TO_BINARY_FLOAT(expr [, fmt [, 'nlsparam' ] ])
TO_CHAR(文字)
TO_CHAR(nchar | clob | nclob)
TO_CHAR(日時)
TO_CHAR({ datetime | interval } [, fmt [, 'nlsparam' ] ])
TO_CHAR(数値)
TO_CHAR(n [, fmt [, 'nlsparam' ] ])
TO_CLOB
TO_CLOB(lob_column | char)
TO_DATE
TO_DATE(char [, fmt [, 'nlsparam' ] ])
TO_DSINTERVAL
TO_DSINTERVAL(char [, 'nlsparam' ])
TO_LOB
TO_LOB(long_column)
TO_MULTI_BYTE
TO_MULTI_BYTE(char)
TO_NCHAR(文字)
TO_NCHAR({char | clob | nclob})
TO_NCHAR(日時)
TO_NCHAR({ datetime | interval } [, fmt [, 'nlsparam' ] ] )
TO_NCHAR(数値)
TO_NCHAR(n [, fmt [, 'nlsparam' ] ])
TO_NCLOB
TO_NCLOB(lob_column | char)
TO_NUMBER
TO_NUMBER(expr [, fmt [, 'nlsparam' ] ])
TO_SINGLE_BYTE
TO_SINGLE_BYTE(char)
TO_TIMESTAMP
TO_TIMESTAMP(char [, fmt [, 'nlsparam' ] ])
TO_TIMESTAMP_TZ
TO_TIMESTAMP_TZ(char [, fmt [, 'nlsparam' ] ])
TO_YMINTERVAL
TO_YMINTERVAL(char)
TRANSLATE
TRANSLATE(expr, from_string, to_string)
TRANSLATE ... USING
TRANSLATE ( char USING { CHAR_CS | NCHAR_CS } )
TREAT
TREAT(expr AS [ REF ] [ schema. ]type)
TRIM
TRIM([ { { LEADING | TRAILING | BOTH } [ trim_character ] | trim_character } FROM ] trim_source )
TRUNC(日付)
TRUNC(date [, fmt ])
TRUNC(数値)
TRUNC(n1 [, n2 ])
TZ_OFFSET
TZ_OFFSET({ 'time_zone_name' | '{ + | - } hh : mi' | SESSIONTIMEZONE | DBTMEZONE } )
UID
UID UNISTR UNISTR( string )
UPDATEXML
UPDATEXML (XMLType_instance, XPath_string, value_expr [, XPath_string, value_expr ]... [, namespace_string ] )
UPPER
UPPER(char)
USER
USER ユーザー定義ファンクション [ schema. ] { [ package. ]function | user_defined_operator } [ @ dblink. ] [ ([ DISTINCT | ALL ] expr [, expr ]...) ]
USERENV
USERENV('parameter')
VALUE
VALUE(correlation_variable)
VAR_POP
VAR_POP(expr) [ OVER (analytic_clause) ] VAR_SAMP VAR_SAMP(expr) [ OVER (analytic_clause) ] VARIANCE VARIANCE([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ] VSIZE VSIZE(expr)
WIDTH_BUCKET
WIDTH_BUCKET (expr, min_value, max_value, num_buckets)
XMLAGG
XMLAGG(XMLType_instance [ order_by_clause ])
XMLCOLATTVAL
XMLCOLATTVAL (value_expr [ AS c_alias ] [, value_expr [ AS c_alias ] ]... )
XMLCOMMENT
XMLCOMMENT ( value_expr )
XMLCDATA
XMLCDATA ( value_expr )
XMLCONCAT
XMLCONCAT(XMLType_instance [, XMLType_instance ]...)
XMLELEMENT
XMLELEMENT ( [ NAME ] identifier [, XML_attributes_clause ] [, value_expr [ AS c_alias ] [, value_expr [ AS c_alias ] ]... )
XMLFOREST
XMLFOREST ( value_expr [ AS c_alias ] [, value_expr [ AS c_alias ] ]... )
XMLPARSE
XMLPARSE ({ DOCUMENT | CONTENT } value_expr [ WELLFORMED ] )
XMLPI
XMLPI ( [ NAME ] identifier [, value_expr ] )
XMLQUERY
XMLQUERY ( XQuery_string [ XML_passing_clause ] RETURNING CONTENT )
XMLROOT
XMLROOT ( value_expr, VERSION { value_expr | NO VALUE } [, STANDALONE { YES | NO | NO VALUE } ] )
XMLSEQUENCE
XMLSEQUENCE( XMLType_instance | sys_refcursor_instance [, fmt ] )
XMLSERIALIZE
XMLSERIALIZE ( { DOCUMENT | CONTENT } value_expr [ AS datatype ] )
XMLTABLE
XMLTABLE ( [ XML_namespaces_clause , ] XQuery_string XMLTABLE_options )
XMLTABLE_options
[ XML_passing_clause ] [ COLUMNS XML_table_column [, XML_table_column ]... ]
XMLTRANSFORM
XMLTRANSFORM(XMLType_instance, XMLType_instance)
SQLエクスプレッション
CASE式
CASE { simple_case_expression | searched_case_expression } [ else_clause ] END
複合式
{ (expr) | { + | - | PRIOR } expr | expr { * | / | + | - | || } expr } 注意: 二重の縦線はBNF表記法ではなく、 構文の一部(連結を示す)です。
CURSOR式
CURSOR (subquery)
DATETIME式
datetime_value_expr AT { LOCAL | TIME ZONE { ' [ + | - ] hh:mm' | DBTIMEZONE | 'time_zone_name' | expr } }
ファンクション式
組込みSQLファンクションまたはユーザー定義ファンクションを式として使用できます。
期間式
interval_value_expr { DAY [ (leading_field_precision) ] TO SECOND [ (fractional_second_precision) ] | YEAR [ (leading_field_precision) ] TO MONTH }
モデル式
{ measure_column [ { condition | expr }[ , { condition | expr } ...] ] | aggregate_function { [ { condition | expr }[ , { condition | expr } ...] ] | [ single_column_for_loop [, single_column_for_loop] ... ] | [ multi_column_for_loop ] } | analytic_function } 注意: 太字で示されている外側の大カッコは構文の一部です。オプションを表すものではありません。
オブジェクト・アクセス式
{ table_alias.column. | object_table_alias. | (expr). } { attribute [.attribute ]... [.method ([ argument [, argument ]... ]) ] | method ([ argument [, argument ]... ]) } スカラー副問合せ式 1行から1列のみ戻す式として使用できます。
単純式
{ [ query_name. | [schema.] { table. | view. | materialized view. } ] { column | ROWID } | ROWNUM | string | number | sequence. { CURRVAL | NEXTVAL } | NULL }
型コンストラクタ式
[ NEW ] [ schema. ]type_name ([ expr [, expr ]... ])
変数式
:host_variable [ [ INDICATOR ] :indicator_variable ]
SQ'L条件式
論理(ブール)演算子でTRUE、FALSEまたはUNKNOWNを計算する式
複合条件
{ (condition) | NOT condition | condition { AND | OR } condition }
EQUALS_PATH条件
EQUALS_PATH (column, path_string [, correlation_integer ])
EXISTS条件
EXISTS (subquery)
浮動小数点条件
expr IS [ NOT ] { NAN | INFINITE }
グループ比較条件
{ expr { = | != | ^= | <> | > | < | >= | <= } { ANY | SOME | ALL } ({ expression_list | subquery }) | expr [, expr ]... { = | != | ^= | <> } { ANY | SOME | ALL } ({ expression_list [, expression_list ]... | subquery } ) } !=、^=および<>は不等性テストを表します。
IN条件
{ expr [ NOT ] IN ({ expression_list | subquery }) | ( expr [, expr ]... [ NOT ] IN ({ expression_list [, expression_list ]... | subquery } ) ) }
IS A SET条件
nested_table IS [ NOT ] A SET
IS ANY条件
[ dimension_column IS ] ANY
IS EMPTY条件
nested_table IS [ NOT ] EMPTY
IS OF TYPE条件
expr IS [ NOT ] OF [ TYPE ] ([ ONLY ] [ schema. ] type [, [ ONLY ] [ schema. ] type ]... )
IS PRESENT条件
cell_reference IS PRESENT
LIKE条件
char1 [ NOT ] ( LIKE | LIKEC | LIKE2 | LIKE4 ) char2 [ ESCAPE esc_char ]
論理条件
{ NOT | AND | OR }
MEMBER条件
expr [ NOT ] MEMBER [ OF ] nested_table
NULL条件
expr IS [ NOT ] NULL
範囲条件
expr [ NOT ] BETWEEN expr AND expr
REGEXP_LIKE条件
REGEXP_LIKE(source_char, pattern [, match_parameter ] )
単純比較条件
{ expr { = | != | ^= | <> | > | < | >= | <= } expr | (expr [, expr ]...) { = | != | ^= | <> } (subquery) } !=、^=および<>は不等性テストを表します。
SUBMULTISET条件
nested_table1 [ NOT ] SUBMULTISET [ OF ] nested_table2
UNDER_PATH条件
UNDER_PATH (column [, levels ], path_string [, correlation_integer ] )