Использование PL/SQL для запуска операции условного слияния

Правка. В исходном сообщении спрашивается, как преобразовать существующий набор данных в установленную таблицу (с именем PROFILES) с помощью подхода, который может решить SQL или PL/SQL.

Редактировать снова: Последний комментарий от OP был довольно тонким. Если у вас нет прямого доступа к SQL, вам в любом случае понадобится CURSOR, управляющий запрос или какая-то другая конструкция для обработки каждой из ваших записей. Многие компоненты промежуточного программного обеспечения на основе JDBC также принимают курсоры в качестве входных данных. Вы можете ввести все свои данные в один вызов процедуры... взгляните на REF CURSOR типов данных в PL/SQL. Если это так, это решение все еще может помочь.

Используя составной ключ соединения, обновите данные в целевой таблице на основе нескольких критериев:

1. INSERT исходные данные, если они еще не существуют.
2. Переключите или UPDATE значение статуса, если идентификатор человека (имя + фамилия) существует.
3. Если человек уже существует в целевой таблице и уже имеет статус «активный», пропустите его.

Образец данных

Я назвал свои таблицы немного по-другому и изменил имя столбца «имя», которое является зарезервированным ключевым словом sql/plsql... для предотвращения любых возможных конфликтов в будущем.

Примеры операторов вставки данных (DML):

*Для ясности: имена в тестовой схеме не совсем соответствуют ОП. STACK_PROFILES = PROFILES, а STACK_PROFILE_MERGE_SOURCE представляет "какой-то источник"... это мог быть xml-канал, текстовый файл csv и т. д. и т. д.

from: load_profile_data.sql...

commit; ...


INSERT INTO STACK_PROFILE_MERGE_SOURCE (profile_name, surname) VALUES ('BRUCE' , 'WAYNE'); INSERT INTO STACK_PROFILE_MERGE_SOURCE (profile_name, surname) VALUES ('SPONGE' , 'ROBERT'); INSERT INTO STACK_PROFILE_MERGE_SOURCE (profile_name, surname) VALUES ('CLARK' , 'KENT'); INSERT INTO STACK_PROFILE_MERGE_SOURCE (profile_name, surname) VALUES ('LOIS' , 'LAINE'); INSERT INTO STACK_PROFILE_MERGE_SOURCE (profile_name, surname) VALUES ('MARTIN' , 'SHORT'); INSERT INTO STACK_PROFILE_MERGE_SOURCE (profile_name, surname) VALUES ('DAMON' , 'WAYANS'); INSERT INTO STACK_PROFILE_MERGE_SOURCE (profile_name, surname) VALUES ('ROBIN' , 'WILLIAMS'); INSERT INTO STACK_PROFILE_MERGE_SOURCE (profile_name, surname) VALUES ('BRUCE' , 'WILLIS'); INSERT INTO STACK_PROFILE_MERGE_SOURCE (profile_name, surname) VALUES ('DENNIS' , 'HOPPER'); INSERT INTO STACK_PROFILE_MERGE_SOURCE (profile_name, surname) VALUES ('WHOOPI' , 'GOLDBERG'); INSERT INTO STACK_PROFILE_MERGE_SOURCE (profile_name, surname) VALUES ('GRACE' , 'HOPPER'); INSERT INTO STACK_PROFILE_MERGE_SOURCE (profile_name, surname) VALUES ('JERI' , 'RYAN');

Тестовые случаи

Полезно понять представленные требования. Написание нескольких тестовых случаев приближает нас.

Для тестовых случаев 1 и 2...

Для тестовых случаев 3 и 4...

Исходный код PL/SQL

Существует более простой способ применить дополнительную условную логику с помощью функции, подобной SQL-слиянию. Следующий блок PL/SQL Anonymous использует outer join syntax для идентификации записей, которые должны быть вставлены или обновлены. Третья категория (активная и уже присутствующая в целевой таблице) также наблюдается, поскольку цикл обработки курсора пропускает записи этого определения.

Цикл обработки и курсор

Мы используем синтаксис FOR UPDATE и WHERE CURRENT OF в операциях dml, потому что состояние данных, на которые ссылается этот запрос, изменяется в течение срока его использования.

 declare
    c_default_status_active   constant number:= 1;
    c_status_inactive         constant number:= 0;

    cursor profile_cur is
       select sp.profile_name as target_name, 
              sp.surname as target_surname, sp.active as original_status,
              spm.profile_name as source_name, spm.surname as source_surname

         from stack_profiles sp, stack_profile_merge_source spm
        where spm.profile_name = sp.profile_name(+)
          and spm.surname = sp.surname(+)
        order by spm.profile_name asc nulls last, 
          spm.surname asc
          for update of sp.profile_name, sp.surname, sp.active;

        v_rec_profile  profile_cur%ROWTYPE;

     begin

      open profile_cur;
     fetch profile_cur into v_rec_profile;

     while profile_cur%found loop
       -- insert condition (no match in outer join...)
       if v_rec_profile.original_status is null
       then
       insert into stack_profiles (profile_name, surname, active)
       values (v_rec_profile.source_name, v_rec_profile.source_surname, 
           c_default_status_active);

       elsif
       -- flip status from inactive to active for existing but 
       -- inactive records.
       v_rec_profile.original_status = c_status_inactive then
       update stack_profiles
          set active = c_default_status_active
        where current of profile_cur;
          end if;

      fetch profile_cur into v_rec_profile;
      end loop;
      close profile_cur;

