BEGIN
#每天执行一次,统计前一天的数据
DECLARE v_if_code varchar(20);
DECLARE v_mt_sum bigint DEFAULT 0;/*存储发送短信数量*/
DECLARE v_mo_sum bigint DEFAULT 0;
DECLARE v_rpt_sum bigint DEFAULT 0;
DECLARE v_filter_sum bigint DEFAULT 0;
DECLARE sum_time_start datetime;
DECLARE sum_time_end datetime;
DECLARE sum_date date;
DECLARE done INT DEFAULT 0;#定义全局变量统计三个运营商的发送数量、成功数量、失败数量
DECLARE china_mobile_mt_qty BIGINT DEFAULT 0;
DECLARE china_unicom_mt_qty BIGINT DEFAULT 0;
DECLARE china_telecom_mt_qty BIGINT DEFAULT 0;DECLARE china_mobile_success_qty BIGINT DEFAULT 0;
DECLARE china_unicom_success_qty BIGINT DEFAULT 0;
DECLARE china_telecom_success_qty BIGINT DEFAULT 0;DECLARE china_mobile_fail_qty BIGINT DEFAULT 0;
DECLARE china_unicom_fail_qty BIGINT DEFAULT 0;
DECLARE china_telecom_fail_qty BIGINT DEFAULT 0;DECLARE mobile_operators_fail_percent float(4,2);
DECLARE china_mobile_fail_percent float(4,2);
DECLARE china_unicom_fail_percent float(4,2);
DECLARE china_telecom_fail_percent float(4,2);#定义三个运营商的号码段正则表达式
DECLARE china_mobile_regexpress VARCHAR(200);
DECLARE china_unicom_regexpress VARCHAR(200);
DECLARE china_telecom_regexpress VARCHAR(200);/*遍历所有tbl_api_info中的接口*/
DECLARE ScannerCursor CURSOR FOR SELECT if_code FROM tbl_api_info WHERE IF_CODE<>'smsystem';
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
set sum_date = Date(DATE_SUB(NOW(), INTERVAL 1 DAY));
set sum_time_start = CONCAT(sum_date,' 00:00:00');
set sum_time_end = CONCAT(sum_date,' 23:59:59');#预处理,删除移动运营商该时段的统计数据
DELETE FROM tbl_mobile_operators_sm_sum WHERE date(sum_time)=date(sum_time_start);#获取三个运营商的号码段正则表达式
SELECT regular_expression INTO china_mobile_regexpress from tbl_mobile_operators where operator_name='中国移动';
SELECT regular_expression INTO china_unicom_regexpress from tbl_mobile_operators where operator_name='中国联通';
SELECT regular_expression INTO china_telecom_regexpress from tbl_mobile_operators where operator_name='中国电信';OPEN ScannerCursor;
REPEAT FETCH ScannerCursor INTO v_if_code;
IF NOT done THEN
BEGIN
/*对于每一个if_code,遍历四个表:tbl_mt_detail,tbl_mo_detail,tbl_rpt_detail,tbl_filter_sm*/
/*开始统计tbl_mt_detail*/
DECLARE sm_mobiles text;
DECLARE sm_content text;
DECLARE mt_qty bigint DEFAULT 0;
DECLARE mt_qty_1 bigint DEFAULT 0;
DECLARE mo_qty bigint DEFAULT 0;
DECLARE v_fail_qty bigint DEFAULT 0;
DECLARE v_china_mobile_fail_qty BIGINT DEFAULT 0;
DECLARE v_china_unicom_fail_qty BIGINT DEFAULT 0;
DECLARE v_china_telecom_fail_qty BIGINT DEFAULT 0; DECLARE sm_chinamobile_mobiles_qty BIGINT DEFAULT 0;
DECLARE sm_chinaunicom_mobiles_qty BIGINT DEFAULT 0;
DECLARE sm_chinatelecom_mobiles_qty BIGINT DEFAULT 0; DECLARE filter_mobiles text;
DECLARE v_filter_qty bigint DEFAULT 0;
DECLARE filter_done INT DEFAULT 0; DECLARE maxCnt INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DROP TABLE IF EXISTS Gather_Data_Tmp_1day;
CREATE TEMPORARY TABLE Gather_Data_Tmp_1day(
`Tmp_Id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`Mobiles` TEXT NOT NULL,
`Content` TEXT ,
PRIMARY KEY (`Tmp_Id`)
)ENGINE=MyISAM DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS Mobiles_Data_Tmp_1day;
CREATE TEMPORARY TABLE Mobiles_Data_Tmp_1day(
`Tmp_Id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`Mobile` TEXT NOT NULL,
PRIMARY KEY (`Tmp_Id`)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET @tSql = CONCAT('INSERT INTO Gather_Data_Tmp_1day (`Mobiles`, `Content`)
SELECT mt.mobiles,mt.content
FROM tbl_mt_detail mt
WHERE mt.if_code = ''',v_if_code,
''' AND mt.handle_time >= ''',sum_time_start,
''' AND mt.handle_time <= ''',sum_time_end,
''' AND (length(mobiles)>11 or char_length(content)>70)');
PREPARE gatherData FROM @tSql;
EXECUTE gatherData;
SELECT MIN(`Tmp_Id`) INTO i FROM Gather_Data_Tmp_1day;
SELECT MAX(`Tmp_Id`) INTO maxCnt FROM Gather_Data_Tmp_1day;
IF ISNULL(i) THEN
SET i = 0;
END IF; IF ISNULL(maxCnt) THEN
SET maxCnt = 0;
END IF; IF(maxCnt>0) THEN
BEGIN
WHILE i <= maxCnt DO
BEGIN
DECLARE sm_mobiles_sql text;
DECLARE sm_content_qty int;
DECLARE sm_content_length int;
DECLARE sm_mobiles_qty int;
SELECT Mobiles, Content INTO sm_mobiles, sm_content FROM Gather_Data_Tmp_1day WHERE Tmp_Id = i;
/*获取号码的个数*/
set sm_mobiles = REPLACE(sm_mobiles,',',',');/*将中文逗号替换为英文逗号*/
set @sm_mobiles_sql = CONCAT("insert Mobiles_Data_Tmp_1day(Mobile) values('",REPLACE(sm_mobiles,',',"'),('"),"')");/*将逗号分隔的号码变成一列放到一个临时表中,*/
delete from Mobiles_Data_Tmp_1day;
PREPARE statement_sql from @sm_mobiles_sql;
EXECUTE statement_sql;
SELECT count(Mobile) into sm_mobiles_qty from Mobiles_Data_Tmp_1day ; SELECT count(Mobile) into sm_chinamobile_mobiles_qty from Mobiles_Data_Tmp_1day
where Mobile REGEXP china_mobile_regexpress;
SELECT count(Mobile) into sm_chinaunicom_mobiles_qty from Mobiles_Data_Tmp_1day
where Mobile REGEXP china_unicom_regexpress;
SELECT count(Mobile) into sm_chinatelecom_mobiles_qty from Mobiles_Data_Tmp_1day
where Mobile REGEXP china_telecom_regexpress;
/*获取短信内容,查看是否需要分拆为多条短信*/
set sm_content_length = CHAR_LENGTH(sm_content);
set sm_content_qty = CEILING(sm_content_length/70 );/*每条短信最多70个汉字*/ set mt_qty = mt_qty + (sm_content_qty * sm_mobiles_qty); set china_mobile_mt_qty = china_mobile_mt_qty + (sm_content_qty * sm_chinamobile_mobiles_qty);
set china_unicom_mt_qty = china_unicom_mt_qty + (sm_content_qty * sm_chinaunicom_mobiles_qty);
set china_telecom_mt_qty = china_telecom_mt_qty + (sm_content_qty * sm_chinatelecom_mobiles_qty);
SET i = i + 1;
END;
END WHILE;
END;
END IF;
/*将mt_qty记录到统计表中*/
IF ISNULL(mt_qty) THEN
SET mt_qty = 0;
END IF; #处理号码为一个和内容长度没有超过标准的短信记录
SELECT count(*) into mt_qty_1 from tbl_mt_detail
where if_code=v_if_code
and handle_time>=sum_time_start and handle_time <=sum_time_end
and LENGTH(mobiles)<=11
and CHAR_LENGTH(content)<=70; IF ISNULL(mt_qty_1) THEN
SET mt_qty_1 = 0;
END IF; # UPDATE tbl_sm_sum smsum set smsum.SEND_QTY=mt_qty + mt_qty_1
# where smsum.if_code=v_if_code and date(smsum.sum_time)=date(sum_time_start); SELECT count(*) into sm_chinamobile_mobiles_qty from tbl_mt_detail
where Mobiles REGEXP china_mobile_regexpress
AND if_code=v_if_code
and handle_time>=sum_time_start and handle_time <=sum_time_end
and LENGTH(mobiles)<=11
and CHAR_LENGTH(content)<=70; SELECT count(*) into sm_chinaunicom_mobiles_qty from tbl_mt_detail
where Mobiles REGEXP china_unicom_regexpress
AND if_code=v_if_code
and handle_time>=sum_time_start and handle_time <=sum_time_end
and LENGTH(mobiles)<=11
and CHAR_LENGTH(content)<=70; SELECT count(*) into sm_chinatelecom_mobiles_qty from tbl_mt_detail
where Mobiles REGEXP china_telecom_regexpress
AND if_code=v_if_code
and handle_time>=sum_time_start and handle_time <=sum_time_end
and LENGTH(mobiles)<=11
and CHAR_LENGTH(content)<=70; set china_mobile_mt_qty = china_mobile_mt_qty + sm_chinamobile_mobiles_qty;
set china_unicom_mt_qty = china_unicom_mt_qty + sm_chinaunicom_mobiles_qty;
set china_telecom_mt_qty = china_telecom_mt_qty + sm_chinatelecom_mobiles_qty;
/*tbl_mt_detail 统计完毕*/
/*开始统计tbl_mo_detail*/
SELECT count(auto_sn) into mo_qty from tbl_mo_detail
where if_code=v_if_code and mo_time>=sum_time_start and mo_time <=sum_time_end;
/*将mo_qty记录到统计表中*/
IF ISNULL(mo_qty) THEN
SET mo_qty = 0;
END IF;
#UPDATE tbl_sm_sum smsum set smsum.REV_QTY=mo_qty
#where smsum.if_code=v_if_code and date(smsum.sum_time)=date(sum_time_start);
/*tbl_mo_detail 统计完毕*/ /*开始根据tbl_rpt_detail,统计发送失败短信数量*/
SELECT count(auto_sn) into v_fail_qty from tbl_rpt_detail where if_code=v_if_code
and rpt_time>=sum_time_start and rpt_time <=sum_time_end and rpt_code<>0; SELECT count(auto_sn) into v_china_mobile_fail_qty from tbl_rpt_detail where if_code=v_if_code
and rpt_time>=sum_time_start and rpt_time <=sum_time_end and rpt_code<>0 and MOBILE
REGEXP china_mobile_regexpress; SELECT count(auto_sn) into v_china_unicom_fail_qty from tbl_rpt_detail where if_code=v_if_code
and rpt_time>=sum_time_start and rpt_time <=sum_time_end and rpt_code<>0 and mobile
REGEXP china_unicom_regexpress; SELECT count(auto_sn) into v_china_telecom_fail_qty from tbl_rpt_detail where if_code=v_if_code
and rpt_time>=sum_time_start and rpt_time <=sum_time_end and rpt_code<>0 and mobile
REGEXP china_telecom_regexpress;
/*将fail_qty记录到统计表中*/
IF ISNULL(v_fail_qty) THEN
SET v_fail_qty = 0;
END IF;
IF ISNULL(v_china_mobile_fail_qty) THEN
SET v_china_mobile_fail_qty = 0;
END IF;
IF ISNULL(v_china_unicom_fail_qty) THEN
SET v_china_unicom_fail_qty = 0;
END IF;
IF ISNULL(v_china_telecom_fail_qty) THEN
SET v_china_telecom_fail_qty = 0;
END IF; #UPDATE tbl_sm_sum smsum set smsum.FAIL_QTY=v_fail_qty where smsum.if_code=v_if_code
#and date(smsum.sum_time)=date(sum_time_start);
set china_mobile_fail_qty = china_mobile_fail_qty + v_china_mobile_fail_qty;
set china_unicom_fail_qty = china_unicom_fail_qty + v_china_unicom_fail_qty;
set china_telecom_fail_qty = china_telecom_fail_qty + v_china_telecom_fail_qty;
/*tbl_rpt_detail 统计完毕*/
/*发送成功数量,即将发送总数量-发送失败数量*/
#UPDATE tbl_sm_sum smsum set smsum.SUCCESS_QTY=mt_qty+mt_qty_1-v_fail_qty
#where smsum.if_code=v_if_code
#and date(smsum.sum_time)=date(sum_time_start);
oracle存储mysql全局变量正则表达式
#每天执行一次,统计前一天的数据
DECLARE v_if_code varchar(20);
DECLARE v_mt_sum bigint DEFAULT 0;/*存储发送短信数量*/
DECLARE v_mo_sum bigint DEFAULT 0;
DECLARE v_rpt_sum bigint DEFAULT 0;
DECLARE v_filter_sum bigint DEFAULT 0;
DECLARE sum_time_start datetime;
DECLARE sum_time_end datetime;
DECLARE sum_date date;
DECLARE done INT DEFAULT 0;#定义全局变量统计三个运营商的发送数量、成功数量、失败数量
DECLARE china_mobile_mt_qty BIGINT DEFAULT 0;
DECLARE china_unicom_mt_qty BIGINT DEFAULT 0;
DECLARE china_telecom_mt_qty BIGINT DEFAULT 0;DECLARE china_mobile_success_qty BIGINT DEFAULT 0;
DECLARE china_unicom_success_qty BIGINT DEFAULT 0;
DECLARE china_telecom_success_qty BIGINT DEFAULT 0;DECLARE china_mobile_fail_qty BIGINT DEFAULT 0;
DECLARE china_unicom_fail_qty BIGINT DEFAULT 0;
DECLARE china_telecom_fail_qty BIGINT DEFAULT 0;DECLARE mobile_operators_fail_percent float(4,2);
DECLARE china_mobile_fail_percent float(4,2);
DECLARE china_unicom_fail_percent float(4,2);
DECLARE china_telecom_fail_percent float(4,2);#定义三个运营商的号码段正则表达式
DECLARE china_mobile_regexpress VARCHAR(200);
DECLARE china_unicom_regexpress VARCHAR(200);
DECLARE china_telecom_regexpress VARCHAR(200);/*遍历所有tbl_api_info中的接口*/
DECLARE ScannerCursor CURSOR FOR SELECT if_code FROM tbl_api_info WHERE IF_CODE<>'smsystem';
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
set sum_date = Date(DATE_SUB(NOW(), INTERVAL 1 DAY));
set sum_time_start = CONCAT(sum_date,' 00:00:00');
set sum_time_end = CONCAT(sum_date,' 23:59:59');#预处理,删除移动运营商该时段的统计数据
DELETE FROM tbl_mobile_operators_sm_sum WHERE date(sum_time)=date(sum_time_start);#获取三个运营商的号码段正则表达式
SELECT regular_expression INTO china_mobile_regexpress from tbl_mobile_operators where operator_name='中国移动';
SELECT regular_expression INTO china_unicom_regexpress from tbl_mobile_operators where operator_name='中国联通';
SELECT regular_expression INTO china_telecom_regexpress from tbl_mobile_operators where operator_name='中国电信';OPEN ScannerCursor;
REPEAT FETCH ScannerCursor INTO v_if_code;
IF NOT done THEN
BEGIN
/*对于每一个if_code,遍历四个表:tbl_mt_detail,tbl_mo_detail,tbl_rpt_detail,tbl_filter_sm*/
/*开始统计tbl_mt_detail*/
DECLARE sm_mobiles text;
DECLARE sm_content text;
DECLARE mt_qty bigint DEFAULT 0;
DECLARE mt_qty_1 bigint DEFAULT 0;
DECLARE mo_qty bigint DEFAULT 0;
DECLARE v_fail_qty bigint DEFAULT 0;
DECLARE v_china_mobile_fail_qty BIGINT DEFAULT 0;
DECLARE v_china_unicom_fail_qty BIGINT DEFAULT 0;
DECLARE v_china_telecom_fail_qty BIGINT DEFAULT 0; DECLARE sm_chinamobile_mobiles_qty BIGINT DEFAULT 0;
DECLARE sm_chinaunicom_mobiles_qty BIGINT DEFAULT 0;
DECLARE sm_chinatelecom_mobiles_qty BIGINT DEFAULT 0; DECLARE filter_mobiles text;
DECLARE v_filter_qty bigint DEFAULT 0;
DECLARE filter_done INT DEFAULT 0; DECLARE maxCnt INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DROP TABLE IF EXISTS Gather_Data_Tmp_1day;
CREATE TEMPORARY TABLE Gather_Data_Tmp_1day(
`Tmp_Id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`Mobiles` TEXT NOT NULL,
`Content` TEXT ,
PRIMARY KEY (`Tmp_Id`)
)ENGINE=MyISAM DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS Mobiles_Data_Tmp_1day;
CREATE TEMPORARY TABLE Mobiles_Data_Tmp_1day(
`Tmp_Id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`Mobile` TEXT NOT NULL,
PRIMARY KEY (`Tmp_Id`)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET @tSql = CONCAT('INSERT INTO Gather_Data_Tmp_1day (`Mobiles`, `Content`)
SELECT mt.mobiles,mt.content
FROM tbl_mt_detail mt
WHERE mt.if_code = ''',v_if_code,
''' AND mt.handle_time >= ''',sum_time_start,
''' AND mt.handle_time <= ''',sum_time_end,
''' AND (length(mobiles)>11 or char_length(content)>70)');
PREPARE gatherData FROM @tSql;
EXECUTE gatherData;
SELECT MIN(`Tmp_Id`) INTO i FROM Gather_Data_Tmp_1day;
SELECT MAX(`Tmp_Id`) INTO maxCnt FROM Gather_Data_Tmp_1day;
IF ISNULL(i) THEN
SET i = 0;
END IF; IF ISNULL(maxCnt) THEN
SET maxCnt = 0;
END IF; IF(maxCnt>0) THEN
BEGIN
WHILE i <= maxCnt DO
BEGIN
DECLARE sm_mobiles_sql text;
DECLARE sm_content_qty int;
DECLARE sm_content_length int;
DECLARE sm_mobiles_qty int;
SELECT Mobiles, Content INTO sm_mobiles, sm_content FROM Gather_Data_Tmp_1day WHERE Tmp_Id = i;
/*获取号码的个数*/
set sm_mobiles = REPLACE(sm_mobiles,',',',');/*将中文逗号替换为英文逗号*/
set @sm_mobiles_sql = CONCAT("insert Mobiles_Data_Tmp_1day(Mobile) values('",REPLACE(sm_mobiles,',',"'),('"),"')");/*将逗号分隔的号码变成一列放到一个临时表中,*/
delete from Mobiles_Data_Tmp_1day;
PREPARE statement_sql from @sm_mobiles_sql;
EXECUTE statement_sql;
SELECT count(Mobile) into sm_mobiles_qty from Mobiles_Data_Tmp_1day ; SELECT count(Mobile) into sm_chinamobile_mobiles_qty from Mobiles_Data_Tmp_1day
where Mobile REGEXP china_mobile_regexpress;
SELECT count(Mobile) into sm_chinaunicom_mobiles_qty from Mobiles_Data_Tmp_1day
where Mobile REGEXP china_unicom_regexpress;
SELECT count(Mobile) into sm_chinatelecom_mobiles_qty from Mobiles_Data_Tmp_1day
where Mobile REGEXP china_telecom_regexpress;
/*获取短信内容,查看是否需要分拆为多条短信*/
set sm_content_length = CHAR_LENGTH(sm_content);
set sm_content_qty = CEILING(sm_content_length/70 );/*每条短信最多70个汉字*/ set mt_qty = mt_qty + (sm_content_qty * sm_mobiles_qty); set china_mobile_mt_qty = china_mobile_mt_qty + (sm_content_qty * sm_chinamobile_mobiles_qty);
set china_unicom_mt_qty = china_unicom_mt_qty + (sm_content_qty * sm_chinaunicom_mobiles_qty);
set china_telecom_mt_qty = china_telecom_mt_qty + (sm_content_qty * sm_chinatelecom_mobiles_qty);
SET i = i + 1;
END;
END WHILE;
END;
END IF;
/*将mt_qty记录到统计表中*/
IF ISNULL(mt_qty) THEN
SET mt_qty = 0;
END IF; #处理号码为一个和内容长度没有超过标准的短信记录
SELECT count(*) into mt_qty_1 from tbl_mt_detail
where if_code=v_if_code
and handle_time>=sum_time_start and handle_time <=sum_time_end
and LENGTH(mobiles)<=11
and CHAR_LENGTH(content)<=70; IF ISNULL(mt_qty_1) THEN
SET mt_qty_1 = 0;
END IF; # UPDATE tbl_sm_sum smsum set smsum.SEND_QTY=mt_qty + mt_qty_1
# where smsum.if_code=v_if_code and date(smsum.sum_time)=date(sum_time_start); SELECT count(*) into sm_chinamobile_mobiles_qty from tbl_mt_detail
where Mobiles REGEXP china_mobile_regexpress
AND if_code=v_if_code
and handle_time>=sum_time_start and handle_time <=sum_time_end
and LENGTH(mobiles)<=11
and CHAR_LENGTH(content)<=70; SELECT count(*) into sm_chinaunicom_mobiles_qty from tbl_mt_detail
where Mobiles REGEXP china_unicom_regexpress
AND if_code=v_if_code
and handle_time>=sum_time_start and handle_time <=sum_time_end
and LENGTH(mobiles)<=11
and CHAR_LENGTH(content)<=70; SELECT count(*) into sm_chinatelecom_mobiles_qty from tbl_mt_detail
where Mobiles REGEXP china_telecom_regexpress
AND if_code=v_if_code
and handle_time>=sum_time_start and handle_time <=sum_time_end
and LENGTH(mobiles)<=11
and CHAR_LENGTH(content)<=70; set china_mobile_mt_qty = china_mobile_mt_qty + sm_chinamobile_mobiles_qty;
set china_unicom_mt_qty = china_unicom_mt_qty + sm_chinaunicom_mobiles_qty;
set china_telecom_mt_qty = china_telecom_mt_qty + sm_chinatelecom_mobiles_qty;
/*tbl_mt_detail 统计完毕*/
/*开始统计tbl_mo_detail*/
SELECT count(auto_sn) into mo_qty from tbl_mo_detail
where if_code=v_if_code and mo_time>=sum_time_start and mo_time <=sum_time_end;
/*将mo_qty记录到统计表中*/
IF ISNULL(mo_qty) THEN
SET mo_qty = 0;
END IF;
#UPDATE tbl_sm_sum smsum set smsum.REV_QTY=mo_qty
#where smsum.if_code=v_if_code and date(smsum.sum_time)=date(sum_time_start);
/*tbl_mo_detail 统计完毕*/ /*开始根据tbl_rpt_detail,统计发送失败短信数量*/
SELECT count(auto_sn) into v_fail_qty from tbl_rpt_detail where if_code=v_if_code
and rpt_time>=sum_time_start and rpt_time <=sum_time_end and rpt_code<>0; SELECT count(auto_sn) into v_china_mobile_fail_qty from tbl_rpt_detail where if_code=v_if_code
and rpt_time>=sum_time_start and rpt_time <=sum_time_end and rpt_code<>0 and MOBILE
REGEXP china_mobile_regexpress; SELECT count(auto_sn) into v_china_unicom_fail_qty from tbl_rpt_detail where if_code=v_if_code
and rpt_time>=sum_time_start and rpt_time <=sum_time_end and rpt_code<>0 and mobile
REGEXP china_unicom_regexpress; SELECT count(auto_sn) into v_china_telecom_fail_qty from tbl_rpt_detail where if_code=v_if_code
and rpt_time>=sum_time_start and rpt_time <=sum_time_end and rpt_code<>0 and mobile
REGEXP china_telecom_regexpress;
/*将fail_qty记录到统计表中*/
IF ISNULL(v_fail_qty) THEN
SET v_fail_qty = 0;
END IF;
IF ISNULL(v_china_mobile_fail_qty) THEN
SET v_china_mobile_fail_qty = 0;
END IF;
IF ISNULL(v_china_unicom_fail_qty) THEN
SET v_china_unicom_fail_qty = 0;
END IF;
IF ISNULL(v_china_telecom_fail_qty) THEN
SET v_china_telecom_fail_qty = 0;
END IF; #UPDATE tbl_sm_sum smsum set smsum.FAIL_QTY=v_fail_qty where smsum.if_code=v_if_code
#and date(smsum.sum_time)=date(sum_time_start);
set china_mobile_fail_qty = china_mobile_fail_qty + v_china_mobile_fail_qty;
set china_unicom_fail_qty = china_unicom_fail_qty + v_china_unicom_fail_qty;
set china_telecom_fail_qty = china_telecom_fail_qty + v_china_telecom_fail_qty;
/*tbl_rpt_detail 统计完毕*/
/*发送成功数量,即将发送总数量-发送失败数量*/
#UPDATE tbl_sm_sum smsum set smsum.SUCCESS_QTY=mt_qty+mt_qty_1-v_fail_qty
#where smsum.if_code=v_if_code
#and date(smsum.sum_time)=date(sum_time_start);
oracle存储mysql全局变量正则表达式
SELECT count(*) into v_filter_qty from tbl_filter_sm
where if_code=v_if_code and handle_time>=sum_time_start and handle_time <=sum_time_end;
IF ISNULL(v_filter_qty) THEN
SET v_filter_qty = 0;
END IF;
#UPDATE tbl_sm_sum smsum set smsum.FILTER_QTY=v_filter_qty
#where smsum.if_code=v_if_code and date(smsum.sum_time)=DATE(sum_time_start); /*插入一条统计记录*/
DELETE FROM tbl_sm_sum WHERE if_code=v_if_code and date(sum_time)=DATE(sum_time_start);
INSERT tbl_sm_sum(if_code,send_qty,success_qty,fail_qty,rev_qty,filter_qty,sum_time)
values(v_if_code,mt_qty+mt_qty_1,mt_qty+mt_qty_1-v_fail_qty,v_fail_qty,mo_qty,v_filter_qty,DATE(sum_time_start));
END;
END IF;
UNTIL done END REPEAT;
CLOSE ScannerCursor;
#新增统计数据到运营商统计表
INSERT tbl_mobile_operators_sm_sum(operator_name,mt_qty,success_qty,fail_qty,sum_time)
values('中国移动',china_mobile_mt_qty,china_mobile_mt_qty-china_mobile_fail_qty,china_mobile_fail_qty,date(sum_time_start));
INSERT tbl_mobile_operators_sm_sum(operator_name,mt_qty,success_qty,fail_qty,sum_time)
values('中国联通',china_unicom_mt_qty,china_unicom_mt_qty-china_unicom_fail_qty,china_unicom_fail_qty,date(sum_time_start));
INSERT tbl_mobile_operators_sm_sum(operator_name,mt_qty,success_qty,fail_qty,sum_time)
values('中国电信',china_telecom_mt_qty,china_telecom_mt_qty-china_telecom_fail_qty,china_telecom_fail_qty,date(sum_time_start));END