mysql 动态sql的拼接以及执行、分页

mysql 动态sql的拼接以及执行、分页,第1张

mysql 动态sql的拼接以及执行、分页

1:建立存储过程,标记参数:

CREATE DEFINER=`root`@`localhost` PROCEDURE `pos_get_drugInList`(IN page integer,IN limitz integer,IN drugCodez VARCHAR(50),IN effectDatez VARCHAR(50),IN chainIdz VARCHAR(19))
BEGIN
DECLARE start integer;
set start = (page-1)*limitz;
set @sql = 'select * from dsos_vot_drugrecord where 1 = 1'; #获取药品信息(最多一千条) if drugCodez <> '' then
set @sql = CONCAT(@sql,' and drugCode= ',drugCodez);
end if; if effectDatez <> '' then
set @sql = CONCAT(@sql,' and effectDate= ','''',effectDatez,'''');end if; if chainIdz <> '' then
set @sql = CONCAT(@sql,' and chainId= ',chainIdz);
end if;
set @sql = CONCAT(@sql,' limit ',start,', ',limitz); -- PREPARE distSQL FROM @SQL ;
-- EXECUTE distSQL;
-- DEALLOCATE PREPARE distSQL ;
select @sql;
END

2:先打印出sql字符串,判断是否是理想值:

3:干掉注释,执行sql语句,看结果:

CREATE DEFINER=`root`@`localhost` PROCEDURE `pos_get_drugInList`(IN page integer,IN limitz integer,IN drugCodez VARCHAR(50),IN effectDatez VARCHAR(50),IN chainIdz VARCHAR(19))
BEGINDECLARE start integer;
set start = (page-1)*limitz;
set @sql = 'select * from dsos_vot_drugrecord where 1 = 1'; #获取药品信息(最多一千条) if drugCodez <> '' then
set @sql = CONCAT(@sql,' and drugCode= ',drugCodez);
end if; if effectDatez <> '' then
set @sql = CONCAT(@sql,' and effectDate= ','''',effectDatez,'''');end if; if chainIdz <> '' then
set @sql = CONCAT(@sql,' and chainId= ',chainIdz);
end if;
set @sql = CONCAT(@sql,' limit ',start,', ',limitz); PREPARE distSQL FROM @SQL ;
EXECUTE distSQL;
DEALLOCATE PREPARE distSQL ;
#select @sql;
END

4:结果:

谢的不好,多多包涵,喜欢的朋友点个赞那啊~(๑╹◡╹)ノ"""

欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/zaji/587356.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2022-04-12
下一篇 2022-04-12

发表评论

登录后才能评论

评论列表(0条)

保存