这是用户在 2024-5-29 17:35 为 file:///D:/software/instantclient_21_7/oracle-database_19_20230703/content/sqlrf/SELECT.html#GUID-CF... 保存的双语快照页面,由 沉浸式翻译 提供双语支持。了解如何保存?

SQL Language Reference

SELECT  选择

Purpose  目的

Use a SELECT statement or subquery to retrieve data from one or more tables, object tables, views, object views, materialized views, analytic views, or hierarchies.
使用 SELECT 语句或子查询从一个或多个表、对象表、视图、对象视图、具体化视图、分析视图或层次结构中检索数据。

If part or all of the result of a SELECT statement is equivalent to an existing materialized view, then Oracle Database may use the materialized view in place of one or more tables specified in the SELECT statement. This substitution is called query rewrite. It takes place only if cost optimization is enabled and the QUERY_REWRITE_ENABLED parameter is set to TRUE. To determine whether query rewrite has occurred, use the EXPLAIN PLAN statement.
如果 SELECT 语句的部分或全部结果相当于现有的物化视图,则 Oracle 数据库可以使用该物化视图来代替 SELECT 中指定的一个或多个表陈述。这种替换称为查询重写。仅当启用成本优化且 QUERY_REWRITE_ENABLED 参数设置为 TRUE 时才会发生。要确定是否发生查询重写,请使用 EXPLAIN PLAN 语句。

See Also: 也可以看看:

Prerequisites 先决条件

For you to select data from a table, materialized view, analytic view, or hierarchy, the object must be in your own schema or you must have the READ or SELECT privilege on the table, materialized view, analytic view, or hierarchy.
要从表、物化视图、分析视图或层次结构中选择数据,对象必须位于您自己的架构中,或者您必须拥有表的 READSELECT 权限、物化视图、分析视图或层次结构。

For you to select rows from the base tables of a view:
供您从视图的基表中选择行:

  • The object must be in your own schema or you must have the READ or SELECT privilege on it, and
    该对象必须位于您自己的架构中,或者您必须对其具有 READSELECT 权限,并且

  • Whoever owns the schema containing the object must have the READ or SELECT privilege on the base tables.
    拥有包含该对象的架构的人必须拥有基表的 READSELECT 权限。

The READ ANY TABLE or SELECT ANY TABLE system privilege also allows you to select data from any table, materialized view, analytic view, or hierarchy, or the base table of any materialized view, analytic view, or hierarchy.
READ ANY TABLESELECT ANY TABLE 系统权限还允许您从任何表、物化视图、分析视图或层次结构,或者任何物化视图、分析视图或层次结构的基表中选择数据。

To specify the FOR UPDATE clause, the preceding prerequisites apply with the following exception: The READ and READ ANY TABLE privileges, where mentioned, do not allow you to specify the FOR UPDATE clause.
要指定 FOR UPDATE 子句,上述先决条件适用,但以下例外: READREAD ANY TABLE 权限(如提及)不允许指定 FOR UPDATE 子句。

To issue an Oracle Flashback Query using the flashback_query_clause, you must have the READ or SELECT privilege on the objects in the select list. In addition, either you must have FLASHBACK object privilege on the objects in the select list, or you must have FLASHBACK ANY TABLE system privilege.
要使用 flashback_query_clause 发出 Oracle 闪回查询,您必须对选择列表中的对象具有 READSELECT 权限。此外,您必须对选择列表中的对象具有 FLASHBACK 对象权限,或者您必须具有 FLASHBACK ANY TABLE 系统特权。

Syntax 句法

select::=  选择::=

(subquery::=, for_update_clause::=)
(子查询::=,for_update_clause::=)

subquery::=  子查询::=

(query_block::=, order_by_clause::=, row_limiting_clause::=)
(query_block::=、order_by_clause::=、row_limiting_clause::=)

query_block::=  查询块::=

(with_clause::=, select_list::=, table_reference::=, join_clause::=, inline_analytic_view, where_clause::=, hierarchical_query_clause::=, group_by_clause::=, model_clause::=)
(with_clause::=、select_list::=、table_reference::=、join_clause::=、inline_analytic_view、where_clause::=、hierarchical_query_clause::=、group_by_clause::=、model_clause::=)

with_clause::=

Note:

You cannot specify only the WITH keyword. You must specify at least one of the clauses plsql_declarations, subquery_factoring_clause, or subav_factoring_clause.


注意:您不能仅指定 WITH 关键字。您必须至少指定 plsql_declarationssubquery_factoring_clausesubav_factoring_clause 子句之一。

query_table_expression::=

(analytic_view, hierarchy, subquery_restriction_clause::=, table_collection_expression::=)
(分析视图、层次结构、子查询限制子句::=、表集合表达式::=)

join_clause::=

(inner_cross_join_clause::=, outer_join_clause::=, cross_outer_apply_clause::=)
(inner_cross_join_clause::=、outer_join_clause::=、cross_outer_apply_clause::=)

inner_cross_join_clause::=
内部交叉连接子句::=

(table_reference::=)  (表参考::=)

outer_join_clause::=  外部连接子句::=

(query_partition_clause::=, outer_join_type::=, table_reference::=)
(query_partition_clause::=、outer_join_type::=、table_reference::=)

hierarchical_query_clause::=
分层查询子句::=

(condition can be any condition as described in Conditions)
condition 可以是条件中描述的任何条件)

grouping_sets_clause::=  分组集子句::=

(rollup_cube_clause::=, grouping_expression_list::=)
(rollup_cube_clause::=,grouping_expression_list::=)

model_clause::=  模型子句::=

(cell_reference_options::=, return_rows_clause::=, reference_model::=, main_model::=)
(cell_reference_options::=、return_rows_clause::=、reference_model::=、main_model::=)

main_model::=  主模型::=

(model_column_clauses::=, cell_reference_options::=, model_rules_clause::=)
( model_column_clauses::=、cell_reference_options::=、model_rules_clause::=)

model_rules_clause::=

(model_iterate_clause::=, cell_assignment::=, order_by_clause::=)
( model_iterate_clause::=、cell_assignment::=、order_by_clause::=)

row_pattern_clause::=  行模式子句::=

(row_pattern_partition_by::=, row_pattern_order_by::=, row_pattern_measures::=, row_pattern_rows_per_match::=, row_pattern_skip_to::=, row_pattern::=, row_pattern_subset_clause::=, row_pattern_definition_list::=)
(row_pattern_partition_by::=、row_pattern_order_by::=、row_pattern_measures::=、row_pattern_rows_per_match::=、row_pattern_skip_to::=、row_pattern::=、row_pattern_subset_clause::=、row_pattern_definition_list::=)

row_pattern_rec_func::=

(row_pattern_classifier_func::=, row_pattern_match_num_func::=, row_pattern_navigation_func::=, row_pattern_aggregate_func::=)
(row_pattern_classifier_func::=、row_pattern_match_num_func::=、row_pattern_navigation_func::=、row_pattern_aggregate_func::=)

