一个循环的存储过程..

[leiru]

现在报如下错误
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "TESTDB.DODATACOMPARE", line 21
ORA-06512: at line 1

想实现的是先把一天的数据都查出来
select t.customer_id
from answer_info t
where t.answer_status = '99'
and t.answer_date between
to_date('2008-11-9','yyyy-mm-dd') and
to_date('2008-11-10','yyyy-mm-dd');
然后打开游标:
接着循环上面的数据,按customer_id(是唯一的)查找tel_1这个字段
select c.tel_1
into v_tel
from customer_info c
where c.life_sys_id = v_customeridData;
查出来以后,就把这个赋值给条件
SELECT count(*)
into v_count
FROM customer_info
WHERE tel_1 = v_tel
or tel_3 = v_tel
or job_tel = v_tel
or little_mobile = v_tel;
如果有重复的数据就把改字段
update answer_info t
set t.Compare_status = '回访成功后重复号码'
where t.customer_id = v_customeridData;



我写的存储过程如下:
create or replace procedure doDataCompare is
v_per number;
v_tel varchar2(100);
v_count number;
v_customeridData varchar2(1000);
Type customeridCursor is ref cursor;
cusor1 customeridCursor;
begin
v_per := 0;
open cusor1 for
select t.customer_id
from answer_info t
where t.answer_status = '99'
and t.answer_date between
to_date('2008-11-9','yyyy-mm-dd') and
to_date('2008-11-10','yyyy-mm-dd');
loop
fetch cusor1
into v_customeridData;
v_per := v_per + 1;
select c.tel_1
into v_tel
from customer_info c
where c.life_sys_id = v_customeridData;
SELECT count(*)
into v_count
FROM customer_info
WHERE tel_1 = v_tel
or tel_3 = v_tel
or job_tel = v_tel
or little_mobile = v_tel;
if (v_count > 1) then
update answer_info t
set t.Compare_status = '回访成功后重复号码'
where t.customer_id = v_customeridData;
end if;
end loop;
close cusor1;
end doDataCompare;

-----------------------------
Type customeridCursor is ref cursor;
cusor1 customeridCursor;
我可以这样定义一个游标吗??

各位帮我看看到底是那里出了问题啊...
在线等...........

[BlueskyWide]

  --给改了一下，试试：
  
  create table answer_info_bak as select * from answer_info where 1=1;  --先给表做个备份。
  
  create or replace procedure doDataCompare is
  tel_1            customer_info.tel_1%type;
  customer_id      answer_info.customer_id%type;
  v_count1         number(8);
  v_per            number;
  v_tel            varchar2(100);
  v_count          number;
  v_customeridData varchar2(1000);
  cursor customeridCursor is
    select t.customer_id
      from answer_info t
     where t.answer_status = '99'
       and t.answer_date between to_date('2008-11-9', 'yyyy-mm-dd') and
           to_date('2008-11-10', 'yyyy-mm-dd');
  cursor c2 is
    select c.tel_1
      from customer_info c
     where c.life_sys_id = v_customeridData;
  cursor c3 is
    SELECT count(*)
      FROM customer_info
     WHERE tel_1 = v_tel
        or tel_3 = v_tel
        or job_tel = v_tel
        or little_mobile = v_tel;
  cursor c4 is
    select count(*) from answer_info;
begin
  v_per := 0;
  open c4;
  fetch c4
    into v_count1;
  for i in 1 .. v_count1 loop
    open customeridCursor;
    fetch customeridCursor
      into v_customeridData;
    v_per := v_per + 1;
    open c2;
    fetch c2
      into v_tel;
    open c3;
    fetch c3
      into v_count;
    if (v_count > 1) then
      update answer_info t
         set t.Compare_status = '回访成功后重复号码'
       where t.customer_id = v_customeridData;
    end if;
  end loop;
  close customeridCursor;
  close c2;
  close c3;
end doDataCompare;

[Quote=引用楼主 leiru 的帖子:]
现在报如下错误
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "TESTDB.DODATACOMPARE", line 21
ORA-06512: at line 1

