sql - Oracle 测试功能

Question

Oracle 中是否有以类似方式工作的内置函数？

CREATE OR REPLACE FUNCTION IFTrueValue(iValue NUMBER, sOper VARCHAR2, iTest NUMBER, iTrue NUMBER) RETURN NUMBER AS
  bCond BOOLEAN;
BEGIN
  EXECUTE IMMEDIATE 'SELECT CASE WHEN ' || iValue || sOper || iTest || ' THEN 1 ELSE 0 END FROM dual' INTO bCond;
  RETURN CASE bCond
           WHEN TRUE THEN iTrue
           ELSE iValue
         END;
END;
/

SELECT IFTrueValue(Mod(20, 20), '=', 0, 20) FROM dual; -- return 20
SELECT IFTrueValue(Mod(25, 20), '=', 0, 20) FROM dual; -- return 5: Mod(25, 20)

标签： sqloraclefunctionplsql

Answer 1

您可以为此使用 xmltable/xmlquery。

带有示例数据的完整示例：

with t(iValue, sOper, iTest, iTrue) as (
  select Mod(20, 20), '=', 0, 20 from dual union all
  select Mod(25, 20), '=', 0, 20 from dual union all
  select Mod(25, 20), '>', 0, 13 from dual
)
select 
   t.*,
   xmlcast(
      xmlquery(
        ('if ($iValue '||sOper||' $iTest)
          then $iTrue
          else $iValue
        ')
        passing 
            iValue as "iValue",
            iTest  as "iTest",
            iTrue  as "iTrue"
        returning content
      ) 
      as number
    )test_function
from 
   t

结果：

    IVALUE SOPER      ITEST      ITRUE TEST_FUNCTION
---------- ----- ---------- ---------- -------------
         0 =              0         20            20
         5 =              0         20             5
         5 >              0         13            13