row_pattern_navigation_func::=

(row_pattern_nav_logical::=, row_pattern_nav_physical::=, row_pattern_nav_compound::=)
(row_pattern_nav_logic::=、row_pattern_nav_physical::=、row_pattern_nav_compound::=)

Semantics  语义学

with_clause with_子句

Use the with_clause to define the following:
使用 with_clause 定义以下内容:

  • PL/SQL procedures and functions (using the plsql_declarations clause)
    PL/SQL 过程和函数(使用 plsql_declarations 子句)

  • Subquery blocks (using subquery_factoring_clause or subav_factoring_clause, or both)
    子查询块(使用 subquery_factoring_clausesubav_factoring_clause 或两者)

plsql_declarations plsql_声明

The plsql_declarations clause lets you declare and define PL/SQL functions and procedures. You can then reference the PL/SQL functions in the query in which you specify this clause, as well as its subqueries, if any. For the purposes of name resolution, these function names have precedence over schema-level stored functions.
plsql_declarations 子句允许您声明和定义 PL/SQL 函数和过程。然后,您可以在指定此子句的查询中引用 PL/SQL 函数及其子查询(如果有)。出于名称解析的目的,这些函数名称优先于架构级存储函数。

If the query in which you specify this clause is not a top-level SELECT statement, then the following rules apply to the top-level SQL statement that contains the query:
如果指定此子句的查询不是顶级 SELECT 语句,则以下规则适用于包含该查询的顶级 SQL 语句:

  • If the top-level statement is a SELECT statement, then it must have either a WITH plsql_declarations clause or the WITH_PLSQL hint.
    如果顶级语句是 SELECT 语句,则它必须具有 WITH plsql_declarations 子句或 WITH_PLSQL 提示。

  • If the top-level statement is a DELETE, MERGE, INSERT, or UPDATE statement, then it must have the WITH_PLSQL hint.
    如果顶级语句是 DELETEMERGEINSERTUPDATE 语句,则它必须具有 WITH_PLSQL 提示。

The WITH_PLSQL hint only enables you to specify the WITH plsql_declarations clause within the statement. It is not an optimizer hint.
WITH_PLSQL 提示仅允许您在语句中指定 WITH plsql_declarations 子句。它不是优化器提示。

See Also: 也可以看看:

subquery_factoring_clause
子查询分解子句

The subquery_factoring_clause lets you assign a name (query_name) to a subquery block. You can then reference the subquery block multiple places in the query by specifying query_name. Oracle Database optimizes the query by treating the query_name as either an inline view or as a temporary table. The query_name is subject to the same naming conventions and restrictions as database schema objects. Refer to "Database Object Naming Rules" for information on database object names.
subquery_factoring_clause 允许您为子查询块分配名称 ( query_name )。然后,您可以通过指定 query_name 在查询中的多个位置引用子查询块。 Oracle 数据库通过将 query_name 视为内联视图或临时表来优化查询。 query_name 遵循与数据库模式对象相同的命名约定和限制。有关数据库对象名称的信息,请参阅“数据库对象命名规则”。

The column aliases following the query_name and the set operators separating multiple subqueries in the AS clause are valid and required for recursive subquery factoring. The search_clause and cycle_clause are valid only for recursive subquery factoring but are not required. See "Recursive Subquery Factoring".
query_name 后面的列别名和 AS 子句中分隔多个子查询的集合运算符是有效的,并且是递归子查询分解所必需的。 search_clausecycle_clause 仅对递归子查询分解有效,但不是必需的。请参阅“递归子查询因式分解”。

You can specify this clause in any top-level SELECT statement and in most types of subqueries. The query name is visible to the main query and to all subsequent subqueries. For recursive subquery factoring, the query name is even visible to the subquery that defines the query name itself.
您可以在任何顶级 SELECT 语句和大多数类型的子查询中指定此子句。查询名称对于主查询和所有后续子查询都是可见的。对于递归子查询分解,查询名称甚至对于定义查询名称本身的子查询也是可见的。

Recursive Subquery Factoring
递归子查询因式分解

If a subquery_factoring_clause refers to its own query_name in the subquery that defines it, then the subquery_factoring_clause is said to be recursive. A recursive subquery_factoring_clause must contain two query blocks: the first is the anchor member and the second is the recursive member. The anchor member must appear before the recursive member, and it cannot reference query_name. The anchor member can be composed of one or more query blocks combined by the set operators: UNION ALL, UNION, INTERSECT or MINUS. The recursive member must follow the anchor member and must reference query_name exactly once. You must combine the recursive member with the anchor member using the UNION ALL set operator.
如果 subquery_factoring_clause 在定义它的子查询中引用它自己的 query_name ,则 subquery_factoring_clause 被认为是递归的。递归 subquery_factoring_clause 必须包含两个查询块:第一个是锚成员,第二个是递归成员。锚成员必须出现在递归成员之前,并且不能引用 query_name 。锚成员可以由一个或多个由集合运算符组合的查询块组成: UNION ALLUNIONINTERSECT 或 < b9> 。递归成员必须跟随锚成员,并且必须恰好引用 query_name 一次。您必须使用 UNION ALL 集合运算符将递归成员与锚定成员组合起来。

The number of column aliases following WITH query_name and the number of columns in the SELECT lists of the anchor and recursive query blocks must be the same.
WITH query_name 后面的列别名数量与锚点和递归查询块的 SELECT 列表中的列数量必须相同。

The recursive member cannot contain any of the following elements:
递归成员不能包含以下任何元素:

  • The DISTINCT keyword or a GROUP BY clause
    DISTINCT 关键字或 GROUP BY 子句

  • The model_clause  model_clause

  • An aggregate function. However, analytic functions are permitted in the select list.
    聚合函数。但是,选择列表中允许使用分析函数。

  • Subqueries that refer to query_name.
    引用 query_name 的子查询。

  • Outer joins that refer to query_name as the right table.
    query_name 引用为右表的外连接。

In previous releases of Oracle Database, the recursive member of a recursive WITH clause ran serially regardless of the parallelism of the entire query (also known as the top-level SELECT statement). Beginning with Oracle Database 12c Release 2 (12.2), the recursive member runs in parallel if the optimizer determines that the top-level SELECT statement can be executed in parallel.
在 Oracle 数据库的早期版本中,无论整个查询(也称为顶级 SELECT 语句)的并行性如何,递归 WITH 子句的递归成员都会串行运行。从 Oracle Database 12c 第 2 版 (12.2) 开始,如果优化器确定顶级 SELECT 语句可以并行执行,则递归成员将并行运行。

search_clause 搜索子句

