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.

可能有可能(尽管星期五来不及开始，以至于我想找出答案还为时过早)，按照

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屋！