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