Oracle:替换基于数据集的选项字符串-这可能吗

Question

表中的列如下所示：

PATTERN
{([option1]+[option2])*([option3]+[option4])}
{([option1]+[option2])*([option3]+[option4])*([option6]+[option7])}
{[option1]+[option6]}
{([option1]+[option2])*([option8]+[option9])}
{([option1]+[option2])*[option4]}
{[option10]}

每个选项都有多个值。有一个表，我们称之为option_set，记录看起来像

OPTION      VALUE
option1     3653265
option2     26452
option3     73552
option3     100
option4     1235
option5     42565
option6     2330
option7     544
option9     2150

我想将选项名称替换为第一个表中的数字，当然如果存在，如果不存在，则=0。我已经在PLSQL中完成了这项工作(获取模式，浏览每个选项，如果存在-regexp_replace(，但我想知道这是否可以在SQL中完成？？我的目标是替换当前OPTION_SET的所有模式的值，并只获取记录，其中所有公式都将大于0。当然-我不能在SQL中运行这个等式，所以我想到了类似的东西

for rec in
(
SELECT...
)
loop
execute immediate '...';
if above_equation > 0 then ..
end loop;

任何想法都将不胜感激

Answer 1

您可以使用递归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|PATTERN|REPL_PATTERN| CALCULED_VALUE-：|：--------------------------------------------------------|：-----------------------------------------|：---------------1|{([option1]+[option2](*([option3]+[option4](}|{2|{([option1]+[option2](*([option3]+[option4](*([option6]+[option7](}|{3 |｛[选项1]+[选项6]｝|｛3653265+2330｝| 36555955|{([option1]+[option2](*[option4]｝|{(3653265+26452(*1235｝|4544450495

db<gt；小提琴这里

Answer 2

这里有一种方法。有很多东西要打开，所以抓紧。

我将测试数据包含在with子句中。当然，你不需要这样；简单地去除两个"；表格"；并在查询中使用实际的表名和列名。

从Oracle12.1开始，我们可以直接在顶部的with子句中定义PL/SQL函数；如果我们这样做，查询必须以斜杠(/(而不是通常的分号(；(结束。如果您的版本早于12.1，您可以单独定义函数。我使用的函数取一个"；算术表达式"；(表示复合算术运算的字符串(，并将其值作为数字返回。它使用本机动态SQL("execute immediate"语句(，这将导致查询相对较慢，因为每行都会解析不同的游标。如果速度成为一个问题，可以改变这一点，使用绑定变量(这样光标只解析一次(。

CCD_ 5子句中的递归查询将每个占位符替换为"0"的对应值；选项"；桌子我使用0，如果；占位符"；表中没有相应的选项，或者如果有，但相应的值为null。(请注意，您的样本数据显示了两次option3；这毫无意义，而且我从样本数据中删除了一次。(

我没有一次替换一个占位符，而是采取了相反的方法；假设图案可能很长；选项"；是小的，这应该更有效。即：在每一步中，我在一次遍历中替换'[optionN]'(对于给定的N(的所有出现；不存在"；具有0的选项。

请注意，递归with子句需要Oracle11.2。如果您的版本甚至早于此(尽管不应该早于此(，还有其他方法；您可能还需要在PL/SQL中执行此操作。

所以，它是——一个完整的SELECT查询：

with
function expr_eval(pattern varchar2) return number as
x number;
begin
execute immediate 'select ' || pattern || ' from dual' into x;
return x;
end;
p (id, pattern) as (
select 1, '{([option1]+[option2])*([option3]+[option4])}'                       from dual union all
select 2, '{([option1]+[option2])*([option3]+[option4])*([option6]+[option7])}' from dual union all
select 3, '{[option1]+[option6]}'                                               from dual union all
select 4, '{([option1]+[option2])*([option8]+[option9])}'                       from dual union all
select 5, '{([option1]+[option2])*[option4]}'                                   from dual union all
select 6, '{[option10]}'                                                        from dual union all
select 7, '{[option2]/([option3]+[option8])-(300-[option2])/(0.1 *[option3])}'  from dual
)
, o (opt, val) as (
select 'option1', 3653265 from dual union all
select 'option2',   26452 from dual union all
select 'option3',     100 from dual union all
select 'option4',    1235 from dual union all
select 'option5',   42565 from dual union all
select 'option6',    2330 from dual union all
select 'option7',     544 from dual union all
select 'option9',    2150 from dual
)
, n (opt, val, rn, ct) as (
select opt, val, rownum, count(*) over ()
from   o
)
, r (id, pattern, rn, ct) as (
select id, substr(pattern, 2, length(pattern) - 2), 1, null
from p
union all
select r.id, replace(r.pattern, '[' || n.opt || ']', nvl(to_char(n.val), 0)),
r.rn + 1, n.ct
from r join n on r.rn = n.rn
)
, ae (id, pattern) as (
select id, regexp_replace(pattern, '[[^]]*]', '0')
from   r
where  rn = ct + 1
) 
select id, expr_eval(pattern) as result 
from   ae
order  by id
/

输出：

ID          RESULT
---- ---------------
1      4912422195
2  14118301388430
3         3655595
4      7911391550
5      4544450495
6               0
7         2879.72