mysq动态语句实例

1、实例代码

DELIMITER $$

DROP PROCEDURE IF EXISTS `usbhostdb`.`procSearchRecordInfo` $$
CREATE  PROCEDURE `procSearchRecordInfo`(vMcode VARCHAR(45),vPno VARCHAR(45), vForigin VARCHAR(45), vRdestinations VARCHAR(45), iGflag INT)
BEGIN
DECLARE strSQL TEXT DEFAULT '';
DECLARE strPno TEXT DEFAULT '';
DECLARE strForigin TEXT DEFAULT '';
DECLARE strRdest TEXT DEFAULT '';
DECLARE strFlag TEXT DEFAULT '';

SET @strSQL = "SELECT Gnid,Forigin,Rdestinations,Pno,funCheckGoodsFlag(Gflag) AS Gflag,Gtime FROM
    goodsinfo AS G
    LEFT JOIN manifestinfo AS M ON G.Gid = M.Gid
    LEFT JOIN frominfo AS F ON M.Fid = F.Fid
    LEFT JOIN recipientinfo AS R ON M.Rid = R.Rid
    LEFT JOIN pickupmemberinfo AS P ON G.Pid = P.Pid WHERE G.Gflag <> -1";
IF vMcode IS NOT NULL AND vMcode <> '' THEN
    SET @strSQL = CONCAT(@strSQL," AND Mcode = ?");
END IF;
IF vPno IS NOT NULL AND vPno <> '' THEN
    SET @strSQL = CONCAT(@strSQL," AND Pno = ?");
END IF;
IF vForigin IS NOT NULL AND vForigin <> '' THEN
    SET @strSQL = CONCAT(@strSQL," AND Forigin = ?");
END IF;
IF vRdestinations IS NOT NULL AND vRdestinations <> '' THEN
    SET @strSQL = CONCAT(@strSQL," AND Rdestinations = ?");
END IF;
IF iGflag = 0 OR iGflag = 1 THEN
    SET @strSQL = CONCAT(@strSQL," AND Gflag = ",iGflag);
END IF;
SET @strSQL = CONCAT(@strSQL," ORDER BY Gtime DESC");
SET @a = vMcode;
SET @b = vPno;
SET @c = vForigin;
SET @d = vRdestinations;
PREPARE stmt FROM @strSQL;
IF vMcode <> '' AND vPno <> '' AND vForigin <> '' AND vRdestinations <> '' THEN
EXECUTE stmt using @a,@b,@c,@d;
ELSEIF vMcode = '' AND vPno <> '' AND vForigin <> '' AND vRdestinations <> '' THEN
EXECUTE stmt using @b,@c,@d;
ELSEIF vMcode <> '' AND vPno = '' AND vForigin <> '' AND vRdestinations <> '' THEN
EXECUTE stmt using @a,@c,@d;
ELSEIF vMcode <> '' AND vPno <> '' AND vForigin = '' AND vRdestinations <> '' THEN
EXECUTE stmt using @a,@b,@d;
ELSEIF vMcode <> '' AND vPno <> '' AND vForigin <> '' AND vRdestinations = '' THEN
EXECUTE stmt using @a,@b,@c;
ELSEIF vMcode = '' AND vPno = '' AND vForigin <> '' AND vRdestinations <> '' THEN
EXECUTE stmt using @c,@d;
ELSEIF vMcode = '' AND vPno <> '' AND vForigin = '' AND vRdestinations <> '' THEN
EXECUTE stmt using @b,@d;
ELSEIF vMcode = '' AND vPno <> '' AND vForigin <> '' AND vRdestinations = '' THEN
EXECUTE stmt using @b,@c;
ELSEIF vMcode <> '' AND vPno = '' AND vForigin = '' AND vRdestinations <> '' THEN
EXECUTE stmt using @a,@d;
ELSEIF vMcode <> '' AND vPno = '' AND vForigin <> '' AND vRdestinations = '' THEN
EXECUTE stmt using @a,@c;
ELSEIF vMcode <> '' AND vPno <> '' AND vForigin = '' AND vRdestinations = '' THEN
EXECUTE stmt using @a,@b;
ELSEIF vMcode = '' AND vPno = '' AND vForigin = '' AND vRdestinations <> '' THEN
EXECUTE stmt using @d;
ELSEIF vMcode = '' AND vPno = '' AND vForigin <> '' AND vRdestinations = '' THEN
EXECUTE stmt using @c;
ELSEIF vMcode = '' AND vPno <> '' AND vForigin = '' AND vRdestinations = '' THEN
EXECUTE stmt using @b;
ELSEIF vMcode <> '' AND vPno = '' AND vForigin = '' AND vRdestinations = '' THEN
EXECUTE stmt using @a;
ELSEIF vMcode = '' AND vPno = '' AND vForigin = '' AND vRdestinations = '' THEN
EXECUTE stmt;
END IF;
END $$

DELIMITER ;

2、原来的语句是直接用单引或双引拼串后执行的，之所以后来在后面又进行了很多if else判断，是因为在组串查询时，若传入的数据中带单引号或双引号，会出现语句错误，而转换为mysql的变量后则没有该问题，但是多了很多循环来判断。