getID:
delimiter $$
DROP PROCEDURE IF EXISTS `getID`$$
CREATE PROCEDURE getID(IN appID INT, IN tID INT, IN floor INT)
BEGIN
DECLARE s VARCHAR(255);
DECLARE f INT;
SET f = floor - 1;
SET @mID = 0;
SET @ms=concat('SELECT `id` INTO @mID FROM `comment` WHERE `appid`= ',appID,'
AND `tid`= ',tID,' ORDER BY `id` LIMIT ',f,',1');
PREPARE STMT FROM @ms;
EXECUTE STMT;
END
$$
delimiter ;
第二种写法:
delimiter $$
DROP PROCEDURE IF EXISTS `getID`$$
CREATE PROCEDURE getID(IN appID INT, IN tID INT, IN floor INT)
BEGIN
DECLARE f INT;
SET f = floor - 1;
SET @appID = appID;
SET @tID = tID;
SET @f = f;
SET @mID = 0;
SET @ms = 'SELECT `id` INTO @mID FROM `wy_comment` WHERE `appid`=?
AND `tid`=? ORDER BY `id` LIMIT ?,1';
PREPARE STMT FROM @ms;
EXECUTE STMT USING @appID,@tID,@f;
END
$$
delimiter ;
测试sql:
CALL getID(25,3399,2);
SELECT @mID;
分享到:
2012-09-04 10:05
浏览 1234
分类:数据库
评论