[20250527]奇怪的sql macros.txt

[20250527]奇怪的sql macros.txt --//oracle 不知道从那个版本支持sql macros操作，想实现一个算式计算结果的功能，结果遇到问题。 --//做一个记录。 1.环境： SCOTT@book01p> @ ver2 ============================== PORT_STRING                   : x86_64/Linux 2.4.xx VERSION                       : 21.0.0.0.0 BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production CON_ID                        : 0 PL/SQL procedure successfully completed. 2.建立宏： CREATE OR REPLACE FUNCTION js_op (n1 NUMBER, op varchar2, n2 NUMBER)    RETURN VARCHAR2 sql_macro(scalar) as begin    return 'n1 '|| op ||' n2'; end; / SCOTT@book01p> select js_op(1,'+',2) from dual ; select js_op(1,'+',2) from dual * ERROR at line 1: ORA-64626: invalid SQL text returned from SQL macro: ORA-00907: missing right parenthesis --//而这样写可以： CREATE OR REPLACE FUNCTION js_op1 (n1 NUMBER,  n2 NUMBER)    RETURN VARCHAR2 sql_macro(scalar) as begin       return 'n1 + n2'; end; / SCOTT@book01p> select js_op1(1,2) from dual ; JS_OP1(1,2) -----------           3 --//改写如下，这样应该可以了吧。 CREATE OR REPLACE FUNCTION js_op (n1 NUMBER, op varchar2, n2 NUMBER)    RETURN VARCHAR2 sql_macro(scalar) as vc varchar2(32); begin    select decode ( op ,'+','n1+n2','-','n1-n2','*','n1*n2','/','n1/n2') into vc from dual ;    return vc; end; / SCOTT@book01p> select js_op(1,'+',2) from dual ; select js_op(1,'+',2) from dual                    * ERROR at line 1: ORA-62565: The SQL Macro method failed with error(s). ORA-01405: fetched column value is NULL --//似乎不允许出现多种情况，改写如下： CREATE OR REPLACE FUNCTION js_op (n1 NUMBER, op varchar2, n2 NUMBER)    RETURN VARCHAR2 sql_macro(scalar) as vc varchar2(32); begin    --select decode ( op ,'+','n1+n2','-','n1-n2','*','n1*n2','/','n1/n2') into vc from dual ;    --select decode ( op ,'+','n1+n2') into vc from dual ;    --vc := '(n1 '|| op ||'n2)';    vc := 'n1 +  n2';    return vc; end; /