oracle ORA-01731: 遇到圆形视图定义
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1501653/
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
ORA-01731: circular view definition encountered
提问by Omnipresent
we are migrating over to oracle from sql server side.
我们正在从 sql server 端迁移到 oracle。
on sqlserver we used to have a view like the following
在 sqlserver 上，我们曾经有过如下所示的视图
create view blah
AS
Select column1,
column2
FROM blah;
but doing this on oracle produces circular view error.
但是在 oracle 上这样做会产生循环视图错误。
is this not allowed on oracle side?
这在 oracle 方面是不允许的吗？
回答by Rob
You cannot have a view reference itself. It logically does not make sense. A view is essentially a cached query whose results are displayed as a table. How can a query refer to itself?
您不能拥有视图引用本身。这在逻辑上是没有意义的。视图本质上是一个缓存查询，其结果显示为表格。查询如何引用自身？
Indeed, circular view definitions are not allowed in Oracle. If you have a circular view definition, then you likely have a bug in your database code that should be addressed. Perhaps the translation from SQL server to Oracle was flawed and accidentally introduced this circular definition?
实际上，Oracle 中不允许循环视图定义。如果您有一个循环视图定义，那么您的数据库代码中可能有一个应该解决的错误。也许从 SQL server 到 Oracle 的转换有缺陷，不小心引入了这个循环定义？
回答by eidylon
You can actually do this in ORACLE, but it is more fragile, as you need to explicitly list the output columns of your CTE. So if you change the tables, you need to manually update the CTE.
您实际上可以在 ORACLE 中执行此操作，但它更脆弱，因为您需要明确列出 CTE 的输出列。因此，如果更改表，则需要手动更新 CTE。
Here is an example from our db, showing how to calculate the hierarchical depth of the a record...
这是我们数据库中的一个示例，展示了如何计算 a 记录的层次深度...
CREATE OR REPLACE VIEW deploy.PHARMACYDISPENSE_EX
AS
WITH SRC (
PDID, WAREID, GCN_SEQNO, QTY, UOFM, XACTDTTM, CREATEDON, PROCESSEDON,
XACTTYPE, OPDID, CLOSEDON, BYPASSEDON, BYPASSEDBY, ITEMNO, LOTNO,
EXP_DATE, VOLUMETYPE, POTYPE, DEPTH
) AS (
SELECT D.PDID, D.WAREID, D.GCN_SEQNO, D.QTY, D.UOFM, D.XACTDTTM,
D.CREATEDON, D.PROCESSEDON, D.XACTTYPE, D.OPDID, D.CLOSEDON,
D.BYPASSEDON, D.BYPASSEDBY, D.ITEMNO, D.LOTNO, D.EXP_DATE,
D.VOLUMETYPE, D.POTYPE, 0 FROM deploy.PHARMACYDISPENSE D
WHERE OPDID IS NULL
UNION ALL
SELECT D.PDID, D.WAREID, D.GCN_SEQNO, D.QTY, D.UOFM, D.XACTDTTM,
D.CREATEDON, D.PROCESSEDON, D.XACTTYPE, D.OPDID, D.CLOSEDON,
D.BYPASSEDON, D.BYPASSEDBY, D.ITEMNO, D.LOTNO, D.EXP_DATE,
D.VOLUMETYPE, D.POTYPE, (S.DEPTH + 1)
FROM deploy.PHARMACYDISPENSE D JOIN SRC S ON S.PDID = D.OPDID
)
SELECT PD.*
FROM SRC PD;
The important part here is the WITH SRC (<output column list>) AS .... You need that output column list. So it is possible, and does work, it just takes a bit more code than in SQL Server.
这里的重要部分是WITH SRC (<output column list>) AS .... 您需要该输出列列表。所以这是可能的，并且确实有效，它只需要比 SQL Server 多一点的代码。
回答by LucasH.-
回答by David
Your example is incomplete - well at least doesn't show the pertinent parts.:
您的示例不完整 - 至少没有显示相关部分。：
-- create a table
CREATE TABLE Scrap
(fieldName VARCHAR2(20));
-- create a view
CREATE VIEW ScrapVW1
AS
SELECT * FROM Scrap;
-- create a second view that uses the first view
CREATE VIEW ScrapVW2
AS
SELECT * FROM Scrap
UNION ALL
SELECT * FROM ScrapVW1;
-- recreate the first view that references the 2nd view which contains a reference to itself
CREATE OR REPLACE VIEW SCRAP_VW1
AS
SELECT * FROM ScrapVW2;
Gives a circular reference error when you try to recreate ScrapVW1. I would guess you have some unintentional name collision going on in your conversion. If it's quite complex I'd get rid of the 'CREATE OR REPLACE VIEW' syntax and just use CREATE VIEW which would then give you 'ORA-00955 Name already used' error.
尝试重新创建 ScrapVW1 时出现循环引用错误。我猜您在转换过程中会发生一些无意的名称冲突。如果它非常复杂，我会摆脱“CREATE OR REPLACE VIEW”语法，而只使用 CREATE VIEW，这会给您“ORA-00955 Name already used”错误。
