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 type_array IS TABLE OF varchar2(256) INDEX BY PLS_INTEGER;function fisJsonObject(jsonclob clob) return number;-转function fJsonListToArray(jsonclob

3、clob) return clob; -构造Array函数function jsonArray return clob; function jsonObject return clob;procedure pJsonObjectPut(psjsonclob in out clob, psparam varchar2, psvalue varchar2 ); procedure pJsonObjectPutClob(psjsonclob in out clob, psparam varchar2, psvalue clob );procedure pJsonArrayPut(psjsonclob

4、 in out clob, psvalue clob );function getArrayCount(fclobArray clob) return number; function getArrayValueCom(fclobArray clob,-列表 findex number:=1) return clob; FUNCTION getJsonValueCom(pjclob clob, param varchar2 )return varchar2;/* -返回指定参数的值FUNCTION get(jclob clob, param varchar2 ) return varchar2

5、; -返回第i个参数的参数值FUNCTION getparam(jclob clob, i number ) return varchar2;-返回第i个参数的域值FUNCTION getvalue(jclob clob, i number ) return varchar2; -返回json值个数 FUNCTION getcount(jclob clob ) return number; -将结果集转成json格式 function fSelectToJson(pcSelect varchar2,psColumnName varchar2) return clob; -绑定变量查询，返回js

6、on function fSelectToJsonBind(pcSelect varchar2,psColumnName varchar2,pcbindclob clob) return clob; -直接查询，返回json procedure pSelectToJson( pcSelect varchar2,-查询sql，用绑定变量 psColumnName varchar2,-结果集的字段 piJsonClob out clob,-返回的json数据结果 piresult out number,-返回结果数量，-1表示异常 psresultinfo out varchar2-错误代码 );

7、 -绑定变量查询 procedure pSelectToJsonBind( pcSelect varchar2,-查询sql，用绑定变量 psColumnName varchar2,-结果集的字段 pcbindclob clob,-绑定变量，param:value,param:value，param参数名与select里的保持一致 piJsonClob out clob,-返回的json数据结果 piresult out number,-返回结果数量，-1表示异常 psresultinfo out varchar2-错误代码 );*/ procedure pCursorToJsonObject

8、(pcselect sys_refcursor, pssubparam type_array, psclob out clob, piresult out number, psresultinfo out varchar2); procedure pCharToJsonValue(pssubvalue type_array,psssubparam type_array,psclob out clob); procedure ptest( piappid number, psappname varchar2, piPageNo in number, piPageSize in number, p

9、iRecordcount out number, psjson out clob, piresult out number,-结果集记录数，-1异常 psresultinfo out varchar2); procedure 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）获取值/

10、* channel_fzs:fzs,app_id_xy:1001,rewa_type: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:11,2:22 -前三个，后三个 - -简单验证是否json function fisJsonObject(jsonclob clob) return number-0true，1false

11、 as inumber int; sjsonclobsub clob; iindex number(14) default 1; icount1 int default 0;- icount2 int default 0;- icount3 int default 0;- icount4 int default 0;- icount5 int default 0;- iKeyBegin int; iKeyEnd int; iValueBegin int; iValueEnd int; begin -sjsonclobsub:=hehe:呵呵,haha:哈哈; -sjsonclobsub:=he