想实现的是先把一天的数据都查出来
select t.customer_id
from answer_info t
where t.answer_status = '99'
and t.answer_date between
to_date('2008-11-9','yyyy-mm-dd') and
to_date('2008-11-10','yyyy…
[/Quote]

[leiru]

怎么跟进过程调试?
我在存储过程里设置了断点..
怎么调试?

[范佩西_11]

fetch cusor1
into v_customeridData;
加个exit when cusor1%notfound;

fetch cusor1
      into v_customeridData;
exit when cusor1%notfound;

[leiru]

[Quote=引用 9 楼 BlueskyWide 的回复:]
SQL code
--给改了一下，试试：

create table answer_info_bak as select * from answer_info where 1=1; --先给表做个备份。

create or replace procedure doDataCompare is
tel_1 customer_info.tel_1%type;
customer_id answer_info.customer_id%type;
v_count1 number(8);
v_per number;
v_tel varchar2(100);
v_count number;…
[/Quote]

报了如下错误:
ORA-06511: PL/SQL: cursor already open
ORA-06512: at "testdb.DOAAA", line 10
ORA-06512: at "testdb.DOAAA", line 34
ORA-06512: at line 1

[leiru]

[Quote=引用 11 楼 linzhangs 的回复:]
fetch cusor1
into v_customeridData;
加个exit when cusor1%notfound;

SQL codefetch cusor1
into v_customeridData;
exit when cusor1%notfound;
[/Quote]

已经有2分钟了,还没有执行完..

[BlueskyWide]

where t.customer_id = v_customeridData;
end if;
end loop;
改为：
where t.customer_id = v_customeridData;
end if;
close customeridCursor;
close c2;
close c3;
end loop;

[leiru]

fetch cusor1
into v_customeridData;
加个exit when cusor1%notfound;
这句话是说当游标没有发现数据时退出执行吗???
现在执行完了..

[leiru]

[Quote=引用 9 楼 BlueskyWide 的回复:]
SQL code
--给改了一下，试试：

create table answer_info_bak as select * from answer_info where 1=1; --先给表做个备份。

create or replace procedure doDataCompare is
tel_1 customer_info.tel_1%type;
customer_id answer_info.customer_id%type;
v_count1 number(8);
v_per number;
v_tel varchar2(100);
v_count number;…
[/Quote]

该好后正在执行,好像也是陷入死循环了,和上面一开始我写的一样..
一直在执行..
是不是也要加个.
fetch cusor1
into v_customeridData;
加个exit when cusor1%notfound; 这个啊..

另外我写的那个加上上面的语句已经可以执行了..
我先测试下数据是否正确,然后马上结贴..
谢谢大家了.~!

[BlueskyWide]

要保证光标customeridCursor和c2取出的数据个数只有一个。注意使用min或max函数。

[leiru]

[Quote=引用 17 楼 BlueskyWide 的回复:]
要保证光标customeridCursor和c2取出的数据个数只有一个。注意使用min或max函数。
[/Quote]


为什么定义这么多游标啊..
好像显得很乱..这部分不太明白,给解释一下啊..
begin
v_per := 0;
open c4;
fetch c4
into v_count1;
for i in 1 .. v_count1 loop
open customeridCursor;
fetch customeridCursor
into v_customeridData;
v_per := v_per + 1;
open c2;
fetch c2
into v_tel;
open c3;
fetch c3
into v_count;
if (v_count > 1) then
update answer_info t
set t.Compare_status = '回访成功后重复号码'
where t.customer_id = v_customeridData;
end if;
end loop;
close customeridCursor;
close c2;
close c3;
end doDataCompare;

[leiru]

哦,知道了,你时循环c4
不过c2的值就一个..可以循环吗??

[leiru]

这样写是仅仅循环c4吗??
里面的c1,c2,c3不用循环了??
这里不是已经有他的循环范围了吗?
cursor customeridCursor is
select t.customer_id
from answer_info t
where t.answer_status = '99'
and t.answer_date between to_date('2008-11-9', 'yyyy-mm-dd') and
to_date('2008-11-10', 'yyyy-mm-dd');

为什么还要循环c4呢??
这样效率会有问题把?

[leiru]

最后一个问题了..
loop
fetch cusor1
into v_customeridData;
v_per := v_per + 1;
select c.tel_1
into v_tel
from customer_info c
where c.life_sys_id = v_customeridData;
SELECT count(*)
into v_count
FROM customer_info
WHERE tel_1 = v_tel
or tel_3 = v_tel
or job_tel = v_tel
or little_mobile = v_tel;
if (v_count > 1) then
如果比较c.tel_1就出现重复数据..后面我就不再比较c.tel_3了...
而是跳转到下一次循环,就像java里的 return一样,存储过程有这个语法吗?
update answer_info t
set t.Compare_status = '回访成功后重复号码'
where t.customer_id = v_customeridData;
end if;

select c.tel_3
into v_tel
from customer_info c
where c.life_sys_id = v_customeridData;
SELECT count(*)
into v_count
FROM customer_info
WHERE tel_1 = v_tel
or tel_3 = v_tel
or job_tel = v_tel
or little_mobile = v_tel;
if (v_count > 1) then
update answer_info t
set t.Compare_status = '回访成功后重复号码'
where t.customer_id = v_customeridData;
end if; [/color]
end loop;

[DragonBill]

用GOTO <<LABEL>>处理

[leiru]

还有一个问题,就是我想只跳出本次循环，但还要进行下一循环.
用return可以吗??
我的条件是这样的..
if (v_count > 1) then
update cc_answer_info t
set t.Compare_status = '回访成功后重复号码'
where t.customer_id = v_customeridData;
return;
end if;
如果用exit when 的话,要怎么写??

[范佩西_11]

加那个就是没有发现就退出循环要不一直会做下去

[leiru]

另外如果我还要再比较令一个值..加上红色的就ok了??????????
loop
fetch cusor1
into v_customeridData;
v_per := v_per + 1;
select c.tel_1
into v_tel
from customer_info c
where c.life_sys_id = v_customeridData;
SELECT count(*)
into v_count
FROM customer_info
WHERE tel_1 = v_tel
or tel_3 = v_tel
or job_tel = v_tel
or little_mobile = v_tel;
if (v_count > 1) then
update answer_info t
set t.Compare_status = '回访成功后重复号码'
where t.customer_id = v_customeridData;
end if;

select [color=#FF6600]c.tel_3 //只换这个地方就可以了吗??
into v_tel
from customer_info c
where c.life_sys_id = v_customeridData;
SELECT count(*)
into v_count
FROM customer_info
WHERE tel_1 = v_tel
or tel_3 = v_tel
or job_tel = v_tel
or little_mobile = v_tel;
if (v_count > 1) then
update answer_info t
set t.Compare_status = '回访成功后重复号码'
where t.customer_id = v_customeridData;
end if; [/color]
end loop;

[范佩西_11]

你要再加值的话再into一次阿，自己可以试下吗。我们只知道大体的语法，调试大多还是靠自己阿

[leiru]

哦.谢谢..

[leiru]

loop
<<outer>>
fetch cusor1
into v_customeridData;
exit when cusor1%notfound;
v_per := v_per + 1;
select c.tel_1
into v_tel
from customer_info c
where c.life_sys_id = v_customeridData
and rownum = 1;
SELECT count(*)
into v_count
FROM customer_info
WHERE tel_1 = v_tel
or tel_3 = v_tel
or job_tel = v_tel
or little_mobile = v_tel;
if (v_count > 1) then
update answer_info t
set t.Compare_status = '回访成功后重复号码'
where t.customer_id = v_customeridData;
insert into cc_telcompare (customer_id,tel,telno) values (v_customeridData,'tel_1',v_tel);
goto outer; end if; //这样就跳出本次循环了...
end loop;

总算解决了,大家也试试啊.