Home » SQL & PL/SQL » SQL & PL/SQL » URGENT - Comparing records in same cursor in pl-sq
URGENT - Comparing records in same cursor in pl-sq [message #37746] Tue, 26 February 2002 06:39 Go to next message
Paul Healy
Messages: 2
Registered: February 2002
Junior Member
I have a program that requires that i extract records from a table. For each record retrieved i am then asked to loop through the rest of the corresponding extracted records to find if a record with data matching certain criteria exists.
If a match is found then i wish to extract certain values and and them to current cursor record. I then wish to delete the match as i no longer need it. After deleting i wish to save the new current reocrds data values.
The problem is that i cant work out how im meant to update the records in the table through cursor use when im needing to loop within a loop and also commit changes within both loops.
can anyone help, this is really desperate
Thanks
Paul
Re: URGENT - Comparing records in same cursor in pl-sq [message #37750 is a reply to message #37746] Tue, 26 February 2002 11:37 Go to previous messageGo to next message
Jon
Messages: 483
Registered: May 2001
Senior Member
Model your change on the following:

drop table table1;
create table table1 (col1 number, col2 number);
insert into table1 values (1,1);
insert into table1 values (1,2);
insert into table1 values (1,5);
insert into table1 values (2,3);
insert into table1 values (3,1);
insert into table1 values (3,2);
select * from table1;
declare cursor c1 is
select col1, col2, rowid
from table1 t1
where rowid > (select min(rowid) from table1 t2 where t1.col1 = t2.col1);
begin
for c1rec in c1
loop
update table1 set col2 = col2 + c1rec.col2
where rowid = (select min(rowid) from table1 where col1 = c1rec.col1);
delete from table1 where rowid = c1rec.rowid;
end loop;
end;
/
Re: URGENT - Comparing records in same cursor in pl-sq [message #37758 is a reply to message #37750] Tue, 26 February 2002 23:42 Go to previous messageGo to next message
Paul
Messages: 164
Registered: April 1999
Senior Member
Many thanks
Re: URGENT - Comparing records in same cursor in pl-sq [message #37768 is a reply to message #37750] Wed, 27 February 2002 03:35 Go to previous messageGo to next message
Jon
Messages: 483
Registered: May 2001
Senior Member
You're welcome.
Re: URGENT - Comparing records in same cursor in pl-sq [message #39298 is a reply to message #37758] Wed, 03 July 2002 23:02 Go to previous message
Zaki
Messages: 7
Registered: July 2002
Junior Member
Sir,
I am not able to get your query of comparing records with the same cursor, if you have the solution let me know.

Regards

Zaki
Previous Topic: CanI use ORACLE in Standalone...
Next Topic: procedure/triggres
Goto Forum:
  


Current Time: Fri Mar 29 07:02:29 CDT 2024