执行存储在表中的SQL查询 [英] execute SQL query stored in a table

问题描述

我有一张表，其中的一列存储SQL查询返回的ID，或者存储逗号分隔的ID.

I have a table having one of the columns that stores SQL query returning ids or it stores comma separated ids.

创建表以存储查询或ID(以分隔)

create table to store query or ids(separated by ,)

create table test1
(
    name varchar(20) primary key,
    stmt_or_value varchar(500),
    type varchar(50)
);

insert into test1 (name, stmt_or_value, type) 
values ('first', 'select id from data where id = 1;','SQL_QUERY')
insert into test1 (name, stmt_or_value, type) 
values ('second', '1,2,3,4','VALUE')

数据表如下

create table data
(
    id number,
    subject varchar(500)
);

insert into data (id, subject) values (1, 'test subject1');
insert into data (id, subject) values (2, 'test subject2');
insert into data (id, subject) values (3, 'test subject2');

我无法制定查询，该查询将在执行存储的sql或基于name的值解析存储的id之后返回值.

I am not able to formulate query that will return values after either executing stored sql or parsing stored ids based on the value of name.

select id, subject 
  from data 
 where id in( EXECUTE IMMEDIATE stmt_or_value 
              where type='SQL_QUERY'  
                and name = 'first') or 
            ( parse and return ids 
               from stmt_or_value 
              where type='VALUE'  
                and name = 'second')

在这方面，你能帮我吗? 逗号分隔值的解析完成后，我基本上在查询的第一部分下面需要帮助: (立即执行stmt_or_value 其中type ='SQL_QUERY'
和名称="first")

Could you please help me in this. Parsing comma separated value is done, I basically need help in below first part of the query: ( EXECUTE IMMEDIATE stmt_or_value where type='SQL_QUERY'
and name = 'first')

推荐答案

这似乎是一个非常特殊的要求，并且很难以健壮的方式解决. STMT_OR_VALUE是一栏两用法"反模式的实施例.此外，解决STMT_OR_VALUE需要流程控制逻辑和动态SQL的使用.因此，它不能成为纯粹的SQL解决方案:您需要使用PL/SQL来组装和执行动态查询.

This seems a very peculiar requirement, and one which will be difficult to solve in a robust fashion. STMT_OR_VALUE is the embodiment of the One Column Two Usages anti-pattern. Furthermore, resolving STMT_OR_VALUE requires flow control logic and the use of dynamic SQL. Consequently it cannot be a pure SQL solution: you need to use PL/SQL to assemble and execute the dynamic query.

这是解决方案的概念证明.我选择了一个可以从SQL调用的函数.这取决于一个假设:您插入TEST1.STMT_OR_VALUE的每个查询字符串单个数字列的投影，每个值字符串仅数字数据的CSV .使用此条件很容易构造一个函数，该函数可以执行动态查询或将字符串标记为一系列数字.两者都被批量收集到一个嵌套表中:

Here is a proof of concept for a solution. I have opted for a function which you can call from SQL. It depends on one assumption: every query string you insert into TEST1.STMT_OR_VALUE has a projection of a single numeric column and every value string is a CSV of numeric data only. With this proviso it is simple to construct a function which either executes a dynamic query or tokenizes the string into a series of numbers; both of which are bulk collected into a nested table:

create or replace function get_ids (p_name in test1.name%type) 
  return sys.odcinumberlist
is
  l_rec test1%rowtype;
  return_value sys.odcinumberlist;
begin

  select * into l_rec
  from test1
  where name = p_name;

  if l_rec.type = 'SQL_QUERY' then 
    -- execute a query
    execute immediate l_rec.stmt_or_value
      bulk collect into return_value;
  else
    -- tokenize a string
    select xmltab.tkn
    bulk collect into return_value
    from ( select l_rec.stmt_or_value from dual) t
        , xmltable(  'for $text in ora:tokenize($in, ",") return $text'
                      passing stmt_or_value as "in"
                      columns tkn number path '.'
                   ) xmltab;
  end if;
  return return_value;
end;
/

请注意，执行动态SQL语句的方法不止一种，还有多种将CSV标记为一系列数字的方法.我的决定是任意的:请随时在此处替换您喜欢的方法.

Note there is more than one way of executing a dynamic SQL statement and a multiplicity of ways to tokenize a CSV into a series of numbers. My decisions are arbitrary: feel free to substitute your preferred methods here.

可以通过table()调用来调用此函数:

This function can be invoked with a table() call:

select * 
from data
where id in ( select * from table(get_ids('first'))) -- execute query
or    id in ( select * from table(get_ids('second'))) -- get string of values
/

这种方法的最大好处是它封装了围绕STMT_OR_VALUE的求值逻辑，并且隐藏了Dynamic SQL的使用.因此，很容易在保留可读性的同时在任何SQL语句中使用它，或者添加更多的机制来生成一组ID.

The big benefit of this approach is it encapsulates the logic around the evaluation of STMT_OR_VALUE and hides use of Dynamic SQL. Consequently it is easy to employ it in any SQL statement whilst retaining readability, or to add further mechanisms for generating a set of IDs.

但是，该解决方案很脆弱.仅当test1表中的值符合规则时，它才有效.也就是说，它们不仅必须可转换为单个数字流，而且SQL语句必须有效且可由EXECUTE IMMEDIATE执行.例如，问题的样本数据中的尾部分号无效，并且会导致EXECUTE IMMEDIATE投掷.动态SQL很难，因为它将编译错误转换为运行时错误.

However, this solution is brittle. It will only work if the values in the test1 table obey the rules. That is, not only must they be convertible to a stream of single numbers but the SQL statements must be valid and executable by EXECUTE IMMEDIATE. For instance, the trailing semi-colon in the question's sample data is invalid and would cause EXECUTE IMMEDIATE to hurl. Dynamic SQL is hard not least because it converts compilation errors into runtime errors.

这篇关于执行存储在表中的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！