Остерегайтесь ошибок в условиях соединения и фильтрации!

Как и в случае соединения таблиц, в команде слияния много подводных камней, особенно если во фразе on вы не смогли надлежащим образом сформулировать условия объединения и фильтрации. Рассмотрим, что произойдет, если мы специфицируем условия соединения таким образом, что будет пропущен фильтр, указывающий, какой фильм следует искать Oracle. Взгляните на следующий блок кода:

SQL> merge into movies Ml

2 using movies M2 on (M2,movie_name = Ml.movie_name)

3 when matched tnen update set Ml,showtime = '9:30 PM'

4 when not matched then insert (Ml.movie_name, Ml.showtime)

5 values ( ' THE MUtlMY ' , ' 9 : 30 PM ') ;

2 row merged.

SQL> select * from movies;

MOVIEJJAME SHOWTIME

GONE WITH THE WIND 9:30 PM

LAWRENCE OF ARABIA 9:30 PM

В подобной ситуации мы правильно определили условия соединения, позволяющие избежать появления в самосоединении декартова произведения. Однако мы забыли включить фильтрующее условие, указывающее Oracle, что необходимо проверить, имеется ли в таблице MOVIES запись для фильма "The Mummy". Следовательно, Oracle идет напролом и заменяет каждую строку таблицы в соответствии со значениями, заданными во фразе when matched. Теперь посмотрим, что произойдет, если мы забудем об условии соединения и включим только условия фильтра:

SQL> merge into movies ml

2 using movies m2 on (m2.movie_name = 'THE MUMMY¹)

3 when matched then update set ml.showtime = 42:00 AM'

4 when not matched then insert (nil .movie_naiie, ml.showtime)

5 values ('THE MUMMY','12:00 AM');

merge into movies ml

ERROR at line It

ORA-00001: unique constraint (SCOTT.PK_MOVIES) violated

На первый взгляд может показаться странным, что Oracle возвращает сообщение о нарушении ограничений. Ведь, в конце концов, мы пытаемся добавить строку для фильма "The Mummy", которая не нарушает никаких ограничений первичного ключа для столбца MOVIE_NAME. На самом деле причина того, что мы получили это сообщение об ошибке, кроется в том, что мы пропустили условие соединения двух копий таблицы MOVIES. Поэтому, как говорится, за кулисами Oracle сформировала декартово произведение, что и привело к возникновению дублирующих строк для фильма "The Mummy", Ошибка возникает, когда Oracle пытается вставить дублирующую строку для фильма "The Mummy" в таблицу MOVIES. А теперь взглянем, что происходит, когда мы отменяем ограничение первичного ключа для таблицы MOVIES:

SQL> alter table movies disable constraint pk_movies;

Table altered.

SQL> merge inco movies ml

2 using movies m2 on (m2.movie_name = 'THE MUMMY¹)

3 when matched then update set nil.showtime = '12:00 AM'

4 when not matched then insert (ml.movie_name, ml.showtime)

5 values ( 'THE MUMMY', '12:00 AM');

2 rows merged,

SQL> select * from movies;

MOVIE_NAME SHOWTIME

GONE WITH THE WIND 9:30 PM

LAWRENCE OF ARABIA 9:30 PM

THE MUMMY 12:00 AM

THE MUMMY 12:00 AM