#sql #json #oracle #plsql #oracle12c
#sql #json #Oracle #plsql #oracle12c
Вопрос:
У меня есть массив JSON внутри varchar
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, я получу
[{«имя»: «Honda», «цвет»: «красный»}, {«имя»: «Toyota», «цвет»: «зеленый»}]
Но как я могу перебрать этот массив Cars и распечатать его компоненты отдельно (получить к ним доступ)?
Комментарии:
1. Для извлечения нужной части не требуется PL / SQL, достаточно использовать SQL.
Ответ №1:
Вы можете напрямую использовать SQL JSON_TABLE() функцию with, которая доступна, начиная с 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,
'
Demo
Если вам действительно нужно использовать 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,
''
а затем вызовите из консоли разработчика SQL как
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"}] }',
'
Demo2
Комментарии:
1. Можете ли вы запустить свой код с JSON, объявленным в переменной, как в моем примере? Когда я пытаюсь это сделать, я получаю ORA-00900: недопустимый оператор SQL, это очень важно для меня. Весь JSON должен находиться внутри переменной varchar
2. Я добавил пример для PL / SQL @David
3. Я думаю, что это немного сложно для меня, есть ли какой-нибудь способ сделать немного проще? Без функций sql, прямо внутри plsql, например, здесь вы получаете массив arrayCars := JSON_QUERY(JSONArray, '$.Cars'); Затем мне нужно просто перебрать этот массив и использовать эти переменные для чего-то. Допустим, у нас есть некоторый testVar varchar и просто добавляем к нему все переменные внутри цикла. testVarchar:= testVarchar Honda; затем testVarchar:= testVarchar красный; затем testVarchar:=testVarchar Toyota; затем testVarchar:= testVarchar зеленый;
4. Я имею в виду, что мне нужно какое-то действие цикла, где я могу что-то сделать и использовать переменные hondas, затем что-то сделать и использовать переменные toyota.
COLUMNS(NESTED PATH '$[*]'
COLUMNS(
name VARCHAR2(100) PATH '$.name',
color VARCHAR2(100) PATH '$.color'
)
)
);
Demo
Если вам действительно нужно использовать PL/SQL , рассмотрите возможность создания функции с SYS_REFCURSOR возвращаемым типом, таким как
а затем вызовите из консоли разработчика SQL как
Редактировать (для вашего последнего комментария):
В качестве альтернативы вы можете использовать простой неявный цикл, такой как
Комментарии:
1. Можете ли вы запустить свой код с JSON, объявленным в переменной, как в моем примере? Когда я пытаюсь это сделать, я получаю ORA-00900: недопустимый оператор SQL, это очень важно для меня. Весь JSON должен находиться внутри переменной varchar
2. Я добавил пример для PL / SQL @David
3. Я думаю, что это немного сложно для меня, есть ли какой-нибудь способ сделать немного проще? Без функций sql, прямо внутри plsql, например, здесь вы получаете массив arrayCars := JSON_QUERY(JSONArray, '$.Cars'); Затем мне нужно просто перебрать этот массив и использовать эти переменные для чего-то. Допустим, у нас есть некоторый testVar varchar и просто добавляем к нему все переменные внутри цикла. testVarchar:= testVarchar Honda; затем testVarchar:= testVarchar красный; затем testVarchar:=testVarchar Toyota; затем testVarchar:= testVarchar зеленый;
4. Я имею в виду, что мне нужно какое-то действие цикла, где я могу что-то сделать и использовать переменные hondas, затем что-то сделать и использовать переменные toyota.
' 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 как
Редактировать (для вашего последнего комментария):
В качестве альтернативы вы можете использовать простой неявный цикл, такой как
Комментарии:
1. Можете ли вы запустить свой код с JSON, объявленным в переменной, как в моем примере? Когда я пытаюсь это сделать, я получаю ORA-00900: недопустимый оператор SQL, это очень важно для меня. Весь JSON должен находиться внутри переменной varchar
2. Я добавил пример для PL / SQL @David
3. Я думаю, что это немного сложно для меня, есть ли какой-нибудь способ сделать немного проще? Без функций sql, прямо внутри plsql, например, здесь вы получаете массив arrayCars := JSON_QUERY(JSONArray, '$.Cars'); Затем мне нужно просто перебрать этот массив и использовать эти переменные для чего-то. Допустим, у нас есть некоторый testVar varchar и просто добавляем к нему все переменные внутри цикла. testVarchar:= testVarchar Honda; затем testVarchar:= testVarchar красный; затем testVarchar:=testVarchar Toyota; затем testVarchar:= testVarchar зеленый;
4. Я имею в виду, что мне нужно какое-то действие цикла, где я могу что-то сделать и использовать переменные hondas, затем что-то сделать и использовать переменные toyota.
COLUMNS(NESTED PATH '$[*]'
COLUMNS(
name VARCHAR2(100) PATH '$.name',
color VARCHAR2(100) PATH '$.color'
)
)
);
Если вам действительно нужно использовать PL/SQL , рассмотрите возможность создания функции с SYS_REFCURSOR возвращаемым типом, таким как
а затем вызовите из консоли разработчика SQL как
Редактировать (для вашего последнего комментария):
В качестве альтернативы вы можете использовать простой неявный цикл, такой как
Комментарии:
1. Можете ли вы запустить свой код с JSON, объявленным в переменной, как в моем примере? Когда я пытаюсь это сделать, я получаю ORA-00900: недопустимый оператор SQL, это очень важно для меня. Весь JSON должен находиться внутри переменной varchar
2. Я добавил пример для PL / SQL @David
3. Я думаю, что это немного сложно для меня, есть ли какой-нибудь способ сделать немного проще? Без функций sql, прямо внутри plsql, например, здесь вы получаете массив arrayCars := JSON_QUERY(JSONArray, ‘$.Cars’); Затем мне нужно просто перебрать этот массив и использовать эти переменные для чего-то. Допустим, у нас есть некоторый testVar varchar и просто добавляем к нему все переменные внутри цикла. testVarchar:= testVarchar Honda; затем testVarchar:= testVarchar красный; затем testVarchar:=testVarchar Toyota; затем testVarchar:= testVarchar зеленый;
4. Я имею в виду, что мне нужно какое-то действие цикла, где я могу что-то сделать и использовать переменные hondas, затем что-то сделать и использовать переменные toyota.
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;
/Demo2
Комментарии:
1. Можете ли вы запустить свой код с JSON, объявленным в переменной, как в моем примере? Когда я пытаюсь это сделать, я получаю ORA-00900: недопустимый оператор SQL, это очень важно для меня. Весь JSON должен находиться внутри переменной varchar
2. Я добавил пример для PL / SQL @David
3. Я думаю, что это немного сложно для меня, есть ли какой-нибудь способ сделать немного проще? Без функций sql, прямо внутри plsql, например, здесь вы получаете массив arrayCars := JSON_QUERY(JSONArray, ‘$.Cars’); Затем мне нужно просто перебрать этот массив и использовать эти переменные для чего-то. Допустим, у нас есть некоторый testVar varchar и просто добавляем к нему все переменные внутри цикла. testVarchar:= testVarchar Honda; затем testVarchar:= testVarchar красный; затем testVarchar:=testVarchar Toyota; затем testVarchar:= testVarchar зеленый;
4. Я имею в виду, что мне нужно какое-то действие цикла, где я могу что-то сделать и использовать переменные hondas, затем что-то сделать и использовать переменные toyota.
COLUMNS(NESTED PATH ‘$[*]’
COLUMNS(
name VARCHAR2(100) PATH ‘$.name’,
color VARCHAR2(100) PATH ‘$.color’
)
)
);Demo
Если вам действительно нужно использовать PL/SQL , рассмотрите возможность создания функции с SYS_REFCURSOR возвращаемым типом, таким как
а затем вызовите из консоли разработчика SQL как
Редактировать (для вашего последнего комментария):
В качестве альтернативы вы можете использовать простой неявный цикл, такой как
Комментарии:
1. Можете ли вы запустить свой код с JSON, объявленным в переменной, как в моем примере? Когда я пытаюсь это сделать, я получаю ORA-00900: недопустимый оператор SQL, это очень важно для меня. Весь JSON должен находиться внутри переменной varchar
2. Я добавил пример для PL / SQL @David
3. Я думаю, что это немного сложно для меня, есть ли какой-нибудь способ сделать немного проще? Без функций sql, прямо внутри plsql, например, здесь вы получаете массив arrayCars := JSON_QUERY(JSONArray, ‘$.Cars’); Затем мне нужно просто перебрать этот массив и использовать эти переменные для чего-то. Допустим, у нас есть некоторый testVar varchar и просто добавляем к нему все переменные внутри цикла. testVarchar:= testVarchar Honda; затем testVarchar:= testVarchar красный; затем testVarchar:=testVarchar Toyota; затем testVarchar:= testVarchar зеленый;
4. Я имею в виду, что мне нужно какое-то действие цикла, где я могу что-то сделать и использовать переменные hondas, затем что-то сделать и использовать переменные toyota.
‘ 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 как
Редактировать (для вашего последнего комментария):
В качестве альтернативы вы можете использовать простой неявный цикл, такой как
Комментарии:
1. Можете ли вы запустить свой код с JSON, объявленным в переменной, как в моем примере? Когда я пытаюсь это сделать, я получаю ORA-00900: недопустимый оператор SQL, это очень важно для меня. Весь JSON должен находиться внутри переменной varchar
2. Я добавил пример для PL / SQL @David
3. Я думаю, что это немного сложно для меня, есть ли какой-нибудь способ сделать немного проще? Без функций sql, прямо внутри plsql, например, здесь вы получаете массив arrayCars := JSON_QUERY(JSONArray, ‘$.Cars’); Затем мне нужно просто перебрать этот массив и использовать эти переменные для чего-то. Допустим, у нас есть некоторый testVar varchar и просто добавляем к нему все переменные внутри цикла. testVarchar:= testVarchar Honda; затем testVarchar:= testVarchar красный; затем testVarchar:=testVarchar Toyota; затем testVarchar:= testVarchar зеленый;
4. Я имею в виду, что мне нужно какое-то действие цикла, где я могу что-то сделать и использовать переменные hondas, затем что-то сделать и использовать переменные toyota.
COLUMNS(NESTED PATH ‘$[*]’
COLUMNS(
name VARCHAR2(100) PATH ‘$.name’,
color VARCHAR2(100) PATH ‘$.color’
)
)
);
Если вам действительно нужно использовать PL/SQL , рассмотрите возможность создания функции с SYS_REFCURSOR возвращаемым типом, таким как
а затем вызовите из консоли разработчика SQL как
Редактировать (для вашего последнего комментария):
В качестве альтернативы вы можете использовать простой неявный цикл, такой как
Комментарии:
1. Можете ли вы запустить свой код с JSON, объявленным в переменной, как в моем примере? Когда я пытаюсь это сделать, я получаю ORA-00900: недопустимый оператор SQL, это очень важно для меня. Весь JSON должен находиться внутри переменной varchar
2. Я добавил пример для PL / SQL @David
3. Я думаю, что это немного сложно для меня, есть ли какой-нибудь способ сделать немного проще? Без функций sql, прямо внутри plsql, например, здесь вы получаете массив arrayCars := JSON_QUERY(JSONArray, ‘$.Cars’); Затем мне нужно просто перебрать этот массив и использовать эти переменные для чего-то. Допустим, у нас есть некоторый testVar varchar и просто добавляем к нему все переменные внутри цикла. testVarchar:= testVarchar Honda; затем testVarchar:= testVarchar красный; затем testVarchar:=testVarchar Toyota; затем testVarchar:= testVarchar зеленый;
4. Я имею в виду, что мне нужно какое-то действие цикла, где я могу что-то сделать и использовать переменные hondas, затем что-то сделать и использовать переменные toyota.