mysql 存储过程动态sql
来源:互联网 发布:mysql体系结构 编辑:程序博客网 时间:2024/06/05 09:50
DROP PROCEDURE IF EXISTS PRO_STA_SYSTEM_ORDER_STATE;
DELIMITER //
CREATE DEFINER=`root`@`%` PROCEDURE `PRO_STA_SYSTEM_ORDER_STATE`(OUT O_RESULT_CODE CHAR(4))
COMMENT '系统订单实时统计'
BEGIN
DECLARE DONE INT DEFAULT TRUE;-- 定义一个标识为true
DECLARE YEAR_MONTH_STR VARCHAR(10);-- 当前年月
DECLARE CONTINUE HANDLER FOR NOT FOUND,SQLEXCEPTION SET DONE = FALSE;-- 异常的情况标识为false
IF month(curdate()) <= 10 THEN
SET YEAR_MONTH_STR = CONCAT(year(curdate()),0,month(curdate()));-- 例如:201703
ELSE
SET YEAR_MONTH_STR = CONCAT(year(curdate()),month(curdate()));-- 例如:201710
END IF;
#到t_company_flows_order_*_*表查寻包括:今日总订单量,成功订单量,流量订单量,流量成功订单量,话费订单量,话费成功订单量,系统卡单数量(流量贝贝平台总卡单数量)
SET @asql = CONCAT('SELECT COUNT(1) TOTAL_ORDER_COUNT,
COUNT(CASE WHEN status = ''2'' then 1 else null end ) TOTAL_SUCCESS_ORDER_COUNT,
COUNT(CASE WHEN businessType = 2 then 1 else null end ) FLOW_ORDER_COUNT,
COUNT(CASE WHEN businessType = 2 AND status = ''2'' then 1 else null end ) FLOW_SUCCESS_ORDER_COUNT,
COUNT(CASE WHEN businessType = 1 then 1 else null end ) BILL_ORDER_COUNT,
COUNT(CASE WHEN businessType = 1 AND status = ''2'' then 1 else null end ) BILL_SUCCESS_ORDER_COUNT,
COUNT(CASE WHEN status in(''1'',''3'') then 1 else null end) SYSTEM_STUCK_ORDER_COUNT INTO @TOTAL_ORDER_COUNT,@TOTAL_SUCCESS_ORDER_COUNT,@FLOW_ORDER_COUNT,
@FLOW_SUCCESS_ORDER_COUNT,@BILL_ORDER_COUNT,@BILL_SUCCESS_ORDER_COUNT,@SYSTEM_STUCK_ORDER_COUNT',' FROM (',
'SELECT businessType,status FROM t_company_flows_order_',YEAR_MONTH_STR,'_01 WHERE createDate >= date(now()) and createDate < date(date_add(now(),interval 1 day)) ',
'UNION ALL SELECT businessType,status FROM t_company_flows_order_',YEAR_MONTH_STR,'_02 WHERE createDate >= date(now()) and createDate < date(date_add(now(),interval 1 day)) ',
'UNION ALL SELECT businessType,status FROM t_company_flows_order_',YEAR_MONTH_STR,'_03 WHERE createDate >= date(now()) and createDate < date(date_add(now(),interval 1 day)) ',
'UNION ALL SELECT businessType,status FROM t_company_flows_order_',YEAR_MONTH_STR,'_04 WHERE createDate >= date(now()) and createDate < date(date_add(now(),interval 1 day)) ',
'UNION ALL SELECT businessType,status FROM t_company_flows_order_',YEAR_MONTH_STR,'_05 WHERE createDate >= date(now()) and createDate < date(date_add(now(),interval 1 day)) ',
'UNION ALL SELECT businessType,status FROM t_company_flows_order_',YEAR_MONTH_STR,'_06 WHERE createDate >= date(now()) and createDate < date(date_add(now(),interval 1 day)) ',
'UNION ALL SELECT businessType,status FROM t_company_flows_order_',YEAR_MONTH_STR,'_07 WHERE createDate >= date(now()) and createDate < date(date_add(now(),interval 1 day)) ',
'UNION ALL SELECT businessType,status FROM t_company_flows_order_',YEAR_MONTH_STR,'_08 WHERE createDate >= date(now()) and createDate < date(date_add(now(),interval 1 day)) ',
'UNION ALL SELECT businessType,status FROM t_company_flows_order_',YEAR_MONTH_STR,'_09 WHERE createDate >= date(now()) and createDate < date(date_add(now(),interval 1 day)) ',
'UNION ALL SELECT businessType,status FROM t_company_flows_order_',YEAR_MONTH_STR,'_10 WHERE createDate >= date(now()) and createDate < date(date_add(now(),interval 1 day)) ) tbl');
prepare sqlstmt from @asql;
execute sqlstmt;
#到order_reau_record_*_*表包括:回调平均成功率,上游渠道卡单数(卡在上游渠道的订单数量),回调平均时长(毫秒)
SET @bsql = CONCAT('SELECT IFNULL(ROUND(COUNT(CASE WHEN CALLBACK_STATUS = ''1'' then 1 else null end )/count(1),2),0) CALLBACK_SUCCESS_AVG,
COUNT(CASE WHEN REQUEST_STATUS = ''1'' AND CALLBACK_STATUS = ''0'' then 1 else null end ) REAU_STUCK_ORDER_COUNT,
IFNULL(ROUND(AVG(CALLBACK_TIME),0),0) CALLBACK_TIMES_AVG INTO @CALLBACK_SUCCESS_AVG,@REAU_STUCK_ORDER_COUNT,@CALLBACK_TIMES_AVG',' FROM (',
'SELECT CALLBACK_STATUS,REQUEST_STATUS,CALLBACK_TIME FROM order_reau_record_',YEAR_MONTH_STR,'_01 WHERE DATE_CREATE >= date(now()) and DATE_CREATE < date(date_add(now(),interval 1 day)) ',
'UNION ALL SELECT CALLBACK_STATUS,REQUEST_STATUS,CALLBACK_TIME FROM order_reau_record_',YEAR_MONTH_STR,'_02 WHERE DATE_CREATE >= date(now()) and DATE_CREATE < date(date_add(now(),interval 1 day)) ',
'UNION ALL SELECT CALLBACK_STATUS,REQUEST_STATUS,CALLBACK_TIME FROM order_reau_record_',YEAR_MONTH_STR,'_03 WHERE DATE_CREATE >= date(now()) and DATE_CREATE < date(date_add(now(),interval 1 day)) ',
'UNION ALL SELECT CALLBACK_STATUS,REQUEST_STATUS,CALLBACK_TIME FROM order_reau_record_',YEAR_MONTH_STR,'_04 WHERE DATE_CREATE >= date(now()) and DATE_CREATE < date(date_add(now(),interval 1 day)) ',
'UNION ALL SELECT CALLBACK_STATUS,REQUEST_STATUS,CALLBACK_TIME FROM order_reau_record_',YEAR_MONTH_STR,'_05 WHERE DATE_CREATE >= date(now()) and DATE_CREATE < date(date_add(now(),interval 1 day)) ',
'UNION ALL SELECT CALLBACK_STATUS,REQUEST_STATUS,CALLBACK_TIME FROM order_reau_record_',YEAR_MONTH_STR,'_06 WHERE DATE_CREATE >= date(now()) and DATE_CREATE < date(date_add(now(),interval 1 day)) ',
'UNION ALL SELECT CALLBACK_STATUS,REQUEST_STATUS,CALLBACK_TIME FROM order_reau_record_',YEAR_MONTH_STR,'_07 WHERE DATE_CREATE >= date(now()) and DATE_CREATE < date(date_add(now(),interval 1 day)) ',
'UNION ALL SELECT CALLBACK_STATUS,REQUEST_STATUS,CALLBACK_TIME FROM order_reau_record_',YEAR_MONTH_STR,'_08 WHERE DATE_CREATE >= date(now()) and DATE_CREATE < date(date_add(now(),interval 1 day)) ',
'UNION ALL SELECT CALLBACK_STATUS,REQUEST_STATUS,CALLBACK_TIME FROM order_reau_record_',YEAR_MONTH_STR,'_09 WHERE DATE_CREATE >= date(now()) and DATE_CREATE < date(date_add(now(),interval 1 day)) ',
'UNION ALL SELECT CALLBACK_STATUS,REQUEST_STATUS,CALLBACK_TIME FROM order_reau_record_',YEAR_MONTH_STR,'_10 WHERE DATE_CREATE >= date(now()) and DATE_CREATE < date(date_add(now(),interval 1 day)) ) tb1');
prepare sqlstmt from @bsql;
execute sqlstmt;
#保存到sta_system_order_state系统订单实时统计表中
INSERT INTO sta_system_order_state(DATE_CREATED,TOTAL_ORDER_COUNT,TOTAL_SUCCESS_ORDER_COUNT,FLOW_ORDER_COUNT,FLOW_SUCCESS_ORDER_COUNT,BILL_ORDER_COUNT,BILL_SUCCESS_ORDER_COUNT,CALLBACK_SUCCESS_AVG,
SYSTEM_STUCK_ORDER_COUNT,REAU_STUCK_ORDER_COUNT,CALLBACK_TIMES_AVG) VALUES
(NOW(),@TOTAL_ORDER_COUNT,@TOTAL_SUCCESS_ORDER_COUNT,@FLOW_ORDER_COUNT,@FLOW_SUCCESS_ORDER_COUNT,@BILL_ORDER_COUNT,@BILL_SUCCESS_ORDER_COUNT,@CALLBACK_SUCCESS_AVG,
@SYSTEM_STUCK_ORDER_COUNT,@REAU_STUCK_ORDER_COUNT,@CALLBACK_TIMES_AVG);
IF DONE THEN
SET O_RESULT_CODE = '0000';-- 成功 '0000'
ELSE
SET O_RESULT_CODE = '9999';-- 失败 '9999'
END IF;
END//
总结:通过into关键字可以将字段放入临时变量中,然后就可以操作了
DELIMITER //
CREATE DEFINER=`root`@`%` PROCEDURE `PRO_STA_SYSTEM_ORDER_STATE`(OUT O_RESULT_CODE CHAR(4))
COMMENT '系统订单实时统计'
BEGIN
DECLARE DONE INT DEFAULT TRUE;-- 定义一个标识为true
DECLARE YEAR_MONTH_STR VARCHAR(10);-- 当前年月
DECLARE CONTINUE HANDLER FOR NOT FOUND,SQLEXCEPTION SET DONE = FALSE;-- 异常的情况标识为false
IF month(curdate()) <= 10 THEN
SET YEAR_MONTH_STR = CONCAT(year(curdate()),0,month(curdate()));-- 例如:201703
ELSE
SET YEAR_MONTH_STR = CONCAT(year(curdate()),month(curdate()));-- 例如:201710
END IF;
#到t_company_flows_order_*_*表查寻包括:今日总订单量,成功订单量,流量订单量,流量成功订单量,话费订单量,话费成功订单量,系统卡单数量(流量贝贝平台总卡单数量)
SET @asql = CONCAT('SELECT COUNT(1) TOTAL_ORDER_COUNT,
COUNT(CASE WHEN status = ''2'' then 1 else null end ) TOTAL_SUCCESS_ORDER_COUNT,
COUNT(CASE WHEN businessType = 2 then 1 else null end ) FLOW_ORDER_COUNT,
COUNT(CASE WHEN businessType = 2 AND status = ''2'' then 1 else null end ) FLOW_SUCCESS_ORDER_COUNT,
COUNT(CASE WHEN businessType = 1 then 1 else null end ) BILL_ORDER_COUNT,
COUNT(CASE WHEN businessType = 1 AND status = ''2'' then 1 else null end ) BILL_SUCCESS_ORDER_COUNT,
COUNT(CASE WHEN status in(''1'',''3'') then 1 else null end) SYSTEM_STUCK_ORDER_COUNT INTO @TOTAL_ORDER_COUNT,@TOTAL_SUCCESS_ORDER_COUNT,@FLOW_ORDER_COUNT,
@FLOW_SUCCESS_ORDER_COUNT,@BILL_ORDER_COUNT,@BILL_SUCCESS_ORDER_COUNT,@SYSTEM_STUCK_ORDER_COUNT',' FROM (',
'SELECT businessType,status FROM t_company_flows_order_',YEAR_MONTH_STR,'_01 WHERE createDate >= date(now()) and createDate < date(date_add(now(),interval 1 day)) ',
'UNION ALL SELECT businessType,status FROM t_company_flows_order_',YEAR_MONTH_STR,'_02 WHERE createDate >= date(now()) and createDate < date(date_add(now(),interval 1 day)) ',
'UNION ALL SELECT businessType,status FROM t_company_flows_order_',YEAR_MONTH_STR,'_03 WHERE createDate >= date(now()) and createDate < date(date_add(now(),interval 1 day)) ',
'UNION ALL SELECT businessType,status FROM t_company_flows_order_',YEAR_MONTH_STR,'_04 WHERE createDate >= date(now()) and createDate < date(date_add(now(),interval 1 day)) ',
'UNION ALL SELECT businessType,status FROM t_company_flows_order_',YEAR_MONTH_STR,'_05 WHERE createDate >= date(now()) and createDate < date(date_add(now(),interval 1 day)) ',
'UNION ALL SELECT businessType,status FROM t_company_flows_order_',YEAR_MONTH_STR,'_06 WHERE createDate >= date(now()) and createDate < date(date_add(now(),interval 1 day)) ',
'UNION ALL SELECT businessType,status FROM t_company_flows_order_',YEAR_MONTH_STR,'_07 WHERE createDate >= date(now()) and createDate < date(date_add(now(),interval 1 day)) ',
'UNION ALL SELECT businessType,status FROM t_company_flows_order_',YEAR_MONTH_STR,'_08 WHERE createDate >= date(now()) and createDate < date(date_add(now(),interval 1 day)) ',
'UNION ALL SELECT businessType,status FROM t_company_flows_order_',YEAR_MONTH_STR,'_09 WHERE createDate >= date(now()) and createDate < date(date_add(now(),interval 1 day)) ',
'UNION ALL SELECT businessType,status FROM t_company_flows_order_',YEAR_MONTH_STR,'_10 WHERE createDate >= date(now()) and createDate < date(date_add(now(),interval 1 day)) ) tbl');
prepare sqlstmt from @asql;
execute sqlstmt;
#到order_reau_record_*_*表包括:回调平均成功率,上游渠道卡单数(卡在上游渠道的订单数量),回调平均时长(毫秒)
SET @bsql = CONCAT('SELECT IFNULL(ROUND(COUNT(CASE WHEN CALLBACK_STATUS = ''1'' then 1 else null end )/count(1),2),0) CALLBACK_SUCCESS_AVG,
COUNT(CASE WHEN REQUEST_STATUS = ''1'' AND CALLBACK_STATUS = ''0'' then 1 else null end ) REAU_STUCK_ORDER_COUNT,
IFNULL(ROUND(AVG(CALLBACK_TIME),0),0) CALLBACK_TIMES_AVG INTO @CALLBACK_SUCCESS_AVG,@REAU_STUCK_ORDER_COUNT,@CALLBACK_TIMES_AVG',' FROM (',
'SELECT CALLBACK_STATUS,REQUEST_STATUS,CALLBACK_TIME FROM order_reau_record_',YEAR_MONTH_STR,'_01 WHERE DATE_CREATE >= date(now()) and DATE_CREATE < date(date_add(now(),interval 1 day)) ',
'UNION ALL SELECT CALLBACK_STATUS,REQUEST_STATUS,CALLBACK_TIME FROM order_reau_record_',YEAR_MONTH_STR,'_02 WHERE DATE_CREATE >= date(now()) and DATE_CREATE < date(date_add(now(),interval 1 day)) ',
'UNION ALL SELECT CALLBACK_STATUS,REQUEST_STATUS,CALLBACK_TIME FROM order_reau_record_',YEAR_MONTH_STR,'_03 WHERE DATE_CREATE >= date(now()) and DATE_CREATE < date(date_add(now(),interval 1 day)) ',
'UNION ALL SELECT CALLBACK_STATUS,REQUEST_STATUS,CALLBACK_TIME FROM order_reau_record_',YEAR_MONTH_STR,'_04 WHERE DATE_CREATE >= date(now()) and DATE_CREATE < date(date_add(now(),interval 1 day)) ',
'UNION ALL SELECT CALLBACK_STATUS,REQUEST_STATUS,CALLBACK_TIME FROM order_reau_record_',YEAR_MONTH_STR,'_05 WHERE DATE_CREATE >= date(now()) and DATE_CREATE < date(date_add(now(),interval 1 day)) ',
'UNION ALL SELECT CALLBACK_STATUS,REQUEST_STATUS,CALLBACK_TIME FROM order_reau_record_',YEAR_MONTH_STR,'_06 WHERE DATE_CREATE >= date(now()) and DATE_CREATE < date(date_add(now(),interval 1 day)) ',
'UNION ALL SELECT CALLBACK_STATUS,REQUEST_STATUS,CALLBACK_TIME FROM order_reau_record_',YEAR_MONTH_STR,'_07 WHERE DATE_CREATE >= date(now()) and DATE_CREATE < date(date_add(now(),interval 1 day)) ',
'UNION ALL SELECT CALLBACK_STATUS,REQUEST_STATUS,CALLBACK_TIME FROM order_reau_record_',YEAR_MONTH_STR,'_08 WHERE DATE_CREATE >= date(now()) and DATE_CREATE < date(date_add(now(),interval 1 day)) ',
'UNION ALL SELECT CALLBACK_STATUS,REQUEST_STATUS,CALLBACK_TIME FROM order_reau_record_',YEAR_MONTH_STR,'_09 WHERE DATE_CREATE >= date(now()) and DATE_CREATE < date(date_add(now(),interval 1 day)) ',
'UNION ALL SELECT CALLBACK_STATUS,REQUEST_STATUS,CALLBACK_TIME FROM order_reau_record_',YEAR_MONTH_STR,'_10 WHERE DATE_CREATE >= date(now()) and DATE_CREATE < date(date_add(now(),interval 1 day)) ) tb1');
prepare sqlstmt from @bsql;
execute sqlstmt;
#保存到sta_system_order_state系统订单实时统计表中
INSERT INTO sta_system_order_state(DATE_CREATED,TOTAL_ORDER_COUNT,TOTAL_SUCCESS_ORDER_COUNT,FLOW_ORDER_COUNT,FLOW_SUCCESS_ORDER_COUNT,BILL_ORDER_COUNT,BILL_SUCCESS_ORDER_COUNT,CALLBACK_SUCCESS_AVG,
SYSTEM_STUCK_ORDER_COUNT,REAU_STUCK_ORDER_COUNT,CALLBACK_TIMES_AVG) VALUES
(NOW(),@TOTAL_ORDER_COUNT,@TOTAL_SUCCESS_ORDER_COUNT,@FLOW_ORDER_COUNT,@FLOW_SUCCESS_ORDER_COUNT,@BILL_ORDER_COUNT,@BILL_SUCCESS_ORDER_COUNT,@CALLBACK_SUCCESS_AVG,
@SYSTEM_STUCK_ORDER_COUNT,@REAU_STUCK_ORDER_COUNT,@CALLBACK_TIMES_AVG);
IF DONE THEN
SET O_RESULT_CODE = '0000';-- 成功 '0000'
ELSE
SET O_RESULT_CODE = '9999';-- 失败 '9999'
END IF;
END//
DELIMITER ;
总结:通过into关键字可以将字段放入临时变量中,然后就可以操作了
0 0
- Mysql 存储过程 动态sql
- mysql 存储过程动态sql
- MYSQL存储过程使用动态SQL 建多表
- mysql存储过程执行动态sql
- mysql存储过程执行动态sql
- mysql存储过程预处理(动态sql)
- mysql 存储过程动态执行sql 例子
- mysql 存储过程 动态sql语句
- mysql存储过程执行动态sql语句
- mysql 存储过程 执行动态sql
- mysql存储过程动态执行sql
- MySQL存储过程实现动态执行SQL
- mysql 存储过程动态执行sql
- mysql 存储过程中使用动态sql
- mysql动态sql,存储过程动态tablename,存储过程参数为表名
- mysql存储过程在动态SQL内获取返回值
- mysql存储过程 在动态SQL内获取返回值
- mysql 存储过程 根据参数 动态执行sql语句
- OpenCV Python教程(1、图像的载入、显示和保存)
- 叠放箱子问题
- 1. performMeasure
- springMVC文件的上传与下载
- ubuntu Hadoop 填坑记
- mysql 存储过程动态sql
- http请求
- Mac系统终端命令行不执行命令 总出现command not found解决方法
- webpack配置环境变量解决“不是内部命令”问题
- 当你有一个想法时--POC
- java 前端乱码问题
- UVA 1151 生成树
- PowerDesigner使用教程
- Linux面试题集锦五