PL/SQL函数未返回指定行反而返回全表数据问题排查

问题描述 #

编写了如下PL/SQL函数GET_UNIT_BY_PK，预期根据传入的code参数返回F_UNITS表中对应行，但实际返回全表所有行；添加ROWNUM=1限制后，仅返回表中第一行：

原始无ROWNUM限制的代码：

FUNCTION GET_UNIT_BY_PK (code IN VARCHAR2)
RETURN CLOB
IS
    l_result CLOB;
BEGIN
    SELECT '[' || 
           '{ "CODE": "' || CODE || '", "TITLE": "' || TITLE || '", "SUBTITLE": "' || SUBTITLE || '", "DESCRIPTION": "' || DESCRIPTION || '", "KEYWORDS": "' || KEYWORDS || '", "STATUS": "' || STATUS || '" }' || 
           ']'
    INTO l_result
    FROM F_UNITS
    WHERE CODE = code;

    RETURN l_result;
END;

添加ROWNUM限制后的代码：

FUNCTION GET_UNIT_BY_PK (code IN VARCHAR2)
RETURN CLOB
IS
    l_result CLOB;
BEGIN
    SELECT '[' || 
           '{ "CODE": "' || CODE || '", "TITLE": "' || TITLE || '", "SUBTITLE": "' || SUBTITLE || '", "DESCRIPTION": "' || DESCRIPTION || '", "KEYWORDS": "' || KEYWORDS || '", "STATUS": "' || STATUS || '" }' || 
           ']'
    INTO l_result
    FROM F_UNITS
    WHERE CODE = code AND ROWNUM=1;

    RETURN l_result;
END;

核心原因 #

这是入参名与表字段名重名导致的解析优先级问题：
PL/SQL中，当局部变量（包括函数入参）和查询的表字段同名时，解析器会优先将其识别为表字段。也就是说WHERE CODE = code实际等价于WHERE CODE = CODE——这个条件对表中所有行都成立（每行的CODE字段必然等于自身），所以会返回全表数据。

添加ROWNUM=1后，因为过滤条件始终为真，数据库只会取满足条件的第一行（也就是表的第一行），自然不会匹配传入的参数值。

解决方法 #

最直接的方案是给入参添加前缀（比如p_），明确区分入参和表字段，修改后的代码示例：

FUNCTION GET_UNIT_BY_PK (p_code IN VARCHAR2)
RETURN CLOB
IS
    l_result CLOB;
BEGIN
    SELECT '[' || 
           '{ "CODE": "' || CODE || '", "TITLE": "' || TITLE || '", "SUBTITLE": "' || SUBTITLE || '", "DESCRIPTION": "' || DESCRIPTION || '", "KEYWORDS": "' || KEYWORDS || '", "STATUS": "' || STATUS || '" }' || 
           ']'
    INTO l_result
    FROM F_UNITS
    WHERE CODE = p_code;

    RETURN l_result;
END;

也可以在WHERE子句中明确指定参数的作用域，比如WHERE CODE = GET_UNIT_BY_PK.code，但前者的代码可读性更高，是PL/SQL开发中的常规命名规范。

内容的提问来源于stack exchange，提问作者Julián Oviedo