What is MERGE statement (9i)?

Question

In what order should a open/fetch/loop set of commands in a PL/SQL block be implemented if you use the %NOTFOUND cursor variable in the exit when statement? Why?

Answer 1

MERGE is a DML command that enables us to optionally update or insert data into a target table, depending on whether matching records already exist. In versions prior to 9i, we would have to code this scenario either in separate bulk SQL statements or in PL/SQL.

We can now see an example of the MERGE statement. In the following example, we will merge the source table into the target table. We will capture a count of the target table rows before and after the merge.

SQL> SELECT COUNT(*) FROM target_table;

  COUNT(*)
----------
     43485

1 row selected.

    SQL> MERGE
      2     INTO  target_table tgt
      3     USING source_table src
      4     ON  ( src.object_id = tgt.object_id )
      5  WHEN MATCHED
      6  THEN
      7     UPDATE
      8     SET   tgt.object_name = src.object_name
      9     ,     tgt.object_type = src.object_type
     10  WHEN NOT MATCHED
     11  THEN
     12     INSERT ( tgt.object_id
     13            , tgt.object_name
     14            , tgt.object_type )
     15     VALUES ( src.object_id
     16            , src.object_name
     17            , src.object_type );

86889 rows merged.