Timesten內(nèi)存數(shù)據(jù)庫支持json格式的包

1、近期公司用到了Timesten內(nèi)存數(shù)據(jù)庫，但是Timesten暫不支持json，所以自己寫了個包，用戶數(shù)據(jù)庫與json的數(shù)據(jù)轉(zhuǎn)換，沒什么高深的，但是要在Timesten里寫也需要時間，所以分享下。 主要包括幾個方面的： 1. 根據(jù)key獲取json的值； 2. 根據(jù)index獲取Array的值； 3. 根據(jù)sys_cursor等生成json數(shù)據(jù)； 4. 驗證是否json格式等備注：數(shù)據(jù)格式只支持字符串的形式，不支持數(shù)字，不然數(shù)據(jù)會有誤。 下面是plsql里寫的包，弄了一個星期，測試通過，可以放心使用。有問題可以找qq：313892960。CREATE OR REPLACE packAge PK

2、_TT_JSON AS-timesten數(shù)據(jù)庫，結(jié)果集轉(zhuǎn)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;-轉(zhuǎn)function fJsonListToArray(jsonclob clob) return clob; -構(gòu)造Array函數(shù)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;/* -返回指定參數(shù)的值FUNCTION get(jclob clob, param varchar2 ) return varchar2; -返回第i個參數(shù)的參數(shù)值FUNCTION getparam(jclob clob, i number ) return varchar2;-返回第i個參數(shù)的域值FUNCTION getvalue(jclob clob, i number ) return varchar2; -返回json值個數(shù) FUNCT

6、ION getcount(jclob clob ) return number; -將結(jié)果集轉(zhuǎn)成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,-結(jié)果集的字段 piJsonClob out clob,-返回的json數(shù)據(jù)結(jié)果 piresult out number,-返回結(jié)果數(shù)量，-1表示異常 psresultinfo out varchar2-錯誤代碼 ); -綁定變量查詢 procedure pSelectToJsonBind( pcSelect varchar2,-查詢sql，用綁定變量 psColumnName varchar2,-結(jié)果集的字段 pcbindclob clob,-綁定變量，"param":"value"

8、,"param":"value"，param參數(shù)名與select里的保持一致 piJsonClob out clob,-返回的json數(shù)據(jù)結(jié)果 piresult out number,-返回結(jié)果數(shù)量，-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,-結(jié)果集記錄數(shù)，-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數(shù)據(jù)庫，結(jié)果集轉(zhuǎn)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"*/ -定義數(shù)組 -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 -不符合規(guī)范的跳出 inumber:=1; end if; end if; else inumber:=1; end if; return inumber; end; -轉(zhuǎn) function fJsonListToArray(jsonclob clob) return clob as begin return ''|jsonclob|'' end; -構(gòu)造Array函數(shù) 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; -添加，轉(zhuǎn)成, 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的值個數(shù)，只支持字符串格式，不支持數(shù)字類型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;-數(shù)量 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 -從第二個字符到倒數(shù)第二個 iinde

38、x:=2; while iindex<dbms_lob.getlength(cclobArray) loop schar:=dbms_lob.substr(cclobArray,1,iindex); if schar='' then icount1:=icount1+1; -判斷數(shù)量 if icount1=icount3 and icount2=icount4 and mod(icount5,2)=0 then icount:=icount+1; end if; elsif schar='' then icount2:=icount2+1; -判斷數(shù)量 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; -判斷數(shù)量 if icount1=icount3 and icount2=icount4 and mod(icount5,2)=0 then icount:=icount+1; end if; elsif schar='' then icount4:=icount4+1; -判斷數(shù)量 if icount1=

40、icount3 and icount2=icount4 and mod(icount5,2)=0 then icount:=icount+1; end if; elsif schar='"' then icount5:=icount5+1; -判斷數(shù)量 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,只支持字符串格式，不支持數(shù)字類型 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;-數(shù)量 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、從第二個字符到倒數(shù)第二個 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; -判斷數(shù)量 if icount1=icount3 and icount2=icount4 and mod(icount5,2)=0 then icount:=icount+1; -記錄value的開始位置和結(jié)束位置 i

45、IndexBegin:=iIndexEnd; iIndexEnd:=iindex; end if; elsif schar='' then icount2:=icount2+1; -判斷數(shù)量 if icount1=icount3 and icount2=icount4 and mod(icount5,2)=0 then icount:=icount+1; -記錄value的開始位置和結(jié)束位置 iIndexBegin:=iIndexEnd; iIndexEnd:=iindex; end if; elsif schar='' then icount3:=icount

46、3+1; -判斷數(shù)量 if icount1=icount3 and icount2=icount4 and mod(icount5,2)=0 then icount:=icount+1; -記錄value的開始位置和結(jié)束位置 iIndexBegin:=iIndexEnd; iIndexEnd:=iindex; end if; elsif schar='' then icount4:=icount4+1; -判斷數(shù)量 if icount1=icount3 and icount2=icount4 and mod(icount5,2)=0 then icount:=icount+1;

47、 -記錄value的開始位置和結(jié)束位置 iIndexBegin:=iIndexEnd; iIndexEnd:=iindex; end if; elsif schar='"' then icount5:=icount5+1; -判斷數(shù)量 if icount1=icount3 and icount2=icount4 and mod(icount5,2)=0 then icount:=icount+1; -記錄value的開始位置和結(jié)束位置 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