如何使用存储在表中的 SQL 语句作为另一个语句的一部分？

在我们的 Oracle 数据库中，我们有一个名为 RULES 的表，其中有一个名为 SQLQUERY 的字段。该字段是一个 varchar，存储了 SQL 语句。 PK 是 DM_PROJECT。

存储的典型语句可以是

select ACCOUNTNUMBER from CUSTOMERS where ACCUMULATED_SALES > 500000

我想做这样的事情：

select 
  * 
from 
  customers 
where
     accountnumber like 'A%'
  or salesregion = 999
  or accountnumber in
     (
       <run the query SQLQUERY from RULES where DM_PROJECT=:DM_PROJECT>
     )

这可以做到吗？

（次要关注点：如果存储的查询使用自己的变量，是否可以完成，例如

select ACCOUNTNUMBER from CUSTOMERS where ACCUMULATEDSALES > :LIMIT 

)

确实很有趣的问题。这有两个方面。

首先是这是否是一个好主意。问题是，RULE 中的文本对数据库是不可见的。它不会出现在依赖性检查中，因此影响分析变得困难。显然（或者可能不明显）规则的语法只能通过运行来验证。这可能会在添加规则时产生问题。所以这也可能是一个维护问题。而且，正如我们将看到的，一旦超越了简单的查询，就很难进行编程。

第二个方面是是否可能。这是。我们需要使用动态SQL；将动态 SQL 与静态 SQL 结合起来是可行的，但比较粗糙。

create table rules (project_name varchar2(30)
                    , rule_name varchar2(30)
                    , rule_text varchar2(4000) )
/
insert into rules 
values ('SO', 'ACC_SALES'
        , 'select ACCOUNTNUMBER from CUSTOMERS where ACCUMULATED_SALES > 500000 ')
/
create table customers (accountnumber number(7,0) 
                        , name varchar2(20)
                        , accumulated_sales number
                        , sales_region varchar2(3))
/
insert into customers values (111, 'ACME Industries', 450000, 'AA')
/
insert into customers values (222, 'Tyrell Corporation', 550000, 'BB')
/
insert into customers values (333, 'Lorax Textiles Co', 500000, 'BB')
/

该函数获取一条规则，执行它并返回一个数字数组。

create or replace type rule_numbers as table of number
/

create or replace function exec_numeric_rule
    ( p_pname in rules.project_name%type
      ,  p_rname in rules.rule_name%type )
    return rule_numbers
is
    return_value rule_numbers;
    stmt rules.rule_text%type;
begin
    select rule_text into stmt
    from rules
    where project_name = p_pname
    and   rule_name = p_rname;

    execute immediate stmt 
        bulk collect into return_value;

    return return_value;
end exec_numeric_rule;
/

我们来测试一下。

SQL> select * from customers
  2  where accountnumber in
  3      ( select * from table (exec_numeric_rule('SO', 'ACC_SALES')))
  4  /

ACCOUNTNUMBER NAME                 ACCUMULATED_SALES SAL
------------- -------------------- ----------------- ---
          222 Tyrell Corporation              550000 BB

1 row selected.

SQL>

这是唯一正确的答案。

但现在我们来回答你的补充问题：

“如果存储的查询可以完成吗 使用自己的变量”

是的，可以，但事情开始变得更加脆弱。新规则：

insert into rules 
values ('SO', 'ACC_SALES_VAR'
        , 'select ACCOUNTNUMBER from CUSTOMERS where ACCUMULATED_SALES > :LMT ')
/

我们修改函数来应用它：

create or replace function exec_numeric_rule
    ( p_pname in rules.project_name%type
      , p_rname in rules.rule_name%type
      , p_variable in number := null)
    return rule_numbers
is
    return_value rule_numbers;
    stmt rules.rule_text%type;
begin
    select rule_text into stmt
    from rules
    where project_name = p_pname
    and   rule_name = p_rname;

    if p_variable is null then
        execute immediate stmt 
            bulk collect into return_value;
    else
        execute immediate stmt 
            bulk collect into return_value
            using p_variable;        
    end if;

    return return_value;
end exec_numeric_rule;
/

手指交叉！

SQL> select * from customers
  2  where accountnumber in
  3      ( select * from table (exec_numeric_rule('SO', 'ACC_SALES_VAR', 480000)))
  4  /

ACCOUNTNUMBER NAME                 ACCUMULATED_SALES SAL
------------- -------------------- ----------------- ---
          222 Tyrell Corporation              550000 BB
          333 Lorax Textiles Co               500000 BB

2 rows selected.

SQL>

好吧，所以它仍然有效。但你可以看到排列并不友好。如果您想将多个参数传递给 RULE，那么您需要更多的函数或更复杂的内部逻辑。如果您想返回日期或字符串集，则需要更多函数。如果您想传递不同 data_types 的 P_VARIABLE 参数，您可能需要更多函数。您当然需要一些类型检查先决条件。

这又回到了我的第一点：是的，这是可以做到的，但是值得这么麻烦吗？