这是我的oracle程序规范
CREATE OR REPLACE PACKAGE PKG_RE_FI AS
PROCEDURE PRC_RE_FI_DETAILS(P_FAN_NO IN VARCHAR2,
P_REF_ID IN TY_APP_REF_ID,
P_COMMENTS IN VARCHAR2,
P_BILLING_FLAG IN VARCHAR2,
P_STATUS OUT VARCHAR2);
END PKG_RE_FI;
TY_APP_REF_ID是
CREATE OR REPLACE TYPE ty_app_REF_ID as varray(500) of obj_array_ref_id
CREATE OR REPLACE TYPE obj_array_ref_id AS OBJECT(
app_ref_id VARCHAR2(100)
)
我使用Spring JDBC Framework(SimpleJdbcCall对象)来执行上述过程.下面是我声明的代码片段
this.reFIJdbcCall = new SimpleJdbcCall(dataSource).withCatalogName("PKG_RE_FI").
withProcedureName("PRC_RE_FI_DETAILS").declareParameters(new SqlParameter("P_FAN_NO", Types.VARCHAR),
new SqlParameter("P_REF_ID", Types.ARRAY),
new SqlParameter("P_COMMENTS", Types.VARCHAR),
new SqlParameter("P_BILLING_FLAG", Types.VARCHAR),
new SqlOutParameter("P_STATUS", Types.VARCHAR)
);
我应该如何将数组传递给
new SqlParameter("P_REF_ID", Types.ARRAY),
到MapSqlParameterSource
MapSqlParameterSource in = new MapSqlParameterSource();
解决方法:
PeudoCode与我的实现方式相同.
# 1.You will require a structDescriptor object for an object equivalent in pl sql like :
StructDescriptor structDes= new StructDescriptor(".", connectionObject);
# 2. You will need to pass one object values such name, class, id to an object array in order and accordance to 'sql_object_name' object.
For exmaple:
STRUCT[] structArray=new STRUCT[.size()];
int index=0;
for (a in ListObj){
Object[] object=new Object[]{a.getName(),a.getId()};
STRUCT struct=new STRUCT(structDes ,connectionObject,object);
structArray[index]=struct;
index++;
}
ArrayDescriptor arrayDes=ArrayDescriptor.createDescriptor(
".
ARRAY array=new ARRAY(arrayDes,connectionObject, structArray);
then pass it to proc
.declareParameters(
new SqlInOutParameter("",OracleTypes.ARRAY,"
."))
like
Hashmap map= new HashMap<>();
map.put("",array);
psStatement.execute(map);
希望能帮助到你.此序列可能根据所使用的sql数据库的要求和类型而有所不同,但基数相同.
标签:spring-jdbc,java,spring,oracle,stored-procedures
来源: https://codeday.me/bug/20191008/1873099.html