如何使用plsql从列表中提取数据
如何使用plsql从列表中提取数据
提问于 2020-06-16 11:29:38
SELECT JSON_QUERY(A.RESPONSEXML,
'$.banking_info.ccris_banking_details.outstanding_credit.items[*].subAccount.subAccountItem.subAccountItem1.credit_position.items'
WITH ARRAY WRAPPER)
FROM RAMCI_RESPONSE A
WHERE A.APPID = '5004505'
AND ROWNUM = 1;
DBMS_OUTPUT.PUT_LINE(ITEMSDATA);结束；
这是我从上述查询中提取数据后得到的字符串。
[["","","2","1","1","1","1","0","0","0","0","0"],["","","0","0","0","1","2","3","2","0","0","0"],["","","0","0","1","2","2","1","1","1","2","0"],["","","0","0","0","1","2","2","2","1","0","0"]]我们怎样才能一排排地得到数据呢？
回答 1
Stack Overflow用户
回答已采纳
发布于 2020-06-16 11:42:48
您可以按以下方式使用层次结构查询：
SQL> with dataa (d) as
2 (select '[["","","2","1","1","1","1","0","0","0","0","0"],["","","0","0","0","1","2","3","2","0","0","0"],["","","0","0","1","2","2","1","1","1","2","0"],["","","0","0","0","1","2","2","2","1","0","0"]]' from dual)
3 -- query starts from here
4 select '[' || ltrim(ltrim(regexp_substr(d,'[^]]+',1,level) || ']',','),'[') as str
5 from dataa
6 connect by level <= regexp_count(d,'\[') - 1;
STR
--------------------------------------------------------------------------------
["","","2","1","1","1","1","0","0","0","0","0"]
["","","0","0","0","1","2","3","2","0","0","0"]
["","","0","0","1","2","2","1","1","1","2","0"]
["","","0","0","0","1","2","2","2","1","0","0"]
SQL>--更新：
SQL> with dataa (d) as
2 (select '[["","","2","1","1","1","1","0","0","0","0","0"],["","","0","0","0","1","2","3","2","0","0","0"],["","","0","0","1","2","2","1","1","1","2","0"],["","","0","0","0","1","2","2","2","1","0","0"]]' from dual)
3 -- query starts from
4 SELECT COALESCE(SUM(CASE WHEN CNTR > 1 THEN 1 END),0) AS CNTR
5 FROM
6 (SELECT T.STR, REGEXP_SUBSTR(T.STR, '[0-9]+',1,COLUMN_VALUE) AS CNTR FROM
7 (select LTRIM(regexp_substr(d,'[^]]+',1,level),',') as str
8 from dataa
9 connect by level <= regexp_count(d,'\[') - 1) T
10 CROSS JOIN
11 table(cast(multiset(select level from dual
12 connect by level <= length (regexp_replace(t.STR, '[^,]+')) + 1)
13 as sys.OdciNumberList)) levels);
CNTR
----------
10
SQL>页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/62407409
复制相关文章
相似问题
腾讯云开发者
