oracle 基于带有“WITH”子句的 SELECT 的视图
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27901751/
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
View based on SELECT with 'WITH' clause
提问by R. Nec
I've select with 'WITH' clause:
我选择了 'WITH' 子句：
with
alias1 as (select...),
alias2 as (select ... from alias1),
alias3 as (select col1, col2 ... from alias2)
select col1,col2 from alias3
I tryied to create view using:
我尝试使用以下方法创建视图：
create view ex_view as (
with
alias1 as (select...),
alias2 as (select ... from alias1),
alias3 as (select col1, col2 ... from alias2)
select col1,col2
from alias3
)
When I tried to execute this create statement got 'unsupported use of WITH clause'
当我尝试执行此创建语句时，“不支持使用 WITH 子句”
How to create view based on my select statement properely?
如何正确地根据我的选择语句创建视图？
回答by Gordon Linoff
Try dropping the parentheses:
尝试去掉括号：
create view ex_view as
with
alias1 as (select...),
alias2 as (select ... from alias1),
alias3 as (select col1, col2 ... from alias2)
from alias3;
回答by Alex Poole
You shouldn't have the parentheses around the query; change it to:
查询周围不应有括号；将其更改为：
create view ex_view as (
with
alias1 as (select...),
alias2 as (select ... from alias1),
alias3 as (select col1, col2 ... from alias2)
select col1,col2
from alias3
)
For example:
例如：
create view ex_view as
with
alias1 as (select dummy from dual),
alias2 as (select dummy as col1, dummy as col2 from alias1),
alias3 as (select col1, col2 from alias2)
select col1,col2
from alias3;
View ex_view created.
The same construct with the parentheses around the query gets ORA-32034: unsupported use of WITH clause too.
查询周围带有括号的相同构造得到 ORA-32034：也不支持使用 WITH 子句。
If you aren't actually using any of the subqueries in more than one level of query - so the 'common' part of 'common table expression' isn't really relevant - you could use inline views instead:
如果您实际上没有在一个以上级别的查询中使用任何子查询 - 所以“公用表表达式”的“公共”部分并不是真正相关的 - 您可以使用内联视图：
create view ex_view as
select alias3.col1, alias3.col2
from (
select col1, col2
from (
select dummy as col1, dummy as col2
from (
select dummy from dual
) alias1
) alias2
) alias3;
But the withclause is valid, and often easier to read and maintain anyway.
但该with条款是有效的，而且通常更易于阅读和维护。
回答by Philippe Malera
Is there a way to create a view with a "WITH FUNCTION" clause.
有没有办法用“WITH FUNCTION”子句创建视图。
CREATE OR REPLACE VIEW test$v AS
WITH
FUNCTION with_function(p_id IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN p_id;
END;
SELECT with_function(dummy) AS dummy
FROM dual;
/
This generate an error :
这会产生一个错误：
ORA-06553: PLS-103: Encountered the symbol "end-of-file"