12、he:呵呵; -去除空格 sjsonclobsub:=replace(dbms_lob.substr(jsonclob,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); -第

13、一个字符和最后一个字符 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 (iindex0); -找key:在iindexBegin与iindexEnd之间 if dbms_lob.substr(sjsonclobsub,1,iindex)= then icount5:=icount5+1; iKeyEnd:=iindex; iKey

14、Begin:=iKeyEnd; end if; iindex:=iindex+1; end loop; iValueBegin:=iKeyBegin+2; -iindex个字符是不是冒号 if dbms_lob.substr(sjsonclobsub,1,iindex)!=: then inumber:=1; return inumber; end if; iindex:=iindex+1; icount5:=0; while (iindex0 or icount20 or icount50); -在iindex开始，找value：或 -value在iValueBegin与iValueEnd之

15、间 if 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 icount4:=icount4+1; elsif dbms_lob.su

16、bstr(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 and icount2=icount4 and mod(icount5,2)=0 and (icount

17、10 or icount20 or icount50) 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(sjsonclobsub)-iValueEnd-1,iValueEnd+2); -hehe:呵呵,haha:哈哈 if sjsonclobsub != then -迭代 inumber:=fisJso

18、nObject(sjsonclobsub); elsif sjsonclobsub= and (icount1=icount3 and icount2=icount4 and mod(icount5,2)=0 and (icount10 or icount20 or icount50) then inumber:=0; else inumber:=1; end if; else -不符合规范的跳出 inumber:=1; end if; end if; else inumber:=1; end if; return inumber; end; -转 function fJsonListToAr

19、ray(jsonclob clob) return clob as begin return |jsonclob|; end; -构造Array函数 function jsonArray return 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;

20、 -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,length(psvalue)= then psjsonclobsub:=dbms_lob.substr(psjsonclob,1,dbms_lob.getlength(psjsonclob)-1) |psparam|:|psvalu

21、e| |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 dbms_lob.substr(psvalue,1,1)= and dbms_lob.substr(psva

22、lue,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(psjsonclob); else psjsonclobsub:=dbms_lob.substr(psjsonclob,dbms_lob.getlength(psjsonclob)-1,1) |,|psparam|:|psvalue| |dbms

23、_lob.substr(psjsonclob,1,dbms_lob.getlength(psjsonclob); end if; end if; psjsonclob:=psjsonclobsub; end;procedure pJsonObjectPutClob(psjsonclob in out clob, psparam varchar2, psvalue clob ) as psjsonclobsub clob; -psjsonclob: begin if psjsonclob is null then psjsonclob:=jsonObject(); end if; if psjs

24、onclob=/* 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| |dbms_lob.substr(psjsonclob,1,dbms_lob.getlength(psjsonclob); else psjsonclobsub:=dbm

25、s_lob.substr(psjsonclob,1,dbms_lob.getlength(psjsonclob)-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(psjsonclob,dbms_lob.getlength

26、(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|:|psvalue| |dbms_lob.substr(psjsonclob,1,dbms_lob.getlength(psjsonclob); end if; end if; psjsonclob:=psj

27、sonclobsub; end; -添加，转成, procedure pJsonArrayPut(psjsonclob in out clob,- psvalue clob- ) as psjsonclobsub clob; svalue clob; begin if psjsonclob is null then psjsonclob:=jsonArray(); end if; if dbms_lob.substr(psvalue,1,1)!= and dbms_lob.substr(psvalue,1,length(psvalue)!= then svalue:=|psvalue|; en

28、d if; if psjsonclob= then psjsonclob:=dbms_lob.substr(psjsonclob,dbms_lob.getlength(psjsonclob)-1,1)|psvalue|dbms_lob.substr(psjsonclob,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.

29、substr(psjsonclob,dbms_lob.getlength(psjsonclob)-1,1)|,|psvalue|dbms_lob.substr(psjsonclob,1,dbms_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的值个

30、数，只支持字符串格式，不支持数字类型function getArrayCount(fclobArray clob) return number 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 default 0;- icount int default 0;-数量 begin cclobArray:=trim

31、(to_char(fclobArray); /*if dbms_lob.getlength(fclobArray) =dbms_lob.getlength(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 -从第二个字符到倒数第二

32、个 iindex:=2; while iindexgetArrayCount(cclobArray) then return ; end if; if dbms_lob.substr(cclobArray,1,1)= and dbms_lob.substr(cclobArray,1,dbms_lob.getlength(cclobArray)= then -从第二个字符到倒数第二个 iindex:=2; while iindexdbms_lob.getlength(cclobArray) loop exit when icount=findex; schar:=dbms_lob.substr(

33、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的开始位置和结束位置 iIndexBegin:=iIndexEnd; iIndexEnd:=iindex; end if; elsif schar= then icount2:=icount2+1; -判断数量 if icount1=icount3 and icount2=icount4 a

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

35、; end if; elsif schar= then icount4:=icount4+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 icount5:=icount5+1; -判断数量 if icount1=icount3 and icount2=icount4 and mod(ic

36、ount5,2)=0 then icount:=icount+1; -记录value的开始位置和结束位置 iIndexBegin:=iIndexEnd; iIndexEnd:=iindex; end if; else null; end if; 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

37、 null; if findex=1 then sclobArraySub:=replace(trim(dbms_lob.substr(cclobArray,iIndexEnd-iIndexD-1,iIndexD+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.substr(cclobArray,iIndexEnd-iIndexD-1,iIndexD+2); -dbms_output.put_line(iIndexBegin); -dbms_output.put_line(iIndexEnd);