mysql 动态sql取结果集_Mysql 动态sql语句返回结果集-存储过程

用户变量的：@变量；

定义全局变量的：set GLOBAL 变量名  或者  set @@global.变量名；

局部变量：declare 变量名 类型

DELIMITER $$    DROP PROCEDURE IF EXISTS `rhtd_account_report_proc`$$ CREATE PROCEDURE `rhtd_callcenter`.`rhtd_account_report_proc`(IN userids VARCHAR(2000),IN  starttimeStart VARCHAR(20),IN starttimeEnd VARCHAR(20),IN timespanlong VARCHAR(10))      BEGIN DECLARE ids VARCHAR(2000); DECLARE selectsql VARCHAR(2000); DECLARE conditionsql VARCHAR(2000); DECLARE replace_ VARCHAR(2000); SET selectsql =' SUM(CASE WHEN bill.calltype = 50 THEN 1 ELSE 0 END)+SUM(CASE WHEN bill.calltype = 51 THEN 1 ELSE 0 END) CallIn,  SUM(CASE WHEN bill.calltype = 50 THEN IFNULL(timespanlong,0) ELSE 0 END) AS CallInTimelong,  SUM(CASE WHEN bill.calltype = 51 THEN 1 ELSE 0 END) InvalidIn, FORMAT((1- IFNULL(SUM(CASE WHEN bill.calltype = 51 THEN 1 ELSE 0 END)/(SUM(CASE WHEN bill.calltype = 50 THEN 1 ELSE 0 END)+SUM(CASE WHEN bill.calltype = 51 THEN 1 ELSE 0 END)),1))*100,2)  succRate, SUM(CASE WHEN bill.calltype = 52 THEN 1 ELSE 0 END) CallOut,  SUM(CASE WHEN bill.calltype = 52 THEN IFNULL(timespanlong,0) ELSE 0 END) AS CallOutTimelong,  SUM(CASE WHEN bill.calltype = 53 THEN 1 ELSE 0 END) InvalidOut,  SUM(IFNULL(timespanlong,0)) CallTimelong, bill.createdby, user.name '; SET conditionsql = ' WHERE 1=1 '; IF ISNULL(userids) || LENGTH(TRIM(userids))<1 THEN  SET ids = '';  ELSE  SET replace_ = REPLACE(userids,',',''',''');  SET ids =CONCAT('''',replace_,'''');  SET replace_ = CONCAT('(',ids,')');  SET conditionsql = CONCAT(conditionsql,' and bill.createdby IN ',replace_); END IF; IF NOT ISNULL(starttimeStart) && LENGTH(TRIM(starttimeStart))>1 THEN   IF NOT ISNULL(starttimeEnd) && LENGTH(TRIM(starttimeEnd))>1 THEN    SET conditionsql = CONCAT(conditionsql,' and bill.starttime BETWEEN ',starttimeStart,' AND ',starttimeEnd);  END IF; END IF;   IF NOT ISNULL(timespanlong) && LENGTH(TRIM(timespanlong))>1 THEN   SET conditionsql = CONCAT(conditionsql,' and bill.timespanlong >= ',timespanlong); END IF; SET conditionsql = CONCAT(conditionsql,' GROUP BY bill.createdby'); /* *将生成的sql放到用户变量中 */ SET @sqlAll2 = CONCAT('select ',selectsql,' from  rhtd_bill bill  LEFT JOIN base_account USER  ON bill.createdby = user.id  ', conditionsql); /* *将结果集存放到虚拟表stmt中 */ PREPARE stmt FROM @sqlAll2;   EXECUTE stmt;      /* 释放虚拟表 */ DEALLOCATE PREPARE stmt;          END$$ DELIMITER ;