在Oracle中计算单列

Question

我的表格如下:

id       jancuk        pijut
-------------------------------
1        20 * 4         
2         2 + 4         
3     3 - 1 + 2         
4        20 / 5

我想用jancuk列的结果更新pijut列。这是可能的在oracle使用查询?
因此，如果表已经更新，那么表应该看起来像这样:

id       jancuk        pijut
-------------------------------
1        20 * 4         80
2         2 + 4          6
3     3 - 1 + 2          4
4        20 / 5          4

请帮帮我。谢谢。

Answer 1

尝试:

update tbl
set "pijut" = xmlquery(replace( "jancuk", '/', ' div ' ) returning content ).getNumberVal();

更多信息链接

Answer 2

您可以使用动态SQL函数来完成此操作-

create or replace function calc_value(str in varchar2) return number is
 val_ number;
begin
    if str is null then
         return null;
    end if;
    execute immediate 'begin :val_ := '||str||'; end;' using out val_;
    return val_;
end;
/

和结果选择将是-

with t as
(
select 1 as id, '        20 * 4 ' as jancuk from dual union all
select 2 as id, '         2 + 4 ' as jancuk from dual union all      
select 3 as id, '     3 - 1 + 2 ' as jancuk from dual union all       
select 4 as id, '        20 / 5 ' as jancuk from dual
)
select id, jancuk, calc_value(jancuk) as pijut
from t