我在varchar中有JSON数组
DECLARE
JsonArray varchar2(1000);
arrayCars varchar2(1000);
BEGIN
JsonArray :={"Cars": [{"name":"Honda", "color":"red" },
{"name":"Toyota", "color":"green"}] }
SELECT JSON_QUERY(JsonArray, '$.Cars') into arrayCars FROM dual;
END;
/
现在,如果我打印出arrayCars我会得到
[{“ name”:“ Honda”,“ color”:“ red”},{“ name”:“ Toyota”,“ color”:“ green”}]
但是,如何循环遍历此Cars数组并单独打印出其组件(访问它们)?
您可以直接使用SQL
与JSON_TABLE()
功能,开始提供Oracle DB 12.1.0.2
的版本,如
WITH t(arrayCars) AS
(
SELECT JSON_QUERY('{"Cars": [{"name":"Honda", "color":"red" },
{"name":"Toyota", "color":"green"}] }', '$.Cars')
FROM dual
)
SELECT name, color
FROM t
CROSS JOIN JSON_TABLE(arrayCars,
'$' COLUMNS(NESTED PATH '$[*]'
COLUMNS(
name VARCHAR2(100) PATH '$.name',
color VARCHAR2(100) PATH '$.color'
)
)
);
如果您确实需要使用PL/SQL
,请考虑使用SYS_REFCURSOR
返回类型创建函数,例如
CREATE OR REPLACE FUNCTION Get_Cars RETURN SYS_REFCURSOR IS
v_recordset SYS_REFCURSOR;
JsonArray VARCHAR2(1000);
arrayCars VARCHAR2(1000);
v_sql VARCHAR2(32767);
BEGIN
JsonArray :='{"Cars": [{"name":"Honda", "color":"red" },
{"name":"Toyota", "color":"green"}] }';
arrayCars := JSON_QUERY(JsonArray, '$.Cars');
DBMS_OUTPUT.PUT_LINE(arrayCars);
v_sql :=
'SELECT name,color
FROM dual
CROSS JOIN JSON_TABLE(:Cars,
''$'' COLUMNS(NESTED PATH ''$[*]''
COLUMNS(
name VARCHAR2(100) PATH ''$.name'',
color VARCHAR2(100) PATH ''$.color''
)
)
)';
OPEN v_recordset FOR v_sql USING arrayCars;
RETURN v_recordset;
END;
/
然后从SQL Developer的控制台调用为
SQL> DECLARE
result SYS_REFCURSOR;
BEGIN
:result := Get_Cars;
END;
/
SQL> PRINT result ;
编辑(您的最后评论):
另外,您可以使用简单的隐式循环,例如
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
v_name VARCHAR2(1000);
v_color VARCHAR2(1000);
BEGIN
FOR c IN (
SELECT name,color
FROM JSON_TABLE('{"Cars": [{"name":"Honda", "color":"red" },
{"name":"Toyota", "color":"green"}] }',
'$' COLUMNS(NESTED PATH '$.Cars[*]'
COLUMNS(
name VARCHAR2(100) PATH '$.name',
color VARCHAR2(100) PATH '$.color'
)
)
)
)
LOOP
v_name := c.name;
v_color := c.color;
DBMS_OUTPUT.PUT_LINE(v_name||' '||v_color);
END LOOP;
END;
/
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句