咨询使用Oracle分解表中一个字段的的数据,取部分数据(数据是json格式的)

Question

本帖最后由 wing929 于 2011-02-11 09:19:16 编辑

Answer 1

  1  declare
  2   s varchar2(30000):='[
  3  {
  4  "accessid":7360006695042,
  5  "cable":1,
  6  "cablecode":"DFQWE",
  7  "cableid":1360010028246,
  8  "cablename":"ASDSADQWXCWE",
  9  "direction":1,
 10  "faccode":"ASDSA/J009/DP1103",
 11  "facid":1370010122652,
 12  "facname":"GJ05010/DP1103",
 13  "factype":2,
 14  "lineorder":35,
 15  "portcode":"5",
 16  "portid":1970022561001,
 17  "portorder":5,
 18  "pxmode":1,
 19  "routeid":36387281,
 20  "routeorder":1
 21  },
 22  {
 23  "accessid":7360006695042,
 24  "cable":1,
 25  "cablecode":"G0ASDSA/J009/PX11",
 26  "cableid":1360010028246,
 27  "cablename":"WQDWQEQE",
 28  "direction":2,
 29  "faccode":"G01HJIAN/J009",
 30  "facid":1370000001900,
 31  "facname":"GJ05010",
 32  "factype":1,
 33  "lineorder":35,
 34  "portcode":"000-52-015",
 35  "portid":2000008909010,
 36  "portorder":1290,
 37  "pxmode":1,
 38  "routeid":36387282,
 39  "routeorder":2
 40  },
 41  {
 42  "accessid":7360006695042,
 43  "cable":0,
 44  "cablecode":"G01HJIAN/ZG05",
 45  "cableid":1360000012334,
 46  "cablename":"asdasdsaxcZG05",
 47  "direction":1,
 48  "faccode":"ASDASDAS",
 49  "facid":1370000001900,
 50  "facname":"GJ05010",
 51  "factype":1,
 52  "lineorder":164,
 53  "portcode":"23-014",
 54  "portid":2000002268654,
 55  "portorder":564,
 56  "pxmode":1,
 57  "routeid":36387283,
 58  "routeorder":3
 59  },
 60  {
 61  "accessid":7360006695042,
 62  "cable":0,
 63  "cablecode":"sadasdN/ZG05",
 64  "cableid":1360000012334,
 65  "cablename":"asdasd",
 66  "direction":2,
 67  "faccode":"asdsad",
 68  "facid":1370000000219,
 69  "facname":"sard",
 70  "factype":0,
 71  "lineorder":164,
 72  "portcode":"004-05-064",
 73  "portid":2020000923209,
 74  "portorder":464,
 75  "pxmode":1,
 76  "routeid":36387284,
 77  "routeorder":4
 78  }
 79  ]';
 80   n pls_integer:=1;
 81   cablecode varchar2(30);
 82   faccode varchar2(30);
 83  begin
 84   while n<=length(regexp_replace(s,'[^}]','')) loop
 85    cablecode:=regexp_substr(s,'"cablecode":(".*")',1,n,'i',1);
 86    faccode:=regexp_substr(s,'"faccode":(".*")',1,n,'i',1);
 87    dbms_output.put_line('cablecode:'||cablecode||' '||'faccode:'||faccode);
 88    n:=n+1;
 89   end loop;
 90* end;
SQL> /
cablecode:"DFQWE" faccode:"ASDSA/J009/DP1103"
cablecode:"G0ASDSA/J009/PX11" faccode:"G01HJIAN/J009"
cablecode:"G01HJIAN/ZG05" faccode:"ASDASDAS"
cablecode:"sadasdN/ZG05" faccode:"asdsad"PL/SQL procedure successfully completed.
需要什么形式的函数可以自己改写一下