CODEWITHSUNDEEP
sqloracle-databaseplsql
Colivar
Colivar

Reputation: 41

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

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;

Upvotes: 0

Views: 1427

Answers (3)

Kacper
Kacper

Reputation: 4818

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;

Upvotes: 0

user330315
user330315

Reputation:

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.

Upvotes: 1

David Aldridge
David Aldridge

Reputation: 52336

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

Upvotes: 0

Related Questions