oracle 内联视图
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1142293/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
oracle-inline view
提问by hrishi
Why inline views are used..??
为什么使用内联视图..??
回答by Tony Andrews
There are many different reasons for using inline views. Some things can't be done without inline views, for example:
使用内联视图有许多不同的原因。有些事情没有内联视图就无法完成，例如：
1) Filtering on the results of an analytic function:
1) 过滤解析函数的结果：
select ename from
( select ename, rank() over (order by sal desc) rnk
from emp
)
where rnk < 4;
2) Using ROWNUM on ordered results:
2) 对有序结果使用 ROWNUM：
select ename, ROWNUM from
( select ename
from emp
order by ename
);
Other times they just make it easier to write the SQL you want to write.
其他时候，它们只是让编写您想要编写的 SQL 变得更容易。
回答by rahul
The inline view is a construct in Oracle SQL where you can place a query in the SQL FROM, clause, just as if the query was a table name.
内联视图是 Oracle SQL 中的一种构造，您可以在其中将查询放置在 SQL FROM, 子句中，就像查询是表名一样。
Inline views provide
内联视图提供
- Bind variables can be introduced inside the statement to limit the data
- Better control over the tuning
- Visibility into the code
- 可以在语句内部引入绑定变量来限制数据
- 更好地控制调音
- 代码可见性
回答by FerranB
From the Oracle Database Conceptsdocument there are the inline view concept definition:
从Oracle数据库概念文件有该内嵌视图概念定义：
An inline view is not a schema object. It is a subquery with an alias (correlation name) that you can use like a view within a SQL statement.
内联视图不是架构对象。它是一个带有别名（相关名称）的子查询，您可以像 SQL 语句中的视图一样使用它。
About the subqueries look in Using Subqueriesfrom the Oracle SQL Referencemanual. It have a very nice pedagogic information.
关于子查询，请参阅Oracle SQL 参考手册中的使用子查询。它有一个非常好的教学信息。
Anyway, today is preferred to use the Subquery Factoring Clausethat is a more powerfull way of use inline views.
无论如何，今天最好使用子查询因子分解子句，这是一种更强大的使用内联视图的方式。
As an example of all together:
举个例子：
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 * FROM dept_costs
WHERE dept_total >
(SELECT avg FROM (SELECT SUM(dept_total)/COUNT(*) avg
FROM dept_costs)
)
ORDER BY department_name;
There you can see one of all:
在那里你可以看到其中之一：
- An inline view query:
SELECT SUM... - A correlated subquery:
SELECT avg FROM... - A subquery factoring:
dept_costs AS (...
- 内联视图查询：
SELECT SUM... - 相关子查询：
SELECT avg FROM... - 子查询分解：
dept_costs AS (...
What are they used for?:
它们有什么用途？：
- To avoid creating an intermediate view object:
CREATE VIEW ... - To simplify some queries that a view cannot be helpfull. For instance, when the view need to filter from the main query.
- 为避免创建中间视图对象：
CREATE VIEW ... - 简化一些视图无法提供帮助的查询。例如，当视图需要从主查询中过滤时。
回答by jva
To get top N ordered rows.
获取前 N 个有序行。
SELECT name, salary,
FROM (SELECT name, salary
FROM emp
ORDER BY salary DESC)
WHERE rownum <= 10;
回答by David Aldridge
An inline view can be regarded as an intermediate result set that contributes to the required data set in some way. Sometimes it is entirely a matter of improving maintainability of the code, and sometimes it is logically neccessary.
内联视图可以被视为以某种方式对所需数据集做出贡献的中间结果集。有时完全是为了提高代码的可维护性，有时在逻辑上是必要的。
回答by Chris Cameron-Mills
You will often use inline views to break your query up into logical parts which helps both readability and makes writing more complex queries a bit easier.
您将经常使用内联视图将查询分解为逻辑部分，这有助于提高可读性并使编写更复杂的查询变得更容易一些。
Jva and Tony Andrews provided some good examples of simple cases where this is useful such as Top-N or Pagination queries where you may want to perform a query and order its results before using that as a part of a larger query which in turn might feed a query doing some other processing, where the logic for these individual queries would be difficult to achieve in a single query.
Jva 和 Tony Andrews 提供了一些有用的简单案例的好例子，例如 Top-N 或 Pagination 查询，您可能希望在将其用作更大查询的一部分之前执行查询并对其结果进行排序，而后者又可能会提供执行一些其他处理的查询，其中这些单独查询的逻辑很难在单个查询中实现。
Another case they can be very useful is if you are writing a query that joins various tables together and want to perform aggregation on some of the tables, separating group functions and the processing into different inline views before performing the joins makes managing cardinality a lot easier. If you want some examples, I would be happy to provide them to make it more clear.
另一种非常有用的情况是，如果您正在编写一个将各种表连接在一起的查询，并希望对某些表执行聚合，在执行连接之前将组功能和处理分离到不同的内联视图中，从而使管理基数变得更加容易. 如果您需要一些示例，我很乐意提供它们以使其更加清晰。
Factored subqueries (where you list your queries in the WITH clause at the start of the query) and inline views also often bring performance benefits. If you need to access the results of the subquery multiple times, you only need to run it once and it can be materialized as a Global Temporary Table (how the optimizer acts isn't totally black and white so I won't go into it here but you can do your own research - for example, see http://jonathanlewis.wordpress.com/2007/07/26/subquery-factoring-2/)
分解子查询（在查询开始时在 WITH 子句中列出您的查询）和内联视图通常也会带来性能优势。如果你需要多次访问子查询的结果，你只需要运行一次，它就可以具体化为一个全局临时表（优化器的行为方式不是完全黑白的，所以我不会深入研究在这里，但您可以进行自己的研究 - 例如，请参阅http://jonathanlewis.wordpress.com/2007/07/26/subquery-factoring-2/）
