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：结果：

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

转载于:https://www.cnblogs.com/zgq7/p/10417340.html