时间戳

首页 > 解决方案 > Oracle APEX 中的 POST 请求（数组）

json - Oracle APEX 中的 POST 请求（数组）

问题描述

我在json中有一个数组，我想把它写在oracle方法post中。但是我在某个地方找不到错误。我尝试将它全部写在一个循环中，代码编译但不写。我做的第一件事是使用循环查找第一个对象。然后我在对象中间使用另一个循环来记录其中的数据！

    v_clob := iot_general.blob_to_clob(p_blob); 
    apex_json.parse(tv, v_clob); 
    sCount := APEX_JSON.get_count(p_path => 'GroupSensor' , p_values => tv); 
    IF sCount > 0 THEN 
      FOR i in 1 .. sCount LOOP 
      v_id := apex_json.get_varchar2(p_path => 'GroupSensor.SerialNumber['|| i ||']', p_values => tv);  
                  cCount := APEX_JSON.get_count(p_path => 'GroupSensor.GroupBob['|| i ||']' , p_values => tv); 
                  IF cCount > 0 THEN 
                              FOR q in 1 .. cCount LOOP 
                              q_temp   := apex_json.get_varchar2(p_path => 'GroupSensor.GroupBob['|| i ||']['|| q ||']', p_values => tv);  
                                INSERT INTO SILO_SENSOR( NAME,  DEVICES_ID)  
                                VALUES (q_temp,v_id  ); 
                                commit; 
                                END LOOP;    
                 END IF; 
      END LOOP; 
    END IF;

这是我的json

 {
  "GroupSensor": [
    {
      "silos": 1,
      "GroupBob": [
        "SENSOR0001",
        "SENSOR0002",
        "SENSOR0003",
        "SENSOR0004",
        "SENSOR0005",
        "SENSOR0006",
        "SENSOR0007",
        "SENSOR0008",
        "SENSOR0009",
        "SENSOR0010"
      ],
      "SerialNumber": "1701"
    },
    {
      "silos": 1,
      "GroupBob": [
        "SENSOR0011",
        "SENSOR0012",
        "SENSOR0013",
        "SENSOR0014",
        "SENSOR0015",
        "SENSOR0016",
        "SENSOR0017",
        "SENSOR0018",
        "SENSOR0019"
      ],
      "SerialNumber": "1702"
    },
    {
      "silos": 1,
      "GroupBob": [
        "SENSOR0020",
        "SENSOR0021",
        "SENSOR0022",
        "SENSOR0023",
        "SENSOR0024",
        "SENSOR0025",
        "SENSOR0026",
        "SENSOR0027",
        "SENSOR0028"
      ],
      "SerialNumber": "1703"
    }
]

}

我想将数据写入 SILO_SENSOR 表，就是这样

NAME            DEVICES_ID
SENSOR0001      1701
SENSOR0002      1701
SENSOR0003      1701
SENSOR0004      1701
SENSOR0005      1701
SENSOR0006      1701
SENSOR0007      1701
SENSOR0008      1701
SENSOR0009      1701
SENSOR0010      1701

标签： jsonoraclepostplsqloracle-apex

解决方案

鉴于此表：

create table silo_sensor (
  name      varchar2(255),
  devices_id varchar2(255)
);

以下应该有效：

declare

  tv     apex_json.t_values;
  v_clob clob;
  scount number;
  ccount number;
  q_temp varchar2(255);
  v_id   varchar2(255);

