JSON_OBJECT_T¶

JSON_OBJECT_T是JSON_ELEMENT_T的子类型，对应json类型中的对象结构。

JSON_OBJECT_T对象类型包含的子程序如下：

构造函数¶

CONSTRUCTOR FUNCTION JSON_OBJECT_T RETURN SELF AS RESULT
STATIC FUNCTION parse(json VARCHAR2) RETURN JSON_OBJECT_T
STATIC FUNCTION parse(json CLOB) RETURN JSON_OBJECT_T
STATIC FUNCTION parse(json BLOB) RETURN JSON_OBJECT_T
CONSTRUCTOR FUNCTION JSON_OBJECT_T(jsn VARCHAR2) RETURN SELF AS RESULT
CONSTRUCTOR FUNCTION JSON_OBJECT_T(jsn CLOB) RETURN SELF AS RESULT
CONSTRUCTOR FUNCTION JSON_OBJECT_T(jsn BLOB) RETURN SELF AS RESULT
CONSTRUCTOR FUNCTION JSON_OBJECT_T(e JSON_ELEMENT_T) RETURN SELF AS RESULT

构造函数JSON_OBJECT_T可以构造一个空的JSON_OBJECT_T实例。
如果构造函数的输入不满足对应的json对象有效值，则会报错。

GET子程序¶

MEMBER FUNCTION get(key VARCHAR2) RETURN JSON_ELEMENT_T
MEMBER FUNCTION get_String(key VARCHAR2) RETURN VARCHAR2
MEMBER FUNCTION get_Number(key VARCHAR2) RETURN NUMBER
MEMBER FUNCTION get_Boolean(key VARCHAR2) RETURN BOOLEAN
MEMBER FUNCTION get_Clob(key VARCHAR2) RETURN CLOB
MEMBER FUNCTION get_Blob(key VARCHAR2) RETURN BLOB
MEMBER FUNCTION get_Object(key VARCHAR2) RETURN JSON_OBJECT_T_T
MEMBER FUNCTION get_Array(key VARCHAR2) RETURN JSON_ARRAY_T_T
MEMBER PROCEDURE get_Clob(key NUMBER, c IN OUT CLOB)
MEMBER PROCEDURE get_Blob(key NUMBER, c IN OUT BLOB)

GET函数和过程根据输入的属性名key找到json对象中对应的值并转换为对应的类型。

注解

神通的GET子程序是拷贝函数/过程，修改GET子程序返回的对象不会修改包含该对象的实参。

SET过程¶

MEMBER PROCEDURE put(key VARCHAR2, value JSON_ELEMENT_T)
MEMBER PROCEDURE put(key VARCHAR2, value VARCHAR2)
MEMBER PROCEDURE put(key VARCHAR2, value NUMBER)
MEMBER PROCEDURE put(key VARCHAR2, value BOOLEAN)
MEMBER PROCEDURE put_Null(key VARCHAR2)

SET过程将json对象中输入属性名key对应的值设为指定值。如果该属性存在则替换原有值，如果不存在则进行插入。

内省函数¶

MEMBER FUNCTION has(key VARCHAR2) RETURN BOOLEAN
MEMBER FUNCTION get_Type(key VARCHAR2) RETURN VARCHAR2
MEMBER FUNCTION get_Keys RETURN JSON_KEY_LIST

has函数判断对象中是否包含key属性。
get_Type函数返回对象中key属性对应值的类型，可以为数组、对象、字符串、数字、null、布尔值。
get_Keys函数返回JSON_KEY_LIST对象，详情见 JSON_KEY_LIST 章节

修改过程¶

MEMBER PROCEDURE remove(key VARCHAR2)
MEMBER PROCEDURE rename_Key(keyOld VARCHAR2, keyNew VARCHAR2)

remove过程将对象中key属性的对结构删除，如果属性不存在则忽略。
rename_Key过程将对象中keyOld属性重命名为keyNew，如果属性不存在或者属性名重复则报错。

克隆函数¶

MEMBER FUNCTION clone RETURN JSON_OBJECT_T

克隆函数返回该对象的拷贝。

示例¶

--remove
create or replace procedure proc1 as
  data    JSON_OBJECT_T;
begin
  data := JSON_OBJECT_T('{
	"first": "John",
	"last": "Doe",
	"address": {
		"country": "USA",
		"zip": "94065"
	}
  }'); 
  data.remove('second');
  data.remove('first');
  dbms_output.put_line(data.to_string);
end;

/
exec proc1;
drop procedure proc1;

drop table t1 cascade;
create table t1(col1 varchar2(50));
insert into t1 values('{"product":"carrot","price":10}');
create or replace function updatePirce(jsonTxt in varchar2) return varchar2 is
	jo json_object_t;
	oldPrice number;
begin
	jo:=json_object_t(jsonTxt);
	oldPrice:=jo.get_Number('price');
	jo.put('price',oldPrice*1.1);
	return jo.to_String;
end;

/
select updatePirce(col1) from t1;
UPDATEPIRCE(varchar)      |
--------------------------
{"price": 11, "product": "|
carrot"}                  |
总数目：1
drop table t1 cascade;