oracle 语法
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了oracle 语法，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含28597字，纯文字阅读大概需要41分钟。
内容图文
2015-11-10
目录
1.修改实例 ALTER SYSTEM
2.创建数据库 CREATE DATABASE
3.修改数据库 ALTER DATABASE
4.删除数据库 DROP DATABASE
5.修改会话 ALTER SESSION
6.创建用户 CREATE USER
7.修改用户 ALTER USER
8.删除用户 DROP USER
9.创建资源文件 CREATE PROFILE
10.修改资源文件 ALTER PROFILE
11.删除资源文件 DROP PROFILE
12.授予权限 GRANT
13.收回权限 REVOKE
14.创建角色 CREATE ROLE
15.修改角色 ALTER ROLE
16.禁止与激活角色 SET ROLE
17.删除角色 DROP ROLE
18.创建模式 CREATE SCHEMA
19.创建表空间 CREATE TABLESPACE
20.修改表空间 ALTER TABLESPACE
21.删除表空间 DROP TABLESPACE
22.创建表 CREATE TABLE
23.修改表 ALTER TABLE
24.删除表 DROP TABLE
25.截断表 TRUNCATE TABLE
26.查询记录 SELECT
27.插入记录 INSERT
28.更新记录 UPDATE
29.删除记录 DELETE
30.创建视图 CREATE VIEW
31.修改视图 ALTER VIEW
32.删除视图 DROP VIEW
33.创建物化视图 CREATE MATERIALIZED VIEW
34.修改物化视图 ALTER MATERIALIZED VIEW
35.删除物化视图 DROP MATERIALIZED VIEW
36.创建索引 CREATE INDEX
37.创建序列号 CREATE SEQUENCE
38.修改序列号 ALTER SEQUENCE
39.删除序列号 DROP SEQUENCE
40.创建同义词 CREATE SYNONYM
41.修改同义词 ALTER SYNONYM
42.删除同义词 DROP SYNONYM
43.创建数据库连接 CREATE DATABASE LINK
44.修改数据库连接 ALTER DATABASE LINK
45.删除数据库连接 DROP DATABASE LINK
1.修改实例
ALTER
SYSTEM
{ archive_log_clause
|
checkpoint_clause
|
check_datafiles_clause
|
distributed_recov_clauses
| FLUSH { SHARED_POOL | GLOBAL CONTEXT | BUFFER_CACHE
| REDO TO target_db_name [ [ NO ]CONFIRM APPLY ] }
| end_session_clauses
| SWITCH LOGFILE
| { SUSPEND | RESUME }
| quiesce_clauses
| rolling_migration_clauses
| rolling_patch_clauses
| security_clauses
| shutdown_dispatcher_clause
| REGISTER
|SET alter_system_set_clause
[ alter_system_set_clause ]...
| RESET alter_system_reset_clause
[ alter_system_reset_clause ]...
| RELOCATE CLIENT client_id
} ;
archive_log_clause ::=
ARCHIVE LOG[ INSTANCE ‘instance_name‘ ]
{ { SEQUENCE integer| CHANGE integer|CURRENT[ NOSWITCH ]|GROUPinteger| LOGFILE ‘filename‘[ USING BACKUP CONTROLFILE ]|NEXT|ALL
}
[ TO ‘location‘ ]
}
checkpoint_clause::=CHECKPOINT[ GLOBAL | LOCAL ]
check_datafiles_clause::=CHECK DATAFILES [ GLOBAL | LOCAL ]
alter_system_set_clause::=
{ set_parameter_clause
| USE_STORED_OUTLINES = (TRUE | FALSE | category_name)
| GLOBAL_TOPIC_ENABLED = (TRUE | FALSE)
}
set_parameter_clause::=
parameter_name =
parameter_value [, parameter_value ]...
[ COMMENT = string ][ DEFERRED ][ { SCOPE = { MEMORY | SPFILE | BOTH }
| SID = { ‘sid‘ | ‘*‘ }
}...
][ CONTAINER = { CURRENT | ALL } ]
alter_system_reset_clause::=
parameter_name
[ { SCOPE = SPFILE
| SID = { ‘sid‘ | ‘*‘ }
}...
]
2.创建数据库
CREATE
DATABASE
[
database
]
{
USER SYS IDENTIFIED BY password
|USER SYSTEM IDENTIFIED BY password
| CONTROLFILE REUSE
| MAXDATAFILES integer| MAXINSTANCES integer|CHARACTERSET charset
|NATIONALCHARACTERSET charset
|SETDEFAULT
{ BIGFILE | SMALLFILE } TABLESPACE
| database_logging_clauses
| tablespace_clauses
| set_time_zone_clause
|[ BIGFILE | SMALLFILE ] USER_DATA TABLESPACE tablespace_name
DATAFILE datafile_tempfile_spec [, datafile_tempfile_spec ]...
| enable_pluggable_database
}... ;
database_logging_clauses::=
{ LOGFILE
[ GROUP integer ] file_specification
[, [ GROUP integer ] file_specification ]...
| MAXLOGFILES integer| MAXLOGMEMBERS integer| MAXLOGHISTORY integer| { ARCHIVELOG | NOARCHIVELOG }
| FORCE LOGGING
}
tablespace_clauses::=
{ EXTENT MANAGEMENT LOCAL
| DATAFILE file_specification [, file_specification ]...
| SYSAUX DATAFILE file_specification [, file_specification ]...
| default_tablespace
| default_temp_tablespace
| undo_tablespace
}
3.修改数据库
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
[
{ STANDBY | CLONE } DATABASE
]
|
OPEN
{
[
READ WRITE
]
[
RESETLOGS | NORESETLOGS
]
[
UPGRADE | DOWNGRADE
]
|
READ
ONLY
}
}
恢复模式
recovery_clauses ::
=
{ general_recovery
|
managed_standby_recovery
|
BEGIN
BACKUP
|
END
BACKUP
}
general_recovery ::
=
RECOVER
[
AUTOMATIC
]
[
FROM ‘location‘
]
{ { full_database_recovery
|
partial_database_recovery
| LOGFILE ‘filename‘
}
[ { TEST
| ALLOW integer CORRUPTION
| parallel_clause
}...
]|CONTINUE[ DEFAULT ]| CANCEL
}
full_database_recovery ::=[ STANDBY ]DATABASE[ { UNTIL { CANCEL
| TIME date
| CHANGE integer
| CONSISTENT
}
| USING BACKUP CONTROLFILE
| SNAPSHOT TIME date
}...
]
partial_database_recovery::=
{ TABLESPACE tablespace [, tablespace ]...
| DATAFILE { ‘filename‘| filenumber }
[, ‘filename‘ | filenumber ]...
}
managed_standby_recovery::=
RECOVER
{ MANAGED STANDBY DATABASE[ { USING ARCHIVED LOGFILE
| DISCONNECT [FROM SESSION]| NODELAY
| UNTIL CHANGE integer| UNTIL CONSISTENT
| parallel_clause
}...
| FINISH
| CANCEL
]
|TO LOGICAL STANDBY { db_name| KEEP IDENTITY }
}
数据文件
database_file_clauses ::=
{ RENAME FILE‘filename‘[, ‘filename‘ ]...
TO‘filename‘| create_datafile_clause
| alter_datafile_clause
| alter_tempfile_clause
| move_datafile_clause
}
create_datafile_clause::=CREATE DATAFILE
{ ‘filename‘| filenumber }
[, ‘filename‘ | filenumber ]...
}
[ AS { file_specification
[, file_specification ]...
| NEW
}
]
alter_datafile_clause::=
DATAFILE
{ ‘filename‘| filenumber }
[, ‘filename‘ | filenumber ]...
}
{ ONLINE
| OFFLINE [ FOR DROP ]| RESIZE size_clause
| autoextend_clause
|ENDBACKUP
}
alter_tempfile_clause::=
TEMPFILE
{ ‘filename‘[, ‘filename‘ ]...
| filenumber [, filenumber ]...
}
{ RESIZE size_clause
| autoextend_clause
|DROP[ INCLUDING DATAFILES ]| ONLINE
| OFFLINE
}
move_datafile_clause::=
MOVE DATAFILE ( ‘filename‘|‘ASM_filename‘| file_number )
[ TO ( ‘filename‘ | ‘ASM_filename‘ ) ][ REUSE ][ KEEP ]
日志文件
logfile_clauses ::=
{ { ARCHIVELOG [ MANUAL ]| NOARCHIVELOG
}
|[ NO ] FORCE LOGGING
| RENAME FILE‘filename‘[, ‘filename‘ ]...
TO‘filename‘| CLEAR [ UNARCHIVED ]
LOGFILE logfile_descriptor [, logfile_descriptor ]...
[ UNRECOVERABLE DATAFILE ]| add_logfile_clauses
| drop_logfile_clauses
| switch_logfile_clause
| supplemental_db_logging
}
add_logfile_clauses::=ADD[ STANDBY ] LOGFILE
{
{ [ INSTANCE ‘instance_name‘ ]|[ THREAD ‘integer‘ ] }
[ GROUP integer ] redo_log_file_spec
[, [ GROUP integer ] redo_log_file_spec ]...
| MEMBER ‘filename‘[ REUSE ][, ‘filename‘ [ REUSE ] ]...
TO logfile_descriptor [, logfile_descriptor ]...
}
drop_logfile_clauses::=DROP[ STANDBY ] LOGFILE
{ logfile_descriptor
[, logfile_descriptor ]...
| MEMBER ‘filename‘[, ‘filename‘ ]...
}
switch_logfile_clause::=
SWITCH ALL LOGFILES TO BLOCKSIZE integer
supplemental_db_logging ::=
{ ADD|DROP } SUPPLEMENTAL LOG
{ DATA
| supplemental_id_key_clause
| supplemental_plsql_clause
}
控制文件
controlfile_clauses ::=CREATE { [ LOGICAL | PHYSICAL ] STANDBY | FAR SYNC INSTANCE }
CONTROLFILE AS‘filename‘[ REUSE ]|BACKUP CONTROLFILE TO
{ ‘filename‘[ REUSE ]| trace_file_clause
}
备份
standby_database_clauses ::=
{ { activate_standby_db_clause
| maximize_standby_db_clause
| register_logfile_clause
| commit_switchover_clause
| start_standby_clause
| stop_standby_clause
| convert_database_clause
} [ parallel_clause ] }
|
{ switchover_clause | failover_clause }
activate_standby_db_clause::=
ACTIVATE
[ PHYSICAL | LOGICAL ]
STANDBY DATABASE[ FINISH APPLY ]
maximize_standby_db_clause::=SET STANDBY DATABASETO MAXIMIZE
{ PROTECTION | AVAILABILITY | PERFORMANCE }
register_logfile_clause::=
REGISTER [ OR REPLACE ][ PHYSICAL | LOGICAL ]
LOGFILE [ file_specification [, file_specification ]...
[ FOR logminer_session_name ]
switchover_clause::=
SWITCHOVER TO target_db_name [ VERIFY | FORCE ]
failover_clause::=
FAILOVER TO target_db_name [ FORCE ]
commit_switchover_clause::=
{ PREPARE|COMMIT } TO SWITCHOVER
[ TO { { [ PHYSICAL | LOGICAL ]PRIMARY|[ PHYSICAL ] STANDBY
} [ { WITH | WITHOUT } SESSION SHUTDOWN
{ WAIT | NOWAIT }
]| LOGICAL STANDBY
}
| CANCEL
]
start_standby_clause::=
START LOGICAL STANDBY APPLY
[ IMMEDIATE ][ NODELAY ][ NEW PRIMARY dblink
| INITIAL [ scn_value ]| { SKIP FAILED TRANSACTION| FINISH }
]
stop_standby_clause::=
{ STOP | ABORT } LOGICAL STANDBY APPLY
convert_database_clause::=CONVERTTO ( PHYSICAL | SNAPSHOT ) STANDBY
默认设置
default_settings_clauses::=
{ DEFAULT EDITION = edition_name
|SETDEFAULT { BIGFILE | SMALLFILE } TABLESPACE
|DEFAULT TABLESPACE tablespace
|DEFAULTTEMPORARY TABLESPACE { tablespace | tablespace_group_name }
| RENAME GLOBAL_NAME TOdatabase.domain [.domain ]...
| ENABLE BLOCK CHANGE TRACKING [ USING FILE ‘filename‘ [ REUSE ] ]
| DISABLE BLOCK CHANGE TRACKING
|[NO] FORCE FULLDATABASE CACHING
| flashback_mode_clause
| set_time_zone_clause
}
set_time_zone_clause::=SET TIME_ZONE =‘{ { + | - } hh : mi | time_zone_region }‘
flashback_mode_clause ::=
FLASHBACK { ON|OFF }
实例
instance_clauses::=
{ ENABLE | DISABLE } INSTANCE ‘instance_name‘
安全
security_clause ::=
GUARD { ALL| STANDBY | NONE }
4.删除数据库
DROP
DATABASE ;
5.修改会话
ALTER
SESSION
{ ADVISE {
COMMIT
|
ROLLBACK
|
NOTHING }
|
CLOSE
DATABASE
LINK dblink
| { ENABLE | DISABLE } COMMITINPROCEDURE| { ENABLE | DISABLE } GUARD
| { ENABLE | DISABLE | FORCE } PARALLEL
{ DML | DDL | QUERY } [ PARALLEL integer ]| { ENABLE RESUMABLE [ TIMEOUT integer ][ NAME string ]| DISABLE RESUMABLE
}
| SYNC WITHPRIMARY| alter_session_set_clause
} ;
alter_session_set_clause ::=SET { { parameter_name = parameter_value }...
| EDITION = edition_name
| CONTAINER = container_name
| ROW ARCHIVAL VISIBILITY = { ACTIVE |ALL }
}
6.创建用户
CREATE
USER
user
IDENTIFIED {
BY
password
| EXTERNALLY [ AS ‘certificate_DN‘ | AS ‘kerberos_principal_name‘ ]| GLOBALLY [ AS ‘[ directory_DN ]‘ ]
}
[ DEFAULT TABLESPACE tablespace
| TEMPORARY TABLESPACE
{ tablespace | tablespace_group_name }
| { 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 }...
| PROFILE profile
| PASSWORD EXPIRE
| ACCOUNT { LOCK | UNLOCK }
| ENABLE EDITIONS
| CONTAINER = { CURRENT | ALL }
]...
] ;
7.修改用户
ALTER
USER
{
user
{ IDENTIFIED
{
BY password [ REPLACE old_password ]| EXTERNALLY [ AS ‘certificate_DN‘ | AS ‘kerberos_principal_name‘ ]| 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 }
| ENABLE EDITIONS [ FOR object_type [, object_type ]... ] [ FORCE ]
| CONTAINER = { CURRENT | ALL }
| container_data_clause
} ...
| user [, user ]... proxy_clause
} ;
8.删除用户
DROP
USER
user
[
CASCADE
] ;
9.创建资源文件
CREATE
PROFILE profile
LIMIT { resource_parameters
|
password_parameters
}...
[
CONTAINER = { CURRENT | ALL }
]
;
resource_parameters::
=
{ { SESSIONS_PER_USER
|
CPU_PER_SESSION
|
CPU_PER_CALL
|
CONNECT_TIME
|
IDLE_TIME
|
LOGICAL_READS_PER_SESSION
|
LOGICAL_READS_PER_CALL
|
COMPOSITE_LIMIT
}
{
integer
| UNLIMITED |DEFAULT }
| PRIVATE_SGA
{ size_clause | UNLIMITED |DEFAULT }
}
password_parameters ::=
{ { FAILED_LOGIN_ATTEMPTS
| PASSWORD_LIFE_TIME
| PASSWORD_REUSE_TIME
| PASSWORD_REUSE_MAX
| PASSWORD_LOCK_TIME
| PASSWORD_GRACE_TIME
}
{ expr | UNLIMITED |DEFAULT }
| PASSWORD_VERIFY_FUNCTION
{ function|NULL|DEFAULT }
}
10.修改资源文件
ALTER
PROFILE profile LIMIT
{ resource_parameters
|
password_parameters } ...
[
CONTAINER = { CURRENT | ALL }
]
;
resource_parameters::
=
{ { SESSIONS_PER_USER
|
CPU_PER_SESSION
|
CPU_PER_CALL
|
CONNECT_TIME
|
IDLE_TIME
|
LOGICAL_READS_PER_SESSION
|
LOGICAL_READS_PER_CALL
|
COMPOSITE_LIMIT
}
{
integer
| UNLIMITED |DEFAULT }
| PRIVATE_SGA
{ size_clause | UNLIMITED |DEFAULT }
}
password_parameters ::=
{ { FAILED_LOGIN_ATTEMPTS
| PASSWORD_LIFE_TIME
| PASSWORD_REUSE_TIME
| PASSWORD_REUSE_MAX
| PASSWORD_LOCK_TIME
| PASSWORD_GRACE_TIME
}
{ expr | UNLIMITED |DEFAULT }
| PASSWORD_VERIFY_FUNCTION
{ function|NULL|DEFAULT }
}
11.删除资源文件
DROP PROFILE profile [ CASCADE ] ;
12.授予权限
GRANT
{ { { grant_system_privileges
|
grant_object_privileges }
[
CONTAINER = { CURRENT | ALL }
]
}
|
grant_roles_to_programs
} ;
grant_system_privileges::
=
{ system_privilege
| role |ALLPRIVILEGES }
[, { system_privilege | role | ALL PRIVILEGES } ]...
TO { grantee_clause | grantee_identified_by } [ WITH { ADMIN | DELEGATE } OPTION ]
grant_object_privileges::=
{ object_privilege |ALL[ PRIVILEGES ] }
[ (column [, column ]...) ]
[, { object_privilege | ALL [ PRIVILEGES ] }
[ (column [, column ]...) ]
]...
on_object_clause
TO grantee_clause
[ WITH HIERARCHY OPTION ][ WITH GRANT OPTION ]
on_object_clause ::=ON { [ schema. ] object
|USERuser[, user]...
| DIRECTORY directory_name
| EDITION edition_name
| MINING MODEL [ schema. ] mining_model_name
| JAVA { SOURCE | RESOURCE } [ schema. ] object
| SQL TRANSLATION PROFILE [ schema. ] profile
}
grant_roles_to_programs::=
role [, role ]... TO program_unit [, program_unit ]...
13.回收权限
REVOKE
{ { revoke_system_privileges
|
revoke_object_privileges }
[
CONTAINER = { CURRENT | ALL }
]
}
|
revoke_roles_from_programs ;
revoke_system_privileges::
=
{ system_privilege
| role |ALLPRIVILEGES }
[, { system_privilege | role | ALL PRIVILEGES } ]...
FROM revokee_clause
revoke_object_privileges::=
{ object_privilege |ALL[ PRIVILEGES ] }
[, { object_privilege | ALL [ PRIVILEGES ] } ]...
on_object_clause
FROM revokee_clause
[ CASCADE CONSTRAINTS | FORCE ]
on_object_clause::=ON { [ schema. ] object
|USERuser[, user]...
| DIRECTORY directory_name
| EDITION edition_name
| MINING MODEL [ schema. ] mining_model_name
| JAVA { SOURCE | RESOURCE } [ schema. ] object
| SQL TRANSLATION PROFILE [ schema. ] profile
}
revoke_roles_from_programs::=
{ role [, role ]... |ALL } FROM program_unit [, program_unit ]...
14.创建角色
CREATE
ROLE role
[
NOT IDENTIFIED
| IDENTIFIED { BY password
| USING [ schema.
]
package
|
EXTERNALLY
|
GLOBALLY
}
]
[
CONTAINER = { CURRENT | ALL }
] ;
15.修改角色
ALTER
ROLE role
{
NOT
IDENTIFIED
|
IDENTIFIED
{
BY
password
| USING [ schema. ] package
| EXTERNALLY
| GLOBALLY
}
}
[ CONTAINER = { CURRENT | ALL } ] ;
16.禁止与激活角色
SET
ROLE
{ role
[
IDENTIFIED BY password
]
[
, role [ IDENTIFIED BY password
]
]...
|
ALL
[
EXCEPT role [, role
]
... ]
|
NONE
} ;
17.删除角色
DROP ROLE role ;
18.创建模式
CREATE
SCHEMA
AUTHORIZATION
schema
{ create_table_statement
|
create_view_statement
|
grant_statement
}...
;
19.创建表空间
CREATE
[
BIGFILE | SMALLFILE
]
{ permanent_tablespace_clause
|
temporary_tablespace_clause
|
undo_tablespace_clause
} ;
永久表空间
permanent_tablespace_clause::
=
TABLESPACE tablespace
[
DATAFILE file_specification [, file_specification
]
... ]
{ MINIMUM EXTENT size_clause
| BLOCKSIZE integer[ K ]| logging_clause
| FORCE LOGGING
| ENCRYPTION tablespace_encryption_spec
|DEFAULT[ table_compression ][ inmemory_clause ][ storage_clause ]| { ONLINE | OFFLINE }
| extent_management_clause
| segment_management_clause
| flashback_mode_clause
}...
临时表空间
temporary_tablespace_clause::=TEMPORARY TABLESPACE tablespace
[ TEMPFILE file_specification [, file_specification ]... ]
[ tablespace_group_clause ][ extent_management_clause ]
undo表空间
undo_tablespace_clause::=
UNDO TABLESPACE tablespace
[ DATAFILE file_specification [, file_specification ]... ]
[ extent_management_clause ][ tablespace_retention_clause ]
20.修改表空间
ALTER
TABLESPACE tablespace
{
DEFAULT
[
table_compression
]
[
inmemory_clause
]
[
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
} ;
datafile_tempfile_clauses ::=
{ ADD { DATAFILE | TEMPFILE }
[ file_specification [, file_specification ]... ]
|DROP {DATAFILE | TEMPFILE } { ‘filename‘| file_number }
| SHRINK TEMPFILE { ‘filename‘| file_number } [KEEP size_clause]| RENAME DATAFILE ‘filename‘[, ‘filename‘ ]...
TO‘filename‘[, ‘filename‘ ]...
| { DATAFILE | TEMPFILE } { ONLINE | OFFLINE }
}
21.删除表空间
DROP
TABLESPACE tablespace
[
INCLUDING CONTENTS [ {AND | KEEP} DATAFILES
]
[
CASCADE CONSTRAINTS
]
] ;
22.创建表
CREATE
[
GLOBAL TEMPORARY
]
TABLE
[
schema.
]
table
{ relational_table
| object_table | XMLType_table } ;
relational_table::=[ (relational_properties) ][ ON COMMIT { DELETE | PRESERVE } ROWS ][ physical_properties ][ table_properties ]
relational_properties::=
{ column_definition
| virtual_column_definition
| period_definition
| { out_of_line_constraint | out_of_line_ref_constraint }
| supplemental_logging_props
}
[, { column_definition
| virtual_column_definition
| period_definition
| { out_of_line_constraint | out_of_line_ref_constraint }
| supplemental_logging_props
}
]...
physical_properties::=
{ [ deferred_segment_creation ] segment_attributes_clause [ table_compression ][ inmemory_table_clause ][ ilm_clause ]|[ deferred_segment_creation ] ORGANIZATION
{ HEAP [ segment_attributes_clause ] heap_org_table_clause
|INDEX[ segment_attributes_clause ] index_org_table_clause
| EXTERNAL external_table_clause
}
| CLUSTER cluster (column[, column ]...)
}
table_properties::=[ column_properties ][ indexing_clause ][ table_partitioning_clauses ][ attribute_clustering_clause ][ CACHE | NOCACHE ][ RESULT_CACHE ( MODE {DEFAULT | FORCE } ) ][ parallel_clause ][ ROWDEPENDENCIES | NOROWDEPENDENCIES ][ enable_disable_clause ]...
[ row_movement_clause ][ flashback_archive_clause ][ ROW ARCHIVAL ][ AS subquery ]
object_table ::=OF[ schema. ] object_type
[ object_table_substitution ][ (object_properties) ][ ON COMMIT { DELETE | PRESERVE } ROWS ][ OID_clause ][ OID_index_clause ][ physical_properties ][ table_properties ]
XMLType_table ::=OF XMLTYPE
[ (oject_properties) ][ XMLTYPE XMLType_storage ][ XMLSchema_spec ][ XMLType_virtual_columns ][ ON COMMIT { DELETE | PRESERVE } ROWS ][ OID_clause ][ OID_index_clause ][ physical_properties ][ table_properties ]
23.修改表
ALTER
TABLE
[
schema.
]
table
[
alter_table_properties
| column_clauses
| constraint_clauses
| alter_table_partitioning
| alter_external_table
| move_table_clause
| modify_opaque_type
]
[
enable_disable_clause
| { ENABLE | DISABLE } { TABLE LOCK | ALL TRIGGERS }
]
...
;
修改表属性
alter_table_properties::
=
{ { { physical_attributes_clause
|
logging_clause
|
table_compression
|
inmemory_alter_table_clause
|
ilm_clause
|
supplemental_table_logging
|
allocate_extent_clause
|
deallocate_unused_clause
| { CACHE | NOCACHE }
| RESULT_CACHE ( MODE {DEFAULT| FORCE} )
| upgrade_table_clause
| records_per_block_clause
| parallel_clause
| row_movement_clause
| flashback_archive_clause
}...
| RENAME TO new_table_name
} [ alter_iot_clauses ][ alter_XMLSchema_clause ]| { shrink_clause
|READONLY|READ WRITE
| REKEY encryption_spec
|[NO] ROW ARCHIVAL
|ADD attribute_clustering_clause
| MODIFY CLUSTERING [ clustering_when ][ zonemap_clause ]|DROP CLUSTERING
}
}
修改表结构
column_clauses::=
{ { add_column_clause
| modify_column_clauses
| drop_column_clause
| add_period_clause
| drop_period_clause
}...
| rename_column_clause
| { modify_collection_retrieval }...
| { modify_LOB_storage_clause }...
| { alter_varray_col_properties }...
}
修改表约束
constraint_clauses::=
{ ADD { { out_of_line_constraint }...
| out_of_line_REF_constraint
}
| MODIFY { CONSTRAINT constraint_name
|PRIMARYKEY|UNIQUE (column[, column ]...)
} constraint_state [ CASCADE ]| RENAME CONSTRAINT old_name TO new_name
| { drop_constraint_clause }...
}
修改表分区
alter_table_partitioning ::=
{ modify_table_default_attrs
| alter_interval_partitioning
| set_subpartition_template
| modify_table_partition
| modify_table_subpartition
| move_table_partition
| move_table_subpartition
| add_table_partition
| coalesce_table_partition
| drop_table_partition
| drop_table_subpartition
| rename_partition_subpart
| truncate_partition_subpart
| split_table_partition
| split_table_subpartition
| merge_table_partitions
| merge_table_subpartitions
| exchange_partition_subpart
}
24.删除表
DROP
TABLE
[
schema.
]
table
[
CASCADE CONSTRAINTS
]
[
PURGE
] ;
25.截断表
TRUNCATE
TABLE
[
schema.
]
table
[
{PRESERVE | PURGE} MATERIALIZED VIEW LOG
]
[
{DROP [ ALL
]
| REUSE} STORAGE ] [ CASCADE ] ;
26.查询记录
subquery [ for_update_clause ] ; subquery::= { query_block | subquery { UNION[ALL]|INTERSECT| MINUS } subquery [ { UNION [ALL]|INTERSECT| MINUS } subquery ]... | ( subquery ) } [ order_by_clause ][ row_limiting_clause ] query_block::=[ with_clause ]SELECT[ hint ][ { { DISTINCT | UNIQUE } | ALL } ] select_list FROM { table_reference | join_clause | ( join_clause ) } [ , { table_reference | join_clause | (join_clause) } ] ... [ where_clause ][ hierarchical_query_clause ][ group_by_clause ][ model_clause ] join_clause ::= table_reference { inner_cross_join_clause | outer_join_clause | cross_outer_apply_clause }... where_clause::=WHERE condition group_by_clause ::=GROUPBY { expr | rollup_cube_clause | grouping_sets_clause } [, { expr | rollup_cube_clause | grouping_sets_clause } ]... [ HAVING condition ] 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 ] ]... row_limiting_clause::=[ OFFSET offset { ROW | ROWS } ][ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ] { ROW | ROWS } { ONLY|WITH TIES } ] for_update_clause ::=FORUPDATE[ OF [ [ schema. ] { table|view } . ] column[, [ [ schema. ] { table|view } . ] column ]... ] [ { NOWAIT | WAIT integer | SKIP LOCKED } ]
27.插入记录
INSERT
[
hint
]
{ single_table_insert
|
multi_table_insert } ;
single_table_insert ::
=
insert_into_clause
{ values_clause
[
returning_clause
]
|
subquery
}
[
error_logging_clause
]
insert_into_clause ::
=
INTO dml_table_expression_clause [ t_alias ][ (column [, column ]...) ]
values_clause ::=VALUES ({ expr |DEFAULT }
[, { expr | DEFAULT } ]...
)
multi_table_insert ::=
{ ALL
{ insert_into_clause [ values_clause ][error_logging_clause] }...
| conditional_insert_clause
} subquery
conditional_insert_clause ::=[ ALL | FIRST ]WHEN condition
THEN insert_into_clause
[ values_clause ][ error_logging_clause ][ insert_into_clause [ values_clause ][ error_logging_clause ] ]...
[ WHEN condition
THEN insert_into_clause
[ values_clause ][ error_logging_clause ][ insert_into_clause [ values_clause ][ error_logging_clause ] ]...
]...
[ ELSE insert_into_clause
[ values_clause ][ error_logging_clause ][ insert_into_clause [ values_clause ][ error_logging_clause ] ]...
]
28.更新记录
UPDATE
[
hint
]
{ dml_table_expression_clause
|
ONLY
(dml_table_expression_clause)
}
[
t_alias
]
update_set_clause
[
where_clause
]
[
returning_clause
]
[
error_logging_clause
]
;
dml_table_expression_clause::
=
{
[
schema.
]
{
table
[
partition_extension_clause
| @ dblink
]
| { view| materialized view } [ @ dblink ]
}
| ( subquery [ subquery_restriction_clause ] )
| table_collection_expression
}
update_set_clause ::=SET
{ { (column[, column ]...) = (subquery)
|column= { expr | (subquery) |DEFAULT }
}
[, { (column [, column]...) = (subquery)
|column= { expr | (subquery) |DEFAULT }
}
]...
| VALUE (t_alias) = { expr | (subquery) }
}
where_clause ::=WHERE condition
returning_clause::=
{ RETURN| RETURNING } expr [, expr ]...
INTO data_item [, data_item ]...
error_logging_clause::=LOG ERRORS
[ INTO [schema.]table ]
[ (simple_expression) ][ REJECT LIMIT { integer | UNLIMITED } ]
29.删除记录
DELETE
[
hint
]
[
FROM
]
{ dml_table_expression_clause
|
ONLY
(dml_table_expression_clause)
}
[
t_alias
]
[
where_clause
]
[
returning_clause
]
[
error_logging_clause
]
;
DML_table_expression_clause::
=
{
[
schema.
]
{
table
[
partition_extension_clause
| @ dblink
]
| { view| materialized view } [ @ dblink ]
}
| ( subquery [ subquery_restriction_clause ] )
| table_collection_expression
}
where_clause::=WHERE condition
returning_clause ::=
{ RETURN| RETURNING } expr [, expr ]...
INTO data_item [, data_item ]...
error_logging_clause ::=LOG ERRORS
[ INTO [schema.]table ]
[ (simple_expression) ][ REJECT LIMIT { integer | UNLIMITED } ]
30.创建视图
CREATE
[
OR REPLACE
]
[
[NO
]
FORCE]
[
EDITIONING | EDITIONABLE [ EDITIONING
]
|
NONEDITIONABLE ]
VIEW
[
schema.
]
view
[
( { alias [ VISIBLE | INVISIBLE
]
[
inline_constraint...
]
|
out_of_line_constraint
}
[
, { alias [ VISIBLE | INVISIBLE
]
[
inline_constraint...
]
|
out_of_line_constraint
}
]
)
|
object_view_clause
|
XMLType_view_clause
]
[
BEQUEATH { CURRENT_USER | DEFINER }
]
AS subquery [ subquery_restriction_clause ] ;
object_view_clause::=OF[ schema. ] type_name
{ WITH OBJECT { IDENTIFIER | ID }
{ DEFAULT| ( attribute [, attribute ]... ) }
| UNDER [ schema. ] superview
}
[ ( { out_of_line_constraint
| attribute { inline_constraint }...
} [, { out_of_line_constraint
| attribute { inline_constraint }...
}
]...
)
]
inline_constraint::=[ CONSTRAINT constraint_name ]
{ [ NOT ]NULL|UNIQUE|PRIMARYKEY| references_clause
|CHECK (condition)
}
[ constraint_state ]
out_of_line_constraint::=[ CONSTRAINT constraint_name ]
{ UNIQUE (column[, column ]...)
|PRIMARYKEY (column[, column ]...)
|FOREIGNKEY (column[, column ]...) references_clause
|CHECK (condition)
} [ constraint_state ]
31.修改视图
ALTER
VIEW
[
schema.
]
view
{
ADD
out_of_line_constraint
| MODIFY CONSTRAINTconstraint
{ RELY | NORELY }
|DROP { CONSTRAINTconstraint|PRIMARYKEY|UNIQUE (column[, column ]...)
}
| COMPILE
| { READONLY|READ WRITE }
| { EDITIONABLE | NONEDITIONABLE }
} ;
32.删除视图
DROP
VIEW
[
schema.
]
view
[
CASCADE CONSTRAINTS
] ;
33.创建物化视图
CREATE MATERIALIZED VIEW[ schema. ] materialized_view
[ OF [ schema. ] object_type ]
[ ( { scoped_table_ref_constraint
| column_alias [ENCRYPT [encryption_spec]]
}
[, { scoped_table_ref_constraint
| column_alias [ENCRYPT [encryption_spec]]
}
]...
)
]
{ 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 ][ evaluation_edition_clause ][ query_rewrite_clause ]AS subquery ;
34.修改物化视图
ALTER MATERIALIZED VIEW[ schema. ] materialized_view
[ physical_attributes_clause
| modify_mv_column_clause
| table_compression
| inmemory_alter_table_clause
| LOB_storage_clause [, LOB_storage_clause ]...
| modify_LOB_storage_clause [, modify_LOB_storage_clause ]...
| alter_table_partitioning
| parallel_clause
| logging_clause
| allocate_extent_clause
| deallocate_unused_clause
| shrink_clause
| { CACHE | NOCACHE }
]
[ alter_iot_clauses ][ USING INDEX physical_attributes_clause ][ MODIFY scoped_table_ref_constraint
| alter_mv_refresh
][ evaluation_edition_clause ][ alter_query_rewrite_clause
| COMPILE
| CONSIDER FRESH
] ;
35.删除物化视图
DROP MATERIALIZED VIEW[ schema. ] materialized_view
[ PRESERVE TABLE ] ;
36.创建索引
CREATE
[
UNIQUE | BITMAP
]
INDEX
[
schema.
]
index
ON
{ cluster_index_clause
|
table_index_clause
|
bitmap_join_index_clause
}
[
USABLE | UNUSABLE
]
;
cluster_index_clause ::
=
CLUSTER
[
schema.
]
cluster index_attributes
table_index_clause ::
=
[
schema.
]
table
[
t_alias
]
(index_expr
[
ASC | DESC
]
[
, index_expr [ ASC | DESC
]
]...)
[
index_properties
]
bitmap_join_index_clause ::
=
[
schema.
]
table
(
[
[ schema.
]
table. | t_alias. ]column[ ASC | DESC ][, [ [ schema. ]table. | t_alias. ]column[ ASC | DESC ]
]...
)
FROM[ schema. ]table[ t_alias ][, [ schema. ]table[ t_alias ]
]...
WHERE condition
[ local_partitioned_index ] index_attributes
37.创建序列号
CREATE SEQUENCE [ schema. ] sequence
[ { INCREMENT BY | START WITH } integer
| { MAXVALUE integer | NOMAXVALUE }
| { MINVALUE integer | NOMINVALUE }
| { CYCLE | NOCYCLE }
| { CACHE integer | NOCACHE }
| { ORDER | NOORDER }
| { KEEP | NOKEEP }
| { SESSION | GLOBAL }
]...
;
38.修改序列号
ALTER SEQUENCE [ schema. ] sequence
{ INCREMENT BYinteger| { MAXVALUE integer| NOMAXVALUE }
| { MINVALUE integer| NOMINVALUE }
| { CYCLE | NOCYCLE }
| { CACHE integer| NOCACHE }
| { ORDER| NOORDER }
| { KEEP | NOKEEP }
| { SESSION | GLOBAL }
} ...
;
39.删除序列号
DROP SEQUENCE [ schema. ] sequence_name ;
40.创建同义词
CREATE
[
OR REPLACE
]
[
EDITIONABLE | NONEDITIONABLE
]
[
PUBLIC
]
SYNONYM
[
schema.
]
synonym
FOR
[
schema.
] object [ @ dblink ] ;
41.修改同义词
ALTER
[
PUBLIC
] SYNONYM [ schema. ] synonym
{ EDITIONABLE | NONEDITIONABLE | COMPILE } ;
42.删除同义词
DROP
[
PUBLIC
] SYNONYM [ schema. ] synonym [FORCE] ;
43.创建数据库连接
CREATE
[
SHARED
]
[
PUBLIC
]
DATABASE
LINK dblink
[
CONNECT TO
{ CURRENT_USER
| user IDENTIFIED BY password [ dblink_authentication
]
}
|
dblink_authentication
]...
[
USING connect_string
]
;
dblink_authentication ::
=
AUTHENTICATED
BY
user IDENTIFIED BY password
44.修改数据库连接
ALTER
DATABASE
LINK dblink
{ CONNECT
TO
user IDENTIFIED BY password [ dblink_authentication ]| dblink_authentication
};
dblink_authentication::=
AUTHENTICATED BYuser IDENTIFIED BY password
45.删除数据库连接
DROP
[
PUBLIC
]
DATABASE LINK dblink ;
原文：http://www.cnblogs.com/cenliang/p/4952578.html
内容总结
以上是互联网集市为您收集整理的oracle 语法全部内容，希望文章能够帮你解决oracle 语法所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】