Oracle：根据数据集替换选项字符串-这可能吗？

您可以使用递归CTE在SQL中执行类似循环的查询，并在每次迭代时替换新令牌，因此，您可以替换所有令牌。我知道在Oracle中的SQL语句中执行动态查询的唯一方法是DBMS_XMLGEN包，因此无需PL / SQL，您就可以评估表达式并根据结果值进行过滤。但是所有这些对于具有模式和选项的低基数表都是可行的。

这是代码：

with a as (
  select 1 as id, '{([option1]+[option2])*([option3]+[option4])}' as pattern from dual union all
  select 2 as id, '{([option1]+[option2])*([option3]+[option4])*([option6]+[option7])}' as pattern from dual union all
  select 3 as id, '{[option1]+[option6]}' as pattern from dual union all
  select 4 as id, '{([option1]+[option2])*([option8]+[option9])}' as pattern from dual union all
  select 5 as id, '{([option1]+[option2])*[option4]}' as pattern from dual union all
  select 6 as id, '{[option10]}]' as pattern from dual
)
, opt as (
  select 'option1' as opt, 3653265 as val from dual union all
  select 'option2' as opt, 26452 as val from dual union all
  select 'option3' as opt, 73552 as val from dual union all
  select 'option3' as opt, 100 as val from dual union all
  select 'option4' as opt, 1235 as val from dual union all
  select 'option5' as opt, 42565 as val from dual union all
  select 'option6' as opt, 2330 as val from dual union all
  select 'option7' as opt, 544 as val from dual union all
  select 'option9' as opt, 2150 as val from dual
)
, opt_ordered as (
  /*Order options to iterate over*/
  select opt.*, row_number() over(order by 1) as rn
  from opt
)
, rec (id, pattern, repl_pattern, lvl) as (
  select
    id,
    pattern,
    pattern as repl_pattern,
    0 as lvl
  from a
  
  union all
  
  select
    r.id,
    r.pattern,
    /*Replace each part at new step*/
    replace(r.repl_pattern, '[' || o.opt || ']', o.val),
    r.lvl + 1
  from rec r
    join opt_ordered o
      on r.lvl + 1 = o.rn
)
, out_prepared as (
  select
    rec.*,
    case
      when instr(repl_pattern, '[') = 0
      /*When there's no more not parsed expressions, then we can try to evaluate them*/
      then dbms_xmlgen.getxmltype(
        'select ' || replace(replace(repl_pattern, '{', ''), '}', '')
        || ' as v from dual'
      )
      /*Otherwise SQL statement will fail*/
    end as parsed_expr
  from rec
  /*Retrieve the last step*/
  where lvl = (select max(rn) from opt_ordered)
)
select
  id,
  pattern,
  repl_pattern,
  extractvalue(parsed_expr, '/ROWSET/ROW/V') as calculated_value
from out_prepared o
where extractvalue(parsed_expr, '/ROWSET/ROW/V') > 0

ID | 模式| REPL_PATTERN | CALCULATED_VALUE- 
：| ：------------------------------------------------- ----------------- | ：---------------------------------------- | ：--------------- 
 1 | {（[option1] + [option2]）*（[option3] + [option4]）} | {（3653265 + 26452）*（73552 + 1235）} | 275194995279     
 2 | {（[选项1] + [选项2]）*（[选项3] + [选项4]）*（[选项6] + [选项7]）} | {（3653265 + 26452）*（73552 + 1235）*（2330 + 544）} | 790910416431846 
 3 | {[option1] + [option6]} | {3653265 + 2330} | 3655595         
 5 | {（[option1] + [option2]）* [option4]} | {（3653265 + 26452）* 1235} | 4544450495      

db <>在这里拨弄