create or replace procedure SP_GET_NEWEST_CAPTCHA(
v_ACCOUNT_ID in VARCHAR2, --接收短信的手机号
v_Tail_num in VARCHAR2, --尾号
v_result out VARCHAR2, --结果
v_CAPTCHA out varchar2 --验证码
) is
l_recvmsgid NUMBER;
begin
--取出验证码
if (not ((v_Tail_num is null) or (v_Tail_num = ' '))) and (not ((v_ACCOUNT_ID is null) or (v_ACCOUNT_ID = ' '))) then
select in_msg_id,in_captcha
into l_recvmsgid, v_CAPTCHA
from( select t.msg_id in_msg_id, CAPTCHA in_captcha
from jp_recvmsg_carcard t
where t.tail_num = v_Tail_num
and t.account_id = v_ACCOUNT_ID
and t.msg_status = 0
order by t.msg_time desc)
where rownum = 1 ;
elsif (v_Tail_num is null) or (v_Tail_num = ' ') then
select in_msg_id,in_captcha
into l_recvmsgid, v_CAPTCHA
from( select t.msg_id in_msg_id, CAPTCHA in_captcha
from jp_recvmsg_carcard t
where t.account_id = v_ACCOUNT_ID
and t.msg_status = 0
order by t.msg_time desc)
where rownum = 1 ;
elsif (v_ACCOUNT_ID is null) or (v_ACCOUNT_ID = ' ') then
select in_msg_id,in_captcha
into l_recvmsgid, v_CAPTCHA
from( select t.msg_id in_msg_id, CAPTCHA in_captcha
from jp_recvmsg_carcard t
where t.tail_num = v_Tail_num
and t.msg_status = 0
order by t.msg_time desc)
where rownum = 1 ;
end if; --更新状态
update jp_recvmsg_carcard t
set t.msg_status = 1
where t.msg_id = l_recvmsgid;
commit;
v_result:='';
EXCEPTION WHEN OTHERS THEN
v_Result:='';
ROLLBACK;
end SP_GET_NEWEST_CAPTCHA;
优化前
create or replace procedure SP_GET_NEWEST_CAPTCHA(
v_ACCOUNT_ID in VARCHAR2, --接收短信的手机号
v_Tail_num in VARCHAR2, --尾号
v_result out VARCHAR2, --结果
v_CAPTCHA out varchar2 --验证码
) is
l_recvmsgid NUMBER;
begin if(((v_Tail_num is null) or (v_Tail_num = ' ')) and ((v_ACCOUNT_ID is null) or (v_ACCOUNT_ID = ' '))) then
v_result:='';
return;
end if;
--取出验证码
select in_msg_id,in_captcha
into l_recvmsgid, v_CAPTCHA
from( select t.msg_id in_msg_id, CAPTCHA in_captcha
from jp_recvmsg_carcard t
where (t.tail_num = v_Tail_num or (((v_Tail_num is null) or (v_Tail_num = ' '))))
and (t.account_id = v_ACCOUNT_ID) or (((v_ACCOUNT_ID is null) or (v_ACCOUNT_ID = ' ')))
and t.msg_status = 0
order by t.msg_time desc)
where rownum = 1 ; --更新状态
update jp_recvmsg_carcard t
set t.msg_status = 1
where t.msg_id = l_recvmsgid;
commit;
v_result:='';
EXCEPTION WHEN OTHERS THEN
v_Result:='';
ROLLBACK;
end SP_GET_NEWEST_CAPTCHA;
优化后
优化案例2
SQL中的排序是很耗时的，根据测试按照时间排序比按照ID排序更耗时，所以如果可以，将按照时间排序改为按照ID排序；
如果必须按照时间排序，应该在时间列上加上索引！