使用IN和OUT参数扩展Spring StoredProcedure [英] Extending Spring StoredProcedure with IN and OUT parameters

问题描述

我有以下运行良好的Oracle PL/SQL调用:

I have the following Oracle PL/SQL call which works perfectly fine:

declare
  dummy number;
begin
   my.proc(a => 89561,
           b => 1,
           c => 1,
           d => '',
           e => 1,
           f => 1,
           g => 1,
           h => 1,
           i => 1,
           j => 1,
           k => 1,
           l => 1,
           m => 1,
           n => 1,
           o => 1,
           p => 1,
          q => dummy);
 end;

我想以编程方式调用它，为此，我创建了一个扩展 org.springframework.jdbc.object.StoredProcedure 的类.类如下:

I want to call it programmatically and for this I created a class which extends org.springframework.jdbc.object.StoredProcedure. Class is the following:

import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.jdbc.object.StoredProcedure;

public class MySP extends StoredProcedure {

    private static final String PROC_NAME = "my.proc";

    public MySP(DataSource ds) {
        super(ds,PROC_NAME);
        compile();
    }

    public void execute() {
        Map<String, Object>  params = new HashMap<>();
        params.put("a", 89561L);
        params.put("b", 1L);
        params.put("c", 1L);
        params.put("d", "");
        params.put("e", 1L);
        params.put("f", 1L);
        params.put("g", 1L);
        params.put("h", 1L);
        params.put("i", 1L);
        params.put("j", 1L);
        params.put("k", 1L);
        params.put("l", 1L);
        params.put("m", 1L);
        params.put("n", 1L);
        params.put("o", 1L);
        params.put("p", 1L);
        params.put("q", 0L);
        super.execute(params);
    }
}

运行他的代码时，出现以下Oracle错误:

When I run his code, I'm geting the following Oracle error:

PLS-00306:调用"proc"时参数的数量或类型错误

我很确定错误是由于我传递"q"参数的方式引起的……但是找不到其他传递它的方式.

I'm pretty sure the error comes from the way I'm passing the "q" parameter ... but can't find another way of passing it.

有什么主意吗?

推荐答案

Spring StoredProcedure要求您声明要传递的参数的类型.请参见下面的示例.

Spring StoredProcedure requires you to declare the type of the parameters that you're going to pass. See the example below.

public class MySP extends StoredProcedure {
    private static final String PROC_NAME = "my.proc";

    public MySP(DataSource ds) {
        super(ds,PROC_NAME);

        //declaraction of parameters
        declareParameter(new SqlParameter("x", java.sql.Types.NUMERIC));
        declareParameter(new SqlOutParameter("y", java.sql.Types.VARCHAR));
        declareParameter(new SqlInOutParameter("z", java.sql.Types.VARCHAR));
    }
    public void execute() {
        Map<String, Object>  params = new HashMap<>();
        params.put("x", 89561L);
        params.put("y", 1L);
        params.put("z", 1L);

        super.execute(params);
    }

}

这篇关于使用IN和OUT参数扩展Spring StoredProcedure的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！