mysql 存储过程 CONCAT 字符串拼接

 

 

mysql 存储过程

 

CREATE  PROCEDURE  pro_province_report (IN startDate VARCHAR(20),IN endDate VARCHAR(20),IN SourceType INT)  
     /*
    功能：根据时间，来源SourceType 
        统计 每个省份的 注册用户 投稿数 作品点击量 投票量 参与人数 
     pro_province_report();
    */

  BEGIN  
   
  DECLARE temp_user_sql VARCHAR(500);
  DECLARE temp_works_sql VARCHAR(500); 
  #条件
  DECLARE dateStr VARCHAR(100); 
  DECLARE dateStr_w VARCHAR(100); 
  #来源条件
  DECLARE sourceStr VARCHAR(100);
  DECLARE sourceStr_w VARCHAR(100);
 
  IF(startDate!=''&&endDate!='') THEN
        SET dateStr=CONCAT( ' AND DATE(addDate)>=DATE("',startDate,'") AND DATE(addDate)<=DATE("',endDate,'")' );
        SET dateStr_w=CONCAT( ' AND DATE(w.addDate)>=DATE("',startDate,'") AND DATE(w.addDate)<=DATE("',endDate,'")' );
  ELSE
        SET dateStr=" AND 1=1 ";
        SET dateStr_w=" AND 1=1 ";
  END IF;

  IF(SourceType>0) THEN
        SET sourceStr=CONCAT(' AND SourceType="',SourceType,'" ');
      SET sourceStr_w=CONCAT(' AND w.SourceType="',SourceType,'" ');
  ELSE
        SET sourceStr=" AND 1=1 ";
        SET sourceStr_w=" AND 1=1 ";
  END IF;

   
  #用户注册 临时表 

  DROP TEMPORARY TABLE IF EXISTS memberUserTemp;

  SET temp_user_sql =CONCAT('CREATE TEMPORARY TABLE memberUserTemp
  SELECT COUNT(1) accountSum,provinceID,province FROM MEMBERUSER WHERE provinceID>0 ',dateStr,sourceStr,' GROUP BY provinceID');         
  DROP TEMPORARY TABLE IF EXISTS memberUserTemp;

   set @temp_user_sql=temp_user_sql;   #将连成成的字符串赋值给一个变量（可以之前没有定义，但要以@开头）
   prepare stmt from @temp_user_sql;  #预处理需要执行的动态SQL，其中stmt是一个变量
   EXECUTE stmt;      #执行SQL语句
   deallocate prepare stmt;    #释放掉预处理段
  #投稿数 临时表 
  SET temp_works_sql=CONCAT('CREATE TEMPORARY TABLE worksTemp
  SELECT COUNT(1) worksSum,provinceID,province FROM WORKS  WHERE provinceID>0 ',dateStr,sourceStr,' GROUP BY provinceID');
  DROP TEMPORARY TABLE IF EXISTS worksTemp;  
   set @temp_works_sql=temp_works_sql;    
   prepare stmt from @temp_works_sql;   
   EXECUTE stmt;    
   deallocate prepare stmt;   
 

  END