create or replace type poid_t as object (poid number);
create or replace type poid_tbl as TABLE OF poid_t;
create or replace type poodgrpid_t as object (poodgrpid number);
create or replace type poodgrpid_tbl as TABLE OF poodgrpid_t;
create or replace type csid_t as object (csid number);
create or replace type csid_tbl as TABLE OF csid_t;
JSON 구조 l_values를 l_poids 배열에 입력 후 select * from table(l_poids)로 읽어들임
declare
l_values clob;
l_poids poid_tbl := poid_tbl();
l_poodgrpids poodgrpid_tbl := poodgrpid_tbl();
l_csids csid_tbl := csid_tbl();
begin
l_values := '{"rows":[{"PO_ID":"117629"},{"PO_ID":"117630"},{"PO_ID":"117631"}]}';
select poid_t(poid) bulk collect into l_poids
from json_table(l_values, '$.rows[*]' columns (poid number path '$.PO_ID'));
dbms_output.put_line('l_poids:');
for i in 1..l_poids.count loop
dbms_output.put_line(l_poids(i).poid);
end loop;
select poodgrpid_t(po_odgrp_id) bulk collect into l_poodgrpids
from po_mstx
where po_id in (select * from table(l_poids));
dbms_output.put_line(chr(10)||'l_poodgrpids:');
for i in 1..l_poodgrpids.count loop
dbms_output.put_line(l_poodgrpids(i).poodgrpid);
end loop;
select csid_t(cs_id) bulk collect into l_csids
from po_csx
where po_id in (select * from table(l_poids))
or po_id in (select * from table(l_poodgrpids));
dbms_output.put_line(chr(10)||'l_csids:');
for i in 1..l_csids.count loop
dbms_output.put_line(l_csids(i).csid);
end loop;
end;
Result :
l_poids:
117629
117630
117631
l_poodgrpids:
16900
16900
74
l_csids:
18475
