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: 也可以看看：
-
SQL Queries and Subqueries for general information on queries and subqueries
SQL 查询和子查询有关查询和子查询的一般信息 -
Oracle Database Data Warehousing Guide for more information on materialized views, query rewrite, and analytic views and hierarchies
Oracle 数据库数据仓库指南，了解有关物化视图、查询重写以及分析视图和层次结构的更多信息 -
If you are querying JSON data see Query JSON Data
如果您要查询 JSON 数据，请参阅查询 JSON 数据 -
If you are querying XML data see Querying XML Content Stored in Oracle XML DB
如果您要查询 XML 数据，请参阅查询 Oracle XML DB 中存储的 XML 内容
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.
要从表、物化视图、分析视图或层次结构中选择数据，对象必须位于您自己的架构中，或者您必须拥有表的 READ 或 SELECT 权限、物化视图、分析视图或层次结构。
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
READorSELECTprivilege on it, and
该对象必须位于您自己的架构中，或者您必须对其具有READ或SELECT权限，并且 -
Whoever owns the schema containing the object must have the
READorSELECTprivilege on the base tables.
拥有包含该对象的架构的人必须拥有基表的READ或SELECT权限。
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 TABLE 或 SELECT 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 子句，上述先决条件适用，但以下例外： READ 和 READ 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 闪回查询，您必须对选择列表中的对象具有 READ 或 SELECT 权限。此外，您必须对选择列表中的对象具有 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_declarations 、 subquery_factoring_clause 或 subav_factoring_clause 子句之一。
plsql_declarations::= plsql_声明::=
subquery_factoring_clause::=
子查询分解子句::=
search_clause::= 搜索子句::=
cycle_clause::= 循环子句::=
subav_factoring_clause::=
subav_clause::= subav_子句::=
hierarchies_clause::= 层次结构子句::=
filter_clauses::= 过滤子句::=
filter_clause::= 过滤子句::=
add_calcs_clause::=
calc_meas_clause::=
select_list::= 选择列表::=
table_reference::=
(query_table_expression::=, flashback_query_clause::=, pivot_clause::=, unpivot_clause::=, row_pattern_clause::=, containers_clause::=)
flashback_query_clause::=
query_table_expression::=
(analytic_view, hierarchy, subquery_restriction_clause::=, table_collection_expression::=)
（分析视图、层次结构、子查询限制子句::=、表集合表达式::=）
inline_external_table::=
内联外部表::=
inline_external_table_properties::=
内联外部表属性::=
modified_external_table::=
修改的外部表::=
modify_external_table_properties::=
修改外部表属性::=
pivot_for_clause::= 枢轴_for_子句::=
pivot_in_clause::= 子句中的枢轴::=
unpivot_in_clause::=
partition_extension_clause::=
分区扩展子句::=
table_collection_expression::=
表集合表达式::=
containers_clause::= 容器子句::=
shards_clause::= 分片子句::=
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::=）
query_partition_clause::=
查询分区子句::=
outer_join_type::= 外部连接类型::=
cross_outer_apply_clause::=
(table_reference::=, query_partition_clause::=)
（表引用::=，查询分区子句::=）
inline_analytic_view 内联分析视图
where_clause::= where_子句::=
hierarchical_query_clause::=
分层查询子句::=
(condition can be any condition as described in Conditions)
（ condition 可以是条件中描述的任何条件）
rollup_cube_clause::=
(grouping_expression_list::=)
（分组表达式列表::=）
grouping_sets_clause::= 分组集子句::=
(rollup_cube_clause::=, grouping_expression_list::=)
（rollup_cube_clause::=，grouping_expression_list::=）
grouping_expression_list::=
分组表达式列表::=
expression_list::= 表达式列表::=
model_clause::= 模型子句::=
(cell_reference_options::=, return_rows_clause::=, reference_model::=, main_model::=)
（cell_reference_options::=、return_rows_clause::=、reference_model::=、main_model::=）
cell_reference_options::=
单元格引用选项::=
return_rows_clause::=
reference_model::= 参考模型::=
main_model::= 主模型::=
(model_column_clauses::=, cell_reference_options::=, model_rules_clause::=)
（ model_column_clauses::=、cell_reference_options::=、model_rules_clause::=）
model_column_clauses::=
model_rules_clause::=
(model_iterate_clause::=, cell_assignment::=, order_by_clause::=)
（ model_iterate_clause::=、cell_assignment::=、order_by_clause::=）
model_iterate_clause::=
cell_assignment::= 单元格分配::=
single_column_for_loop::=
multi_column_for_loop::=
多列for_循环::=
row_limiting_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_partition_by::=
row_pattern_order_by::=
row_pattern_measures::= 行模式测量::=
row_pattern_measure_column::=
行_模式_测量_列::=
row_pattern_rows_per_match::=
row_pattern_skip_to::=
row_pattern::= 行模式::=
row_pattern_term::=
row_pattern_factor::= 行模式因子::=
row_pattern_primary::=
row_pattern_permute::=
row_pattern_quantifier::=
行模式量词::=
row_pattern_subset_clause::=
row_pattern_subset_item::=
row_pattern_definition_list::=
行模式定义列表::=
row_pattern_definition::=
行模式定义::=
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_classifier_func::=
row_pattern_match_num_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::=）
row_pattern_nav_logical::=
row_pattern_nav_逻辑::=
row_pattern_nav_physical::=
row_pattern_nav_compound::=
row_pattern_aggregate_func::=
Semantics 语义学
with_clause with_子句
Use the with_clause to define the following:
使用 with_clause 定义以下内容：
-
PL/SQL procedures and functions (using the
plsql_declarationsclause)
PL/SQL 过程和函数（使用plsql_declarations子句） -
Subquery blocks (using
subquery_factoring_clauseorsubav_factoring_clause, or both)
子查询块（使用subquery_factoring_clause或subav_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
SELECTstatement, then it must have either aWITHplsql_declarationsclause or theWITH_PLSQLhint.
如果顶级语句是SELECT语句，则它必须具有WITHplsql_declarations子句或WITH_PLSQL提示。 -
If the top-level statement is a
DELETE,MERGE,INSERT, orUPDATEstatement, then it must have theWITH_PLSQLhint.
如果顶级语句是DELETE、MERGE、INSERT或UPDATE语句，则它必须具有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: 也可以看看：
-
Oracle Database PL/SQL Language Reference for syntax and restrictions for
function_declarationandprocedure_declaration.
Oracle 数据库 PL/SQL 语言参考，了解function_declaration和procedure_declaration的语法和限制。 -
"Using a PL/SQL Function in the WITH Clause: Examples"
“在WITH子句中使用PL/SQL函数：示例”
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_clause 和 cycle_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 ALL 、 UNION 、 INTERSECT 或 < 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
DISTINCTkeyword or aGROUPBYclause
DISTINCT关键字或GROUPBY子句 -
The
model_clausemodel_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_nameas 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
BREADTHFIRSTBYif you want sibling rows returned before any child rows are returned.
如果您希望在返回任何子行之前返回同级行，请指定BREADTHFIRSTBY。 -
Specify
DEPTHFIRSTBYif you want child rows returned before any siblings rows are returned.
如果您希望在返回任何同级行之前返回子行，请指定DEPTHFIRSTBY。 -
Sibling rows are ordered by the columns listed after the
BYkeyword.
同级行按BY关键字后列出的列排序。 -
The
c_aliaslist following theSEARCHkeyword must contain column names from the column alias list forquery_name.
SEARCH关键字后面的c_alias列表必须包含query_name的列别名列表中的列名称。 -
The
ordering_columnis automatically added to the column list for the query name. The query that selects fromquery_namecan include anORDERBYonordering_columnto return the rows in the order that was specified by theSEARCHclause.
ordering_column会自动添加到查询名称的列列表中。从query_name中选择的查询可以在ordering_column上包含ORDERBY，以按 < 指定的顺序返回行。 b5> 子句。
cycle_clause 循环子句
Use the CYCLE clause to mark cycles in the recursion.
使用 CYCLE 子句标记递归中的循环。
-
The
c_aliaslist following theCYCLEkeyword must contain column names from the column alias list forquery_name. Oracle Database uses these columns to detect a cycle.
CYCLE关键字后面的c_alias列表必须包含query_name的列别名列表中的列名称。 Oracle 数据库使用这些列来检测循环。 -
cycle_valueandno_cycle_valueshould be character strings of length 1.
cycle_value和no_cycle_value应该是长度为1的字符串。 -
If a cycle is detected, then the cycle mark column specified by
cycle_mark_c_aliasfor the row causing the cycle is set to the value specified forcycle_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_clausein a single SQL statement. Anyquery_namedefined in thesubquery_factoring_clausecan be used in any subsequent named query block in thesubquery_factoring_clause.
在一条 SQL 语句中只能指定一个subquery_factoring_clause。subquery_factoring_clause中定义的任何query_name都可以在subquery_factoring_clause中的任何后续命名查询块中使用。 -
In a compound query with set operators, you cannot use the
query_namefor any of the component queries, but you can use thequery_namein theFROMclause 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_columnhas to be different from the name used forcycle_mark_c_alias.
ordering_column使用的名称必须与cycle_mark_c_alias使用的名称不同。 -
The
ordering_columnand cycle mark column names cannot already be in the column alias list forquery_name.
ordering_column和循环标记列名称不能已位于query_name的列别名列表中。
See Also: 也可以看看：
-
Oracle Database Concepts for information about inline views
Oracle 数据库概念，了解有关内联视图的信息 -
"Subquery Factoring: Example"
“子查询因式分解：示例” -
"Recursive Subquery Factoring: Examples"
“递归子查询分解：示例”
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 语句中定义。如果分析视图是持久视图，则当前用户必须对其具有选择访问权限。
See Also: 也可以看看：
Analytic Views: Examples分析视图：示例
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.
如果您希望数据库仅返回所选的每组重复行的一个副本，请指定 DISTINCT 或 UNIQUE 。这两个关键字是同义词。重复行是指选择列表中每个表达式的值都匹配的行。
Restrictions on DISTINCT and UNIQUE Queries
对 DISTINCT 和 UNIQUE 查询的限制
These types of queries are subject to the following restrictions:
这些类型的查询受到以下限制：
-
When you specify
DISTINCTorUNIQUE, 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 parameterDB_BLOCK_SIZE.
当您指定DISTINCT或UNIQUE时，所有选择列表表达式中的字节总数限制为数据块的大小减去一些开销。该大小由初始化参数DB_BLOCK_SIZE指定。 -
You cannot specify
DISTINCTif theselect_listcontains 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_expression （ FROM 子句中指定 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
exprASc_aliassyntax with theUNIONALLoperator 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, andSYSDATE
聚合函数和函数USER、UID和SYSDATE -
Expressions identical to those in the
group_by_clause. If thegroup_by_clauseis in a subquery, then all columns in the select list of the subquery must match theGROUPBYcolumns in the subquery. If the select list andGROUPBYcolumns 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在子查询中，则子查询的选择列表中的所有列都必须与子查询中的GROUPBY列匹配。如果顶级查询或子查询的选择列表与GROUPBY列不匹配，则该语句将导致 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
FROMclause, 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 中调用它们，以便它们发挥宏的作用。
See Also: 也可以看看：
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_name 或 subav_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"
“使用子查询：示例”
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.
指定 LATERAL 将 subquery 指定为横向内联视图。在横向内联视图中，您可以在查询的 FROM 子句中指定显示在横向内联视图左侧的表。您可以在 subquery 内的任何位置（例如 SELECT 、 FROM 和 WHERE 子句）和任何嵌套级别指定此左相关性。
Restrictions on LATERAL 横向限制
Lateral inline views are subject to the following restrictions:
横向内联视图受到以下限制：
-
If you specify
LATERAL, then you cannot specify thepivot_clause, theunpivot_clause, or a pattern in thetable_referenceclause.
如果指定LATERAL，则无法在table_reference子句中指定pivot_clause、unpivot_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 theFROMclause 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 LIMIT 和 access_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 TABLE 或 ALTER TABLE 语句指定的某些外部表属性。
You can override external table parameters at runtime.
您可以在运行时覆盖外部表参数。
Restrictions 限制
-
You must specify the key words
EXTERNAL MODIFYin the query. If you do not specify the keywords, you will see aMissing or invalid optionerror.
您必须在查询中指定关键字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, orREJECT LIMIT.
您必须在查询中至少指定一个属性。DEFAULT DIRECTORY、LOCATION、ACCESS PARAMETERS或REJECT LIMIT之一。 -
If you specify more than one external table properties, they must be listed in order. First the
DEFAULT DIRECTORYmust be specified, followed by theACCESS PARAMETERS,LOCATIONandREJECT LIMIT. Otherwise an error will be raised.
如果指定多个外部表属性，则必须按顺序列出它们。首先必须指定DEFAULT DIRECTORY，然后是ACCESS PARAMETERS、LOCATION和REJECT LIMIT。否则会引发错误。 -
In the
DEFAULT DIRECTORYclause, you must specify only one proper default directory. Otherwise aMissing DEFAULT keyworderror will occur.
在DEFAULT DIRECTORY子句中，您必须仅指定一个适当的默认目录。否则会出现Missing DEFAULT keyword错误。 -
You must enclose a filename in the
LOCATIONclause within quotes. Otherwise aMissing keyworderror will occur. Note that the access driver will decide whether or not to allow aLOCATIONclause in the query. If the clause is disallowed for a particular access driver, an error will be raised.
您必须将文件名括在LOCATION子句中并用引号括起来。否则会出现Missing keyword错误。请注意，访问驱动程序将决定是否允许查询中使用LOCATION子句。如果特定访问驱动程序不允许使用该子句，则会引发错误。 -
For
ORACLE_LOADERandORACLE_DATAPUMPaccess drivers, the external file location in theLOCATIONclause 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, aMissing keyworderror will occur.
对于ORACLE_LOADER和ORACLE_DATAPUMP访问驱动程序，LOCATION子句中的外部文件位置必须按以下格式指定：目录：位置，即目录和位置必须用冒号分隔。子句中的多个位置必须用逗号分隔。否则，将会出现Missing keyword错误。 -
Note that
LOCATIONwill be made optional inCREATE TABLE, and must be specified either when creating or querying the external table. Otherwise an error will be raised in the access driver.
请注意，LOCATION在CREATE TABLE中将变为可选，并且必须在创建或查询外部表时指定。否则，访问驱动程序中将会出现错误。 -
When populating external data using
ORACLE DATAPUMPviaCTAS, the external file location must be specified. This will be the only case whereLOCATIONclause is mandatory inCREATE TABLE.
当通过CTAS使用ORACLE DATAPUMP填充外部数据时，必须指定外部文件位置。这将是LOCATION子句在CREATE TABLE中是强制性的唯一情况。 -
When overriding access parameters, a proper access parameter list must be provided in the
ACCESS PARAMETERSclause, with enclosing parentheses.
当覆盖访问参数时，必须在ACCESS PARAMETERS子句中提供正确的访问参数列表，并用括号括起来。Note that the syntax and allowable values for the access parameters in the
modified_external_tableclause 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 beUNLIMITEDor some valid value that is within range. Otherwise aReject limit out of rangeerror 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 DIRECTORY 、 LOCATION 、 ACCESS 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
VERSIONSBETWEEN{SCN|TIMESTAMP}orVERSIONSASOF{SCN|TIMESTAMP}. You can also implement session-level Flashback using theDBMS_FLASHBACKpackage.
使用子句VERSIONSBETWEEN{SCN|}或VERSIONSASOF{SCNTIMESTAMP}。您还可以使用DBMS_FLASHBACK包实现会话级闪回。 -
A valid time period for each object in the select list, using the clauses
VERSIONSPERIODFORorASOFPERIODFOR. You can also implement valid-time session-level Flashback using theDBMS_FLASHBACK_ARCHIVEpackage.
选择列表中每个对象的有效时间段，使用子句VERSIONSPERIODFOR或ASOFPERIODFOR。您还可以使用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
VERSIONSBETWEENSCN... to retrieve the versions of the row that existed between two SCNs. Both expressions must evaluate to a number and cannot evaluate to NULL.MINVALUEandMAXVALUEresolve to the SCN of the oldest and most recent data available, respectively.
指定VERSIONSBETWEENSCN... 以检索两个 SCN 之间存在的行的版本。两个表达式的计算结果都必须为数字，并且不能计算为 NULL。MINVALUE和MAXVALUE分别解析为最旧和最新可用数据的 SCN。 -
Specify
VERSIONSBETWEENTIMESTAMP... 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.MINVALUEandMAXVALUEresolve to the timestamp of the oldest and most recent data available, respectively.
指定VERSIONSBETWEENTIMESTAMP... 以检索两个时间戳之间存在的行的版本。两个表达式的计算结果都必须为时间戳值，并且不能计算为 NULL。MINVALUE和MAXVALUE分别解析为最旧和最新可用数据的时间戳。
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 fortable.
对于valid_time_column，指定table的有效时间维度列的名称。 -
Use the
BETWEENclause to specify the time period during which rows are considered valid. Both expressions must evaluate to a timestamp value and cannot evaluate to NULL.MINVALUEresolves to the earliest date or timestamp in the start time column oftable.MAXVALUEresolves to latest date or timestamp in the end time column oftable.
使用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 fortable.
对于valid_time_column，指定table的有效时间维度列的名称。 -
Use
exprto 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 数据库开发指南 了解有关时间有效性的更多信息 -
CREATETABLEperiod_definition to learn how to configure a table to support Temporal Validity and for information about thevalid_time_column, start time column, and end time column
CREATETABLEperiod_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
ASOFclause.
您不能在ASOF子句的表达式中指定列表达式或子查询。 -
You cannot specify the
ASOFclause if you have specified thefor_update_clause.
如果您已指定for_update_clause，则无法指定ASOF子句。 -
You cannot use the
ASOFclause in the defining query of a materialized view.
您不能在物化视图的定义查询中使用ASOF子句。 -
You cannot use the
VERSIONSclause in flashback queries to temporary or external tables, or tables that are part of a cluster.
您不能在对临时表、外部表或属于集群的表的闪回查询中使用VERSIONS子句。 -
You cannot use the
VERSIONSclause in flashback queries to views. However, you can use theVERSIONSsyntax in the defining query of a view.
您不能在视图的闪回查询中使用VERSIONS子句。但是，您可以在定义视图查询时使用VERSIONS语法。 -
You cannot specify the
flashback_query_clauseif you have specifiedquery_namein thequery_table_expression.
如果您在query_table_expression中指定了query_name，则无法指定flashback_query_clause。
See Also: 也可以看看：
-
Oracle Database Development Guide for more information on Oracle Flashback Query
Oracle 数据库开发指南 了解有关 Oracle 闪回查询的更多信息 -
"Using Flashback Queries: Example"
“使用闪回查询：示例” -
Oracle Database Development Guide and Oracle Database PL/SQL Packages and Types Reference for information about session-level Flashback using the
DBMS_FLASHBACKpackage
Oracle 数据库开发指南和 Oracle 数据库 PL/SQL 包和类型参考，了解有关使用DBMS_FLASHBACK包的会话级闪回的信息 -
Oracle Database Administrator's Guide and to the description of
FLASHBACK_TRANSACTION_QUERYin the Oracle Database Reference for more information about transaction history
Oracle 数据库管理员指南以及 Oracle 数据库参考中FLASHBACK_TRANSACTION_QUERY的描述，了解有关事务历史记录的更多信息
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.
对于 PARTITION 或 SUBPARTITION ，指定 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: 也可以看看：
-
"References to Objects in Remote Databases" for more information on referring to database links
有关引用数据库链接的更多信息，请参阅“对远程数据库中对象的引用” -
"Distributed Queries" for more information about distributed queries and "Using Distributed Queries: Example"
“分布式查询”了解有关分布式查询的更多信息和“使用分布式查询：示例”
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
REFon a remote table.
您无法查询远程表上的用户定义类型或对象REF。 -
You cannot query columns of type
ANYTYPE,ANYDATA, orANYDATASETfrom remote tables.
您无法从远程表查询ANYTYPE、ANYDATA或ANYDATASET类型的列。
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 子句中定义的暂时分析视图。
See Also: 也可以看看：
Analytic Views: Examples分析视图：示例
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 允许您指示数据库从表中的随机数据样本中进行选择，而不是从整个表中进行选择。
See Also: 也可以看看：
"Selecting a Sample: Examples"
“选择样本：示例”
BLOCK 堵塞
BLOCK instructs the database to attempt to perform random block sampling instead of random row sampling.
BLOCK 指示数据库尝试执行随机块采样而不是随机行采样。
Block sampling is possible only during full table scans or index fast full scans. If a more efficient execution path exists, then Oracle Database does not perform block sampling. If you want to guarantee block sampling for a particular table or index, then use the FULL or INDEX_FFS hint.
仅在全表扫描或索引快速全扫描期间才可以进行块采样。如果存在更有效的执行路径，则 Oracle 数据库不会执行块采样。如果要保证特定表或索引的块采样，请使用 FULL 或 INDEX_FFS 提示。
Beginning with Oracle Database 12c Release 2 (12.2.), you can specify block sampling for external tables. In earlier releases, specifying block sampling for external tables had no effect; row sampling was performed.
从 Oracle Database 12c 第 2 版 (12.2.) 开始，您可以为外部表指定块采样。在早期版本中，为外部表指定块采样没有效果；进行了行抽样。
sample_percent 样本百分比
For sample_percent, specify the percentage of the total row or block count to be included in the sample. The value must be in the range .000001 to, but not including, 100. This percentage indicates the probability of each row, or each cluster of rows in the case of block sampling, being selected as part of the sample. It does not mean that the database will retrieve exactly sample_percent of the rows of table.
对于 sample_percent ，指定要包含在样本中的总行数或块数的百分比。该值必须在 0.000001 到 100 之间（但不包括 100）。此百分比表示每行或块抽样情况下的每个行簇被选为样本一部分的概率。这并不意味着数据库将准确检索 table 行中的 sample_percent 。
WARNING:
The use of statistically incorrect assumptions when using this feature can lead to incorrect or undesirable results.
警告：使用此功能时使用统计上不正确的假设可能会导致不正确或不需要的结果。
SEED seed_value SEED 种子值
Specify this clause to instruct the database to attempt to return the same sample from one execution to the next. The seed_value must be an integer between 0 and 4294967295. If you omit this clause, then the resulting sample will change from one execution to the next.
指定此子句可指示数据库尝试从一次执行到下一次执行返回相同的样本。 seed_value 必须是 0 到 4294967295 之间的整数。如果省略此子句，则生成的样本将从一次执行更改为下一次执行。
Restrictions on sample_clause
对sample_clause的限制
The following restrictions apply to the SAMPLE clause:
以下限制适用于 SAMPLE 子句：
-
You cannot specify the
SAMPLEclause in a subquery in a DML statement.
您不能在 DML 语句的子查询中指定SAMPLE子句。 -
You can specify the
SAMPLEclause in a query on a base table, a container table of a materialized view, or a view that is key preserving. You cannot specify this clause on a view that is not key preserving.
您可以在基表、物化视图的容器表或保留键的视图的查询中指定SAMPLE子句。您不能在不保留键的视图上指定此子句。
subquery_restriction_clause
子查询限制子句
The subquery_restriction_clause lets you restrict the subquery in one of the following ways:
subquery_restriction_clause 允许您通过以下方式之一限制子查询：
WITH READ ONLY 只读
Specify WITH READ ONLY to indicate that the table or view cannot be updated.
指定 WITH READ ONLY 表示表或视图无法更新。
WITH CHECK OPTION 带检查选项
Specify WITH CHECK OPTION to indicate that Oracle Database prohibits any changes to the table or view that would produce rows that are not included in the subquery. When used in the subquery of a DML statement, you can specify this clause in a subquery in the FROM clause but not in subquery in the WHERE clause.
指定 WITH CHECK OPTION 表示 Oracle 数据库禁止对表或视图进行任何更改，否则会产生未包含在子查询中的行。当用于 DML 语句的子查询时，可以在子查询的 FROM 子句中指定该子句，但不能在子查询的 WHERE 子句中指定。
CONSTRAINT constraint CONSTRAINT 约束
Specify the name of the CHECK OPTION constraint. If you omit this identifier, then Oracle automatically assigns the constraint a name of the form SYS_Cn, where n is an integer that makes the constraint name unique within the database.
指定 CHECK OPTION 约束的名称。如果省略此标识符，Oracle 会自动为约束分配一个 SYS_C n 形式的名称，其中 n 是一个整数，使约束名称在数据库中唯一。
See Also: 也可以看看：
"Using the WITH CHECK OPTION Clause: Example"
“使用WITH CHECK OPTION子句：示例”
table_collection_expression
表集合表达式
The table_collection_expression lets you inform Oracle that the value of collection_expression should be treated as a table for purposes of query and DML operations. The collection_expression can be a subquery, a column, a function, or a collection constructor. Regardless of its form, it must return a collection value—that is, a value whose type is nested table or varray. This process of extracting the elements of a collection is called collection unnesting.
table_collection_expression 可让您通知 Oracle collection_expression 的值应被视为表以进行查询和 DML 操作。 collection_expression 可以是子查询、列、函数或集合构造函数。无论其形式如何，它都必须返回一个集合值，即类型为嵌套表或变量数组的值。提取集合元素的过程称为集合取消嵌套。
The optional plus (+) is relevant if you are joining the TABLE collection expression with the parent table. The + creates an outer join of the two, so that the query returns rows from the outer table even if the collection expression is null.
如果您要将 TABLE 集合表达式与父表连接，则可选的加号 (+) 是相关的。 + 创建两者的外连接，以便查询从外表返回行，即使集合表达式为 null。
Note:
In earlier releases of Oracle, when collection_expression was a subquery, table_collection_expression was expressed as THE subquery. That usage is now deprecated.
注意：在 Oracle 的早期版本中，当
collection_expression 是子查询时， table_collection_expression 表示为 THE subquery 。这种用法现在已被弃用。
The collection_expression can reference columns of tables defined to its left in the FROM clause. This is called left correlation. Left correlation can occur only in table_collection_expression. Other subqueries cannot contains references to columns defined outside the subquery.
collection_expression 可以引用 FROM 子句中定义在其左侧的表的列。这称为左相关。左相关只能发生在 table_collection_expression 中。其他子查询不能包含对子查询外部定义的列的引用。
The optional (+) lets you specify that table_collection_expression should return a row with all fields set to null if the collection is null or empty. The (+) is valid only if collection_expression uses left correlation. The result is similar to that of an outer join.
可选的 (+) 允许您指定如果集合为 null 或空， table_collection_expression 应返回所有字段设置为 null 的行。仅当 collection_expression 使用左相关时， (+) 才有效。结果与外连接的结果类似。
When you use the (+) syntax in the WHERE clause of a subquery in an UPDATE or DELETE operation, you must specify two tables in the FROM clause of the subquery. Oracle Database ignores the outer join syntax unless there is a join in the subquery itself.
当您在 UPDATE 或 DELETE 操作的子查询的 WHERE 子句中使用 (+) 语法时，必须在子查询的 FROM 子句。 Oracle 数据库会忽略外连接语法，除非子查询本身存在连接。
See Also: 也可以看看：
-
"Outer Joins" “外连接”
-
"Table Collections: Examples" and "Collection Unnesting: Examples"
“表集合：示例”和“集合解除嵌套：示例”
t_alias t_别名
Specify a correlation name, which is an alias for the table, view, materialized view, or subquery for evaluating the query. This alias is required if the select list references any object type attributes or object type methods. Correlation names are most often used in a correlated query. Other references to the table, view, or materialized view throughout the query must refer to this alias.
指定关联名称，它是用于评估查询的表、视图、物化视图或子查询的别名。如果选择列表引用任何对象类型属性或对象类型方法，则需要此别名。关联名称最常用于关联查询中。整个查询中对表、视图或物化视图的其他引用必须引用此别名。
See Also: 也可以看看：
"Using Correlated Subqueries: Examples"
“使用相关子查询：示例”
pivot_clause 枢轴子句
The pivot_clause lets you write cross-tabulation queries that rotate rows into columns, aggregating data in the process of the rotation. The output of a pivot operation typically includes more columns and fewer rows than the starting data set. The pivot_clause performs the following steps:
pivot_clause 允许您编写交叉表查询，将行旋转为列，并在旋转过程中聚合数据。透视操作的输出通常包括比起始数据集更多的列和更少的行。 pivot_clause 执行以下步骤：
-
The
pivot_clausecomputes the aggregation functions specified at the beginning of the clause. Aggregation functions must specify aGROUPBYclause to return multiple values, yet thepivot_clausedoes not contain an explicitGROUPBYclause. Instead, thepivot_clauseperforms an implicitGROUPBY. The implicit grouping is based on all the columns not referred to in thepivot_clause, along with the set of values specified in thepivot_in_clause.). If you specify more than one aggregation function, then you must provide aliases for at least all but one of the aggregation functions.
pivot_clause计算子句开头指定的聚合函数。聚合函数必须指定GROUPBY子句才能返回多个值，但pivot_clause不包含显式GROUPBY执行隐式GROUPBY。隐式分组基于pivot_clause中未引用的所有列以及pivot_in_clause中指定的值集。）。如果指定多个聚合函数，则必须至少为除其中之一之外的所有聚合函数提供别名。 -
The grouping columns and aggregated values calculated in Step 1 are configured to produce the following cross-tabular output:
配置步骤 1 中计算的分组列和聚合值以生成以下跨表输出：-
All the implicit grouping columns not referred to in the
pivot_clause, followed by
pivot_clause中未提及的所有隐式分组列，后跟 -
New columns corresponding to values in the
pivot_in_clause. Each aggregated value is transposed to the appropriate new column in the cross-tabulation. If you specify theXMLkeyword, then the result is a single new column that expresses the data as an XML string. The database generates a name for each new column. If you do not provide an alias for an aggregation function, then the database uses each pivot column value as the name for each new column to which that aggregated value is transposed. If you provide an alias for an aggregation function, then the database generates a name for each new column to which that aggregated value is transposed by concatenating the pivot column name, the underscore character (_), and the aggregation function alias. If a generated column name exceeds the maximum length of a column name, then an ORA-00918 error is returned. To avoid this issue, specify a shorter alias for the pivot column heading, the aggregation function, or both.
与pivot_in_clause中的值相对应的新列。每个聚合值都会转置到交叉表中相应的新列。如果指定XML关键字，则结果是将数据表示为 XML 字符串的单个新列。数据库为每个新列生成一个名称。如果您没有为聚合函数提供别名，则数据库将使用每个数据透视列值作为该聚合值转置到的每个新列的名称。如果您为聚合函数提供别名，则数据库会通过连接数据透视列名称、下划线字符 (_) 和聚合函数别名，为聚合值转置到的每个新列生成一个名称。如果生成的列名超过列名的最大长度，则返回 ORA-00918 错误。要避免此问题，请为数据透视列标题、聚合函数或两者指定较短的别名。
-
The subclauses of the pivot_clause have the following semantics:
pivot_clause 的子句具有以下语义：
XML
The optional XML keyword generates XML output for the query. The XML keyword permits the pivot_in_clause to contain either a subquery or the wildcard keyword ANY. Subqueries and ANY wildcards are useful when the pivot_in_clause values are not known in advance. With XML output, the values of the pivot column are evaluated at execution time. You cannot specify XML when you specify explicit pivot values using expressions in the pivot_in_clause.
可选的 XML 关键字生成查询的 XML 输出。 XML 关键字允许 pivot_in_clause 包含子查询或通配符关键字 ANY 。当事先未知 pivot_in_clause 值时，子查询和 ANY 通配符非常有用。对于 XML 输出，数据透视列的值在执行时进行评估。当您使用 pivot_in_clause 中的表达式指定显式枢轴值时，无法指定 XML 。
When XML output is generated, the aggregate function is applied to each distinct pivot value, and the database returns a column of XMLType containing an XML string for all value and measure pairs.
生成 XML 输出时，聚合函数将应用于每个不同的主值，并且数据库返回包含所有值和度量对的 XML 字符串的 XMLType 列。
expr 表达式
For expr, specify an expression that evaluates to a constant value of a pivot column. You can optionally provide an alias for each pivot column value. If there is no alias, the column heading becomes a quoted identifier.
对于 expr ，指定一个计算结果为数据透视列常量值的表达式。您可以选择为每个数据透视列值提供别名。如果没有别名，则列标题将成为带引号的标识符。
subquery 子查询
A subquery is used only in conjunction with the XML keyword. When you specify a subquery, all values found by the subquery are used for pivoting. The output is not the same cross-tabular format returned by non-XML pivot queries. Instead of multiple columns specified in the pivot_in_clause, the subquery produces a single XML string column. The XML string for each row holds aggregated data corresponding to the implicit GROUP BY value of that row. The XML string for each output row includes all pivot values found by the subquery, even if there are no corresponding rows in the input data.
子查询仅与 XML 关键字结合使用。当您指定子查询时，子查询找到的所有值都将用于透视。输出与非 XML 数据透视查询返回的跨表格式不同。子查询生成单个 XML 字符串列，而不是 pivot_in_clause 中指定的多个列。每行的 XML 字符串保存与该行的隐式 GROUP BY 值相对应的聚合数据。每个输出行的 XML 字符串都包含子查询找到的所有主元值，即使输入数据中没有对应的行也是如此。
The subquery must return a list of unique values at the execution time of the pivot query. If the subquery does not return a unique value, then Oracle Database raises a run-time error. Use the DISTINCT keyword in the subquery if you are not sure the query will return unique values.
子查询必须在执行数据透视查询时返回唯一值的列表。如果子查询不返回唯一值，则 Oracle 数据库将引发运行时错误。如果您不确定查询是否会返回唯一值，请在子查询中使用 DISTINCT 关键字。
ANY
The ANY keyword is used only in conjunction with the XML keyword. The ANY keyword acts as a wildcard and is similar in effect to subquery. The output is not the same cross-tabular format returned by non-XML pivot queries. Instead of multiple columns specified in the pivot_in_clause, the ANY keyword produces a single XML string column. The XML string for each row holds aggregated data corresponding to the implicit GROUP BY value of that row. However, in contrast to the behavior when you specify subquery, the ANY wildcard produces an XML string for each output row that includes only the pivot values found in the input data corresponding to that row.
ANY 关键字仅与 XML 关键字结合使用。 ANY 关键字充当通配符，其效果与 subquery 类似。输出与非 XML 数据透视查询返回的跨表格式不同。 ANY 关键字生成单个 XML 字符串列，而不是 pivot_in_clause 中指定的多个列。每行的 XML 字符串保存与该行的隐式 GROUP BY 值相对应的聚合数据。但是，与指定 subquery 时的行为相反， ANY 通配符为每个输出行生成一个 XML 字符串，其中仅包含在与该行对应的输入数据中找到的主值。
See Also: 也可以看看：
Oracle Database Data Warehousing Guide for more information about PIVOT and UNPIVOT and "Using PIVOT and UNPIVOT: Examples"
Oracle 数据库数据仓库指南，了解有关 PIVOT 和 UNPIVOT 以及“使用 PIVOT 和 UNPIVOT：示例”的更多信息
unpivot_clause unpivot_子句
The unpivot_clause rotates columns into rows.
unpivot_clause 将列旋转为行。
-
The
INCLUDE|EXCLUDENULLSclause gives you the option of including or excluding null-valued rows.INCLUDENULLScauses the unpivot operation to include null-valued rows;EXCLUDENULLSeliminates null-values rows from the return set. If you omit this clause, then the unpivot operation excludes nulls.
INCLUDE|EXCLUDENULLS子句提供包含或排除空值行的选项。INCLUDENULLS导致逆透视操作包含空值行；EXCLUDENULLS从返回集中消除空值行。如果省略此子句，则 unpivot 操作将排除空值。 -
For
column, specify a name for each output column that will hold measure values, such assales_quantity.
对于column，为将保存度量值的每个输出列指定名称，例如sales_quantity。 -
In the
pivot_for_clause, specify a name for each output column that will hold descriptor values, such as quarter or product.
在pivot_for_clause中，为每个将保存描述符值的输出列指定名称，例如季度或产品。 -
In the
unpivot_in_clause, specify the input data columns whose names will become values in the output columns of thepivot_for_clause. These input data columns have names specifying a category value, such as Q1, Q2, Q3, Q4. The optionalASclause lets you map the input data column names to the specifiedliteralvalues in the output columns.
在unpivot_in_clause中，指定输入数据列，其名称将成为pivot_for_clause输出列中的值。这些输入数据列的名称指定了类别值，例如 Q1、Q2、Q3、Q4。可选的AS子句允许您将输入数据列名称映射到输出列中指定的literal值。
The unpivot operation turns a set of value columns into one column. Therefore, the data types of all the value columns must be in the same data type group, such as numeric or character.
unpivot 操作将一组值列转换为一个列。因此，所有值列的数据类型必须属于同一数据类型组，例如数字或字符。
-
If all the value columns are
CHAR, then the unpivoted column isCHAR. If any value column isVARCHAR2, then the unpivoted column isVARCHAR2.
如果所有值列均为CHAR，则取消透视的列为CHAR。如果任何值列是VARCHAR2，则取消透视的列是VARCHAR2。 -
If all the value columns are
NUMBER, then the unpivoted column isNUMBER. If any value column isBINARY_DOUBLE, then the unpivoted column isBINARY_DOUBLE. If no value column isBINARY_DOUBLEbut any value column isBINARY_FLOAT, then the unpivoted column isBINARY_FLOAT.
如果所有值列均为NUMBER，则取消透视的列为NUMBER。如果任何值列是BINARY_DOUBLE，则取消透视的列是BINARY_DOUBLE。如果没有值列是BINARY_DOUBLE但任何值列都是BINARY_FLOAT，则取消透视的列是BINARY_FLOAT。
containers_clause 容器子句
The CONTAINERS clause is useful in a multitenant container database (CDB). This clause lets you query data in the specified table or view across all containers in a CDB.
CONTAINERS 子句在多租户容器数据库 (CDB) 中很有用。此子句允许您跨 CDB 中的所有容器查询指定表或视图中的数据。
-
To query data in a CDB, you must be a common user connected to the CDB root, and the table or view must exist in the root and all PDBs. The query returns all rows from the table or view in the CDB root and in all open PDBs.
要查询CDB中的数据，您必须是连接到CDB根的普通用户，并且表或视图必须存在于根和所有PDB中。该查询返回 CDB 根和所有打开的 PDB 中的表或视图中的所有行。 -
To query data in an application container, you must be a common user connected to the application root, and the table or view must exist in the application root and all PDBs in the application container. The query returns all rows from the table or view in the application root and in all open PDBs in the application container.
要查询应用程序容器中的数据，您必须是连接到应用程序根目录的普通用户，并且表或视图必须存在于应用程序根目录和应用程序容器中的所有PDB中。该查询返回应用程序根目录以及应用程序容器中所有打开的 PDB 中的表或视图中的所有行。
The table or view must be in your own schema. It is not necessary to specify schema, but if you do then you must specify your own schema.
表或视图必须位于您自己的架构中。不必指定 schema ，但如果指定，则必须指定您自己的架构。
The query returns all rows from the table or view in the root and in all open PDBs, except PDBs that are open in RESTRICTED mode. If the queried table or view does not already contain a CON_ID column, then the query adds a CON_ID column to the query result, which identifies the container whose data a given row represents.
该查询返回根和所有打开的 PDB 中表或视图的所有行，以 RESTRICTED 模式打开的 PDB 除外。如果查询的表或视图尚不包含 CON_ID 列，则查询会向查询结果添加 CON_ID 列，该列标识给定行代表其数据的容器。
See Also: 也可以看看：
-
Oracle Database Administrator's Guide for more information on the
CONTAINERSclause
Oracle 数据库管理员指南 了解有关CONTAINERS子句的更多信息
shards_clause 分片子句
Use the shards_clause to query Oracle supplied objects such as V$, DBA/USER/ALL views, and dictionary tables across shards. You can execute a query with the shards_clause only on the shard catalog database.
使用 shards_clause 跨分片查询 Oracle 提供的对象，例如 V$ 、 DBA/USER/ALL 视图和字典表。您只能在分片目录数据库上使用 shards_clause 执行查询。
This feature enables easier centralized management by providing the ability to execute queries across all shards from a central shard catalog.
此功能提供从中央分片目录跨所有分片执行查询的能力，从而实现更轻松的集中管理。
join_clause 加入子句
Use the appropriate join_clause syntax to identify tables that are part of a join from which to select data. The inner_cross_join_clause lets you specify an inner or cross join. The outer_join_clause lets you specify an outer join. The cross_outer_apply_clause lets you specify a variation of an ANSI CROSS JOIN or an ANSI LEFT OUTER JOIN with left correlation support.
使用适当的 join_clause 语法来标识属于要从中选择数据的联接的一部分的表。 inner_cross_join_clause 允许您指定内部连接或交叉连接。 outer_join_clause 允许您指定外部联接。 cross_outer_apply_clause 允许您指定 ANSI CROSS JOIN 或 ANSI LEFT OUTER JOIN 具有左相关支持。
When you join more than two row sources, you can use parentheses to override default precedence. For example, the following syntax:
当连接两个以上的行源时，可以使用括号来覆盖默认优先级。例如，以下语法：
SELECT ... FROM a JOIN (b JOIN c) ...
results in a join of b and c, and then a join of that result set with a.
导致 b 和 c 的联接，然后该结果集与 a 的联接。
See Also: 也可以看看：
"Joins" for more information on joins, "Using Join Queries: Examples", "Using Self Joins: Example", and "Using Outer Joins: Examples"
有关连接的详细信息，请参阅“连接”、“使用连接查询：示例”、“使用自连接：示例”和“使用外部连接：示例”
inner_cross_join_clause 内部交叉连接子句
Inner joins return only those rows that satisfy the join condition.
内连接仅返回那些满足连接条件的行。
INNER 内
Specify INNER to explicitly specify an inner join.
指定 INNER 以显式指定内部联接。
JOIN 加入
The JOIN keyword explicitly states that a join is being performed. You can use this syntax to replace the comma-delimited table expressions used in WHERE clause joins with FROM clause join syntax.
JOIN 关键字明确指出正在执行联接。您可以使用此语法将 WHERE 子句连接中使用的逗号分隔表表达式替换为 FROM 子句连接语法。
ON condition 开启状态
Use the ON clause to specify a join condition. Doing so lets you specify join conditions separate from any search or filter conditions in the WHERE clause.
使用 ON 子句指定连接条件。这样做可以让您指定与 WHERE 子句中的任何搜索或过滤条件分开的连接条件。
USING (column) 使用（列）
When you are specifying an equijoin of columns that have the same name in both tables, the USING column clause indicates the columns to be used. You can use this clause only if the join columns in both tables have the same name. Within this clause, do not qualify the column name with a table name or table alias.
当您指定两个表中具有相同名称的列的等值连接时， USING column 子句指示要使用的列。仅当两个表中的联接列具有相同名称时才可以使用此子句。在此子句中，不要使用表名或表别名来限定列名。
The CROSS keyword indicates that a cross join is being performed. A cross join produces the cross-product of two relations and is essentially the same as the comma-delimited Oracle Database notation.
CROSS 关键字表示正在执行交叉连接。交叉联接生成两个关系的叉积，本质上与逗号分隔的 Oracle 数据库表示法相同。
NATURAL 自然的
The NATURAL keyword indicates that a natural join is being performed. Refer to NATURAL for the full semantics of this clause.
NATURAL 关键字表示正在执行自然连接。有关此子句的完整语义，请参阅 NATURAL。
outer_join_clause 外连接子句
Outer joins return all rows that satisfy the join condition and also return some or all of those rows from one table for which no rows from the other satisfy the join condition. You can specify two types of outer joins: a conventional outer join using the table_reference syntax on both sides of the join, or a partitioned outer join using the query_partition_clause on one side or the other. A partitioned outer join is similar to a conventional outer join except that the join takes place between the outer table and each partition of the inner table. This type of join lets you selectively make sparse data more dense along the dimensions of interest. This process is called data densification.
外连接返回满足连接条件的所有行，并且还返回一个表中另一表中不存在满足连接条件的行的部分或全部行。您可以指定两种类型的外连接：在连接两侧使用 table_reference 语法的传统外连接，或在一侧或另一侧使用 query_partition_clause 的分区外连接。分区外联接与传统外联接类似，只是联接发生在外表和内表的每个分区之间。这种类型的联接允许您有选择地使稀疏数据沿感兴趣的维度变得更加密集。这个过程称为数据致密化。
query_partition_clause 查询分区子句
The query_partition_clause lets you define a partitioned outer join. Such a join extends the conventional outer join syntax by applying the outer join to partitions returned by the query. Oracle Database creates a partition of rows for each expression you specify in the PARTITION BY clause. The rows in each query partition have same value for the PARTITION BY expression.
query_partition_clause 允许您定义分区外连接。这种连接通过将外连接应用于查询返回的分区来扩展传统的外连接语法。 Oracle 数据库为您在 PARTITION BY 子句中指定的每个表达式创建行分区。每个查询分区中的行对于 PARTITION BY 表达式具有相同的值。
The query_partition_clause can be on either side of the outer join. The result of a partitioned outer join is a UNION of the outer joins of each of the partitions in the partitioned result set and the table on the other side of the join. This type of result is useful for filling gaps in sparse data, which simplifies analytic calculations.
query_partition_clause 可以位于外连接的任一侧。分区外联接的结果是分区结果集中每个分区与联接另一侧的表的外联接的 UNION 。此类结果对于填补稀疏数据中的空白非常有用，从而简化了分析计算。
If you omit this clause, then the database treats the entire table expression—everything specified in table_reference—as a single partition, resulting in a conventional outer join.
如果省略此子句，则数据库会将整个表表达式（ table_reference 中指定的所有内容）视为单个分区，从而产生传统的外连接。
To use the query_partition_clause in an analytic function, use the upper branch of the syntax (without parentheses). To use this clause in a model query (in the model_column_clauses) or a partitioned outer join (in the outer_join_clause), use the lower branch of the syntax (with parentheses).
要在分析函数中使用 query_partition_clause ，请使用语法的上部分支（不带括号）。要在模型查询（在 model_column_clauses 中）或分区外联接（在 outer_join_clause 中）中使用此子句，请使用语法的下部分支（带括号）。
Restrictions on Partitioned Outer Joins
分区外连接的限制
Partitioned outer joins are subject to the following restrictions:
分区外连接受到以下限制：
-
You can specify the
query_partition_clauseon either the right or left side of the join, but not both.
您可以在连接的右侧或左侧指定query_partition_clause，但不能同时指定两者。 -
You cannot specify a
FULLpartitioned outer join.
您不能指定FULL分区外连接。 -
If you specify the
query_partition_clausein an outer join with anONclause, then you cannot specify a subquery in theONcondition.
如果在带有ON子句的外连接中指定query_partition_clause，则无法在ON条件中指定子查询。
See Also: 也可以看看：
"Using Partitioned Outer Joins: Examples"
“使用分区外连接：示例”
The NATURAL keyword indicates that a natural join is being performed. A natural join is based on all columns in the two tables that have the same name. It selects rows from the two tables that have equal values in the relevant columns. If two columns with the same name do not have compatible data types, then an error is raised. When specifying columns that are involved in the natural join, do not qualify the column name with a table name or table alias.
NATURAL 关键字表示正在执行自然连接。自然连接基于两个表中具有相同名称的所有列。它从两个表中选择相关列中具有相同值的行。如果具有相同名称的两列不具有兼容的数据类型，则会引发错误。指定自然连接中涉及的列时，请勿使用表名或表别名来限定列名。
On occasion, the table pairings in natural or cross joins may be ambiguous. For example, consider the following join syntax:
有时，自然连接或交叉连接中的表配对可能不明确。例如，考虑以下连接语法：
a NATURAL LEFT JOIN b LEFT JOIN c ON b.c1 = c.c1
This example can be interpreted in either of the following ways:
该示例可以用以下任一方式解释：
a NATURAL LEFT JOIN (b LEFT JOIN c ON b.c1 = c.c1) (a NATURAL LEFT JOIN b) LEFT JOIN c ON b.c1 = c.c1
To avoid this ambiguity, you can use parentheses to specify the pairings of joined tables. In the absence of such parentheses, the database uses left associativity, pairing the tables from left to right.
为了避免这种歧义，您可以使用括号来指定连接表的配对。如果没有此类括号，数据库将使用左关联性，将表从左到右配对。
Restriction on Natural Joins
对自然连接的限制
You cannot specify a LOB column, columns of ANYTYPE, ANYDATA, or ANYDATASET, or a collection column as part of a natural join.
您不能将 LOB 列、 ANYTYPE 、 ANYDATA 或 ANYDATASET 列或集合列指定为自然连接的一部分。
outer_join_type 外部连接类型
The outer_join_type indicates the kind of outer join being performed:
outer_join_type 指示正在执行的外连接类型：
-
Specify
RIGHTto indicate a right outer join.
指定RIGHT以指示右外连接。 -
Specify
LEFTto indicate a left outer join.
指定LEFT以指示左外连接。 -
Specify
FULLto indicate a full or two-sided outer join. In addition to the inner join, rows from both tables that have not been returned in the result of the inner join will be preserved and extended with nulls.
指定FULL以指示完整或两侧外连接。除了内部联接之外，两个表中未在内联接结果中返回的行将被保留并用空值扩展。 -
You can specify the optional
OUTERkeyword followingRIGHT,LEFT, orFULLto explicitly clarify that an outer join is being performed.
您可以在RIGHT、LEFT或FULL后面指定可选的OUTER关键字，以明确说明正在执行外连接。
ON condition 开启状态
Use the ON clause to specify a join condition. Doing so lets you specify join conditions separate from any search or filter conditions in the WHERE clause.
使用 ON 子句指定连接条件。这样做可以让您指定与 WHERE 子句中的任何搜索或过滤条件分开的连接条件。
Restriction on the ON condition Clause
ON条件限制条款
You cannot specify this clause with a NATURAL outer join.
您不能使用 NATURAL 外连接指定此子句。
USING column 使用栏
In an outer join with the USING clause, the query returns a single column that coalesces the two matching columns in the join. The coalesce function is as follows:
在带有 USING 子句的外连接中，查询返回合并连接中两个匹配列的单个列。合并函数如下：
COALESCE (a, b) = a if a NOT NULL, else b.
Therefore: 所以：
-
A left outer join returns all the common column values from the left table in the
FROMclause.
左外连接返回FROM子句中左表的所有公共列值。 -
A right outer join returns all the common column values from the right table in the
FROMclause.
右外连接返回FROM子句中右表中的所有公共列值。 -
A full outer join returns all the common column values from both joined tables.
完整外连接返回两个连接表中的所有公共列值。
Restriction on the USING column Clause
USING栏的限制条款
The USING column clause is subject to the following restrictions:
USING column 子句受以下限制：
-
Within this clause, do not qualify the column name with a table name or table alias.
在此子句中，不要使用表名或表别名来限定列名。 -
You cannot specify a LOB column or a collection column in the
USINGcolumnclause.
您不能在USINGcolumn子句中指定 LOB 列或集合列。 -
You cannot specify this clause with a
NATURALouter join.
您不能使用NATURAL外连接指定此子句。
See Also: 也可以看看：
-
"Outer Joins" for additional rules and restrictions pertaining to outer joins
“外连接”了解与外连接相关的附加规则和限制 -
Oracle Database Data Warehousing Guide for a complete discussion of partitioned outer joins and data densification
Oracle 数据库数据仓库指南，完整讨论分区外连接和数据致密化 -
"Using Outer Joins: Examples"
“使用外连接：示例”
cross_outer_apply_clause 交叉外层应用子句
This clause allows you to perform a variation of an ANSI CROSS JOIN or an ANSI LEFT OUTER JOIN with left correlation support. You can specify a table_reference or collection_expression to the right of the APPLY keyword. The table_reference can be a table, inline view, or TABLE collection expression. The collection_expression can be a subquery, a column, a function, or a collection constructor. Regardless of its form, it must return a collection value—that is, a value whose type is nested table or varray. The table_reference or collection_expression can reference columns of tables defined in the FROM clause to the left of the APPLY keyword. This is called left correlation.
此子句允许您执行 ANSI CROSS JOIN 或 ANSI LEFT OUTER JOIN 的变体左相关性支持。您可以在 APPLY 关键字右侧指定 table_reference 或 collection_expression 。 table_reference 可以是表、内联视图或 TABLE 集合表达式。 collection_expression 可以是子查询、列、函数或集合构造函数。无论其形式如何，它都必须返回一个集合值，即类型为嵌套表或变量数组的值。 table_reference 或 collection_expression 可以引用 APPLY 关键字左侧的 FROM 子句中定义的表列。这称为左相关。
-
Specify
CROSSAPPLYto perform a variation of an ANSICROSSJOIN. Only rows from the table on the left side of the join that produce a result set fromtable_referenceorcollection_expressionare returned.
指定CROSSAPPLY以执行 ANSICROSSJOIN的变体。仅返回连接左侧表中生成table_reference或collection_expression结果集的行。 -
Specify
OUTERAPPLYto perform a variation of an ANSILEFTOUTERJOIN. All rows from the table on the left side of the join are returned. Rows that do not produce a result set fromtable_referenceorcollection_expressionhave the NULL value in the corresponding column(s).
指定OUTERAPPLY以执行 ANSILEFTOUTERJOIN的变体。返回连接左侧表中的所有行。不从table_reference或collection_expression生成结果集的行在相应的列中具有 NULL 值。
Restriction on the cross_outer_apply_clause
cross_outer_apply_clause 的限制
The table_reference cannot be a lateral inline view.
table_reference 不能是横向内联视图。
inline_analytic_view 内联分析视图
An inline analytic view is a transitory analytic view that is specified in the FROM clause. To create an inline analytic view, use the ANALYTIC VIEW keyword and specify a subav_clause that defines the analytic view. Optionally, you may specify an inline_av_alias, which is an alias for the inline analytic view. The rules for the inline_av_alias are the same as the rules for an inline view alias.
内联分析视图是在 FROM 子句中指定的暂时分析视图。要创建内联分析视图，请使用 ANALYTIC VIEW 关键字并指定定义分析视图的 subav_clause 。或者，您可以指定 inline_av_alias ，它是内联分析视图的别名。 inline_av_alias 的规则与内联视图别名的规则相同。
See Also: 也可以看看：
where_clause where_子句
The WHERE condition lets you restrict the rows selected to those that satisfy one or more conditions. For condition, specify any valid SQL condition.
WHERE 条件允许您将所选行限制为满足一个或多个条件的行。对于 condition ，指定任何有效的 SQL 条件。
If you omit this clause, then the database returns all rows from the tables, views, or materialized views in the FROM clause.
如果省略此子句，则数据库将返回 FROM 子句中的表、视图或具体化视图中的所有行。
Note:
If this clause refers to a DATE column of a partitioned table or index, then the database performs partition pruning only if:
注意：如果此子句引用分区表或索引的
DATE 列，则数据库仅在以下情况下执行分区修剪：
-
You created the table or index partitions by fully specifying the year using the
TO_DATEfunction with a 4-digit format mask, and
您通过使用带有 4 位格式掩码的TO_DATE函数完全指定年份来创建表或索引分区，并且 -
You specify the date in the
where_clauseof the query using theTO_DATEfunction and either a 2- or 4-digit format mask.
您可以使用TO_DATE函数和 2 位或 4 位格式掩码在查询的where_clause中指定日期。
See Also: 也可以看看：
-
Conditions for the syntax description of
condition
condition语法描述的条件 -
"Selecting from a Partition: Example"
“从分区中选择：示例”
hierarchical_query_clause
分层查询子句
The hierarchical_query_clause lets you select rows in a hierarchical order.
hierarchical_query_clause 允许您按层次结构顺序选择行。
SELECT statements that contain hierarchical queries can contain the LEVEL pseudocolumn in the select list. LEVEL returns the value 1 for a root node, 2 for a child node of a root node, 3 for a grandchild, and so on. The number of levels returned by a hierarchical query may be limited by available user memory.
包含分层查询的 SELECT 语句可以在选择列表中包含 LEVEL 伪列。 LEVEL 对于根节点返回值 1，对于根节点的子节点返回值 2，对于孙节点返回值 3，依此类推。分层查询返回的级别数可能受到可用用户内存的限制。
Oracle processes hierarchical queries as follows:
Oracle 按如下方式处理分层查询：
-
A join, if present, is evaluated first, whether the join is specified in the
FROMclause or withWHEREclause predicates.
如果存在连接，则首先评估该连接，无论该连接是在FROM子句中指定还是在WHERE子句谓词中指定。 -
The
CONNECTBYcondition is evaluated.
评估CONNECTBY条件。 -
Any remaining
WHEREclause predicates are evaluated.
任何剩余的WHERE子句谓词都会被评估。
If you specify this clause, then do not specify either ORDER BY or GROUP BY, because they will destroy the hierarchical order of the CONNECT BY results. If you want to order rows of siblings of the same parent, then use the ORDER SIBLINGS BY clause.
如果指定此子句，则不要指定 ORDER BY 或 GROUP BY ，因为它们会破坏 CONNECT BY 结果。如果要对同一父级的同级行进行排序，请使用 ORDER SIBLINGS BY 子句。
See Also: 也可以看看：
"Hierarchical Queries" for a discussion of hierarchical queries and "Using the LEVEL Pseudocolumn: Examples"
“分层查询”讨论分层查询和“使用 LEVEL 伪列：示例”
START WITH Clause 从子句开始
Specify a condition that identifies the row(s) to be used as the root(s) of a hierarchical query. The condition can be any condition as described in Conditions. Oracle Database uses as root(s) all rows that satisfy this condition. If you omit this clause, then the database uses all rows in the table as root rows.
指定一个条件，用于标识要用作分层查询的根的行。 condition 可以是条件中描述的任何条件。 Oracle 数据库使用满足此条件的所有行作为根。如果省略此子句，则数据库将使用表中的所有行作为根行。
CONNECT BY Clause CONNECT BY 子句
Specify a condition that identifies the relationship between parent rows and child rows of the hierarchy. The condition can be any condition as described in Conditions. However, it must use the PRIOR operator to refer to the parent row.
指定标识层次结构的父行和子行之间关系的条件。 condition 可以是条件中描述的任何条件。但是，它必须使用 PRIOR 运算符来引用父行。
See Also: 也可以看看：
-
Pseudocolumns for more information on
LEVEL
有关LEVEL的更多信息的伪列 -
"Hierarchical Queries" for general information on hierarchical queries
“分层查询”了解分层查询的一般信息 -
"Hierarchical Query Examples"
《分层查询示例》
group_by_clause 分组依据子句
Specify the GROUP BY clause if you want the database to group the selected rows based on the value of expr(s) for each row and return a single row of summary information for each group. If this clause contains CUBE or ROLLUP extensions, then the database produces superaggregate groupings in addition to the regular groupings.
如果您希望数据库根据每行的 expr (s) 值对所选行进行分组并返回单行，请指定 GROUP BY 子句每个组的摘要信息。如果此子句包含 CUBE 或 ROLLUP 扩展，则数据库除了常规分组之外还会生成超级聚合分组。
Expressions in the GROUP BY clause can contain any columns of the tables, views, or materialized views in the FROM clause, regardless of whether the columns appear in the select list.
GROUP BY 子句中的表达式可以包含 FROM 子句中表、视图或物化视图的任何列，无论这些列是否出现在选择列表。
The GROUP BY clause groups rows but does not guarantee the order of the result set. To order the groupings, use the ORDER BY clause.
GROUP BY 子句对行进行分组，但不保证结果集的顺序。要对分组进行排序，请使用 ORDER BY 子句。
See Also: 也可以看看：
-
Oracle Database Data Warehousing Guide for an expanded discussion and examples of using SQL grouping syntax for data aggregation
Oracle 数据库数据仓库指南，提供使用 SQL 分组语法进行数据聚合的扩展讨论和示例 -
the GROUP_ID, GROUPING, and GROUPING_ID functions for examples
GROUP_ID、GROUPING 和 GROUPING_ID 函数的示例 -
"Using the GROUP BY Clause: Examples"
“使用 GROUP BY 子句：示例” -
Restrictions for Linguistic Collations for information on implications of how
GROUPBYcharacter values are compared linguistically
语言排序规则的限制，提供有关如何在语言上比较GROUPBY字符值的含义的信息 -
Appendix C in Oracle Database Globalization Support Guide for the collation determination rules for the expressions in the
GROUPBYclause
Oracle 数据库全球化支持指南中的附录 C 有关GROUPBY子句中表达式的排序规则确定规则
The ROLLUP operation in the simple_grouping_clause groups the selected rows based on the values of the first n, n-1, n-2, ... 0 expressions in the GROUP BY specification, and returns a single row of summary for each group. You can use the ROLLUP operation to produce subtotal values by using it with the SUM function. When used with SUM, ROLLUP generates subtotals from the most detailed level to the grand total. Aggregate functions such as COUNT can be used to produce other kinds of superaggregates.
simple_grouping_clause 中的 ROLLUP 操作根据 GROUP 规范，并返回每个组的单行摘要。您可以将 ROLLUP 运算与 SUM 函数结合使用来生成小计值。与 SUM 一起使用时， ROLLUP 生成从最详细级别到总计的小计。诸如 COUNT 之类的聚合函数可用于生成其他类型的超级聚合。
For example, given three expressions (n=3) in the ROLLUP clause of the simple_grouping_clause, the operation results in n+1 = 3+1 = 4 groupings.
例如，如果 simple_grouping_clause 的 ROLLUP 子句中有三个表达式 (n=3)，则运算结果为 n+1 = 3+1 = 4 个分组。
Rows grouped on the values of the first n expressions are called regular rows, and the others are called superaggregate rows.
根据第一个 n 表达式的值分组的行称为常规行，其他行称为超级聚合行。
See Also: 也可以看看：
Oracle Database Data Warehousing Guide for information on using ROLLUP with materialized views
Oracle 数据库数据仓库指南 有关使用 ROLLUP 与物化视图的信息
The CUBE operation in the simple_grouping_clause groups the selected rows based on the values of all possible combinations of expressions in the specification. It returns a single row of summary information for each group. You can use the CUBE operation to produce cross-tabulation values.
simple_grouping_clause 中的 CUBE 操作根据规范中所有可能的表达式组合的值对所选行进行分组。它返回每个组的单行摘要信息。您可以使用 CUBE 操作来生成交叉表值。
For example, given three expressions (n=3) in the CUBE clause of the simple_grouping_clause, the operation results in 2n = 23 = 8 groupings. Rows grouped on the values of n expressions are called regular rows, and the rest are called superaggregate rows.
例如，给定 simple_grouping_clause 的 CUBE 子句中的三个表达式 (n=3)，运算结果为 2 n = 2 3 表达式的值分组的行称为常规行，其余的称为超级聚合行。
See Also: 也可以看看：
-
Oracle Database Data Warehousing Guide for information on using
CUBEwith materialized views
Oracle 数据库数据仓库指南 有关使用CUBE与物化视图的信息 -
"Using the GROUP BY CUBE Clause: Example"
“使用 GROUP BY CUBE 子句：示例”
GROUPING SETS are a further extension of the GROUP BY clause that let you specify multiple groupings of data. Doing so facilitates efficient aggregation by pruning the aggregates you do not need. You specify just the desired groups, and the database does not need to perform the full set of aggregations generated by CUBE or ROLLUP. Oracle Database computes all groupings specified in the GROUPING SETS clause and combines the results of individual groupings with a UNION ALL operation. The UNION ALL means that the result set can include duplicate rows.
GROUPING SETS 是 GROUP BY 子句的进一步扩展，可让您指定多个数据分组。这样做可以通过修剪不需要的聚合来促进高效聚合。您只需指定所需的组，数据库不需要执行 CUBE 或 ROLLUP 生成的全套聚合。 Oracle 数据库计算 GROUPING SETS 子句中指定的所有分组，并将各个分组的结果与 UNION ALL 操作组合起来。 UNION ALL 表示结果集可以包含重复的行。
Within the GROUP BY clause, you can combine expressions in various ways:
在 GROUP BY 子句中，您可以通过多种方式组合表达式：
-
To specify composite columns, group columns within parentheses so that the database treats them as a unit while computing
ROLLUPorCUBEoperations.
要指定复合列，请将括号内的列分组，以便数据库在计算ROLLUP或CUBE操作时将它们视为一个单元。 -
To specify concatenated grouping sets, separate multiple grouping sets,
ROLLUP, andCUBEoperations with commas so that the database combines them into a singleGROUPBYclause. The result is a cross-product of groupings from each grouping set.
要指定串联分组集，请使用逗号分隔多个分组集、ROLLUP和CUBE操作，以便数据库将它们合并为单个GROUPBY
See Also: 也可以看看：
"Using the GROUPING SETS Clause: Example"
“使用 GROUPING SETS 子句：示例”
Use the HAVING clause to restrict the groups of returned rows to those groups for which the specified condition is TRUE. If you omit this clause, then the database returns summary rows for all groups.
使用 HAVING 子句将返回行的组限制为指定 condition 为 TRUE 的组。如果省略此子句，则数据库将返回所有组的汇总行。
Specify GROUP BY and HAVING after the where_clause and hierarchical_query_clause. If you specify both GROUP BY and HAVING, then they can appear in either order.
在 where_clause 和 hierarchical_query_clause 之后指定 GROUP BY 和 HAVING 。如果您同时指定 GROUP BY 和 HAVING ，则它们可以按任一顺序显示。
See Also: 也可以看看：
"Using the HAVING Condition: Example"
“使用 HAVING 条件：示例”
Restrictions on the GROUP BY Clause
GROUP BY 子句的限制
This clause is subject to the following restrictions:
本条款受以下限制：
-
You cannot specify LOB columns, nested tables, or varrays as part of
expr.
您不能将 LOB 列、嵌套表或变量指定为expr的一部分。 -
The expressions can be of any form except scalar subquery expressions.
表达式可以是除标量子查询表达式之外的任何形式。 -
If the
group_by_clausereferences any object type columns, then the query will not be parallelized.
如果group_by_clause引用任何对象类型列，则查询将不会并行化。
model_clause 模型子句
The model_clause lets you view selected rows as a multidimensional array and randomly access cells within that array. Using the model_clause, you can specify a series of cell assignments, referred to as rules, that invoke calculations on individual cells and ranges of cells. These rules operate on the results of a query and do not update any database tables.
model_clause 允许您将选定的行作为多维数组查看，并随机访问该数组中的单元格。使用 model_clause ，您可以指定一系列单元格分配（称为规则），这些分配对单个单元格和单元格区域调用计算。这些规则对查询结果进行操作，并且不会更新任何数据库表。
When using the model_clause in a query, the SELECT and ORDER BY clauses must refer only to those columns defined in the model_column_clauses.
在查询中使用 model_clause 时， SELECT 和 ORDER BY 子句必须仅引用 model_column_clauses 。
See Also: 也可以看看：
-
The syntax description of
exprin "About SQL Expressions" and the syntax description ofconditionin Conditions
“关于SQL表达式”中expr的语法说明和条件中condition的语法说明 -
Oracle Database Data Warehousing Guide for an expanded discussion and examples
Oracle 数据库数据仓库指南，提供扩展讨论和示例 -
"The MODEL clause: Examples"
“ MODEL 子句：示例”
main_model 主模型
The main_model clause defines how the selected rows will be viewed in a multidimensional array and what rules will operate on which cells in that array.
main_model 子句定义如何在多维数组中查看所选行以及对该数组中的哪些单元格进行操作的规则。
model_column_clauses 模型列子句
The model_column_clauses define and classify the columns of a query into three groups: partition columns, dimension columns, and measure columns. For expr, you can specify a column, constant, host variable, single-row function, aggregate function, or any expression involving them. If expr is a column, then the column alias (c_alias) is optional. If expr is not a column, then the column alias is required. If you specify a column alias, then you must use the alias to refer to the column in the model_rules_clause, SELECT list, and the query ORDER BY clauses.
model_column_clauses 定义查询列并将其分类为三组：分区列、维度列和度量列。对于 expr ，您可以指定列、常量、主变量、单行函数、聚合函数或涉及它们的任何表达式。如果 expr 是一列，则列别名 ( c_alias ) 是可选的。如果 expr 不是列，则需要列别名。如果指定列别名，则必须使用别名来引用 model_rules_clause 、 SELECT 列表和查询 ORDER BY 条款。
PARTITION BY 分区依据
The PARTITION BY clause specifies the columns that will be used to divide the selected rows into partitions based on the values of the specified columns.
PARTITION BY 子句指定将用于根据指定列的值将所选行划分为分区的列。
DIMENSION BY 维度依据
The DIMENSION BY clause specifies the columns that will identify a row within a partition. The values of the dimension columns, along with those of the partition columns, serve as array indexes to the measure columns within a row.
DIMENSION BY 子句指定将标识分区内的行的列。维度列的值以及分区列的值充当行内度量列的数组索引。
MEASURES 措施
The MEASURES clause identifies the columns on which the calculations can be performed. Measure columns in individual rows are treated like cells that you can reference, by specifying the values for the partition and dimension columns, and update.
MEASURES 子句标识可以执行计算的列。各个行中的度量列被视为可以通过指定分区和维度列的值来引用并更新的单元格。
cell_reference_options 单元格引用选项
Use the cell_reference_options clause to specify how null and absent values are treated in rules and how column uniqueness is constrained.
使用 cell_reference_options 子句指定规则中如何处理空值和缺失值以及如何限制列唯一性。
IGNORE NAV 忽略资产净值
When you specify IGNORE NAV, the database returns the following values for the null and absent values of the data type specified:
当您指定 IGNORE NAV 时，数据库将为指定数据类型的空值和缺失值返回以下值：
-
Zero for numeric data types
数值数据类型为零 -
01-JAN-2000 for datetime data types
日期时间数据类型为 2000 年 1 月 1 日 -
An empty string for character data types
字符数据类型的空字符串 -
Null for all other data types
对于所有其他数据类型均为 Null
KEEP NAV 保持导航
When you specify KEEP NAV, the database returns null for both null and absent cell values. KEEP NAV is the default.
当您指定 KEEP NAV 时，数据库将为 null 和缺失的单元格值返回 null。 KEEP NAV 是默认值。
UNIQUE SINGLE REFERENCE 独特的单一参考
When you specify UNIQUE SINGLE REFERENCE, the database checks only single-cell references on the right-hand side of the rule for uniqueness, not the entire query result set.
当您指定 UNIQUE SINGLE REFERENCE 时，数据库仅检查规则右侧的单单元格引用的唯一性，而不是整个查询结果放。
UNIQUE DIMENSION 独特的维度
When you specify UNIQUE DIMENSION, the database checks that the PARTITION BY and DIMENSION BY columns form a unique key to the query. UNIQUE DIMENSION is the default.
当您指定 UNIQUE DIMENSION 时，数据库会检查 PARTITION BY 和 DIMENSION BY DIMENSION 是默认值。
model_rules_clause 模型规则子句
Use the model_rules_clause to specify the cells to be updated, the rules for updating those cells, and optionally, how the rules are to be applied and processed.
使用 model_rules_clause 指定要更新的单元格、更新这些单元格的规则以及（可选）如何应用和处理规则。
Each rule represents an assignment and consists of a left-hand side and right-hand side. The left-hand side of the rule identifies the cells to be updated by the right-hand side of the rule. The right-hand side of the rule evaluates to the values to be assigned to the cells specified on the left-hand side of the rule.
每个规则代表一个分配并由左侧和右侧组成。规则的左侧标识规则右侧要更新的单元格。规则右侧的计算结果为要分配给规则左侧指定的单元格的值。
UPSERT ALL 全部更新
UPSERT ALL allows UPSERT behavior for a rule with both positional and symbolic references on the left-hand side of the rule. When evaluating an UPSERT ALL rule, Oracle performs the following steps to create a list of cell references to be upserted:
UPSERT ALL 允许在规则左侧同时具有位置和符号引用的规则的 UPSERT 行为。在评估 UPSERT ALL 规则时，Oracle 执行以下步骤来创建要更新插入的单元格引用列表：
-
Find the existing cells that satisfy all the symbolic predicates of the cell reference.
查找满足单元格引用的所有符号谓词的现有单元格。 -
Using just the dimensions that have symbolic references, find the distinct dimension value combinations of these cells.
仅使用具有符号引用的维度，找到这些单元格的不同维度值组合。 -
Perform a cross product of these value combinations with the dimension values specified by way of positional references.
对这些值组合与通过位置参考指定的尺寸值执行叉积。
Refer to Oracle Database Data Warehousing Guide for more information on the semantics of UPSERT ALL.
有关 UPSERT ALL 语义的更多信息，请参阅 Oracle 数据库数据仓库指南。
UPSERT 更新插入
When you specify UPSERT, the database applies the rules to those cells referenced on the left-hand side of the rule that exist in the multidimensional array, and inserts new rows for those that do not exist. UPSERT behavior applies only when positional referencing is used on the left-hand side and a single cell is referenced. UPSERT is the default. Refer to cell_assignment for more information on positional referencing and single-cell references.
当您指定 UPSERT 时，数据库会将规则应用于多维数组中存在的规则左侧引用的单元格，并为不存在的单元格插入新行。 UPSERT 行为仅在左侧使用位置引用并且引用单个单元格时适用。 UPSERT 是默认值。有关位置引用和单单元格引用的更多信息，请参阅 cell_assignment。
UPDATE and UPSERT can be specified for individual rules as well. When either UPDATE or UPSERT is specified for a specific rule, it takes precedence over the option specified in the RULES clause.
也可以为单个规则指定 UPDATE 和 UPSERT 。当为特定规则指定 UPDATE 或 UPSERT 时，它优先于 RULES 子句中指定的选项。
Note:
If an UPSERT ALL, UPSERT, or UPDATE rule does not contain the appropriate predicates, then the database may implicitly convert it to a different type of rule:
注意：如果
UPSERT ALL 、 UPSERT 或 UPDATE 规则不包含适当的谓词，则数据库可能会隐式转换它不同类型的规则：
-
If an
UPSERTrule contains an existential predicate, then the rule is treated as anUPDATErule.
如果UPSERT规则包含存在谓词，则该规则将被视为UPDATE规则。 -
An
UPSERTALLrule must have at least one existential predicate and one qualified predicate on its left side. If it has no existential predicate, then it is treated as anUPSERTrule. If it has no qualified predicate, then it is treated as anUPDATErule
UPSERTALL规则必须在其左侧至少有一个存在谓词和一个限定谓词。如果它没有存在谓词，则将其视为UPSERT规则。如果它没有限定谓词，则将其视为UPDATE规则
UPDATE 更新
When you specify UPDATE, the database applies the rules to those cells referenced on the left-hand side of the rule that exist in the multidimensional array. If the cells do not exist, then the assignment is ignored.
当您指定 UPDATE 时，数据库会将规则应用于多维数组中存在的规则左侧引用的单元格。如果单元格不存在，则忽略分配。
AUTOMATIC ORDER 自动下单
When you specify AUTOMATIC ORDER, the database evaluates the rules based on their dependency order. In this case, a cell can be assigned a value once only.
当您指定 AUTOMATIC ORDER 时，数据库将根据规则的依赖顺序评估规则。在这种情况下，一个单元格只能被赋值一次。
SEQUENTIAL ORDER 顺序
When you specify SEQUENTIAL ORDER, the database evaluates the rules in the order they appear. In this case, a cell can be assigned a value more than once. SEQUENTIAL ORDER is the default.
当您指定 SEQUENTIAL ORDER 时，数据库将按照规则出现的顺序评估规则。在这种情况下，一个单元格可以被多次赋值。 SEQUENTIAL ORDER 是默认值。
ITERATE ... [UNTIL] 迭代... [直到]
Use ITERATE ... [UNTIL] to specify the number of times to cycle through the rules and, optionally, an early termination condition. The parentheses around the UNTIL condition are optional.
使用 ITERATE ... [ UNTIL ] 指定循环规则的次数以及（可选）提前终止条件。 UNTIL 条件周围的括号是可选的。
When you specify ITERATE ... [UNTIL], rules are evaluated in the order in which they appear. Oracle Database returns an error if both AUTOMATIC ORDER and ITERATE ... [UNTIL] are specified in the model_rules_clause.
当您指定 ITERATE ... [ UNTIL ] 时，规则将按照它们出现的顺序进行评估。如果 model_rules_clause 中同时指定了 AUTOMATIC ORDER 和 ITERATE ... [UNTIL] ，Oracle 数据库将返回错误。
cell_assignment 单元格分配
The cell_assignment clause, which is the left-hand side of the rule, specifies one or more cells to be updated. When a cell_assignment references a single cell, it is called a single-cell reference. When more than one cell is referenced, it is called a multiple-cell reference.
cell_assignment 子句位于规则的左侧，指定要更新的一个或多个单元格。当 cell_assignment 引用单个单元格时，称为单单元格引用。当引用多个单元格时，称为多单元格引用。
All dimension columns defined in the model_clause must be qualified in the cell_assignment clause. A dimension can be qualified using either symbolic or positional referencing.
model_clause 中定义的所有维度列都必须在 cell_assignment 子句中进行限定。可以使用符号或位置引用来限定尺寸。
A symbolic reference qualifies a single dimension column using a Boolean condition like dimension_column=constant. A positional reference is one where the dimension column is implied by its position in the DIMENSION BY clause. The only difference between symbolic references and positional references is in the treatment of nulls.
符号引用使用布尔条件（例如 dimension_column = constant 来限定单个维度列。位置引用是一种其中维度列由其在 DIMENSION BY 子句中的位置隐含的引用。符号引用和位置引用之间的唯一区别在于对空值的处理。
Using a single-cell symbolic reference such as a[x=null,y=2000], no cells qualify because x=null evaluates to FALSE. However, using a single-cell positional reference such as a[null,2000], a cell where x is null and y is 2000 qualifies because null = null evaluates to TRUE. With single-cell positional referencing, you can reference, update, and insert cells where dimension columns are null.
使用单单元格符号引用（例如 a[x=null,y=2000] ），没有单元格符合条件，因为 x=null 计算结果为 FALSE 。但是，使用单单元格位置引用（例如 a[null,2000] ），其中 x 为 null 且 y 为 2000 的单元格符合条件，因为 null = null 计算结果为 TRUE 。通过单单元格位置引用，您可以引用、更新和插入维度列为空的单元格。
You can specify a condition or an expression representing a dimension column value using either symbolic or positional referencing. condition cannot contain aggregate functions or the CV function, and condition must reference a single dimension column. expr cannot contain a subquery. Refer to "Model Expressions" for information on model expressions.
您可以使用符号或位置引用来指定表示维度列值的条件或表达式。 condition 不能包含聚合函数或 CV 函数，并且 condition 必须引用单个维度列。 expr 不能包含子查询。有关模型表达式的信息，请参阅“模型表达式”。
single_column_for_loop 单列for_循环
The single_column_for_loop clause lets you specify a range of cells to be updated within a single dimension column.
single_column_for_loop 子句允许您指定要在单个维度列中更新的单元格范围。
The IN clause lets you specify the values of the dimension column as either a list of values or as a subquery. When using subquery, it cannot:
IN 子句允许您将维度列的值指定为值列表或子查询。当使用 subquery 时，它不能：
-
Be a correlated query
是一个相关查询 -
Return more than 10,000 rows
返回超过 10,000 行 -
Be a query defined in the
WITHclause
是WITH子句中定义的查询
The FROM clause lets you specify a range of values for a dimension column with discrete increments within the range. The FROM clause can only be used for those columns with a data type for which addition and subtraction is supported. The INCREMENT and DECREMENT values must be positive.
FROM 子句允许您指定维度列的值范围，并在该范围内离散增量。 FROM 子句只能用于那些数据类型支持加法和减法的列。 INCREMENT 和 DECREMENT 值必须为正数。
Optionally, you can specify the LIKE clause within the FROM clause. In the LIKE clause, pattern is a character string containing a single pattern-matching character %. This character is replaced during execution with the current incremented or decremented value in the FROM clause.
或者，您可以在 FROM 子句中指定 LIKE 子句。在 LIKE 子句中， pattern 是包含单个模式匹配字符 % 的字符串。该字符在执行期间被替换为 FROM 子句中当前递增或递减的值。
If all dimensions other than those used by a FOR loop involve a single-cell reference, then the expressions can insert new rows. The number of dimension value combinations generated by FOR loops is counted as part of the 10,000 row limit of the MODEL clause.
如果除 FOR 循环使用的维度以外的所有维度都涉及单单元格引用，则表达式可以插入新行。 FOR 循环生成的维度值组合的数量计为 MODEL 子句的 10,000 行限制的一部分。
multi_column_for_loop 多列for_循环
The multi_column_for_loop clause lets you specify a range of cells to be updated across multiple dimension columns. The IN clause lets you specify the values of the dimension columns as either multiple lists of values or as a subquery. When using subquery, it cannot:
multi_column_for_loop 子句允许您指定要跨多个维度列更新的单元格范围。 IN 子句允许您将维度列的值指定为多个值列表或子查询。当使用 subquery 时，它不能：
-
Be a correlated query
是一个相关查询 -
Return more than 10,000 rows
返回超过 10,000 行 -
Be a query defined in the
WITHclause
是WITH子句中定义的查询
If all dimensions other than those used by a FOR loop involve a single-cell reference, then the expressions can insert new rows. The number of dimension value combinations generated by FOR loops is counted as part of the 10,000 row limit of the MODEL clause.
如果除 FOR 循环使用的维度以外的所有维度都涉及单单元格引用，则表达式可以插入新行。 FOR 循环生成的维度值组合的数量计为 MODEL 子句的 10,000 行限制的一部分。
See Also: 也可以看看：
Oracle Database Data Warehousing Guide for more information about using FOR loops in the MODEL clause
Oracle 数据库数据仓库指南，了解有关在 MODEL 子句中使用 FOR 循环的更多信息
order_by_clause
Use the ORDER BY clause to specify the order in which cells on the left-hand side of the rule are to be evaluated. The expr must resolve to a dimension or measure column. If the ORDER BY clause is not specified, then the order defaults to the order of the columns as specified in the DIMENSION BY clause. See order_by_clause for more information.
Restrictions on the order_by_clause
Use of the ORDER BY clause in the model rule is subject to the following restrictions:
-
You cannot specify
SIBLINGS,position, orc_aliasin theorder_by_clauseof themodel_clause. -
You cannot specify this clause on the left-hand side of the model rule and also specify a
FORloop on the right-hand side of the rule.
expr
Specify an expression representing the value or values of the cell or cells specified on the right-hand side of the rule. expr cannot contain a subquery. Refer to "Model Expressions" for information on model expressions.
return_rows_clause
The return_rows_clause lets you specify whether to return all rows selected or only those rows updated by the model rules. ALL is the default.
reference_model
Use the reference_model clause when you need to access multiple arrays from inside the model_clause. This clause defines a read-only multidimensional array based on the results of a query.
当您需要从 model_clause 内部访问多个数组时，请使用 reference_model 子句。该子句根据查询结果定义一个只读多维数组。
The subclauses of the reference_model clause have the same semantics as for the main_model clause. Refer to model_column_clauses and cell_reference_options.
reference_model 子句的子句与 main_model 子句具有相同的语义。请参阅 model_column_clauses 和 cell_reference_options。
Restrictions on the reference_model Clause
对reference_modelClause的限制
This clause is subject to the following restrictions:
本条款受以下限制：
-
PARTITIONBYcolumns cannot be specified for reference models.
无法为参考模型指定PARTITIONBY列。 -
The subquery of the reference model cannot refer to columns in an outer subquery.
参考模型的子查询不能引用外部子查询中的列。
Set Operators: UNION, UNION ALL, INTERSECT, MINUS
集合运算符：UNION、UNION ALL、INTERSECT、MINUS
The set operators combine the rows returned by two SELECT statements into a single result. The number and data types of the columns selected by each component query must be the same, but the column lengths can be different. The names of the columns in the result set are the names of the expressions in the select list preceding the set operator.
集合运算符将两个 SELECT 语句返回的行组合成一个结果。每个组件查询选择的列数和数据类型必须相同，但列长度可以不同。结果集中的列名称是选择列表中集合运算符之前的表达式的名称。
If you combine more than two queries with set operators, then the database evaluates adjacent queries from left to right. The parentheses around the subquery are optional. You can use them to specify a different order of evaluation.
如果使用集合运算符组合两个以上的查询，则数据库将从左到右评估相邻的查询。子查询周围的括号是可选的。您可以使用它们来指定不同的评估顺序。
Refer to "The UNION [ALL], INTERSECT, MINUS Operators" for information on these operators, including restrictions on their use.
有关这些运算符的信息，包括其使用限制，请参阅“UNION [ALL]、INTERSECT、MINUS 运算符”。
order_by_clause 按子句排序
Use the ORDER BY clause to order rows returned by the statement. Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.
使用 ORDER BY 子句对语句返回的行进行排序。如果没有 order_by_clause ，则无法保证多次执行的同一查询将以相同的顺序检索行。
The SIBLINGS keyword is valid only if you also specify the hierarchical_query_clause (CONNECT BY). ORDER SIBLINGS BY preserves any ordering specified in the hierarchical query clause and then applies the order_by_clause to the siblings of the hierarchy.
仅当您还指定 hierarchical_query_clause ( CONNECT BY ) 时， SIBLINGS 关键字才有效。 ORDER SIBLINGS BY 保留分层查询子句中指定的任何顺序，然后将 order_by_clause 应用于层次结构的同级。
expr 表达式
expr orders rows based on their value for expr. The expression is based on columns in the select list or columns in the tables, views, or materialized views in the FROM clause.
expr 根据 expr 的值对行进行排序。该表达式基于选择列表中的列或 FROM 子句中的表、视图或具体化视图中的列。
position 位置
Specify position to order rows based on their value for the expression in this position of the select list. The position value must be an integer.
指定 position 以根据选择列表中此位置的表达式的值对行进行排序。 position 值必须是整数。
You can specify multiple expressions in the order_by_clause. Oracle Database first sorts rows based on their values for the first expression. Rows with the same value for the first expression are then sorted based on their values for the second expression, and so on. The database sorts nulls following all others in ascending order and preceding all others in descending order. Refer to "Sorting Query Results" for a discussion of ordering query results.
您可以在 order_by_clause 中指定多个表达式。 Oracle 数据库首先根据第一个表达式的值对行进行排序。然后，第一个表达式具有相同值的行将根据第二个表达式的值进行排序，依此类推。数据库将空值按升序排列在所有其他值之后，并按降序排列在所有其他值之前。有关对查询结果排序的讨论，请参阅“对查询结果进行排序”。
ASC | DESC
Specify whether the ordering sequence is ascending or descending. ASC is the default.
指定排序顺序是升序还是降序。 ASC 是默认值。
NULLS FIRST | NULLS LAST
首先为空 |最后为空
Specify whether returned rows containing null values should appear first or last in the ordering sequence.
指定返回的包含空值的行应出现在排序序列中的第一个还是最后一个。
NULLS LAST is the default for ascending order, and NULLS FIRST is the default for descending order.
NULLS LAST 默认为升序， NULLS FIRST 默认为降序。
Restrictions on the ORDER BY Clause
ORDER BY 子句的限制
The following restrictions apply to the ORDER BY clause:
以下限制适用于 ORDER BY 子句：
-
If you have specified the
DISTINCToperator in this statement, then this clause cannot refer to columns unless they appear in the select list.
如果您在此语句中指定了DISTINCT运算符，则该子句无法引用列，除非它们出现在选择列表中。 -
An
order_by_clausecan contain no more than 255 expressions.
order_by_clause最多可以包含 255 个表达式。 -
You cannot order by a LOB,
LONG, orLONGRAWcolumn, nested table, or varray.
您不能按 LOB、LONG或LONGRAW列、嵌套表或 varray 进行排序。 -
If you specify a group_by_clause in the same statement, then this
order_by_clauseis restricted to the following expressions:
如果您在同一语句中指定 group_by_clause，则此order_by_clause仅限于以下表达式：-
Constants 常数
-
Aggregate functions 聚合函数
-
Analytic functions 解析函数
-
The functions
USER,UID, andSYSDATE
函数USER、UID和SYSDATE -
Expressions identical to those in the
group_by_clause
与group_by_clause中的表达式相同 -
Expressions comprising the preceding expressions that evaluate to the same value for all rows in a group
包含前面表达式的表达式，对于组中的所有行计算结果为相同值
-
See Also: 也可以看看：
-
"Using the ORDER BY Clause: Examples"
“使用 ORDER BY 子句：示例” -
Restrictions for Linguistic Collations for information on implications of how
ORDERBYcharacter values are compared linguistically
语言排序规则的限制，提供有关如何在语言上比较ORDERBY字符值的含义的信息 -
Appendix C in Oracle Database Globalization Support Guide for the collation determination rules for the expressions in the
ORDERBYclause
Oracle 数据库全球化支持指南中的附录 C 有关ORDERBY子句中表达式的排序规则确定规则
row_limiting_clause 行限制子句
The row_limiting_clause allows you to limit the rows returned by the query. You can specify an offset, and the number of rows or percentage of rows to return. You can use this clause to implement top-N reporting. For consistent results, specify the order_by_clause to ensure a deterministic sort order.
row_limiting_clause 允许您限制查询返回的行。您可以指定偏移量以及要返回的行数或行百分比。您可以使用此子句来实现 top-N 报告。为了获得一致的结果，请指定 order_by_clause 以确保确定的排序顺序。
OFFSET 抵消
Use this clause to specify the number of rows to skip before row limiting begins. offset must be a number or an expression that evaluates to a numeric value. If you specify a negative number, then offset is treated as 0. If you specify NULL, or a number greater than or equal to the number of rows returned by the query, then 0 rows are returned. If offset includes a fraction, then the fractional portion is truncated. If you do not specify this clause, then offset is 0 and row limiting begins with the first row.
使用此子句指定在行限制开始之前要跳过的行数。 offset 必须是数字或计算结果为数值的表达式。如果指定负数，则 offset 将被视为 0。如果指定 NULL 或大于或等于查询返回的行数的数字，则返回 0 行。如果 offset 包含小数，则小数部分将被截断。如果不指定此子句，则 offset 为 0，并且行限制从第一行开始。
ROW | ROWS 行 |行列式
These keywords can be used interchangeably and are provided for semantic clarity.
这些关键字可以互换使用，并且是为了语义清晰而提供的。
FETCH 拿来
Use this clause to specify the number of rows or percentage of rows to return. If you do not specify this clause, then all rows are returned, beginning at row offset + 1.
使用此子句指定要返回的行数或行百分比。如果不指定此子句，则返回从行 offset + 1 开始的所有行。
FIRST | NEXT 第一 |下一个
These keywords can be used interchangeably and are provided for semantic clarity.
这些关键字可以互换使用，并且是为了语义清晰而提供的。
rowcount | percent PERCENT 行数|百分比PERCENT
Use rowcount to specify the number of rows to return. rowcount must be a number or an expression that evaluates to a numeric value. If you specify a negative number, then rowcount is treated as 0. If rowcount is greater than the number of rows available beginning at row offset + 1, then all available rows are returned. If rowcount includes a fraction, then the fractional portion is truncated. If rowcount is NULL, then 0 rows are returned.
使用 rowcount 指定要返回的行数。 rowcount 必须是数字或计算结果为数值的表达式。如果指定负数，则 rowcount 被视为 0。如果 rowcount 大于从行 offset 开始的可用行数 + 1，则返回所有可用行。如果 rowcount 包含小数，则小数部分将被截断。如果 rowcount 为 NULL，则返回 0 行。
Use percent PERCENT to specify the percentage of the total number of selected rows to return. percent must be a number or an expression that evaluates to a numeric value. If you specify a negative number, then percent is treated as 0. If percent is NULL, then 0 rows are returned.
使用 percent PERCENT 指定要返回的选定行总数的百分比。 percent 必须是数字或计算结果为数值的表达式。如果指定负数，则 percent 被视为 0。如果 percent 为 NULL，则返回 0 行。
If you do not specify rowcount or percent PERCENT, then 1 row is returned.
如果您未指定 rowcount 或 percent PERCENT ，则返回 1 行。
ROW | ROWS 行 |行列式
These keywords can be used interchangeably and are provided for semantic clarity.
这些关键字可以互换使用，并且是为了语义清晰而提供的。
ONLY | WITH TIES 仅|有领带
Specify ONLY to return exactly the specified number of rows or percentage of rows.
指定 ONLY 以准确返回指定的行数或行百分比。
Specify WITH TIES to return additional rows with the same sort key as the last row fetched. If you specify WITH TIES, then you must specify the order_by_clause. If you do not specify the order_by_clause, then no additional rows will be returned.
指定 WITH TIES 以返回与最后提取的行具有相同排序键的其他行。如果指定 WITH TIES ，则必须指定 order_by_clause 。如果您不指定 order_by_clause ，则不会返回任何其他行。
Restrictions on the row_limiting_clause
row_limiting_clause 的限制
This clause is subject to the following restrictions:
本条款受以下限制：
-
You cannot specify this clause with the
for_update_clause.
您不能使用for_update_clause指定此子句。 -
If you specify this clause, then the select list cannot contain the sequence pseudocolumns
CURRVALorNEXTVAL.
如果指定此子句，则选择列表不能包含序列伪列CURRVAL或NEXTVAL。 -
Materialized views are not eligible for an incremental refresh if the defining query contains the
row_limiting_clause.
如果定义查询包含row_limiting_clause，则物化视图不符合增量刷新的条件。 -
If the select list contains columns with identical names and you specify the
row_limiting_clause, then an ORA-00918 error occurs. This error occurs whether the identically named columns are in the same table or in different tables. You can work around this issue by specifying unique column aliases for the identically named columns.
如果选择列表包含具有相同名称的列并且您指定了row_limiting_clause，则会出现 ORA-00918 错误。无论同名列位于同一个表还是不同表中，都会发生此错误。您可以通过为同名列指定唯一的列别名来解决此问题。
See Also: 也可以看看：
"Row Limiting: Examples"
“行限制：示例”
for_update_clause for_update_子句
The FOR UPDATE clause lets you lock the selected rows so that other users cannot lock or update the rows until you end your transaction. You can specify this clause only in a top-level SELECT statement, not in subqueries.
FOR UPDATE 子句允许您锁定选定的行，以便其他用户在您结束事务之前无法锁定或更新这些行。您只能在顶级 SELECT 语句中指定此子句，而不能在子查询中指定。
Note:
Prior to updating a LOB value, you must lock the row containing the LOB. One way to lock the row is with an embedded SELECT ... FOR UPDATE statement. You can do this using one of the programmatic languages or DBMS_LOB package. For more information on lock rows before writing to a LOB, see Oracle Database SecureFiles and Large Objects Developer's Guide.
注意：在更新 LOB 值之前，必须锁定包含 LOB 的行。锁定行的一种方法是使用嵌入的
SELECT ... FOR UPDATE 语句。您可以使用一种编程语言或 DBMS_LOB 包来执行此操作。有关写入 LOB 之前锁定行的更多信息，请参阅 Oracle Database SecureFiles 和大型对象开发人员指南。
Nested table rows are not locked as a result of locking the parent table rows. If you want the nested table rows to be locked, then you must lock them explicitly.
嵌套表行不会因锁定父表行而被锁定。如果您希望锁定嵌套表行，则必须显式锁定它们。
Restrictions on the FOR UPDATE Clause
FOR UPDATE 子句的限制
This clause is subject to the following restrictions:
本条款受以下限制：
-
You cannot specify this clause with the following other constructs: the
DISTINCToperator,CURSORexpression, set operators,group_by_clause, or aggregate functions.
您不能使用以下其他构造指定此子句：DISTINCT运算符、CURSOR表达式、集合运算符、group_by_clause或聚合函数。 -
The tables locked by this clause must all be located on the same database and on the same database as any
LONGcolumns and sequences referenced in the same statement.
此子句锁定的表必须全部位于同一数据库上，并且与同一语句中引用的任何LONG列和序列位于同一数据库上。
See Also: 也可以看看：
"Using the FOR UPDATE Clause: Examples"
“使用 FOR UPDATE 子句：示例”
Using the FOR UPDATE Clause on Views
在视图上使用 FOR UPDATE 子句
In general, this clause is not supported on views. However, in some cases, a SELECT ... FOR UPDATE query on a view can succeed without any errors. This occurs when the view has been merged to its containing query block internally by the query optimizer, and SELECT ... FOR UPDATE succeeds on the internally transformed query. The examples in this section illustrate when using the FOR UPDATE clause on a view can succeed or fail.
一般来说，该条款不受观点支持。但是，在某些情况下，对视图的 SELECT ... FOR UPDATE 查询可以成功且不会出现任何错误。当查询优化器已将视图内部合并到其包含的查询块，并且 SELECT ... FOR UPDATE 在内部转换的查询上成功时，就会发生这种情况。本节中的示例说明了在视图上使用 FOR UPDATE 子句时可能成功或失败。
-
Using the
FORUPDATEclause on merged views
在合并视图上使用FORUPDATE子句An error can occur when you use the
FORUPDATEclause on a merged view if both of the following conditions apply:
如果同时满足以下两个条件，则在合并视图上使用FORUPDATE子句时可能会发生错误：-
The underlying column of the view is an expression
视图的基础列是一个表达式 -
The
FORUPDATEclause applies to a column list
FORUPDATE子句适用于列列表
The following statement succeeds because the underlying column of the view is not an expression:
以下语句会成功，因为视图的基础列不是表达式：SELECT employee_id FROM (SELECT * FROM employees) FOR UPDATE OF employee_id;The following statement succeeds because, while the underlying column of the view is an expression, the
FORUPDATEclause does not apply to a column list:
以下语句会成功，因为虽然视图的基础列是表达式，但FORUPDATE子句不适用于列列表：SELECT employee_id FROM (SELECT employee_id+1 AS employee_id FROM employees) FOR UPDATE;The following statement fails because the underlying column of the view is an expression and the
FORUPDATEclause applies to a column list:
以下语句失败，因为视图的基础列是表达式，并且FORUPDATE子句应用于列列表：SELECT employee_id FROM (SELECT employee_id+1 AS employee_id FROM employees) FOR UPDATE OF employee_id; * Error at line 2: ORA-01733: virtual column not allowed here -
-
Using the
FORUPDATEclause on non-merged views
在非合并视图上使用FORUPDATE子句Since the
FORUPDATEclause is not supported on views, anything that prevents view merging, such as theNO_MERGEhint, parameters that disallow view merging, or something in the query structure that prevents view merging, will result in anORA-02014error.
由于视图不支持FORUPDATE子句，任何阻止视图合并的内容（例如NO_MERGE提示、禁止视图合并的参数或某些内容）阻止视图合并的查询结构将导致ORA-02014错误。In the following example, the
GROUPBYstatement prevents view merging, which causes an error:
在以下示例中，GROUPBY语句阻止视图合并，从而导致错误：SELECT avgsal FROM (SELECT AVG(salary) AS avgsal FROM employees GROUP BY job_id) FOR UPDATE; FROM (SELECT AVG(salary) AS avgsal FROM employees GROUP BY job_id) * ERROR at line 2: ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.
Note:
Due to the complexity of the view merging mechanism, Oracle recommends against using the FOR UPDATE clause on views.
注意：由于视图合并机制的复杂性，Oracle 建议不要在视图上使用
FOR UPDATE 子句。
OF ... column OF ... 列
Use the OF ... column clause to lock the select rows only for a particular table or view in a join. The columns in the OF clause only indicate which table or view rows are locked. The specific columns that you specify are not significant. However, you must specify an actual column name, not a column alias. If you omit this clause, then the database locks the selected rows from all the tables in the query.
使用 OF ... column 子句仅锁定联接中特定表或视图的选择行。 OF 子句中的列仅指示哪些表或视图行被锁定。您指定的特定列并不重要。但是，您必须指定实际的列名称，而不是列别名。如果省略此子句，则数据库将锁定查询中所有表中选定的行。
NOWAIT | WAIT 现在等等|等待
The NOWAIT and WAIT clauses let you tell the database how to proceed if the SELECT statement attempts to lock a row that is locked by another user.
NOWAIT 和 WAIT 子句可让您告诉数据库在 SELECT 语句尝试锁定已被其他用户锁定的行时如何继续。
-
Specify
NOWAITto return control to you immediately if a lock exists.
如果存在锁，请指定NOWAIT以立即将控制权返回给您。 -
Specify
WAITto instruct the database to waitintegerseconds for the row to become available and then return control to you.
指定WAIT以指示数据库等待integer秒以使该行变得可用，然后将控制权返回给您。
If you specify neither WAIT nor NOWAIT, then the database waits until the row is available and then returns the results of the SELECT statement.
如果您既不指定 WAIT 也不指定 NOWAIT ，则数据库将等待该行可用，然后返回 SELECT 语句的结果。
SKIP LOCKED 跳过锁定
SKIP LOCKED is an alternative way to handle a contending transaction that is locking some rows of interest. Specify SKIP LOCKED to instruct the database to attempt to lock the rows specified by the WHERE clause and to skip any rows that are found to be already locked by another transaction. This feature is designed for use in multiconsumer queue environments. It enables queue consumers to skip rows that are locked by other consumers and obtain unlocked rows without waiting for the other consumers to finish. Refer to Oracle Database Advanced Queuing User's Guide for more information.
SKIP LOCKED 是处理锁定某些感兴趣行的竞争事务的另一种方法。指定 SKIP LOCKED 指示数据库尝试锁定 WHERE 子句指定的行并跳过发现已被其他子句锁定的任何行交易。此功能设计用于多消费者队列环境。它使队列使用者能够跳过被其他使用者锁定的行并获取未锁定的行，而无需等待其他使用者完成。有关详细信息，请参阅 Oracle 数据库高级队列用户指南。
Note on the WAIT and SKIP LOCKED Clauses
关于 WAIT 和 SKIP LOCKED 子句的注释
If you specify WAIT or SKIP LOCKED and the table is locked in exclusive mode, then the database will not return the results of the SELECT statement until the lock on the table is released. In the case of WAIT, the SELECT FOR UPDATE clause is blocked regardless of the wait time specified.
如果指定 WAIT 或 SKIP LOCKED 并且表以独占模式锁定，那么数据库将不会返回 SELECT ，无论指定的等待时间如何， SELECT FOR UPDATE 子句都会被阻止。
row_pattern_clause 行模式子句
The MATCH_RECOGNIZE clause lets you perform pattern matching. Use this clause to recognize patterns in a sequence of rows in table, which is called the row pattern input table. The result of a query that uses the MATCH_RECOGNIZE clause is called the row pattern output table.
MATCH_RECOGNIZE 子句允许您执行模式匹配。使用此子句可以识别 table 中的行序列中的模式，该序列称为行模式输入表。使用 MATCH_RECOGNIZE 子句的查询结果称为行模式输出表。
The MATCH_RECOGNIZE enables you to do the following tasks:
MATCH_RECOGNIZE 使您能够执行以下任务：
-
Logically partition and order the data with the
PARTITIONBYandORDERBYclauses.
使用PARTITIONBY和ORDERBY子句对数据进行逻辑分区和排序。 -
Define measures, which are expressions usable in other parts of the SQL query, in the
MEASURESclause.
在MEASURES子句中定义度量，这些度量是可用于 SQL 查询其他部分的表达式。 -
Define patterns of rows to seek using the
PATTERNclause. These patterns use regular expression syntax, a powerful and expressive feature, applied to the pattern variables you define.
使用PATTERN子句定义要查找的行模式。这些模式使用正则表达式语法，这是一种强大且富有表现力的功能，应用于您定义的模式变量。 -
Specify the logical conditions required to map a row to a row pattern variable in the
DEFINEclause.
在DEFINE子句中指定将行映射到行模式变量所需的逻辑条件。
See Also: 也可以看看：
-
Oracle Database Data Warehousing Guide for more information on pattern matching
Oracle 数据库数据仓库指南 了解有关模式匹配的更多信息 -
"Row Pattern Matching: Example"
“行模式匹配：示例”
row_pattern_partition_by
Specify PARTITION BY to divide the rows in the row pattern input table into logical groups called row pattern partitions. Use column to specify one or more partitioning columns. Each partition consists of the set of rows in the row pattern input table that have the same value(s) on the partitioning column(s).
指定 PARTITION BY 将行模式输入表中的行划分为称为行模式分区的逻辑组。使用 column 指定一个或多个分区列。每个分区由行模式输入表中的行集组成，这些行在分区列上具有相同的值。
If you specify this clause, then matches are found within partitions and do not cross partition boundaries. If you do not specify this clause, then all rows of the row input table constitute a single row pattern partition.
如果指定此子句，则在分区内找到匹配项，并且不会跨越分区边界。如果不指定此子句，则行输入表的所有行构成单个行模式分区。
row_pattern_order_by
Specify ORDER BY to order rows within each row pattern partition. Use column to specify one or more ordering columns. If you specify multiple columns, then Oracle Database first sorts rows based on their values for the first column. Rows with the same value for the first column are then sorted based on their values for the second column, and so on. Oracle Database sorts nulls following all others in ascending order.
指定 ORDER BY 对每个行模式分区内的行进行排序。使用 column 指定一个或多个排序列。如果指定多列，则 Oracle 数据库首先根据第一列的值对行进行排序。然后，第一列具有相同值的行将根据第二列的值进行排序，依此类推。 Oracle 数据库按升序对所有其他值进行排序。
If you do not specify this clause, then the result of the row_pattern_clause is nondeterministic and you may get inconsistent results each time you run the query.
如果不指定此子句，则 row_pattern_clause 的结果是不确定的，并且每次运行查询时可能会得到不一致的结果。
row_pattern_measures 行模式测量
Use the MEASURES clause to define one or more row pattern measure columns. These columns are included in the row pattern output table and contain values that are useful for analyzing data.
使用 MEASURES 子句定义一个或多个行模式度量列。这些列包含在行模式输出表中，并包含对分析数据有用的值。
When you define a row pattern measure column, using the row_pattern_measure_column clause, you specify its pattern measure expression. The values in the column are calculated by evaluating the pattern measure expression whenever a match is found.
当您定义行模式度量列时，使用 row_pattern_measure_column 子句指定其模式度量表达式。只要找到匹配项，就会通过评估模式度量表达式来计算列中的值。
row_pattern_measure_column
行_模式_度量_列
Use this clause to define a row pattern measure column.
使用此子句定义行模式度量列。
-
For
expr, specify the pattern measure expression. A pattern measure expression is an expression as described in Expressions that can contain only the following elements:
对于expr，指定模式度量表达式。模式度量表达式是表达式中描述的表达式，只能包含以下元素：-
Constants: Text literals and numeric literals
常量：文本文字和数字文字 -
References to any column of the row pattern input table
对行模式输入表的任意列的引用 -
The
CLASSIFIERfunction, which returns the name of the primary row pattern variable to which the row is mapped. Refer to row_pattern_classifier_func for more information.
CLASSIFIER函数，返回行映射到的主行模式变量的名称。有关详细信息，请参阅 row_pattern_classifier_func。 -
The
MATCH_NUMBERfunction, which returns the sequential number of a row pattern match within the row pattern partition. Refer to row_pattern_match_num_func for more information.
MATCH_NUMBER函数，返回行模式分区内行模式匹配的序号。有关详细信息，请参阅 row_pattern_match_num_func。 -
Row pattern navigation functions:
PREV,NEXT,FIRST, andLAST. Refer to row_pattern_navigation_func for more information.
行模式导航函数：PREV、NEXT、FIRST和LAST。有关详细信息，请参阅 row_pattern_navigation_func。 -
Row pattern aggregate functions: AVG, COUNT, MAX, MIN, or SUM. Refer to row_pattern_aggregate_func for more information.
行模式聚合函数：AVG、COUNT、MAX、MIN 或 SUM。有关详细信息，请参阅 row_pattern_aggregate_func。
-
-
For
c_alias, specify the alias for the pattern measure expression. Oracle Database uses this alias in the column heading of the row pattern output table. TheASkeyword is optional. The alias can be used in other parts of the query, such as theSELECT...ORDERBYclause.
对于c_alias，指定模式度量表达式的别名。 Oracle 数据库在行模式输出表的列标题中使用此别名。AS关键字是可选的。别名可以用在查询的其他部分，例如SELECT...ORDERBY子句。
row_pattern_rows_per_match
This clause lets you specify whether the row pattern output table includes summary or detailed data about each match.
此子句允许您指定行模式输出表是否包含有关每个匹配的摘要数据或详细数据。
-
If you specify
ONEROWPERMATCH, then each match produces one summary row. This is the default.
如果您指定ONEROWPERMATCH，则每次匹配都会生成一个摘要行。这是默认设置。 -
If you specify
ALLROWSPERMATCH, then each match that spans multiple rows will produce one output row for each row in the match.
如果您指定ALLROWSPERMATCH，则跨越多行的每个匹配将为匹配中的每一行生成一个输出行。
row_pattern_skip_to
This clause lets you specify the point to resume row pattern matching after a non-empty match is found.
此子句允许您指定在找到非空匹配后恢复行模式匹配的点。
-
Specify
AFTERMATCHSKIPTONEXTROWto resume pattern matching at the row after the first row of the current match.
指定AFTERMATCHSKIPTONEXTROW以恢复该行的模式匹配在当前匹配的第一行之后。 -
Specify
AFTERMATCHSKIPPASTLASTROWto resume pattern matching at the next row after the last row of the current match. This is the default.
指定AFTERMATCHSKIPPASTLASTROW在下一个恢复模式匹配当前匹配的最后一行之后的行。这是默认设置。 -
Specify
AFTERMATCHSKIPTOFIRSTvariable_nameto resume pattern matching at the first row that is mapped to pattern variablevariable_name. Thevariable_namemust be defined in theDEFINEclause.
指定AFTERMATCHSKIPTOFIRSTvariable_name在第一次恢复模式匹配映射到模式变量variable_name的行。variable_name必须在DEFINE子句中定义。 -
Specify
AFTERMATCHSKIPTOLASTvariable_nameto resume pattern matching at the last row that is mapped to pattern variablevariable_name. Thevariable_namemust be defined in theDEFINEclause.
指定AFTERMATCHSKIPTOLASTvariable_name恢复最后一次的模式匹配映射到模式变量variable_name的行。variable_name必须在DEFINE子句中定义。 -
AFTERMATCHSKIPTOvariable_namehas the same behavior asAFTERMATCHSKIPTOLASTvariable_name.
AFTERMATCHSKIPTOvariable_name与AFTERMATCHSKIPTOLASTvariable_name。
See Also: 也可以看看：
Oracle Database Data Warehousing Guide for more information on the AFTER MATCH SKIP clauses
Oracle 数据库数据仓库指南，了解有关 AFTER MATCH SKIP 子句的更多信息
PATTERN 图案
Use the PATTERN clause to define which pattern variables must be matched, the sequence in which they must be matched, and the quantity of rows that must be matched for each pattern variable.
使用 PATTERN 子句定义必须匹配哪些模式变量、必须匹配的顺序以及每个模式变量必须匹配的行数。
A row pattern match consists of a set of contiguous rows in a row pattern partition. Each row of the match is mapped to a pattern variable. The mapping of rows to pattern variables must conform to the regular expression specified in the row_pattern clause, and all conditions in the DEFINE clause must be true.
行模式匹配由行模式分区中的一组连续行组成。匹配的每一行都映射到一个模式变量。行到模式变量的映射必须符合 row_pattern 子句中指定的正则表达式，并且 DEFINE 子句中的所有条件都必须为 true。
Note:
It is outside the scope of this document to explain regular expression concepts and details. If you are not familiar with regular expressions, then you are encouraged to familiarize yourself with the topic using other sources.
注意：解释正则表达式概念和细节超出了本文档的范围。如果您不熟悉正则表达式，那么我们鼓励您使用其他来源来熟悉该主题。
The precedence of the elements that you specify in the regular expression of the PATTERNS clause, in decreasing order, is as follows:
在 PATTERNS 子句的正则表达式中指定的元素的优先级（按降序排列）如下：
-
Row pattern elements (specified in the
row_pattern_primaryclause)
行模式元素（在row_pattern_primary子句中指定） -
Row pattern quantifiers (specified in the
row_pattern_quantifierclause)
行模式量词（在row_pattern_quantifier子句中指定） -
Concatenation (specified in the
row_pattern_termclause)
连接（在row_pattern_term子句中指定） -
Alternation (specified in the
row_patternclause)
交替（在row_pattern子句中指定）
See Also: 也可以看看：
Oracle Database Data Warehousing Guide for more information on the PATTERN clause
Oracle 数据库数据仓库指南，了解有关 PATTERN 子句的更多信息
row_pattern 行模式
Use this clause to specify the row pattern. A row pattern is a regular expression that can take one of the following forms:
使用此子句指定行模式。行模式是一种正则表达式，可以采用以下形式之一：
-
A single row pattern term
单行模式项For example:
PATTERN(A)例如：PATTERN(A) -
A row pattern, a vertical bar, and a row pattern term
行模式、竖线和行模式术语For example:
PATTERN(A|B)例如：PATTERN(A|B) -
A recursively built row pattern, a vertical bar, and a row pattern term
递归构建的行模式、垂直条和行模式项For example:
PATTERN(A|B|C)例如：PATTERN(A|B|C)
The vertical bar in this clause represents alternation. Alternation matches a single regular expression from a list of several possible regular expressions. Alternatives are preferred in the order they are specified. For example, if you specify PATTERN(A|B|C), then Oracle Database attempts to match A first. If A is not matched, then it attempts to match B. If B is not matched, then it attempts to match C.
本节中的竖线代表交替。交替匹配多个可能的正则表达式列表中的单个正则表达式。替代方案按照指定的顺序优先。例如，如果您指定 PATTERN(A|B|C) ，则 Oracle 数据库首先尝试匹配 A 。如果 A 不匹配，则会尝试匹配 B 。如果 B 不匹配，则会尝试匹配 C 。
row_pattern_term 行_模式_术语
This clause lets you specify a row pattern term. A row pattern term can take one of the following forms:
该子句允许您指定行模式术语。行模式项可以采用以下形式之一：
-
A single row pattern factor
单行模式因子For example:
PATTERN(A)例如：PATTERN(A) -
A row pattern term followed by a row pattern factor.
行模式项后跟行模式因子。For example:
PATTERN(A B)例如：PATTERN(A B) -
A recursively built row pattern term followed by a row pattern factor
递归构建的行模式项，后跟行模式因子For example:
PATTERN(A B C)例如：PATTERN(A B C)
The syntax used in the second and third examples represents concatenation. Concatenation is used to list two or more items in a pattern to be matched and the order in which they are to be matched. For example, if you specify PATTERN(A B C), then Oracle Database first matches A, then uses the resulting matched rows to match B, then uses the resulting matched rows to match C. Only rows that match A, B, and C, are included in the row pattern match.
第二个和第三个示例中使用的语法表示串联。连接用于列出要匹配的模式中的两个或多个项目以及它们的匹配顺序。例如，如果您指定 PATTERN(A B C) ，则 Oracle 数据库首先匹配 A ，然后使用生成的匹配行来匹配 B ，然后使用生成的匹配行来匹配匹配 C 。仅匹配 A 、 B 和 C 的行包含在行模式匹配中。
row_pattern_factor 行模式因子
This clause lets you specify a row pattern factor. A row pattern factor consists of a row pattern element, specified using the row_pattern_primary clause, and an optional row pattern quantifier, specified using the row_pattern_quantifier clause.
该子句允许您指定行模式因子。行模式因子由使用 row_pattern_primary 子句指定的行模式元素和使用 row_pattern_quantifier 子句指定的可选行模式量词组成。
row_pattern_primary 行模式主
Use this clause to specify the row pattern element. Table 19-1 lists the valid row pattern elements and their descriptions.
使用此子句指定行模式元素。表 19-1 列出了有效的行模式元素及其描述。
Table 19-1 Row Pattern Elements
表 19-1 行模式元素
| Row Pattern Element 行图案元素 | Description |
|---|---|
|
|
Specify a primary pattern variable name that is defined in the |
|
|
|
|
|
|
|
|
Use |
|
|
Exclusion syntax. Use |
|
|
Use |
row_pattern_permute 行模式排列
Use the PERMUTE clause to express a pattern that is a permutation of the specified row pattern elements. For example, PATTERN (PERMUTE (A, B, C)) is equivalent to an alternation of all permutations of the three row pattern elements A, B, and C, similar to the following:
使用 PERMUTE 子句来表达作为指定行模式元素的排列的模式。例如， PATTERN (PERMUTE (A, B, C)) 相当于三行的所有排列的交替模式元素 A 、 B 和 C ，类似于以下内容：
PATTERN (A B C | A C B | B A C | B C A | C A B | C B A)
Note that the row pattern elements are expanded lexicographically and that each element to permute must be separated by a comma from the other elements.
请注意，行模式元素按字典顺序扩展，并且要排列的每个元素必须用逗号与其他元素分隔。
See Also: 也可以看看：
Oracle Database Data Warehousing Guide for more information on permutations
Oracle 数据库数据仓库指南 了解有关排列的更多信息
row_pattern_quantifier 行模式量词
Use this clause to specify the row pattern quantifier, which is a postfix operator that defines the number of iterations accepted for a match.
使用此子句指定行模式量词，它是一个后缀运算符，用于定义匹配接受的迭代次数。
Row pattern quantifiers are referred to as greedy; they will attempt to match as many instances of the regular expression on which they are applied as possible. The exception is row pattern quantifiers that have a question mark (?) as a suffix, which are referred to as reluctant. They will attempt to match as few instances as possible of the regular expression on which they are applied.
行模式量词被称为贪婪；它们将尝试匹配尽可能多的应用它们的正则表达式实例。例外是以问号 ( ? ) 作为后缀的行模式量词，这被称为勉强的。它们将尝试匹配应用它们的正则表达式的尽可能少的实例。
Table 19-2 lists the valid row pattern quantifiers and the number of iterations they accept for a match. In this table, n and m represent unsigned integers.
表 19-2 列出了有效的行模式量词以及它们接受的匹配迭代次数。在此表中， n 和 m 表示无符号整数。
Table 19-2 Row Pattern Quantifiers
表 19-2 行模式量词
| Row Pattern Quantifier 行模式量词 | Number of Iterations Accepted for a Match 匹配接受的迭代次数 |
|---|---|
|
|
0 or more iterations (greedy) |
|
|
0 or more iterations (reluctant) |
|
|
1 or more iterations (greedy) |
|
|
1 or more iterations (reluctant) |
|
|
0 or 1 iterations (greedy) |
|
|
0 or 1 iterations (reluctant) |
|
|
|
|
|
|
|
|
Between |
|
|
Between |
|
|
Between 0 and |
|
|
Between 0 and |
|
|
|
See Also: 也可以看看：
Oracle Database Data Warehousing Guide for more information on row pattern quantifiers
Oracle 数据库数据仓库指南 了解有关行模式量词的更多信息
row_pattern_subset_clause
The SUBSET clause lets you specify one or more union row pattern variables. Use the row_pattern_subset_item clause to declare each union row pattern variable.
SUBSET 子句允许您指定一个或多个联合行模式变量。使用 row_pattern_subset_item 子句声明每个联合行模式变量。
You can specify union row pattern variables in the following clauses:
您可以在以下子句中指定联合行模式变量：
-
MEASURESclause: In the expression for a row pattern measure column. That is, in expressionexprof therow_pattern_measure_columnclause.
MEASURES子句：在行模式度量列的表达式中。即，在row_pattern_measure_column子句的表达式expr中。 -
DEFINEclause: In the condition that defines a primary pattern variable. That is, inconditionof therow_pattern_definitionclause
DEFINE子句：在定义主模式变量的条件中。即在row_pattern_definition子句的condition中
row_pattern_subset_item 行模式子集项目
This clause lets you create a grouping of multiple pattern variables that can be referred to with a variable name of its own. The variable name that refers to this grouping is called a union row pattern variable.
此子句允许您创建多个模式变量的分组，这些变量可以用其自己的变量名进行引用。引用此分组的变量名称称为联合行模式变量。
-
For
variable_nameon the left side of the equal sign, specify the name of the union row pattern variable.
对于等号左侧的variable_name，指定联合行模式变量的名称。 -
On the right side of the equal sign, specify a comma-separated list of distinct primary row pattern variables within parentheses. This list cannot include any union row pattern variables.
在等号右侧，在括号内指定不同主行模式变量的逗号分隔列表。该列表不能包含任何联合行模式变量。
See Also: 也可以看看：
Oracle Database Data Warehousing Guide for more information on defining union row pattern variables
Oracle 数据库数据仓库指南 了解有关定义联合行模式变量的更多信息
DEFINE 定义
Use the DEFINE clause to specify one or more row pattern definitions. A row pattern definition specifies the conditions that a row must meet in order to be mapped to a specific pattern variable.
使用 DEFINE 子句指定一个或多个行模式定义。行模式定义指定行必须满足的条件才能映射到特定的模式变量。
The DEFINE clause only supports running semantics.
DEFINE 子句仅支持运行语义。
See Also: 也可以看看：
-
Oracle Database Data Warehousing Guide for more information on the
DEFINEclause
Oracle 数据库数据仓库指南，了解有关DEFINE子句的更多信息 -
Oracle Database Data Warehousing Guide for more information on running and final semantics
Oracle 数据库数据仓库指南 有关运行和最终语义的更多信息
row_pattern_definition_list
行模式定义列表
This clause lets you specify one or more row pattern definitions.
该子句允许您指定一个或多个行模式定义。
row_pattern_definition 行模式定义
This clause lets you specify a row pattern definition, which contains the conditions that a row must meet in order to be mapped to the specified pattern variable.
此子句允许您指定行模式定义，其中包含行必须满足才能映射到指定模式变量的条件。
-
For
variable_name, specify the name of the pattern variable.
对于variable_name，指定模式变量的名称。 -
For
condition, specify a condition as described in Conditions, with the following extension:conditioncan contain any of the functions described by row_pattern_navigation_func::= and row_pattern_aggregate_func::=.
对于condition，指定条件，如条件中所述，并具有以下扩展名：condition可以包含 row_pattern_navigation_func::= 和 row_pattern_aggregate_func::= 描述的任何函数。
row_pattern_rec_func
This clause comprises the following clauses, which let you specify row pattern recognition functions:
该子句由以下子句组成，可让您指定行模式识别函数：
-
row_pattern_classifier_func: Use this clause to specify theCLASSIFIERfunction, which returns a character string whose value is the name of the variable to which the row is mapped.
row_pattern_classifier_func：使用此子句指定CLASSIFIER函数，该函数返回一个字符串，其值为该行映射到的变量的名称。 -
row_pattern_match_num_func: Use this clause to specify theMATCH_NUMBERfunction, which returns a numeric value with scale 0 (zero) whose value is the sequential number of the match within the row pattern partition.
row_pattern_match_num_func：使用此子句指定MATCH_NUMBER函数，该函数返回小数位数为 0（零）的数值，其值是行模式分区中匹配项的序号。 -
row_pattern_navigation_func: Use this clause to specify functions that perform row pattern navigation operations.
row_pattern_navigation_func：使用此子句指定执行行模式导航操作的函数。 -
row_pattern_aggregate_func: Use this clause to specify an aggregate function in the expression for a row pattern measure column or in the condition that defines a primary pattern variable.
row_pattern_aggregate_func：使用此子句在行模式度量列的表达式中或在定义主模式变量的条件中指定聚合函数。
You can specify row pattern recognition functions in the following clauses:
您可以在以下子句中指定行模式识别函数：
-
MEASURESclause: In the expression for a row pattern measure column. That is, in expressionexprof therow_pattern_measure_columnclause.
MEASURES子句：在行模式度量列的表达式中。即，在row_pattern_measure_column子句的表达式expr中。 -
DEFINEclause: In the condition that defines a primary pattern variable. That is, inconditionof therow_pattern_definitionclause
DEFINE子句：在定义主模式变量的条件中。即在row_pattern_definition子句的condition中
A row pattern recognition function may behave differently depending whether you specify it in the MEASURES or DEFINE clause. These details are explained in the semantics for each clause.
行模式识别函数的行为可能会有所不同，具体取决于您是在 MEASURES 还是 DEFINE 子句中指定它。这些细节在每个子句的语义中都有解释。
row_pattern_classifier_func
The CLASSIFIER function returns a character string whose value is the name of the variable to which the row is mapped.
CLASSIFIER 函数返回一个字符串，其值是该行映射到的变量的名称。
-
In the
MEASURESclause:
在MEASURES子句中：-
If you specify
ONEROWPERMATCH, then the query uses the last row of the match when processing theMEASURESclause, so theCLASSIFIERfunction returns the name of the pattern variable to which the last row of the match is mapped.
如果您指定ONEROWPERMATCH，则查询在处理MEASURES函数返回匹配的最后一行映射到的模式变量的名称。 -
If you specify
ALLROWSPERMATCH, then for each row of the match found, theCLASSIFIERfunction returns the name of the pattern variable to which the row is mapped.
如果您指定ALLROWSPERMATCH，则对于找到的匹配的每一行，CLASSIFIER函数返回该行映射到的模式变量的名称。
For empty matches—that is, matches that contain no rows, the
CLASSIFERfunction returns NULL.
对于空匹配（即不包含行的匹配），CLASSIFER函数返回 NULL。 -
-
In the
DEFINEclause, theCLASSIFIERfunction returns the name of the primary pattern variable to which the current row is mapped.
在DEFINE子句中，CLASSIFIER函数返回当前行映射到的主模式变量的名称。
row_pattern_match_num_func
The MATCH_NUMBER function returns a numeric value with scale 0 (zero) whose value is the sequential number of the match within the row pattern partition.
MATCH_NUMBER 函数返回一个小数位数为 0（零）的数值，其值是行模式分区内匹配的序号。
Matches within a row pattern partition are numbered sequentially starting with 1 in the order in which they are found. If multiple rows satisfy a match, then they are all assigned the same match number. Note that match numbering starts over again at 1 in each row pattern partition, because there is no inherent ordering between row pattern partitions.
行模式分区内的匹配项按照找到的顺序从 1 开始按顺序编号。如果多行满足匹配，则它们都被分配相同的匹配编号。请注意，在每个行模式分区中，匹配编号会从 1 重新开始，因为行模式分区之间没有固有的顺序。
-
In the
MEASURESclause: You can useMATCH_NUMBERto obtain the sequential number of the match within the row pattern.
在MEASURES子句中： 您可以使用MATCH_NUMBER来获取行模式中匹配项的序号。 -
In the
DEFINEclause: You can useMATCH_NUMBERto define conditions that depend upon the match number.
在DEFINE子句中： 您可以使用MATCH_NUMBER定义取决于匹配编号的条件。
row_pattern_navigation_func
This clause lets you perform the following row pattern navigation operations:
此子句允许您执行以下行模式导航操作：
-
Navigate among the group of rows mapped to a pattern variable using the
FIRSTandLASTfunctions of therow_pattern_nav_logicalclause.
使用row_pattern_nav_logical子句的FIRST和LAST函数在映射到模式变量的行组之间导航。 -
Navigate among all rows in a row pattern partition using the
PREVandNEXTfunctions of therow_pattern_nav_physicalclause
使用row_pattern_nav_physical子句的PREV和NEXT函数在行模式分区中的所有行之间导航 -
Nest the
FIRSTorLASTfunction within thePREVorNEXTfunction using therow_pattern_nav_compoundclause.
使用row_pattern_nav_compound子句将FIRST或LAST函数嵌套在PREV或NEXT函数中。
row_pattern_nav_logical 行模式导航逻辑
This clause lets you use the FIRST and LAST functions to navigate among the group of rows mapped to a pattern variable using an optional logical offset.
此子句允许您使用 FIRST 和 LAST 函数使用可选的逻辑偏移量在映射到模式变量的行组之间进行导航。
-
The
FIRSTfunction returns the value of expressionexprwhen evaluated in the first row of the group of rows mapped to the pattern variable that is specified inexpr. If no rows are mapped to the pattern variable, then theFIRSTfunction returns NULL.
在映射到expr中指定的模式变量的行组的第一行中求值时，FIRST函数返回表达式expr的值。如果没有行映射到模式变量，则FIRST函数返回 NULL。 -
The
LASTfunction returns the value of expressionexprwhen evaluated in the last row of the group of rows mapped to the pattern variable that is specified inexpr. If no rows are mapped to the pattern variable, then theLASTfunction returns NULL.
在映射到expr中指定的模式变量的行组的最后一行中求值时，LAST函数返回表达式expr的值。如果没有行映射到模式变量，则LAST函数返回 NULL。 -
Use
exprto specify the expression to be evaluated. It must contain at least one row pattern column reference. If it contains more than one row pattern column reference, then all must refer to the same pattern variable.
使用expr指定要计算的表达式。它必须至少包含一个行模式列引用。如果它包含多个行模式列引用，则所有行模式列引用都必须引用同一模式变量。 -
Use the optional
offsetto specify the logical offset within the set of rows mapped to the pattern variable. When specified with theFIRSTfunction, the offset is the number of rows from the first row, in ascending order. When specified with theLASTfunction, the offset is the number of rows from the last row in descending order. The default offset is 0.
使用可选的offset指定映射到模式变量的行集中的逻辑偏移量。当使用FIRST函数指定时，偏移量是从第一行开始的行数，按升序排列。当使用LAST函数指定时，偏移量是从最后一行开始按降序排列的行数。默认偏移量为 0。For
offset, specify a non-negative integer. It must be a runtime constant (literal, bind variable, or expressions involving them), but not a column or subquery.
对于offset，指定一个非负整数。它必须是运行时常量（文字、绑定变量或涉及它们的表达式），但不是列或子查询。If you specify an
offsetthat is greater than or equal to the number of rows mapped to the pattern variable minus 1, then the function returns NULL.
如果指定的offset大于或等于映射到模式变量的行数减 1，则该函数返回 NULL。
You can specify running or final semantics for the FIRST and LAST functions as follows:
您可以为 FIRST 和 LAST 函数指定运行或最终语义，如下所示：
-
The
MEASURESclause supports running and final semantics. SpecifyRUNNINGfor running semantics. SpecifyFINALfor final semantics. The default isRUNNING.
MEASURES子句支持运行和最终语义。指定RUNNING用于运行语义。为最终语义指定FINAL。默认为RUNNING。 -
The
DEFINEclause supports only running semantics. Therefore, running semantics will be used whether you specify or omitRUNNING. You cannot specifyFINAL.
DEFINE子句仅支持运行语义。因此，无论您指定还是省略RUNNING，都将使用运行语义。您不能指定FINAL。See Also: 也可以看看：
-
Oracle Database Data Warehousing Guide for more information on the
FIRSTandLASTfunctions
Oracle 数据库数据仓库指南，了解有关FIRST和LAST函数的更多信息 -
Oracle Database Data Warehousing Guide for more information on running and final semantics
Oracle 数据库数据仓库指南 有关运行和最终语义的更多信息
-
row_pattern_nav_physical
This clause lets you use the PREV and NEXT functions to navigate all rows in a row pattern partition using an optional physical offset.
此子句允许您使用 PREV 和 NEXT 函数使用可选的物理偏移量来导航行模式分区中的所有行。
-
The
PREVfunction returns the value of expressionexprwhen evaluated in the previous row in the partition. If there is no previous row in the partition, then thePREVfunction returns NULL.
在分区中的上一行中求值时，PREV函数返回表达式expr的值。如果分区中没有前一行，则PREV函数返回 NULL。 -
The
NEXTfunction returns the value of expressionexprwhen evaluated in the next row in the partition. If there is no next row in the partition, then the NEXT function returns NULL.
在分区中的下一行中计算时，NEXT函数返回表达式expr的值。如果分区中没有下一行，则 NEXT 函数返回 NULL。 -
Use
exprto specify the expression to be evaluated. It must contain at least one row pattern column reference. If it contains more than one row pattern column reference, then all must refer to the same pattern variable.
使用expr指定要计算的表达式。它必须至少包含一个行模式列引用。如果它包含多个行模式列引用，则所有行模式列引用都必须引用同一模式变量。 -
Use the optional
offsetto specify the physical offset within the partition. When specified with thePREVfunction, it is the number of rows before the current row. When specified with theNEXTfunction, it is the number of rows after the current row. The default is 1. If you specify an offset of 0, then the current row is evaluated.
使用可选的offset指定分区内的物理偏移量。当使用PREV函数指定时，它是当前行之前的行数。当使用NEXT函数指定时，它是当前行之后的行数。默认值为 1。如果指定偏移量 0，则计算当前行。For
offset, specify a non-negative integer. It must be a runtime constant (literal, bind variable, or expressions involving them), but not a column or subquery.
对于offset，指定一个非负整数。它必须是运行时常量（文字、绑定变量或涉及它们的表达式），但不是列或子查询。
The PREV and NEXT functions always use running semantics. Therefore, you cannot specify the RUNNING or FINAL keywords with this clause.
PREV 和 NEXT 函数始终使用运行语义。因此，您不能使用此子句指定 RUNNING 或 FINAL 关键字。
See Also: 也可以看看：
-
Oracle Database Data Warehousing Guide for more information on the
PREVandNEXTfunctions
Oracle 数据库数据仓库指南，了解有关PREV和NEXT函数的更多信息 -
Oracle Database Data Warehousing Guide for more information on running and final semantics
Oracle 数据库数据仓库指南 有关运行和最终语义的更多信息
row_pattern_nav_compound
This clause lets you nest the row_pattern_nav_logical clause within the row_pattern_nav_physical clause. That is, it lets you nest the FIRST or LAST function within the PREV or NEXT function. The row_pattern_nav_logical clause is evaluated first and then the result is supplied to the row_pattern_nav_physical clause.
此子句允许您将 row_pattern_nav_logical 子句嵌套在 row_pattern_nav_physical 子句中。也就是说，它允许您将 FIRST 或 LAST 函数嵌套在 PREV 或 NEXT 函数中。首先评估 row_pattern_nav_logical 子句，然后将结果提供给 row_pattern_nav_physical 子句。
Refer to row_pattern_nav_logical and row_pattern_nav_physical for the full semantics of these clauses.
有关这些子句的完整语义，请参阅 row_pattern_nav_logic 和 row_pattern_nav_physical。
See Also: 也可以看看：
Oracle Database Data Warehousing Guide for more information on nesting the FIRST and LAST functions within the PREV and NEXT functions
Oracle 数据库数据仓库指南，了解有关在 PREV 和 NEXT 函数中嵌套 FIRST 和 LAST 函数的更多信息
row_pattern_aggregate_func
This clause lets you use an aggregate function in the expression for a row pattern measure column or in the condition that defines a primary pattern variable.
此子句允许您在行模式度量列的表达式中或在定义主模式变量的条件中使用聚合函数。
For aggregate_function, specify any one of the AVG, COUNT, MAX, MIN, or SUM functions. The DISTINCT keyword is not supported.
对于 aggregate_function ，指定 AVG、COUNT、MAX、MIN 或 SUM 函数之一。不支持 DISTINCT 关键字。
You can specify running or final semantics for aggregate functions as follows:
您可以为聚合函数指定运行或最终语义，如下所示：
-
The
MEASURESclause supports running and final semantics. SpecifyRUNNINGfor running semantics. SpecifyFINALfor final semantics. The default isRUNNING.
MEASURES子句支持运行和最终语义。指定RUNNING用于运行语义。为最终语义指定FINAL。默认为RUNNING。 -
The
DEFINEclause supports only running semantics. Therefore, running semantics will be used whether you specify or omitRUNNING. You cannot specifyFINAL.
DEFINE子句仅支持运行语义。因此，无论您指定还是省略RUNNING，都将使用运行语义。您不能指定FINAL。
See Also: 也可以看看：
-
Oracle Database Data Warehousing Guide for more information on aggregate functions
Oracle 数据库数据仓库指南 了解有关聚合函数的更多信息 -
Oracle Database Data Warehousing Guide for more information on running and final semantics
Oracle 数据库数据仓库指南 有关运行和最终语义的更多信息
Examples 例子
SQL Macros - Table Valued Macros: Examples
SQL 宏 - 表值宏：示例
The macro function budget computes the amount of each department's budget for a given job. It returns the number of employees in each department with the specified job title.
宏函数 budget 计算每个部门针对给定工作的预算金额。它返回每个部门中具有指定职位的员工人数。
create or replace function budget(job varchar2) return varchar2 SQL_MACRO is begin return q'{ select deptno, sum(sal) budget from emp where job = budget.job group by deptno }'; end; /
SELECT * FROM budget ('MANAGER'); DEPTNO BUDGET –---------- –------- 20 2975 30 2850 10 2450
Using a PL/SQL Function in the WITH Clause: Examples
在WITH子句中使用PL/SQL函数：示例
The following example declares and defines a PL/SQL function get_domain in the WITH clause. The get_domain function returns the domain name from a URL string, assuming that the URL string has the "www" prefix immediately preceding the domain name, and the domain name is separated by dots on the left and right. The SELECT statement uses get_domain to find distinct catalog domain names from the orders table in the oe schema.
以下示例在 WITH 子句中声明并定义 PL/SQL 函数 get_domain 。 get_domain 函数从 URL 字符串中返回域名，假设 URL 字符串紧接在域名前面有“ www ”前缀，并且域名之间用点分隔左边和右边。 SELECT 语句使用 get_domain 从 oe 架构中的 orders 表中查找不同的目录域名。
WITH FUNCTION get_domain(url VARCHAR2) RETURN VARCHAR2 IS pos BINARY_INTEGER; len BINARY_INTEGER; BEGIN pos := INSTR(url, 'www.'); len := INSTR(SUBSTR(url, pos + 4), '.') - 1; RETURN SUBSTR(url, pos + 4, len); END; SELECT DISTINCT get_domain(catalog_url) FROM product_information; /
Subquery Factoring: Example
子查询因式分解：示例
The following statement creates the query names dept_costs and avg_cost for the initial query block containing a join, and then uses the query names in the body of the main query.
以下语句为包含联接的初始查询块创建查询名称 dept_costs 和 avg_cost ，然后在主查询正文中使用这些查询名称。
WITH dept_costs AS ( SELECT department_name, SUM(salary) dept_total FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY department_name), avg_cost AS ( SELECT SUM(dept_total)/COUNT(*) avg FROM dept_costs) SELECT * FROM dept_costs WHERE dept_total > (SELECT avg FROM avg_cost) ORDER BY department_name; DEPARTMENT_NAME DEPT_TOTAL ------------------------------ ---------- Sales 304500 Shipping 156400
Recursive Subquery Factoring: Examples
递归子查询分解：示例
The following statement shows the employees who directly or indirectly report to employee 101 and their reporting level.
以下语句显示了直接或间接向员工 101 汇报的员工及其汇报级别。
WITH reports_to_101 (eid, emp_last, mgr_id, reportLevel) AS ( SELECT employee_id, last_name, manager_id, 0 reportLevel FROM employees WHERE employee_id = 101 UNION ALL SELECT e.employee_id, e.last_name, e.manager_id, reportLevel+1 FROM reports_to_101 r, employees e WHERE r.eid = e.manager_id ) SELECT eid, emp_last, mgr_id, reportLevel FROM reports_to_101 ORDER BY reportLevel, eid; EID EMP_LAST MGR_ID REPORTLEVEL ---------- ------------------------- ---------- ----------- 101 Kochhar 100 0 108 Greenberg 101 1 200 Whalen 101 1 203 Mavris 101 1 204 Baer 101 1 205 Higgins 101 1 109 Faviet 108 2 110 Chen 108 2 111 Sciarra 108 2 112 Urman 108 2 113 Popp 108 2 206 Gietz 205 2
The following statement shows employees who directly or indirectly report to employee 101, their reporting level, and their management chain.
以下语句显示了直接或间接向员工 101 汇报的员工、他们的汇报级别以及他们的管理链条。
WITH reports_to_101 (eid, emp_last, mgr_id, reportLevel, mgr_list) AS ( SELECT employee_id, last_name, manager_id, 0 reportLevel, CAST(manager_id AS VARCHAR2(2000)) FROM employees WHERE employee_id = 101 UNION ALL SELECT e.employee_id, e.last_name, e.manager_id, reportLevel+1, CAST(mgr_list || ',' || manager_id AS VARCHAR2(2000)) FROM reports_to_101 r, employees e WHERE r.eid = e.manager_id ) SELECT eid, emp_last, mgr_id, reportLevel, mgr_list FROM reports_to_101 ORDER BY reportLevel, eid; EID EMP_LAST MGR_ID REPORTLEVEL MGR_LIST ---------- ------------------------- ---------- ----------- -------- 101 Kochhar 100 0 100 108 Greenberg 101 1 100,101 200 Whalen 101 1 100,101 203 Mavris 101 1 100,101 204 Baer 101 1 100,101 205 Higgins 101 1 100,101 109 Faviet 108 2 100,101,108 110 Chen 108 2 100,101,108 111 Sciarra 108 2 100,101,108 112 Urman 108 2 100,101,108 113 Popp 108 2 100,101,108 206 Gietz 205 2 100,101,205
The following statement shows the employees who directly or indirectly report to employee 101 and their reporting level. It stops at reporting level 1.
以下语句显示了直接或间接向员工 101 汇报的员工及其汇报级别。它停止在报告级别 1。
WITH reports_to_101 (eid, emp_last, mgr_id, reportLevel) AS ( SELECT employee_id, last_name, manager_id, 0 reportLevel FROM employees WHERE employee_id = 101 UNION ALL SELECT e.employee_id, e.last_name, e.manager_id, reportLevel+1 FROM reports_to_101 r, employees e WHERE r.eid = e.manager_id ) SELECT eid, emp_last, mgr_id, reportLevel FROM reports_to_101 WHERE reportLevel <= 1 ORDER BY reportLevel, eid; EID EMP_LAST MGR_ID REPORTLEVEL ---------- ------------------------- ---------- ----------- 101 Kochhar 100 0 108 Greenberg 101 1 200 Whalen 101 1 203 Mavris 101 1 204 Baer 101 1 205 Higgins 101 1
The following statement shows the entire organization, indenting for each level of management.
以下语句显示了整个组织，并针对每个管理级别进行了缩进。
WITH org_chart (eid, emp_last, mgr_id, reportLevel, salary, job_id) AS ( SELECT employee_id, last_name, manager_id, 0 reportLevel, salary, job_id FROM employees WHERE manager_id is null UNION ALL SELECT e.employee_id, e.last_name, e.manager_id, r.reportLevel+1 reportLevel, e.salary, e.job_id FROM org_chart r, employees e WHERE r.eid = e.manager_id ) SEARCH DEPTH FIRST BY emp_last SET order1 SELECT lpad(' ',2*reportLevel)||emp_last emp_name, eid, mgr_id, salary, job_id FROM org_chart ORDER BY order1; EMP_NAME EID MGR_ID SALARY JOB_ID -------------------- ---------- ---------- ---------- ---------- King 100 24000 AD_PRES Cambrault 148 100 11000 SA_MAN Bates 172 148 7300 SA_REP Bloom 169 148 10000 SA_REP Fox 170 148 9600 SA_REP Kumar 173 148 6100 SA_REP Ozer 168 148 11500 SA_REP Smith 171 148 7400 SA_REP De Haan 102 100 17000 AD_VP Hunold 103 102 9000 IT_PROG Austin 105 103 4800 IT_PROG Ernst 104 103 6000 IT_PROG Lorentz 107 103 4200 IT_PROG Pataballa 106 103 4800 IT_PROG Errazuriz 147 100 12000 SA_MAN Ande 166 147 6400 SA_REP . . .
The following statement shows the entire organization, indenting for each level of management, with each level ordered by hire_date. The value of is_cycle is set to Y for any employee who has the same hire_date as any manager above him in the management chain.
以下语句显示了整个组织，对每个管理级别进行缩进，每个级别按 hire_date 排序。对于与管理链中任何上级经理具有相同 hire_date 的任何员工， is_cycle 的值设置为 Y 。
WITH dup_hiredate (eid, emp_last, mgr_id, reportLevel, hire_date, job_id) AS ( SELECT employee_id, last_name, manager_id, 0 reportLevel, hire_date, job_id FROM employees WHERE manager_id is null UNION ALL SELECT e.employee_id, e.last_name, e.manager_id, r.reportLevel+1 reportLevel, e.hire_date, e.job_id FROM dup_hiredate r, employees e WHERE r.eid = e.manager_id ) SEARCH DEPTH FIRST BY hire_date SET order1 CYCLE hire_date SET is_cycle TO 'Y' DEFAULT 'N' SELECT lpad(' ',2*reportLevel)||emp_last emp_name, eid, mgr_id, hire_date, job_id, is_cycle FROM dup_hiredate ORDER BY order1; EMP_NAME EID MGR_ID HIRE_DATE JOB_ID IS_CYCLE -------------------- ---------- ---------- --------- ---------- -------- King 100 17-JUN-03 AD_PRES N De Haan 102 100 13-JAN-01 AD_VP N Hunold 103 102 03-JAN-06 IT_PROG N Austin 105 103 25-JUN-05 IT_PROG N . . . Kochhar 101 100 21-SEP-05 AD_VP N Mavris 203 101 07-JUN-02 HR_REP N Baer 204 101 07-JUN-02 PR_REP N Higgins 205 101 07-JUN-02 AC_MGR N Gietz 206 205 07-JUN-02 AC_ACCOUNT Y Greenberg 108 101 17-AUG-02 FI_MGR N Faviet 109 108 16-AUG-02 FI_ACCOUNT N Chen 110 108 28-SEP-05 FI_ACCOUNT N . . .
The following statement counts the number of employees under each manager.
以下语句计算每个经理下的员工人数。
WITH emp_count (eid, emp_last, mgr_id, mgrLevel, salary, cnt_employees) AS ( SELECT employee_id, last_name, manager_id, 0 mgrLevel, salary, 0 cnt_employees FROM employees UNION ALL SELECT e.employee_id, e.last_name, e.manager_id, r.mgrLevel+1 mgrLevel, e.salary, 1 cnt_employees FROM emp_count r, employees e WHERE e.employee_id = r.mgr_id ) SEARCH DEPTH FIRST BY emp_last SET order1 SELECT emp_last, eid, mgr_id, salary, sum(cnt_employees), max(mgrLevel) mgrLevel FROM emp_count GROUP BY emp_last, eid, mgr_id, salary HAVING max(mgrLevel) > 0 ORDER BY mgr_id NULLS FIRST, emp_last; EMP_LAST EID MGR_ID SALARY SUM(CNT_EMPLOYEES) MGRLEVEL ------------------ ---------- ---------- ---------- ------------------ ---------- King 100 24000 106 3 Cambrault 148 100 11000 7 2 De Haan 102 100 17000 5 2 Errazuriz 147 100 12000 6 1 Fripp 121 100 8200 8 1 Hartstein 201 100 13000 1 1 Kaufling 122 100 7900 8 1 . . .
Analytic Views: Examples 分析视图：示例
The following statement uses the persistent analytic view sales_av. The query selects the member_name hierarchical attribute of time_hier, which is the alias of a hierarchy of the same name, and values from the sales and units measures of the analytic view that are dimensioned by the time attribute dimension used by the time_hier hierarchy.. The results of the selection are filtered to those for the YEAR level of the hierarchy. The results are returned in hierarchical order.
以下语句使用持久分析视图 sales_av。该查询选择 time_hier 的 member_name 层次结构属性（它是同名层次结构的别名），以及来自分析视图的销售额和单位度量值的值，这些值由 所使用的时间属性维度进行维度化。 time_hier 层次结构。选择的结果将筛选到层次结构的 YEAR 级别的结果。结果按层次顺序返回。
SELECT time_hier.member_name as TIME,
sales,
units
FROM
sales_av HIERARCHIES(time_hier)
WHERE time_hier.level_name = 'YEAR'
ORDER BY time_hier.hier_order;
The results of the query are the following:
查询结果如下：
TIME SALES UNITS
------ ------------- ---------
CY2011 6755115980.73 24462444
CY2012 6901682398.95 24400619
CY2013 7240938717.57 24407259
CY2014 7579746352.89 24402666
CY2015 7941102885.15 24475206Transitory Analytic View Examples
瞬态分析视图示例
The following statement defines the transitory analytic view my_av in the WITH clause. The transitory analytic view is based on the persistent analytic view sales_av. The lag_sales calculated measure is a LAG calculation that is used at query time.
以下语句在 WITH 子句中定义瞬态分析视图 my_av。暂时分析视图基于持久分析视图 sales_av。 lag_sales 计算度量是在查询时使用的 LAG 计算。
WITH
my_av ANALYTIC VIEW AS (
USING sales_av HIERARCHIES (time_hier)
ADD MEASURES (
lag_sales AS (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1))
)
)
SELECT time_hier.member_name time, sales, lag_sales
FROM my_av HIERARCHIES (time_hier)
WHERE time_hier.level_name = 'YEAR'
ORDER BY time_hier.hier_order;The results of the query are the following:
查询结果如下：
TIME SALES LAG_SALES
------ ---------- ----------
CY2011 6755115981 (null)
CY2012 6901682399 6755115981
CY2013 7240938718 6901682399
CY2014 7579746353 7240938718
CY2015 7941102885 7579746353The following statement defines a transitory analytic view that uses a filter clause.
以下语句定义使用过滤子句的暂时分析视图。
WITH
my_av ANALYTIC VIEW AS (
USING sales_av HIERARCHIES (time_hier)
FILTER FACT (
time_hier TO quarter_of_year IN (1, 2)
AND year_name IN ('CY2011', 'CY2012')
)
)
SELECT time_hier.member_name time, sales
FROM my_av HIERARCHIES (time_hier)
WHERE time_hier.level_name IN ('YEAR', 'QUARTER')
ORDER BY time_hier.hier_order;The results of the query are the following:
查询结果如下：
TIME SALES
-------- ----------
CY2011 3340459835
Q1CY2011 1625299627
Q2CY2011 1715160208
CY2012 3397271965
Q1CY2012 1644857783
Q2CY2012 1752414182Inline Analytic View Example
内联分析视图示例
The following statement defines an inline analytic view in the FROM clause. The transitory analytic view is based on the persistent analytic view sales_av. The lag_sales calculated measure is a LAG calculation that is used at query time.
以下语句在 FROM 子句中定义内联分析视图。暂时分析视图基于持久分析视图 sales_av。 lag_sales 计算度量是在查询时使用的 LAG 计算。
SELECT time_hier.member_name time, sales, lag_sales
FROM
ANALYTIC VIEW (
USING sales_av HIERARCHIES (time_hier)
ADD MEASURES (
lag_sales AS (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1))
)
)
WHERE time_hier.level_name = 'YEAR'
ORDER BY time_hier.hier_order;The results of the query are the following:
查询结果如下：
TIME SALES LAG_SALES
------ ---------- ----------
CY2011 6755115981 (null)
CY2012 6901682399 6755115981
CY2013 7240938718 6901682399
CY2014 7579746353 7240938718
CY2015 7941102885 7579746353Simple Query Examples 简单查询示例
The following statement selects rows from the employees table with the department number of 30:
以下语句从 employees 表中选择部门编号为 30 的行：
SELECT * FROM employees WHERE department_id = 30 ORDER BY last_name;
The following statement selects the name, job, salary and department number of all employees except purchasing clerks from department number 30:
以下语句选择 30 号部门中除采购文员外的所有员工的姓名、工作、工资和部门号：
SELECT last_name, job_id, salary, department_id FROM employees WHERE NOT (job_id = 'PU_CLERK' AND department_id = 30) ORDER BY last_name;
The following statement selects from subqueries in the FROM clause and for each department returns the total employees and salaries as a decimal value of all the departments:
以下语句从 FROM 子句中的子查询中进行选择，并为每个部门返回所有部门的员工总数和工资（以十进制值表示）：
SELECT a.department_id "Department", a.num_emp/b.total_count "%_Employees", a.sal_sum/b.total_sal "%_Salary" FROM (SELECT department_id, COUNT(*) num_emp, SUM(salary) sal_sum FROM employees GROUP BY department_id) a, (SELECT COUNT(*) total_count, SUM(salary) total_sal FROM employees) b ORDER BY a.department_id;
Selecting from a Partition: Example
从分区中选择：示例
You can select rows from a single partition of a partitioned table by specifying the keyword PARTITION in the FROM clause. This SQL statement assigns an alias for and retrieves rows from the sales_q2_2000 partition of the sample table sh.sales:
您可以通过在 FROM 子句中指定关键字 PARTITION 从分区表的单个分区中选择行。此 SQL 语句为示例表 sh.sales 的 sales_q2_2000 分区分配别名并检索行：
SELECT * FROM sales PARTITION (sales_q2_2000) s WHERE s.amount_sold > 1500 ORDER BY cust_id, time_id, channel_id;
The following example selects rows from the oe.orders table for orders earlier than a specified date:
以下示例从 oe.orders 表中选择早于指定日期的订单的行：
SELECT * FROM orders WHERE order_date < TO_DATE('2006-06-15', 'YYYY-MM-DD');
Selecting a Sample: Examples
选择样本：示例
The following query estimates the number of orders in the oe.orders table:
以下查询估计 oe.orders 表中的订单数：
SELECT COUNT(*) * 10 FROM orders SAMPLE (10); COUNT(*)*10 ----------- 70
Because the query returns an estimate, the actual return value may differ from one query to the next.
由于查询返回估计值，因此每个查询的实际返回值可能有所不同。
SELECT COUNT(*) * 10 FROM orders SAMPLE (10); COUNT(*)*10 ----------- 80
The following query adds a seed value to the preceding query. Oracle Database always returns the same estimate given the same seed value:
以下查询将种子值添加到前面的查询中。给定相同的种子值，Oracle 数据库始终返回相同的估计值：
SELECT COUNT(*) * 10 FROM orders SAMPLE(10) SEED (1); COUNT(*)*10 ----------- 130 SELECT COUNT(*) * 10 FROM orders SAMPLE(10) SEED(4); COUNT(*)*10 ----------- 120 SELECT COUNT(*) * 10 FROM orders SAMPLE(10) SEED (1); COUNT(*)*10 ----------- 130
Using Flashback Queries: Example
使用闪回查询：示例
The following statements show a current value from the sample table hr.employees and then change the value. The intervals used in these examples are very short for demonstration purposes. Time intervals in your own environment are likely to be larger.
以下语句显示示例表 hr.employees 中的当前值，然后更改该值。出于演示目的，这些示例中使用的间隔非常短。在您自己的环境中，时间间隔可能会更大。
SELECT salary FROM employees WHERE last_name = 'Chung'; SALARY ---------- 3800 UPDATE employees SET salary = 4000 WHERE last_name = 'Chung'; 1 row updated. SELECT salary FROM employees WHERE last_name = 'Chung'; SALARY ---------- 4000
To learn what the value was before the update, you can use the following Flashback Query:
要了解更新之前的值，您可以使用以下闪回查询：
SELECT salary FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' MINUTE) WHERE last_name = 'Chung'; SALARY ---------- 3800
To learn what the values were during a particular time period, you can use a version Flashback Query:
要了解特定时间段内的值，您可以使用版本闪回查询：
SELECT salary FROM employees VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP - INTERVAL '10' MINUTE AND SYSTIMESTAMP - INTERVAL '1' MINUTE WHERE last_name = 'Chung';
To revert to the earlier value, use the Flashback Query as the subquery of another UPDATE statement:
要恢复到之前的值，请使用闪回查询作为另一个 UPDATE 语句的子查询：
UPDATE employees SET salary = (SELECT salary FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '2' MINUTE) WHERE last_name = 'Chung') WHERE last_name = 'Chung'; 1 row updated. SELECT salary FROM employees WHERE last_name = 'Chung'; SALARY ---------- 3800
Using the GROUP BY Clause: Examples
使用 GROUP BY 子句：示例
To return the minimum and maximum salaries for each department in the employees table, issue the following statement:
要返回 employees 表中每个部门的最低和最高工资，请发出以下语句：
SELECT department_id, MIN(salary), MAX (salary) FROM employees GROUP BY department_id ORDER BY department_id;
To return the minimum and maximum salaries for the clerks in each department, issue the following statement:
要返回每个部门职员的最低和最高工资，请发出以下声明：
SELECT department_id, MIN(salary), MAX (salary) FROM employees WHERE job_id = 'PU_CLERK' GROUP BY department_id ORDER BY department_id;
Using the GROUP BY CUBE Clause: Example
使用 GROUP BY CUBE 子句：示例
To return the number of employees and their average yearly salary across all possible combinations of department and job category, issue the following query on the sample tables hr.employees and hr.departments:
要返回所有可能的部门和工作类别组合中的员工人数及其平均年薪，请对示例表 hr.employees 和 hr.departments 发出以下查询：
SELECT DECODE(GROUPING(department_name), 1, 'All Departments', department_name) AS department_name, DECODE(GROUPING(job_id), 1, 'All Jobs', job_id) AS job_id, COUNT(*) "Total Empl", AVG(salary) * 12 "Average Sal" FROM employees e, departments d WHERE d.department_id = e.department_id GROUP BY CUBE (department_name, job_id) ORDER BY department_name, job_id; DEPARTMENT_NAME JOB_ID Total Empl Average Sal ------------------------------ ---------- ---------- ----------- Accounting AC_ACCOUNT 1 99600 Accounting AC_MGR 1 144000 Accounting All Jobs 2 121800 Administration AD_ASST 1 52800 . . . Shipping ST_CLERK 20 33420 Shipping ST_MAN 5 87360
Using the GROUPING SETS Clause: Example
使用 GROUPING SETS 子句：示例
The following example finds the sum of sales aggregated for three precisely specified groups:
以下示例查找三个精确指定组的合计销售额总和：
-
(channel_desc, calendar_month_desc, country_id) -
(channel_desc, country_id) -
(calendar_month_desc, country_id)
Without the GROUPING SETS syntax, you would have to write less efficient queries with more complicated SQL. For example, you could run three separate queries and UNION them, or run a query with a CUBE(channel_desc, calendar_month_desc, country_id) operation and filter out five of the eight groups it would generate.
如果没有 GROUPING SETS 语法，您将不得不使用更复杂的 SQL 编写效率较低的查询。例如，您可以运行三个单独的查询并 UNION 它们，或者运行带有 CUBE(channel_desc, calendar_month_desc, country_id) 操作的查询并过滤掉它将生成的八个组中的五个。
SELECT channel_desc, calendar_month_desc, co.country_id, TO_CHAR(sum(amount_sold) , '9,999,999,999') SALES$ FROM sales, customers, times, channels, countries co WHERE sales.time_id=times.time_id AND sales.cust_id=customers.cust_id AND sales.channel_id= channels.channel_id AND customers.country_id = co.country_id AND channels.channel_desc IN ('Direct Sales', 'Internet') AND times.calendar_month_desc IN ('2000-09', '2000-10') AND co.country_iso_code IN ('UK', 'US') GROUP BY GROUPING SETS( (channel_desc, calendar_month_desc, co.country_id), (channel_desc, co.country_id), (calendar_month_desc, co.country_id) ); CHANNEL_DESC CALENDAR COUNTRY_ID SALES$ -------------------- -------- ---------- ---------- Internet 2000-09 52790 124,224 Direct Sales 2000-09 52790 638,201 Internet 2000-10 52790 137,054 Direct Sales 2000-10 52790 682,297 2000-09 52790 762,425 2000-10 52790 819,351 Internet 52790 261,278 Direct Sales 52790 1,320,497
See Also: 也可以看看：
The functions GROUP_ID, GROUPING, and GROUPING_ID for more information on those functions
函数 GROUP_ID、GROUPING 和 GROUPING_ID 有关这些函数的更多信息
Hierarchical Query Examples
分层查询示例
The following query with a CONNECT BY clause defines a hierarchical relationship in which the employee_id value of the parent row is equal to the manager_id value of the child row:
以下带有 CONNECT BY 子句的查询定义了一种层次关系，其中父行的 employee_id 值等于 manager_id 子行的值：
SELECT last_name, employee_id, manager_id FROM employees CONNECT BY employee_id = manager_id ORDER BY last_name;
In the following CONNECT BY clause, the PRIOR operator applies only to the employee_id value. To evaluate this condition, the database evaluates employee_id values for the parent row and manager_id, salary, and commission_pct values for the child row:
在下面的 CONNECT BY 子句中， PRIOR 运算符仅适用于 employee_id 值。为了评估此条件，数据库会评估父行的 employee_id 值以及子行的 manager_id 、 salary 和 commission_pct 值:
SELECT last_name, employee_id, manager_id FROM employees CONNECT BY PRIOR employee_id = manager_id AND salary > commission_pct ORDER BY last_name;
To qualify as a child row, a row must have a manager_id value equal to the employee_id value of the parent row and it must have a salary value greater than its commission_pct value.
要成为子行，行的 manager_id 值必须等于父行的 employee_id 值，并且 salary 值必须大于它的 commission_pct 值。
Using the HAVING Condition: Example
使用 HAVING 条件：示例
To return the minimum and maximum salaries for the employees in each department whose lowest salary is less than $5,000, issue the next statement:
要返回每个部门最低工资低于 5,000 美元的员工的最低和最高工资，请发出以下语句：
SELECT department_id, MIN(salary), MAX (salary) FROM employees GROUP BY department_id HAVING MIN(salary) < 5000 ORDER BY department_id; DEPARTMENT_ID MIN(SALARY) MAX(SALARY) ------------- ----------- ----------- 10 4400 4400 30 2500 11000 50 2100 8200 60 4200 9000
The following example uses a correlated subquery in a HAVING clause that eliminates from the result set any departments without managers and managers without departments:
以下示例在 HAVING 子句中使用相关子查询，该子查询从结果集中消除任何没有经理的部门和没有部门的经理：
SELECT department_id, manager_id FROM employees GROUP BY department_id, manager_id HAVING (department_id, manager_id) IN (SELECT department_id, manager_id FROM employees x WHERE x.department_id = employees.department_id) ORDER BY department_id;
Using the ORDER BY Clause: Examples
使用 ORDER BY 子句：示例
To select all purchasing clerk records from employees and order the results by salary in descending order, issue the following statement:
要从 employees 中选择所有采购职员记录并按工资降序排列结果，请发出以下语句：
SELECT * FROM employees WHERE job_id = 'PU_CLERK' ORDER BY salary DESC;
To select information from employees ordered first by ascending department number and then by descending salary, issue the following statement:
要从 employees 中选择信息，首先按部门编号升序排列，然后按工资降序排列，请发出以下语句：
SELECT last_name, department_id, salary FROM employees ORDER BY department_id ASC, salary DESC, last_name;
To select the same information as the previous SELECT and use the positional ORDER BY notation, issue the following statement, which orders by ascending department_id, then descending salary, and finally alphabetically by last_name:
要选择与前面的 SELECT 相同的信息并使用位置 ORDER BY 表示法，请发出以下语句，该语句按升序 department_id ，然后降序 salary ，最后按字母顺序排列 last_name ：
SELECT last_name, department_id, salary FROM employees ORDER BY 2 ASC, 3 DESC, 1;
The MODEL clause: Examples
MODEL 子句：示例
The view created below is based on the sample sh schema and is used by the example that follows.
下面创建的视图基于示例 sh 架构，并由下面的示例使用。
CREATE OR REPLACE VIEW sales_view_ref AS SELECT country_name country, prod_name prod, calendar_year year, SUM(amount_sold) sale, COUNT(amount_sold) cnt FROM sales,times,customers,countries,products WHERE sales.time_id = times.time_id AND sales.prod_id = products.prod_id AND sales.cust_id = customers.cust_id AND customers.country_id = countries.country_id AND ( customers.country_id = 52779 OR customers.country_id = 52776 ) AND ( prod_name = 'Standard Mouse' OR prod_name = 'Mouse Pad' ) GROUP BY country_name,prod_name,calendar_year; SELECT country, prod, year, sale FROM sales_view_ref ORDER BY country, prod, year; COUNTRY PROD YEAR SALE ---------- ----------------------------------- -------- --------- France Mouse Pad 1998 2509.42 France Mouse Pad 1999 3678.69 France Mouse Pad 2000 3000.72 France Mouse Pad 2001 3269.09 France Standard Mouse 1998 2390.83 France Standard Mouse 1999 2280.45 France Standard Mouse 2000 1274.31 France Standard Mouse 2001 2164.54 Germany Mouse Pad 1998 5827.87 Germany Mouse Pad 1999 8346.44 Germany Mouse Pad 2000 7375.46 Germany Mouse Pad 2001 9535.08 Germany Standard Mouse 1998 7116.11 Germany Standard Mouse 1999 6263.14 Germany Standard Mouse 2000 2637.31 Germany Standard Mouse 2001 6456.13 16 rows selected.
The next example creates a multidimensional array from sales_view_ref with columns containing country, product, year, and sales. It also:
下一个示例从 sales_view_ref 创建一个多维数组，其中包含国家/地区、产品、年份和销售额。它也是：
-
Assigns the sum of the sales of the Mouse Pad for years 1999 and 2000 to the sales of the Mouse Pad for year 2001, if a row containing sales of the Mouse Pad for year 2001 exists.
如果存在包含 2001 年鼠标垫销售额的行，则将 1999 年和 2000 年鼠标垫销售额的总和分配给 2001 年鼠标垫销售额。 -
Assigns the value of sales of the Standard Mouse for year 2001 to sales of the Standard Mouse for year 2002, creating a new row if a row containing sales of the Standard Mouse for year 2002 does not exist.
将 2001 年标准鼠标的销售额值分配给 2002 年标准鼠标的销售额，如果不存在包含 2002 年标准鼠标销售额的行，则创建一个新行。
SELECT country,prod,year,s FROM sales_view_ref MODEL PARTITION BY (country) DIMENSION BY (prod, year) MEASURES (sale s) IGNORE NAV UNIQUE DIMENSION RULES UPSERT SEQUENTIAL ORDER ( s[prod='Mouse Pad', year=2001] = s['Mouse Pad', 1999] + s['Mouse Pad', 2000], s['Standard Mouse', 2002] = s['Standard Mouse', 2001] ) ORDER BY country, prod, year; COUNTRY PROD YEAR SALE ---------- ----------------------------------- -------- --------- France Mouse Pad 1998 2509.42 France Mouse Pad 1999 3678.69 France Mouse Pad 2000 3000.72 France Mouse Pad 2001 6679.41 France Standard Mouse 1998 2390.83 France Standard Mouse 1999 2280.45 France Standard Mouse 2000 1274.31 France Standard Mouse 2001 2164.54 France Standard Mouse 2002 2164.54 Germany Mouse Pad 1998 5827.87 Germany Mouse Pad 1999 8346.44 Germany Mouse Pad 2000 7375.46 Germany Mouse Pad 2001 15721.9 Germany Standard Mouse 1998 7116.11 Germany Standard Mouse 1999 6263.14 Germany Standard Mouse 2000 2637.31 Germany Standard Mouse 2001 6456.13 Germany Standard Mouse 2002 6456.13 18 rows selected.
The first rule uses UPDATE behavior because symbolic referencing is used on the left-hand side of the rule. The rows represented by the left-hand side of the rule exist, so the measure columns are updated. If the rows did not exist, then no action would have been taken.
第一个规则使用 UPDATE 行为，因为符号引用用在规则的左侧。规则左侧表示的行存在，因此度量列已更新。如果行不存在，则不会采取任何操作。
The second rule uses UPSERT behavior because positional referencing is used on the left-hand side and a single cell is referenced. The rows do not exist, so new rows are inserted and the related measure columns are updated. If the rows did exist, then the measure columns would have been updated.
第二条规则使用 UPSERT 行为，因为在左侧使用位置引用并且引用单个单元格。这些行不存在，因此将插入新行并更新相关度量列。如果行确实存在，则度量列将被更新。
See Also: 也可以看看：
Oracle Database Data Warehousing Guide for an expanded discussion and examples
Oracle 数据库数据仓库指南，提供扩展讨论和示例
The next example uses the same sales_view_ref view and the analytic function SUM to calculate a cumulative sum (csum) of sales per country and per year.
下一个示例使用相同的 sales_view_ref 视图和分析函数 SUM 来计算每个国家/地区和每年的销售额累积总和 ( csum )。
SELECT country, year, sale, csum FROM (SELECT country, year, SUM(sale) sale FROM sales_view_ref GROUP BY country, year ) MODEL DIMENSION BY (country, year) MEASURES (sale, 0 csum) RULES (csum[any, any]= SUM(sale) OVER (PARTITION BY country ORDER BY year ROWS UNBOUNDED PRECEDING) ) ORDER BY country, year; COUNTRY YEAR SALE CSUM --------------- ---------- ---------- ---------- France 1998 4900.25 4900.25 France 1999 5959.14 10859.39 France 2000 4275.03 15134.42 France 2001 5433.63 20568.05 Germany 1998 12943.98 12943.98 Germany 1999 14609.58 27553.56 Germany 2000 10012.77 37566.33 Germany 2001 15991.21 53557.54 8 rows selected.
Row Limiting: Examples 行限制：示例
The following statement returns the 5 employees with the lowest employee_id values:
以下语句返回 employee_id 值最低的 5 名员工：
SELECT employee_id, last_name FROM employees ORDER BY employee_id FETCH FIRST 5 ROWS ONLY; EMPLOYEE_ID LAST_NAME ----------- ------------------------- 100 King 101 Kochhar 102 De Haan 103 Hunold 104 Ernst
The following statement returns the next 5 employees with the lowest employee_id values:
以下语句返回具有最低 employee_id 值的接下来 5 名员工：
SELECT employee_id, last_name FROM employees ORDER BY employee_id OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY; EMPLOYEE_ID LAST_NAME ----------- ------------------------- 105 Austin 106 Pataballa 107 Lorentz 108 Greenberg 109 Faviet
The following statement returns the 5 percent of employees with the lowest salaries:
以下语句返回工资最低的 5% 的员工：
SELECT employee_id, last_name, salary FROM employees ORDER BY salary FETCH FIRST 5 PERCENT ROWS ONLY; EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 132 Olson 2100 128 Markle 2200 136 Philtanker 2200 127 Landry 2400 135 Gee 2400 119 Colmenares 2500
Because WITH TIES is specified, the following statement returns the 5 percent of employees with the lowest salaries, plus all additional employees with the same salary as the last row fetched in the previous example:
由于指定了 WITH TIES ，因此以下语句将返回 5% 的工资最低的员工，以及与上一示例中获取的最后一行工资相同的所有其他员工：
SELECT employee_id, last_name, salary FROM employees ORDER BY salary FETCH FIRST 5 PERCENT ROWS WITH TIES; EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 132 Olson 2100 128 Markle 2200 136 Philtanker 2200 127 Landry 2400 135 Gee 2400 119 Colmenares 2500 131 Marlow 2500 140 Patel 2500 144 Vargas 2500 182 Sullivan 2500 191 Perkins 2500
Using the FOR UPDATE Clause: Examples
使用 FOR UPDATE 子句：示例
The following statement locks rows in the employees table with purchasing clerks located in Oxford, which has location_id 2500, and locks rows in the departments table with departments in Oxford that have purchasing clerks:
以下语句锁定 employees 表中包含位于牛津的采购职员的行（该表中有 location_id 2500 个），并锁定 departments 表中包含位于牛津的部门的行有采购文员的：
SELECT e.employee_id, e.salary, e.commission_pct FROM employees e, departments d WHERE job_id = 'SA_REP' AND e.department_id = d.department_id AND location_id = 2500 ORDER BY e.employee_id FOR UPDATE;
The following statement locks only those rows in the employees table with purchasing clerks located in Oxford. No rows are locked in the departments table:
以下语句仅锁定 employees 表中采购员位于牛津的那些行。 departments 表中没有行被锁定：
SELECT e.employee_id, e.salary, e.commission_pct FROM employees e JOIN departments d USING (department_id) WHERE job_id = 'SA_REP' AND location_id = 2500 ORDER BY e.employee_id FOR UPDATE OF e.salary;
Using the WITH CHECK OPTION Clause: Example
使用WITH CHECK OPTION子句：示例
The following statement is legal even though the third value inserted violates the condition of the subquery where_clause:
即使插入的第三个值违反了子查询 where_clause 的条件，以下语句也是合法的：
INSERT INTO (SELECT department_id, department_name, location_id FROM departments WHERE location_id < 2000) VALUES (9999, 'Entertainment', 2500);
However, the following statement is illegal because it contains the WITH CHECK OPTION clause:
但是，以下语句是非法的，因为它包含 WITH CHECK OPTION 子句：
INSERT INTO (SELECT department_id, department_name, location_id FROM departments WHERE location_id < 2000 WITH CHECK OPTION) VALUES (9999, 'Entertainment', 2500); * ERROR at line 2: ORA-01402: view WITH CHECK OPTION where-clause violation
Using PIVOT and UNPIVOT: Examples
使用 PIVOT 和 UNPIVOT：示例
The oe.orders table contains information about when an order was placed (order_date), how it was place (order_mode), and the total amount of the order (order_total), as well as other information. The following example shows how to use the PIVOT clause to pivot order_mode values into columns, aggregating order_total data in the process, to get yearly totals by order mode:
oe.orders 表包含有关何时下订单 ( order_date )、如何下订单 ( order_mode ) 以及订单总金额 ( < b3>），以及其他信息。以下示例演示如何使用 PIVOT 子句将 order_mode 值透视到列中，聚合流程中的 order_total 数据，以按订单模式获取年度总计：
CREATE TABLE pivot_table AS SELECT * FROM (SELECT EXTRACT(YEAR FROM order_date) year, order_mode, order_total FROM orders) PIVOT (SUM(order_total) FOR order_mode IN ('direct' AS Store, 'online' AS Internet)); SELECT * FROM pivot_table ORDER BY year; YEAR STORE INTERNET ---------- ---------- ---------- 2004 5546.6 2006 371895.5 100056.6 2007 1274078.8 1271019.5 2008 252108.3 393349.4
The UNPIVOT clause lets you rotate specified columns so that the input column headings are output as values of one or more descriptor columns, and the input column values are output as values of one or more measures columns. The first query that follows shows that nulls are excluded by default. The second query shows that you can include nulls using the INCLUDE NULLS clause.
UNPIVOT 子句允许您旋转指定列，以便将输入列标题输出为一个或多个描述符列的值，并将输入列值输出为一个或多个度量列的值。接下来的第一个查询显示默认情况下排除空值。第二个查询显示您可以使用 INCLUDE NULLS 子句包含空值。
SELECT * FROM pivot_table UNPIVOT (yearly_total FOR order_mode IN (store AS 'direct', internet AS 'online')) ORDER BY year, order_mode; YEAR ORDER_ YEARLY_TOTAL ---------- ------ ------------ 2004 direct 5546.6 2006 direct 371895.5 2006 online 100056.6 2007 direct 1274078.8 2007 online 1271019.5 2008 direct 252108.3 2008 online 393349.4 7 rows selected. SELECT * FROM pivot_table UNPIVOT INCLUDE NULLS (yearly_total FOR order_mode IN (store AS 'direct', internet AS 'online')) ORDER BY year, order_mode; YEAR ORDER_ YEARLY_TOTAL ---------- ------ ------------ 2004 direct 5546.6 2004 online 2006 direct 371895.5 2006 online 100056.6 2007 direct 1274078.8 2007 online 1271019.5 2008 direct 252108.3 2008 online 393349.4 8 rows selected.
Using Join Queries: Examples
使用连接查询：示例
The following examples show various ways of joining tables in a query. In the first example, an equijoin returns the name and job of each employee and the number and name of the department in which the employee works:
以下示例显示了在查询中连接表的各种方法。在第一个示例中，等值连接返回每个员工的姓名和工作以及该员工工作的部门的编号和名称：
SELECT last_name, job_id, departments.department_id, department_name FROM employees, departments WHERE employees.department_id = departments.department_id ORDER BY last_name, job_id; LAST_NAME JOB_ID DEPARTMENT_ID DEPARTMENT_NAME ------------------- ---------- ------------- ---------------------- Abel SA_REP 80 Sales Ande SA_REP 80 Sales Atkinson ST_CLERK 50 Shipping Austin IT_PROG 60 IT . . .
You must use a join to return this data because employee names and jobs are stored in a different table than department names. Oracle Database combines rows of the two tables according to this join condition:
您必须使用联接来返回此数据，因为员工姓名和职位与部门名称存储在不同的表中。 Oracle 数据库根据此连接条件合并两个表的行：
employees.department_id = departments.department_id
The following equijoin returns the name, job, department number, and department name of all sales managers:
以下等值连接返回所有销售经理的姓名、职位、部门编号和部门名称：
SELECT last_name, job_id, departments.department_id, department_name FROM employees, departments WHERE employees.department_id = departments.department_id AND job_id = 'SA_MAN' ORDER BY last_name; LAST_NAME JOB_ID DEPARTMENT_ID DEPARTMENT_NAME ------------------- ---------- ------------- ----------------------- Cambrault SA_MAN 80 Sales Errazuriz SA_MAN 80 Sales Partners SA_MAN 80 Sales Russell SA_MAN 80 Sales Zlotkey SA_MAN 80 Sales
This query is identical to the preceding example, except that it uses an additional where_clause condition to return only rows with a job value of 'SA_MAN'.
此查询与前面的示例相同，只是它使用附加的 where_clause 条件来仅返回 job 值为“ SA_MAN ”的行。
Using Subqueries: Examples
使用子查询：示例
To determine who works in the same department as employee 'Lorentz', issue the following statement:
要确定谁与员工“ Lorentz ”在同一部门工作，请发出以下语句：
SELECT last_name, department_id FROM employees WHERE department_id = (SELECT department_id FROM employees WHERE last_name = 'Lorentz') ORDER BY last_name, department_id;
To give all employees in the employees table a 10% raise if they have changed jobs—if they appear in the job_history table—issue the following statement:
要为 employees 表中所有换工作的员工（如果他们出现在 job_history 表中）加薪 10%，请发出以下语句：
UPDATE employees SET salary = salary * 1.1 WHERE employee_id IN (SELECT employee_id FROM job_history);
To create a second version of the departments table new_departments, with only three of the columns of the original table, issue the following statement:
要创建仅包含原始表的三列的 departments 表 new_departments 的第二个版本，请发出以下语句：
CREATE TABLE new_departments (department_id, department_name, location_id) AS SELECT department_id, department_name, location_id FROM departments;
Using Self Joins: Example
使用自连接：示例
The following query uses a self join to return the name of each employee along with the name of the employee's manager. A WHERE clause is added to shorten the output.
以下查询使用自联接返回每个员工的姓名以及该员工的经理的姓名。添加 WHERE 子句以缩短输出。
SELECT e1.last_name||' works for '||e2.last_name "Employees and Their Managers" FROM employees e1, employees e2 WHERE e1.manager_id = e2.employee_id AND e1.last_name LIKE 'R%' ORDER BY e1.last_name; Employees and Their Managers ------------------------------- Rajs works for Mourgos Raphaely works for King Rogers works for Kaufling Russell works for King
The join condition for this query uses the aliases e1 and e2 for the sample table employees:
此查询的连接条件使用示例表 employees 的别名 e1 和 e2 ：
e1.manager_id = e2.employee_id
Using Outer Joins: Examples
使用外连接：示例
The following example shows how a partitioned outer join fills data gaps in rows to facilitate analytic function specification and reliable report formatting. The example first creates a small data table to be used in the join:
以下示例显示分区外联接如何填充行中的数据间隙，以促进分析函数规范和可靠的报告格式设置。该示例首先创建一个用于连接的小数据表：
SELECT d.department_id, e.last_name FROM departments d LEFT OUTER JOIN employees e ON d.department_id = e.department_id ORDER BY d.department_id, e.last_name;
Users familiar with the traditional Oracle Database outer joins syntax will recognize the same query in this form:
熟悉传统 Oracle 数据库外连接语法的用户将识别以下形式的相同查询：
SELECT d.department_id, e.last_name FROM departments d, employees e WHERE d.department_id = e.department_id(+) ORDER BY d.department_id, e.last_name;
Oracle strongly recommends that you use the more flexible FROM clause join syntax shown in the former example.
Oracle 强烈建议您使用前一示例中所示的更灵活的 FROM 子句连接语法。
The left outer join returns all departments, including those without any employees. The same statement with a right outer join returns all employees, including those not yet assigned to a department:
左外连接返回所有部门，包括没有任何员工的部门。具有右外连接的同一语句返回所有员工，包括尚未分配给部门的员工：
Note:
The employee Zeuss was added to the employees table for these examples, and is not part of the sample data.
注意：员工 Zeuss 已添加到这些示例的员工表中，并且不是示例数据的一部分。
SELECT d.department_id, e.last_name FROM departments d RIGHT OUTER JOIN employees e ON d.department_id = e.department_id ORDER BY d.department_id, e.last_name; DEPARTMENT_ID LAST_NAME ------------- ------------------------- . . . 110 Gietz 110 Higgins Grant Zeuss
It is not clear from this result whether employees Grant and Zeuss have department_id NULL, or whether their department_id is not in the departments table. To determine this requires a full outer join:
从该结果中不清楚员工 Grant 和 Zeuss 是否有 department_id NULL ，或者他们的 department_id 是否不在 departments 表中。要确定这一点需要完整的外部联接：
SELECT d.department_id as d_dept_id, e.department_id as e_dept_id, e.last_name FROM departments d FULL OUTER JOIN employees e ON d.department_id = e.department_id ORDER BY d.department_id, e.last_name; D_DEPT_ID E_DEPT_ID LAST_NAME ---------- ---------- ------------------------- . . . 110 110 Gietz 110 110 Higgins . . . 260 270 999 Zeuss Grant
Because the column names in this example are the same in both tables in the join, you can also use the common column feature by specifying the USING clause of the join syntax. The output is the same as for the preceding example except that the USING clause coalesces the two matching columns department_id into a single column output:
由于本示例中的列名在连接中的两个表中相同，因此您还可以通过指定连接语法的 USING 子句来使用公共列功能。输出与前面的示例相同，只是 USING 子句将两个匹配列 department_id 合并为单个列输出：
SELECT department_id AS d_e_dept_id, e.last_name FROM departments d FULL OUTER JOIN employees e USING (department_id) ORDER BY department_id, e.last_name; D_E_DEPT_ID LAST_NAME ----------- ------------------------- . . . 110 Higgins 110 Gietz . . . 260 270 999 Zeuss Grant
Using Partitioned Outer Joins: Examples
使用分区外连接：示例
The following example shows how a partitioned outer join fills in gaps in rows to facilitate analytic calculation specification and reliable report formatting. The example first creates and populates a simple table to be used in the join:
以下示例显示分区外联接如何填充行中的间隙，以促进分析计算规范和可靠的报告格式设置。该示例首先创建并填充一个要在联接中使用的简单表：
CREATE TABLE inventory (time_id DATE, product VARCHAR2(10), quantity NUMBER); INSERT INTO inventory VALUES (TO_DATE('01/04/01', 'DD/MM/YY'), 'bottle', 10); INSERT INTO inventory VALUES (TO_DATE('06/04/01', 'DD/MM/YY'), 'bottle', 10); INSERT INTO inventory VALUES (TO_DATE('01/04/01', 'DD/MM/YY'), 'can', 10); INSERT INTO inventory VALUES (TO_DATE('04/04/01', 'DD/MM/YY'), 'can', 10); SELECT times.time_id, product, quantity FROM inventory PARTITION BY (product) RIGHT OUTER JOIN times ON (times.time_id = inventory.time_id) WHERE times.time_id BETWEEN TO_DATE('01/04/01', 'DD/MM/YY') AND TO_DATE('06/04/01', 'DD/MM/YY') ORDER BY 2,1; TIME_ID PRODUCT QUANTITY --------- ---------- ---------- 01-APR-01 bottle 10 02-APR-01 bottle 03-APR-01 bottle 04-APR-01 bottle 05-APR-01 bottle 06-APR-01 bottle 10 01-APR-01 can 10 02-APR-01 can 03-APR-01 can 04-APR-01 can 10 05-APR-01 can 06-APR-01 can 12 rows selected.
The data is now more dense along the time dimension for each partition of the product dimension. However, each of the newly added rows within each partition is null in the quantity column. It is more useful to see the nulls replaced by the preceding non-NULL value in time order. You can achieve this by applying the analytic function LAST_VALUE on top of the query result:
现在，产品维度的每个分区的数据在时间维度上更加密集。但是，每个分区中新添加的每一行在数量列中均为空。查看按时间顺序将空值替换为前面的非 NULL 值会更有用。您可以通过在查询结果之上应用分析函数 LAST_VALUE 来实现此目的：
SELECT time_id, product, LAST_VALUE(quantity IGNORE NULLS) OVER (PARTITION BY product ORDER BY time_id) quantity FROM ( SELECT times.time_id, product, quantity FROM inventory PARTITION BY (product) RIGHT OUTER JOIN times ON (times.time_id = inventory.time_id) WHERE times.time_id BETWEEN TO_DATE('01/04/01', 'DD/MM/YY') AND TO_DATE('06/04/01', 'DD/MM/YY')) ORDER BY 2,1; TIME_ID PRODUCT QUANTITY --------- ---------- ---------- 01-APR-01 bottle 10 02-APR-01 bottle 10 03-APR-01 bottle 10 04-APR-01 bottle 10 05-APR-01 bottle 10 06-APR-01 bottle 10 01-APR-01 can 10 02-APR-01 can 10 03-APR-01 can 10 04-APR-01 can 10 05-APR-01 can 10 06-APR-01 can 10 12 rows selected.
See Also: 也可以看看：
Oracle Database Data Warehousing Guide for an expanded discussion on filling gaps in time series calculations and examples of usage
Oracle 数据库数据仓库指南，对填补时间序列计算中的空白和使用示例进行了扩展讨论
Using Antijoins: Example 使用反连接：示例
The following example selects a list of employees who are not in a particular set of departments:
以下示例选择不属于特定部门组的员工列表：
SELECT * FROM employees WHERE department_id NOT IN (SELECT department_id FROM departments WHERE location_id = 1700) ORDER BY last_name;
Using Semijoins: Example 使用半连接：示例
In the following example, only one row needs to be returned from the departments table, even though many rows in the employees table might match the subquery. If no index has been defined on the salary column in employees, then a semijoin can be used to improve query performance.
在以下示例中，只需从 departments 表返回一行，即使 employees 表中的许多行可能与子查询匹配。如果 employees 中的 salary 列没有定义索引，则可以使用半连接来提高查询性能。
SELECT * FROM departments WHERE EXISTS (SELECT * FROM employees WHERE departments.department_id = employees.department_id AND employees.salary > 2500) ORDER BY department_name;
Using CROSS APPLY and OUTER APPLY Joins: Examples
使用 CROSS APPLY 和 OUTER APPLY 连接：示例
The following statement uses the CROSS APPLY clause of the cross_outer_apply_clause. The join returns only rows from the table on the left side of the join (departments) that produce a result from the inline view on the right side of the join. That is, the join returns only the departments that have at least one employee. The WHERE clause restricts the result set to include only the Marketing, Operations, and Public Relations departments. However, the Operations department is not included in the result set because it has no employees.
以下语句使用 cross_outer_apply_clause 的 CROSS APPLY 子句。连接仅返回连接左侧表中的行 ( departments )，这些行从连接右侧的内联视图生成结果。也就是说，联接仅返回至少拥有一名员工的部门。 WHERE 子句将结果集限制为仅包括营销、运营和公共关系部门。但是，Operations 部门不包含在结果集中，因为它没有员工。
SELECT d.department_name, v.employee_id, v.last_name FROM departments d CROSS APPLY (SELECT * FROM employees e WHERE e.department_id = d.department_id) v WHERE d.department_name IN ('Marketing', 'Operations', 'Public Relations') ORDER BY d.department_name, v.employee_id; DEPARTMENT_NAME EMPLOYEE_ID LAST_NAME ------------------------------ ----------- ------------------------- Marketing 201 Hartstein Marketing 202 Fay Public Relations 204 Baer
The following statement uses the OUTER APPLY clause of the cross_outer_apply_clause. The join returns all rows from the table on the left side of the join (departments) regardless of whether they produce a result from the inline view on the right side of the join. That is, the join returns all departments regardless of whether the departments have any employees. The WHERE clause restricts the result set to include only the Marketing, Operations, and Public Relations departments. The Operations department is included in the result set even though it has no employees.
以下语句使用 cross_outer_apply_clause 的 OUTER APPLY 子句。连接返回连接左侧表中的所有行 ( departments )，无论它们是否从连接右侧的内联视图生成结果。也就是说，连接会返回所有部门，无论这些部门是否有员工。 WHERE 子句将结果集限制为仅包括营销、运营和公共关系部门。尽管运营部门没有员工，但它仍包含在结果集中。
SELECT d.department_name, v.employee_id, v.last_name FROM departments d OUTER APPLY (SELECT * FROM employees e WHERE e.department_id = d.department_id) v WHERE d.department_name IN ('Marketing', 'Operations', 'Public Relations') ORDER by d.department_name, v.employee_id; DEPARTMENT_NAME EMPLOYEE_ID LAST_NAME ------------------------------ ----------- ------------------------- Marketing 201 Hartstein Marketing 202 Fay Operations Public Relations 204 Baer
Using Lateral Inline Views: Example
使用横向内联视图：示例
The following example shows a join with two operands. The second operand is an inline view that specifies the first operand, table e, in the WHERE clause. This results in an error.
以下示例显示了具有两个操作数的联接。第二个操作数是一个内联视图，它在 WHERE 子句中指定第一个操作数表 e 。这会导致错误。
SELECT * FROM employees e, (SELECT * FROM departments d WHERE e.department_id = d.department_id); ORA-00904: "E"."DEPARTMENT_ID": invalid identifier
The following example shows a join with two operands. The second operand is a lateral inline view that specifies the first operand, table e, in the WHERE clause and succeeds without an error.
以下示例显示了具有两个操作数的联接。第二个操作数是横向内联视图，它在 WHERE 子句中指定第一个操作数表 e ，并且成功且没有错误。
SELECT * FROM employees e, LATERAL(SELECT * FROM departments d WHERE e.department_id = d.department_id);
Table Collections: Examples
表集合：示例
You can perform DML operations on nested tables only if they are defined as columns of a table. Therefore, when the query_table_expr_clause of an INSERT, DELETE, or UPDATE statement is a table_collection_expression, the collection expression must be a subquery that uses the TABLE collection expression to select the nested table column of the table. The examples that follow are based on the following scenario:
仅当嵌套表定义为表的列时，才可以对嵌套表执行 DML 操作。因此，当 INSERT 、 DELETE 或 UPDATE 语句的 query_table_expr_clause 是 table_collection_expression 时，集合表达式必须是使用 TABLE 集合表达式来选择表的嵌套表列的子查询。以下示例基于以下场景：
Suppose the database contains a table hr_info with columns department_id, location_id, and manager_id, and a column of nested table type people which has last_name, department_id, and salary columns for all the employees of each respective manager:
假设数据库包含一个表 hr_info ，其中包含 department_id 、 location_id 和 manager_id 列，以及嵌套表类型 people 其中包含每个经理的所有员工的 last_name 、 department_id 和 salary 列：
CREATE TYPE people_typ AS OBJECT ( last_name VARCHAR2(25), department_id NUMBER(4), salary NUMBER(8,2)); / CREATE TYPE people_tab_typ AS TABLE OF people_typ; / CREATE TABLE hr_info ( department_id NUMBER(4), location_id NUMBER(4), manager_id NUMBER(6), people people_tab_typ) NESTED TABLE people STORE AS people_stor_tab; INSERT INTO hr_info VALUES (280, 1800, 999, people_tab_typ());
The following example inserts into the people nested table column of the hr_info table for department 280:
以下示例插入到部门 280 的 hr_info 表的 people 嵌套表列中：
INSERT INTO TABLE(SELECT h.people FROM hr_info h WHERE h.department_id = 280) VALUES ('Smith', 280, 1750);
The next example updates the department 280 people nested table:
下一个示例更新部门 280 people 嵌套表：
UPDATE TABLE(SELECT h.people FROM hr_info h WHERE h.department_id = 280) p SET p.salary = p.salary + 100;
The next example deletes from the department 280 people nested table:
下一个示例从部门 280 people 嵌套表中删除：
DELETE TABLE(SELECT h.people FROM hr_info h WHERE h.department_id = 280) p WHERE p.salary > 1700;
Collection Unnesting: Examples
集合解除嵌套：示例
To select data from a nested table column, use the TABLE collection expression to treat the nested table as columns of a table. This process is called collection unnesting.
要从嵌套表列中选择数据，请使用 TABLE 集合表达式将嵌套表视为表的列。这个过程称为集合解除嵌套。
You could get all the rows from hr_info, which was created in the preceding example, and all the rows from the people nested table column of hr_info using the following statement:
您可以使用以下命令获取在上例中创建的 hr_info 中的所有行，以及 hr_info 的 people 嵌套表列中的所有行陈述：
SELECT t1.department_id, t2.* FROM hr_info t1, TABLE(t1.people) t2 WHERE t2.department_id = t1.department_id;
Now suppose that people is not a nested table column of hr_info, but is instead a separate table with columns last_name, department_id, address, hiredate, and salary. You can extract the same rows as in the preceding example with this statement:
现在假设 people 不是 hr_info 的嵌套表列，而是一个包含列 last_name 、 department_id 、 < b4> 、 hiredate 和 salary 。您可以使用以下语句提取与前面示例中相同的行：
SELECT t1.department_id, t2.* FROM hr_info t1, TABLE(CAST(MULTISET( SELECT t3.last_name, t3.department_id, t3.salary FROM people t3 WHERE t3.department_id = t1.department_id) AS people_tab_typ)) t2;
Finally, suppose that people is neither a nested table column of table hr_info nor a table itself. Instead, you have created a function people_func that extracts from various sources the name, department, and salary of all employees. You can get the same information as in the preceding examples with the following query:
最后，假设 people 既不是表 hr_info 的嵌套表列，也不是表本身。相反，您创建了一个函数 people_func ，它从各种来源提取所有员工的姓名、部门和工资。您可以使用以下查询获取与前面示例中相同的信息：
SELECT t1.department_id, t2.* FROM hr_info t1, TABLE(CAST (people_func( ... ) AS people_tab_typ)) t2;
See Also: 也可以看看：
Oracle Database Object-Relational Developer's Guide for more examples of collection unnesting.
Oracle 数据库对象关系开发人员指南 有关集合取消嵌套的更多示例。
Using the LEVEL Pseudocolumn: Examples
使用 LEVEL 伪列：示例
The following statement returns all employees in hierarchical order. The root row is defined to be the employee whose job is AD_VP. The child rows of a parent row are defined to be those who have the employee number of the parent row as their manager number.
以下语句按层次结构顺序返回所有员工。根行定义为工作为 AD_VP 的员工。父行的子行定义为那些将父行的员工编号作为其经理编号的行。
SELECT LPAD(' ',2*(LEVEL-1)) || last_name org_chart, employee_id, manager_id, job_id FROM employees START WITH job_id = 'AD_VP' CONNECT BY PRIOR employee_id = manager_id; ORG_CHART EMPLOYEE_ID MANAGER_ID JOB_ID ------------------ ----------- ---------- ---------- Kochhar 101 100 AD_VP Greenberg 108 101 FI_MGR Faviet 109 108 FI_ACCOUNT Chen 110 108 FI_ACCOUNT Sciarra 111 108 FI_ACCOUNT Urman 112 108 FI_ACCOUNT Popp 113 108 FI_ACCOUNT Whalen 200 101 AD_ASST Mavris 203 101 HR_REP Baer 204 101 PR_REP Higgins 205 101 AC_MGR Gietz 206 205 AC_ACCOUNT De Haan 102 100 AD_VP Hunold 103 102 IT_PROG Ernst 104 103 IT_PROG Austin 105 103 IT_PROG Pataballa 106 103 IT_PROG Lorentz 107 103 IT_PROG
The following statement is similar to the previous one, except that it does not select employees with the job FI_MGR.
以下语句与上一条语句类似，只是它不选择担任 FI_MGR 职位的员工。
SELECT LPAD(' ',2*(LEVEL-1)) || last_name org_chart, employee_id, manager_id, job_id FROM employees WHERE job_id != 'FI_MGR' START WITH job_id = 'AD_VP' CONNECT BY PRIOR employee_id = manager_id; ORG_CHART EMPLOYEE_ID MANAGER_ID JOB_ID ------------------ ----------- ---------- ---------- Kochhar 101 100 AD_VP Faviet 109 108 FI_ACCOUNT Chen 110 108 FI_ACCOUNT Sciarra 111 108 FI_ACCOUNT Urman 112 108 FI_ACCOUNT Popp 113 108 FI_ACCOUNT Whalen 200 101 AD_ASST Mavris 203 101 HR_REP Baer 204 101 PR_REP Higgins 205 101 AC_MGR Gietz 206 205 AC_ACCOUNT De Haan 102 100 AD_VP Hunold 103 102 IT_PROG Ernst 104 103 IT_PROG Austin 105 103 IT_PROG Pataballa 106 103 IT_PROG Lorentz 107 103 IT_PROG
Oracle Database does not return the manager Greenberg, although it does return employees who are managed by Greenberg.
Oracle 数据库不返回经理 Greenberg ，尽管它返回由 Greenberg 管理的员工。
The following statement is similar to the first one, except that it uses the LEVEL pseudocolumn to select only the first two levels of the management hierarchy:
以下语句与第一个语句类似，不同之处在于它使用 LEVEL 伪列仅选择管理层次结构的前两级：
SELECT LPAD(' ',2*(LEVEL-1)) || last_name org_chart, employee_id, manager_id, job_id FROM employees START WITH job_id = 'AD_PRES' CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 2; ORG_CHART EMPLOYEE_ID MANAGER_ID JOB_ID ------------------ ----------- ---------- ---------- King 100 AD_PRES Kochhar 101 100 AD_VP De Haan 102 100 AD_VP Raphaely 114 100 PU_MAN Weiss 120 100 ST_MAN Fripp 121 100 ST_MAN Kaufling 122 100 ST_MAN Vollman 123 100 ST_MAN Mourgos 124 100 ST_MAN Russell 145 100 SA_MAN Partners 146 100 SA_MAN Errazuriz 147 100 SA_MAN Cambrault 148 100 SA_MAN Zlotkey 149 100 SA_MAN Hartstein 201 100 MK_MAN
Using Distributed Queries: Example
使用分布式查询：示例
This example shows a query that joins the departments table on the local database with the employees table on the remote database:
此示例显示将本地数据库上的 departments 表与 remote 数据库上的 employees 表连接起来的查询：
SELECT last_name, department_name FROM employees@remote, departments WHERE employees.department_id = departments.department_id;
Using Correlated Subqueries: Examples
使用相关子查询：示例
The following examples show the general syntax of a correlated subquery:
以下示例显示了相关子查询的一般语法：
SELECT select_list FROM table1 t_alias1 WHERE expr operator (SELECT column_list FROM table2 t_alias2 WHERE t_alias1.column operator t_alias2.column); UPDATE table1 t_alias1 SET column = (SELECT expr FROM table2 t_alias2 WHERE t_alias1.column = t_alias2.column); DELETE FROM table1 t_alias1 WHERE column operator (SELECT expr FROM table2 t_alias2 WHERE t_alias1.column = t_alias2.column);
The following statement returns data about employees whose salaries exceed their department average. The following statement assigns an alias to employees, the table containing the salary information, and then uses the alias in a correlated subquery:
以下语句返回有关工资超过部门平均水平的员工的数据。以下语句为包含工资信息的表 employees 分配一个别名，然后在相关子查询中使用该别名：
SELECT department_id, last_name, salary FROM employees x WHERE salary > (SELECT AVG(salary) FROM employees WHERE x.department_id = department_id) ORDER BY department_id;
For each row of the employees table, the parent query uses the correlated subquery to compute the average salary for members of the same department. The correlated subquery performs the following steps for each row of the employees table:
对于 employees 表的每一行，父查询使用相关子查询来计算同一部门成员的平均工资。相关子查询对 employees 表的每一行执行以下步骤：
-
The
department_idof the row is determined.
该行的department_id已确定。 -
The
department_idis then used to evaluate the parent query.
然后使用department_id来评估父查询。 -
If the salary in that row is greater than the average salary of the departments of that row, then the row is returned.
如果该行的工资大于该行部门的平均工资，则返回该行。
The subquery is evaluated once for each row of the employees table.
对于 employees 表的每一行，子查询都会计算一次。
Selecting from the DUAL Table: Example
从 DUAL 表中选择：示例
The following statement returns the current date:
以下语句返回当前日期：
SELECT SYSDATE FROM DUAL;
You could select SYSDATE from the employees table, but the database would return 14 rows of the same SYSDATE, one for every row of the employees table. Selecting from DUAL is more convenient.
您可以从 employees 表中选择 SYSDATE ，但数据库将返回 14 行相同的 SYSDATE ，每行对应 employees 中选择更方便。
Selecting Sequence Values: Examples
选择序列值：示例
The following statement increments the employees_seq sequence and returns the new value:
以下语句递增 employees_seq 序列并返回新值：
SELECT employees_seq.nextval FROM DUAL;
The following statement selects the current value of employees_seq:
以下语句选择 employees_seq 的当前值：
SELECT employees_seq.currval FROM DUAL;
Row Pattern Matching: Example
行模式匹配：示例
This example uses row pattern matching to query stock price data. The following statements create table Ticker and inserts stock price data into the table:
此示例使用行模式匹配来查询股票价格数据。以下语句创建表 Ticker 并将股票价格数据插入表中：
CREATE TABLE Ticker (SYMBOL VARCHAR2(10), tstamp DATE, price NUMBER); INSERT INTO Ticker VALUES('ACME', '01-Apr-11', 12); INSERT INTO Ticker VALUES('ACME', '02-Apr-11', 17); INSERT INTO Ticker VALUES('ACME', '03-Apr-11', 19); INSERT INTO Ticker VALUES('ACME', '04-Apr-11', 21); INSERT INTO Ticker VALUES('ACME', '05-Apr-11', 25); INSERT INTO Ticker VALUES('ACME', '06-Apr-11', 12); INSERT INTO Ticker VALUES('ACME', '07-Apr-11', 15); INSERT INTO Ticker VALUES('ACME', '08-Apr-11', 20); INSERT INTO Ticker VALUES('ACME', '09-Apr-11', 24); INSERT INTO Ticker VALUES('ACME', '10-Apr-11', 25); INSERT INTO Ticker VALUES('ACME', '11-Apr-11', 19); INSERT INTO Ticker VALUES('ACME', '12-Apr-11', 15); INSERT INTO Ticker VALUES('ACME', '13-Apr-11', 25); INSERT INTO Ticker VALUES('ACME', '14-Apr-11', 25); INSERT INTO Ticker VALUES('ACME', '15-Apr-11', 14); INSERT INTO Ticker VALUES('ACME', '16-Apr-11', 12); INSERT INTO Ticker VALUES('ACME', '17-Apr-11', 14); INSERT INTO Ticker VALUES('ACME', '18-Apr-11', 24); INSERT INTO Ticker VALUES('ACME', '19-Apr-11', 23); INSERT INTO Ticker VALUES('ACME', '20-Apr-11', 22);
The following query uses row pattern matching to find all cases where stock prices dipped to a bottom price and then rose. This is generally called a V-shape. The resulting output contains only three rows because the query specifies ONE ROW PER MATCH, and three matches were found.
以下查询使用行模式匹配来查找股票价格跌至底部价格然后上涨的所有情况。这通常称为V形。结果输出仅包含三行，因为查询指定 ONE ROW PER MATCH ，并且找到了三个匹配项。
SELECT * FROM Ticker MATCH_RECOGNIZE ( PARTITION BY symbol ORDER BY tstamp MEASURES STRT.tstamp AS start_tstamp, LAST(DOWN.tstamp) AS bottom_tstamp, LAST(UP.tstamp) AS end_tstamp ONE ROW PER MATCH AFTER MATCH SKIP TO LAST UP PATTERN (STRT DOWN+ UP+) DEFINE DOWN AS DOWN.price < PREV(DOWN.price), UP AS UP.price > PREV(UP.price) ) MR ORDER BY MR.symbol, MR.start_tstamp; SYMBOL START_TST BOTTOM_TS END_TSTAM ---------- --------- --------- --------- ACME 05-APR-11 06-APR-11 10-APR-11 ACME 10-APR-11 12-APR-11 13-APR-11 ACME 14-APR-11 16-APR-11 18-APR-11