Use the SEARCH clause to specify an ordering for the rows.
使用 SEARCH 子句指定行的顺序。

  • Specify BREADTH FIRST BY if you want sibling rows returned before any child rows are returned.
    如果您希望在返回任何子行之前返回同级行,请指定 BREADTH FIRST BY

  • Specify DEPTH FIRST BY if you want child rows returned before any siblings rows are returned.
    如果您希望在返回任何同级行之前返回子行,请指定 DEPTH FIRST BY

  • Sibling rows are ordered by the columns listed after the BY keyword.
    同级行按 BY 关键字后列出的列排序。

  • The c_alias list following the SEARCH keyword must contain column names from the column alias list for query_name.
    SEARCH 关键字后面的 c_alias 列表必须包含 query_name 的列别名列表中的列名称。

  • The ordering_column is automatically added to the column list for the query name. The query that selects from query_name can include an ORDER BY on ordering_column to return the rows in the order that was specified by the SEARCH clause.
    ordering_column 会自动添加到查询名称的列列表中。从 query_name 中选择的查询可以在 ordering_column 上包含 ORDER BY ,以按 < 指定的顺序返回行。 b5> 子句。

cycle_clause 循环子句

Use the CYCLE clause to mark cycles in the recursion.
使用 CYCLE 子句标记递归中的循环。

  • The c_alias list following the CYCLE keyword must contain column names from the column alias list for query_name. Oracle Database uses these columns to detect a cycle.
    CYCLE 关键字后面的 c_alias 列表必须包含 query_name 的列别名列表中的列名称。 Oracle 数据库使用这些列来检测循环。

  • cycle_value and no_cycle_value should be character strings of length 1.
    cycle_valueno_cycle_value 应该是长度为1的字符串。

  • If a cycle is detected, then the cycle mark column specified by cycle_mark_c_alias for the row causing the cycle is set to the value specified for cycle_value. The recursion will then stop for this row. That is, it will not look for child rows for the offending row, but it will continue for other noncyclic rows.
    如果检测到循环,则由 cycle_mark_c_alias 为导致循环的行指定的循环标记列将设置为为 cycle_value 指定的值。然后递归将针对该行停止。也就是说,它不会查找违规行的子行,但会继续查找其他非循环行。

  • If no cycles are found, then the cycle mark column is set to the default value specified for no_cycle_value.
    如果未找到循环,则循环标记列将设置为为 no_cycle_value 指定的默认值。

  • The cycle mark column is automatically added to the column list for the query_name.
    循环标记列会自动添加到 query_name 的列列表中。

  • A row is considered to form a cycle if one of its ancestor rows has the same values for the cycle columns.
    如果某行的祖先行具有相同的循环列值,则该行被视为形成循环。

If you omit the CYCLE clause, then the recursive WITH clause returns an error if cycles are discovered. In this case, a row forms a cycle if one of its ancestor rows has the same values for all the columns in the column alias list for query_name that are referenced in the WHERE clause of the recursive member.
如果省略 CYCLE 子句,则在发现循环时递归 WITH 子句将返回错误。在这种情况下,如果一行的祖先行之一对于 query_name 的列别名列表中的所有列具有相同的值(在 WHERE 子句中引用),则该行形成一个循环。递归成员。

Restrictions on Subquery Factoring
子查询分解的限制

This clause is subject to the following restrictions:
本条款受以下限制:

  • You can specify only one subquery_factoring_clause in a single SQL statement. Any query_name defined in the subquery_factoring_clause can be used in any subsequent named query block in the subquery_factoring_clause.
    在一条 SQL 语句中只能指定一个 subquery_factoring_clausesubquery_factoring_clause 中定义的任何 query_name 都可以在 subquery_factoring_clause 中的任何后续命名查询块中使用。

  • In a compound query with set operators, you cannot use the query_name for any of the component queries, but you can use the query_name in the FROM clause of any of the component queries.
    在具有集合运算符的复合查询中,您不能对任何组件查询使用 query_name ,但可以在任何组件查询的 FROM 子句中使用 query_name 。的组件查询。

  • You cannot specify duplicate names in the column alias list for query_name.
    您不能在 query_name 的列别名列表中指定重复的名称。

  • The name used for the ordering_column has to be different from the name used for cycle_mark_c_alias.
    ordering_column 使用的名称必须与 cycle_mark_c_alias 使用的名称不同。

  • The ordering_column and cycle mark column names cannot already be in the column alias list for query_name.
    ordering_column 和循环标记列名称不能已位于 query_name 的列别名列表中。

See Also: 也可以看看:

subav_factoring_clause

With the subav_factoring_clause, you can define a transitory analytic view that filters fact data prior to aggregation or adds calculated measures to a query of an analytic view. The subav_name argument assigns a name to the transitory analytic view. You can then reference the transitory analytic view multiple places in the query by specifying subav_name. The subav_name is subject to the same naming conventions and restrictions as database schema objects. Refer to "Database Object Naming Rules" for information on database object names.
使用 subav_factoring_clause ,您可以定义一个临时分析视图,该视图在聚合之前过滤事实数据或将计算的度量添加到分析视图的查询中。 subav_name 参数为瞬态分析视图指定一个名称。然后,您可以通过指定 subav_name 在查询中的多个位置引用临时分析视图。 subav_name 遵循与数据库模式对象相同的命名约定和限制。有关数据库对象名称的信息,请参阅“数据库对象命名规则”。

You can specify this clause in any top-level SELECT statement and in most types of subqueries. The query name is visible to the main query and to all subsequent subqueries.
您可以在任何顶级 SELECT 语句和大多数类型的子查询中指定此子句。查询名称对于主查询和所有后续子查询都是可见的。

The subav_clause argument defines a transitory analytic view.
subav_clause 参数定义瞬态分析视图。

subav_clause 子av_子句

With the USING keyword, specify the name of an analytic view, which may be a transitory analytic view previously defined in the WITH clause or it may be a persistent analytic view. A persistent analytic view is defined in a CREATE ANALYTIC VIEW statement. If the analytic view is a persistent one, then the current user must have select access on it.
使用 USING 关键字指定分析视图的名称,该名称可以是先前在 WITH 子句中定义的瞬态分析视图,也可以是持久分析视图。持久分析视图在 CREATE ANALYTIC VIEW 语句中定义。如果分析视图是持久视图,则当前用户必须对其具有选择访问权限。

hierarchies_clause 层次结构子句

The hierarchies_clause specifies the hierarchies of the base analytic view that the results of the transitory analytic view are dimensioned by. With the HIERARCHIES keyword, specify the alias of one or more hierarchies of the base analytic view.
hierarchies_clause 指定基本分析视图的层次结构,瞬态分析视图的结果是根据该层次结构来确定尺寸的。使用 HIERARCHIES 关键字指定基本分析视图的一个或多个层次结构的别名。

If you do not specify a HIERARCHIES clause, then the default hierarchies of the base analytic view are used.
如果不指定 HIERARCHIES 子句,则使用基本分析视图的默认层次结构。

filter_clauses 过滤子句

