DELIMITER $$ DROP PROCEDURE IF EXISTS `test_release`.`Sp_JP_A_NotifyBegin`$$ CREATE DEFINER=`encysys48`@`%` PROCEDURE `Sp_JP_A_NotifyBegin`(
OUT v_Result varchar(10),
OUT v_OrderID varchar(500),
OUT v_HFserialid varchar(500),
OUT v_ChargeSerialID varchar(500),
OUT v_ErrorCode varchar(100),
OUT v_FullMoney varchar(200),
OUT v_PlatId varchar(200),
OUT v_NotifyCount varchar(4),
OUT v_PHONERESMONEY varchar(200),
OUT v_AGENTID varchar(500)
)
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN
label2: BEGIN
DECLARE l_hforderid varchar(30);
DECLARE l_hfserialid varchar(30);
DECLARE l_errorcode varchar(10);
DECLARE l_charserialid varchar(50);
DECLARE l_fullmoney decimal(10, 2);
DECLARE l_platid int(10);
DECLARE l_notofynum int(4);
DECLARE l_status int(4);
DECLARE l_increce int(10);
DECLARE l_nowtime timestamp;
DECLARE l_phoneresmoney varchar(30);
DECLARE l_agentid varchar(30);
DECLARE l_CheckCode varchar(100);
DECLARE l_PlatCheckResult varchar(10);
DECLARE l_stop_flag int(2);
DECLARE l_flag int(2);
DECLARE l_cursorEmpty int(2) DEFAULT 0; #游标是否为空标记位，0-为空 ;1-不为空
DECLARE l_cursorOpen int(2) DEFAULT 0; #游标是否打开标记位，0-没打开;1-已打开
DECLARE l_cursorClose int(2) DEFAULT 0; #游标是否关闭标记位，0-未关闭;1-已关闭 #定义游标
DECLARE cur_notify CURSOR FOR
SELECT a.hf_serialid
FROM (SELECT hf_serialid
FROM jp_fullnote s
WHERE charge_status IN (0, 3)
AND (notify_flag = 1 OR (notify_flag = 2 AND DATE_ADD(end_time, INTERVAL 3 MINUTE) < l_nowtime))
AND DATE_ADD(begin_time, INTERVAL 48 HOUR) > l_nowtime
AND DATE_ADD(begin_time, INTERVAL 3 HOUR) < l_nowtime
ORDER BY DATE_ADD(end_time, INTERVAL notify_count MINUTE) ASC
) a
LIMIT 0, 10; #其他异常
DECLARE EXIT HANDLER FOR SQLWARNING, SQLEXCEPTION, NOT FOUND
BEGIN
#先判断是否需要关闭游标：如果游标“已打开且未关闭”，则需要关闭
IF l_cursorOpen = 1 AND l_cursorClose = 0 THEN
CLOSE cur_notify;
END IF; IF l_cursorEmpty = 0 THEN
SET v_OrderID = '-1';
SET v_HFserialid = '-1';
SET v_ErrorCode = '-1';
SET v_FullMoney = '-1';
SET v_PlatId = '-1';
SET v_result = '';
SET v_PHONERESMONEY = '-1';
SET v_AGENTID = '-1';
ROLLBACK;
ELSE
SET v_result = '';
SET v_notifycount = l_increce;
COMMIT;
END IF;
END; #赋初始值
SET v_result = '';
SET v_OrderID = '';
SET v_HFserialid = '';
SET v_ChargeSerialID = '';
SET v_ErrorCode = '';
SET v_FullMoney = '';
SET v_PlatId = '';
SET l_notofynum = 0;
SET l_increce = 0;
SET v_PHONERESMONEY = '';
SET v_AGENTID = '';
SET l_nowtime = now();
SET l_stop_flag = 0;
SET l_flag = 0; #自动流程监控
SELECT CONCAT('ChPlat', t.sys_id, '_A8002_NotifyHF') INTO l_CheckCode
FROM jp_sysplat t;
CALL Sp_MO_PlatAutoCheck(l_CheckCode, '3B', l_PlatCheckResult); #使用游标
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE ''
BEGIN
SET l_stop_flag = 1;
END; #打开游标
OPEN cur_notify;
SET l_cursorOpen = 1; #打开游标后，把游标打开的标记位置为1，表示已打开 FETCH cur_notify INTO l_hfserialid;
#进入游标循环
label1: WHILE l_stop_flag < 1 DO
#每次循环计数
SET l_cursorEmpty = l_cursorEmpty + 1; SELECT charge_status,ERROR_CODE,hf_orderid,hf_serialid,finish_money,
f.senderobj,charge_serialid,PHONE_RESMONEY,AGENT_ID
INTO l_status, l_errorcode, l_hforderid, l_hfserialid, l_fullmoney,
l_platid, l_charserialid, l_phoneresmoney, l_agentid
FROM jp_fullnote f
WHERE hf_serialid = l_hfserialid; IF l_status = 3 THEN
SET l_fullmoney = 0;
END IF; BEGIN
DECLARE EXIT HANDLER FOR SQLWARNING, SQLEXCEPTION, NOT FOUND
SET l_flag = 1; SELECT notify_return_code
INTO l_errorcode
FROM jp_return_code r
WHERE r.error_code = l_errorcode;
END; IF l_flag = 1 THEN
FETCH cur_notify INTO l_hfserialid;
ITERATE label1;
END IF; SET v_OrderID = CONCAT(v_OrderID, l_hforderid, ',');
SET v_HFserialid = CONCAT(v_HFserialid, l_hfserialid, ',');
SET v_ChargeSerialID = CONCAT(v_ChargeSerialID, l_charserialid, ',');
SET v_ErrorCode = CONCAT(v_ErrorCode, l_errorcode, ',');
SET v_FullMoney = CONCAT(v_FullMoney, l_fullmoney, ',');
SET v_PlatId = CONCAT(v_PlatId, l_platid, ','); IF l_phoneresmoney > 10000 THEN
SET v_PHONERESMONEY = CONCAT(v_PHONERESMONEY, '', ',');
ELSE
SET v_PHONERESMONEY = CONCAT(v_PHONERESMONEY, l_phoneresmoney, ',');
END IF; SET v_AGENTID = CONCAT(v_AGENTID, l_agentid, ','); UPDATE jp_fullnote
SET notify_flag = 2,
notify_count = notify_count + 1,
end_time = l_nowtime
WHERE hf_serialid = l_hfserialid; SET l_increce = l_increce + 1;
COMMIT; FETCH cur_notify INTO l_hfserialid;
END WHILE label1;
END; #关闭游标
CLOSE cur_notify;
SET l_cursorClose = 1; #关闭游标后，把游标关闭的标记位置为1，表示已关闭 #判断游标是否为空
IF l_cursorEmpty = 0 THEN
SET v_OrderID = '-1';
SET v_HFserialid = '-1';
SET v_ChargeSerialID = '-1';
SET v_ErrorCode = '-1';
SET v_FullMoney = '-1';
SET v_PlatId = '-1';
SET v_PHONERESMONEY = '-1';
SET v_AGENTID = '-1';
LEAVE label2;
END IF; SET v_NotifyCount = l_increce;
SET v_result = '';
COMMIT;
END;
END$$ DELIMITER ;
优化前
DELIMITER $$ DROP PROCEDURE IF EXISTS `encysys48`.`sp_jp_a_notifyBegin_acc`$$ CREATE DEFINER=`encysys48`@`%` PROCEDURE `sp_jp_a_notifyBegin_acc`(
IN v_num VARCHAR(2), #流水号最后一位除10，所得余数
OUT v_Result varchar(10), #结果码
OUT v_OrderID varchar(500), #订单号
OUT v_HFserialid varchar(500), #话费流水号
OUT v_ChargeSerialID varchar(500), #对端流水
OUT v_ErrorCode varchar(100), #结果码
OUT v_FullMoney varchar(200), #充值金额
OUT v_PlatId varchar(200), #中心平台号
OUT v_NotifyCount varchar(4), #数量
OUT v_PHONERESMONEY varchar(200), #充值后手机余额
OUT v_AGENTID varchar(500)
)
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN
label2: BEGIN
DECLARE l_num INT(2); #流水号最后一位除10，所得余数
DECLARE l_hforderid varchar(30);
DECLARE l_hfserialid varchar(30);
DECLARE l_errorcode varchar(10);
DECLARE l_charserialid varchar(50);
DECLARE l_fullmoney decimal(10, 2);
DECLARE l_platid int(10);
DECLARE l_notofynum int(4);
DECLARE l_status int(4);
DECLARE l_increce int(10);
DECLARE l_nowtime timestamp;
DECLARE l_phoneresmoney varchar(20);
DECLARE l_agentid varchar(30);
DECLARE l_CheckCode varchar(100);
DECLARE l_PlatCheckResult varchar(10);
DECLARE l_stop_flag int(2);
DECLARE l_flag int(2);
DECLARE l_str VARCHAR(50);
DECLARE l_cursorEmpty int(2) DEFAULT 0; #游标是否为空标记位，0-为空 ;1-不为空
DECLARE l_cursorOpen int(2) DEFAULT 0; #游标是否打开标记位，0-没打开;1-已打开
DECLARE l_cursorClose int(2) DEFAULT 0; #游标是否关闭标记位，0-未关闭;1-已关闭
declare l_search_times INT(2) DEFAULT 0; #循环执行游标的次数
DECLARE l_start_time TIMESTAMP; #游标搜索的起始时间
DECLARE l_end_time TIMESTAMP; #游标搜索的结束时间
#定义游标
DECLARE cur_notify CURSOR FOR
SELECT a.hf_serialid
FROM (SELECT hf_serialid
FROM jp_fullnote s
WHERE charge_status IN (0, 3)
AND (notify_flag = 1 OR (notify_flag = 2 AND DATE_ADD(end_time, INTERVAL 3 MINUTE) < l_nowtime))
AND MOD(SUBSTRING(hf_serialid, CHAR_LENGTH(hf_serialid), 1), 10) = l_num
AND begin_time >= l_start_time and begin_time < l_end_time
ORDER BY DATE_ADD(end_time, INTERVAL notify_count MINUTE) ASC
) a
LIMIT 0, 10; #其他异常
DECLARE EXIT HANDLER FOR SQLWARNING, SQLEXCEPTION, NOT FOUND
BEGIN
#先判断是否需要关闭游标：如果游标“已打开且未关闭”，则需要关闭
IF l_cursorOpen = 1 AND l_cursorClose = 0 THEN
CLOSE cur_notify;
END IF; IF l_cursorEmpty = 0 THEN
SET v_OrderID = '-1';
SET v_HFserialid = '-1';
SET v_ErrorCode = '-1';
SET v_FullMoney = '-1';
SET v_PlatId = '-1';
SET v_result = '';
SET v_PHONERESMONEY = '-1';
SET v_AGENTID = '-1';
ROLLBACK;
ELSE
SET v_result = '';
SET v_notifycount = l_increce;
COMMIT;
END IF;
CALL sp_SN_dbwarning(1, 'Sp_JP_A_NotifyBegin_Acc', '', "", NULL, NULL);
END; #赋初始值
SET v_result = '';
SET v_OrderID = '';
SET v_HFserialid = '';
SET v_ChargeSerialID = '';
SET v_ErrorCode = '';
SET v_FullMoney = '';
SET v_PlatId = '';
SET l_notofynum = 0;
SET l_increce = 0;
SET v_PHONERESMONEY = '';
SET v_AGENTID = '';
SET l_nowtime = now();
SET l_stop_flag = 0;
SET l_flag = 0;
SET l_num = v_Num;
#自动流程监控
BEGIN
DECLARE EXIT HANDLER FOR SQLWARNING, SQLEXCEPTION, NOT FOUND SET l_CheckCode = 'unknown_A80??_Charge_General?';
SELECT
CASE v_Num
WHEN 0 THEN '_A8040_Charge_General0'
WHEN 1 THEN '_A8041_Charge_General1'
WHEN 2 THEN '_A8042_Charge_General2'
WHEN 3 THEN '_A8043_Charge_General3'
WHEN 4 THEN '_A8044_Charge_General4'
WHEN 5 THEN '_A8045_Charge_General5'
WHEN 6 THEN '_A8046_Charge_General6'
WHEN 7 THEN '_A8047_Charge_General7'
WHEN 8 THEN '_A8048_Charge_General8'
WHEN 9 THEN '_A8049_Charge_General9' ELSE '_A80??_Charge_General?' END INTO l_str
FROM DUAL;
SELECT CONCAT('ChPlat', t.sys_id, l_str) INTO l_CheckCode
FROM jp_sysplat t;
CALL Sp_MO_PlatAutoCheck(l_CheckCode, '3B', l_PlatCheckResult);
END;
set l_start_time = now();
#使用游标
looplabel: while ((l_increce < 10) and (l_search_times < 18)) do
set l_end_time = l_start_time;
set l_start_time = DATE_ADD(l_end_time, INTERVAL -10 MINUTE);
set l_stop_flag = 0;
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE ''
BEGIN
SET l_stop_flag = 1;
END;
#打开游标
OPEN cur_notify;
SET l_cursorOpen = 1; #打开游标后，把游标打开的标记位置为1，表示已打开 FETCH cur_notify INTO l_hfserialid;
INSERT INTO w_help(char_content) VALUES(l_increce);
#进入游标循环
label1: WHILE l_stop_flag < 1 and l_increce < 10 DO
#每次循环计数
SET l_cursorEmpty = l_cursorEmpty + 1; SELECT charge_status,ERROR_CODE,hf_orderid,hf_serialid,finish_money,
senderobj,charge_serialid,PHONE_RESMONEY,AGENT_ID
INTO l_status, l_errorcode, l_hforderid, l_hfserialid, l_fullmoney,
l_platid, l_charserialid, l_phoneresmoney, l_agentid
FROM jp_fullnote f
WHERE hf_serialid = l_hfserialid; #判断订单是否是失败
IF l_status = 3 THEN
SET l_fullmoney = 0;
END IF; #获取错误码
BEGIN
DECLARE EXIT HANDLER FOR SQLWARNING, SQLEXCEPTION, NOT FOUND
SET l_flag = 1; SELECT notify_return_code
INTO l_errorcode
FROM jp_return_code r
WHERE r.error_code = l_errorcode;
END; IF l_flag = 1 THEN
FETCH cur_notify INTO l_hfserialid;
ITERATE label1;
END IF; #拼接参数
SET v_OrderID = CONCAT(v_OrderID, l_hforderid, ',');
SET v_HFserialid = CONCAT(v_HFserialid, l_hfserialid, ',');
SET v_ChargeSerialID = CONCAT(v_ChargeSerialID, l_charserialid, ',');
SET v_ErrorCode = CONCAT(v_ErrorCode, l_errorcode, ',');
SET v_FullMoney = CONCAT(v_FullMoney, l_fullmoney, ',');
SET v_PlatId = CONCAT(v_PlatId, l_platid, ','); IF l_phoneresmoney > 10000 THEN
SET v_PHONERESMONEY = CONCAT(v_PHONERESMONEY, '', ',');
ELSE
SET v_PHONERESMONEY = CONCAT(v_PHONERESMONEY, l_phoneresmoney, ',');
END IF; SET v_AGENTID = CONCAT(v_AGENTID, l_agentid, ','); UPDATE jp_fullnote
SET notify_flag = 2,
notify_count = notify_count + 1,
end_time = l_nowtime
WHERE hf_serialid = l_hfserialid; SET l_increce = l_increce + 1;
INSERT INTO w_help(char_content) VALUES(l_increce);
COMMIT; #进入下一次循环
FETCH cur_notify INTO l_hfserialid;
END WHILE label1;
END;
#关闭游标
CLOSE cur_notify;
SET l_cursorClose = 1; #关闭游标后，把游标关闭的标记位置为1，表示已关闭
set l_search_times = l_search_times + 1;
INSERT INTO w_help(char_content) VALUES(l_search_times);
END WHILE looplabel;
#判断游标是否为空
IF l_cursorEmpty = 0 THEN
SET v_OrderID = '-1';
SET v_HFserialid = '-1';
SET v_ChargeSerialID = '-1';
SET v_ErrorCode = '-1';
SET v_FullMoney = '-1';
SET v_PlatId = '-1';
SET v_PHONERESMONEY = '-1';
SET v_AGENTID = '-1';
LEAVE label2;
END IF; SET v_NotifyCount = l_increce;
insert into w_help(char_content) values(l_increce);
SET v_result = '';
COMMIT;
END;
END$$ DELIMITER ;
优化后
优化的核心是：
优化前，搜索三小时以内的满足条件的订单，十条；
优化后，搜索十分钟以内的，满足条件的，搜索结果数目大于10，返回10条搜素结果；
搜索结果数目小于10，继续搜索10~20分钟之间的，总共之和大于10，返回结果；
总共结果小于10，继续搜索20~30分钟之间的。
这样，即使搜索三小时以内的，速度也明显的快。