mysql 中字符串拼接，查询sql语句总结

DELIMITER $$

USE `ld_wpfmgl_sys`$$

DROP PROCEDURE IF EXISTS `code_query`$$

CREATE DEFINER=`root`@`%` PROCEDURE `code_query`(IN `$opfrom` INT, IN `$username` VARCHAR(50), IN `$password` VARCHAR(50), IN `$pagesize` INT, IN `$pageindex` INT, IN `$enterpriseID` INT, IN `$productSeriesID` INT, IN `$productID` INT, IN `$productStandard` VARCHAR(50), IN `$taskID` INT, IN `$code` VARCHAR(50))
COMMENT '3.35 编码查询 web'
BEGIN
/*********************参数定义区域(begin)*************************/

/*定义接收外部参数变量*/
DECLARE $_opfrom INT; # 命令请求来源
DECLARE $_username VARCHAR(50); # 用户名
DECLARE $_password VARCHAR(50); # 密码

DECLARE $_pagesize INT DEFAULT 0; # 页码数
DECLARE $_pageindex INT DEFAULT 0; # 当前页数

DECLARE $_enterpriseID INT DEFAULT 0; # 企业ID
DECLARE $_productSeriesID INT DEFAULT 0; # 产品系列ID
DECLARE $_productID INT DEFAULT 0; # 产品ID
DECLARE $_productStandard VARCHAR(50) ; # 产品规格
DECLARE $_taskID INT ; # 任务名称
DECLARE $_code VARCHAR(50) ; # 码值

/*内部使用变量*/
DECLARE $totalPages INT DEFAULT 0; # 返回用户总数

DECLARE $sqltext VARCHAR(2000); # 拼接sql串

DECLARE $f_code_id INT DEFAULT 0 ; # 父码ID

DECLARE $biaoshi INT DEFAULT 0; # 标识记号

DECLARE $t_id INT DEFAULT 0; # 任务id

/*错误代码及描述

*/
DECLARE $_error INT DEFAULT 0;
#declare continue handler for sqlexception set $_error=1;

/*接收外部参数*/
SET $_opfrom =IFNULL($opfrom,0);
SET $_username=IFNULL($username,'');
SET $_password=IFNULL($PASSWORD,'');

SET $_pagesize=IFNULL($pagesize,0);
SET $_pageindex=IFNULL($pageindex,0);

SET $_enterpriseID = IFNULL($enterpriseID ,0);
SET $_productSeriesID = IFNULL($productSeriesID,0);
SET $_productID = IFNULL($productID ,0);
SET $_productStandard = IFNULL($productStandard,'');
SET $_taskID = IFNULL($taskID ,0);
SET $_code = IFNULL($CODE ,'');

/*********************参数定义区域(end)***************************/

/*********************业务逻辑处理区域(begin)*********************/
#事务开始

SET $sqltext = 'select s.id as taskNO,bu.c_name as enterpriseName,ps.serie_name as proSeries,p.product_name as proName, p.product_spec as proStandard,pr.name as packRatio_name,pr.ratio as packRatio, s.batchNO as batchNO,s.subBatchNO as subBatchNO,s.workshop as workshop,s.proline as proline,s.team as team,s.proBeginDate,s.proEndDate,s.product_date as produceDate,s.expired_date as exprieDate, s.code_count as codeCount,(SELECT COUNT(1) FROM ld_bus_code_produced cp where cp.task_id = s.id ) as codeUsed,s.createBy as opName, s.state as status';

SET $sqltext = CONCAT($sqltext,' from ld_bus_task s join ld_bus_business bu on s.business_id=bu.id
join ld_bus_product_series ps on s.product_series_id=ps.id
join ld_bus_product p on s.product_id=p.id
join ld_bus_pack_ratio pr on s.ratio_id=pr.id where s.isvalid=1 ');

#1判断任务ID是否为空
IF($_taskID !=0)THEN

SET $sqltext=CONCAT($sqltext,' and s.id =',$_taskID);

#2判断码值是否为空
ELSEIF($_code!='')THEN

#获取码值所属任务
SELECT task_id FROM ld_bus_code_produced WHERE CODE =$_code
INTO $t_id;

SET $sqltext=CONCAT($sqltext,' and s.id =',$t_id);
#set $sqltext=concat($sqltext,' and cp.code= \'',$_code,'\'');

#3判断产品规格是否为空
ELSEIF($_productStandard!='')THEN

SET $sqltext=CONCAT($sqltext,' and p.product_spec= \'',$_productStandard,'\'');

ELSE

SET $biaoshi = 1;

END IF;#1

#任务号，编码，产品规格都为空时才判断企业信息
IF($biaoshi=1)THEN
#判断企业是否为空
IF($_enterpriseID !=0)THEN

SET $sqltext=CONCAT($sqltext,' and s.business_id = ',$_enterpriseID);

END IF;

#判断产品系列是否为空
IF($_productSeriesID!=0)THEN

SET $sqltext=CONCAT($sqltext,' and s.product_series_id= ',$_productSeriesID);

END IF;

#判断产品是否为空
IF($_productID!=0)THEN

SET $sqltext=CONCAT($sqltext,' and s.product_id= ',$_productID);

END IF;

END IF;


#set $sqltext = concat($sqltext,' where cp.isValid = 1 group by cp.task_id ');

# 输出拼接sql语句返回的总数
SET @querycount = CONCAT('select count(1) as totalPages from (',$sqltext,')a');

PREPARE querycount FROM @querycount;

EXECUTE querycount;

# 拼接分页语句
SET $sqltext = CONCAT($sqltext,' order by s.createDate desc limit ',$_pageindex,',',$_pagesize);

#输出拼接的sql语句
SET @querysql = $sqltext;

PREPARE stmt FROM @querysql;

EXECUTE stmt;


/*********************业务逻辑处理区域(end)***********************/

/*********************事务报错处理区域(begin)*********************/
#2 报错处理
IF($_error=1) THEN

ROLLBACK;
ELSE

COMMIT;
END IF;
/*********************事务报错处理区域(end)***********************/

/*********************操作日志记录区域(begin)*********************/
INSERT INTO ld_sys_use_log(username,optname,optfrom,optdesc,errorCode)
VALUES($_username,'code_query',$_opfrom,'3.35 编码查询 web',$_error);

/*********************操作日志记录区域(end)***********************/

/*********************返回区域(begin)*****************************/
SELECT $_error AS errorCode;
/*********************返回区域(end)*******************************/
END$$

DELIMITER ;