You may specify a given hier_alias in at most one filter_clause.
您最多可以在一个 filter_clause 中指定给定的 hier_alias

filter_clause 过滤子句

The filter clause applies the specified predicate condition to the fact table, which reduces the number of rows returned from the table before aggregation of the measure values. The predicate may contain any SQL row function or operation. The predicate may refer to any attribute of the specified hierarchy or it may refer to a measure of the analytic view if you specify the MEASURES keyword.
过滤子句将指定的谓词条件应用于事实表,这会减少在聚合度量值之前从表返回的行数。谓词可以包含任何 SQL 行函数或操作。谓词可以引用指定层次结构的任何属性,或者如果您指定 MEASURES 关键字,则它可以引用分析视图的度量。

For example, the following clause restricts the aggregation of measure values to those for the first and second quarters of every year of a time hierarchy.
例如,以下子句将度量值的聚合限制为时间层次结构每年第一季度和第二季度的度量值。

FILTER FACT (time_hier TO quarter_of_year IN (1,2))

If you then select from the transitory analytic view the sales for the years 2000 and 2001, the values returned are the aggregated values of the first and second quarters only.
如果您随后从临时分析视图中选择 2000 年和 2001 年的销售额,则返回的值仅为第一季度和第二季度的合计值。

An example of specifying a predicate for a measure in the filter clause is the following.
下面是在过滤子句中为度量指定谓词的示例。

FILTER FACT (MEASURES TO sales BETWEEN 100 AND 200)

attr_dim_alias

The alias of an attribute dimension in the base analytic view. The USER_ANALYTIC_VIEW_DIMENSIONS view contains the aliases of the attribute dimensions in an analytic view.
基础分析视图中属性维度的别名。 USER_ANALYTIC_VIEW_DIMENSIONS 视图包含分析视图中属性维度的别名。

hier_alias 层次别名

The alias of a hierarchy in the base analytic view. The USER_ANALYTIC_VIEW_HIERS view contains the aliases of the hierarchies in an analytic view.
基础分析视图中层次结构的别名。 USER_ANALYTIC_VIEW_HIERS 视图包含分析视图中层次结构的别名。

add_calcs_clause 添加计算子句

With the ADD MEASURES keywords, you may add calculated measures to the transitory analytic view.
使用 ADD MEASURES 关键字,您可以将计算的度量添加到瞬态分析视图中。

calc_meas_clause 计算测量子句

Specify a name for the calculated measure and an analytic view expression that specifies values for the calculated measure. The analytic view expression can be any valid calc_meas_expression as described in Analytic View Expressions. For example, the following adds a calculated measure named “share_sales.”
指定计算度量的名称以及指定计算度量值的分析视图表达式。分析视图表达式可以是任何有效的 calc_meas_expression ,如分析视图表达式中所述。例如,以下添加了一个名为“share_sales”的计算度量。

ADD MEASURES (share_sales AS (SHARE_OF(sales HIERARCHY time_hier PARENT)))

hint 暗示

Specify a comment that passes instructions to the optimizer on choosing an execution plan for the statement.
指定一条注释,将指令传递给优化器以选择语句的执行计划。

See Also: 也可以看看:

"Hints" for the syntax and description of hints
“Hints”用于提示的语法和描述

DISTINCT | UNIQUE 独特|独特的

Specify DISTINCT or UNIQUE if you want the database to return only one copy of each set of duplicate rows selected. These two keywords are synonymous. Duplicate rows are those with matching values for each expression in the select list.
如果您希望数据库仅返回所选的每组重复行的一个副本,请指定 DISTINCTUNIQUE 。这两个关键字是同义词。重复行是指选择列表中每个表达式的值都匹配的行。

Restrictions on DISTINCT and UNIQUE Queries
对 DISTINCT 和 UNIQUE 查询的限制

These types of queries are subject to the following restrictions:
这些类型的查询受到以下限制:

  • When you specify DISTINCT or UNIQUE, the total number of bytes in all select list expressions is limited to the size of a data block minus some overhead. This size is specified by the initialization parameter DB_BLOCK_SIZE.
    当您指定 DISTINCTUNIQUE 时,所有选择列表表达式中的字节总数限制为数据块的大小减去一些开销。该大小由初始化参数 DB_BLOCK_SIZE 指定。

  • You cannot specify DISTINCT if the select_list contains LOB columns.
    如果 select_list 包含 LOB 列,则无法指定 DISTINCT

ALL

Specify ALL if you want the database to return all rows selected, including all copies of duplicates. The default is ALL.
如果您希望数据库返回所有选定的行(包括所有重复项的副本),请指定 ALL 。默认为 ALL

select_list 选择列表

The select_list lets you specify the columns you want to retrieve from the database.
select_list 允许您指定要从数据库检索的列。

* (all-column wildcard) *(全列通配符)

Specify the all-column wildcard (asterisk) to select all columns, excluding pseudocolumns and INVISIBLE columns, from all tables, views, or materialized views listed in the FROM clause. The columns are returned in the order indicated by the COLUMN_ID column of the *_TAB_COLUMNS data dictionary view for the table, view, or materialized view.
指定全列通配符(星号)以从 FROM 子句中列出的所有表、视图或具体化视图中选择所有列(不包括伪列和 INVISIBLE 列)。这些列按照表、视图或物化视图的 *_TAB_COLUMNS 数据字典视图的 COLUMN_ID 列指示的顺序返回。

If you are selecting from a table rather than from a view or a materialized view, then columns that have been marked as UNUSED by the ALTER TABLE SET UNUSED statement are not selected.
如果您从表中而不是从视图或物化视图中进行选择,则由 ALTER TABLE SET 的列 UNUSED 语句没有被选中。

See Also: 也可以看看:

ALTER TABLE, "Simple Query Examples", and "Selecting from the DUAL Table: Example"
ALTER TABLE、“简单查询示例”和“从 DUAL 表中选择:示例”

query_name.* 查询名称.*

Specify query_name followed by a period and the asterisk to select all columns from the specified subquery block. For query_name, specify a subquery block name already specified in the subquery_factoring_clause. You must have specified the subquery_factoring_clause in order to specify query_name in the select_list. If you specify query_name in the select_list, then you also must specify query_name in the query_table_expression (FROM clause).
指定 query_name 后跟句点和星号以选择指定子查询块中的所有列。对于 query_name ,指定已在 subquery_factoring_clause 中指定的子查询块名称。您必须指定 subquery_factoring_clause 才能在 select_list 中指定 query_name 。如果您在 select_list 中指定 query_name ,那么您还必须在 query_table_expressionFROM 子句中指定 query_name )。

table.* | view.* | materialized view.*
表。*|查看。*|物化视图。*

