oracle SQL 查询可以执行从表中选择的字符串查询吗?
使用 oracle SQL 时，是否可以基于子查询中的 text_string 运行查询?一个例子可能会阐明我想要做什么
When using oracle SQL is it possible to run a query based on a text_string from a subquery? An example might clarify what I'm trying to do
select count(sql_text), sql_text
from
(select sql_text
from query_table) sql_table
group by sql_text;
外部查询旨在计算从 query_table 中检索到的每个查询的结果数.
The outer query is intended to count the number of results for each query retrieved from the query_table.
有什么方法可以在同一个查询中执行我从 query_table 中检索到的 sql 语句吗?
Is there some way I can execute the sql statements I retrieved from my query_table in the same query?
谢谢
我能够使用 dbms_xmlgen.get_xml() 函数从表中查询 sql.我想任何导致 sql 被解析和执行的命令都可以工作.话虽如此，以下是我能够完成任务的通用代码:
I was able to query sql from a table using the dbms_xmlgen.get_xml() function. I suppose that any command which caused the sql to be parsed and executed would work. That being said, here's the generic code that I was able to accomplish things with:
select to_number (
extractvalue(
xmltype(
dbms_xmlgen.getxml('select count(*) c from '|| table_name)), '/ROWSET/ROW/C'))counter,
sql_text
from
(select '('||sql_text||')' table_name
from query_table) sql_table;
虽然这可能不是最优雅的做事方式，但它确实有效，而且是一个单一的 sql 语句.
While perhaps not the most elegant way to do things, it works and is a single sql statement.
推荐答案
通常，这不是一个特别好的设计——将 SQL 存储在表中并动态执行它会引入各种安全和维护问题.
Generally, this isn't a particularly good design-- storing SQL in tables and dynamically executing it introduces all sorts of security and maintenance issues.
很有可能(虽然在星期五开始太晚了，我无法尝试弄清楚)来做一个非常酷的 XML 查询，就像 此查询对将在一个查询中完成所有这些的架构.
It is probably possible (though it's way too late on a Friday that started way too early for me to try to figure it out) to do a really cool XML query along the lines of this query that runs a count(*) against every table in the schema that would do this all in one query.
不过，对于绝大多数程序员来说，更简单的方法是循环查询，一次运行一个，然后将结果存储在某处.例如，可能会将局部变量添加到计数集合中.
For the vast majority of programmers, though, the simpler approach would be to loop over the queries, run them one at a time, and store the results somewhere. Potentially the local variable would be added to a collection of counts, for example.
FOR q IN (SELECT sql_text FROM query_table)
LOOP
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM (' || q.sql_text || ')'
INTO some_local_variable;
<<do something with the local variable>>
END LOOP;
由于您正在尝试创建视图，因此您可以采用此逻辑并将其放入流水线表函数中.您将执行 PIPE ROW 以在循环内返回数据.然后可以在流水线表函数之上创建您的视图.
Since you're trying to create a view, you could take this logic and put it in a pipelined table function. You'd do a PIPE ROW to return data within the loop. Your view could then be created on top of the pipelined table function.
相关文章