Oracle SQL & scriptella: как сделать условную вставку или обновление на основе критерия выбора?

Я сталкиваюсь с одной проблемой, касающейся запросов Oracle Sql в Scriptella.

Мое намерение состоит в том, чтобы - мне нужно извлечь некоторые данные столбца из таблицы PRODUCT_PRICE для каждой строки (определяется с помощью условия where), извлеченные из таблицы PRODUCT, а затем, если извлеченные данные из PRODUCT_PRICE не содержат ничего или ноль, то я должен вставить новый столбец для значение цены, соответствующее идентификатору продукта и магазина в таблицу, или, если существует строка, соответствующая идентификатору продукта и магазина, то мне нужно обновить цену.

Следующий код должен очистить логику, которую я описываю -

<query connection-id="db">
select PRODUCT_ID as "product_id1", STORE_ID as "store_id1" from PRODUCT
  <query connection-id="db">
  select REGULAR_PRICE, PRODUCT_ID as "product_id2", STORE_ID as "store_id2" from PRODUCT_PRICE where PRODUCT_ID=?product_id1 and STORE_ID=?store_id1
    <script connection-id="db" if="rownum==0">
    insert into PRODUCT_PRICE(REGULAR_PRICE, PRODUCT_ID, STORE_ID) values(?price, product_id1, store_id1)
    </script>
    <script connection-id="db" if="rownum gt 0">
    update REGULAR_PRICE=?price where PRODUCT_ID=?product_id2 and STORE_ID=?store_id2
    </script>
  </query>
<query>

Мой вопрос - это не работает! Как вы видите, мне нужно вставить product_id, store_id, а также 'price' в таблицу PRODUCT_PRICE, если нет данных, соответствующих идентификатору продукта и магазина. Также я должен обновлять только цену, если существует строка с таким же product & store_id. Но когда результат, полученный во втором запросе, ничего не содержит, скрипт с условием "rownum==0" не работает. Так как же добиться этого в Oracle?

Я знаю, что в базе данных MySQL или SyBase существует один синтаксис, который называется "если существует (выберите....) / затем что-то / еще что-то", но я не смог найти ничего в базе данных Oracle. Как добиться этой условной вставки или обновления на основе критерия выбора в базе данных Oracle, потому что наш проект основан на Oracle??

PS - Здесь переменная "цена" уже найдена до первого запроса. Я поставил только соответствующую часть кода.

Источник

1 ответ

Решение

Вы можете реализовать это с помощью функции count():

<query connection-id="db">
   select PRODUCT_ID as "product_id1", STORE_ID as "store_id1" from PRODUCT
   <query connection-id="db">
       select COUNT(*) as Price_Count from PRODUCT_PRICE where PRODUCT_ID=?product_id1 and STORE_ID=?store_id1
       <script connection-id="db" if="Price_Count==0">
           insert into PRODUCT_PRICE(REGULAR_PRICE, PRODUCT_ID, STORE_ID) values(?price, product_id1, store_id1)
       </script>
       <script connection-id="db" if="Price_Count gt 0">
           update REGULAR_PRICE=?price where PRODUCT_ID=?product_id1 and STORE_ID=?store_id1
      </script>
  </query>
<query>

Хотя это решение должно работать, вы можете использовать возможности соединений SQL. Следующее решение требует только одного запроса для выполнения этой задачи. Я буду использовать левое внешнее соединение. Идея состоит в том, что вы объединяете 2 таблицы по product_id, store_id. Если в таблице product_price нет совпадений, соответствующий атрибут цены будет нулевым. Представьте, что у вас есть следующий вход:

select * from product;
PRODUCT_ID      STORE_ID  
1               1
2               1
3               1

select * from product_price;
PRODUCT_ID      STORE_ID    PRICE  
2               1           100
2               1           150
3               1           200

Левое внешнее объединение этих двух таблиц будет выглядеть так:

select p.product_id, p.store_id , pp.price   from product p LEFT OUTER JOIN product_price pp ON p.product_id =pp.product_id  and p.store_id =pp.store_id;

PRODUCT_ID      STORE_ID    PRICE  
1   1   null
2   1   100
2   1   150
3   1   200

Затем вам нужно подать заявку по группам и посчитать по цене:

select p.product_id, p.store_id , count(pp.price) as Prices_Count   from product p LEFT OUTER JOIN product_price pp ON p.product_id =pp.product_id  and p.store_id =pp.store_id group by (p.product_id, p.store_id)
PRODUCT_ID      STORE_ID    Prices_Count  
1   1   0
3   1   1
2   1   2

Затем XML становится тривиальным, и, что более важно, он должен быть намного быстрее, потому что объединения баз данных более эффективны, чем соединения, сделанные любым внешним клиентом.

  <query connection-id="db">
       select p.product_id, p.store_id , count(pp.REGULAR_PRICE) as Prices_Count   from product p LEFT OUTER JOIN product_price pp ON p.product_id =pp.product_id  and p.store_id =pp.store_id group by (p.product_id, p.store_id)

       <script connection-id="db" if="Prices_Count==0">
         insert into PRODUCT_PRICE(REGULAR_PRICE, PRODUCT_ID, STORE_ID) values(?price, product_id, store_id)
       </script>
       <script connection-id="db" if="Prices_Count gt 0">
          update REGULAR_PRICE=?price where PRODUCT_ID=?product_id and STORE_ID=?store_id
        </script>
  </query>

Я думаю, что этот подход можно еще больше оптимизировать с помощью MERGE INTO, так что все может быть выполнено в одном операторе Oracle, но я оставлю его голодным.

Источник
Другие вопросы по тегам