Specify the object name followed by a period and the asterisk to select all columns from the specified table, view, or materialized view. Oracle Database returns a set of columns in the order in which the columns were specified when the object was created. A query that selects rows from two or more tables, views, or materialized views is a join.
指定对象名称,后跟句点和星号,以从指定的表、视图或物化视图中选择所有列。 Oracle 数据库按照创建对象时指定列的顺序返回一组列。从两个或多个表、视图或具体化视图中选择行的查询是联接。

You can use the schema qualifier to select from a table, view, or materialized view in a schema other than your own. If you omit schema, then the database assumes the table, view, or materialized view is in your own schema.
您可以使用模式限定符从您自己的模式以外的模式中的表、视图或物化视图中进行选择。如果省略 schema ,则数据库假定表、视图或物化视图位于您自己的架构中。

See Also: 也可以看看:

"Joins" “加入”

t_alias .* t_别名.*

Specify a correlation name (alias) followed by a period and the asterisk to select all columns from the object with that correlation name specified in the FROM clause of the same subquery. The object can be a table, view, materialized view, or subquery. Oracle Database returns a set of columns in the order in which the columns were specified when the object was created. A query that selects rows from two or more objects is a join.
指定相关名称(别名),后跟句点和星号,以从具有在同一子查询的 FROM 子句中指定的相关名称的对象中选择所有列。该对象可以是表、视图、物化视图或子查询。 Oracle 数据库按照创建对象时指定列的顺序返回一组列。从两个或多个对象中选择行的查询是联接。

expr 表达式

Specify an expression representing the information you want to select. A column name in this list can be qualified with schema only if the table, view, or materialized view containing the column is qualified with schema in the FROM clause. If you specify a member method of an object type, then you must follow the method name with parentheses even if the method takes no arguments.
指定代表您要选择的信息的表达式。仅当包含该列的表、视图或物化视图在 FROM 子句中用 schema 限定时,此列表中的列名称才能用 schema 限定。如果指定对象类型的成员方法,则即使该方法不带参数,也必须在方法名称后面加上括号。

The expression can also hold a scalar value that can be return values of PL/SQLfunctions, subqueries that return a single value per row, and SQL macros.
该表达式还可以保存标量值,该标量值可以是 PL/SQL 函数的返回值、每行返回单个值的子查询以及 SQL 宏。

c_alias c_别名

Specify an alias for the column expression. Oracle Database will use this alias in the column heading of the result set. The AS keyword is optional. The alias effectively renames the select list item for the duration of the query. The alias can be used in the order_by_clause but not other clauses in the query.
指定列表达式的别名。 Oracle 数据库将在结果集的列标题中使用此别名。 AS 关键字是可选的。别名在查询期间有效地重命名选择列表项。别名可以在 order_by_clause 中使用,但不能在查询中的其他子句中使用。

See Also: 也可以看看:

  • Oracle Database Data Warehousing Guide for information on using the expr AS c_alias syntax with the UNION ALL operator in queries of multiple materialized views
    Oracle 数据库数据仓库指南 有关在多个物化视图的查询

  • "About SQL Expressions" for the syntax of expr
    expr 语法的“关于 SQL 表达式”

Restrictions on the Select List
选择列表的限制

The select list is subject to the following restrictions:
选择列表受以下限制:

  • If you also specify a group_by_clause in this statement, then this select list can contain only the following types of expressions:
    如果您在此语句中还指定了 group_by_clause,则此选择列表只能包含以下类型的表达式:

    • Constants  常数

    • Aggregate functions and the functions USER, UID, and SYSDATE
      聚合函数和函数 USERUIDSYSDATE

    • Expressions identical to those in the group_by_clause. If the group_by_clause is in a subquery, then all columns in the select list of the subquery must match the GROUP BY columns in the subquery. If the select list and GROUP BY columns of a top-level query or of a subquery do not match, then the statement results in ORA-00979.
      表达式与 group_by_clause 中的表达式相同。如果 group_by_clause 在子查询中,则子查询的选择列表中的所有列都必须与子查询中的 GROUP BY 列匹配。如果顶级查询或子查询的选择列表与 GROUP BY 列不匹配,则该语句将导致 ORA-00979。

    • Expressions involving the preceding expressions that evaluate to the same value for all rows in a group
      涉及前面的表达式且组中所有行的计算结果相同的表达式

  • You can select a rowid from a join view only if the join has one and only one key-preserved table. The rowid of that table becomes the rowid of the view.
    仅当连接有且仅有一个键保留表时,您才可以从连接视图中选择 rowid。该表的 rowid 成为视图的 rowid。

    See Also: 也可以看看:

    Oracle Database Administrator's Guide for information on key-preserved tables
    Oracle 数据库管理员指南 有关密钥保留表的信息

  • If two or more tables have some column names in common, and if you are specifying a join in the FROM clause, then you must qualify column names with names of tables or table aliases.
    如果两个或多个表有一些共同的列名,并且您在 FROM 子句中指定联接,则必须使用表名或表别名来限定列名。

FROM Clause FROM 子句

The FROM clause lets you specify the objects from which data is selected.
FROM 子句允许您指定从中选择数据的对象。

You can invoke a polymorphic table function (PTF) in the query block of the FROM clause like other existing table functions. A PTF is a table function whose operands can have more than one type. .
您可以像其他现有表函数一样在 FROM 子句的查询块中调用多态表函数 (PTF)。 PTF 是一种表函数,其操作数可以有多种类型。 。

Starting with Oracle Database Release 19c, version 19.7, you can write table valued macros and use them inside the FROM clause, where it would be legal to call a PL/SQL function. SQL table macros are expressions, typically used in a FROM clause, to act like a kind of polymorphic (parameterized) views. You must define these macro functions in PL/SQL and call them from SQL for them to function as macros.
从 Oracle 数据库版本 19c 版本 19.7 开始,您可以编写表值宏并在 FROM 子句中使用它们,在该子句中调用 PL/SQL 函数是合法的。 SQL 表宏是表达式,通常在 FROM 子句中使用,其作用类似于一种多态(参数化)视图。您必须在 PL/SQL 中定义这些宏函数,并从 SQL 中调用它们,以便它们发挥宏的作用。

ONLY 仅有的

The ONLY clause applies only to views. Specify ONLY if the view in the FROM clause is a view belonging to a hierarchy and you do not want to include rows from any of its subviews.
ONLY 子句仅适用于视图。如果 FROM 子句中的视图是属于层次结构的视图,并且您不想包含其任何子视图中的行,请指定 ONLY

query_table_expression 查询表表达式

Use the query_table_expression clause to identify a subquery block, table, view, materialized view, analytic view, hierarchy, partition, or subpartition, or to specify a subquery that identifies the objects. In order to specify a subquery block, you must have specified the subquery block name (query_name in the subquery_factoring_clause or subav_name in the subav_factoring_clause ).
使用 query_table_expression 子句来标识子查询块、表、视图、物化视图、分析视图、层次结构、分区或子分区,或者指定标识对象的子查询。为了指定子查询块,您必须指定子查询块名称( subquery_factoring_clause 中的 query_namesubav_factoring_clause 中的 subav_name )。

