在不使用CREATE的情况下在Oracle中定义VIEW [英] Define a VIEW in Oracle without using CREATE
问题描述
我没有足够的特权使用CREATE语句，我只能使用SELECT.我有一个脚本，该脚本包含三个需要运行的不同部分和计算，并且每个脚本都引用同一复杂的WITH语句选择，从而使代码变得混乱，并且很难在三个不同的位置进行维护.
I do not have sufficient privileges to use a CREATE statement, I can only SELECT. I have a script that has three distinct parts and calculations that need to run and each one references the same complicated WITH statement selection that redundantly clutters the code and is a pain to maintain in three separate locations.
我尝试创建临时表和视图，但是同样，特权不支持.有没有一种方法可以使用SQL或PL/SQL语法在不使用CREATE的情况下定义我的WITH语句一次，然后像使用其他任何表一样引用它?示例:
I have tried creating temp tables and views, but again, privileges do not support. Is there a way using either SQL or PL/SQL syntax to define my WITH statement ONCE without using CREATE, and then reference it like I would any other table? Example:
--Define the temp table
WITH tempview AS (SELECT .... FROM ...);
--First query
SELECT ... FROM tempview;
/
--Second query
SELECT ... FROM tempview;
/
--Third query
SELECT ... FROM tempview;
/
推荐答案
获得正确的权限并创建永久对象是最好的方法.听起来好像只能在一个脚本中使用此视图，这不一定会使它的创建有效性降低，但是您可能会发现，根据您的DBA和策略，调整它的难度更大.正如@DCookie所建议的那样，当然值得尝试这种方法.
Getting the correct permissions and creating permanent objects is the best approach. It sounds like this view would only be used in a single script, which doesn't necessarily make it any less valid to create it, but you might find it harder to justify depending on your DBA and policies. It's certainly worth trying that approach, as @DCookie suggested.
如果失败，则可能存在一些变通办法，具体取决于您将在其中运行此脚本的客户端.
If that fails then there may be hacky workarounds, depending on the client you will run this script in.
例如，在SQL * Plus中，滥用替换变量以获取与您描述的内容接近的内容.它使用 define命令来创建包含视图"查询的替换变量，然后在WITH子句中使用该变量. (您不能像这样替换整个with，但是无论如何，这样可能更清晰).我使用了一个简单的虚拟查询:
For instance, in SQL*Plus it's possible to abuse substitution variables to get something close to what you describe. This uses the define command to create a substitution variable that contains the 'view' query, and then uses that variable inside a WITH clause. (You can't replace the entire with like this, but it's maybe clearer like this anyway). I'm used a trivial dummy query:
define tempview_query = 'SELECT * -
FROM dual -
UNION ALL -
SELECT * -
FROM dual'
WITH tempview AS (&tempview_query)
SELECT * FROM tempview;
WITH tempview AS (&tempview_query)
SELECT * FROM tempview;
运行脚本时，产生的输出为:
When the script is run the output produced is:
D
-
X
X
2 rows selected.
D
-
X
X
2 rows selected.
我还执行过set verify off隐藏替代项，但是将其打开可能对了解正在发生的情况很有帮助.
I've also executed set verify off to hide the substitutions, but turning it on might be instructive to see what's happening.
请注意查询每行末尾的破折号；这是连续字符，并作为define文档提及:
Notice the dashes at the end of each line of the query; that's the continuation character, and as the define docs mention:
如果已定义变量的值跨越多行(使用SQL * Plus命令延续字符)，则SQL * Plus会用空格替换每个延续字符和回车符.
If the value of a defined variable extends over multiple lines (using the SQL*Plus command continuation character), SQL*Plus replaces each continuation character and carriage return with a space.
，因此set verify on显示的'new'查询将使整个视图查询显示在一行上(如果显示的话).可行的是，如果查询时间足够长，您会达到某个行长限制，但希望不会达到该点(除非您这样做；请参阅下文).
so the 'new' query shown by set verify on will have your entire view query on a single line (if you display it). It's feasible that with a long enough query you'd hit some line length limit but hopefully you won't reach that point (except you did; see below).
您可以在SQL Developer中执行相同的操作，但是继续需要使用两个破折号，因此:
You can do the same thing in SQL Developer, but there the continuation needs to use two dashes, so:
define tempview_query = 'SELECT * --
FROM dual --
UNION ALL --
SELECT * --
FROM dual'
区别在于它与SQL * Plus的延续不太一样；这里的定义必须以破折号结尾，但是不能以SQL * Plus文档描述的方式替换-因此，使用一个破折号可以使定义起作用，但查询最终将无效. (至少在4.2.0中；可能是一个错误……)通过使用两个破折号，多行定义仍然有效，破折号仍然是查询的一部分，但它们被视为注释标记；因此，它们会使替换查询看起来很奇怪(再次显示，如果显示)，但不会停止它的运行.除非有人看着v$sql，否则您不会注意到set verify off.
except it isn't quite the same as the continuation in SQL*Plus; here the define has to end with a dash, but it is not replaced in the way the SQL*Plus docs describe - so with a single dash the define works but the query ends up invalid. (At least in 4.2.0; possibly a bug...) By using two dashes the multi-line define still works, the dashes remain part of the query, but they're treated as comment markers; so they make the substituted query look odd (again, if you display it) but don't stop it working. You won't notice with set verify off unless someone looks in v$sql.
如果您的查询超过240个字符-除非它很琐碎，否则很有可能会重复-您会遇到类似这样的情况:
If your query exceeds 240 characters - which is rather likely unless it's trivial enough to repeat anyway - you'll hit something like:
string beginning "'SELECT * ..." is too long. maximum size is 240 characters.
SQL * Plus和SQL Developer都允许您使用
Both SQL*Plus and SQL Developer allow you to set a substitution variable from a query, using the column ... new_value command:
column tempalias new_value tempview_query
set termout off
select q'[SELECT *
FROM dual
UNION ALL
SELECT *
FROM dual]'
FROM dual;
set termout on
查询选择视图查询的文本作为字符串；我使用了替代报价机制，并带有
The query selects the text of your view query as a string; I've used the alternative quoting mechanism, with [] as the delimiters, so you don't have to escape any single quotes in the view query. (You need to pick a delimiter that can't appear in the query too, of course). Also note that you don't need the line continuation character any more.
查询生成的文本文字的别名为tempalias. column命令将tempview_query替换变量设置为别名列表达式包含的内容.然后，使用替换变量与前面的示例相同.
The text literal that query generates is aliased as tempalias. The column command sets the tempview_query substitution variable to whatever that aliased column expression contains. Using the substitution variable is then the same as in the previous examples.
WITH tempview AS (&tempview_query)
SELECT * FROM tempview;
set termout行仅隐藏了该生成的查询；您可以暂时省略off行以查看查询产生的内容，并且该行与您期望的视图查询完全匹配.
The set termout lines just hide that generating query; you can temporarily omit the off line to see what the query produces, and that it does exactly match the view query you expected.
其他客户端可能具有类似的机制，但是这是我真正熟悉的仅有的两个.我可能还应该重申，这有点hack，不是我必须推荐的东西……
Other clients might have similar mechanisms, but those are the only two I'm really familiar with. I should probably also reiterate that this is a bit of a hack, and not something I'd necessarily recommend...
这篇关于在不使用CREATE的情况下在Oracle中定义VIEW的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
