Timesten内存数据库支持json格式的包

1、近期公司用到了Timesten内存数据库，但是Timesten暂不支持json，所以自己写了个包，用户数据库与json的数据转换，没什么高深的，但是要在Timesten里写也需要时间，所以分享下。 主要包括几个方面的： 1. 根据key获取json的值； 2. 根据index获取Array的值； 3. 根据sys_cursor等生成json数据； 4. 验证是否json格式等备注：数据格式只支持字符串的形式，不支持数字，不然数据会有误。 下面是plsql里写的包，弄了一个星期，测试通过，可以放心使用。有问题可以找qq：313892960。CREATE OR REPLACE packAge PK

2、_TT_JSON AS-timesten数据库，结果集转json格式-从clob（json）获取值/* "channel_fzs":"fzs","app_id_xy":"1001","rewa_type":"PCA","reward_fee":"10.01","reward_date":"yyyy-mm-dd","idfa":"idfa"*/type typ

3、e_array IS TABLE OF varchar2(256) INDEX BY PLS_INTEGER;function fisJsonObject(jsonclob clob) return number;-转function fJsonListToArray(jsonclob clob) return clob; -构造Array函数function jsonArray return clob; function jsonObject return clob;procedure pJsonObjectPut(psjsonclob in out clob, psparam varcha

4、r2, psvalue varchar2 ); procedure pJsonObjectPutClob(psjsonclob in out clob, psparam varchar2, psvalue clob );procedure pJsonArrayPut(psjsonclob in out clob, psvalue clob );function getArrayCount(fclobArray clob) return number; function getArrayValueCom(fclobArray clob,-列表 findex number:=1) return c

5、lob; FUNCTION getJsonValueCom(pjclob clob, param varchar2 )return varchar2;/* -返回指定参数的值FUNCTION get(jclob clob, param varchar2 ) return varchar2; -返回第i个参数的参数值FUNCTION getparam(jclob clob, i number ) return varchar2;-返回第i个参数的域值FUNCTION getvalue(jclob clob, i number ) return varchar2; -返回json值个数 FUNCT

6、ION getcount(jclob clob ) return number; -将结果集转成json格式 function fSelectToJson(pcSelect varchar2,psColumnName varchar2) return clob; -绑定变量查询，返回json function fSelectToJsonBind(pcSelect varchar2,psColumnName varchar2,pcbindclob clob) return clob; -直接查询，返回json procedure pSelectToJson( pcSelect varchar2,

7、-查询sql，用绑定变量 psColumnName varchar2,-结果集的字段 piJsonClob out clob,-返回的json数据结果 piresult out number,-返回结果数量，-1表示异常 psresultinfo out varchar2-错误代码 ); -绑定变量查询 procedure pSelectToJsonBind( pcSelect varchar2,-查询sql，用绑定变量 psColumnName varchar2,-结果集的字段 pcbindclob clob,-绑定变量，"param":"value"

8、,"param":"value"，param参数名与select里的保持一致 piJsonClob out clob,-返回的json数据结果 piresult out number,-返回结果数量，-1表示异常 psresultinfo out varchar2-错误代码 );*/ procedure pCursorToJsonObject(pcselect sys_refcursor, pssubparam type_array, psclob out clob, piresult out number, psresultinfo out varch

9、ar2); procedure pCharToJsonValue(pssubvalue type_array,psssubparam type_array,psclob out clob); procedure ptest( piappid number, psappname varchar2, piPageNo in number, piPageSize in number, piRecordcount out number, psjson out clob, piresult out number,-结果集记录数，-1异常 psresultinfo out varchar2); proce

10、dure doGeneralApi(pcInput in clob, oper_user_id in number, member_id in varchar2, pcOutput in out clob);end;/CREATE OR REPLACE packAge BODY pk_tt_json AS-timesten数据库，结果集转json格式-从clob（json）获取值/* "channel_fzs":"fzs","app_id_xy":"1001","rewa_type":"

11、;PCA","reward_fee":"10.01","reward_date":"yyyy-mm-dd","idfa":"idfa"*/ -定义数组 -type type_array IS TABLE OF varchar2(100) INDEX BY PLS_INTEGER; -验证json格式的字符串 -共六种字符， -"1":"11","2":"22","1"

12、:"11","2":"22" -前三个，"，后三个" - -简单验证是否json function fisJsonObject(jsonclob clob) return number-0true，1false as inumber int; sjsonclobsub clob; iindex number(14) default 1; icount1 int default 0;- icount2 int default 0;- icount3 int default 0;- icount4 int defaul

13、t 0;- icount5 int default 0;-" iKeyBegin int; iKeyEnd int; iValueBegin int; iValueEnd int; begin -sjsonclobsub:='"hehe":"呵呵","haha":"哈哈"' -sjsonclobsub:='"hehe":"呵呵"' -去除空格 sjsonclobsub:=replace(dbms_lob.substr(jsonclob

