mysql 存储过程动态sql

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//

DELIMITER ;

总结：通过into关键字可以将字段放入临时变量中，然后就可以操作了