从java向oracle过程发送对象数组

Question

我尝试将json的json包含数组发送到procedure，此过程会将两个表中的数据相加，我构建了以下过程：

create or replace PROCEDURE INSERT_ELE
(
  ELEMENT_SET_NAME IN VARCHAR2 
, ELEMENT_SET_TYPE VARCHAR2 
, EFFECTIVE_START_DATE IN VARCHAR2 
, EFFECTIVE_END_DATE IN VARCHAR2
, ELEMENT_TYPE_ID IN VARCHAR2 
, OUT_SEQ OUT NUMBER
) AS 
BEGIN
  INSERT ALL INTO payroll_test.PAY_ELEMENT_SETS(ELEMENT_SET_NAME, ELEMENT_SET_TYPE, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE)
  VALUES (ELEMENT_SET_NAME, ELEMENT_SET_TYPE, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE)
  
  INTO payroll_test.PAY_ELEMENT_SET_MEMBERS(ELEMENT_TYPE_ID, ELEMENT_SET_ID)
  VALUES (ELEMENT_TYPE_ID, PAY_ELEMENT_SETS_MEMBERS_SEQ.NEXTVAL +1)
  select  ELEMENT_SET_NAME as ELEMENT_SET_NAME, ELEMENT_SET_TYPE as ELEMENT_SET_TYPE, EFFECTIVE_START_DATE as EFFECTIVE_START_DATE, EFFECTIVE_END_DATE as EFFECTIVE_END_DATE, ELEMENT_TYPE_ID as ELEMENT_TYPE_ID  from dual;
  COMMIT;
END INSERT_ELE

这是我的java代码：

            String query = null;
            query = "{call INSERT_ELE(?,?,?,?,?,?)}";
            cstmt = connection.prepareCall(query);
            cstmt.setString(1, objectGroupFormBean.getElementSetName());
            cstmt.setString(2, objectGroupFormBean.getElementSetType());
            cstmt.setString(3, objectGroupFormBean.getEffectiveStartDate());
            cstmt.setString(4, objectGroupFormBean.getEffectiveEndDate());
            cstmt.setString(5, objectGroupFormBean.getElementTypeId());
            cstmt.registerOutParameter(6, OracleTypes.NUMBER);
            cstmt.executeUpdate();
            objectGroupFormBean.setElementSetId(cstmt.getInt(6));
            objectGroupFormBean.getElementSetId();
            objectGroupFormBeanList.add(objectGroupFormBean);

但这只是像这样接受有效载荷：

{
    "elementSetName": "test",
    "elementSetType": "App",
    "effectiveStartDate": "10-10-1981",
    "effectiveEndDate": "20-08-2020",
    "element":
        {
            "elementId": "181",
            "inclusionStatus": "Include"
        }
    
}

我怎样才能像这样把元素作为对象的json：

{
    "elementSetName": "test",
    "elementSetType": "App",
    "effectiveStartDate": "10-10-1981",
    "effectiveEndDate": "20-08-2020",
    "element": [
        {
            "elementId": "181",
            "inclusionStatus": "Include"
        },
        {
            "elementId": "189",
            "inclusionStatus": "Include"
        }
    ]
}

Answer 1

如何反转这个来避免所有的参数传递。只需将JSON发送到过程并让它进行插入，例如

SQL> with t as
  2  (
  3    select
  4    '{
  5        "elementSetName": "test",
  6        "elementSetType": "App",
  7        "effectiveStartDate": "10-10-1981",
  8        "effectiveEndDate": "20-08-2020",
  9        "element": [
 10            {
 11                "elementId": "181",
 12                "inclusionStatus": "Include"
 13            },
 14            {
 15                "elementId": "189",
 16                "inclusionStatus": "Include"
 17            }
 18        ]
 19    }' j from dual
 20  )
 21  select
 22    json_value(j,'$.elementSetName') name,
 23    json_value(j,'$.elementSetType') type,
 24    json_value(j,'$.effectiveStartDate') sdate,
 25    json_value(j,'$.effectiveEndDate') edate
 26  from t;

NAME            TYPE            SDATE           EDATE
--------------- --------------- --------------- ---------------
test            App             10-10-1981      20-08-2020

1 row selected.

SQL>
SQL> with t as
  2  (
  3    select
  4    '{
  5        "elementSetName": "test",
  6        "elementSetType": "App",
  7        "effectiveStartDate": "10-10-1981",
  8        "effectiveEndDate": "20-08-2020",
  9        "element": [
 10            {
 11                "elementId": "181",
 12                "inclusionStatus": "Include"
 13            },
 14            {
 15                "elementId": "189",
 16                "inclusionStatus": "Include"
 17            }
 18        ]
 19    }' j from dual
 20  )
 21  select jt.*
 22  from t,
 23     json_table(j,'$.element[*]' columns ( elementId number path '$.elementId',
 24                                        inclusionStatus varchar2(20) path '$.inclusionStatus')) jt;

 ELEMENTID INCLUSIONSTATUS
---------- --------------------
       181 Include
       189 Include

2 rows selected.

SQL>
SQL>
SQL>

因此，您的过程最终将是

procedure INS(p_json varchar2) is
begin
  insert into ...
  select ...
end;

其中，SELECT仅基于上面的内容