14、,32767,1),' ','')|replace(dbms_lob.substr(jsonclob,32767,32768),' ',''); -去除换行符 sjsonclobsub:=replace(dbms_lob.substr(sjsonclobsub,32767,1),chr(10),'')|replace(dbms_lob.substr(sjsonclobsub,32767,32768),chr(10),''); dbms_output.put_line(sjsonclobsub); -

15、第一个字符和最后一个字符 if dbms_lob.substr(sjsonclobsub,1,1)='' and dbms_lob.substr(sjsonclobsub,1,dbms_lob.getlength(sjsonclobsub)='' and dbms_lob.substr(sjsonclobsub,1,2)='"' then while (iindex<=dbms_lob.getlength(sjsonclobsub) loop exit when (mod(icount5,2)=0 and icount5>0

16、); -找key:在iindexBegin与iindexEnd之间 if dbms_lob.substr(sjsonclobsub,1,iindex)='"' then icount5:=icount5+1; iKeyEnd:=iindex; iKeyBegin:=iKeyEnd; end if; iindex:=iindex+1; end loop; iValueBegin:=iKeyBegin+2; -iindex个字符是不是冒号 if dbms_lob.substr(sjsonclobsub,1,iindex)!=':' then inumber

17、:=1; return inumber; end if; iindex:=iindex+1; icount5:=0; while (iindex<=dbms_lob.getlength(sjsonclobsub) loop exit when (icount1=icount3 and icount2=icount4 and mod(icount5,2)=0 and (icount1>0 or icount2>0 or icount5>0); -在iindex开始，找value：""或 -value在iValueBegin与iValueEnd之间 if

18、 dbms_lob.substr(sjsonclobsub,1,iindex)='' then icount1:=icount1+1; elsif dbms_lob.substr(sjsonclobsub,1,iindex)='' then icount2:=icount2+1; elsif dbms_lob.substr(sjsonclobsub,1,iindex)='' then icount3:=icount3+1; elsif dbms_lob.substr(sjsonclobsub,1,iindex)='' then i

19、count4:=icount4+1; elsif dbms_lob.substr(sjsonclobsub,1,iindex)='"' then icount5:=icount5+1; else null; end if; iindex:=iindex+1; end loop; iValueEnd:=iindex-1; -dbms_output.put_line(iValueEnd); -判断iValueEnd之后有无对象 if iValueEnd=dbms_lob.getlength(sjsonclobsub)-1 then if (icount1=icount3

20、and icount2=icount4 and mod(icount5,2)=0 and (icount1>0 or icount2>0 or icount5>0) then inumber:=0; else inumber:=1; end if; else -后面还有对象 if dbms_lob.substr(sjsonclobsub,2,iValueEnd+1)=',"' then sjsonclobsub:=''|dbms_lob.substr(sjsonclobsub,dbms_lob.getlength(sjsonclobs

21、ub)-iValueEnd-1,iValueEnd+2); -'"hehe":"呵呵","haha":"哈哈"' if sjsonclobsub !='' then -迭代 inumber:=fisJsonObject(sjsonclobsub); elsif sjsonclobsub='' and (icount1=icount3 and icount2=icount4 and mod(icount5,2)=0 and (icount1>0 or icount

22、2>0 or icount5>0) then inumber:=0; else inumber:=1; end if; else -不符合规范的跳出 inumber:=1; end if; end if; else inumber:=1; end if; return inumber; end; -转 function fJsonListToArray(jsonclob clob) return clob as begin return ''|jsonclob|'' end; -构造Array函数 function jsonArray return

23、clob as begin return '' end; function jsonObject return clob as begin return '' end; -put键值对 procedure pJsonObjectPut(psjsonclob in out clob, psparam varchar2, psvalue varchar2 ) as psjsonclobsub clob; -psjsonclob: begin if psjsonclob is null then psjsonclob:=jsonObject(); end if; if

24、 psjsonclob=''/* or psjsonclob=''*/ then if dbms_lob.substr(psvalue,1,1)='' and dbms_lob.substr(psvalue,1,length(psvalue)='' then psjsonclobsub:=dbms_lob.substr(psjsonclob,1,dbms_lob.getlength(psjsonclob)-1) |'"'|psparam|'":'|psvalue|'

25、9; |dbms_lob.substr(psjsonclob,1,dbms_lob.getlength(psjsonclob); else psjsonclobsub:=dbms_lob.substr(psjsonclob,1,dbms_lob.getlength(psjsonclob)-1) |'"'|psparam|'":"'|psvalue|'"' |dbms_lob.substr(psjsonclob,1,dbms_lob.getlength(psjsonclob); end if; else if

26、 dbms_lob.substr(psvalue,1,1)='' and dbms_lob.substr(psvalue,1,length(psvalue)='' then psjsonclobsub:=dbms_lob.substr(psjsonclob,dbms_lob.getlength(psjsonclob)-1,1) |',"'|psparam|'":'|psvalue|'' |dbms_lob.substr(psjsonclob,1,dbms_lob.getlength(psjson

27、clob); else psjsonclobsub:=dbms_lob.substr(psjsonclob,dbms_lob.getlength(psjsonclob)-1,1) |',"'|psparam|'":"'|psvalue|'"' |dbms_lob.substr(psjsonclob,1,dbms_lob.getlength(psjsonclob); end if; end if; psjsonclob:=psjsonclobsub; end;procedure pJsonObjectPutC

28、lob(psjsonclob in out clob, psparam varchar2, psvalue clob ) as psjsonclobsub clob; -psjsonclob: begin if psjsonclob is null then psjsonclob:=jsonObject(); end if; if psjsonclob=''/* or psjsonclob=''*/ then if dbms_lob.substr(psvalue,1,1)='' and dbms_lob.substr(psvalue,1,leng

29、th(psvalue)='' then psjsonclobsub:=dbms_lob.substr(psjsonclob,1,dbms_lob.getlength(psjsonclob)-1) |'"'|psparam|'":'|psvalue|'' |dbms_lob.substr(psjsonclob,1,dbms_lob.getlength(psjsonclob); else psjsonclobsub:=dbms_lob.substr(psjsonclob,1,dbms_lob.getlength(p

30、sjsonclob)-1) |'"'|psparam|'":"'|psvalue|'"' |dbms_lob.substr(psjsonclob,1,dbms_lob.getlength(psjsonclob); end if; else if dbms_lob.substr(psvalue,1,1)='' and dbms_lob.substr(psvalue,1,length(psvalue)='' then psjsonclobsub:=dbms_lob.substr(

31、psjsonclob,dbms_lob.getlength(psjsonclob)-1,1) |',"'|psparam|'":'|psvalue|'' |dbms_lob.substr(psjsonclob,1,dbms_lob.getlength(psjsonclob); else psjsonclobsub:=dbms_lob.substr(psjsonclob,dbms_lob.getlength(psjsonclob)-1,1) |',"'|psparam|'":"

32、;'|psvalue|'"' |dbms_lob.substr(psjsonclob,1,dbms_lob.getlength(psjsonclob); end if; end if; psjsonclob:=psjsonclobsub; end; -添加，转成, procedure pJsonArrayPut(psjsonclob in out clob,- psvalue clob- ) as psjsonclobsub clob; svalue clob; begin if psjsonclob is null then psjsonclob:=json

33、Array(); end if; if dbms_lob.substr(psvalue,1,1)!='' and dbms_lob.substr(psvalue,1,length(psvalue)!='' then svalue:=''|psvalue|'' end if; if psjsonclob='' then psjsonclob:=dbms_lob.substr(psjsonclob,dbms_lob.getlength(psjsonclob)-1,1)|psvalue|dbms_lob.substr(p

34、sjsonclob,1,dbms_lob.getlength(psjsonclob); elsif dbms_lob.substr(psjsonclob,1,1)='' and dbms_lob.substr(psjsonclob,1,dbms_lob.getlength(psjsonclob)='' then psjsonclob:=dbms_lob.substr(psjsonclob,dbms_lob.getlength(psjsonclob)-1,1)|','|psvalue|dbms_lob.substr(psjsonclob,1,dbm

35、s_lob.getlength(psjsonclob); else psjsonclob:=dbms_lob.substr(psjsonclob,dbms_lob.getlength(psjsonclob)-1,1)|','|psvalue|''|dbms_lob.substr(psjsonclob,1,dbms_lob.getlength(psjsonclob); end if; end; -获取Array的值个数，只支持字符串格式，不支持数字类型function getArrayCount(fclobArray clob) return number as

36、cclobArray clob; iindex int default 1; schar varchar2(4); icount1 int default 0;- icount2 int default 0;- icount3 int default 0;- icount4 int default 0;- icount5 int default 0;-" icount int default 0;-数量 begin cclobArray:=trim(to_char(fclobArray); /*if dbms_lob.getlength(fclobArray) =dbms_lob.g

37、etlength(replace(fclobArray,'','') and dbms_lob.getlength(fclobArray) =dbms_lob.getlength(replace(fclobArray,'','') then*/ if dbms_lob.substr(cclobArray,1,1)='' and dbms_lob.substr(cclobArray,1,dbms_lob.getlength(cclobArray)='' then -从第二个字符到倒数第二个 iinde

38、x:=2; while iindex<dbms_lob.getlength(cclobArray) loop schar:=dbms_lob.substr(cclobArray,1,iindex); if schar='' then icount1:=icount1+1; -判断数量 if icount1=icount3 and icount2=icount4 and mod(icount5,2)=0 then icount:=icount+1; end if; elsif schar='' then icount2:=icount2+1; -判断数量 i

39、f icount1=icount3 and icount2=icount4 and mod(icount5,2)=0 then icount:=icount+1; end if; elsif schar='' then icount3:=icount3+1; -判断数量 if icount1=icount3 and icount2=icount4 and mod(icount5,2)=0 then icount:=icount+1; end if; elsif schar='' then icount4:=icount4+1; -判断数量 if icount1=

40、icount3 and icount2=icount4 and mod(icount5,2)=0 then icount:=icount+1; end if; elsif schar='"' then icount5:=icount5+1; -判断数量 if icount1=icount3 and icount2=icount4 and mod(icount5,2)=0 then icount:=icount+1; end if; else null; end if; iindex:=iindex+1; end loop; else icount:=0; end if

41、; return icount; end;-获取array的value,只支持字符串格式，不支持数字类型 function getArrayValueCom(fclobArray clob,-列表 findex number:=1) return clob as cclobArray clob; iindex int default 1; schar varchar2(4); icount1 int default 0;- icount2 int default 0;- icount3 int default 0;- icount4 int default 0;- icount5 int de

42、fault 0;-" icount int default 0;-数量 sclobArraySub clob; iIndexBegin number(14) default 0; iIndexEnd number(14) default 0; iIndexD number(14) default 0; begin cclobArray:=trim(to_char(fclobArray); /*if dbms_lob.getlength(fclobArray) =dbms_lob.getlength(replace(fclobArray,'','') a

43、nd dbms_lob.getlength(fclobArray) =dbms_lob.getlength(replace(fclobArray,'','') then*/ if findex>getArrayCount(cclobArray) then return '' end if; if dbms_lob.substr(cclobArray,1,1)='' and dbms_lob.substr(cclobArray,1,dbms_lob.getlength(cclobArray)='' then -

44、从第二个字符到倒数第二个 iindex:=2; while iindex<dbms_lob.getlength(cclobArray) loop exit when icount=findex; schar:=dbms_lob.substr(cclobArray,1,iindex); if schar='' then icount1:=icount1+1; -判断数量 if icount1=icount3 and icount2=icount4 and mod(icount5,2)=0 then icount:=icount+1; -记录value的开始位置和结束位置 i

45、IndexBegin:=iIndexEnd; iIndexEnd:=iindex; end if; elsif schar='' then icount2:=icount2+1; -判断数量 if icount1=icount3 and icount2=icount4 and mod(icount5,2)=0 then icount:=icount+1; -记录value的开始位置和结束位置 iIndexBegin:=iIndexEnd; iIndexEnd:=iindex; end if; elsif schar='' then icount3:=icount

46、3+1; -判断数量 if icount1=icount3 and icount2=icount4 and mod(icount5,2)=0 then icount:=icount+1; -记录value的开始位置和结束位置 iIndexBegin:=iIndexEnd; iIndexEnd:=iindex; end if; elsif schar='' then icount4:=icount4+1; -判断数量 if icount1=icount3 and icount2=icount4 and mod(icount5,2)=0 then icount:=icount+1;

47、 -记录value的开始位置和结束位置 iIndexBegin:=iIndexEnd; iIndexEnd:=iindex; end if; elsif schar='"' then icount5:=icount5+1; -判断数量 if icount1=icount3 and icount2=icount4 and mod(icount5,2)=0 then icount:=icount+1; -记录value的开始位置和结束位置 iIndexBegin:=iIndexEnd; iIndexEnd:=iindex; end if; else null; end i

48、f; iindex:=iindex+1; end loop; -dbms_output.put_line(iIndexBegin); -dbms_output.put_line(iIndexEnd); if icount1=icount3 and icount2=icount4 and mod(icount5,2)=0 then if schar='"' then null; if findex=1 then sclobArraySub:=replace(trim(dbms_lob.substr(cclobArray,iIndexEnd-iIndexD-1,iInde

49、xD+2),'"',''); else -从iIndexBegin后的第一个','逗号开始取值； iIndexD:=iIndexBegin-1+dbms_lob.instr(dbms_lob.substr(cclobArray,dbms_lob.getlength(cclobArray)-iIndexBegin+1,iIndexBegin),',',1,1); sclobArraySub:=replace(trim(dbms_lob.substr(cclobArray,iIndexEnd-iIndexD,iIndexD+1),'"',''); end if; else if findex=1 then sclobArraySub:=trim(dbms_lob