如何正确编写PL/SQL函数并在Scala等其他语言中调用

你遇到的报错主要是PL/SQL语法使用错误导致的，两个报错的具体根源如下：

• 第一个PLS-00103错误：PL/SQL函数的IS/AS关键字之后到BEGIN之前的区域就是默认的变量声明段，不需要额外加DECLARE关键字，多余的DECLARE触发了语法校验失败。
• 第二个PLS-00103错误：一是你把函数创建语句和查询语句放在同一段脚本执行，没有正确的语句结束标识区分两段代码；二是Oracle中取模运算没有%运算符，需要用标准MOD()函数；三是SQL中字符串值、普通列名不需要用双引号包裹，错误的引号使用也会导致语法解析失败。

修正后的函数代码如下，直接在SQL Developer中执行即可完成创建：

CREATE OR REPLACE FUNCTION func_id(
    str_id IN VARCHAR2,
    num_group IN NUMBER
) RETURN NUMBER
IS
    -- 变量直接在IS和BEGIN之间声明，无需加DECLARE
    temp VARCHAR2(2);
    result NUMBER;
BEGIN
    -- 取str_id末尾最多2位字符
    temp := substr(str_id, -least(length(str_id), 2));
    -- Oracle取模使用标准MOD函数，不要用%
    result := MOD(TO_NUMBER(temp), num_group);
    RETURN result;
EXCEPTION
    -- 新增异常处理，避免str_id末尾非数字时中断查询
    WHEN OTHERS THEN
        RETURN 0; -- 可根据业务需求自定义异常返回值
END;
/

1. 直接在SQL中调用 #

普通查询使用时直接把函数写到WHERE子句中即可，示例：

-- 固定参数调用，替换为你实际的表名
SELECT * FROM 你的业务表 WHERE func_id(id, 2) = 1;

-- 预留占位符的动态查询写法，供程序调用时绑定参数
SELECT * FROM 你的业务表 WHERE func_id(id, ?) = ?;

2. 在Scala程序中调用 #

以JDBC调用为例，使用PreparedStatement做参数绑定即可替换两个占位值，避免SQL注入风险，示例代码：

import java.sql.{Connection, DriverManager, PreparedStatement, ResultSet}

object OracleFunctionCall {
  def main(args: Array[String]): Unit = {
    // 替换为你实际的数据库连接配置
    val jdbcUrl = "jdbc:oracle:thin:@//数据库地址:端口/服务名"
    val dbUser = "你的用户名"
    val dbPwd = "你的密码"

    var conn: Connection = null
    var pstmt: PreparedStatement = null
    var rs: ResultSet = null

    try {
      Class.forName("oracle.jdbc.driver.OracleDriver")
      conn = DriverManager.getConnection(jdbcUrl, dbUser, dbPwd)

      // 你要动态替换的两个变量值
      val groupCount: Int = 2
      val targetModValue: Int = 1

      val querySql = "SELECT * FROM 你的业务表 WHERE func_id(id, ?) = ?"
      pstmt = conn.prepareStatement(querySql)
      // 按顺序绑定占位符参数
      pstmt.setInt(1, groupCount)
      pstmt.setInt(2, targetModValue)

      rs = pstmt.executeQuery()
      // 处理查询结果
      while (rs.next()) {
        // 按你的业务字段读取数据即可
        println(rs.getString("id"))
      }
    } catch {
      case e: Exception => e.printStackTrace()
    } finally {
      // 按顺序关闭资源
      if (rs != null) rs.close()
      if (pstmt != null) pstmt.close()
      if (conn != null) conn.close()
    }
  }
}

如果使用Spark、MyBatis等框架，直接把函数写入SQL语句，使用框架自带的参数绑定机制替换占位值即可。

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