DELIMITER $$
CREATE FUNCTION remove_list_from_collect(uid INT, listid INT) RETURNS INT
BEGIN
DECLARE _curUserId INT;
DECLARE _curResult INT;
DECLARE _totalCount INT;
DECLARE _curCount INT;
SET _curCount = 0; SELECT count(uid) FROM list_seq WHERE list_seq.uid != uid AND list_seq.listid = listid INTO _totalCount;
collectUserLoop: LOOP
SELECT uid FROM list_seq WHERE list_seq.uid != uid AND list_seq.listid = listid LIMIT _curCount,1 INTO _curUserId;
SELECT remove_collect_list(_curUserId, listid) INTO _curResult;
IF _curCount >= _totalCount THEN LEAVE collectUserLoop;
END IF;
SET _curCount = _curCount+1;
END LOOP collectUserLoop;
RETURN _totalCount;
END$$
DELIMITER ;
DELIMITER $$
CREATE FUNCTION remove_list_from_collect(uid INT, listid INT) RETURNS INT
BEGIN
DECLARE _curUserId INT;
DECLARE _curResult INT;
DECLARE _totalCount INT;
DECLARE _curCount INT;
SET _curCount = 0; SELECT count(uid) FROM list_seq WHERE list_seq.uid != uid AND list_seq.listid = listid INTO _totalCount;
collectUserLoop: LOOP
PREPARE S1 FROM 'SELECT uid FROM list_seq WHERE list_seq.uid != uid AND list_seq.listid = listid LIMIT ?,1 INTO _curUserId';
EXECUTE S1 USING _curCount;
DEALLOCATE PREPARE S1;
SELECT remove_collect_list(_curUserId, listid) INTO _curResult;
IF _curCount >= _totalCount THEN LEAVE collectUserLoop;
END IF;
SET _curCount = _curCount+1;
END LOOP collectUserLoop;
RETURN _totalCount;
END$$
DELIMITER ;两种形式, 均报错You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '_curCount;
DEALLOCATE PREPARE S1;
SELECT remove_collect_list(_curUserId, lis' at line 12求问各位, 如何在limit 后使用一个变量?谢谢。
CREATE FUNCTION remove_list_from_collect(uid INT, listid INT) RETURNS INT
BEGIN
DECLARE _curUserId INT;
DECLARE _curResult INT;
DECLARE _totalCount INT;
DECLARE _curCount INT;
SET _curCount = 0; SELECT count(uid) FROM list_seq WHERE list_seq.uid != uid AND list_seq.listid = listid INTO _totalCount;
collectUserLoop: LOOP
SELECT uid FROM list_seq WHERE list_seq.uid != uid AND list_seq.listid = listid LIMIT _curCount,1 INTO _curUserId;
SELECT remove_collect_list(_curUserId, listid) INTO _curResult;
IF _curCount >= _totalCount THEN LEAVE collectUserLoop;
END IF;
SET _curCount = _curCount+1;
END LOOP collectUserLoop;
RETURN _totalCount;
END$$
DELIMITER ;
DELIMITER $$
CREATE FUNCTION remove_list_from_collect(uid INT, listid INT) RETURNS INT
BEGIN
DECLARE _curUserId INT;
DECLARE _curResult INT;
DECLARE _totalCount INT;
DECLARE _curCount INT;
SET _curCount = 0; SELECT count(uid) FROM list_seq WHERE list_seq.uid != uid AND list_seq.listid = listid INTO _totalCount;
collectUserLoop: LOOP
PREPARE S1 FROM 'SELECT uid FROM list_seq WHERE list_seq.uid != uid AND list_seq.listid = listid LIMIT ?,1 INTO _curUserId';
EXECUTE S1 USING _curCount;
DEALLOCATE PREPARE S1;
SELECT remove_collect_list(_curUserId, listid) INTO _curResult;
IF _curCount >= _totalCount THEN LEAVE collectUserLoop;
END IF;
SET _curCount = _curCount+1;
END LOOP collectUserLoop;
RETURN _totalCount;
END$$
DELIMITER ;两种形式, 均报错You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '_curCount;
DEALLOCATE PREPARE S1;
SELECT remove_collect_list(_curUserId, lis' at line 12求问各位, 如何在limit 后使用一个变量?谢谢。
PREPARE S1 FROM CONCAT('SELECT uid FROM list_seq WHERE list_seq.uid != uid AND list_seq.listid = listid LIMIT ',
_curCount,
'?,1 INTO _curUserId');
EXECUTE S1;
DEALLOCATE PREPARE S1;