如何使用plsql pljson框架解析json树? [英] How to parse a json tree using plsql pljson framework?

问题描述

我正在使用网络服务 http://maps.google. com/maps/api/geocode/json?address = mysore 即可在我的plsql代码中获取位置数据.我可以使用在oracle中使用json 的帮助来访问第一级数据.但这为从json获取一级数据提供了帮助.

I am using a webservice http://maps.google.com/maps/api/geocode/json?address=mysore to get location data in my plsql code. I am able to access the first level data using help from work with json in oracle. But this provides help to get first level data from json.

我需要进一步获取lat和lng值.有人可以帮我吗?

I need to further get the lat and lng values. Can anyone help me with this?

Location = {
  "bounds" : {
    "northeast" : {
      "lat" : 44.9483849,
      "lng" : -93.1261959
    },
    "southwest" : {
      "lat" : 44.9223829,
      "lng" : -93.200307
    }
  },
  "location" : {
    "lat" : 44.9330076,
    "lng" : -93.16290629999999
  },
  "location_type" : "APPROXIMATE",
  "viewport" : {
    "northeast" : {
      "lat" : 44.9483849,
      "lng" : -93.1261959
    },
    "southwest" : {
      "lat" : 44.9223829,
      "lng" : -93.200307
    }
  }
}

这是我的代码，用于从google maps api获取地址.我需要从响应中获取纬度，经度和formatted_address.

This is my code to get the address from the google maps api. I need to fetch latitutde, longitude and formatted_address from the response.

CREATE OR REPLACE PROCEDURE geo_lat_long_addr_proc(
      ADDRESS     VARCHAR2 DEFAULT 'EUR')
     IS
      v_debug_mode        BOOLEAN := TRUE;
      v_req               utl_http.req;
      v_resp              utl_http.resp;
      v_msg               VARCHAR2(80);
      v_entire_msg        VARCHAR2(32767) := NULL;
      v_conversion_factor NUMBER;
      v_url               VARCHAR2(256) :=
              'http://maps.google.com/maps/api/geocode/json?address='||
              ADDRESS
              ;
    BEGIN

      v_req := utl_http.begin_request(url => v_url,
         method => 'GET');
      v_resp := utl_http.get_response(r => v_req);

      IF    v_debug_mode
      THEN
         dbms_output.put_line('HTTP Status Return code: '||
                         v_resp.status_code);
      END IF;

      BEGIN
         LOOP
           utl_http.read_text(r => v_resp,data => v_msg);
           v_entire_msg := v_entire_msg||v_msg;
         END LOOP;
      EXCEPTION
         WHEN  utl_http.end_of_body
         THEN  null;
      END;

      IF    v_debug_mode
      THEN  dbms_output.put_line(v_entire_msg);
      END IF;

      utl_http.end_response(r => v_resp);

    EXCEPTION
      WHEN  others
      THEN  RETURN;
    END geo_lat_long_addr_proc;
    /

推荐答案

我能够使用下面的pljson代码获取经纬度，经度和位置数据.

I was able to get lat, lng and location data using the below pljson code.

 l_response_obj     json;
   l_resultObject   json;
   l_list           json_list;
   l_locationObject   json;
   l_geometryObject   json;

l_response_obj := json(l_entire_msg);
      l_list := json_list(l_response_obj.get ('results'));

  -- Grab the first item in the list
  l_resultObject := json(l_list.head);


  -- Show the location data
  l_geometryObject := json(l_resultObject.get ('geometry'));
  l_locationObject := json(l_geometryObject.get ('location'));
  -- dbms_output.put_line ('Lat = ' || locationObject.get ('lat').TO_CHAR ());
  -- dbms_output.put_line ('Lng = ' || locationObject.get ('lng').TO_CHAR ());

  p_o_lat := l_locationObject.get ('lat').TO_CHAR ();
  p_o_lng := l_locationObject.get ('lng').TO_CHAR ();
  p_o_formatted_addr :=
     l_resultObject.get ('formatted_address').TO_CHAR ();

这篇关于如何使用plsql pljson框架解析json树?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！