[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。