问一下, mysql数据库.CREATE FUNCTION applicants_count (state INT UNSIGNED, city INT UNSIGNED, start INT UNSIGNED) RETURNS TEXT DETERMINISTIC
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE result TEXT DEFAULT '';
DECLARE d_id INT UNSIGNED;
DECLARE d_name VARCHAR(20);
DECLARE d_online TINYINT(1);
DECLARE d_Ind VARCHAR(100);
DECLARE d_Cat VARCHAR(100);
DECLARE d_Date VARCHAR(10);
DECLARE d_Deg VARCHAR(10);
DECLARE d_state VARCHAR(2);
DECLARE d_title VARCHAR(200); DECLARE cur CURSOR FOR SELECT start Id, accounts.screen_name ScreenName, 0 Online, IF(applicants.k_industry IS NULL,'---',applicants.k_industry) Ind,
job_function Cat, substr(LastLogin, 1, 10) Date, degree.name Deg, states.state,
applicants.k_title Title
FROM applicants
LEFT JOIN accounts on applicants.account_id = accounts.id
LEFT JOIN online ON applicants.account_id = online.account_id
LEFT OUTER JOIN degree on applicants.max_degree = degree.id
LEFT JOIN states on applicants.state = states.id
WHERE accounts.type = 1 AND accounts.closed = 0 AND online.account_id IS NULL AND ((true) AND (true))
ORDER BY FLOOR((UNIX_TIMESTAMP(accounts.LastLogin)-UNIX_TIMESTAMP(NOW()))/2592000) desc , accounts.screen_name asc
LIMIT start, 20;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN cur;
REPEAT
FETCH cur INTO d_id, d_name, d_online, d_Ind, d_Cat, d_Date, d_Deg, d_state, d_title;
IF NOT done THEN
SET result = CONCAT(result, '<id>', d_id, '</id>');
END IF;
UNTIL done END REPEAT;
CLOSE cur;
RETURN result;
END |
这个function 为什么不对啊?
..
LIMIT start, 20;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
...这里的有错。这儿limit中不能用变量吗?
SET @a = start;
PREPARE stmt1 FROM 'SELECT accounts.id Id, accounts.screen_name ScreenName, 0 Online, IF(applicants.k_industry IS NULL,\'---\',applicants.k_industry) Ind,
job_function Cat, substr(LastLogin, 1, 10) Date, degree.name Deg, states.state,
applicants.k_title Title
FROM applicants
LEFT JOIN accounts ON applicants.account_id = accounts.id
LEFT JOIN online ON applicants.account_id = online.account_id
LEFT OUTER JOIN degree on applicants.max_degree = degree.id
LEFT JOIN states on applicants.state = states.id
WHERE accounts.type = 1 AND accounts.closed = 0 AND online.account_id IS NULL AND ((true) AND (true))
ORDER BY FLOOR((UNIX_TIMESTAMP(accounts.LastLogin)-UNIX_TIMESTAMP(NOW()))/2592000) desc , accounts.screen_name asc
LIMIT ?, 20';
DECLARE cur CURSOR FOR EXECUTE stmt1 USING @a;
DEALLOCATE PREPARE stmt1;
......请问这有什么问题?EXECUTE 没有返回值吗?