PLSQL编程，解析字符串的加减乘除

表：

SQL> select b.index_code, b.amount from cin_circ_org_index_amount b;
 
INDEX_CODE               AMOUNT
-------------------- ----------
a64210170               9830.91
a64210171               2626.43
a64210172               7204.48
a64210173              94356.82
a64210190             109364.48
a64210191              94356.82
a64210192               5176.75
a64210193               9830.91
a64210194               2626.43 

 

SQL> select b.index_code, a.operator, a.right_expression
  2    from cin_circ_verify_formulas a, cin_circ_indexes b
  3   where a.left_index_id = b.index_id;
 
INDEX_CODE                                         OPERATOR RIGHT_EXPRESSION
-------------------------------------------------- -------- --------------------------------------------------------------------------------
a10010001                                          =        a10010001+a10020004
a10020001                                          <=       a10020001+a10020004*a10010001
a10020004                                          =        a10020004*a10020005
a10020005                                          =        a10020005+a10020005

 

　解析程序：

---验证公式字符串解析
  procedure sec_calc_indexes_str(p_str         in out varchar2,
                                 p_loc         in out integer,
                                 p_period_name varchar2,
                                 p_rep_org_id  number,
                                 p_data_org_id number,
                                 p_fqc_flag    number,
                                 p_index_type  varchar2) is
  
    j        integer;
    v_length number;
    x        varchar2(3000);
  
    y        varchar2(1);
    v_amount varchar2(500); --指标金额
  
  begin
  
    y := 'X';
    x := p_str;
    select length(x) into v_length from dual;
    while (y = 'X' or y = 'Y') loop
      if substr(x, p_loc, 1) is not null and
         (upper(substr(x, p_loc, 1)) <> lower(substr(x, p_loc, 1)) or
          nvl(translate(substr(x, p_loc, 1), '\1234567890', '\'),
              'is number') = 'is number') then
        if y = 'X' then
          j := p_loc;
        end if;
        y := 'Y';
      else
        if y = 'Y' then
          y := 'Z';
        end if;
        --exit;
      end if;
      p_loc := p_loc + 1;
    end loop;
    p_loc := p_loc - 1;
    --在此需要计算 v_amount 的值
    begin
      select nvl(amount, 0)
        into v_amount
        from cin_circ_org_index_amount a
       where period = p_period_name
         and rep_org_id = p_rep_org_id
         and rep_data_org_id = p_data_org_id
         and index_code = substr(x, j, p_loc - j)
         and fqc_flag = p_fqc_flag
         and a.index_type = p_index_type; --add
      if to_number(v_amount) < 0 then
        v_amount := '(' || v_amount || ')';
      end if;
    exception
      when no_data_found then
        v_amount := 0;
    end;
    --v_amount := 1999999;
    p_str := substr(x, 1, j - 1) || v_amount || substr(x, p_loc);
    p_loc := length(substr(x, 1, j - 1) || v_amount) + 1;
    if length(rtrim(p_str)) > p_loc + 7 then
      sec_calc_indexes_str(p_str,
                           p_loc,
                           p_period_name,
                           p_rep_org_id,
                           p_data_org_id,
                           p_fqc_flag,
                           p_index_type);
    
    end if;
  end sec_calc_indexes_str;

 begin
          v_str     := sec_calc_index_rec.right_expression;
          v_old_str := sec_calc_index_rec.right_expression;
          dbms_output.put_line(v_str);
          v_loc := 1;
          sec_calc_indexes_str(v_str,
                               v_loc,
                               p_period_name,
                               p_rep_org_id,
                               data_org_rec.rep_data_org_id,
                               p_fqc_flag,
                               p_index_type);
          --dbms_output.put_line('select '||v_str||' from dual ');
          execute immediate 'select ' || v_str || ' from dual ' --动态sql，计算右边公式值
            into v_right_amount;
        
          if v_right_amount is null then
            v_right_amount := 0;
          end if;
        
          if sec_calc_index_rec.operator = '=' and
             v_left_amount = v_right_amount then
            v_comparison_opt := TRUE;
          elsif sec_calc_index_rec.operator = '>=' and
                v_left_amount >= v_right_amount then
            v_comparison_opt := TRUE;
          elsif sec_calc_index_rec.operator = '<=' and
                v_left_amount <= v_right_amount then
            v_comparison_opt := TRUE;
          elsif sec_calc_index_rec.operator = '<>' and
                v_left_amount <> v_right_amount then
            v_comparison_opt := TRUE;
          else
            v_comparison_opt := FALSE;
          end if;