commit;

end;

Обсуждение

I have noted many different approaches to this type of problem. The specific approach used here is to demonstrate the concept involved. Results may vary depending on the database configuration, its usage and set up.

Хорошо, я полагаю, это не очень хорошая практика, но поскольку ваш столбец ACTIVE имеет тип NUMBER (1), вы можете легко сгенерировать исключение ORA-01438, просто попытавшись обновить его значение до большего. Например, что-то вроде этого вызовет исключение, если новое и старое значения active равны:

MERGE INTO profiles USING (
    SELECT
        'Mark' myName,
        'Zibi' mySurname,
        1    myActive
   FROM DUAL
) ON (
   name = myName
   AND surname = mySurname
)
WHEN MATCHED THEN
    UPDATE SET
        active =  CASE WHEN active = myActive THEN 11 ELSE myActive END
WHEN NOT MATCHED THEN
    INSERT (
        name,
        surname,
        active
    ) VALUES (
        myName,
        mySurname,
        myActive
    );

В таком случае лучше использовать PL/SQL через хранимую процедуру или просто выполняя анонимный блок SQL со стороны клиента вместо одного оператора MERGE SQL.

Анонимный блок PL/SQL может выглядеть так:

declare
  -- Parameters of query, initialization values  
  pName    profiles.name%type    := 'Mark';
  pSurname profiles.surname%type := 'Zibi';
  pActive  profiles.active%type  := 0;

  -- variable used for test against table
  vIsActiveInDb profiles.active%type;
begin

  select 
    max(profs.active) into vIsActiveInDb
  from 
    profiles profs
  where 
    profs.name = pName and profs.surname = pSurname
  ;

  if(vIsActiveInDb is null) then
    -- profile not found, create new one 
    insert into profiles(name, surname, active)
    values(pName, pSurname, pActive);

  elsif(vIsActiveInDb != pActive) then
    -- profile found, activity flag differs 
    update profiles set active = pActive 
    where name = pName and surname = pSurname;

  else
    -- profile found with same activity flag
    raise_application_error(
      -20001, -- custom error code from -20000 to -20999
      'Profile "'||pName||' '||pSurname||'" already exists with same activity flag'
    );  
  end if;

end;

SQLFiddle

В приведенном выше коде есть два предложения:
1. Пара (name, surname) является первичным ключом, поэтому всегда выбирается одна строка или ничего;
2. Поле active не может быть пустым (например, создано с ограничением not null).
Код будет немного сложнее, если это предложение не сработает. Этот вариант можно найти в этом SQLFiddle< /а>.

Я никогда не использовал MyBatis, но на основе ответа из вашего комментария XML-описание для такого запроса может выглядеть так:

<update id="UpdateProfileActivity" parameterType="map" statementType="CALLABLE">   
  declare
    -- Parameters of query, initialization values
    pName    profiles.name%type    := #{piName,    mode=IN, jdbcType=VARCHAR};
    pSurname profiles.surname%type := #{piSurname, mode=IN, jdbcType=VARCHAR};
    pActive  profiles.active%type  := #{piActivity,mode=IN, jdbcType=NUMERIC};

    -- variable used for test against table
    vIsActiveInDb profiles.active%type;   begin

    select
      max(profs.active) into vIsActiveInDb
    from
      profiles profs
    where
      profs.name = pName and profs.surname = pSurname
    ;

    if(vIsActiveInDb is null) then
      -- profile not found, create new one
      insert into profiles(name, surname, active)
      values(pName, pSurname, pActive);

    elsif(vIsActiveInDb != pActive) then
      -- profile found, activity flag differs
      update profiles set active = pActive
      where name = pName and surname = pSurname;

    else
      -- profile found with same activity flag
      raise_application_error(
        -20001, -- custom error code from -20000 to -20999
        'Profile "'||pName||' '||pSurname||'" already exists with same activity flag'
      );
    end if;

  end; 
</update>

Вы можете сделать это, добавив условие where либо к источнику -using (---- подзапрос ---), чтобы отфильтровать команду при совпадении, либо добавить условие where после несоответствия.

В следующем примере я буду объединять записи с id 520 до 530, при этом я не буду вставлять запись, где id = 525

--------
merge into merchant_tmp2 dest
using (select * from merchant where id between 520 and 530) src
on(dest.id=src.id)
when matched then 
update set address=address ||' - updated'
when not matched then 
insert (ID,....)
values (src.ID,....)
where src.id <> 525;

ссылка: https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm#SQLRF01606