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全局变量正则表达式

解决方案 »

  1.   

    /*统计被过滤短信数量*/
    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