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, а также «цену» в таблицу PRODUCT_PRICE, если нет данных, соответствующих идентификатору продукта и магазина. Также мне нужно обновить только цену, если существует строка одного и того же продукта и store_id. Но когда результат, полученный во втором запросе, ничего не содержит, то скрипт с условием "rownum==0" не работает. Итак, как этого добиться в Oracle?

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

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


sql oracle scriptella
person user1838450    schedule 10.04.2013    source источник

Ответы (1)


arrow_upward
5
arrow_downward

Вы можете реализовать это с помощью функции 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, но я оставлю это для голодных умов.

person ejboy    schedule 10.04.2013
comment
Спасибо за этот вопрос. Я не занимался тяжелым SQL более 3 лет. Для меня это была хорошая практика! - person ejboy; 10.04.2013