我想在MySQL中完成这样一个功能,根据传入的参数动态拼装成一个sql,然后使用Prepare来执行这个sql(这些已经完成),然后得到其查询结果,用游标遍历结果集,并根据结果集里的某个属性的值进行一些操作。可我现在无法获得Execute后取得的返回结果。
下面是我的procedure,是可以执行的,关键是在哪里能够对返回的结果集进行获取,我不想再程序中获取,最好可以在存储过程中完成遍历结果集,大家看看能不能有什么好办法。
DELIMITER $$
DROP PROCEDURE IF EXISTS getspecial $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getspecial`(
IN starttime VARCHAR(8),
IN startcity VARCHAR(3),
IN leavecity VARCHAR(3),
IN aircom VARCHAR(2)
)
BEGIN
DECLARE sqlStr VARCHAR(1000);
DECLARE city_Couple VARCHAR(7);
SET sqlStr = 'select sf.specialfileid,so.specialowcoursesid,PERFORMSTOPDATE,discount,isexflight,
flight,dayahead,saleticketdate,sprice from specialowcourses so,specialfile sf
where so.specialfileid = sf.specialfileid ';
IF starttime IS NOT NULL THEN
SET @dw = DATE_FORMAT(dateConvert(starttime),'%W');
SET sqlStr = CONCAT(sqlStr,' and sf.',@dw,' = 1');
SET sqlStr = CONCAT(sqlStr,' and ',starttime,' between sf.performstartdate and sf.performstopdate ');
SET sqlStr = CONCAT(sqlStr,' and exdate not like\'',starttime,'\'');
END IF;
IF startcity IS NOT NULL AND leavecity IS NOT NULL THEN
SET city_Couple = CONCAT(startcity,'-',leavecity);
SET sqlStr = CONCAT(sqlStr,' and (courses like \'%',city_Couple,'%\' or courses like \'%',UCASE(startcity),'-ALL%\' or courses like \'%ALL-',UCASE(leavecity),'%\' or courses like \'%ALL-ALL%\') ');
SET sqlStr = CONCAT(sqlStr,' and (excourses not like \'%',city_Couple,'%\' and excourses not like \'%',UCASE(startcity),'-ALL%\' and excourses not like \'%ALL-',UCASE(leavecity),'%\' and excourses not like \'%ALL-ALL%\') ');
END IF;
IF aircom IS NOT NULL THEN
SET sqlStr = CONCAT(sqlStr,' and sf.airwaycode = \'',aircom,'\' ');
END IF;
SET @sql = sqlStr;
PREPARE STMT FROM @sql;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
END$$
DELIMITER ;
就是上面这段代码,请大家帮想想办法,我估计是不是能在
PREPARE STMT FROM @sql;
EXECUTE STMT;
这里做些什么来获取返回结果呢?我知道Mysql的存储过程对动态查询的支持很有限,如果不能的话有哪位仁兄有什么好点的办法可以完成我的需求,谢谢了。