Oracle SQL自定义函数始终返回Null问题排查求助

快速解决方案 #

如果你遇到同样的问题，移除VARCHAR2类型参数名称中的下划线即可解决。比如将参数KPI_TYPE改为KPITYPE。

问题详情 #

我有一张TARGETS_TABLE表，存储KPI及各等级目标值，表结构和数据如下：

需要创建名为KPI的自定义函数，接收两个参数：

• RAW_SCORE（NUMBER类型）：原始分数
• KPI_TYPE（VARCHAR2类型）：KPI类型

函数需根据原始分数匹配对应KPI的目标等级，返回系数，预期示例：

• KPI(6,'KPI A') 返回0.5
• KPI(10,'KPI A') 返回1.0
• KPI(9,'KPI B') 返回0
• KPI(100,'KPI B') 返回1.5

编写的函数脚本如下：

create or replace FUNCTION KPI(RAW_SCORE in NUMBER, KPI_TYPE in VARCHAR2)
RETURN NUMBER AS
ACTUAL_SCORE NUMBER;
BEGIN
    select case when RAW_SCORE >= target_150 then 1.50
                when RAW_SCORE >= target_100 then 1.00
                when RAW_SCORE >= target_50 then 0.50
            else 0
            end into ACTUAL_SCORE
    from TARGETS_TABLE
    where TARGETS_TABLE.KPI = KPI_TYPE;
RETURN ACTUAL_SCORE;

但调用函数时始终返回Null，单独执行SELECT语句手动传参却能得到预期结果。

TARGETS_TABLE建表语句：

CREATE TABLE "TARGETS_TABLE" 
(    "KPI" VARCHAR2(300 BYTE),  
    "TARGET_50" NUMBER(38,14), 
    "TARGET_100" NUMBER(38,14), 
    "TARGET_150" NUMBER(38,17)
) SEGMENT CREATION IMMEDIATE 
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
NOCOMPRESS LOGGING
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DATA" ;

问题原因 #

Oracle在解析函数参数时，若参数名称包含下划线，可能会与内部标识符或表列的命名规则产生解析歧义，导致WHERE子句中的参数匹配失效，最终查询无结果返回Null。移除参数名中的下划线后，Oracle能正确识别参数，匹配WHERE条件，返回预期结果。

修改后的函数示例：

create or replace FUNCTION KPI(RAW_SCORE in NUMBER, KPITYPE in VARCHAR2)
RETURN NUMBER AS
ACTUAL_SCORE NUMBER;
BEGIN
    select case when RAW_SCORE >= target_150 then 1.50
                when RAW_SCORE >= target_100 then 1.00
                when RAW_SCORE >= target_50 then 0.50
            else 0
            end into ACTUAL_SCORE
    from TARGETS_TABLE
    where TARGETS_TABLE.KPI = KPITYPE;
RETURN ACTUAL_SCORE;

内容的提问来源于stack exchange，提问作者anon_stat_123