CODEWITHSUNDEEP
oracle-databaseplsqlcursor
Md Wasi
Md Wasi

Reputation: 503

How to insert multiple cursor result set into one table

1 table having table structure-

 create table tab_abc
 ( id        varchar2(10),
   inv_bfr   varchar2(20),
   desc      varchar2(10),
   inv_afr   varchar2(10)  );

I defined 2 cursor here as C1 & C2 ->

 cursor C1 is select id, count(inv) AS "inv_bfr", desc from tab_a group by id, desc;

 cursor C2 is select count(inv) AS "inv_afr" from tab_a;

Result set of both cursor C1 & C2 will insert into table tab_abc. Cursor C1 will open before one DML operation perform & cursor C2 will open after DML operation perform. Could you please help me can i use OPEN CURSOR THEN FETCH process would be good or FOR CURSOR LOOP INSERT INTO TABLE process.

Upvotes: 0

Views: 1540

Answers (1)

Alex Poole
Alex Poole

Reputation: 191245

You don't need to use cursors (or collections, more realistically), or even any PL/SQL here. You can insert data into the table before your 'DML operaton perform' step, and then update if afterwards, e.g. with a merge:

-- initial population
insert into tab_abc (id, inv_bfr, descr, inv_afr)
select id, count(*) as inv_bfr, descr, 0 as inv_after
from tab_a
group by id, descr;

-- intermediate DML operation

-- post-DML update
merge into tab_abc t
using (
  select id, 0 as inv_bfr, descr, count(*) as inv_afr
  from tab_a
  group by id, descr
) afr
on (afr.id = t.id and afr.descr = t.descr)
when matched then
  update set inv_afr = afr.inv_afr
when not matched then
  insert (id, inv_bfr, descr, inv_afr)
  values (afr.id, afr.inv_bfr, afr.descr, afr.inv_afr);

You can wrap all of that in a PL/SQL block if you need to for other reasons, of course.

db<>fiddle demo with a few made-up rows.

Upvotes: 1

Related Questions