oracle正则应用案例(文本提取数字求和)

场景说明：

数据库字段记录中存在“打发十分(1/10)，放大发(1/15)，放fsda发(1/7)”这种情况，期望获取所有的括号内分数，取所有分母之和，再用于具体的业务数据比对。

创建样例数据：

drop table test1;
create table test1(a1 varchar2(500),id varchar2(10));
insert into test1 values('打发十分(1/10)，放大发(1/15)，放fsda发(1/7), ','1');
insert into test1 values('打发十分(1/10)，放大发(1/15) ','2');
select * from test1;

针对单行记录的处理如下:

SELECT SUM(TO_NUMBER(REGEXP_SUBSTR(str, '\d+\/(\d+)\)', 1, LEVEL, NULL, 1))) AS denominator_sum
FROM (SELECT '打发十分(1/10)，放大发(1/15)' AS str FROM dual)
CONNECT BY LEVEL <= REGEXP_COUNT(str, '\d+\/\d+\)');

针对多行记录，没有搞定，改为编写一个函数进行调用：

CREATE OR REPLACE FUNCTION calculate_sum(str IN VARCHAR2)
RETURN NUMBER
IS
  sum_num NUMBER := 0;
BEGIN
  FOR i IN 1..REGEXP_COUNT(str, '\d+\/\d+\)') LOOP
    BEGIN
      sum_num := sum_num + TO_NUMBER(REGEXP_SUBSTR(str, '\d+\/(\d+)\)', 1, i, NULL, 1));
    EXCEPTION
      WHEN OTHERS THEN
        RETURN -1;
    END;
  END LOOP;

  RETURN sum_num;
END;

SELECT calculate_sum(a1) t1,id,a1 from test1;
T1	ID	A1
32	1	打发十分(1/10)，放大发(1/15)，放fsda发(1/7), 
25	2	打发十分(1/10)，放大发(1/15) 

测试完毕，结果分别为32和25。计算有误情况下返回-1排查原始数据。