Using Merge statement inside a cursor

Question

We have a requirement to populate a master table which consists of columns from a set of 20 different tables.

I have written a stored procedure to join some of the tables that return me max number of columns and have them in a cursor.

Now. I am using for loop to iterate through the cursor records so I can insert them into the master table.

How I can use a merge statement inside the cursor for loop so I can check if I need to update existing row or insert a new row depending if the records already exists or not.

Any ideas if we can use merge statement inside a cursor for loop? Any examples?

Solution

You can do a MERGE by selecting the cursor's data from DUAL. For example

Create a source and destination table with some data

SQL> create table src ( col1 number, col2 varchar2(10) );

Table created.

SQL> create table dest( col1 number, col2 varchar2(10) );

Table created.

SQL> insert into src values( 1, 'A' );

1 row created.

SQL> insert into src values( 2, 'B' );

1 row created.

SQL> insert into dest values( 1, 'C' );

1 row created.

SQL> commit;

Commit complete.

Run the merge

SQL> ed
Wrote file afiedt.buf

  1  begin
  2    for x in (select * from src)
  3    loop
  4      merge into dest
  5      using( select x.col1 col1, x.col2 col2
  6               from dual ) src
  7         on( src.col1 = dest.col1 )
  8       when matched then
  9         update set col2 = src.col2
 10       when not matched then
 11         insert( col1, col2 )
 12           values( src.col1, src.col2 );
 13    end loop;
 14* end;
SQL> /

PL/SQL procedure successfully completed.

And verify that the merge did what we wanted. Row 1 was updated and row 2 was inserted.

SQL> select * from dest;

      COL1 COL2
---------- ----------
         1 A
         2 B

However, it generally wouldn't make too much sense to structure the code this way. You'd generally be better off putting the query that you'd use to open the cursor into the MERGE statement directly so that rather than selecting one row of data from DUAL, you're selecting all the data you want to merge from all the tables you're trying to merge the data from. Of course, it may make sense to create a view for this query that the MERGE statement can query in order to keep the MERGE statement readable.

Comments

[jagamot]

in the for loop, can "(select * from src)" be a cursor?

[Justin Cave]

@HonorGod - No, it can't be a cursor. It can, and should, however, be the query that was used to open the cursor.