Re: Cursor Loop How to

Why do you consider his naming convention "odd"?

• Alton

Kenneth C Stahl wrote:

> 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 - 07:31:33 CDT