我这个存储过程,每天有20个左右的客户端调用,大概没3分钟一次,一天可能调用上千次;oracle 的open cursor我设得20,10来分钟就出错,改为350,大概1天左右出错;web服务器tomcat就提示open cursors maximum ora-01000错误,再访问就联不上数据库了
我的办法是改进你的存储过程,其实你的目的不就是当执行第一个SQL,查询无结果时再执行第二个,再无结果,执行第三个,不需要这么多隐式光标的。改成如下方式:CREATE OR REPLACE procedure get_tele_num ( p_user_id in varchar2, p_num_no out varchar2 ) as l_count number(10);BEGIN select count(*), max(num_no) into l_count, p_num_no from cust_mast_info where is_continue_turn=0 and select_flag=0 and is_bespeak=0 and tele_count=0 and rownum<=1 ; if l_count = 0 then select count(*), max(num_no) into l_count, p_num_no from cust_mast_info where is_continue_turn=0 and select_flag=0 and is_bespeak=0 and tele_count=1 and rownum<=1 ; if l_count = 0 then select count(*), max(num_no) into l_count, p_num_no from cust_mast_info where is_continue_turn=0 and select_flag=0 and is_bespeak=0 and tele_count=2 and rownum<=1 ; end if;end if;if l_count = 0 then p_num_no:='123456789'; elsif p_num_no is not null then update cust_mast_info set select_flag = 1,user_id = p_user_id,selected_time=sysdate where num_no = p_num_no ; end if; end get_tele_num; /
to: eng(eng_llw) 不对吧,你的写法有很多的begin end,在每个里面可是都有一个隐式游标,而我的只有一个!!
(
p_user_id in varchar2, p_num_no out varchar2 )
as
l_count number(10);BEGIN
select count(*), max(num_no) into l_count, p_num_no
from cust_mast_info
where is_continue_turn=0 and select_flag=0 and is_bespeak=0 and tele_count=0 and rownum<=1 ; if l_count = 0 then
select count(*), max(num_no) into l_count, p_num_no
from cust_mast_info
where is_continue_turn=0 and select_flag=0 and is_bespeak=0 and tele_count=1
and rownum<=1 ; if l_count = 0 then
select count(*), max(num_no) into l_count, p_num_no
from cust_mast_info
where is_continue_turn=0 and select_flag=0 and is_bespeak=0 and tele_count=2
and rownum<=1 ;
end if;end if;if l_count = 0 then
p_num_no:='123456789';
elsif p_num_no is not null then
update cust_mast_info
set select_flag = 1,user_id = p_user_id,selected_time=sysdate
where num_no = p_num_no ;
end if; end get_tele_num;
/
不对吧,你的写法有很多的begin end,在每个里面可是都有一个隐式游标,而我的只有一个!!