Using Merge statement inside a cursor

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?

Answers


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.


Need Your Help

Align input fields in a fieldset

jquery html css html5 css3

I am having a selection input fields in a fieldset and a submit button.All the programming logic were completed to make it work as desired.But i am currently facing an uphill task of aligning the i...

Displaying message after paypal payment

php javascript html paypal

I want to display a message in php ($mymessage) after a successful paypal payement.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.