为什么这句
DECLARE CONTINUE HANDLER FOR SQLSTATE '1065' 在程序中会出错:Error Code : 1064,是个捕获异常(查询数据为空时的异常)的语句,
剪切掉该句则执行通过,为什么呢?望各个路人指点下,谢谢!DELIMITER $$DROP PROCEDURE IF EXISTS `tt`.`simpleproc`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `simpleproc`(in p_code varchar(10),
out p_content varchar(4000),out p_error varchar(400),in p_phone varchar(11))
BEGIN
DECLARE g_code varchar(400);
DECLARE g_verify varchar(4);
DECLARE g_ask_flag1 varchar(4);
DECLARE g_ask_flag2 varchar(4);
#g_answer_flag varchar(4);
DECLARE g_ask varchar(400);
#g_answer varchar(400);
DECLARE g_rows numeric;
DECLARE g_enterprise_code numeric;
DECLARE g_type numeric(1);
DECLARE g_userid numeric;
DECLARE g_content varchar(4000);
DECLARE g_shortmsg varchar(4000);
DECLARE g_lb numeric(10);
DECLARE g_dm numeric(10);
DECLARE g_error numeric DEFAULT 0; #默认为0
#短信规则:8019-66-1987:8019为SP提供本企业号+系统提供企业号+短信代号,-为分隔符号
set g_code = substr(p_code, 3);#661987
#短信问答规则:9问题到106xx;回去答案:99+验证码到106xx
set g_ask_flag1 = substr(p_code, 1, 1);
set g_ask_flag2 = substr(p_code, 2, 1);
set g_ask = substr(p_code, 2);
#g_answer_flag = substr(p_code, 2, 2);
set g_verify = 1234; #trunc(dbms_random.value(1000, 9999));
#------------发送问题开始--------------------
if g_ask_flag1 = '9' and g_ask_flag2<>'9' then
#----------发送代码开始-----------------------
set g_enterprise_code = substr(p_code, 1, 2);
select count(*) into g_rows from rms_enterprise
where code = g_enterprise_code;
#-------------企业是否存在开始----------------
if g_rows >= 1 then
#---------股评(涉及到点播)开始----------------------
if length(g_code) = 6 then
#6位的是点播的股评
set g_type = 1;
begin
#首先在企业表查找内容
select content, user_id into p_content, g_userid
from RMS_GUPING_CONTENT_ENTERPRISE
where id = (select max(id) from RMS_GUPING_CONTENT_ENTERPRISE
where code = g_code
and enterprise_id =
(select id
from rms_enterprise
where code = g_enterprise_code));
#企业表没的情况找总表
DECLARE CONTINUE HANDLER FOR SQLSTATE '1065'
begin
select content, user_id into p_content, g_userid
from rms_guping_content_all
where id = (select max(id) from rms_guping_content_all
where code = g_code);
end;
end;
else
set g_type = 2;
set g_error = 1;
end if;
#-----------股评(涉及到点播)结束----------------------------------------
end if;
#-------------企业是否存在结束---------------
set p_error = g_error;
if g_error > 0 then
select error_content
into p_error
from rms_error
where error_no = g_error;
end if;
end if;
#----------发送代码结束-------------------------
END$$DELIMITER ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '1065' 在程序中会出错:Error Code : 1064,是个捕获异常(查询数据为空时的异常)的语句,
剪切掉该句则执行通过,为什么呢?望各个路人指点下,谢谢!DELIMITER $$DROP PROCEDURE IF EXISTS `tt`.`simpleproc`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `simpleproc`(in p_code varchar(10),
out p_content varchar(4000),out p_error varchar(400),in p_phone varchar(11))
BEGIN
DECLARE g_code varchar(400);
DECLARE g_verify varchar(4);
DECLARE g_ask_flag1 varchar(4);
DECLARE g_ask_flag2 varchar(4);
#g_answer_flag varchar(4);
DECLARE g_ask varchar(400);
#g_answer varchar(400);
DECLARE g_rows numeric;
DECLARE g_enterprise_code numeric;
DECLARE g_type numeric(1);
DECLARE g_userid numeric;
DECLARE g_content varchar(4000);
DECLARE g_shortmsg varchar(4000);
DECLARE g_lb numeric(10);
DECLARE g_dm numeric(10);
DECLARE g_error numeric DEFAULT 0; #默认为0
#短信规则:8019-66-1987:8019为SP提供本企业号+系统提供企业号+短信代号,-为分隔符号
set g_code = substr(p_code, 3);#661987
#短信问答规则:9问题到106xx;回去答案:99+验证码到106xx
set g_ask_flag1 = substr(p_code, 1, 1);
set g_ask_flag2 = substr(p_code, 2, 1);
set g_ask = substr(p_code, 2);
#g_answer_flag = substr(p_code, 2, 2);
set g_verify = 1234; #trunc(dbms_random.value(1000, 9999));
#------------发送问题开始--------------------
if g_ask_flag1 = '9' and g_ask_flag2<>'9' then
#----------发送代码开始-----------------------
set g_enterprise_code = substr(p_code, 1, 2);
select count(*) into g_rows from rms_enterprise
where code = g_enterprise_code;
#-------------企业是否存在开始----------------
if g_rows >= 1 then
#---------股评(涉及到点播)开始----------------------
if length(g_code) = 6 then
#6位的是点播的股评
set g_type = 1;
begin
#首先在企业表查找内容
select content, user_id into p_content, g_userid
from RMS_GUPING_CONTENT_ENTERPRISE
where id = (select max(id) from RMS_GUPING_CONTENT_ENTERPRISE
where code = g_code
and enterprise_id =
(select id
from rms_enterprise
where code = g_enterprise_code));
#企业表没的情况找总表
DECLARE CONTINUE HANDLER FOR SQLSTATE '1065'
begin
select content, user_id into p_content, g_userid
from rms_guping_content_all
where id = (select max(id) from rms_guping_content_all
where code = g_code);
end;
end;
else
set g_type = 2;
set g_error = 1;
end if;
#-----------股评(涉及到点播)结束----------------------------------------
end if;
#-------------企业是否存在结束---------------
set p_error = g_error;
if g_error > 0 then
select error_content
into p_error
from rms_error
where error_no = g_error;
end if;
end if;
#----------发送代码结束-------------------------
END$$DELIMITER ;
终于熬到现在弄好了:
申明变量处:DECLARE error_flag numeric DEFAULT 0; #默认为0捕获异常处:DECLARE CONTINUE HANDLER FOR SQLSTATE '1065'
换成
declare continue handler for not found set error_flag=1;
在需要捕获异常的地方使用判断语句:根据error_flag是否为1,执行完后再把error_flag置为0,需要的地方可以嵌套很多次,注意每次需要置0,这个很关键,以免影响后面的执行。
谢谢,各位路人,散分。
夜深了,快亮了。