The analytic view in the expression may be a transitory analytic view defined in the with_clause or a persistent analytic view.
表达式中的分析视图可以是 with_clause 中定义的瞬时分析视图或持久分析视图。

See Also: 也可以看看:

"Using Subqueries: Examples"
“使用子查询:示例”

LATERAL 

Specify LATERAL to designate subquery as a lateral inline view. Within a lateral inline view, you can specify tables that appear to the left of the lateral inline view in the FROM clause of a query. You can specify this left correlation anywhere within subquery (such as the SELECT, FROM, and WHERE clauses) and at any nesting level.
指定 LATERALsubquery 指定为横向内联视图。在横向内联视图中,您可以在查询的 FROM 子句中指定显示在横向内联视图左侧的表。您可以在 subquery 内的任何位置(例如 SELECTFROMWHERE 子句)和任何嵌套级别指定此左相关性。

Restrictions on LATERAL 横向限制

Lateral inline views are subject to the following restrictions:
横向内联视图受到以下限制:

  • If you specify LATERAL, then you cannot specify the pivot_clause, the unpivot_clause, or a pattern in the table_reference clause.
    如果指定 LATERAL ,则无法在 table_reference 子句中指定 pivot_clauseunpivot_clause 或模式。

  • If a lateral inline view contains the query_partition_clause, and it is the right side of a join clause, then it cannot contain a left correlation to the left table in the join clause. However, it can contain a left correlation to a table to its left in the FROM clause that is not the left table.
    如果横向内联视图包含 query_partition_clause ,并且它是 join 子句的右侧,则它不能包含与 join 子句中的左表的左关联。但是,它可以包含与 FROM 子句中左侧表(不是左表)的左相关性。

  • A lateral inline view cannot contain a left correlation to the first table in a right outer join or full outer join.
    横向内联视图不能在右外连接或完全外连接中包含与第一个表的左关联。

See Also: 也可以看看:

"Using Lateral Inline Views: Example"
“使用横向内联视图:示例”

inline_external_table 内联外部表

Specify this clause to inline an external table in a query. You must specify the table columns and properties for the external table that will be inlined in the query.
指定此子句可在查询中内联外部表。您必须指定将在查询中内联的外部表的表列和属性。

inline_external_table_properties
内联外部表属性

This clause extends the external_table_data_props with the REJECT LIMIT and access_driver_type options. Use this clause to specify the properties of the external table.
此子句使用 REJECT LIMITaccess_driver_type 选项扩展 external_table_data_props 。使用此子句指定外部表的属性。

In addition to supporting external data residing in operating file systems and Big Data sources and formats such as HDFS and Hive, Oracle supports external data residing in objects.
除了支持驻留在操作系统文件系统和大数据源以及 HDFS 和 Hive 等格式中的外部数据外,Oracle 还支持驻留在对象中的外部数据。

modified_external_table 修改的外部表

You can use this clause to override some external table properties specified by the CREATE TABLE or ALTER TABLE statements from within a query.
您可以使用此子句覆盖查询中 CREATE TABLEALTER TABLE 语句指定的某些外部表属性。

You can override external table parameters at runtime.
您可以在运行时覆盖外部表参数。

Restrictions 限制

  • You must specify the key words EXTERNAL MODIFY in the query. If you do not specify the keywords, you will see a Missing or invalid option error.
    您必须在查询中指定关键字 EXTERNAL MODIFY 。如果不指定关键字,您将看到 Missing or invalid option 错误。

  • You must reference an external table in the query. If you do not, you will see an error.
    您必须在查询中引用外部表。如果不这样做,您将看到错误。

  • You must specify at least one property in the query. One of DEFAULT DIRECTORY, LOCATION, ACCESS PARAMETERS, or REJECT LIMIT.
    您必须在查询中至少指定一个属性。 DEFAULT DIRECTORYLOCATIONACCESS PARAMETERSREJECT LIMIT 之一。

  • If you specify more than one external table properties, they must be listed in order. First the DEFAULT DIRECTORY must be specified, followed by the ACCESS PARAMETERS, LOCATION and REJECT LIMIT. Otherwise an error will be raised.
    如果指定多个外部表属性,则必须按顺序列出它们。首先必须指定 DEFAULT DIRECTORY ,然后是 ACCESS PARAMETERSLOCATIONREJECT LIMIT 。否则会引发错误。

  • In the DEFAULT DIRECTORY clause, you must specify only one proper default directory. Otherwise a Missing DEFAULT keyword error will occur.
    DEFAULT DIRECTORY 子句中,您必须仅指定一个适当的默认目录。否则会出现 Missing DEFAULT keyword 错误。

  • You must enclose a filename in the LOCATION clause within quotes. Otherwise a Missing keyword error will occur. Note that the access driver will decide whether or not to allow a LOCATION clause in the query. If the clause is disallowed for a particular access driver, an error will be raised.
    您必须将文件名括在 LOCATION 子句中并用引号括起来。否则会出现 Missing keyword 错误。请注意,访问驱动程序将决定是否允许查询中使用 LOCATION 子句。如果特定访问驱动程序不允许使用该子句,则会引发错误。

  • For ORACLE_LOADER and ORACLE_DATAPUMP access drivers, the external file location in the LOCATION clause must be specified in the following format: directory: location, i.e, the directory and location must be separated by a colon. Multiple locations in the clause must be separated by a comma. Otherwise, a Missing keyword error will occur.
    对于 ORACLE_LOADERORACLE_DATAPUMP 访问驱动程序, LOCATION 子句中的外部文件位置必须按以下格式指定:目录:位置,即目录和位置必须用冒号分隔。子句中的多个位置必须用逗号分隔。否则,将会出现 Missing keyword 错误。

  • Note that LOCATION will be made optional in CREATE TABLE, and must be specified either when creating or querying the external table. Otherwise an error will be raised in the access driver.
    请注意, LOCATIONCREATE TABLE 中将变为可选,并且必须在创建或查询外部表时指定。否则,访问驱动程序中将会出现错误。

  • When populating external data using ORACLE DATAPUMP via CTAS, the external file location must be specified. This will be the only case where LOCATION clause is mandatory in CREATE TABLE.
    当通过 CTAS 使用 ORACLE DATAPUMP 填充外部数据时,必须指定外部文件位置。这将是 LOCATION 子句在 CREATE TABLE 中是强制性的唯一情况。

  • When overriding access parameters, a proper access parameter list must be provided in the ACCESS PARAMETERS clause, with enclosing parentheses.
    当覆盖访问参数时,必须在 ACCESS PARAMETERS 子句中提供正确的访问参数列表,并用括号括起来。

    Note that the syntax and allowable values for the access parameters in the modified_external_table clause are the same as for the external table DDL for each access driver. For more see Oracle Database Utilitiesfor additional details regarding syntax and permissible values.
    请注意, modified_external_table 子句中访问参数的语法和允许值与每个访问驱动程序的外部表 DDL 相同。有关语法和允许值的更多详细信息,请参阅 Oracle 数据库实用程序。

  • If you specify the REJECT LIMIT, then it must either be UNLIMITED or some valid value that is within range. Otherwise a Reject limit out of range error will be raised.
    如果您指定 REJECT LIMIT ,则它必须是 UNLIMITED 或范围内的某个有效值。否则将引发 Reject limit out of range 错误。

