この章では、Oracle SQL文の構文について説明します。
この章では、次の項目について説明します。
SQL文は、プログラムおよびユーザーがOracle Databaseのデータにアクセスするための手段です。
この項では、各SQL文および関連する構文を示します。副次句の構文については、第5章「副次句」を参照してください。
参照: Oracle SQLの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。 |
ALTER CLUSTER [ schema. ]cluster { physical_attributes_clause | SIZE size_clause | allocate_extent_clause | deallocate_unused_clause | { CACHE | NOCACHE } } ... [ parallel_clause ] ;
ALTER DATABASE [ database ] { startup_clauses | recovery_clauses | database_file_clauses | logfile_clauses | controlfile_clauses | standby_database_clauses | default_settings_clauses | instance_clauses | security_clause } ;
ALTER DIMENSION [ schema. ] dimension { ADD { level_clause | hierarchy_clause | attribute_clause | extended_attribute_clause } } ... | { DROP { LEVEL level [ RESTRICT | CASCADE ] | HIERARCHY hierarchy | ATTRIBUTE attribute [ LEVEL level [ COLUMN column ] ]... } } ... | COMPILE ;
ALTER DISKGROUP { { diskgroup_name { { add_disk_clause | drop_disk_clause } [, { add_disk_clause | drop_disk_clause } ]... | resize_disk_clauses } [ rebalance_diskgroup_clause ] | { disk_online_clause | disk_offline_clause | rebalance_diskgroup_clause | check_diskgroup_clause | diskgroup_template_clauses | diskgroup_directory_clauses | diskgroup_alias_clauses | diskgroup_attributes | drop_diskgroup_file_clause } | { diskgroup_name [, diskgroup_name ] ... | ALL } { undrop_disk_clause | diskgroup_availability } }
ALTER FLASHBACK ARCHIVE flashback_archive { SET DEFAULT | { ADD | MODIFY } TABLESPACE tablespace [flashback_archive_quota] | REMOVE TABLESPACE tablespace_name | MODIFY RETENTION flashback_archive_retention | PURGE { ALL | BEFORE { SCN expr | TIMESTAMP expr} } };
ALTER FUNCTION [ schema. ] function function_compile_clause;
ALTER INDEX [ schema. ]index { { deallocate_unused_clause | allocate_extent_clause | shrink_clause | parallel_clause | physical_attributes_clause | logging_clause } ... | rebuild_clause | PARAMETERS ( 'ODCI_parameters' ) ) | COMPILE | { ENABLE | DISABLE } | UNUSABLE | VISIBLE | INVISIBLE | RENAME TO new_name | COALESCE | { MONITORING | NOMONITORING } USAGE | UPDATE BLOCK REFERENCES | alter_index_partitioning } ;
ALTER INDEXTYPE [ schema. ] indextype { { { ADD | DROP } [ schema. ] operator ( parameter_types ) } ... [ using_type_clause ] | COMPILE } [ WITH LOCAL RANGE PARTITION ] [ storage_table_clause ] ;
ALTER JAVA { SOURCE | CLASS } [ schema. ]object_name [ RESOLVER ( ( match_string [, ] { schema_name | - } )... ) ] { { COMPILE | RESOLVE } | invoker_rights_clause } ;
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 [ 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 [ schema. ] operator { add_binding_clause | drop_binding_clause | COMPILE } ;
ALTER OUTLINE [ PUBLIC | PRIVATE ] outline { REBUILD | RENAME TO new_outline_name | CHANGE CATEGORY TO new_category_name | { ENABLE | DISABLE } } ... ;
ALTER PACKAGE [ schema. ] package package_compile_clause;
ALTER PROCEDURE [ schema. ] procedure procedure_compile_clause;
ALTER PROFILE profile LIMIT { resource_parameters | password_parameters } ... ;
ALTER RESOURCE COST { { CPU_PER_SESSION | CONNECT_TIME | LOGICAL_READS_PER_SESSION | PRIVATE_SGA } integer } ... ;
ALTER ROLE role { NOT IDENTIFIED | IDENTIFIED { BY password | USING [ schema. ] package | EXTERNALLY | GLOBALLY } } ;
ALTER ROLLBACK SEGMENT rollback_segment { ONLINE | OFFLINE | storage_clause | SHRINK [ TO size_clause ] };
ALTER SEQUENCE [ schema. ] sequence { INCREMENT BY integer | { MAXVALUE integer | NOMAXVALUE } | { MINVALUE integer | NOMINVALUE } | { CYCLE | NOCYCLE } | { CACHE integer | NOCACHE } | { ORDER | NOORDER } } ... ;
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 { 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 | rolling_migration_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 [ 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 } ] ... ;
ALTER TABLESPACE tablespace { DEFAULT [ table_compression ] storage_clause | MINIMUM EXTENT size_clause | RESIZE size_clause | COALESCE | SHRINK SPACE [ KEEP size_clause] | 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 [ schema. ] trigger { ENABLE | DISABLE | RENAME TO new_name | trigger_compile_clause } ;
ALTER TYPE [ schema. ]type alter_type_clauses;
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 } ... | PROFILE profile | DEFAULT ROLE { role [, role ]... | ALL [ EXCEPT role [, role ] ... ] | NONE } | PASSWORD EXPIRE | ACCOUNT { LOCK | UNLOCK } } ... | user [, user ]... proxy_clause } ;
ALTER VIEW [ schema. ] view { ADD out_of_line_constraint | MODIFY CONSTRAINT constraint { RELY | NORELY } | DROP { CONSTRAINT constraint | PRIMARY KEY | UNIQUE (column [, column ]...) } | COMPILE } ;
ANALYZE { { TABLE [ schema. ] table | INDEX [ schema. ] index } [ partition_extension_clause ] | CLUSTER [ schema. ] cluster } { validation_clauses | LIST CHAINED ROWS [ into_clause ] | DELETE [ SYSTEM ] STATISTICS } ;
ASSOCIATE STATISTICS WITH { column_association | function_association } [ storage_table_clause ] ;
AUDIT { audit_operation_clause | audit_schema_object_clause | NETWORK } [ BY { SESSION | ACCESS } ] [ WHENEVER [ NOT ] SUCCESSFUL ] ;
CALL { routine_clause | object_access_expression } [ INTO :host_variable [ [ INDICATOR ] :indicator_variable ] ] ;
COMMENT ON { TABLE [ schema. ] { table | view } | COLUMN [ schema. ] { table. | view. | materialized_view. } column | OPERATOR [ schema. ] operator | INDEXTYPE [ schema. ] indextype | MATERIALIZED VIEW materialized_view | MINING MODEL [ schema. ] model } IS string ;
COMMIT [ WORK ] [ [ COMMENT string ] | [ WRITE [ WAIT | NOWAIT ] [ IMMEDIATE | BATCH ] ] | FORCE { string [, integer ] | CORRUPT_XID string | CORRUPT_XID_ALL } ] ;
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 ] } }... ] [ parallel_clause ] [ NOROWDEPENDENCIES | ROWDEPENDENCIES ] [ CACHE | NOCACHE ] ;
CREATE [ OR REPLACE ] CONTEXT namespace USING [ schema. ] package [ INITIALIZED { EXTERNALLY | GLOBALLY } | ACCESSED GLOBALLY ] ;
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 ]... [ character_set_clause ] ;
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 [ SHARED ] [ PUBLIC ] DATABASE LINK dblink [ CONNECT TO { CURRENT_USER | user IDENTIFIED BY password [ dblink_authentication ] } | dblink_authentication ]... [ USING connect_string ] ;
CREATE DIMENSION [ schema. ] dimension level_clause ... { hierarchy_clause | attribute_clause | extended_attribute_clause }... ;
CREATE [ OR REPLACE ] DIRECTORY directory AS 'path_name' ;
CREATE DISKGROUP diskgroup_name [ { HIGH | NORMAL | EXTERNAL } REDUNDANCY ] { [ FAILGROUP failgroup_name ] DISK qualified_disk_clause [, qualified_disk_clause]... } ... [ ATTRIBUTE { 'attribute_name' = 'attribute_value' }... ] ;
CREATE FLASHBACK ARCHIVE [DEFAULT] flashback_archive TABLESPACE tablespace [flashback_archive_quota] flashback_archive_retention ;
CREATE [ OR REPLACE ] FUNCTION plsql_source
CREATE [ UNIQUE | BITMAP ] INDEX [ schema. ] index ON { cluster_index_clause | table_index_clause | bitmap_join_index_clause } [ UNUSABLE ] ;
CREATE [ OR REPLACE ] INDEXTYPE [ schema. ] indextype FOR [ schema. ] operator (paramater_type [, paramater_type ]...) [, [ schema. ] operator (paramater_type [, paramater_type ]...) ]... using_type_clause [WITH LOCAL RANGE PARTITION ] [ storage_table_clause ] ;
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 | - })...) ] { USING { BFILE (directory_object_name, server_file_name) | { CLOB | BLOB | BFILE } subquery | 'key_for_BLOB' } | AS source_char }
CREATE [ OR REPLACE ] LIBRARY [ schema. ] libname { IS | AS } 'filename' [ AGENT 'agent_dblink' ] ;
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 ]... | USING NO INDEX ] [ create_mv_refresh ] [ FOR UPDATE ] [ { DISABLE | ENABLE } QUERY REWRITE ] AS subquery ;
CREATE MATERIALIZED VIEW LOG ON [ schema. ] table [ 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 [ OR REPLACE ] OPERATOR [ schema. ] operator binding_clause ;
CREATE [ OR REPLACE ] [ PUBLIC | PRIVATE ] OUTLINE [ outline ] [ FROM [ PUBLIC | PRIVATE ] source_outline ] [ FOR CATEGORY category ] [ ON statement ] ;
CREATE [ OR REPLACE ] PACKAGE plsql_source
CREATE [ OR REPLACE ] PACKAGE BODY plsql_source
CREATE PFILE [= 'pfile_name' ] FROM { SPFILE [= 'spfile_name'] | MEMORY } ;
CREATE [ OR REPLACE ] PROCEDURE plsql_source;
CREATE PROFILE profile LIMIT { resource_parameters | password_parameters }... ;
CREATE RESTORE POINT restore_point [ AS OF {TIMESTAMP | SCN} expr ] [ PRESERVE | GUARANTEE FLASHBACK DATABASE ];
CREATE ROLE role [ NOT IDENTIFIED | IDENTIFIED { BY password | USING [ schema. ] package | EXTERNALLY | GLOBALLY } ] ;
CREATE [ PUBLIC ] ROLLBACK SEGMENT rollback_segment [ TABLESPACE tablespace | storage_clause ]...];
CREATE SCHEMA AUTHORIZATION schema { create_table_statement | create_view_statement | grant_statement }... ;
CREATE SEQUENCE [ schema. ] sequence [ { INCREMENT BY | START WITH } integer | { MAXVALUE integer | NOMAXVALUE } | { MINVALUE integer | NOMINVALUE } | { CYCLE | NOCYCLE } | { CACHE integer | NOCACHE } | { ORDER | NOORDER } ]... ;
CREATE SPFILE [= 'spfile_name' ] FROM { PFILE [= 'pfile_name' ] | MEMORY } ;
CREATE [ OR REPLACE ] [ PUBLIC ] SYNONYM [ schema. ] synonym FOR [ schema. ] object [ @ dblink ] ;
CREATE [ GLOBAL TEMPORARY ] TABLE [ schema. ] table { relational_table | object_table | XMLType_table }
CREATE [ BIGFILE | SMALLFILE ] { permanent_tablespace_clause | temporary_tablespace_clause | undo_tablespace_clause } ;
CREATE [ OR REPLACE ] TRIGGER plsql_source
CREATE [OR REPLACE] TYPE plsql_source;
CREATE [ OR REPLACE ] TYPE BODY plsql_source
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 }... EDO | PROFILE profile | PASSWORD EXPIRE | ACCOUNT { LOCK | UNLOCK } [ DEFAULT TABLESPACE tablespace | TEMPORARY TABLESPACE { tablespace | tablespace_group_name } | { QUOTA { size_clause | UNLIMITED } ON tablespace }... | PROFILE profile | PASSWORD EXPIRE | ACCOUNT { LOCK | UNLOCK } ]... ] ;
CREATE [OR REPLACE] [[NO] FORCE] VIEW [schema.] view [ ( { alias [ inline_constraint... ] | out_of_line_constraint } [, { alias [ inline_constraint...] | out_of_line_constraint } ] ) | object_view_clause | XMLType_view_clause ] AS subquery [ subquery_restriction_clause ] ;
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 [ schema. ] cluster [ INCLUDING TABLES [ CASCADE CONSTRAINTS ] ] ;
DROP CONTEXT namespace ;
DROP DATABASE ;
DROP [ PUBLIC ] DATABASE LINK dblink ;
DROP DIMENSION [ schema. ] dimension ;
DROP DIRECTORY directory_name ;
DROP DISKGROUP diskgroup_name [ FORCE INCLUDING CONTENTS | { INCLUDING | EXCLUDING } CONTENTS ]
DROP FLASHBACK ARCHIVE flashback_archive;
DROP FUNCTION [ schema. ] function_name ;
DROP INDEX [ schema. ] index [ FORCE ] ;
DROP INDEXTYPE [ schema. ] indextype [ FORCE ] ;
DROP JAVA { SOURCE | CLASS | RESOURCE } [ schema. ] object_name ;
DROP LIBRARY library_name ;
DROP MATERIALIZED VIEW [ schema. ] materialized_view [ PRESERVE TABLE ] ;
DROP MATERIALIZED VIEW LOG ON [ schema. ] table ;
DROP OPERATOR [ schema. ] operator [ FORCE ] ;
DROP OUTLINE outline ;
DROP PACKAGE [ BODY ] [ schema. ] package ;
DROP PROCEDURE [ schema. ] procedure ;
DROP PROFILE profile [ CASCADE ] ;
DROP RESTORE POINT restore_point ;
DROP ROLE role ;
DROP ROLLBACK SEGMENT rollback_segment ;
DROP SEQUENCE [ schema. ] sequence_name ;
DROP [PUBLIC] SYNONYM [ schema. ] synonym [FORCE] ;
DROP TABLE [ schema. ] table [ CASCADE CONSTRAINTS ] [ PURGE ] ;
DROP TABLESPACE tablespace [ INCLUDING CONTENTS [ {AND | KEEP} DATAFILES ] [ CASCADE CONSTRAINTS ] ] ;
DROP TRIGGER [ schema. ] trigger ;
DROP TYPE [ schema. ] type_name [ FORCE | VALIDATE ] ;
DROP TYPE BODY [ schema. ] type_name ;
DROP USER user [ CASCADE ] ;
DROP VIEW [ schema. ] view [ CASCADE CONSTRAINTS ] ;
EXPLAIN PLAN [ SET STATEMENT_ID = string ] [ INTO [ schema. ] table [ @ dblink ] ] FOR statement ;
FLASHBACK [ STANDBY ] DATABASE [ database ] { TO { { SCN | TIMESTAMP } expr | RESTORE POINT restore_point } | TO BEFORE { SCN | TIMESTAMP} expr | RESETLOGS } };
FLASHBACK TABLE [ schema. ] table [, [ schema. ] table ]... TO { { { SCN | TIMESTAMP } expr | RESTORE POINT restore_point } [ { ENABLE | DISABLE } TRIGGERS ] | BEFORE DROP [ RENAME TO table ] } ;
GRANT { grant_system_privileges | grant_object_privileges } ;
INSERT [ hint ] { single_table_insert | multi_table_insert } ;
LOCK TABLE [ schema. ] { table | view } [ partition_extension_clause | @ dblink ] [, [ schema. ] { table | view } [ partition_extension_clause | @ dblink ] ]... IN lockmode MODE [ NOWAIT | WAIT integer ] ;
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 { sql_statement_clause | schema_object_clause | NETWORK } [ WHENEVER [ NOT ] SUCCESSFUL ] ;
PURGE { { TABLE table | INDEX index } | { RECYCLEBIN | DBA_RECYCLEBIN } | TABLESPACE tablespace [ USER username ] } ;
RENAME old_name TO new_name ;
REVOKE { revoke_system_privileges | revoke_object_privileges } ;
ROLLBACK [ WORK ] [ TO [ SAVEPOINT ] savepoint | FORCE string ] ;
SAVEPOINT savepoint ;
[ subquery_factoring_clause ] subquery [ for_update_clause ] ;
SET { CONSTRAINT | CONSTRAINTS } { constraint [, constraint ]... | ALL } { IMMEDIATE | DEFERRED } ;
SET ROLE { role [ IDENTIFIED BY password ] [, role [ IDENTIFIED BY password ] ]... | ALL [ EXCEPT role [, role ]... ] | NONE } ;
SET TRANSACTION { { READ { ONLY | WRITE } | ISOLATION LEVEL { SERIALIZABLE | READ COMMITTED } | USE ROLLBACK SEGMENT rollback_segment } [ NAME string ] | NAME string } ;
TRUNCATE CLUSTER [schema.] cluster [ {DROP | REUSE} STORAGE ] ;
TRUNCATE TABLE [schema.] table [ {PRESERVE | PURGE} MATERIALIZED VIEW LOG ] [ {DROP | REUSE} STORAGE ] ;
UPDATE [ hint ] { dml_table_expression_clause | ONLY (dml_table_expression_clause) } [ t_alias ] update_set_clause [ where_clause ] [ returning_clause ] [error_logging_clause] ;