Oracle实现公式解析的算法

hbf

我们在开发各种管理系统时（例如：财务、人资、统计等）都会涉及到指标的数据计算，为了系统以后的可维护性，通常我们会将需要计算的指标做成可维护公式的，这样，在以后的管理中就可以很方便的通过调整公式来改变指标的计算方法。

   这样，问题同时也就来了，如何将设置的公式解析成可计算的表达式？如何设计Oracle表结构呢？前段时间UncleToo正好开发统计系统时遇到了类似的问题，逛了很多IT论坛，找了很多相关的帖子，请教了许多大神，没有得到满意的答案，最后自己写个方法实现了解析公式的功能，执行效率还不错，这里分享给大家，希望对大家有帮助，如果你还有其他更好的方法也可以与UncleToo一起讨论。

一、表结构设计

这里我们需要三张表（为了讲解方便，我把表结构都简单化了，省略了公式解析没用到的字段，大家可以根据自己的需要加上去）

表一：指标基础表（存放公式）

这张表是我们维护指标基础属性的表，存放的是指标的完整公式。表中的ACCOUNT_ORDER是一个重要的字段，用来存放指标计算等级的，等级越高，计算越靠后，相反，等级越小，计算越靠前。这个字段在遍历指标时用到，我们会在下面函数调用的时候讲解。
create table T_ITEM  
(  
  ITEM_NAME           VARCHAR2(50),  --指标名称  
  ITEM_CODE_PREFIX    VARCHAR2(50),  --指标编码  
  CALCULATION_FORMULA VARCHAR2(1000),--公式  
  ACCOUNT_ORDER       NUMBER(10)     --计算等级  
)  
表中存放数据如下：

表二：公式拆分表（暂且这么叫吧）

这张表是存放指标公式中涉及到的指标
create table T_ITEM_FMLA  
(  
  FMLA_ITEM_ID     NUMBER(10) not null, --主键ID  
  ITEM_CODE_PREFIX VARCHAR2(50),        --公式等号左边指标  
  FMLA_ITEM_CO_PR  VARCHAR2(50)         --公式等号右边指标  
)  
表中存放数据如下：

表三：数据表

这张表是存放指标数据的，这里事先已经存放了不需要计算的指标的数据，根据这些数据计算带公式的指标
create table T_ITEM_VALUE  
(  
  ITEM_CODE_PREFIX VARCHAR2(40) not null,  --指标编码  
  DATA_DATE        DATE not null,          --数据日期  
  DATA_VALUE       NUMBER(38,15)           --指标数据  
)  
表中存放数据如下：

二、编写sql脚本。

