java - JDBC oracle 存储过程，处理嵌套对象？

Question

我正在尝试调用一个 Oracle 存储过程，它结合了嵌套对象和表，如下所示，

CREATE OR REPLACE PROCEDURE MYSCHEMA.MY_PROC(
    p_obj_school IN OUT OBJ_SCHOOL) IS ...

具有以下对象类型

CREATE TYPE   "OBJ_SCHOOL" as OBJECT (Students LST_STUDENTS);
CREATE TYPE   "LST_STUDENTS" AS TABLE OF STUDENTS;
CREATE TYPE   "STUDENTS" as OBJECT(Subjects LST_SUBJECT);
CREATE TYPE   "LST_SUBJECT" AS TABLE OF SUBJECT;
CREATE TYPE   "SUBJECT" as OBJECT(Marks Number, Remark VARCHAR2(1000 BYTE));

我不确定如何实现这一点。

虽然我尝试了一个类似的存储过程，但参数工作如下

CREATE OR REPLACE PROCEDURE MYSCHEMA.MY_PROC2(
    p_obj_school IN OUT OBJ_SCHOOL2 , id OUT Number ) IS ...

谁的类型比较简单，如下所示

CREATE TYPE "OBJ_SCHOOL2" AS OBJECT (SEQID NUMBER,
STUDENTS OBJ_STUDENTS)
CREATE TYPE  "OBJ_STUDENTS" AS OBJECT (STUDENTNAME VARCHAR2(50 BYTE),CLASS VARCHAR2(2 BYTE),
STUDENTID NUMBER)

使用如下的 java 代码片段

                OBJ_SCHOOL2 objSchool2Request = new OBJ_SCHOOL2();
objSchool2Request.setSEQID(1);
OBJ_STUDENTS objStudentsRequest = new OBJ_STUDENTS();
objStudentsRequest.setSTUDENTNAME("name");
objStudentsRequest.setCLASS("4B");
objStudentsRequest.setSTUDENTID(1);

objSchool2Request.setOBJ_STUDENT(objStudentsRequest);
//where the mapper class is OBJ_SCHOOL2 pojo that implements SQLDATA
            final OBJ_SCHOOL2_MAPPER mapper = new OBJ_SCHOOL2_MAPPER(objSchool2Request);
        
          SimpleJdbcCall simpleJdbcCall =
              new SimpleJdbcCall(dataSource())
                  .withSchemaName("MYSCHEMA")
                  .withProcedureName("MY_PROC2")
                  .declareParameters(new SqlInOutParameter("P_OBJ_SCHOOL", OracleTypes.STRUCT , "MYSCHEMA.OBJ_SCHOOL2" ))
               
                  .declareParameters(new SqlOutParameter("ID", OracleTypes.NUMBER));
                  
                  

          Map<String, Object> inParamMap = new HashMap<String, Object>();
          inParamMap.put("p_obj_school", mapper);
          
          SqlParameterSource in = new MapSqlParameterSource(inParamMap);

          Map<String, Object> simpleJdbcCallResult = simpleJdbcCall.execute(in);

我可以成功调用存储过程。

但是，对于前面提到的存储过程 (MYSCHEMA.MY_PROC)，我不确定如何实现包含“表”类型的嵌套对象。

标签： javaoraclestored-proceduresjdbctemplatejava-stored-procedures