Re: Cursor Loop How to

From: Kenneth C Stahl <BluesSax_at_Unforgettable.com>
Date: Tue, 20 Jul 1999 07:58:33 -0400
Message-ID: <37946469.3A5AE986@Unforgettable.com>

Paul wrote:

> I have a table that has a time stamp stored as a text field. The
> problem is that I have several hundred that are the same.
>
> I know who to find them.
> SELECT Time_Stamp FROM Time_Table WHERE Create_Time = '1200000000'
>
> How to I find them and then add +1 to each of them progressively so that
> they all the records have a different time stamp.
>
> I do want to see the records that would be changed before I run the
> actual updating statement, so I want to use a select statement.
>
> I have know that the cursor when using a fetch statement needs a select
> into to work. This approach does not let me see what the changes would
> be prior to running the SQL.
>
> I have rights to update records in an existing table, but can't create a
> new table.
>
> Example:
> Original Revised
> 1200000000 1200000000
> 1200000000 1200000001
> 1200000000 1200000002
> 1200000000 1200000003
> 1200000000 1200000004
>

Your naming convention seems odd, but here goes.This is a pl/sql script to be used in sqlplus.

set serveroutput on;
set feedback off;
set pages 0;
Declare

    Temp_Stamp Number(10);
    Do_Update Boolean := False;
    Cursor C1 is
    select *
    from time_table
    where create_time = '1200000000'
    order by time_stamp
    for update of time_stamp;
begin

    dbms_output.enable(1000000);
    For i in c1 loop

        if c1%rowcount > 1 then
            Temp_Stamp = To_Number(i.time_stamp) + 1;
            dbms_output.put_line('Old: '||i.time_stamp||' new:
'||to_char(Temp_Stamp));
            If Do_Update then
                update time_table
                set time_stamp = to_char(Temp_Stamp)
                where current of c1;
           end if;
        end if;

    end loop;
end;
.
/

Run it once with the Do_Update set to false to make sure the outcome is what you expect and once you are satisfied, change Do_Update to true.

There is no error checking in all of this so you'll want to flesh it out, but this is the basic gist.

Ken Received on Tue Jul 20 1999 - 06:58:33 CDT