Re: Simple FOR loop

On 4 Apr 2003 13:23:05 -0800, knut_meidal_at_email.com (Knut E. Meidal) wrote:

>Using Oracle 9.0.1, this is what I want to do:
>* Create a simple loop iterating over a set of date values
>* Inside loop, run an update of a table, using the loop variable as where
>clause item.
>
>I'm using SQL Navigator for doing this, but it will be put in a stored
>procedure onvce I figure this out.
>I'm not really able to create this in an easy way, which I hoped. I hoped to
>do it using a simple FOR loop, and avoid some cursor programming (since I'm
>not really a PL/SQL programmer)
>
>I've been trying something like this:
>
>declare l_dateval date;
>
>for l_dateval in (select distinct date_col from slv) loop
> update slv
> set slv.settledayhist = (select s.settleday from statement s where s.id
>= slv.setl_id)
> where slv.settleday = to_date('27.02.2003', 'DD.MM.RRRR')
> and slv.settledayhist IS NULL;
>end loop;
>
>Any insights? Can it be done?
>
>Thanks in advance
>Knut

Yes it can be done, but as you don't refer to l_dateval.date_col in your update statement it very much looks like the update statement is wrong.
Other than that I'm not sure why you don't simply code a subquery in the where clause and forget about the for loop. Most likely you don't need it at all, and it will be more performat without pl/sql

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Fri Apr 04 2003 - 16:46:21 CST