执行一个存储过程会造成连接数满吗?
因为oracle用得少,最近在用一个存储过程中出现了连接数满而出错的问题,特请教高人:该存储过程中,我用游标对两张表进行比对,一张表1000万数据,一张30万。比较的方式是如果a表中的记录存在于b表中,则为b表做一个标志,若不存在,则插入一条到b表中,如下:
create or replace function prc_zt return number is
Result number(1,0) ;
sfzh varchar2(18);
ztbh varchar2(50);
xb varchar2(1);
xm varchar2(30);
CURSOR cr_zt IS
select a.zt,a.sf,a.xb,a.xm from a
begin
open cr_zt;
LOOP
FETCH cr_zt INTO ztbh,sfzh,xb,xm;
EXIT WHEN cr_zt%NOTFOUND;
select 0 into Result from dual;
begin
select 1 into Result from dual where exists(select * from b where b.zhenghao=sfzh);
exception
WHEN NO_DATA_FOUND
THEN select 0 into Result from dual;
end;
if result=1 then
update b set alert=99 where zhenghao=sfzh;
else
insert into b(a,b,c,c,alert)
values(seq_zaitao.nextval,xm,xb,sfzh,99);
end if;
END loop;
CLOSE cr_zt;
commit;
return(0);
exception
WHEN OTHERS THEN
rollback;
return(1);
end ZtToYr;
可是运行到一定时间后就报连接已满的错误,怎么回事啊?