java - Spring 3.0实现带可选参数的存储过程调用

Question

我一直在努力寻找一种从 Spring 3.0 到 Oracle 11.2 进行存储过程调用的方法，同时牢记以下几点:

• 许多调用都是针对具有过多可选参数(具有默认值的参数)。
• 有些存储过程可以混合使用:IN、OUT 和/或IN OUT。
• 我不需要处理 OUT。

我希望能够使用所需参数(必需和/或可选)调用存储过程。换句话说，我不希望将值(甚至不是null)传递给我选择的可选参数(看起来像当null 以编程方式[尽管不是在 PL/SQL 中] 传递给参数映射器，不使用默认值)。我尝试以尽可能多的方式实现这些调用，但没有任何效果:

create or replace
procedure update_stored_proc (
h1 in boolean default false,
h2 in number,
h3 in varchar2 default 'H3',
h4 in varchar2 default 'H4',
h5 in varchar2 default 'H5',
h6 in out number
);

对于update_stored_proc()，只有两个必需参数(h2 和h6)和四个可选参数。我的最终目标是通过传递 h1、h2 和 h6 来调用 update_stored_proc()。即使当我通过 SimpleJdbcCall 调用存储过程并设置所有值时，我也会得到一个异常:

SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(updatingDataSource)
                    .withProcedureName("update_stored_proc")
                    .withoutProcedureColumnMetaDataAccess();

simpleJdbcCall.declareParameters(new SqlParameter("h1", OracleTypes.BOOLEAN))
              .declareParameters(new SqlParameter("h2", OracleTypes.NUMBER))
              .declareParameters(new SqlParameter("h3", OracleTypes.VARCHAR))
              .declareParameters(new SqlParameter("h4", OracleTypes.VARCHAR))
              .declareParameters(new SqlParameter("h5", OracleTypes.VARCHAR))
              .declareParameters(new SqlInOutParameter("h6", OracleTypes.NUMBER));

MapSqlParameterSource in = new MapSqlParameterSource()
            .addValue("h1", false, OracleTypes.BOOLEAN)
            .addValue("h2", 123, OracleTypes.NUMBER)
            .addValue("h3", "h3", OracleTypes.VARCHAR)
            .addValue("h4", "h4", OracleTypes.VARCHAR)
            .addValue("h5", "h5", OracleTypes.VARCHAR)
            .addValue("h6", "h6", OracleTypes.NUMBER);

simpleJdbcCall.compile();
simpleJdbcCall.execute(in);

我得到的异常表明列类型在某种程度上无效:

org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{call update_stored_proc(?, ?, ?, ?, ?, ?)}]; SQL state [99999]; error code [17004]; Invalid column type; nested exception is java.sql.SQLException: Invalid column type

我已将 OracleTypes 替换为 Types，甚至删除了 withoutProcedureColumnMetaDataAccess()，但错误仍然存​​在。

Answer 1

这个问题原来需要几个解决方案才能开始工作。首先，Oracle 的 SQL 标准实现不包含 BOOLEAN类型，即使其专有的 PL/SQL 脚本语言确实支持它(DBA 不在其存储过程中使用 BOOLEAN 类型的更多原因)。我想到的解决方案是使用匿名 block ，声明一个局部变量，并将其分配给 BOOLEAN参数:

DECLARE
  h1_false BOOLEAN := false;
  h6_ NUMBER;
BEGIN
  update_stored_proc(
  h1 => h1_false,
  h2 => :h2,
  h6 => h6_
  );
END;

请注意，我不关心 h1 的值(就此而言，我的特定功能的 OUT 参数/h6 )参数，但我可以通过简单的 IF THEN ELSE 来想象语句，可以将绑定(bind)参数分配给 h1 ，即

DECLARE
  h1_false BOOLEAN;
  h6_ NUMBER;
BEGIN
  IF :h1 THEN
    h1_false = true;
  ELSE
    h1_false = false;
  END IF;

  update_stored_proc(
  h1 => h1_false,
  h2 => :h2,
  h6 => h6_
  );
END;

第二个问题的方式Spring处理可选参数。如果一个声明可选参数，那么Spring的 SimpleJdbcCall -- 和 StoredProcedure就此而言——期望这些参数的值。因此，当这些值变得可用时，必须注意这些值。否则当可选参数没有值时，必须传NULL 不会触发要使用的 PL/SQL 参数的 DEFAULT 值。这意味着您的查询字符串(在本例中为匿名 PL/SQL block )必须动态生成。因此，匿名 block 变为:

DECLARE
  h1_false BOOLEAN := false;
  h6_ NUMBER;
BEGIN
  update_stored_proc(
  h1 => h1_false,
  h2 => :h2,
  h6 => h6_

我切换到 StoredProcedure解决方案而不是 SimpleJdbcCall ，因为事实证明它更简单。尽管我必须补充一点，我必须延长 StoredProcedure为我的存储过程类创建一个公共(public)类，并将该扩展类用于我的自定义存储过程类。在你的StoredProcedure类，您只需声明所需的参数(确保此时不 compile() 查询):

declareParameter(new SqlParameter("h2", OracleTypes.NUMBER));

注意:如果您不需要 OUT参数，就像我的情况一样，不要将其包含在您的声明中。否则，分配一个绑定(bind)变量，即 h6 => :h6在匿名 block 中并在您的 StoredProcedure 中声明它，即 declareParameter(new SqlOutParameter("h6", OracleTypes.NUMBER));并确保获得 :h6 execute() 时值出来返回 Map<String, Object> .如果你的OUT值为 BOOLEAN类型，那么我不知道如何检索该值。

其余的可选参数应该在你的StoredProcedure中动态构建.即:

if (someObj.getH3() != null) {
  declareParameter(new SqlParameter("h3", OracleTypes.VARCHAR));
  paramMap.put("h3", someObj.getH3());
  anonymousPLSQLBlockQueryString += " , h3 => :h3";
}

哪里paramMap代表 Map那将被传递给StoredProcedure#execute(paramMap) .你对 h4 做同样的事情和 h5 ，最后，您必须确保正确关闭匿名 block 查询字符串，即:

anonymousPLSQLBlockQueryString += " ); END;";
setSql(anonymousPLSQLBlockQueryString);
setSqlReadyForUse(true);
compile();