如何在Scala构造的Oracle查询中对ID列每一行值使用自定义Scala函数

错误根因 #

你当前的写法是在Scala代码拼接SQL字符串阶段就执行了本地的func函数，传入的参数是字符串字面量"ID"，而非Oracle表中ID列的每行实际值，因此会触发字符串转数字的格式错误。且本地Scala函数无法直接作用于Oracle数据库端的行数据，必须将逻辑下沉到数据库层执行。

方案1：直接转换为Oracle原生SQL逻辑（推荐） #

你的func逻辑非常简单，可以直接用Oracle内置函数实现，无需额外创建数据库对象：

// 注意：如果group_id为用户传入参数，建议使用预编译占位符避免SQL注入
val groupId: Int = 你的目标分组值
val query = s"""
  SELECT * FROM table 
  WHERE MOD(TO_NUMBER(SUBSTR(ID, LENGTH(ID)-1, 2)), 4) = $groupId
"""

如果需要兼容ID长度不足2位、或者最后两位存在非数字字符的场景，可以补充校验逻辑：

-- 示例：仅处理ID长度>=2且最后两位为数字的记录，12c+版本支持转换异常默认值
MOD(TO_NUMBER(SUBSTR(ID, LENGTH(ID)-1, 2) DEFAULT 0 ON CONVERSION ERROR), 4)

方案2：在Oracle端创建自定义函数 #

如果你的实际业务逻辑比示例复杂、或者需要多处复用，可以先在Oracle数据库中创建PL/SQL自定义函数：

CREATE OR REPLACE FUNCTION FUNC_CAL_GROUP_ID(p_id VARCHAR2) RETURN NUMBER IS
    v_two_num NUMBER;
BEGIN
    v_two_num := TO_NUMBER(SUBSTR(p_id, LENGTH(p_id)-1, 2));
    RETURN MOD(v_two_num, 4);
END;
/

创建完成后直接在Scala拼接的SQL中调用该函数即可：

val groupId: Int = 你的目标分组值
val query = s"SELECT * FROM table WHERE FUNC_CAL_GROUP_ID(ID) = $groupId"

注意事项 #

• 涉及用户传入参数拼接SQL时，必须使用PreparedStatement预编译占位符替换变量，避免SQL注入风险
• 如果ID列存在非数字结尾的脏数据，建议在SQL中补充转换异常处理逻辑，避免查询报错

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