oracle SQL 查询可以执行从表中选择的字符串查询吗? [英] Can an oracle SQL query execute a string query selected from a table?

问题描述

使用 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.

这篇关于oracle SQL 查询可以执行从表中选择的字符串查询吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！