计算单列甲骨文

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(); 

欲了解更多信息link

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