Oracle PL/SQL function - update and select in same loop

Question

I was wondering how to make my function more efficient. Also I think the update statement is not processing because it happens during the same loop iteration as the select statement on the same table. I was wondering how I should write this function to be efficient and actually working. I use this function in a trigger on when an order is done. Thanks in advance.

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;

You don't need to prefix your parameters with p_ if you prefix them with the function name in the body. e.g. "select recept_id from relation_6 where dieet_id= get_gewicht_product .dieet_id;" — David Aldridge
– David Aldridge, Commented Oct 13, 2016 at 10:41

user330315 · Accepted Answer · 2016-10-13 10:42:20Z

1

I think the whole procedure can be reduced to a single MERGE statement. No (nested) loops necessary:

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.result;

The inner query is the logic to calculate the new value for restvoorraad for each product. I don't think I got all the joins right, but if you can write a SELECT query that calculates this correctly, just plug it into the MERGE statement.

answered Oct 13, 2016 at 10:42

user330315

Sign up to request clarification or add additional context in comments.

4 Comments

Colivar Over a year ago

Thanks for the suggestion I havent learned about merge yet but this looks very useful. I`ll try and use it for my function(procedure).

Colivar Over a year ago

does this work if the select statement for "t" gets more than 1 record?

user330315 Over a year ago

@Colivar: yes of course. It will update all rows that meet the criteria. If the inner select returns more then one row per product_id you will need to apply a group by - the inner select must only return one row per the unique key of the target table.

Colivar Over a year ago

I added the group by and its working perfectedly thanks a lot!

David Aldridge · Accepted Answer · 2016-10-13 10:42:06Z

0

You would get better performance by using SQL statements without the cursor and the row-by-row processing.

answered Oct 13, 2016 at 10:42

David Aldridge

52.5k8 gold badges73 silver badges99 bronze badges

Comments

Kacper · Accepted Answer · 2016-10-13 10:53:49Z

0

I believe this merge is correct and much more efficient than cursors:

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;

answered Oct 13, 2016 at 10:53

Kacper

4,8182 gold badges23 silver badges35 bronze badges

Collectives™ on Stack Overflow

Oracle PL/SQL function - update and select in same loop

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related