create or replace function json_extract(col varchar,in_ky varchar) return varchar
as
ret varchar;
tmp varchar;
keys varchar;
ky  varchar;
cnt int;
begin
  -- 获取key值
  ky:=substr(in_ky,1);
  -- 去除json串首尾的{}
  tmp:=substr(col,2,len(col)-2);
  -- 计算k-v对数量(算出分隔符逗号','的个数，然后+1即为k-v对数量)
  cnt:=len(tmp)-len(replace(tmp,',',''))+1;
  for i in 1..cnt loop
  -- 遍历每一组k-v对
  ret:=regexp_substr(tmp,'[^,]+',1,i);
  -- 获取k-v对的key值并做格式处理(去掉双引号'"')
  keys:=replace(regexp_substr(ret,'[^:]+',1,1),'"','');
  -- 判断key值是否匹配目标key,若匹配则返回对应的value值
  if ky=keys then
    return replace(regexp_substr(ret,'[^:]+',1,2),'"','');
  end if;
  end loop;
end;

create type v_varray is varray(100) of varchar;
create or replace function json_array(chrlst_split_comma varchar) return v_varray
is
cnt int;
--type v_varray is varray(100) of varchar;
arraylist v_varray;
begin
	-- 计算入参中逗号分隔符的数量
	cnt := (length(chrlst_split_comma)-length(replace(chrlst_split_comma,',','')))/length(',');
	-- 扩展长度为分隔符数量+1(字符串数量)的数组
	arraylist.extend(cnt+1);
	for i in 1..cnt+1 loop
	-- 按数组下标依次赋值为对应位置的字符串
	arraylist(i) := regexp_substr(chrlst_split_comma,'[^,]+',1,i);
	end loop;
return arraylist;
end json_array;