Oracle参数传list集合clob

procedure 存储过程名(
    v_transfer_list  clob,--单号、vip编号集合。例子：'[{"transfer_id":"aa16120100000005","vip_id":100654455},{"transfer_id":"aa16120100000005","vip_id":100655374}]'
)
is
  v_transfer_id varchar;
  v_vip_id number;
  v_jsonarray json_list;
  v_jsonobj json;
  v_cnt1 pls_integer;
  v_cnt2 pls_integer;
  v_json_value json_value;
  v_json_keylist json_list;
  v_json_keycode varchar(100);
begin
  
  v_jsonArray:=json_list(v_transfer_list);
  if v_jsonArray.count=0 then
    return;
  end if;
  
  --循环取出每个JsonObject
  for v_cnt1 in 1..v_jsonArray.count loop
 
    v_jsonObj := json(v_jsonArray.get(v_cnt1));--单个LIST集合
    v_json_keylist:=v_jsonObj.get_keys();--主键集合
    
    for v_cnt2 in 1..v_jsonObj.count loop
      v_json_keycode:=v_json_keylist.get(v_cnt2).get_string;--获取主键编号
      v_json_value:=v_jsonObj.get(v_cnt2);--获取值
      
      IF v_json_keycode='transfer_id' THEN 
        v_transfer_id:=v_json_value.get_string;
      END IF;
      
      IF v_json_keycode='vip_id' THEN 
        v_vip_id:=v_json_value.get_number;
      END IF;  
    end loop;
    
  end loop;

exception when others then
  rollback;
end 存储过程名;