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

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

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