PL/SQL 实现四则运算

最新推荐文章于 2022-01-05 17:30:13 发布

congza2755

最新推荐文章于 2022-01-05 17:30:13 发布

阅读量375

点赞数

第一步，公式解析

自写了两个函数用于四则运算公式的解析

将符号替换成#号，本来可以直接用正则表达式去分段，但是为了逻辑更直观还是写了一个替换函数。
FUNCTION CHANGE_OPERATOR(FORMULA VARCHAR2)
RETURN VARCHAR2
AS
  V_FORMULA VARCHAR2(100);
BEGIN
  V_FORMULA := REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(FORMULA,'(',''),')',''),'+','#'),'-','#'),'*','#'),'/','#');
  RETURN V_FORMULA;
END CHANGE_OPERATOR;

2，建议一个表变量，根据#号将公式中的字段截断保存
/**---- 2 将字符串按照 # 解析成不同字段**/
---- 返回事先创建好的 CREATE OR REPLACE TYPE STR_SPLIT IS TABLE OF VARCHAR2 (4000);
---- 调用函数语句 SELECT * FROM TABLE(SUB_FORMULA(FORMULA))
FUNCTION SUB_FORMULA(FORMULA VARCHAR2)
RETURN STR_SPLIT PIPELINED
AS
V_LENGTH NUMBER :=LENGTH(FORMULA);----字符串长度
V_INDEX       NUMBER; ---- #的位置
V_START       NUMBER:=1; ---开始位置
BEGIN
WHILE V_START <= V_LENGTH LOOP
  V_INDEX:= INSTR(FORMULA,'#',V_START);
  IF V_INDEX =0 THEN
   PIPE ROW(SUBSTR(FORMULA,V_START));
   V_START :=V_LENGTH + 1;
  ELSE
   PIPE ROW(SUBSTR(FORMULA,V_START,V_INDEX-V_START));
   V_START := V_INDEX + 1;
  END IF;
END LOOP;
RETURN;
EXCEPTION
    WHEN OTHERS THEN
         DBMS_OUTPUT.PUT_LINE(SQLCODE || ' ' || SQLERRM);
    RETURN;
END SUB_FORMULA;

第二步，计算

应该修改的简单点再上传上来的

/**
    ---3 四则运算函数**/
FUNCTION OPERATION_GLOBAL (FORMULA IN VARCHAR2)
RETURN VARCHAR2
IS
FORMULA_V   VARCHAR2(100) :='('||FORMULA||')';
V_FORMULA   VARCHAR2(500); ------储存转成#号后的公式
FIELD_NO    VARCHAR2(100); ------字段编号
FIELD_VALUE VARCHAR2(100); --- 数据值
FIELD_TYPE NUMBER(2);   ---- 数据类型
RESULT_V      NUMBER;
/* V_COUNT     NUMBER;*/
R_RESULT   VARCHAR2(100);
BEGIN
------ 调用CHANGE_OPERATOR函数，将四则运算符号转换成#
V_FORMULA :=CHANGE_OPERATOR(FORMULA);
-----创建游标C1_CURSOR 用于保存公式分割后的字段名
DECLARE
    CURSOR C1_CURSOR IS
    SELECT * FROM TABLE(PACK_RISK_FUNCTION.SUB_FORMULA(V_FORMULA));
BEGIN
    OPEN C1_CURSOR;
      FETCH C1_CURSOR INTO FIELD_NO;
    WHILE C1_CURSOR%FOUND LOOP
    -----    这里查询计算数据，替换计算因子得到算式    表A 为公式因子表，也就是字段表，表B为值表。

SELECT A.FIELDTYPE,B.FIELDVALUE INTO FIELD_TYPE,FIELD_VALUE FROM tableA A ,tableB B
WHERE B.AID=A.ID AND A.FIELDNO = FIELD_NO

   -----将公式中字段名与数据进行替换最终得到算式
    FORMULA_V := REPLACE(FORMULA_V,FIELD_NO,FIELD_VALUE);
    FETCH C1_CURSOR INTO FIELD_NO;
    END LOOP;
    CLOSE C1_CURSOR;
END;
------ 执行计算
EXECUTE IMMEDIATE 'SELECT'||FORMULA_V||' FROM DUAL' INTO RESULT_V;
---- 结果返回字符串，并四舍五入到小数点后4位
R_RESULT := TO_CHAR(ROUND(RESULT_V,4),'fm99999999990.9999');
RETURN R_RESULT;
EXCEPTION
WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLCODE || ' ' || SQLERRM);
R_RESULT := 'X';
RETURN R_RESULT;

END;

在计算前最好判断一下公式的格式，写了个小函数判断，将公式中的因子用数字代替，这样就可以直接使用动态SQL执行计算，不成功自然就说明该公式有问题。

   /**---- 判断公式字段是否有效**/
FUNCTION FORMULA_JUDGE(FORMULA VARCHAR2)
RETURN VARCHAR2
IS
FORMULA_V   RISKITEMINFO.RECKONNAME%TYPE :='('||FORMULA||')';
V_FORMULA    VARCHAR2(500);
FIELD_NO     VARCHAR2(100); ------字段编号
R_RESULT    VARCHAR2(100):=0;
V_COUNT     NUMBER(10);

BEGIN
     -------- 公式直接计算，用于判断公式是否正确如果发生异常，被捕获,标识公式错误
   EXECUTE IMMEDIATE 'SELECT'||FORMULA_V||' FROM DUAL';
     ------ 调用CHANGE_OPERATOR函数，将四则运算符号转换成#
   V_FORMULA :=PACK_RISK_FUNCTION.CHANGE_OPERATOR(FORMULA_V);

    -----判断公式中的字段是否被启用
    -----创建游标C1_CURSOR 用于保存公式分割后的字段名
    DECLARE
      CURSOR C1_CURSOR IS
      SELECT * FROM TABLE(PACK_RISK_FUNCTION.SUB_FORMULA(V_FORMULA));
    BEGIN
      OPEN C1_CURSOR;
        FETCH C1_CURSOR INTO FIELD_NO;
      WHILE C1_CURSOR%FOUND LOOP
        SELECT COUNT(*) INTO V_COUNT FROM RISKRAWDATAFIELD WHERE FIELDNO =FIELD_NO AND ISALIVE =1;
          IF V_COUNT = 0 THEN
            R_RESULT:= '公式中存在无效数据项';
            RETURN R_RESULT;
          END IF;
        FETCH C1_CURSOR INTO FIELD_NO;
      END LOOP;
      CLOSE C1_CURSOR;
    END;
    RETURN R_RESULT;
EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE || ' ' || SQLERRM);
        R_RESULT :='公式格式错误';
      RETURN R_RESULT;
END;