|
|
Oracle and JSON: Using PL/JSON
JSON (JavaScript Object Notation) is a lightweight data format that is very well suited for transmitting data over the Internet. Despite thereference to JavaScript in its name, JSON is a language-independent syntax and native support for it has been included in many modern programming languages. In fact, JSON is so popular nowadays that entire database management systems have built their record structure around it,for example Apache CouchDB.
As a Web developer who does a lot of work with PL/SQL, I have recently found myself creating PL/SQL procedures that output JSON data, and making these procedures available as RESTful HTTP services that are then consumed by Ajax functions in the front-end of my applications. This can be a bit of a pain, however, as I need to output the JSON data manually, making it very prone to errors and very difficult to debug in many cases.
Thankfully, an open source library for PL/SQL called PL/JSON is available that resolves some of the issues associated with working with JSON data in an Oracle application. In this post, you will learn how to install and use PL/JSON to work with JSON data in your own PL/SQL applications.
Download and install the library
The latest version of PL/JSON, at the time of writing, is version 0.8.6. The compressed archive is less than 200KB in size. You can download PL/JSON from SourceForge at http://sourceforge.net/projects/pljson/files/. Unzipthe library to your hard drive and install it in a SQL*Plus session using the following command:
You should see the following output:
| ----------------------------------- |
| -- Compiling objects for PL/JSON -- |
| ----------------------------------- |
| PL/SQL procedure successfully complete |
Assuming you see no errors from running the install script, PL/JSON is now installed in your Oracle database.
Using PL/JSON
With PL/JSON installed, you can now start working with the traditional “Hello, world” example, using the following PL/SQL code:
| 05 | --Create a new JSON object, passing the JSON code as the constructor |
| 06 | jsonObj := json('{"greeting":"Hello World"}'); |
| 07 | --Output the value for the JSON data with the key "greeting" |
| 08 | dbms_output.put_line(json_ext.get_varchar2(jsonObj, 'greeting')); |
This produces the following output:
As you can see from the above output, PL/JSON parses JSON data. But what if you want to create a JSON object without actually providing the data in JSON format. Take the following example:
| 05 | --Use an empty constructor to create a blank JSON object |
| 07 | -- Use the put procedure to add a string, number, boolean and null property to the JSON object |
| 08 | jsonObj.put('name', 'Joe Lennon'); |
| 09 | jsonObj.put('age', 24); |
| 10 | jsonObj.put('awesome', json_bool(true)); |
| 11 | jsonObj.put('children', json_null()); |
| 13 | --Print the string representation of the JSON object (pretty-print) |
| 15 | dbms_output.put_line('Am I awesome?'); |
| 16 | --Use getters to retrieve the value of the boolean property "awesome" and print the result |
| 17 | dbms_output.put_line(json_ext.get_json_bool(jsonObj, 'awesome').to_char); |
The output for the above looks like:
JSON Arrays
JSON objects can contain data in several types – number, string, boolean, null or array. At this point, you’ve seen how to work with the first four of these types, but not arrays. PL/JSON refers to these as a json_list. Working with json_list objects is very similar to working with a regular JSON object, as shown in the following example:
| 06 | jsonArray := json_list('[{"name":"Joe","age":24},{"name":"Jill","age":26}]'); |
| 07 | jsonObj := json.to_json(jsonArray.get_elem(1)); |
| 08 | dbms_output.put_line(json_ext.get_varchar2(jsonObj, 'name')); |
| 09 | jsonObj := json.to_json(jsonArray.get_elem(2)); |
| 10 | dbms_output.put_line(to_char(json_ext.get_number(jsonObj, 'age'))); |
The above code create a JSON array (or json_list) containing two objects with two properties, name and age. The first object has the name“Joe” and age 24, the second has name “Jill” and age 26. The get_elem function is used to return an item in the JSON array, and the to_json function casts this is a JSON object. In the above example, you first assign jsonObj to the first object in the JSON array, printing out the value of the name property for this object (in this case, the string value “Joe”). Next, you assign jsonObj to the second object in the array, and print out the value of the age property (in this case, age is26). The output for this example is as follows:
To wrap up this post, let’s have a look at a more complex scenario for using JSON in PL/SQL. Let’s say that you want to create a JSON representation of the result of a SQL statement. The following example demonstrates just that:
| 07 | select initcap(lower(object_type)) name, count(*) count |
| 09 | where upper(object_type) like 'INDEX%' |
| 12 | jsonArray := json_list(); |
| 13 | for objType in get_data loop |
| 15 | jsonObj.put('object_type', objType.name); |
| 16 | jsonObj.put('count', objType.count); |
| 17 | jsonArray.add_elem(jsonObj.to_anydata); |
| 24 | for i in 1..jsonArray.count loop |
| 26 | json_ext.get_varchar2( |
| 27 | json.to_json(jsonArray.get_elem(i)), |
| 30 | to_char(json_ext.get_number( |
| 31 | json.to_json(jsonArray.get_elem(i)), |
In the example above, you first initialize the JSON array to an emptyjson_list. Next, you loop through the get_data cursor, which selects back the counts for all object types in the database that begin with “INDEX”. For each iteration of this loop, a JSON object is created, the object type name and count are added as properties to the object, and the object is added to the JSON array. Then the string representation ofthe array is printed as output. Next, you loop through the JSON array itself, and print out the object_type and count properties for each itemin the list. The overall output should look as follows:
| "object_type" : "IndexPartition", |
| "object_type" : "Indextype", |
Indextype->8 |
|
QQ群⑧：
窥视卡
雷达卡
发表于 2016-7-28 09:38:55
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