[20250527]oracle如何实现字符串计算公式转换为数字.txt

[20250527]oracle如何实现字符串计算公式转换为数字.txt --//比如如下字符串 '(1+2+3)*4',需要将该字符串的内容经过计算后输出计算结果24.如何使用实现。 SELECT TO_NUMBER(XMLQUERY('(1+2+3)*4' RETURNING CONTENT)) AS result FROM DUAL; --//简单测试验证看看。 1.环境： SYS@test> @ ver2 ============================== PORT_STRING                   : IBMPC/WIN_NT64-9.1.0 VERSION                       : 12.2.0.1.0 BANNER                        : Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production CON_ID                        : 0 PL/SQL procedure successfully completed. 2.测试： SYS@test> select  TO_NUMBER(XMLQUERY('(1+2+3)*4' RETURNING CONTENT)) AS result FROM DUAL;     RESULT ----------         24 SYS@test> select  TO_NUMBER(XMLQUERY('(1+2)*(3+4)' RETURNING CONTENT)) AS result FROM DUAL;     RESULT ----------         21 SYS@test> select  TO_NUMBER(XMLQUERY('(1+2)*(3 + 4)' RETURNING CONTENT)) AS result FROM DUAL;     RESULT ----------         21 SYS@test> select  TO_NUMBER(XMLQUERY('(1 + 2 ) * ( 3 + 4)' RETURNING CONTENT)) AS result FROM DUAL;     RESULT ----------         21 --//OK没有问题。 SYS@test> select power(2,4) from dual ; POWER(2,4) ----------         16 SYS@test> select  TO_NUMBER(XMLQUERY('power(2,4)' RETURNING CONTENT)) AS result FROM DUAL; select  TO_NUMBER(XMLQUERY('power(2,4)' RETURNING CONTENT)) AS result FROM DUAL                                                                            * ERROR at line 1: ORA-19237: XPST0017 - unable to resolve call to function - fn:power SYS@test> select  TO_NUMBER(XMLQUERY('9/3' RETURNING CONTENT)) AS result FROM DUAL; select  TO_NUMBER(XMLQUERY('9/3' RETURNING CONTENT)) AS result FROM DUAL                                                                     * ERROR at line 1: ORA-19224: XPTY0004 - XQuery static type mismatch: expected - node()* got - xs:integer --//无法做除法运算。 SYS@test> select  TO_NUMBER(XMLQUERY('9*0.33333' RETURNING CONTENT)) AS result FROM DUAL;     RESULT ----------    2.99997 --//小数点没有问题。也就是除法运算不行的。 --//上网查询发现，除法 要使用 div代替： SYS@test> select  TO_NUMBER(XMLQUERY('9 div 3' RETURNING CONTENT)) AS result FROM DUAL;     RESULT ----------          3 SYS@test> select  TO_NUMBER(XMLQUERY('1 div 3' RETURNING CONTENT)) AS result FROM DUAL;     RESULT ---------- .333333333 SYS@test> select * from dual where TO_NUMBER(XMLQUERY(replace('1 / 3 * 9 *8','/','div') RETURNING CONTENT)) =24; D - X --//计算没有问题。 SCOTT@book01p> select  TO_NUMBER(XMLQUERY('1 div 0' RETURNING CONTENT)) AS result FROM DUAL; select  TO_NUMBER(XMLQUERY('1 div 0' RETURNING CONTENT)) AS result FROM DUAL * ERROR at line 1: ORA-01476: divisor is equal to zero --//分母为0计算报ORA-01476: divisor is equal to zero错误。 --//12c以后版本sql语句支持函数，可以建立如下脚本： $ cat js_exp.sql WITH FUNCTION js_exp (vc IN VARCHAR2)         RETURN NUMBER      AS         n   NUMBER;      BEGIN         SELECT TO_NUMBER (XMLQUERY (vc RETURNING CONTENT)) INTO n FROM DUAL;         RETURN n;      EXCEPTION         WHEN OTHERS         THEN            RETURN NULL;      END; SELECT js_exp ('&&1') result   FROM DUAL; / --//注意后面的斜线是必须的，否则无法执行,没有后面的分号没事，但是没有后面的斜线不会执行。 SCOTT@book01p> @ js_exp 1+2     RESULT ----------          3 SCOTT@book01p> @ hash HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ---------- ------------------- ----------- 4272871752 gqjfqd3zaxqa8            0      55624      1388734953  feaed948  2025-05-28 17:13:09    16777217 SCOTT@book01p> @ sql_id gqjfqd3zaxqa8 -- SQL_ID = gqjfqd3zaxqa8 come from shared pool WITH FUNCTION js_exp (vc IN VARCHAR2)         RETURN NUMBER      AS         n   NUMBER;      BEGIN         SELECT TO_NUMBER (XMLQUERY (vc RETURNING CONTENT)) INTO n FROM DUAL;         RETURN n;      EXCEPTION         WHEN OTHERS         THEN            RETURN NULL;      END; SELECT js_exp ('1+2') result   FROM DUAL; --//建立如下脚本，验证自己写的计算sql_id是否正确。 $ cat -Ev aa.txt WITH FUNCTION js_exp (vc IN VARCHAR2)$         RETURN NUMBER$      AS$         n   NUMBER;$      BEGIN$         SELECT TO_NUMBER (XMLQUERY (vc RETURNING CONTENT)) INTO n FROM DUAL;$ $         RETURN n;$      EXCEPTION$         WHEN OTHERS$         THEN$            RETURN NULL;$      END;$ SELECT js_exp ('1+2') result$   FROM DUAL$ $ sql_idz.sh aa.txt 1 sql_text = WITH FUNCTION js_exp (vc IN VARCHAR2)         RETURN NUMBER      AS         n   NUMBER;      BEGIN         SELECT TO_NUMBER (XMLQUERY (vc RETURNING CONTENT)) INTO n FROM DUAL;         RETURN n;      EXCEPTION         WHEN OTHERS         THEN            RETURN NULL;      END; SELECT js_exp ('1+2') result   FROM DUAL\0 full_hash_value(16) = 051D8B3AA18156D3FB45D668FEAED948 or 051d8b3aa18156d3fb45d668feaed948 hash_value(10) = 4272871752 or hash_value(16) = FEAED948 or feaed948 sql_id(16) = FB45D668FEAED948 or fb45d668feaed948 sql_id(32) = gqjfqd3zaxqa8 sql_id(32) = gqjfqd3zaxqa8 sql_id(32) = gqjfqd3zaxqa8 --//ok,计算没有问题。 SCOTT@book01p> @ js_exp 1/0     RESULT ---------- --//返回null。