您可以直接使用
JSON_TABLE()
函数与
Oracle DB 12.1.0.2
版本一起使用
SQL
,如下所示。
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;
/
示例2