modify_external_table_properties
修改外部表属性

You can specify the external table properties that you want to modify at run time using this clause. The parameters that you can modify are DEFAULT DIRECTORY, LOCATION, ACCESS PARAMETERS (BADFILE, LOGFILE, DISCARDFILE) and REJECT LIMIT.
您可以使用此子句指定要在运行时修改的外部表属性。您可以修改的参数是 DEFAULT DIRECTORYLOCATIONACCESS PARAMETERS (BADFILE, LOGFILE, DISCARDFILE)REJECT LIMIT

Example: Overriding External Table Parameters in a Query
示例:覆盖查询中的外部表参数

SELECT * FROM sales_external EXTERNAL MODIFY (LOCATION 'sales_9.csv’ REJECT LIMIT UNLIMITED);

flashback_query_clause  闪回查询子句

Use the flashback_query_clause to retrieve data from a table, view, or materialized view based on time dimensions associated with the data.
使用 flashback_query_clause 根据与数据关联的时间维度从表、视图或物化视图中检索数据。

This clause implements SQL-driven Flashback, which lets you specify the following:
该子句实现 SQL 驱动的闪回,它允许您指定以下内容:

  • A different system change number or timestamp for each object in the select list, using the clauses VERSIONS BETWEEN { SCN | TIMESTAMP } or VERSIONS AS OF { SCN | TIMESTAMP }. You can also implement session-level Flashback using the DBMS_FLASHBACK package.
    使用子句 VERSIONS BETWEEN { SCN | }VERSIONS AS OF { SCN TIMESTAMP } 。您还可以使用 DBMS_FLASHBACK 包实现会话级闪回。

  • A valid time period for each object in the select list, using the clauses VERSIONS PERIOD FOR or AS OF PERIOD FOR. You can also implement valid-time session-level Flashback using the DBMS_FLASHBACK_ARCHIVE package.
    选择列表中每个对象的有效时间段,使用子句 VERSIONS PERIOD FORAS OF PERIOD FOR 。您还可以使用 DBMS_FLASHBACK_ARCHIVE 包实现有效时间会话级闪回。

A Flashback Query lets you retrieve a history of changes made to a row. You can retrieve the corresponding identifier of the transaction that made the change using the VERSIONS_XID pseudocolumn. You can also retrieve information about the transaction that resulted in a particular row version by issuing an Oracle Flashback Transaction Query. You do this by querying the FLASHBACK_TRANSACTION_QUERY data dictionary view for a particular transaction ID.
闪回查询允许您检索对行所做的更改的历史记录。您可以使用 VERSIONS_XID 伪列检索进行更改的事务的相应标识符。您还可以通过发出 Oracle 闪回事务查询来检索有关导致特定行版本的事务的信息。您可以通过查询 FLASHBACK_TRANSACTION_QUERY 数据字典视图中的特定事务 ID 来完成此操作。

VERSIONS BETWEEN { SCN | TIMESTAMP }
{ SCN | 之间的版本时间戳}

Specify VERSIONS BETWEEN to retrieve multiple versions of the rows returned by the query. Oracle Database returns all committed versions of the rows that existed between two SCNs or between two timestamp values. The first specified SCN or timestamp must be earlier than the second specified SCN or timestamp. The rows returned include deleted and subsequently reinserted versions of the rows.
指定 VERSIONS BETWEEN 以检索查询返回的行的多个版本。 Oracle 数据库返回两个 SCN 之间或两个时间戳值之间存在的行的所有已提交版本。第一个指定的 SCN 或时间戳必须早于第二个指定的 SCN 或时间戳。返回的行包括行的已删除版本和随后重新插入的版本。

  • Specify VERSIONS BETWEEN SCN ... to retrieve the versions of the row that existed between two SCNs. Both expressions must evaluate to a number and cannot evaluate to NULL. MINVALUE and MAXVALUE resolve to the SCN of the oldest and most recent data available, respectively.
    指定 VERSIONS BETWEEN SCN ... 以检索两个 SCN 之间存在的行的版本。两个表达式的计算结果都必须为数字,并且不能计算为 NULL。 MINVALUEMAXVALUE 分别解析为最旧和最新可用数据的 SCN。

  • Specify VERSIONS BETWEEN TIMESTAMP ... to retrieve the versions of the row that existed between two timestamps. Both expressions must evaluate to a timestamp value and cannot evaluate to NULL. MINVALUE and MAXVALUE resolve to the timestamp of the oldest and most recent data available, respectively.
    指定 VERSIONS BETWEEN TIMESTAMP ... 以检索两个时间戳之间存在的行的版本。两个表达式的计算结果都必须为时间戳值,并且不能计算为 NULL。 MINVALUEMAXVALUE 分别解析为最旧和最新可用数据的时间戳。

AS OF { SCN | TIMESTAMP }
截至 {SCN |时间戳}

Specify AS OF to retrieve the single version of the rows returned by the query at a particular change number (SCN) or timestamp. If you specify SCN, then expr must evaluate to a number. If you specify TIMESTAMP, then expr must evaluate to a timestamp value. In either case, expr cannot evaluate to NULL. Oracle Database returns rows as they existed at the specified system change number or time.
指定 AS OF 以检索查询在特定更改号 (SCN) 或时间戳处返回的行的单一版本。如果您指定 SCN ,则 expr 的计算结果必须为数字。如果您指定 TIMESTAMP ,则 expr 必须计算为时间戳值。无论哪种情况, expr 都不能计算为 NULL。 Oracle 数据库返回在指定的系统更改编号或时间存在的行。

Oracle Database provides a group of version query pseudocolumns that let you retrieve additional information about the various row versions. Refer to "Version Query Pseudocolumns" for more information.
Oracle 数据库提供了一组版本查询伪列,可让您检索有关各个行版本的附加信息。有关详细信息,请参阅“版本查询伪列”。

When both clauses are used together, the AS OF clause determines the SCN or moment in time from which the database issues the query. The VERSIONS clause determines the versions of the rows as seen from the AS OF point. The database returns null for a row version if the transaction started before the first BETWEEN value or ended after the AS OF point.
当两个子句一起使用时, AS OF 子句确定数据库发出查询的 SCN 或时刻。 VERSIONS 子句确定从 AS OF 点看到的行的版本。如果事务在第一个 BETWEEN 值之前开始或在 AS OF 点之后结束,则数据库会为行版本返回 null。

VERSIONS PERIOD FOR 版本期限

Specify VERSIONS PERIOD FOR to retrieve rows from table based on whether they are considered valid during the specified time period. In order to use this clause, table must support Temporal Validity.
指定 VERSIONS PERIOD FOR 以根据 table 中的行在指定时间段内是否被视为有效来检索这些行。为了使用此子句, table 必须支持时间有效性。

  • For valid_time_column, specify the name of the valid time dimension column for table.
    对于 valid_time_column ,指定 table 的有效时间维度列的名称。

  • Use the BETWEEN clause to specify the time period during which rows are considered valid. Both expressions must evaluate to a timestamp value and cannot evaluate to NULL. MINVALUE resolves to the earliest date or timestamp in the start time column of table. MAXVALUE resolves to latest date or timestamp in the end time column of table.
    使用 BETWEEN 子句指定行被视为有效的时间段。两个表达式的计算结果都必须为时间戳值,并且不能计算为 NULL。 MINVALUE 解析为 table 开始时间列中的最早日期或时间戳。 MAXVALUE 解析为 table 结束时间列中的最新日期或时间戳。

AS OF PERIOD FOR 截至期间

Specify AS OF PERIOD FOR to retrieve rows from table based on whether they are considered valid as of the specified time. In order to use this clause, table must support Temporal Validity.
指定 AS OF PERIOD FOR 来根据 table 中的行是否被视为有效来检索行指定时间。为了使用此子句, table 必须支持时间有效性。

  • For valid_time_column, specify the name of the valid time dimension column for table.
    对于 valid_time_column ,指定 table 的有效时间维度列的名称。

  • Use expr to specify the time as of which rows are considered valid. The expression must evaluate to a timestamp value and cannot evaluate to NULL.
    使用 expr 指定行被视为有效的时间。该表达式的计算结果必须为时间戳值,并且不能计算为 NULL。

See Also: 也可以看看:

  • Oracle Database Development Guide for more information on Temporal Validity
    Oracle 数据库开发指南 了解有关时间有效性的更多信息

  • CREATE TABLE period_definition to learn how to configure a table to support Temporal Validity and for information about the valid_time_column, start time column, and end time column
    CREATE TABLE period_definition 了解如何配置表以支持时间有效性以及有关 valid_time_column 、开始时间列和结束时间列的信息

Note on Flashback Queries
关于闪回查询的注意事项

When performing a flashback query, Oracle Database might not use query optimizations that it would use for other types of queries, which could have a negative impact on performance. In particular, this occurs when you specify multiple flashback queries in a hierarchical query.
执行闪回查询时,Oracle 数据库可能不会使用用于其他类型查询的查询优化,这可能会对性能产生负面影响。特别是,当您在分层查询中指定多个闪回查询时,会发生这种情况。

Restrictions on Flashback Queries
闪回查询的限制

These queries are subject to the following restrictions:
这些查询受到以下限制:

  • You cannot specify a column expression or a subquery in the expression of the AS OF clause.
    您不能在 AS OF 子句的表达式中指定列表达式或子查询。

  • You cannot specify the AS OF clause if you have specified the for_update_clause.
    如果您已指定 for_update_clause ,则无法指定 AS OF 子句。

  • You cannot use the AS OF clause in the defining query of a materialized view.
    您不能在物化视图的定义查询中使用 AS OF 子句。

  • You cannot use the VERSIONS clause in flashback queries to temporary or external tables, or tables that are part of a cluster.
    您不能在对临时表、外部表或属于集群的表的闪回查询中使用 VERSIONS 子句。

  • You cannot use the VERSIONS clause in flashback queries to views. However, you can use the VERSIONS syntax in the defining query of a view.
    您不能在视图的闪回查询中使用 VERSIONS 子句。但是,您可以在定义视图查询时使用 VERSIONS 语法。

  • You cannot specify the flashback_query_clause if you have specified query_name in the query_table_expression.
    如果您在 query_table_expression 中指定了 query_name ,则无法指定 flashback_query_clause

See Also: 也可以看看:

partition_extension_clause
分区扩展子句

For PARTITION or SUBPARTITION, specify the name or key value of the partition or subpartition within table from which you want to retrieve data.
对于 PARTITIONSUBPARTITION ,指定 table 中要从中检索数据的分区或子分区的名称或键值。

For range- and list-partitioned data, as an alternative to this clause, you can specify a condition in the WHERE clause that restricts the retrieval to one or more partitions of table. Oracle Database will interpret the condition and fetch data from only those partitions. It is not possible to formulate such a WHERE condition for hash-partitioned data.
对于范围分区和列表分区数据,作为此子句的替代方案,您可以在 WHERE 子句中指定一个条件,将检索限制为 table 的一个或多个分区。 Oracle 数据库将解释条件并仅从这些分区获取数据。不可能为散列分区数据制定这样的 WHERE 条件。

See Also: 也可以看看:

"References to Partitioned Tables and Indexes" and "Selecting from a Partition: Example"
“对分区表和索引的引用”和“从分区中选择:示例”

dblink 数据库链接

For dblink, specify the complete or partial name for a database link to a remote database where the table, view, or materialized view is located. This database need not be an Oracle Database.
对于 dblink ,指定表、视图或物化视图所在的远程数据库的数据库链接的完整或部分名称。该数据库不必是 Oracle 数据库。

See Also: 也可以看看:

If you omit dblink, then the database assumes that the table, view, or materialized view is on the local database.
如果省略 dblink ,则数据库假定表、视图或物化视图位于本地数据库上。

Restrictions on Database Links
数据库链接的限制

Database links are subject to the following restrictions:
数据库链接受到以下限制:

  • You cannot query a user-defined type or an object REF on a remote table.
    您无法查询远程表上的用户定义类型或对象 REF

  • You cannot query columns of type ANYTYPE, ANYDATA, or ANYDATASET from remote tables.
    您无法从远程表查询 ANYTYPEANYDATAANYDATASET 类型的列。

table | view | materialized_view | analytic_view | hierarchy
表|查看 |物化视图 |分析视图 |等级制度

Specify the name of a table, view, materialized view, analytic view, or hierarchy from which data is selected.
指定从中选择数据的表、视图、物化视图、分析视图或层次结构的名称。

analytic_view 分析视图

A persistent analytic view defined with the CREATE ANALYTIC VIEW statement or a transitory analytic view defined in a WITH clause.
使用 CREATE ANALYTIC VIEW 语句定义的持久分析视图或在 WITH 子句中定义的暂时分析视图。

hierarchy 等级制度

A hierarchy defined with the CREATE HIERARCHY statement.
使用 CREATE HIERARCHY 语句定义的层次结构。

sample_clause 样本子句

The sample_clause lets you instruct the database to select from a random sample of data from the table, rather than from the entire table.
sample_clause 允许您指示数据库从表中的随机数据样本中进行选择,而不是从整个表中进行选择。