Oracle中的视图扩展

Question

我们有一些开发人员过于喜欢视图了。现在我们有了引用视图的视图，而这些视图又引用了其他视图，如此循环往复。

为了帮助我进行调整，我想要展开这些视图。

我需要一个函数，它接受一个字符串并返回一个字符串。输入字符串是查询语句，输出字符串是没有视图的相同查询语句。

CREATE OR REPLACE VIEW myView AS
SELECT * FROM emp

使用函数/存储过程“F”：

F('SELECT * FROM myView') 

...将返回：

SELECT * FROM ( SELECT * FROM emp)

---

1. 这个有没有适用于Oracle的包？
2. 有人有以下代码吗：
   1. SQL或PL/SQL语言编写的代码
   2. 其他语言编写的代码

- Stephanie Page

---

是否有视图命名约定来配合这种疯狂？ - OMG Ponies

命名规范，是的，但严格遵循并不是。 - Stephanie Page

@OMG Ponies：可以使用*DEPENDENCIES视图来确定一个视图是否依赖于另一个视图（并获取依赖的子视图名称）。当然，如果视图的名称也出现在查询文本中作为别名或注释，这可能会导致额外的麻烦... - Vincent Malgrat

@vincent... 是的，就像你说的那样。但并不像你说的那么难。视图的名称可能出现在查询中的许多位置，但唯一重要的是出现在FROM之后，并且在WHERE、GROUP BY、ORDER BY或HAVING之前（以先出现为准）。然后它将是空格前或逗号后的第一个单词。 - Stephanie Page

看起来Oracle已经听取了你的建议 :) 请查看我的更新答案。 - Jeffrey Kemp

Answer 1

简短回答：

1. 目前不支持

2. 我也不知道有没有支持的方法

更新

看起来 Oracle 12c 正好有你需要的东西：DBMS_UTILITY.expand_sql_text

http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/d_util.htm#ARPLS73973

- Jeffrey Kemp

Answer 2

您提出的一个问题是，涉及视图的查询通常有多种重写方式，因此仅扩展视图文本可能无法告诉您有关查询执行方式的大量信息。由于您的目的是优化，我建议查询的执行计划可能会为您提供实际需要的信息。这不会显示重写后的查询，但它将向您显示所有实际表以及在执行查询时如何引用它们。我所知道的查看实际执行计划的最佳方法是：

SELECT /*+ gather_plan_statistics */ * FROM myView

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'))

- Dave Costa

---

你没有理解。我不关心获取执行计划...我想要实际的SQL...视图只有一种写法。这不是多项选择...创建视图myView作为从表中选择所有*的结果。如果我传入了myView，我希望返回值是"SELECT * FROM table"。 - Stephanie Page

我明白我没有直接回答你的问题。由于已经有人指出你特定问题的答案很简单是“不”，我试图提出一个可能有帮助的替代方案。我的重点在于重写，如果你的兴趣在于调整查询，那么我的经验是仅仅扩展视图文本的效用有限，而理解查询的实际执行情况则更加有用。 - Dave Costa

Answer 3

有一些方法可以扩展视图，但输出结果太丑陋，可能没有用处。

12c有一个新的过程名为DBMS_UTILITY.EXPAND_SQL_TEXT。

11g有一个未记录的过程dbms_sql2.expand_sql_text。

以下是一个简单的例子。输出结果可能对优化器有帮助，但如果您需要人类可读的SQL语句，则可能没有多大用处。

create or replace view view1 as select 1 a, 2 b from dual;
create or replace view view2 as select a from view1;

declare
    v_output clob;
begin
    dbms_utility.expand_sql_text('select * from view2', v_output);
    dbms_output.put_line(v_output);
end;
/

Output:
SELECT "A1"."A" "A" FROM  (SELECT "A2"."A" "A" FROM  (SELECT 1 "A",2 "B" FROM "SYS"."DUAL" "A3") "A2") "A1"

- Jon Heller