Функция Oracle PL / SQL - обновление и выбор в одном цикле

#sql #oracle #plsql

#sql #Oracle #plsql

Вопрос:

Мне было интересно, как сделать мою функцию более эффективной. Также я думаю, что оператор update не обрабатывается, потому что это происходит во время той же итерации цикла, что и оператор select в той же таблице. Мне было интересно, как мне написать эту функцию, чтобы она была эффективной и действительно работала. Я использую эту функцию в триггере при выполнении заказа. Заранее спасибо.

 create or replace function get_gewicht_product(p_dieet_id number)
return number
is
    cursor c_Rids
    is
        select recept_id
          from relation_6
         where dieet_id = p_dieet_id;

    type type_coll_med
        is table of relation_5%rowtype
        index by pls_integer;
    t_med type_coll_med;

    product_id number;
    gewicht_id number;
    restvoorraad_id number;
    result number;
begin

    for r_med in c_Rids
    loop
        select *
          bulk collect into t_med
          from relation_5
         where recept_recept_id = r_med.recept_id;

        for i in 1 .. t_med.count
        loop
            select restvoorraad
              into restvoorraad_id
              from voorraad
             where product_product_id=t_med(i).product_product_id;

            dbms_output.put_line(t_med(i).gewicht);
            dbms_output.put_line(restvoorraad_id);

            gewicht_id := t_med(i).gewicht;
            result := restvoorraad_id-gewicht_id;

            dbms_output.put_line(result);

            update voorraad
               set restvoorraad = result
             where product_id = t_med(i).product_product_id;
        end loop;
    end loop;

    return 1;
end get_gewicht_product;

Ответ №1:

Я думаю, что вся процедура может быть сведена к одному оператору СЛИЯНИЯ. Нет необходимости в (вложенных) циклах:

 merge into voorraad v
using
(
   select r5.product_product_id, r5.gewicht, v.restvoorraad, v.restvoorraad - r5.gewicht as result
   from relation_6 r6 
     join relation_5 r5 on r5.recept_recept_id= r6.recept_id
     join voorraad v on v.product_product_id = r5.product_product_id
   where r6.dieet_id = p_dieet_id 
) t ON (t.product_product_id = v.product_id)
when matched then update
  set restvoorraad = t.resu<

Внутренний запрос — это логика для вычисления нового значения restvoorraad для каждого продукта. Я не думаю, что я правильно понял все соединения, но если вы можете написать запрос SELECT, который вычисляет это правильно, просто подключите его к оператору MERGE .

1. Спасибо за предложение, я еще не узнал о слиянии, но это выглядит очень полезным. Я попробую использовать его для своей функции (процедуры).

2. работает ли это, если оператор select для «t» содержит более 1 записи?

3. @Colivar: да, конечно. Он обновит все строки, соответствующие критериям. Если внутренний выбор возвращает более одной строки product_id , вам нужно будет применить a group by — внутренний выбор должен возвращать только одну строку для уникального ключа целевой таблицы.

4. Я добавил группу, и она работает отлично, большое спасибо!

Ответ №2:

Вы получите лучшую производительность, используя инструкции SQL без курсора и построчной обработки.

Ответ №3:

Я считаю, что это слияние правильное и гораздо более эффективное, чем курсоры:

 merge into voorraad v
using (select r5.* from relation_5 r5 inner join relation_6 r6 on (r6.recept_id = r5.recept_recept_id) where r6.dieet_id=p_dieet_id) r
on (r.product_product_id = v.product_product_id)
update set v.restvoorraad = v.restvoorraad_id - r.gewicht;