关于对同一个表使用游标嵌套的问题
create table dumpy
(
id number(10),
name varchar2(10),
rand number(10,2)
);
insert into dumpy values( '1 ', 'aaa ', '1.00 ');
insert into dumpy values( '1 ', 'aaa ', '1.00 ');
insert into dumpy values( '1 ', 'bbb ', '1.00 ');
insert into dumpy values(1, 'ccc ',1.00);
insert into dumpy values(1, 'ddd ',1.00);
insert into dumpy values(1, 'eee ',1.00);
insert into dumpy values(1, 'fff ',1.00);
--为何以下代码在pl sql 6.0中出错?我的目的是删除同一表中字段完全重复的记录
create or replace procedure
find_dumpy(u_id number)
as
CURSOR c_name IS SELECT * FROM dumpy for update;
TEMP_REC_C c_name%rowtype;
CURSOR c_temp IS SELECT * FROM dumpy for update;
temp_rec c_temp%rowty;
begin
open c_name;
LOOP
fetch c_name into TEMP_REC_C;
exit when c_name%notfound;
open c_temp;
loop
fetch c_temp into temp_rec;
exit when c_temp%notfound;
if(temp_rec.id=TEMP_REC_C.id and temp_rec.name=TEMP_REC_C.name and temp_rec.rand=TEMP_REC_C.rand)
fetch c_temp into temp_rec;
if(temp_rec.id=TEMP_REC_C.id and temp_rec.name=TEMP_REC_C.name and temp_rec.rand=TEMP_REC_C.rand)
delete from dumpy where cursor of c_temp;
end loop;
close c_temp;
end loop;
close c_name;
end;
------解决方案--------------------
create or replace procedure
find_dumpy(u_id number)
as
CURSOR c_name IS SELECT * FROM dumpy for update;
TEMP_REC_C c_name%rowtype;
CURSOR c_temp IS SELECT * FROM dumpy for update;
temp_rec c_temp%rowtype;
begin
open c_name;
LOOP
fetch c_name into TEMP_REC_C;
exit when c_name%notfound;
open c_temp;
loop
fetch c_temp into temp_rec;
exit when c_temp%notfound;
if(temp_rec.id=TEMP_REC_C.id and temp_rec.name=TEMP_REC_C.name and temp_rec.rand=TEMP_REC_C.rand) then
fetch c_temp into temp_rec;
end if ;
if(temp_rec.id=TEMP_REC_C.id and temp_rec.name=TEMP_REC_C.name and temp_rec.rand=TEMP_REC_C.rand) then
delete from dumpy where id = TEMP_REC_C.id;
--delete from dumpy where cursor of c_temp;
--dbms_output.put_line('ss');
end if ;
end loop;
close c_temp;
end loop;
close c_name;
end;
我给改了一下，不报错了，但还未得到正确结果，哪位再改下，得到正确结果就对了！
[解决办法]
这样就可以删除了。完全重复的记录。
DELETE FROM DUMPY
WHERE ROWID
IN ( SELECT T2.ROWID FROM DUMPY T1 , DUMPY T2 WHERE T1.ID=T2.ID AND T1.NAME=T2.NAME AND T1.RAND=T2.RAND AND T1.ROWID < T2.ROWID )