这里我们采用Oracle自定义函数来写，方便在其他地方调用。sql中每个重点的地方都有标注，这里就不一一说明了，直接上代码：
FUNCTION get_value(itemCodePrefix     VARCHAR2,  
                   calculationFormula VARCHAR2,  
                   valueDate         date) RETURN NUMBER AS  
    n_value  NUMBER;  
    v_string VARCHAR2(4000);  
    v_sql    VARCHAR2(4000);  
    v_r      VARCHAR2(1000);  
    cursor cur1 is  
      select fm.fmla_item_co_pr, nvl(rt.data_value, 0) data_value  
        from T_ITEM_FMLA fm, T_ITEM_VALUE rt  
       where fm.fmla_item_co_pr = rt.item_code_prefix(+)  
         and rt.data_date = valueDate  
         and fm.item_code_prefix = itemCodePrefix;  
  begin  

      if calculationFormula is null then  
        --如果公式为空，这指标数据默认为0  
        n_value := 0;  
      else  
        v_string := '''' || calculationFormula || '''';  
        --循环公式指标，将指标替换成对应数据  
        for c in cur1 loop  
          v_string := 'replace(' || v_string || ',''[' || c.FMLA_ITEM_CO_PR ||  
                      ']'',''' || c.data_value || ''')';  
        end loop;  
        --将替换后的表达式字符串赋值  
        v_sql := 'select ' || v_string || ' from dual';  
        execute immediate v_sql into v_r;  
        commit;  

        --将字符串里的--替换成+，要不然会出错  
        v_r := replace(v_r,'--','+');  
        --这里最后判断一下最后的表达式里是否还存在没有替换的指标  
        if instr(v_r, '[') > 0 or instr(v_r, ']') > 0 then  
          n_value := 0;  
        else  
          --执行表达式  
          v_sql := 'select ' || v_r || ' from dual';  
          execute immediate v_sql into n_value;  
          commit;  
        end if;  
      end if;  
    --返回数据  
    return n_value;  

    --异常处理  
    exception  
      when ZERO_DIVIDE  then  
        return 0;  
  END get_value;  
参数介绍：

itemCodePrefix：需要计算的指标，即公式等号左边的指标

calculationFormula：计算公式，即公式等号右边的指标

valueDate:数据日期

三、函数调用

遍历T_ITEM表，根据计算等级排序，找到所有带公式的指标，将公式传给指标、公式、日期传给get_value函数即可得到该指标的计算值。

调用示例：
for c in (select t.* from T_ITEM t  
      where t.CALCULATION_FORMULA is not null  
          order by t.account_order asc) loop  
    n_value := get_value(c.item_code_prefix,c.calculation_formula,d_date);  

    --下面是其他操作，省略...  
end loop;  
到此，公式解析已经写好了。

当时在写这个功能的时候，还写了一个判断公式是否有除数为0的情况，后来嫌调用麻烦就没用上，直接用Oracle的exception给处理了，但是有时候我们在程序中确实需要这样的功能，所以这里UncleToo也拿出来一起分享给大家。

代码：

[sql] view plaincopy
FUNCTION fun_exist_zero(calculationFormula VARCHAR2) RETURN NUMBER AS  
    n_count    number;  
    n_rntValue number := 0;  
    n_i        number := 1;  
    n_op       number;  
    n_tmp      number;  
    v_div      VARCHAR2(100);  
    v_tmp      VARCHAR2(1000);  
    v_sql      VARCHAR2(1000);  
    n_while    number;  
  begin  
    select lengthb(calculationFormula) -  
           lengthb(replace(calculationFormula, '/', ''))  
      into n_count  
      from dual;  

    if n_count = 0 then  
      n_rntValue := 0;  
    else  
      v_tmp := calculationFormula;  
      while n_i <= n_count loop  
        n_op := instr(v_tmp, '/');  
        if substr(v_tmp, n_op + 1, 1) = '0' or instr(v_tmp, '/0')>0 then  
          n_rntValue := 1;  
          exit;  
        elsif substr(v_tmp, n_op + 1, 1) = '(' then  
          select substr(v_tmp,  
                        n_op + 2,  
                        instr(v_tmp, ')', n_op + 1) - n_op - 2)  
            into v_div  
            from dual;  

          n_tmp := lengthb(v_div) - lengthb(replace(v_div, '(', ''));  
          if n_tmp = 0 then  
            v_div := v_div;  
            v_sql := 'select ' || v_div || ' from dual';  
            execute immediate v_sql  
              into n_tmp;  
            commit;  
            if n_tmp = 0 then  
              n_rntValue := 1;  
              exit;  
            end if;  
          else  
            v_div := substr(v_tmp,  
                            n_op + 2,  
                            instr(v_tmp,  
                                  ')',  
                                  n_op + lengthb(v_div) + 2,  
                                  n_tmp) - (n_op + 1));  
            if instr(v_div, '/') > 0 then  
              n_rntValue := fun_exist_zero(v_div);  
            else  
              v_sql := 'select ' || v_div || ' from dual';  
              execute immediate v_sql  
                into n_while;  
              if n_while = 0 then  
                n_rntValue := 1;  
                exit;  
              else  
                n_rntValue := 0;  
              end if;  
            end if;  
          end if;  
        else  
          v_tmp := substr(v_tmp, n_op + 1);  
        end if;  
        n_i := n_i + 1;  
      end loop;  

    end if;  

    return n_rntValue;  
  END fun_exist_zero;  

调用时，直接将公式传进来，返回1，表示存在除数为0的情况，返回0，表示不存在，这里就不多解释了，函数只用到了循环和字符串处理函数等基本知识，有兴趣的同学可以研究研究。这个函数还不太完善，如果公式比较复杂，带有多层括号嵌套，会有问题，大家在使用的时候根据自己的实际情况继续完善完善。

wheat

爱那么短，遗忘却那么长。