现在报如下错误
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;
我可以这样定义一个游标吗??各位帮我看看到底是那里出了问题啊...
在线等...........
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;
我可以这样定义一个游标吗??各位帮我看看到底是那里出了问题啊...
在线等...........
into v_tel
from customer_info c
where c.life_sys_id = v_customeridData;
应该是这句返回的数值超过1个。
into v_tel
from customer_info c
where c.life_sys_id = v_customeridData;
应该是这句返回的数值超过1个。
这句绝对是返回一个..
我从数据库里查了..c.life_sys_id 是唯一的...
应该是这个 select c.tel_1
into v_tel
from customer_info c
where c.life_sys_id = v_customeridData
and rownum = 1; ---加上这个再试,看看是不是这里的问题
请确认一下。
然后执行会不会出现错误。
v_tel 这个字段就存了一个手机号码..
刚才AND ROWNUM=1 试了一下,3分钟了还没执行完,我的数据才几百条啊,
好像陷入死循环了...另外如果我试循环这个语句的话.
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');
应该后面不会出现多条数据的..我查了id值是唯一的..不可能有多条..
跟进过程调试下,具体在v_customeridData等于什么值的时候报错。就比较明朗了。
--给改了一下,试试:
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;
into v_customeridData;
加个exit when cusor1%notfound;
fetch cusor1
into v_customeridData;
exit when cusor1%notfound;
报了如下错误:
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
end if;
end loop;
改为:
where t.customer_id = v_customeridData;
end if;
close customeridCursor;
close c2;
close c3;
end loop;
into v_customeridData;
加个exit when cusor1%notfound;
这句话是说当游标没有发现数据时退出执行吗???
现在执行完了..
该好后正在执行,好像也是陷入死循环了,和上面一开始我写的一样..
一直在执行..
是不是也要加个.
fetch cusor1
into v_customeridData;
加个exit when cusor1%notfound; 这个啊..另外我写的那个加上上面的语句已经可以执行了..
我先测试下数据是否正确,然后马上结贴..
谢谢大家了.~!
好像显得很乱..这部分不太明白,给解释一下啊..
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;
里面的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呢??
这样效率会有问题把?
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;
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;
用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 的话,要怎么写??
<<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;总算解决了,大家也试试啊.