关于公式字段的计算问题,给个思路也可以!

Question

问题是这样的.
现有一张表结构类似如下所述指标编号  计算公式  指标值  可分解标志
   A      (B+C)+E      ?        1
   B                  10        0
   C        D+E        ?        1
   D                  30        0
   E                   5        0
....(注:可分解标志为'1'的指标的指标值由用户直接输入)
现在能否用一个存储过程实现如下功能:
将所有可分解指标为'1'的指标值通过计算公式计算出来.
对表结构改进有何建议也欢迎提出.

Answer 1

创建类型tbl_str,
创建函数to_table,
创建测试表Test,
创建公式符号表expression,
创建函数f_test_get_value,目前仅支持+ - * / ( )，如果需要别的符号自己加在表expression中.
Test表中的Formula字段每个字符都要用空格分开，例如 ( B + C ) + E
代码如下;
create or replace type tbl_str as table of varchar2(4000);
/*********************************************************
/* Description:字定义类型，用于to_table函数
/* Author:He Yixiang
*********************************************************/
/
create or replace function to_table(pv_str varchar2,pv_split varchar2) return tbl_str
as
  ltab tbl_str := tbl_str();
  pos integer := 0;
  ls varchar2(4000) := pv_str;
/*********************************************************
/* Description:同Split函数
/* Author:He Yixiang
*********************************************************/
begin
  pos := instr(ls,pv_split);
  while pos > 0 loop
    ltab.extend;
    ltab(ltab.count) := substr(ls,1,pos - 1);
    ls := substr(ls,pos + length(pv_split));
    pos := instr(ls,pv_split);
  end loop;
  ltab.extend;
  ltab(ltab.count) := ls;
  return ltab;
end;
/
create table test (id char(1),formula varchar2(100),value number,flag char(1));
insert into test select 'A','( B + C ) + E',null,1 from dual;
insert into test select 'B',null,10,0 from dual;
insert into test select 'C','D + E',null,1 from dual;
insert into test select 'D',null,30,0 from dual;
insert into test select 'E',null,5,0 from dual;
commit;
create table expression (exp_value char(1));
insert into expression (exp_value) values ('+');
insert into expression (exp_value) values ('-');
insert into expression (exp_value) values ('*');
insert into expression (exp_value) values ('/');
insert into expression (exp_value) values ('(');
insert into expression (exp_value) values (')');
commit;
create or replace function f_test_get_value(pc_id in char) return number is
  Result number;
  c_flag char(1);
  type curtype is ref cursor;
  cur curtype;
  c_para char(1);
  v_sqlstr varchar2(4000);
  n_count number;
/*********************************************************
/* Description:根据表test中的公式Formula计算返回Value值
/* Author:He Yixiang
*********************************************************/
begin
  select flag into c_flag from test where id=pc_id;
  if c_flag='0' then
    select value into Result from test where id=pc_id;
  else
    open cur for 
    select column_value from table(cast(to_table((select formula from test where id=pc_id),' ') as tbl_str));
    v_sqlstr:='select ';
    loop
      fetch cur into c_para;
      exit when cur%notfound;
      select count(*) into n_count from expression where exp_value=c_para;
      if n_count>0 then
        v_sqlstr:=v_sqlstr||c_para;
      else
        v_sqlstr:=v_sqlstr||f_get_value(c_para);
      end if;
    end loop;
    v_sqlstr:=v_sqlstr||' from dual';
    execute immediate v_sqlstr into Result;
    close cur;
  end if;
  return(Result);
end f_test_get_value;
/
将上面代码拷贝到Command窗口执行即可。
测试如下：
SQL> select * from test;ID FORMULA                             VALUE FLAG
-- ------------------------------ ---------- ----
A  ( B + C ) + E                             1
B                                         10 0
C  D + E                                     1
D                                         30 0
E                                          5 0SQL> select f_test_get_value('D') from dual;F_TEST_GET_VALUE('D')
---------------------
                   30SQL> select f_test_get_value('C') from dual;F_TEST_GET_VALUE('C')
---------------------
                   35SQL> select f_test_get_value('A') from dual;F_TEST_GET_VALUE('A')
---------------------
                   50测试通过。你在使用的时候需要注意test表中最好注意，ID不能有重复的，如果flag=1那么value必须为null,反之flag=0则value必须有值，Formula公式不能错，注意空格分割。

Answer 2

能不能解释一下 ltab.extend; 的用意？