mysq动态语句实例
来源:互联网 发布:淘宝一元商品有哪些 编辑:程序博客网 时间:2024/05/16 08:50
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的变量后则没有该问题,但是多了很多循环来判断。
- mysq动态语句实例
- mysq高效查询语句
- MySQ语句查询
- mysq语句整理
- mysq 语句l常见优化
- MySQ数据库中的基础语句
- mysq中的各类查询语句
- 动态SQL Select 语句实例
- 动态生成sql语句实例
- mysq
- SQL Server,Oracle,mysq l语句分页
- MySq之一条mysql语句来更新(插入)查询结果
- mysq数据库安装环境配置及基本入门语句
- robot framework自动化测试实例(第三篇:动态获取坐标,动态等待,循环语句)
- 【Mysql】在mysq中抓取正在l执行的sql语句方法
- mysq对数据的l查询,增加,修改和删除sql语句
- 动态sql语句用法实例:SQL Server2000下验证(部分转载)
- mysq多实例安装和管理 [一] -- 多配置文件方式安装管理
- mysql备份(二)
- 接受不完美的自己
- 30年影响中国安防30人名单
- 容貌是可以改变的
- web 2.0海量小文件cache集群探讨(原创)
- mysq动态语句实例
- Python2.5.4移植项目:import module问题的解决与boa服务器CGI访问问题
- 【测试—自动化测试】每日构建与自动发布_02
- confirm
- 知识是相通的
- prompt
- 遗失儿童节
- sql server和oracle 数据类型
- 在 VS2008 下操作 Excel 的方法总结