Re: Convert PLSQL update with forall

Hi Pawel

I will test it this week, should work in one of the problematic cases. We have a couple more which are similar but instead of 2 updates with same conditions we have 6 and all 6 with different conditions. Those I will look into Jacek examples.

Thanks a lot

2018-05-20 21:22 GMT+02:00 Pawel Kwiatkowski <yarel79_at_gmail.com>:

> Have you tried something like :
>
> begin
> for indx in (complex query) loop
> update t1 set
> c3 = decode(c10,'NO',c2 + c4,c3),
> c5 = decode(c10,'NO',c5, c7 + c8)
> where
> c1 = indx.c1
> and c2 = indx.c2;
> end loop;
> end;
> /
>
> br,
> Pawel
>
> On Sun, May 20, 2018 at 5:42 PM, amonte <ax.mount_at_gmail.com> wrote:
>
>> Hello
>>
>> I have a plsql procedure which contains a complex cursor and a couple of
>> update statements using the cursor output, it looks like
>>
>>
>> begin
>>
>> for indx in (complex query)
>> loop
>>
>> if indx.c10 = 'NO' then
>> update t1
>> set c3 = c2 + c4
>> where c1 = indx.c1
>> and c2 = indx.c2;
>> else
>> update t1
>> set c5 = c7 + c8
>> where c1 = indx.c1
>> and c2 = indx.c2
>> end if;
>>
>> end loop;
>> end;
>> /
>>
>> The cursor (join of 7 tabls and a few EXISTS subqueries) returns
>> aproximately 2 million rows and the process is not as fast as desired. I
>> was looking into FORALL to improve this procedure but I cannot find a way
>> due to the conditional updates (two different update statements). Anyone's
>> got an idea if FORALL can be implemented in such situations?
>>
>> Thank you very much
>>
>> Alex
>>
>>
>

--
http://www.freelists.org/webpage/oracle-l