如何使用列值作为选择表达式 [英] How to use column value as select expression

问题描述

有一个表，其中包含一个包含SQL选择表达式的列.请参见以下示例:

There is a table that contains a column containing a SQL select expression. See the following example:

╔════╦════════╦═══════════════════════════════════════════╗
║ ID ║ Code   ║ Expression                                ║
║ 1  ║ B12321 ║ SELECT * FROM table WHERE code LIKE '%'   ║
║ 2  ║ A35525 ║ SELECT * FROM table WHERE code = '1234'   ║
║ 3  ║ C23213 ║ SELECT * FROM table WHERE code <> '%D'    ║
╚════╩════════╩═══════════════════════════════════════════╝

我想遍历Expression列，执行这些语句，然后将结果插入到另一个表中.这有可能吗?我找不到与此相关的问题.除此之外，我还读到了一个使用游标在表中循环浏览的方法，但是对此有很多消极的看法.

I want to loop throught the Expression column, execute those statements and insert the result into another table. Is this even possible? I can't find related questions about that. Besides that, I read about a using a cursor to loop throught the table but there is a lot of negativity about that.

如果可能，您是否可以提供有关我所遇到问题的有用链接，或者甚至可以提供一个示例代码来完成此操作.

If possible, can you provide usefull links about the question I have, or even better a sample code doing this.

推荐答案

您需要动态SQL.由于您不希望返回任何数据，因此可以通过一个小的PL/SQL过程来实现:

You want dynamic SQL. As you don't want to return any data, this is possible with a small PL/SQL procedure:

create or replace procedure insert_by_code(in_code varchar2) as
begin
  for rec in (select expression from mytable where code = in_code) loop
    execute immediate 'insert into other_table ' || rec.expression;
    commit;
  end loop;
end insert_by_code;

或带有匿名屏蔽的临时存储:

or ad hoc with an anonymous block:

begin
  for rec in (select expression from mytable where code = :code) loop
    execute immediate 'insert into other_table ' || rec.expression;
    commit;
  end loop;
end;

这篇关于如何使用列值作为选择表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！