begin

  v_clob := q'-
    {
      "GroupSensor": [
        {
          "silos": 1,
          "GroupBob": [
            "SENSOR0001",
            "SENSOR0002",
            "SENSOR0003",
            "SENSOR0004",
            "SENSOR0005",
            "SENSOR0006",
            "SENSOR0007",
            "SENSOR0008",
            "SENSOR0009",
            "SENSOR0010"
          ],
          "SerialNumber": "1701"
        },
        {
          "silos": 1,
          "GroupBob": [
            "SENSOR0011",
            "SENSOR0012",
            "SENSOR0013",
            "SENSOR0014",
            "SENSOR0015",
            "SENSOR0016",
            "SENSOR0017",
            "SENSOR0018",
            "SENSOR0019"
          ],
          "SerialNumber": "1702"
        },
        {
          "silos": 1,
          "GroupBob": [
            "SENSOR0020",
            "SENSOR0021",
            "SENSOR0022",
            "SENSOR0023",
            "SENSOR0024",
            "SENSOR0025",
            "SENSOR0026",
            "SENSOR0027",
            "SENSOR0028"
          ],
          "SerialNumber": "1703"
        }
      ]
    }
  -';

  apex_json.parse(tv, v_clob); 

  sCount := APEX_JSON.get_count(p_path => 'GroupSensor' , p_values => tv); 

  for i in 1 .. sCount loop 
    v_id := apex_json.get_varchar2(p_path => 'GroupSensor[%d].SerialNumber', p_values => tv, p0 => i);  

    cCount := APEX_JSON.get_count(p_path => 'GroupSensor[%d].GroupBob' , p_values => tv, p0 => i); 

    for q in 1 .. cCount loop 
      q_temp := apex_json.get_varchar2(p_path => 'GroupSensor[%d].GroupBob[%d]', p_values => tv, p0 => i, p1 => q);  
      insert into silo_sensor(name, devices_id)  
      values (q_temp, v_id); 
    end loop;    
  end loop;

  commit;

end;

有几点需要注意：

  • 您不需要IF sCount > 0 THENorIF cCount > 0 THEN检查。只需进入循环，如果计数为 0，那么您将不会进入它。您可能需要检查的唯一原因if是该属性是否可能不存在。在这种情况下，您的检查应该是if sCount is not null因为get_varchar2如果属性不存在将返回 null 。

  • 不要在循环内提​​交。如果您在循环时遇到错误，则您将提交部分事务，并且修复将更加困难。要么在最后提交，要么根本不提交（让调用者在他们想要的时候提交）。

因为您在 18c 上运行，所以您可以使用 JSON 的内置 PL/SQL 类型。这些应该比 APEX_JSON 执行得更好。

declare

  top_obj json_object_t;
  gs_arr  json_array_t;
  gs_obj  json_object_t;
  gb_arr  json_array_t;
  v_clob  clob;
  q_temp  varchar2(255);
  v_id    varchar2(255);

begin

  v_clob := q'-
    {
      "GroupSensor": [
        {
          "silos": 1,
          "GroupBob": [
            "SENSOR0001",
            "SENSOR0002",
            "SENSOR0003",
            "SENSOR0004",
            "SENSOR0005",
            "SENSOR0006",
            "SENSOR0007",
            "SENSOR0008",
            "SENSOR0009",
            "SENSOR0010"
          ],
          "SerialNumber": "1701"
        },
        {
          "silos": 1,
          "GroupBob": [
            "SENSOR0011",
            "SENSOR0012",
            "SENSOR0013",
            "SENSOR0014",
            "SENSOR0015",
            "SENSOR0016",
            "SENSOR0017",
            "SENSOR0018",
            "SENSOR0019"
          ],
          "SerialNumber": "1702"
        },
        {
          "silos": 1,
          "GroupBob": [
            "SENSOR0020",
            "SENSOR0021",
            "SENSOR0022",
            "SENSOR0023",
            "SENSOR0024",
            "SENSOR0025",
            "SENSOR0026",
            "SENSOR0027",
            "SENSOR0028"
          ],
          "SerialNumber": "1703"
        }
      ]
    }
  -';

  top_obj := json_object_t.parse(v_clob);
  gs_arr := top_obj.get_array('GroupSensor');

  for i in 0 .. gs_arr.get_size - 1 loop 
    gs_obj := json_object_t(gs_arr.get(i));
    v_id := gs_obj.get_string('SerialNumber');
    gb_arr := gs_obj.get_array('GroupBob');

    for q in 0 .. gb_arr.get_size - 1 loop 
      q_temp := gb_arr.get_string(i);
      insert into silo_sensor(name, devices_id)  
      values (q_temp, v_id); 
    end loop;    
  end loop;

  commit;

end;

推荐阅读