mysql数据库存储过程带预处理sql的模板

CREATE DEFINER=`root`@`%` PROCEDURE `getMaSalemenPLPercentageRecordByPage`(IN `pageSize` int,IN `pageNo` int,IN `companyID`  long,IN `apixID`  long,IN `userID`  long,IN `beginTime`  varchar(30),IN `endTime`  varchar(30))
BEGIN
#根据日期分组，计算某一页的数据
DECLARE beginS int DEFAULT 0;
DECLARE endS int DEFAULT 10;
DECLARE baseSql VARCHAR(5000) DEFAULT '';
DECLARE totalCount int DEFAULT 0;-- 总条数
DECLARE beginTimePage TIMESTAMP;-- 分页开始时间
DECLARE endTimePage TIMESTAMP;-- 分页结束时间


set beginS=(pageNo-1)*pageSize;
set endS=pageSize;


DROP table if EXISTS baseTable;


set baseSql=CONCAT(baseSql,"create temporary table baseTable select '合计' as total ");
set baseSql=CONCAT(baseSql," from  table_a  where 1=1 ");
if(companyID is not null and companyID != '' and companyID != 0) then  set baseSql=CONCAT(baseSql," AND a.company_id =  ",companyID);  END if;
if(apixID is not null and apixID != '' and apixID != 0) then  set baseSql=CONCAT(baseSql," AND a.apix_id =  ",apixID);  END if;
if(userID is not null and userID != '' and userID != 0) then  set baseSql=CONCAT(baseSql," AND a.user_id =  ",userID);  END if;
if(beginTime is not null and beginTime != '' ) then  set baseSql=CONCAT(baseSql," AND r.query_time >=  '",beginTime,"'");  END if;
if(endTime is not null and endTime != '' ) then  set baseSql=CONCAT(baseSql," AND r.query_time <=  '",date_format(endTime,'%Y-%m-%d 23:59:59'),"'");  END if;

-- select baseSql;
set @baseSql=baseSql;   -- 注意很重要，将连成成的字符串赋值给一个变量（可以之前没有定义，但要以@开头）
  prepare stmt from @baseSql;  -- 预处理需要执行的动态SQL，其中stmt是一个变量
  EXECUTE stmt;      -- 执行SQL语句
  deallocate prepare stmt;     -- 释放掉预处理段

-- 处理好的数据与其他表进行关联等操作

select *
from baseTable ;


truncate baseTable;
END